Window Functions 視窗函數

什麼是 Window Function？

比喻：Window Function 就像站在教室門口看全班 🪟

GROUP BY 把全班打散到各個小房間，你只能看到自己那間的統計。 Window Function 讓你站在門口，既能看到自己的分數，也能看到全班的統計。

Window Function 在不縮減列數的情況下做統計計算。

GROUP BY vs Window Function

-- GROUP BY：每組一列（原始資料被壓縮）
SELECT Department, AVG(Score)
FROM Students
GROUP BY Department;
-- 結果只有 3 列（3 個科系）

-- Window Function：保留每一列，加上統計欄位
SELECT
    Name,
    Department,
    Score,
    AVG(Score) OVER (PARTITION BY Department) AS 系平均
FROM Students;
-- 結果還是 30 列（每個學生一列），但多了系平均欄位

OVER() 基本語法

SELECT
    Name,
    Score,
    AVG(Score) OVER () AS 全班平均,       -- ← OVER() 空的 = 全體
    Score - AVG(Score) OVER () AS 差距    -- ← 每人與平均的差距
FROM Students;

結果：

 Name | Score | 全班平均 | 差距
------+-------+---------+------
 小明 |    85 |   75.0  | 10.0
 小華 |    78 |   75.0  |  3.0
 小美 |    62 |   75.0  | -13.0

PARTITION BY — 分組窗口

SELECT
    Name,
    Department,
    Score,
    AVG(Score) OVER (PARTITION BY Department) AS 系平均,
    MAX(Score) OVER (PARTITION BY Department) AS 系最高分,
    Score - AVG(Score) OVER (PARTITION BY Department) AS 與系平均差距
FROM Students;

PARTITION BY Department  -- 按科系分窗口
                         -- 每個學生都能看到「自己科系」的統計
                         -- 但不會把資料壓縮成一列

ROW_NUMBER、RANK、DENSE_RANK

SELECT
    Name,
    Score,
    ROW_NUMBER() OVER (ORDER BY Score DESC) AS 序號,
    RANK()       OVER (ORDER BY Score DESC) AS 排名,
    DENSE_RANK() OVER (ORDER BY Score DESC) AS 密集排名
FROM Students;

結果（假設有同分情況）：

 Name | Score | 序號 | 排名 | 密集排名
------+-------+------+------+---------
 小明 |    95 |    1 |    1 |       1
 小華 |    90 |    2 |    2 |       2
 小美 |    90 |    3 |    2 |       2   ← RANK 同分同名次
 小剛 |    85 |    4 |    4 |       3   ← RANK 跳 4，DENSE_RANK 接 3

分組排名

-- 每個科系中的排名
SELECT
    Name,
    Department,
    Score,
    RANK() OVER (
        PARTITION BY Department      -- ← 每個科系各自排
        ORDER BY Score DESC          -- ← 分數高到低
    ) AS 系內排名
FROM Students;

-- 只取每科系前 3 名
WITH ranked AS (
    SELECT *, RANK() OVER (PARTITION BY Department ORDER BY Score DESC) AS rk
    FROM Students
)
SELECT * FROM ranked WHERE rk <= 3;

LAG / LEAD — 前後列

SELECT
    Name,
    Score,
    LAG(Score, 1) OVER (ORDER BY Score DESC) AS 前一名分數,
    LEAD(Score, 1) OVER (ORDER BY Score DESC) AS 後一名分數,
    Score - LAG(Score, 1) OVER (ORDER BY Score DESC) AS 與前一名差距
FROM Students;

LAG(Score, 1)   -- 往前看 1 列的 Score（上一名的分數）
LEAD(Score, 1)  -- 往後看 1 列的 Score（下一名的分數）

SUM / AVG 的窗口累計

-- 累計營業額
SELECT
    OrderDate,
    Amount,
    SUM(Amount) OVER (ORDER BY OrderDate) AS 累計營業額,
    AVG(Amount) OVER (ORDER BY OrderDate) AS 滾動平均
FROM Orders;

-- 移動平均（最近 7 天）
SELECT
    OrderDate,
    Amount,
    AVG(Amount) OVER (
        ORDER BY OrderDate
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW  -- ← 往前 6 列到現在
    ) AS 七日移動平均
FROM Orders;