2024年10月04日玄貓（BlackCat）

SQL分組與直方圖技巧

本文探討 SQL 中資料分組與直方圖的建立技巧，涵蓋使用 NTILE 函式進行等分分組、利用字串函式繪製水平和垂直直方圖，以及使用視窗函式突破 GROUP BY 查詢限制，並深入說明 ROLLUP 和 CUBE 擴充套件功能在計算小計和總計方面的應用。

資料函式庫 SQL

SQL 分組直方圖視窗函式 CUBE ROLLUP

在資料函式庫操作中，我們經常需要將資料分組並進行統計分析。本文將介紹如何使用 SQL 進行資料分組，並利用各種技巧建立直方圖，以及如何使用視窗函式和 GROUP BY 擴充套件功能簡化小計和總計的計算。首先，我們可以使用 NTILE 函式將資料等分為指定的組數，方便後續分析。接著，可以利用字串函式，例如 REPEAT、LPAD 或 REPLICATE，根據各組資料的數量生成星號，從而繪製水平直方圖。對於垂直直方圖，則可以結合 ROW_NUMBER 和 MAX 函式實作。此外，視窗函式可以幫助我們突破傳統 GROUP BY 查詢的限制，例如在查詢每個部門最高薪水的同時，顯示員工姓名等非分組欄位。最後，ROLLUP 和 CUBE 擴充套件功能可以簡化小計和總計的計算，避免繁瑣的 UNION ALL 操作。

將資料分組並建立直方圖的技術

12.8 建立預定義數量的分組

在資料分析中，將資料分成固定數量或固定大小的分組是常見的需求。本文將探討如何使用SQL將員薪水料分成四個分組，並建立水平和垂直直方圖來展示各部門的員工數量。

將員工分成四個分組

要將員工分成四個分組，可以使用NTILE視窗函式。以下是一個範例查詢：

SELECT NTILE(4) OVER (ORDER BY empno) AS grp,
       empno,
       ename
FROM emp;

內容解密：

NTILE(4)：將結果集分成四個分組。
OVER (ORDER BY empno)：根據empno排序結果集。
grp：表示分組的編號。

12.9 建立水平直方圖

要建立水平直方圖，可以使用聚合函式COUNT和字串函式來生成代表員工數量的*字元。

DB2的實作方法

SELECT deptno,
       REPEAT('*', COUNT(*)) AS cnt
FROM emp
GROUP BY deptno;

Oracle、PostgreSQL和MySQL的實作方法

SELECT deptno,
       LPAD('*', COUNT(*), '*') AS cnt
FROM emp
GROUP BY deptno;

SQL Server的實作方法

SELECT deptno,
       REPLICATE('*', COUNT(*)) AS cnt
FROM emp
GROUP BY deptno;

內容解密：

COUNT(*)：計算每個部門的員工數量。
REPEAT、LPAD和REPLICATE：根據員工數量生成對應數量的*字元。

12.10 建立垂直直方圖

要建立垂直直方圖，需要使用ROW_NUMBER視窗函式和聚合函式MAX來進行資料轉換。

通用的SQL查詢

SELECT MAX(deptno_10) AS d10,
       MAX(deptno_20) AS d20,
       MAX(deptno_30) AS d30
FROM (
  SELECT ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY empno) AS rn,
         CASE WHEN deptno = 10 THEN '*' ELSE NULL END AS deptno_10,
         CASE WHEN deptno = 20 THEN '*' ELSE NULL END AS deptno_20,
         CASE WHEN deptno = 30 THEN '*' ELSE NULL END AS deptno_30
  FROM emp
) x
GROUP BY rn
ORDER BY rn DESC;

內容解密：

ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY empno)：為每個部門的員工分配一個唯一的編號。
CASE WHEN：根據部門編號傳回*或NULL。
MAX和GROUP BY rn：將結果轉換為垂直直方圖。

使用視窗函式解決GROUP BY查詢限制

在進行資料分析時，我們經常需要執行GROUP BY查詢來彙總資料。然而，傳統的GROUP BY查詢存在一個限制：SELECT子句中只能包含GROUP BY子句中的欄位或聚合函式的結果。本篇文章將介紹如何使用視窗函式來突破這一限制，傳回非GROUP BY欄位。

問題描述

假設我們需要查詢每個部門和每個職位中薪水最高和最低的員工，並傳回員工的姓名、部門、職位和薪水。傳統的GROUP BY查詢無法直接實作這一需求。

解決方案

使用視窗函式MAX OVER和MIN OVER來找出每個部門和每個職位中的最高和最低薪水，然後保留薪水符合這些極值的員工記錄。

程式碼範例

SELECT 
  deptno,
  ename,
  job,
  sal,
  CASE 
    WHEN sal = max_by_dept THEN 'TOP SAL IN DEPT'
    WHEN sal = min_by_dept THEN 'LOW SAL IN DEPT'
  END AS dept_status,
  CASE 
    WHEN sal = max_by_job THEN 'TOP SAL IN JOB'
    WHEN sal = min_by_job THEN 'LOW SAL IN JOB'
  END AS job_status
FROM (
  SELECT 
    deptno,
    ename,
    job,
    sal,
    MAX(sal) OVER (PARTITION BY deptno) AS max_by_dept,
    MAX(sal) OVER (PARTITION BY job) AS max_by_job,
    MIN(sal) OVER (PARTITION BY deptno) AS min_by_dept,
    MIN(sal) OVER (PARTITION BY job) AS min_by_job
  FROM emp
) emp_sals
WHERE sal IN (max_by_dept, max_by_job, min_by_dept, min_by_job);

內容解密：

內層查詢：使用視窗函式MAX(sal) OVER (PARTITION BY deptno)和MIN(sal) OVER (PARTITION BY deptno)找出每個部門的最高和最低薪水。同樣，使用MAX(sal) OVER (PARTITION BY job)和MIN(sal) OVER (PARTITION BY job)找出每個職位的最高和最低薪水。
外層查詢：使用CASE表示式根據內層查詢的結果判斷每個員工的薪水狀態（最高或最低），並傳回相應的狀態文字。
WHERE子句：篩選出薪水等於最高或最低薪水的員工記錄。

結果展示

執行上述查詢後，我們可以得到每個部門和每個職位中薪水最高和最低的員薪水訊，包括部門、姓名、職位、薪水以及相應的狀態。

視窗函式的優勢

視窗函式（如MAX OVER和MIN OVER）允許我們在不改變原始資料粒度的情況下進行聚合計算。這使得我們能夠在SELECT子句中包含非GROUP BY欄位，從而提供了更大的靈活性和分析能力。

簡單小計的計算

在資料分析中，經常需要計算簡單的小計（subtotal），即根據某個欄位進行匯總，同時提供總計（grand total）。例如，計算員薪水料表（EMP）中按職位（JOB）匯總的薪水總和，並提供所有薪水的總計。

解決方案

現代資料函式庫系統提供了多種方法來解決這個問題，其中最常用的方法是使用 GROUP BY 子句的擴充套件功能，如 ROLLUP 或 CUBE。

DB2 和 Oracle

DB2 和 Oracle 資料函式庫支援 ROLLUP 擴充套件，可以輕鬆實作簡單小計的計算。範例如下：

SELECT 
  CASE GROUPING(JOB)
    WHEN 0 THEN JOB
    ELSE 'TOTAL'
  END AS JOB,
  SUM(SAL) AS SAL
FROM EMP
GROUP BY ROLLUP(JOB);

SQL Server 和 MySQL

SQL Server 和 MySQL 也支援 ROLLUP，但語法稍有不同。範例如下：

SELECT 
  COALESCE(JOB, 'TOTAL') AS JOB,
  SUM(SAL) AS SAL
FROM EMP
GROUP BY JOB WITH ROLLUP;

PostgreSQL

PostgreSQL 同樣支援 ROLLUP，其語法與 SQL Server 和 MySQL 類別似：

SELECT 
  COALESCE(JOB, 'TOTAL') AS JOB,
  SUM(SAL) AS SAL
FROM EMP
GROUP BY ROLLUP(JOB);

詳細解析

初步匯總：首先，使用 SUM 聚合函式按 JOB 分組計算薪水總和。
```
SELECT JOB, SUM(SAL) AS SAL
FROM EMP
GROUP BY JOB;
```
結果：
```
JOB         SAL
```

–

– ANALYST 6000 CLERK 4150 MANAGER 8275 PRESIDENT 5000 SALESMAN 5600


2. **使用 ROLLUP**：接著，使用 `ROLLUP` 擴充套件計算小計和總計。

```sql
SELECT JOB, SUM(SAL) AS SAL
FROM EMP
GROUP BY ROLLUP(JOB);

結果：

JOB         SAL

---
-
---
-- 
---
-
---
ANALYST    6000
CLERK      4150
MANAGER    8275
PRESIDENT  5000
SALESMAN   5600
          29025

格式化輸出：最後，使用 CASE 或 COALESCE 對結果進行格式化，將 NULL 值替換為 ‘TOTAL’。

DB2 和 Oracle 的 CASE 使用方法

SELECT 
  CASE GROUPING(JOB)
    WHEN 0 THEN JOB
    ELSE 'TOTAL'
  END AS JOB,
  SUM(SAL) AS SAL
FROM EMP
GROUP BY ROLLUP(JOB);

結果：

JOB         SAL
---
-
---
-- 
---
-
---
---
ANALYST    6000
CLERK      4150
MANAGER    8275
PRESIDENT  5000
SALESMAN   5600
TOTAL      29025

SQL Server 和 MySQL 的 COALESCE 使用方法

SELECT 
  COALESCE(JOB, 'TOTAL') AS JOB,
  SUM(SAL) AS SAL
FROM EMP
GROUP BY JOB WITH ROLLUP;

結果相同：

JOB         SAL
---
-
---
-- 
---
-
---
---
ANALYST    6000
CLERK      4150
MANAGER    8275
PRESIDENT  5000
SALESMAN   5600
TOTAL      29025

使用CUBE擴充套件計算所有可能的表示式組合的小計

在資料分析中，經常需要對資料進行匯總和計算小計。不同的資料函式倉管理系統（DBMS）提供了不同的功能來實作這一需求。本篇文章將介紹如何使用CUBE擴充套件來計算所有可能的表示式組合的小計。

Oracle、DB2和SQL Server的解決方案

這些資料函式倉管理系統的解決方案基本相同。首先，使用聚合函式SUM並按DEPTNO和JOB分組來計算每個JOB和DEPTNO組合的總薪水。

select deptno, job, sum(sal) sal
from emp
group by deptno, job;

內容解密：

select deptno, job, sum(sal) sal：選擇部門編號、職位和薪水的總和。
from emp：從員工表中選擇資料。
group by deptno, job：按部門編號和職位進行分組。

接下來，使用CUBE擴充套件對GROUP BY子句進行擴充套件，以計算DEPTNO、JOB以及整個表的匯總。

select deptno, job, sum(sal) sal
from emp
group by cube(deptno, job);

內容解密：

group by cube(deptno, job)：使用CUBE擴充套件對DEPTNO和JOB進行分組，並計算所有可能的組合的小計。

然後，使用GROUPING函式與CASE表示式結合，格式化結果，使其更具可讀性。

select deptno, job,
case grouping(deptno)||grouping(job)
when '00' then 'TOTAL BY DEPT AND JOB'
when '10' then 'TOTAL BY JOB'
when '01' then 'TOTAL BY DEPT'
when '11' then 'GRAND TOTAL FOR TABLE'
end category,
sum(sal) sal
from emp
group by cube(deptno, job)
order by grouping(job), grouping(deptno);

內容解密：

grouping(deptno)||grouping(job)：將GROUPING函式的結果連線起來，以確定每個行的分類別。
case ... end：根據連線的結果，判斷每個行屬於哪種類別。
order by grouping(job), grouping(deptno)：按JOB和DEPTNO的GROUPING結果排序。

PostgreSQL的解決方案

PostgreSQL的語法與前述資料函式倉管理系統略有不同，但基本思路相同。

select deptno, job,
case concat(cast(grouping(deptno) as char(1)), cast(grouping(job) as char(1)))
when '00' then 'TOTAL BY DEPT AND JOB'
when '10' then 'TOTAL BY JOB'
when '01' then 'TOTAL BY DEPT'
when '11' then 'GRAND TOTAL FOR TABLE'
end category,
sum(sal) as sal
from emp
group by cube(deptno, job);

內容解密：

concat(cast(grouping(deptno) as char(1)), cast(grouping(job) as char(1)))：將GROUPING函式的結果轉換為字元並連線起來。
其他部分與Oracle等資料函式庫的解決方案相同。

MySQL的解決方案

MySQL目前不支援CUBE函式，因此需要使用多個UNION ALL來實作相同的功能。

select deptno, job, 'TOTAL BY DEPT AND JOB' as category, sum(sal) as sal
from emp
group by deptno, job
union all
select null, job, 'TOTAL BY JOB', sum(sal)
from emp
group by job
union all
select deptno, null, 'TOTAL BY DEPT', sum(sal)
from emp
group by deptno
union all
select null, null, 'GRAND TOTAL FOR TABLE', sum(sal)
from emp;

內容解密：

union all：將多個查詢結果合併。
每個查詢分別計算不同維度的小計和總計。

計算所有可能表示式組合的小計

在處理資料報表時，經常需要計算不同維度的小計和總計。不同的資料函式庫系統提供了不同的功能來實作這一需求。本文將介紹如何在不同的資料函式庫系統中使用 GROUPING SETS、CUBE 和 UNION ALL 來計算所有可能的表示式組合的小計。

使用 GROUPING SETS

GROUPING SETS 是 SQL 中的一個擴充套件功能，允許使用者定義多個分組集合，並對每個集合進行聚合運算。這種方法比使用 CUBE 或 ROLLUP 更為靈活，因為它允許使用者精確控制需要計算小計的維度組合。

範例：使用 GROUPING SETS 計算小計

SELECT 
    deptno,
    job,
    CASE 
        GROUPING(deptno) || GROUPING(job)
        WHEN '00' THEN 'TOTAL BY DEPT AND JOB'
        WHEN '10' THEN 'TOTAL BY JOB'
        WHEN '01' THEN 'TOTAL BY DEPT'
        WHEN '11' THEN 'GRAND TOTAL FOR TABLE'
    END AS category,
    SUM(sal) AS sal
FROM 
    emp
GROUP BY 
    GROUPING SETS ((deptno), (job), (deptno, job), ())

內容解密：

GROUPING SETS 的使用：在 GROUP BY 子句中使用 GROUPING SETS 來定義需要計算小計的維度組合。
CASE 表示式的應用：利用 GROUPING 函式和 CASE 表示式來判斷每個結果行的聚合層級，並給出相應的分類別標籤。
SUM(sal) AS sal：計算每個分組的薪水總和。

使用 CUBE

CUBE 是另一種用於計算多維度聚合的 SQL 功能。它會自動為指定的所有維度組合生成小計和總計。

範例：使用 CUBE 計算小計

SELECT 
    deptno,
    job,
    CASE 
        GROUPING(deptno) || GROUPING(job)
        WHEN '00' THEN 'TOTAL BY DEPT AND JOB'
        WHEN '10' THEN 'TOTAL BY JOB'
        WHEN '01' THEN 'TOTAL BY DEPT'
        WHEN '11' THEN 'GRAND TOTAL FOR TABLE'
    END AS category,
    SUM(sal) AS sal
FROM 
    emp
GROUP BY 
    CUBE(deptno, job)

內容解密：

CUBE 的使用：在 GROUP BY 子句中使用 CUBE 來自動生成所有可能的維度組合的小計和總計。
CASE 表示式的應用：與 GROUPING SETS 範例相同，用於判斷聚合層級。

使用 UNION ALL

在不支援 GROUPING SETS 或 CUBE 的資料函式庫系統中，可以使用 UNION ALL 來合併多個查詢結果，以達到同樣的效果。

範例：使用 UNION ALL 計算小計

SELECT 
    deptno, 
    job, 
    'TOTAL BY DEPT AND JOB' AS category, 
    SUM(sal) AS sal
FROM 
    emp
GROUP BY 
    deptno, job
UNION ALL
SELECT 
    NULL, 
    job, 
    'TOTAL BY JOB', 
    SUM(sal)
FROM 
    emp
GROUP BY 
    job
UNION ALL
SELECT 
    deptno, 
    NULL, 
    'TOTAL BY DEPT', 
    SUM(sal)
FROM 
    emp
GROUP BY 
    deptno

內容解密：

UNION ALL 的使用：透過合併多個查詢結果來生成不同維度的小計。
SUM(sal) AS sal：計算每個分組的薪水總和。