SQL群組聚合函式與視窗函式解析

SQL 的 GROUP BY 子句搭配聚合函式，能有效地對資料進行分組統計，例如計算各部門的員工數量或平均薪資。然而，在處理包含 NULL 值的資料時，COUNT 函式的行為需要特別注意，使用 COUNT(*) 才能正確統計包含 NULL 的組別數量。此外，使用 UNION ALL 時可能產生重複的群組，需留意使用 UNION 去除重複。SELECT 子句中未被聚合函式包裹的欄位，必須出現在 GROUP BY 子句中，確保查詢語法的正確性。理解這些細節，能有效避免 SQL 查詢中常見的錯誤。接著，我們探討視窗函式，它與聚合函式類別似，但可以傳回多個值。透過 OVER 關鍵字，可以將聚合函式轉換為視窗函式。視窗函式的執行順序在 SQL 處理的最後一步，在 ORDER BY 子句之前。PARTITION BY 子句可以將結果集分成多個分割槽，每個分割槽獨立進行計算，而 ORDER BY 子句則可以指定分割槽內的排序方式，這在計算執行總計等場景中非常有用。Framing 子句則更進一步，可以定義不同的「子視窗」來進行更精細的計算，例如計算移動平均值等。

SQL 群組（GROUP BY）與聚合函式（Aggregate Functions）解析

在 SQL 中，GROUP BY 子句用於將資料表中的資料依據特定欄位分組，並對每組資料進行聚合運算。聚合函式如 COUNT、SUM、AVG 等用於計算每組的統計值。本文將探討 GROUP BY 與聚合函式的使用，以及其中可能遇到的一些常見問題與解決方案。

NULL 值與 COUNT 函式的處理

當使用 COUNT 函式統計某欄位的資料筆數時，若該欄位包含 NULL 值，則 NULL 值將被忽略。例如：

SELECT COALESCE(name, 'NULL') AS name, COUNT(name) AS cnt
FROM fruits
GROUP BY name;

執行結果可能如下：

| name | cnt | |

–|

–| | Apple | 1 | | NULL | 0 | | Oranges | 3 | | Peach | 1 |

在上述例子中，對於 name 為 NULL 的資料，COUNT(name) 傳回了 0，因為 NULL 值被忽略了。然而，實際上存在五筆 NULL 資料。

解決 NULL 群組悖論

要正確統計包含 NULL 值的群組資料數量，可以使用 COUNT(*) 取代 COUNT(name)：

SELECT COALESCE(name, 'NULL') AS name, COUNT(*) AS cnt
FROM fruits
GROUP BY name;

執行結果如下：

| name | cnt | |

–|

–| | Apple | 1 | | NULL | 5 | | Oranges | 3 | | Peach | 1 |

#### 內容解密：

• COUNT(*) 統計的是資料表的列數，而非特定欄位的值，因此不會忽略 NULL 值。
• 使用 COUNT(*) 可以正確統計每組的資料筆數，包括包含 NULL 值的群組。

群組重複問題

SQL 結果集允許重複列，因此在某些情況下可能會出現重複的群組。例如，使用 UNION ALL 合併兩個相同的 GROUP BY 查詢結果：

SELECT COALESCE(name, 'NULL') AS name, COUNT(*) AS cnt
FROM fruits
GROUP BY name
UNION ALL
SELECT COALESCE(name, 'NULL') AS name, COUNT(*) AS cnt
FROM fruits
GROUP BY name;

執行結果如下：

| name | cnt | |

–|

–| | Apple | 1 | | NULL | 5 | | Oranges | 3 | | Peach | 1 | | Apple | 1 | | NULL | 5 | | Oranges | 3 | | Peach | 1 |

#### 內容解密：

• 使用 UNION ALL 會保留重複列，因此出現重複的群組。
• 若要避免重複群組，可以使用 UNION（預設會去除重複列）。

SELECT 與 GROUP BY 的關係

在使用 GROUP BY 時，SELECT 子句中未被聚合函式包裹的欄位必須出現在 GROUP BY 子句中。例如：

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

#### 內容解密：

• deptno 出現在 SELECT 子句中且未被聚合，因此必須包含在 GROUP BY 子句中。
• 常數、標量函式傳回值、視窗函式和非相關子查詢的結果是此規則的例外。

視窗函式複習

一旦瞭解了SQL中分組和聚合函式的概念，理解視窗函式就變得容易了。視窗函式與聚合函式類別似，對定義的集合（一個組）中的行進行聚合運算，但與傳回每個組的一個值不同，視窗函式可以為每個組傳回多個值。要進行聚合運算的行組就是視窗。DB2實際上將這樣的函式稱為線上分析處理（OLAP）函式，Oracle將它們稱為分析函式，但ISO SQL標準稱它們為視窗函式，因此本文使用這個術語。

一個簡單的例子

假設您想要計算所有部門的員工總數。傳統的做法是對整個EMP表發出COUNT(*)查詢：

select count(*) as cnt
from emp

CNT

– 14

然而，通常您會發現自己希望從不代表聚合的行或代表不同聚合的行中存取這樣的聚合資料。視窗函式使這類別問題變得簡單。例如，以下查詢顯示瞭如何使用視窗函式從詳細行（一行代表一個員工）中存取聚合資料（員工總數）：

select ename,
deptno,
count(*) over() as cnt
from emp
order by 2

ENAME DEPTNO CNT

CLARK 10 14 KING 10 14 MILLER 10 14 SMITH 20 14 ADAMS 20 14 FORD 20 14 SCOTT 20 14 JONES 20 14 ALLEN 30 14 BLAKE 30 14 MARTIN 30 14 JAMES 30 14 TURNER 30 14 WARD 30 14

內容解密：

此查詢中的視窗函式呼叫是COUNT(*) OVER()。OVER關鍵字的存在表明COUNT的呼叫將被視為視窗函式，而不是聚合函式。通常，SQL標準允許所有聚合函式也可以是視窗函式，而OVER關鍵字是語言區分這兩種用法的方式。

評估順序

在深入研究OVER子句之前，重要的是要注意，視窗函式是在SQL處理的最後一步中執行的，就在ORDER BY子句之前。作為視窗函式最後處理的一個例子，讓我們從前面的部分取出查詢，並使用WHERE子句過濾掉DEPTNO為20和30的員工：

select ename,
deptno,
count(*) over() as cnt
from emp
where deptno not in (20, 30)
order by 2

這將顯示如何根據不同的篩選條件傳回不同的結果。

內容解密：

此查詢首先根據WHERE子句過濾資料，然後應用視窗函式計算剩餘行的總數。最後根據deptno排序結果。

使用GROUP BY和聚合函式時的注意事項

當使用GROUP BY和聚合函式時，SELECT列表中的專案（來自FROM子句中的表）如果未用作聚合函式的引數，則必須包含在GROUP BY子句中。例如：

select deptno, count(*) as cnt
from emp
group by deptno

DEPTNO CNT

10 3 20 5 30 6

但是，如果在SELECT列表中包含其他列，例如JOB，則必須將其新增到GROUP BY子句中：

select deptno, job, count(*) as cnt
from emp
group by deptno, job

DEPTNO JOB CNT

10 CLERK 1 10 MANAGER 1 10 PRESIDENT 1 20 CLERK 2 20 ANALYST 2 20 MANAGER 1 30 CLERK 1 30 MANAGER 1 30 SALESMAN 4

內容解密：

此查詢透過新增JOB列來更改分組，從而更改結果集。因此，必須將JOB包含在GROUP BY子句中以及DEPTNO中；否則，查詢將失敗。

視窗函式詳解：分割槽與排序的應用

視窗函式（Window Function）是SQL中一項強大的功能，允許使用者在不改變查詢結果集行數的情況下進行聚合計算或其他分析操作。本篇將探討視窗函式的PARTITION BY子句及其相關應用。

PARTITION BY 的基本概念

PARTITION BY子句用於將查詢結果集分成多個分割槽，每個分割槽內可以獨立進行聚合計算或其他視窗函式操作。這與傳統的GROUP BY不同，PARTITION BY不會減少結果集的行數，而是為每一行傳回其所屬分割槽的聚合結果。

SELECT ename,
       deptno,
       COUNT(*) OVER(PARTITION BY deptno) AS cnt
FROM emp
ORDER BY 2;

內容解密：

1. COUNT(*) OVER(PARTITION BY deptno)：此陳述式對deptno進行分割槽，並計算每個分割槽內的行數。
2. 結果集中，每個員工對應的CNT值是其所在部門的員工總數。
3. PARTITION BY使得聚合計算在每個部門內獨立進行。

NULL 值對 PARTITION BY 的影響

與GROUP BY類別似，PARTITION BY會將所有NULL值視為同一分割槽。因此，當使用COUNT(*)時，所有NULL值會被計入同一分割槽；而使用COUNT(列名)時，NULL值將被忽略。

SELECT COALESCE(comm, -1) AS comm,
       COUNT(*) OVER(PARTITION BY comm) AS cnt
FROM emp;

內容解密：

1. COALESCE(comm, -1)：將NULL值轉換為-1，以提高可讀性。
2. COUNT(*) OVER(PARTITION BY comm)：計算每個手續費值的出現次數，包括NULL值（被轉換為-1）。

ORDER BY 在視窗函式中的作用

某些情況下，行的處理順序對視窗函式的結果至關重要。此時，可以在OVER子句中使用ORDER BY來指定行的排序方式。

-- 示例：使用ORDER BY的視窗函式
SELECT ename,
       sal,
       ROW_NUMBER() OVER(ORDER BY sal DESC) AS row_num
FROM emp;

內容解密：

1. ROW_NUMBER() OVER(ORDER BY sal DESC)：按薪水降序排列，並為每一行分配一個唯一的行號。
2. ORDER BY sal DESC：指定按薪水降序排序。

同時使用多個視窗函式

視窗函式的一個強大之處在於，可以在同一個查詢中對不同的列使用不同的分割槽和排序方式。

SELECT ename,
       deptno,
       COUNT(*) OVER(PARTITION BY deptno) AS dept_cnt,
       job,
       COUNT(*) OVER(PARTITION BY job) AS job_cnt
FROM emp
ORDER BY 2;

內容解密：

1. COUNT(*) OVER(PARTITION BY deptno)：計算每個部門的員工數量。
2. COUNT(*) OVER(PARTITION BY job)：計算每種職位的員工數量。
3. 同一個查詢中，可以對不同列進行不同的分割槽計算。

視窗函式複習：深入理解SQL視窗函式的應用

視窗函式的基本概念

在SQL中，視窗函式是一種強大的工具，能夠對結果集中的資料進行計算和分析。視窗函式與聚合函式不同，聚合函式會將多行資料聚合成一行，而視窗函式則可以保留原始資料的同時進行計算。

PARTITION BY子句的作用

在視窗函式中，PARTITION BY子句用於將結果集分割成多個分割槽，每個分割槽內可以獨立進行計算。如果沒有指定PARTITION BY子句，則整個結果集被視為一個單一的分割槽。

ORDER BY子句在視窗函式中的作用

某些視窗函式需要對分割槽內的資料進行排序，這就需要使用ORDER BY子句。在SQL Server中，當使用聚合視窗函式時，不允許在OVER子句中使用ORDER BY，但在使用視窗排名函式時則允許。

使用ORDER BY建立執行總計

考慮以下查詢，它計算了部門10中員工的薪水總和和執行總計：

SELECT 
    deptno,
    ename,
    hiredate,
    sal,
    SUM(sal) OVER (PARTITION BY deptno) AS total1,
    SUM(sal) OVER () AS total2,
    SUM(sal) OVER (ORDER BY hiredate) AS running_total
FROM 
    emp
WHERE 
    deptno = 10;

結果分析

| DEPTNO | ENAME | HIREDATE | SAL | TOTAL1 | TOTAL2 | RUNNING_TOTAL | |

-|

|

|

|

-|

-|

| | 10 | CLARK | 09-JUN-81 | 2450 | 8750 | 8750 | 2450 | | 10 | KING | 17-NOV-81 | 5000 | 8750 | 8750 | 7450 | | 10 | MILLER| 23-JAN-82 | 1300 | 8750 | 8750 | 8750 |

#### 內容解密：

1. SUM(sal) OVER (PARTITION BY deptno)：計算部門10中所有員工的薪水總和。
2. SUM(sal) OVER ()：由於沒有指定PARTITION BY，因此計算整個結果集的薪水總和，但由於WHERE子句的限制，結果與total1相同。
3. SUM(sal) OVER (ORDER BY hiredate)：根據員工的僱用日期進行排序，並計算執行總計。

Framing子句的作用

Framing子句允許定義不同的「子視窗」來進行計算。以下查詢展示了不同的Framing子句對計算結果的影響：

SELECT 
    deptno,
    ename,
    sal,
    SUM(sal) OVER (ORDER BY hiredate 
                   RANGE BETWEEN UNBOUNDED PRECEDING 
                   AND CURRENT ROW) AS run_total1,
    SUM(sal) OVER (ORDER BY hiredate 
                   ROWS BETWEEN 1 PRECEDING 
                   AND CURRENT ROW) AS run_total2,
    SUM(sal) OVER (ORDER BY hiredate 
                   RANGE BETWEEN CURRENT ROW 
                   AND UNBOUNDED FOLLOWING) AS run_total3,
    SUM(sal) OVER (ORDER BY hiredate 
                   ROWS BETWEEN CURRENT ROW 
                   AND 1 FOLLOWING) AS run_total4
FROM 
    emp
WHERE 
    deptno = 10;

結果分析

| DEPTNO | ENAME | SAL | RUN_TOTAL1 | RUN_TOTAL2 | RUN_TOTAL3 | RUN_TOTAL4 | |

-|

|

|

-|

-|

-|

-| | 10 | CLARK | 2450 | 2450 | 2450 | 8750 | 7450 | | 10 | KING | 5000 | 7450 | 7450 | 6300 | 6300 | | 10 | MILLER| 1300 | 8750 | 6300 | 1300 | 1300 |

#### 內容解密：

1. RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：從分割槽的第一行到當前行進行累計。
2. ROWS BETWEEN 1 PRECEDING AND CURRENT ROW：從當前行的前一行到當前行進行累計。
3. RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING：從當前行到分割槽的最後一行進行累計。
4. ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING：從當前行到當前行的下一行進行累計。