DuckDB Window Functions 實戰：排名、移動平均與分組分析

Python 學習 - 本文屬於一個選集。

§ 120: 本文

一. 前言：Group By 會把資料壓扁，Window 不會
#

做分析時，我們常先想到 GROUP BY：

SELECT city, sum(amount) AS revenue
FROM sales
GROUP BY city;

這能算出每個城市的營收，代價是明細列消失了。如果你還想保留訂單日期、商品與原始金額，同時回答下面這些問題呢？

這筆訂單在城市內排第幾？
和上一筆相比，金額增加多少？
截至今天的累積營收是多少？
最近三筆或七天的移動平均是多少？
每個城市只留下營收最高的兩筆，怎麼寫最清楚？

這就是 Window Functions（視窗函式） 的戰場。

它不會把多列聚合成一列，而是替每一列加上「從相關資料算出的新欄位」。 DuckDB 很適合做這類本機分析：SQL 表達力完整，又能直接從 Python、CSV 或 Parquet 接資料。

如果你還不熟 DuckDB 的基本查詢，先看 Python DuckDB 實戰；偏好 DataFrame expression 的讀者，也可以對照 Polars 入門的 over()。

今天拍拍君不重講檔案匯入，而是把分析 SQL 裡最容易寫對一半的 Window Functions 拆清楚。

二. 準備資料：一張小表練完所有概念
#

建立專案並安裝 DuckDB：

mkdir duckdb-window-lab
cd duckdb-window-lab
uv init
uv add duckdb

先用 Python 建立記憶體資料庫：

import duckdb

con = duckdb.connect()

con.sql("""
CREATE TABLE sales (
    order_id INTEGER,
    city VARCHAR,
    sold_at DATE,
    product VARCHAR,
    amount DECIMAL(10, 2)
)
""")

con.sql("""
INSERT INTO sales VALUES
    (1, 'Taipei',  DATE '2026-09-01', 'keyboard', 2400.00),
    (2, 'Taipei',  DATE '2026-09-01', 'mouse',     900.00),
    (3, 'Taipei',  DATE '2026-09-03', 'monitor',  5200.00),
    (4, 'Taipei',  DATE '2026-09-06', 'coffee',    180.00),
    (5, 'Tainan',  DATE '2026-09-01', 'keyboard', 2100.00),
    (6, 'Tainan',  DATE '2026-09-02', 'notebook',  320.00),
    (7, 'Tainan',  DATE '2026-09-04', 'monitor',  4800.00),
    (8, 'Tainan',  DATE '2026-09-04', 'mouse',     780.00)
""")

這份資料刻意放了同日訂單。等一下談排名與 frame 時，它們會讓「同分資料」的差異浮出來。

三. 核心心智模型：Partition、Order、Frame
#

Window Functions 最常見的形狀是：

function(expression) OVER (
    PARTITION BY group_column
    ORDER BY sort_column
    ROWS BETWEEN ... AND ...
)

三個部分分工不同：

元件	問題	作用
`PARTITION BY`	跟誰一起算？	把資料切成互不相干的分組
`ORDER BY`	誰先誰後？	決定排名、前後列與累積順序
frame	每列要看多遠？	限制聚合函式可見的鄰近範圍

先算每筆訂單占城市營收的比例：

SELECT
    order_id,
    city,
    amount,
    sum(amount) OVER (PARTITION BY city) AS city_total,
    round(
        100 * amount / sum(amount) OVER (PARTITION BY city),
        1
    ) AS city_share_pct
FROM sales
ORDER BY city, order_id;

這裡沒有 ORDER BY，因為城市總額與列順序無關。每一列仍然保留，只是多出 city_total 與比例。

若拿掉 PARTITION BY city，整張表會變成一個 partition，分母就改成所有城市的總營收。

四. 排名：ROW_NUMBER、RANK、DENSE_RANK 怎麼選？
#

三個排名函式長得很像，但遇到同分時結果不同：

SELECT
    city,
    order_id,
    product,
    amount,
    row_number() OVER (
        PARTITION BY city
        ORDER BY amount DESC, order_id
    ) AS row_no,
    rank() OVER (
        PARTITION BY city
        ORDER BY amount DESC
    ) AS rank_no,
    dense_rank() OVER (
        PARTITION BY city
        ORDER BY amount DESC
    ) AS dense_rank_no
FROM sales
ORDER BY city, amount DESC, order_id;

row_number()：每列一定拿到不同編號。
rank()：同分同名次，下一名會跳號，例如 1, 2, 2, 4。
dense_rank()：同分同名次，但不跳號，例如 1, 2, 2, 3。

row_number() 的排序最好加上唯一欄位當 tie-breaker。若只寫 ORDER BY amount DESC，同金額訂單之間沒有穩定順序，重跑時不該依賴誰先出現。

選擇原則很簡單：

需求	函式
每組固定取 N 列	`row_number()`
比賽名次，同分後跳號	`rank()`
價格層級、評等層級	`dense_rank()`

五. LAG 與 LEAD：和前後一筆比較
#

lag() 取前一列，lead() 取後一列。它們很適合算變化量、間隔時間與狀態轉換：

SELECT
    city,
    sold_at,
    order_id,
    amount,
    lag(amount) OVER w AS previous_amount,
    amount - lag(amount) OVER w AS amount_change,
    lead(sold_at) OVER w AS next_sold_at
FROM sales
WINDOW w AS (
    PARTITION BY city
    ORDER BY sold_at, order_id
)
ORDER BY city, sold_at, order_id;

每個城市的第一列沒有前一筆，因此 previous_amount 與 amount_change 會是 NULL。

需要預設值時，可以傳入 offset 與 default：

SELECT
    city,
    order_id,
    amount,
    lag(amount, 1, 0) OVER (
        PARTITION BY city
        ORDER BY sold_at, order_id
    ) AS previous_or_zero
FROM sales;

不過，0 不一定代表「沒有上一筆」。分析資料時，保留 NULL 通常比較誠實；只有業務定義真的把起點視為零，才使用預設值。

六. 累積值：不要把預設 frame 當空氣
#

累積營收可以這樣寫：

SELECT
    city,
    sold_at,
    order_id,
    amount,
    sum(amount) OVER (
        PARTITION BY city
        ORDER BY sold_at, order_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM sales
ORDER BY city, sold_at, order_id;

UNBOUNDED PRECEDING 表示從 partition 起點開始，CURRENT ROW 表示算到目前列。

有 ORDER BY 卻省略 frame 時，預設行為涉及 RANGE 與 peer rows。同一個排序值的列可能一起進入 frame，結果和「一列一列往前加」不一樣。

因此，想表達逐列累積時，拍拍君建議明寫：

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

這不只是風格問題，而是把同日、同分、同時間戳的行為寫進查詢契約。

七. 移動平均：ROWS、RANGE、GROUPS 的差別
#

「最近三筆平均」適合 ROWS：

SELECT
    city,
    sold_at,
    order_id,
    amount,
    round(avg(amount) OVER (
        PARTITION BY city
        ORDER BY sold_at, order_id
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 2) AS avg_last_3_orders
FROM sales
ORDER BY city, sold_at, order_id;

目前列加前兩列，最多三筆。 partition 開頭不足三筆時，frame 會自然裁切，不需要另外補資料。

但「最近三天」不是三筆。一天可能有十筆訂單，也可能完全沒資料，這時應該使用 RANGE：

SELECT
    city,
    sold_at,
    order_id,
    amount,
    round(avg(amount) OVER (
        PARTITION BY city
        ORDER BY sold_at
        RANGE BETWEEN INTERVAL 2 DAYS PRECEDING AND CURRENT ROW
    ), 2) AS avg_last_3_calendar_days
FROM sales
ORDER BY city, sold_at, order_id;

兩者回答的是不同問題：

ROWS：依實體列數移動。
RANGE：依排序值的距離移動，例如日期或數值區間。
GROUPS：依相同排序值形成的 peer group 數量移動。

若每個日期有多筆資料，又想以「最近三個有交易的日期」為單位，可以使用：

GROUPS BETWEEN 2 PRECEDING AND CURRENT ROW

先把自然語言需求問清楚：最近三筆、三個日曆日，還是三個有資料的日期？選錯 frame，SQL 可以執行，答案卻會悄悄變成另一件事。

八. 用命名 WINDOW 減少重複
#

同一份排序常會同時計算累積值、平均、最小與最大值。重複貼上 OVER (...) 不只難看，也容易漏改其中一段。

SELECT
    city,
    sold_at,
    order_id,
    amount,
    sum(amount) OVER recent AS rolling_sum,
    round(avg(amount) OVER recent, 2) AS rolling_avg,
    min(amount) OVER recent AS rolling_min,
    max(amount) OVER recent AS rolling_max
FROM sales
WINDOW recent AS (
    PARTITION BY city
    ORDER BY sold_at, order_id
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)
ORDER BY city, sold_at, order_id;

命名 window 讓四個指標共用同一份 partition、ordering 與 frame 定義。 DuckDB 也有機會共享資料布局，避免重複工作。

如果指標的 frame 不同，請分開命名，例如 running、last_3_rows、last_7_days，不要靠註解猜。

九. QUALIFY：每組 Top N 不必包子查詢
#

Window Functions 的結果在 WHERE 之後才計算，因此不能這樣寫：

-- 錯誤示範：WHERE 看不到 row_number() 的結果
SELECT
    *,
    row_number() OVER (PARTITION BY city ORDER BY amount DESC) AS rn
FROM sales
WHERE rn <= 2;

傳統寫法要包一層 CTE 或子查詢。 DuckDB 提供 QUALIFY，可以直接過濾 window 結果：

SELECT
    city,
    order_id,
    product,
    amount,
    row_number() OVER (
        PARTITION BY city
        ORDER BY amount DESC, order_id
    ) AS rn
FROM sales
QUALIFY rn <= 2
ORDER BY city, rn;

可以把常見 SQL 過濾順序記成：

WHERE    -> 過濾原始列
HAVING   -> 過濾 GROUP BY 聚合結果
QUALIFY  -> 過濾 Window Functions 結果

要注意：先用 WHERE 刪掉的列，不會再出現在 window 的 partition 裡。如果要先對完整歷史算排名，再只顯示最近日期，通常要先在 CTE 算 window，外層才過濾日期。

十. NULL 與 FIRST_VALUE：預設行為要講清楚
#

一般 window 函式預設尊重 NULL。例如感測值缺漏時，lag(value) 可能取到 NULL；若需求是找上一個非空值，可使用 IGNORE NULLS：

SELECT
    measured_at,
    value,
    last_value(value IGNORE NULLS) OVER (
        ORDER BY measured_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS last_known_value
FROM measurements
ORDER BY measured_at;

相對地，sum()、avg() 等 aggregate window 通常會忽略輸入中的 NULL。兩類函式的 null semantics 不完全相同，不能只看結果「好像合理」。

補值前也要分清楚：

last_value(... IGNORE NULLS) 是向前帶值。
fill(value ORDER BY measured_at) 是線性插值。
coalesce(value, 0) 是把缺值直接當零。

它們代表三種不同假設，不能互換。

十一. 從 Python 執行並保留型別
#

正式程式可以把 SQL 放進字串，透過 connection 執行：

query = """
SELECT
    city,
    sold_at,
    order_id,
    amount,
    sum(amount) OVER (
        PARTITION BY city
        ORDER BY sold_at, order_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total,
    row_number() OVER (
        PARTITION BY city
        ORDER BY amount DESC, order_id
    ) AS revenue_rank
FROM sales
ORDER BY city, sold_at, order_id
"""

result = con.sql(query)
print(result)

df = result.df()
print(df.dtypes)

若來源是 Parquet，可以把 FROM sales 換成 read_parquet(?)，再用參數傳路徑，不要把外部輸入直接拼進 SQL。

分析邏輯複雜時，拍拍君習慣把每個中間階段命名：

WITH daily AS (
    SELECT city, sold_at, sum(amount) AS daily_revenue
    FROM sales
    GROUP BY city, sold_at
), metrics AS (
    SELECT
        *,
        avg(daily_revenue) OVER (
            PARTITION BY city
            ORDER BY sold_at
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ) AS moving_avg_7_active_days
    FROM daily
)
SELECT *
FROM metrics
ORDER BY city, sold_at;

先聚合到「一天一列」，再做七個活躍日的移動平均，語義會比直接在訂單列上計算清楚很多。

十二. 效能與記憶體：Window 是 Blocking Operator
#

Window Functions 通常要先取得整個輸入、分組並排序，才算得出結果。 DuckDB 官方文件將它們列為 blocking operators，也是 SQL 中較吃記憶體的操作之一。

實務上可以這樣控制成本：

先用 WHERE 限定真的需要的日期與資料範圍。
只選必要欄位，不要習慣性 SELECT *。
能先聚合成日、週或使用者粒度，就不要在原始事件層做所有 window。
相同規格用命名 WINDOW，讓意圖與共享機會都更清楚。
排序欄位盡量精簡，但必須保留決定性 tie-breaker。

用 EXPLAIN 看計畫，不執行查詢：

EXPLAIN
SELECT
    city,
    sum(amount) OVER (PARTITION BY city ORDER BY sold_at)
FROM sales;

需要實測時再用：

EXPLAIN ANALYZE
SELECT
    city,
    avg(amount) OVER (
        PARTITION BY city
        ORDER BY sold_at
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    )
FROM sales;

EXPLAIN ANALYZE 真的會執行查詢。測 production 級資料前，先確認成本、輸出位置與資料範圍。

十三. 常見錯誤清單
#

1. Window 裡完全沒寫 ORDER BY
#

總額比例不一定需要順序；排名、lag()、累積值通常需要。沒有順序時，不要假設檔案排列或插入順序會自動成為契約。

2. ORDER BY 不夠唯一
#

同分資料若需要逐列編號，加入 order_id 等穩定 tie-breaker。

3. 把三筆當三天
#

ROWS 2 PRECEDING 看的是列數；日曆區間要用 RANGE INTERVAL，或先聚合到每日粒度。

4. 用 WHERE 過濾 window 結果
#

使用 QUALIFY，或先在 CTE 計算再由外層 WHERE 過濾。

5. 省略 frame 卻依賴逐列累積
#

碰到相同排序值時，peer rows 可能改變結果。把 ROWS BETWEEN ... 明寫出來。

6. 把 NULL 自動當成 0
#

缺資料、真正的零與尚未發生是三件事。先定義業務語義，再決定 IGNORE NULLS、fill() 或 coalesce()。

十四. 最小測試：用邊界資料驗證 SQL
#

Window 查詢最怕在正常資料看不出問題。測試資料至少要包含：

partition 只有一列
相同排序值與相同排名值
日期中間有缺口
NULL 值
partition 開頭與結尾

可以直接用 Python assertion 做 smoke test：

rows = con.sql("""
    SELECT
        order_id,
        sum(amount) OVER (
            PARTITION BY city
            ORDER BY sold_at, order_id
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS running_total
    FROM sales
    WHERE city = 'Taipei'
    ORDER BY sold_at, order_id
""").fetchall()

assert rows[0] == (1, 2400.00)
assert rows[1] == (2, 3300.00)
assert rows[-1] == (4, 8680.00)