SQL視窗函式進階應用技巧

視窗函式是 SQL 中強大的工具，能有效處理複雜的資料分析問題。不同於聚合函式，視窗函式可以保留個別資料列的資訊，同時進行計算和比較。在實際應用中，視窗函式能簡化許多資料處理流程，例如計算移動平均、排名、分組統計等。本文將示範如何使用視窗函式解決存取前後列資料、排名、去重複等常見問題，並探討在不同資料函式庫中的實作方式，包含 DB2、SQL Server 和 Oracle。此外，文章也將介紹如何利用 SQL 查詢生成簡單的預測資料，例如根據訂單處理時間預測驗證和出貨日期，並提供不同資料函式庫的實作範例，例如使用 Oracle 的 CONNECT BY 子句、PostgreSQL 的 GENERATE_SERIES 函式以及 MySQL 的替代方案。

進階資料查詢技術：使用視窗函式解決複雜問題

在資料函式庫查詢中，視窗函式（Window Functions）提供了一種強大且靈活的方式來處理複雜的資料分析任務。本文將探討如何使用視窗函式來解決三個常見的問題：存取前後列資料、排名結果以及去除重複資料。

11.8 存取前後列資料

問題描述

給定員工薪水錶 EMP，要求查詢每個員工的姓名、薪水、下一位員工的薪水（FORWARD）以及前一位員工的薪水（REWIND）。如果員工是薪水最高或最低者，則 FORWARD 或 REWIND 應分別回傳最低或最高薪水。

解決方案

使用視窗函式 LEAD OVER 和 LAG OVER 可以輕鬆實作此功能：

SELECT ename, sal,
       COALESCE(LEAD(sal) OVER (ORDER BY sal), MIN(sal) OVER ()) AS forward,
       COALESCE(LAG(sal) OVER (ORDER BY sal), MAX(sal) OVER ()) AS rewind
FROM emp;

內容解密：

1. LEAD(sal) OVER (ORDER BY sal)：取得下一行員工的薪水，按照薪水排序。
2. LAG(sal) OVER (ORDER BY sal)：取得上一行員工的薪水，按照薪水排序。
3. COALESCE 函式：用於處理 LEAD 和 LAG 可能回傳的 NULL 值。當員工是薪水最高或最低者時，分別使用 MIN(sal) OVER () 和 MAX(sal) OVER () 回傳最低和最高薪水。

11.9 排名結果

問題描述

對 EMP 表中的員工薪水進行排名，允許並列排名。

解決方案

使用視窗函式 DENSE_RANK OVER 可以實作此功能：

SELECT DENSE_RANK() OVER (ORDER BY sal) AS rnk, sal
FROM emp;

內容解密：

1. DENSE_RANK() OVER (ORDER BY sal)：根據薪水進行排名，並允許並列排名。排名是連續的，不會因為並列而跳過數字。

11.10 去除重複資料

問題描述

從 EMP 表中查詢不同的職位（JOB），不顯示重複資料。

解決方案

除了傳統的 DISTINCT 和 GROUP BY 方法外，還可以使用視窗函式 ROW_NUMBER OVER：

SELECT job
FROM (
  SELECT job,
         ROW_NUMBER() OVER (PARTITION BY job ORDER BY job) AS rn
  FROM emp
) x
WHERE rn = 1;

內容解密：

1. ROW_NUMBER() OVER (PARTITION BY job ORDER BY job)：為每個 JOB 分組內的資料進行編號，當遇到新的 JOB 時，編號重置為 1。
2. 外層查詢：篩選出 rn = 1 的資料，即每個 JOB 的第一筆資料，從而去除重複資料。

進階搜尋技術：查詢最新僱員薪水

在資料函式庫查詢中，經常需要處理複雜的資料檢索需求，例如找出每個部門最新僱員的薪水。本篇文章將探討如何使用 SQL 查詢來實作這一目標。

問題描述

假設我們有一個員薪水料表 emp，包含員工姓名、部門編號、薪水和僱傭日期等資訊。我們需要查詢每個部門最新僱員的薪水，並將結果以特定格式呈現。

解決方案

DB2 和 SQL Server

對於 DB2 和 SQL Server 資料函式庫，我們可以使用視窗函式 MAX OVER 結合 CASE 表示式來實作查詢。

SELECT 
    deptno,
    ename,
    sal,
    hiredate,
    MAX(latest_sal) OVER (PARTITION BY deptno) AS latest_sal
FROM (
    SELECT 
        deptno,
        ename,
        sal,
        hiredate,
        CASE 
            WHEN hiredate = MAX(hiredate) OVER (PARTITION BY deptno) 
            THEN sal 
            ELSE 0 
        END AS latest_sal
    FROM emp
) x
ORDER BY 1, 4 DESC;

Oracle

對於 Oracle 資料函式庫，我們可以使用 KEEP 子句結合 DENSE_RANK 和 LAST 函式來實作查詢。

SELECT 
    deptno,
    ename,
    sal,
    hiredate,
    MAX(sal) KEEP (DENSE_RANK LAST ORDER BY hiredate) OVER (PARTITION BY deptno) AS latest_sal
FROM emp
ORDER BY 1, 4 DESC;

查詢原理

DB2 和 SQL Server

1. 子查詢：首先，在子查詢中使用 MAX OVER 視窗函式找出每個部門最新僱員的僱傭日期。如果某員工的僱傭日期與該部門最新僱傭日期相符，則傳回該員工的薪水，否則傳回 0。

   SELECT 
       deptno,
       ename,
       sal,
       hiredate,
       CASE 
           WHEN hiredate = MAX(hiredate) OVER (PARTITION BY deptno) 
           THEN sal 
           ELSE 0 
       END AS latest_sal
   FROM emp;
   

2. 外層查詢：然後，在外層查詢中使用 MAX OVER 再次對結果進行分組，以取得每個部門非零的最新薪水值。

Oracle

1. 直接查詢：Oracle 的解決方案直接使用 MAX 聚合函式結合 KEEP 子句和 DENSE_RANK 視窗函式，找出每個部門中最新僱傭日期對應的薪水。

結果呈現

最終查詢結果將呈現每個員工的部門編號、姓名、薪水、僱傭日期以及其部門最新僱員的薪水。

Oracle 資料函式庫中使用 KEEP 子句解決問題

在 Oracle 資料函式庫中，KEEP 子句可以用來對分組或分割槽後的資料進行排序，並選擇第一筆或最後一筆資料進行聚合運算。以下是一個具體的例子，用於取得每個部門中最後聘用的員工的薪水。

使用 MAX OVER 無法直接解決問題

select deptno,
       ename,
       sal,
       hiredate,
       max(sal) over(partition by deptno) latest_sal
from emp
order by 1, 4 desc

上述查詢只會傳回每個部門的最高薪水，而不是最後聘用的員工的薪水。

使用 KEEP 子句解決問題

select deptno,
       ename,
       sal,
       hiredate,
       max(sal) keep(dense_rank last order by hiredate) 
       over(partition by deptno) latest_sal
from emp
order by 1, 4 desc

內容解密：

1. max(sal) keep(dense_rank last order by hiredate)：這部分查詢使用了 KEEP 子句來對每個部門的員工按照聘用日期進行排序，並選擇最後一筆（即最新聘用的員工）的薪水。
2. dense_rank last order by hiredate：這部分指定了按照 hiredate 進行排序，並使用 dense_rank 來對結果進行排名。
3. over(partition by deptno)：這部分將結果按照 deptno 進行分割槽，使得每個部門的資料被獨立處理。

另一種解決方案

select deptno,
       ename,
       sal,
       hiredate,
       max(sal) keep(dense_rank first order by hiredate desc) 
       over(partition by deptno) latest_sal
from emp
order by 1, 4 desc

內容解密：

1. 這種寫法是將排序順序改為降冪（order by hiredate desc），並取第一筆資料，等效於取最新聘用的員工的薪水。
2. 這兩種寫法的結果相同，但實作方式不同。

簡單預測的生成

本文討論如何根據當前資料生成額外的列和行，以表示未來的動作。例如，根據訂單處理的時間，預測驗證和出貨的時間。

使用遞迴 WITH 子句（適用於 DB2、MySQL 和 SQL Server）

with nrows(n) as (
  select 1 from t1 union all
  select n+1 from nrows where n+1 <= 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,
         getdate()+nrows.n as order_date,
         getdate()+nrows.n+2 as process_date
  from nrows
) orders, nrows
order by 1

內容解密：

1. 使用遞迴 WITH 子句生成多行資料。
2. 使用 CASE 表示式根據生成的行數來計算 verified 和 shipped 的日期。

使用 CONNECT BY 子句（適用於 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 as order_date,
         sysdate+nrows.n+2 as process_date
  from nrows
) orders, nrows
order by 1

內容解密：

1. 使用 CONNECT BY 子句生成多行資料。
2. 與遞迴 WITH 子句類別似，使用 CASE 表示式計算額外的列。

資料函式庫查詢技巧：生成簡單預測資料

在處理訂單資料時，常常需要根據訂單日期和處理時間來預測訂單的驗證和出貨日期。本文將介紹如何使用SQL查詢來生成簡單的預測資料。

問題描述

假設我們有一個訂單資料表，需要根據訂單日期和處理時間來預測訂單的驗證和出貨日期。訂單處理時間為2天，需要傳回3行資料，分別代表訂單的不同狀態。

解決方案

Oracle 資料函式庫

首先，我們使用Oracle的CONNECT BY語法來生成3行資料，代表3個訂單。

with nrows as (
  select level n
  from dual
  connect by level <= 3
)
select nrows.n id,
       sysdate + nrows.n order_date,
       sysdate + nrows.n + 2 process_date
from nrows;

結果

Cartesian積

接下來，我們需要生成Cartesian積，以便為每個訂單傳回3行資料。

with nrows as (
  select level n
  from dual
  connect by level <= 3
)
select nrows.n,
       orders.*
from (
  select nrows.n id,
         sysdate + nrows.n order_date,
         sysdate + nrows.n + 2 process_date
  from nrows
) orders, nrows;

結果

生成驗證和出貨日期

最後，我們使用CASE表示式來生成驗證和出貨日期。

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

PostgreSQL可以使用GENERATE_SERIES函式來生成一系列數字。

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);

MySQL

MySQL不支援自動生成行的函式，但可以使用其他方法來實作。

圖表翻譯：

此圖示呈現了訂單處理流程的不同階段，包括訂單日期、處理日期、驗證日期和出貨日期。圖表中每個階段的資料都是根據訂單處理時間和狀態生成的。

@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

圖表翻譯： 此圖示呈現了訂單處理流程的不同階段，包括訂單日期、處理日期、驗證日期和出貨日期。圖表中每個階段的資料都是根據訂單處理時間和狀態生成的。