SQL視窗函式與CTE應用詳解

視窗函式是 SQL 中重要的分析工具，允許在不改變資料排序的情況下執行計算，例如計算移動平均、累計總和等。Framing 子句控制視窗函式的計算範圍，ROWS 根據行數，RANGE 根據值範圍。理解 framing 子句的差異對於準確使用視窗函式至關重要，例如計算特定時間段內的銷售額或排名。此外，公用表表達式（CTE）可以簡化複雜的 SQL 查詢，提高程式碼可讀性和可維護性。CTE 可以作為一個臨時結果集，在多個查詢中重複使用，避免冗餘程式碼。結合視窗函式和 CTE 可以實作更複雜的資料分析，例如計算每個部門的薪資排名、找出每個產品類別的銷售額貢獻度等。

視窗函式詳解：理解 SQL 中的資料分析與運算

在 SQL 中，視窗函式提供了一種強大的工具，能夠在不改變原始資料排序的情況下進行資料分析與彙總計算。本篇文章將探討視窗函式的應用，特別是 framing 子句對查詢輸出的影響。

執行總計的變化：理解 framing 子句的作用

在 SQL 查詢中，ROWS 和 RANGE 是兩個用於定義視窗框架的重要關鍵字。透過不同的 framing 子句，我們可以控制視窗函式的計算範圍。

`ROWS` 與 `RANGE` 的比較

ROWS：根據指定的行數來定義框架，例如 ROWS 1 PRECEDING 表示當前行及前一行。
RANGE：根據值的範圍來定義框架，例如 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 表示從第一行到當前行的所有行。

實際範例解析

考慮以下查詢範例：

SELECT ename,
       sal,
       MIN(sal) OVER (ORDER BY sal) AS min1,
       MAX(sal) OVER (ORDER BY sal) AS max1,
       MIN(sal) OVER (ORDER BY sal 
                      RANGE BETWEEN UNBOUNDED PRECEDING 
                      AND UNBOUNDED FOLLOWING) AS min2,
       MAX(sal) OVER (ORDER BY sal 
                      RANGE BETWEEN UNBOUNDED PRECEDING 
                      AND UNBOUNDED FOLLOWING) AS max2,
       MIN(sal) OVER (ORDER BY sal 
                      RANGE BETWEEN CURRENT ROW 
                      AND CURRENT ROW) AS min3,
       MAX(sal) OVER (ORDER BY sal 
                      RANGE BETWEEN CURRENT ROW 
                      AND CURRENT ROW) AS max3,
       MAX(sal) OVER (ORDER BY sal 
                      ROWS BETWEEN 3 PRECEDING 
                      AND 3 FOLLOWING) AS max4
FROM emp;

結果分析

| ENAME | SAL | MIN1 | MAX1 | MIN2 | MAX2 | MIN3 | MAX3 | MAX4 | |

|

| | SMITH | 800 | 800 | 800 | 800 | 5000 | 800 | 800 | 1250 | | JAMES | 950 | 800 | 950 | 800 | 5000 | 950 | 950 | 1250 | | ADAMS | 1100 | 800 | 1100 | 800 | 5000 | 1100 | 1100 | 1300 | | … | … | … | … | … | … | … | … | … |

詳細解釋

MIN1 和 MAX1：由於預設的 framing 子句是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，因此 MIN1 總是顯示第一個薪水（最小值），而 MAX1 顯示到當前行為止的最大薪水。
```
MIN(sal) OVER (ORDER BY sal) -- 等同於 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
```
MIN2 和 MAX2：這兩個欄位使用 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING，因此它們計算的是整個結果集的最小和最大值。
```
MIN(sal) OVER (ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
```
MIN3 和 MAX3：這兩個欄位僅考慮當前行的薪水，因此它們的值等於 SAL。
```
MIN(sal) OVER (ORDER BY sal RANGE BETWEEN CURRENT ROW AND CURRENT ROW)
```
MAX4：這個欄位使用 ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING，表示取當前行及前後三行的最大值。例如，對於 MARTIN（薪水 = 1250），它會比較 WARD（1250）、ADAMS（1100）、JAMES（950）、MILLER（1300）、TURNER（1500）和 ALLEN（1600）的薪水，因此 MAX4 為 1600。

可讀性與效能的最佳實踐

視窗函式不僅能提供詳細和彙總資訊，還能提升查詢效率。以下是一個實用的查詢範例，能夠同時回答多個問題，例如每個部門的員工數量、不同職位的員工數量以及總員工數。

SELECT deptno,
       job,
       COUNT(*) OVER (PARTITION BY deptno) AS emp_cnt,
       COUNT(job) OVER (PARTITION BY deptno, job) AS job_cnt,
       COUNT(*) OVER () AS total
FROM emp;

程式碼解析

COUNT(*) OVER (PARTITION BY deptno) -- 每個部門的員工數量
COUNT(job) OVER (PARTITION BY deptno, job) -- 每個部門中每個職位的員工數量
COUNT(*) OVER () -- 總員工數量

視窗函式與公用表表達式在SQL查詢中的應用

視窗函式和公用表表達式（CTE）是SQL中強大的工具，能夠簡化複雜的查詢並提高查詢效率。本文將探討視窗函式和CTE的基本概念及其在實際查詢中的應用。

視窗函式的優勢

視窗函式允許在不改變原始資料順序的情況下進行計算。與傳統的聚合函式不同，視窗函式可以傳回詳細資料和聚合結果。以下是一個使用視窗函式的例子，用於計算每個部門的員工總數、不同職位的員工數量以及總員工數：

select deptno,
       emp_cnt as dept_total,
       total,
       max(case when job = 'CLERK' then job_cnt else 0 end) as clerks,
       max(case when job = 'MANAGER' then job_cnt else 0 end) as mgrs,
       max(case when job = 'PRESIDENT' then job_cnt else 0 end) as prez,
       max(case when job = 'ANALYST' then job_cnt else 0 end) as anals,
       max(case when job = 'SALESMAN' then job_cnt else 0 end) as smen
from (
  select deptno,
         job,
         count(*) over (partition by deptno) as emp_cnt,
         count(job) over (partition by deptno, job) as job_cnt,
         count(*) over () as total
  from emp
) x
group by deptno, emp_cnt, total

內容解密：

內部查詢：使用視窗函式計算每個部門的員工總數（emp_cnt）、每個部門中每個職位的員工數量（job_cnt）以及總員工數（total）。
外部查詢：透過CASE表示式將不同職位的員工數量轉換為欄位，方便報表呈現。
分組聚合：根據部門編號（deptno）、部門員工總數（emp_cnt）和總員工數（total）進行分組，以取得最終報表。

使用視窗函式回答多個問題

視窗函式還可以用於回答多個相關問題，例如查詢每個部門的最高和最低薪水、每個職位的最高和最低薪水等。以下是一個例子：

select ename as name,
       sal,
       max(sal) over (partition by deptno) as hiDpt,
       min(sal) over (partition by deptno) as loDpt,
       max(sal) over (partition by job) as hiJob,
       min(sal) over (partition by job) as loJob,
       max(sal) over () as hi,
       min(sal) over () as lo,
       sum(sal) over (partition by deptno order by sal, empno) as dptRT,
       sum(sal) over (partition by deptno) as dptSum,
       sum(sal) over () as ttl
from emp
order by deptno, dptRT

內容解密：

薪水比較：透過視窗函式計算每個部門的最高和最低薪水（hiDpt和loDpt）、每個職位的最高和最低薪水（hiJob和loJob）以及所有員工的最高和最低薪水（hi和lo）。
累計薪水：計算每個部門的薪水累計總和（dptRT）和部門薪水總和（dptSum），以及所有員工的薪水總和（ttl）。
排序：按部門編號和累計薪水排序結果。

公用表表達式（CTE）

CTE是一種臨時結果集，可以在查詢中參照。它對於簡化複雜查詢非常有用，尤其是在需要多次參照同一子查詢時。以下是一個使用CTE的例子，用於找出每個職位的最大員工數量：

with head_count_tab as (
  select job, count(empno) as HeadCount
  from emp
  group by job
)
select max(HeadCount) as HighestJobHeadCount
from head_count_tab

內容解密：

定義CTE：使用WITH子句定義一個名為head_count_tab的CTE，計算每個職位的員工數量。
主查詢：從CTE中查詢最大的員工數量。

Common Table Expressions（CTE）的應用與解析

CTE 的基本概念與優勢

Common Table Expressions（CTE）是 SQL 中一個強大的功能，主要用於克服子查詢（subquery）的一些限制，尤其是在實作遞迴查詢（recursive query）方面。CTE 允許我們在查詢中定義一個臨時的結果集，這個結果集可以在主查詢中被參照。

CTE 的主要優點包括：

提升可讀性：透過將複雜的查詢分解為多個簡單的部分，CTE 使得 SQL 查詢更易於理解和維護。
支援遞迴查詢：CTE 最強大的功能之一是支援遞迴查詢，這使得處理層次結構資料或需要重複計算的資料成為可能。

簡單 CTE 示例：計算每個職位的員工數量

以下是一個簡單的 CTE 示例，用於計算每個職位的員工數量，並找出員工數量最多的職位：

WITH head_count_tab (job, HeadCount) AS (
  SELECT job, COUNT(empno)
  FROM emp
  GROUP BY job
)
SELECT MAX(HeadCount) AS HighestJobHeadCount
FROM head_count_tab;

內容解密：

WITH head_count_tab (job, HeadCount) AS：定義了一個名為 head_count_tab 的 CTE，包含 job 和 HeadCount 兩個欄位。
SELECT job, COUNT(empno) FROM emp GROUP BY job：計算每個職位的員工數量。
SELECT MAX(HeadCount) AS HighestJobHeadCount FROM head_count_tab：從 CTE 結果中找出最大的員工數量。

遞迴 CTE 示例：計算 Fibonacci 數列

遞迴 CTE 是 CTE 的一個強大應用，以下示例展示瞭如何使用遞迴 CTE 計算 Fibonacci 數列的前20個數字：

WITH RECURSIVE workingTable (fibNum, NextNumber, index1) AS (
  SELECT 0, 1, 1
  UNION ALL
  SELECT fibNum + NextNumber, fibNum, index1 + 1
  FROM workingTable
  WHERE index1 < 20
)
SELECT fibNum
FROM workingTable;

內容解密：

WITH RECURSIVE workingTable (fibNum, NextNumber, index1) AS：定義了一個遞迴 CTE，用於計算 Fibonacci 數列。
SELECT 0, 1, 1：初始化第一行的值。
UNION ALL SELECT fibNum + NextNumber, fibNum, index1 + 1 FROM workingTable WHERE index1 < 20：遞迴計算 Fibonacci 數列的下一行，直到 index1 達到20。
SELECT fibNum FROM workingTable：從 CTE 結果中選取 fibNum 欄位的值。

CTE 與子查詢的比較

雖然 CTE 和子查詢都可以實作類別似的功能，但在某些情況下，CTE 提供了更好的可讀性和可維護性。特別是在處理複雜的查詢或需要遞迴計算時，CTE 的優勢更加明顯。

資料函式庫查詢與操作技術深度解析

在資料函式倉管理和查詢領域，SQL 語言提供了強大的功能來處理各種複雜的資料操作需求。本文將探討 SQL 中的多個重要主題，包括日期處理、資料分組、階層式查詢等，並提供實用的範例和技術解析。

日期處理技術

日期處理是資料函式庫操作中的常見任務。不同的資料函式庫系統（如 DB2、MySQL、Oracle、PostgreSQL 和 SQL Server）提供了多樣化的日期函式和操作方式。

日期比較與提取

日期比較：可以使用特定函式提取日期的各個部分（如年、月、日），然後進行比較。例如，使用 DATEPART 函式（SQL Server）或 EXTRACT 函式（PostgreSQL 和 MySQL）來提取日期的特定部分。
```
-- SQL Server
SELECT * FROM 表名 WHERE DATEPART(YEAR, 日期欄位) = 2023;

-- PostgreSQL 和 MySQL
SELECT * FROM 表名 WHERE EXTRACT(YEAR FROM 日期欄位) = 2023;
```

建立日曆

建立日曆：可以透過生成一系列日期來建立日曆。不同資料函式庫系統有不同的方法，例如使用遞迴公用表表達式（CTE）或特定的日期函式。
```
-- PostgreSQL 使用 GENERATE_SERIES 函式
SELECT 日期 FROM GENERATE_SERIES('2023-01-01', '2023-12-31', '1 day') AS 日期;
```

資料分組與彙總

資料分組是 SQL 中的一個重要功能，允許對資料進行彙總和分析。

GROUP BY 子句

基本使用：GROUP BY 子句用於根據一個或多個欄位對資料進行分組，並對每個組進行彙總計算。
```
SELECT 欄位1, SUM(欄位2) AS 總和 FROM 表名 GROUP BY 欄位1;
```

GROUPING SETS 和 CUBE

GROUPING SETS：允許在單一查詢中指定多個分組集合，提供了比 GROUP BY 更靈活的分組方式。

-- Oracle 和 DB2
SELECT 欄位1, 欄位2, SUM(欄位3) FROM 表名 GROUP BY GROUPING SETS((欄位1), (欄位2));

CUBE：是 GROUPING SETS 的一種特殊形式，用於產生所有可能的分組組合。

-- Oracle 和 DB2
SELECT 欄位1, 欄位2, SUM(欄位3) FROM 表名 GROUP BY CUBE(欄位1, 欄位2);

階層式查詢

階層式查詢用於處理具有階層結構的資料，如組織架構或目錄結構。

CONNECT BY 子句（Oracle）

基本使用：CONNECT BY 子句用於定義階層查詢中的父子關係。

-- Oracle
SELECT * FROM 表名 START WITH 父節點 IS NULL CONNECT BY PRIOR 子節點 = 父節點;

處理階層資料的其他方法

其他資料函式庫系統可能需要使用遞迴公用表表達式（CTE）來實作類別似的功能。

-- SQL Server、PostgreSQL 等使用遞迴 CTE
WITH RECURSIVE 階層查詢 AS (
  SELECT * FROM 表名 WHERE 父節點 IS NULL
  UNION ALL
  SELECT 表名.* FROM 表名 JOIN 階層查詢 ON 表名.父節點 = 階層查詢.子節點
)
SELECT * FROM 階層查詢;

結語

本文介紹了 SQL 中的多個重要主題，包括日期處理、資料分組和階層式查詢。這些技術對於處理和分析複雜的資料集至關重要。瞭解並掌握這些技術，將有助於提高資料函式庫查詢和操作的效率和靈活性。

圖表說明：

此處可以插入 Plantuml 圖表來說明階層結構或日期處理流程。

@startuml
skinparam backgroundColor #FEFEFE
skinparam componentStyle rectangle

title SQL視窗函式與CTE應用詳解

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

圖表翻譯： 此圖展示了從開始到結束的流程，包括日期處理、分組彙總和階層式查詢等步驟。

隨著資料函式庫技術的不斷發展，未來可能會出現更多高效的查詢和分析方法。持續學習和掌握新的技術，將有助於更好地應對複雜的資料處理需求。

玄貓 BlackCat

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。

SQL視窗函式與CTE應用詳解

視窗函式詳解：理解 SQL 中的資料分析與運算

執行總計的變化：理解 framing 子句的作用

ROWS 與 RANGE 的比較

實際範例解析

結果分析

| ENAME | SAL | MIN1 | MAX1 | MIN2 | MAX2 | MIN3 | MAX3 | MAX4 | |

|

|

|

|

|

|

|

|

詳細解釋

可讀性與效能的最佳實踐

程式碼解析

視窗函式與公用表表達式在SQL查詢中的應用

視窗函式的優勢

內容解密：

使用視窗函式回答多個問題

內容解密：

公用表表達式（CTE）

內容解密：

Common Table Expressions（CTE）的應用與解析

CTE 的基本概念與優勢

簡單 CTE 示例：計算每個職位的員工數量

內容解密：

遞迴 CTE 示例：計算 Fibonacci 數列

內容解密：

CTE 與子查詢的比較

資料函式庫查詢與操作技術深度解析

日期處理技術

日期比較與提取

建立日曆

資料分組與彙總

GROUP BY 子句

GROUPING SETS 和 CUBE

階層式查詢

CONNECT BY 子句（Oracle）

處理階層資料的其他方法

結語

圖表說明：

玄貓 BlackCat

`ROWS` 與 `RANGE` 的比較