返回文章列表
玄貓（BlackCat）

SQL 移動加總與樞紐分析技術解析

本文探討如何使用 SQL 進行資料匯總、重塑與移動計算，包含移動加總和樞紐分析的實作技巧，並提供詳細的程式碼範例與圖表說明，涵蓋多種資料函式庫系統如 DB2、Oracle、PostgreSQL、MySQL 和 SQL Server 的應用。

資料函式庫 SQL
SQL 移動加總 樞紐分析 ROLLUP 遞迴查詢 報表製作

在資料函式庫應用中，高效的資料處理和報表生成至關重要。本文將介紹如何運用 SQL 實作移動加總和樞紐分析，有效地處理和呈現資料。移動加總能計算特定時間區間內的累積值，例如計算員工過去 90 天的薪資總和。樞紐分析則能將資料從行格式轉換為列格式，方便使用者閱讀和分析，例如將各部門的薪資資料按部門和經理進行彙總和展示。兩種技術都能提升資料分析的效率，並提供更具洞察力的報表。

資料匯總與報表重塑：移動加總與樞紐分析

在資料分析與報表生成過程中，經常需要對資料進行匯總、重塑與移動計算，以滿足不同的業務需求。本文將介紹如何使用 SQL 實作移動加總（Moving Sum）與樞紐分析（Pivoting），並給出具體的查詢範例。

移動加總（Moving Sum）

移動加總是一種常見的資料分析技術，用於計算某個時間視窗內的累計值。例如，計算過去 90 天內的薪水總和。

原始查詢

首先，我們使用一個自連線（Self-Join）查詢來找出每個員工入職日期前 90 天內入職的其他員工，並計算其薪水總和。

SELECT e.hiredate,
       e.sal,
       d.sal AS sal_to_sum,
       d.hiredate AS within_90_days
FROM emp e, emp d
WHERE d.hiredate BETWEEN e.hiredate - 90 AND e.hiredate
ORDER BY 1;

內容解密：

  • SELECT e.hiredate, e.sal, d.sal AS sal_to_sum, d.hiredate AS within_90_days：選擇員工的入職日期、薪水，以及與其相關的其他員工的薪水和入職日期。
  • FROM emp e, emp d：對 emp 表進行自連線，分別以 e 和 d 作為別名。
  • WHERE d.hiredate BETWEEN e.hiredate - 90 AND e.hiredate：篩選出入職日期在當前員工入職日期前 90 天內的其他員工。
  • ORDER BY 1：按第一列（入職日期）排序結果。

使用聚合函式最佳化查詢

為了得到更具表達力的結果，我們可以使用聚合函式 SUM 對每個員工計算過去 90 天內的薪水總和。

SELECT e.hiredate,
       e.sal,
       SUM(d.sal) AS spending_pattern
FROM emp e, emp d
WHERE d.hiredate BETWEEN e.hiredate - 90 AND e.hiredate
GROUP BY e.hiredate, e.sal
ORDER BY 1;

內容解密：

  • SUM(d.sal) AS spending_pattern：計算每個分組內 d.sal 的總和，並命名為 spending_pattern。
  • GROUP BY e.hiredate, e.sal：按員工的入職日期和薪水分組，以便計算每個分組的薪水總和。

子查詢實作移動加總

我們也可以使用子查詢來實作相同的功能。

SELECT e.hiredate,
       e.sal,
       (SELECT SUM(sal) FROM emp d WHERE d.hiredate BETWEEN e.hiredate - 90 AND e.hiredate) AS spending_pattern
FROM emp e
ORDER BY 1;

內容解密：

  • (SELECT SUM(sal) FROM emp d WHERE d.hiredate BETWEEN e.hiredate - 90 AND e.hiredate)：子查詢計算每個員工入職日期前 90 天內其他員工的薪水總和。
  • AS spending_pattern：將子查詢結果命名為 spending_pattern。

樞紐分析（Pivoting）與小計

樞紐分析用於將資料從行格式轉換為列格式，以便更好地呈現資料。以下範例展示如何生成包含小計的報表，並將其轉換為更易讀的格式。

原始報表

假設我們有以下報表，顯示每個部門的管理者和薪水總和，以及部門小計和總計。

DEPTNO MGR   SAL
---
---
 
---
-  
---
-
10     7782  1300
10     7839  2450
10           3750
20     7566  6000
20     7788  1100
20     7839  2975
20     7902   800
20          10875
30     7698  6550
30     7839  2850
30           9400
           24025

期望結果

我們希望將報表轉換為以下格式，使其更易讀。

MGR   DEPT10 DEPT20 DEPT30 TOTAL
---
-  
---
---
 
---
---
 
---
---
 
---
--
7566      0   6000      0  
7698      0      0   6550  
7782   1300      0      0  
7788      0   1100      0  
7839   2450   2975   2850  
7902      0    800      0  
          3750  10875   9400 24025

SQL 解法

我們可以使用 ROLLUP 和 CASE 表示式來實作樞紐分析和生成小計。

SELECT mgr,
       SUM(CASE deptno WHEN 10 THEN sal ELSE 0 END) AS dept10,
       SUM(CASE deptno WHEN 20 THEN sal ELSE 0 END) AS dept20,
       SUM(CASE deptno WHEN 30 THEN sal ELSE 0 END) AS dept30,
       SUM(CASE flag WHEN '11' THEN sal ELSE NULL END) AS total
FROM (
    SELECT deptno, mgr, SUM(sal) AS sal,
           CAST(GROUPING(deptno) AS CHAR(1)) || CAST(GROUPING(mgr) AS CHAR(1)) AS flag
    FROM emp
    WHERE mgr IS NOT NULL
    GROUP BY ROLLUP(deptno, mgr)
) x
GROUP BY mgr;

Plantuml 圖示說明：

圖表翻譯： 此圖示展示了從原始資料到最終報表的處理過程。首先，使用 ROLLUP 生成小計；然後，使用 CASE 表示式進行樞紐分析；最後，得到易讀的報表格式。

資料透視與小計報表製作技術解析

在資料分析與報表製作過程中，經常需要將資料進行適當的格式化與重塑，以滿足不同使用者的需求。本篇技術解析將探討如何利用 SQL 查詢技巧來實作資料的透視與小計報表製作。

基礎查詢與資料匯總

首先，我們需要對 emp 表進行查詢，並根據 deptno 和 mgr 進行資料匯總。以下是一個基本的查詢範例：

SELECT deptno, mgr, SUM(sal) AS sal
FROM emp
WHERE mgr IS NOT NULL
GROUP BY mgr, deptno
ORDER BY 1, 2;

內容解密：

  • 該查詢首先篩選出 mgr 不為空的員工記錄。
  • 然後根據 mgr 和 deptno 對員工的薪水進行匯總。
  • 最後按照 deptno 和 mgr 的順序輸出結果。

利用 ROLLUP 擴充套件進行小計計算

接下來，我們使用 ROLLUP 擴充套件來對資料進行小計計算：

SELECT deptno, mgr, SUM(sal) AS sal
FROM emp
WHERE mgr IS NOT NULL
GROUP BY deptno, mgr WITH ROLLUP;

內容解密：

  • ROLLUP 操作會為每個 deptno 生成小計，並最終生成總計。
  • 透過 GROUPING 函式可以識別哪些行是小計或總計。

結合 GROUPING 函式進行標識

為了區分普通匯總資料和小計資料，我們使用 GROUPING 函式：

SELECT deptno, mgr, SUM(sal) AS sal,
       CONCAT(CAST(GROUPING(deptno) AS CHAR(1)), 
              CAST(GROUPING(mgr) AS CHAR(1))) AS flag
FROM emp
WHERE mgr IS NOT NULL
GROUP BY deptno, mgr WITH ROLLUP;

內容解密：

  • GROUPING(deptno) 和 GROUPING(mgr) 分別檢查 deptno 和 mgr 是否為小計或總計行。
  • 將結果拼接成一個二進位制標識 flag，用於後續處理。

最終報表製作

最後，我們利用前一步的結果來製作最終的報表：

SELECT mgr,
       SUM(CASE deptno WHEN 10 THEN sal ELSE 0 END) AS dept10,
       SUM(CASE deptno WHEN 20 THEN sal ELSE 0 END) AS dept20,
       SUM(CASE deptno WHEN 30 THEN sal ELSE 0 END) AS dept30,
       SUM(CASE flag WHEN '11' THEN sal ELSE NULL END) AS total
FROM (
  SELECT deptno, mgr, SUM(sal) AS sal,
         CONCAT(CAST(GROUPING(deptno) AS CHAR(1)), 
                CAST(GROUPING(mgr) AS CHAR(1))) AS flag
  FROM emp
  WHERE mgr IS NOT NULL
  GROUP BY deptno, mgr WITH ROLLUP
) x
GROUP BY mgr
ORDER BY COALESCE(mgr, 9999);

內容解密：

  • 利用 CASE 表示式對不同部門的薪水進行匯總，並生成相應的欄位。
  • 使用子查詢結果中的 flag 欄位來計算總計。
  • 最終結果按 mgr 排序，並使用 COALESCE 處理空值。

圖表翻譯：

此圖示說明瞭資料從原始表格經過多步驟處理，最終形成報表的過程。

13.1 呈現父子關係

問題描述

想要將父項資訊與子項資料一起呈現。例如，顯示每位員工的姓名及其經理的名字，預期結果集如下：

EMPS_AND_MGRS
---
-
---
-
---
-
---
-
---
-
---
-
---
---
FORD works for JONES
SCOTT works for JONES
JAMES works for BLAKE
TURNER works for BLAKE
MARTIN works for BLAKE
WARD works for BLAKE
ALLEN works for BLAKE
MILLER works for CLARK
ADAMS works for SCOTT
CLARK works for KING
BLAKE works for KING
JONES works for KING
SMITH works for FORD

解決方案

透過在 MGR 和 EMPNO 上進行自連線（self-join）來找到每位員工的經理姓名。然後，使用特定資料函式庫系統提供的字串串接功能來生成所需的結果集。

DB2、Oracle 和 PostgreSQL

使用雙豎線（||）作為串接運算子：

select a.ename || ' works for ' || b.ename as emps_and_mgrs
from emp a, emp b
where a.mgr = b.empno;

MySQL

使用 CONCAT 函式進行字串串接：

select concat(a.ename, ' works for ', b.ename) as emps_and_mgrs
from emp a, emp b
where a.mgr = b.empno;

SQL Server

使用加號（+）作為串接運算子：

select a.ename + ' works for ' + b.ename as emps_and_mgrs
from emp a, emp b
where a.mgr = b.empno;

詳解

所有解決方案的實作本質上是相同的，差異僅在於字串串接的方法。

關鍵在於 MGR 和 EMPNO 之間的連線。首先，建立一個笛卡爾積（Cartesian product），將 EMP 表與其自身連線：

select a.empno, b.empno
from emp a, emp b;

這會傳回所有可能的 EMPNO 組合。接下來，篩選結果，只保留每個員工及其經理的 EMPNO：

select a.empno, b.empno as mgr
from emp a, emp b
where a.mgr = b.empno;

現在，透過選擇 B.ENAME 而不是 B.EMPNO，可以傳回每位經理的名字。

如果難以理解這個過程，可以使用純量子查詢（scalar subquery）代替自連線：

select a.ename,
       (select b.ename from emp b where b.empno = a.mgr) as mgr
from emp a;

純量子查詢版本等同於自連線，除了有一行例外：員工 KING 也出現在結果集中。這是因為自連線方案中使用了等值連線，篩選掉了 MGR 為 NULL 的員工。如果需要包含這些員工，可以使用外連線：

-- ANSI 外連線語法
select a.ename, b.ename as mgr
from emp a left join emp b on (a.mgr = b.empno);

-- Oracle 外連線語法
select a.ename, b.ename as mgr
from emp a, emp b
where a.mgr = b.empno (+);

13.2 呈現子-父-祖父關係

問題描述

考慮員工 CLARK，其長官是 KING，而 CLARK 又是 MILLER 的長官，形成一個三層關係。希望能夠表達出從 MILLER 到 KING 的完整階層關係，預期結果集如下：

LEAF___BRANCH___ROOT
---
-
---
-
---
-
---
-
---
--
MILLER-->CLARK-->KING

解決方案

對於支援遞迴查詢功能的資料函式庫系統（如 DB2、PostgreSQL 和 SQL Server），可以使用遞迴公用表表達式（Recursive Common Table Expressions, CTEs）來解決這個問題。

DB2、PostgreSQL 和 SQL Server

使用遞迴 WITH 子句來查詢 MILLER 的經理 CLARK，以及 CLARK 的經理 KING。這裡使用 SQL Server 的字串串接運算子 +：

with x (tree, mgr, depth)
as (
  select cast(ename as varchar(100)),
         mgr, 0
  from emp
  where ename = 'MILLER'
  union all
  select cast(x.tree + '-->' + e.ename as varchar(100)),
         e.mgr, x.depth + 1
  from emp e, x
  where x.mgr = e.empno
)
select tree as leaf___branch___root
from x
where depth = (select max(depth) from x);

詳解

這個查詢首先找到 MILLER，然後遞迴地找到其長官，直到到達最頂層（KING）。透過遞迴 CTE，將每個員工及其長官的名字串接起來，形成所需的階層關係表達。

圖表翻譯：

此圖示呈現了遞迴查詢的過程，從 MILLER 開始，不斷向上查詢其長官，直到 KING。 圖表翻譯： 此圖表顯示了從 MILLER 到 KING 的階層關係，MILLER 的長官是 CLARK，而 CLARK 的長官是 KING。透過這種結構，我們可以清晰地瞭解整個組織架構中的上下級關係。

遞迴查詢在員工階層架構中的應用

在資料函式庫查詢中，處理具有階層結構的資料是一個常見的需求。本文將探討如何使用遞迴查詢（Recursive Query）來呈現員工階層架構，並以不同的資料函式庫系統（DB2、SQL Server、PostgreSQL、MySQL 和 Oracle）為例，展示具體的實作方法。

使用遞迴公用表表達式（Recursive CTE）

DB2、SQL Server、PostgreSQL

這些資料函式庫系統支援使用遞迴公用表表達式（Recursive Common Table Expressions, CTE）來處理階層查詢。以下是一個範例查詢，用於找出從特定員工（例如 ‘MILLER’）開始的階層結構：

WITH RECURSIVE x (tree, mgr, depth) AS (
  SELECT CAST(ename AS VARCHAR(100)), mgr, 0
  FROM emp
  WHERE ename = 'MILLER'
  UNION ALL
  SELECT CAST(x.tree || '-->' || e.ename AS VARCHAR(100)), e.mgr, x.depth + 1
  FROM emp e, x
  WHERE x.mgr = e.empno
)
SELECT tree AS leaf___branch___root
FROM x
WHERE depth = 2;

MySQL

MySQL 的遞迴查詢語法與上述資料函式庫系統類別似，但需要使用 RECURSIVE 關鍵字：

WITH RECURSIVE x (tree, mgr, depth) AS (
  SELECT CAST(ename AS CHAR(100)), mgr, 0
  FROM emp
  WHERE ename = 'MILLER'
  UNION ALL
  SELECT CAST(CONCAT(x.tree, '-->', e.ename) AS CHAR(100)), e.mgr, x.depth + 1
  FROM emp e, x
  WHERE x.mgr = e.empno
)
SELECT tree AS leaf___branch___root
FROM x
WHERE depth = 2;

程式碼解析：

  1. 遞迴CTE定義：首先定義一個遞迴CTE x，包含欄位 tree、mgr 和 depth。
  2. 錨點查詢：第一部分查詢選取起始員工 ‘MILLER’ 的資料，depth 初始化為 0。
  3. 遞迴查詢：第二部分查詢透過 UNION ALL 與錨點查詢結合，遞迴地選取下屬員工的資料，並將 depth 加 1。
  4. 結果過濾：最終查詢根據 depth 值過濾結果，傳回完整的階層路徑。

使用 CONNECT BY 子句（Oracle）

Oracle 資料函式庫提供了 CONNECT BY 子句來處理階層查詢。以下是一個範例查詢，用於實作相同的功能：

SELECT LTRIM(SYS_CONNECT_BY_PATH(ename, '-->'), '-->') AS leaf___branch___root
FROM emp
WHERE LEVEL = 3
START WITH ename = 'MILLER'
CONNECT BY PRIOR mgr = empno;

程式碼解析：

  1. SYS_CONNECT_BY_PATH 函式：用於串聯階層路徑中的員工姓名。
  2. START WITH 子句：指定階層查詢的起始點。
  3. CONNECT BY 子句：定義階層關係，透過 PRIOR 關鍵字存取前一筆記錄的欄位值。
  4. LEVEL 偽列：用於過濾結果，傳回指定層級的資料。

圖表說明

@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

圖表翻譯： 此圖示呈現了從 MILLER 到 KING 的階層關係。MILLER 直接隸屬於 CLARK，而 CLARK 又隸屬於 KING，清晰地展示了員工之間的上下級關係。