在資料函式庫應用中，有效地轉換和呈現資料對於生成有價值的報告至關重要。本文將介紹一系列 SQL 技巧，涵蓋從簡單的資料格式轉換到複雜的報表生成，例如根據現有資料生成預測、將多行資料合併成單行、進行逆樞紐操作以及抑制重複值等。這些技巧將以 Oracle 和 PostgreSQL 的 SQL 語法為例，並提供詳細的程式碼說明和圖表翻譯，幫助讀者理解並應用於實際的資料函式庫操作和報表生成任務中。
資料函式庫查詢中的資料轉換與報告生成技術
在處理資料函式庫查詢時，經常需要將資料從一種格式轉換為另一種格式，以滿足不同的報告需求。本章節將介紹如何使用SQL查詢來實作資料的轉換和報告生成。
生成簡單預測
在某些情況下，我們需要根據現有的資料生成簡單的預測。例如，假設我們有一個訂單處理系統，需要顯示每個訂單的處理狀態，包括驗證和發貨狀態。
使用Oracle實作
WITH nrows AS (
SELECT LEVEL n
FROM DUAL
CONNECT BY LEVEL <= 3
)
SELECT id,
order_date,
process_date,
CASE WHEN nrows.n >= 2 THEN process_date + 1 ELSE NULL END AS verified,
CASE WHEN nrows.n = 3 THEN process_date + 2 ELSE NULL END AS shipped
FROM (
SELECT nrows.n id,
SYSDATE + nrows.n order_date,
SYSDATE + nrows.n + 2 process_date
FROM nrows
) orders, nrows;
使用PostgreSQL實作
SELECT id,
order_date,
process_date,
CASE WHEN gs.n >= 2 THEN process_date + 1 ELSE NULL END AS verified,
CASE WHEN gs.n = 3 THEN process_date + 2 ELSE NULL END AS shipped
FROM (
SELECT gs.id,
CURRENT_DATE + gs.id AS order_date,
CURRENT_DATE + gs.id + 2 AS process_date
FROM GENERATE_SERIES(1,3) gs(id)
) orders,
GENERATE_SERIES(1,3) gs(n);
程式碼解密：
- 使用遞迴查詢或
GENERATE_SERIES函式生成多行資料：首先，我們使用遞迴查詢（Oracle）或GENERATE_SERIES函式（PostgreSQL）生成多行資料，以代表不同的訂單。 - 建立笛卡爾積：然後，我們將生成的訂單資料與另一個遞迴查詢或
GENERATE_SERIES函式的結果建立笛卡爾積，以得到每個訂單的多行資料。 - 使用
CASE表示式計算驗證和發貨日期：最後，我們使用CASE表示式根據行號計算每個訂單的驗證和發貨日期。
將結果集轉換為一行
有時，我們需要將多行資料轉換為一行，以方便報告的生成。例如，將不同部門的員工數量顯示在一行中。
-- 假設原查詢結果為：
-- DEPTNO CNT
--
---
---
---
-
---
---
-- 10 3
-- 20 5
-- 30 6
-- 需要轉換為：
-- DEPTNO_10 DEPTNO_20 DEPTNO_30
--
---
-
---
--
---
-
---
---
---
-
---
---
-- 3 5 6
具體的SQL查詢陳述式取決於所使用的資料函式倉管理系統。對於支援PIVOT操作的資料函式庫，可以直接使用PIVOT；否則，可以使用條件聚合來實作類別似的效果。
程式碼解密：
- 使用條件聚合實作行列轉換：透過使用條件聚合函式（如
SUM、MAX等），結合CASE表示式，可以將多行資料轉換為一行。 - 根據具體需求調整聚合函式和條件：需要根據實際的資料和報告需求，選擇合適的聚合函式和條件表示式。
資料轉換：將列資料轉換為欄位
在許多資料處理和分析的場景中，我們經常需要將資料從一種格式轉換為另一種格式。這種轉換通常被稱為「資料重塑」或「資料樞紐」。本篇文章將介紹如何使用SQL技術來實作資料的樞紐轉換。
將結果集轉換為單一行
首先，我們來看一個經典的例子。假設我們有一個員工表（emp），其中包含部門編號（deptno）和員薪水訊。我們想要計算每個部門的員工數量，並將結果顯示在單一行中。
SELECT
SUM(CASE WHEN deptno = 10 THEN 1 ELSE 0 END) AS deptno_10,
SUM(CASE WHEN deptno = 20 THEN 1 ELSE 0 END) AS deptno_20,
SUM(CASE WHEN deptno = 30 THEN 1 ELSE 0 END) AS deptno_30
FROM emp;
內容解密：
- 使用
CASE表示式來判斷每個員工屬於哪個部門。如果部門編號符合條件，則傳回1，否則傳回0。 - 使用
SUM聚合函式來計算每個部門的員工數量。 - 最終結果將顯示在單一行中，每個部門的員工數量對應一個欄位。
將結果集轉換為多行
接下來，我們來看另一個例子。假設我們想要將員工按照職位（job）分組，並將每個職位的員工姓名顯示在不同的欄位中。
SELECT
MAX(CASE WHEN job = 'CLERK' THEN ename ELSE NULL END) AS clerks,
MAX(CASE WHEN job = 'ANALYST' THEN ename ELSE NULL END) AS analysts,
MAX(CASE WHEN job = 'MANAGER' THEN ename ELSE NULL END) AS mgrs,
MAX(CASE WHEN job = 'PRESIDENT' THEN ename ELSE NULL END) AS prez,
MAX(CASE WHEN job = 'SALESMAN' THEN ename ELSE NULL END) AS sales
FROM (
SELECT job, ename, ROW_NUMBER() OVER (PARTITION BY job ORDER BY ename) rn
FROM emp
) x
GROUP BY rn;
內容解密：
- 使用
ROW_NUMBER()視窗函式為每個職位的員工分配一個唯一的行號。 - 使用
CASE表示式來判斷每個員工屬於哪個職位，並將對應的員工姓名傳回。 - 使用
MAX聚合函式來去除NULL值，並將每個職位的員工姓名顯示在不同的欄位中。 - 最終結果將顯示在多行中，每個職位的員工姓名對應一個欄位。
圖表翻譯：
此查詢首先使用視窗函式為每個部門或職位的員工進行排序和編號，然後使用 CASE 表示式和聚合函式來將這些資料轉換為所需的格式。這種技術在資料分析和報告中非常有用，可以幫助我們更好地理解和呈現資料。
資料轉換：將欄位資料轉換為列資料
在資料函式庫查詢中，經常需要將資料從一種格式轉換為另一種格式。資料轉換是一種常見的需求，尤其是在報表生成和資料分析中。本文將介紹如何使用SQL查詢將欄位資料轉換為列資料。
問題描述
假設我們有一個查詢結果，如下所示：
DEPTNO_10 DEPTNO_20 DEPTNO_30
---
-
---
---
---
-
---
---
---
-
---
---
3 5 6
我們的目標是將這個結果轉換為以下格式：
DEPTNO COUNTS_BY_DEPT
---
---
---
-
---
-
---
---
10 3
20 5
30 6
解決方案
要實作這個轉換，我們可以使用笛卡爾積（Cartesian product）來將欄位資料轉換為列資料。首先，我們需要建立一個檢視表（view），以便於後續的查詢。
create view emp_cnts as
(
select
sum(case when deptno=10 then 1 else 0 end) as deptno_10,
sum(case when deptno=20 then 1 else 0 end) as deptno_20,
sum(case when deptno=30 then 1 else 0 end) as deptno_30
from emp
)
使用UNION運算元
我們可以使用UNION運算元來合併多個查詢結果，從而實作欄位資料到列資料的轉換。
select '10' as deptno, deptno_10 as counts_by_dept from emp_cnts
union all
select '20' as deptno, deptno_20 as counts_by_dept from emp_cnts
union all
select '30' as deptno, deptno_30 as counts_by_dept from emp_cnts
使用笛卡爾積
另一種方法是使用笛卡爾積來實作相同的結果。
select
case id
when 1 then '10'
when 2 then '20'
when 3 then '30'
end as deptno,
case id
when 1 then deptno_10
when 2 then deptno_20
when 3 then deptno_30
end as counts_by_dept
from emp_cnts,
(
select level as id from dual connect by level <= 3
) x
程式碼解密：
- 檢視表的建立：首先，我們建立了一個名為
emp_cnts的檢視表，該檢視表包含三個欄位，分別代表不同部門的計數。 - UNION運算元的使用：透過使用UNION運算元，我們將三個獨立的查詢結果合併成一個結果集，每個查詢結果對應一個部門的計數。
- 笛卡爾積的使用：另一種方法是使用笛卡爾積來實作相同的結果。我們建立了一個臨時表
x，該表包含一個欄位id，其值從1到3。然後，我們使用CASE表示式根據id的值選擇對應的部門和計數。
圖表翻譯：
此圖示呈現了資料轉換的過程：
@startuml
skinparam backgroundColor #FEFEFE
skinparam componentStyle rectangle
title SQL資料函式庫查詢轉換與報告生成技術
package "資料庫架構" {
package "應用層" {
component [連線池] as pool
component [ORM 框架] as orm
}
package "資料庫引擎" {
component [查詢解析器] as parser
component [優化器] as optimizer
component [執行引擎] as executor
}
package "儲存層" {
database [主資料庫] as master
database [讀取副本] as replica
database [快取層] as cache
}
}
pool --> orm : 管理連線
orm --> parser : SQL 查詢
parser --> optimizer : 解析樹
optimizer --> executor : 執行計畫
executor --> master : 寫入操作
executor --> replica : 讀取操作
cache --> executor : 快取命中
master --> replica : 資料同步
note right of cache
Redis/Memcached
減少資料庫負載
end note
@enduml
圖表翻譯： 此圖表呈現了將欄位資料轉換為列資料的兩種方法：使用UNION運算元和使用笛卡爾積。兩種方法都可以實作相同的結果，但笛卡爾積的方法更靈活，可以處理更多的欄位。
資料轉換與報表生成的技術探討
在資料處理與報表生成的過程中，經常需要將資料從一種形式轉換為另一種形式，以滿足不同的業務需求或報表格式。本文將探討如何使用SQL技術來實作資料的轉換和報表的生成，特別是在逆樞紐（Reverse Pivoting）和重複值抑制（Suppressing Repeating Values）方面的應用。
逆樞紐操作：將多列轉換為單列
逆樞紐操作是一種常見的資料轉換技術，用於將多列的資料轉換為單列的形式。以下是一個實際的例子，展示如何將員工的姓名（ENAME）、職位（JOB）和薪水（SAL）轉換為單列輸出。
解決方案
首先，我們需要使用遞迴CTE（Common Table Expression）結合笛卡爾積（Cartesian Product）來為每個員工生成多行資料。然後，使用視窗函式ROW_NUMBER()對每行資料進行排序和編號，最後使用CASE表示式將多列資料轉換為單列。
WITH four_rows (id) AS (
SELECT 1
UNION ALL
SELECT id + 1
FROM four_rows
WHERE id < 4
),
x_tab (ename, job, sal, rn) AS (
SELECT e.ename, e.job, e.sal,
ROW_NUMBER() OVER (PARTITION BY e.empno ORDER BY e.empno)
FROM emp e
JOIN four_rows ON 1 = 1
)
SELECT
CASE rn
WHEN 1 THEN ename
WHEN 2 THEN job
WHEN 3 THEN CAST(sal AS CHAR(4))
END AS emps
FROM x_tab;
程式碼解密
- 遞迴CTE（four_rows）：生成一個包含四行的臨時結果集，用於與員薪水料進行笛卡爾積，確保每個員工都有四行資料。
- 視窗函式（ROW_NUMBER() OVER）：對每個員工的資料進行編號，從1到4。
- CASE表示式：根據
rn的值，將ename、job或sal的值輸出到單列emps中。
重複值抑制：避免在報表中重複顯示相同的值
在生成報表時，經常需要避免重複顯示相同的值，以提高報表的可讀性。以下是一個例子，展示如何抑制DEPTNO的重複顯示。
解決方案
使用視窗函式ROW_NUMBER()來為每個部門的員工進行編號，然後根據編號是否為1來決定是否顯示DEPTNO。
SELECT
CASE
WHEN rn = 1 THEN CAST(deptno AS CHAR(2))
ELSE ''
END AS deptno,
ename
FROM (
SELECT deptno, ename,
ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY empno) AS rn
FROM emp
) AS subquery;
程式碼解密
- 子查詢：使用
ROW_NUMBER()對每個部門的員工進行編號。 - CASE表示式：如果
rn為1，則顯示DEPTNO，否則顯示空值。
資料重塑與報表分析技術
在資料函式庫查詢與報表分析中，經常需要對資料進行重塑與轉換，以滿足特定的報表需求或進行跨行的計算。本文將介紹如何使用SQL技術來實作資料的重塑、報表生成以及跨行計算。
抑制結果集中重複的值
在生成報表時，經常需要抑制結果集中重複的值，以使報表更加清晰易讀。例如，假設我們有一個員工表emp，包含部門編號deptno和員工姓名ename，我們希望輸出每個部門的員工列表，但只顯示一次部門編號。
解決方案
使用視窗函式LAG OVER來比較當前行的部門編號與前一行的部門編號，如果相同則顯示為NULL，否則顯示部門編號。
SELECT
CASE
WHEN LAG(deptno) OVER (ORDER BY deptno) = deptno THEN NULL
ELSE deptno
END AS deptno,
ename
FROM emp;
程式碼解析
LAG(deptno) OVER (ORDER BY deptno)：取得前一行的deptno值，並按照deptno排序。CASE WHEN：比較當前行的deptno與前一行的deptno，如果相同則傳回NULL，否則傳回deptno。- 最終結果只顯示一次部門編號，使報表更加清晰。
將結果集旋轉以便於跨行計算
有時需要進行跨行計算，例如比較不同部門的總薪水。為此，需要將行資料旋轉為列資料，以便於計算。
解決方案
使用聚合函式SUM和條件表示式CASE WHEN來旋轉資料。
SELECT
d20_sal - d10_sal AS d20_10_diff,
d20_sal - d30_sal AS d20_30_diff
FROM (
SELECT
SUM(CASE WHEN deptno = 10 THEN sal END) AS d10_sal,
SUM(CASE WHEN deptno = 20 THEN sal END) AS d20_sal,
SUM(CASE WHEN deptno = 30 THEN sal END) AS d30_sal
FROM emp
) totals_by_dept;
程式碼解析
- 內層查詢使用
SUM(CASE WHEN ... THEN sal END)來計算每個部門的總薪水，並將結果命名為d10_sal、d20_sal和d30_sal。 - 外層查詢對內層查詢的結果進行跨行計算，計算部門20與部門10和部門30之間的薪水差額。
將資料分組到固定大小的桶中
在某些場景下，需要將資料分組到固定大小的桶中，例如將員工按照員工編號分組，每組五人。
解決方案
使用視窗函式ROW_NUMBER OVER來為每行資料分配一個序號，然後除以5並取上限值，以確定每行資料所屬的組別。
SELECT
CEIL(ROW_NUMBER() OVER (ORDER BY empno) / 5.0) AS grp,
empno,
ename
FROM emp;
程式碼解析
ROW_NUMBER() OVER (ORDER BY empno)：為每行資料分配一個序號，按照empno排序。CEIL(... / 5.0)：將序號除以5並取上限值，以確定每行資料所屬的組別。- 最終結果將員工分組到固定大小的桶中，每組五人。