SQL 視窗分析

先預測結果，再看輸出。

SQL 範例會標明資料表與引擎差異。題庫請直接提交 SELECT 或 WITH 查詢，依題目保留欄位名稱與排序規則。建表、寫入資料與交易範例供本機學習；題庫查詢環境不允許修改資料。

01｜先理解資料與結果

視窗函式為每原列附加分析值，不會像 GROUP BY 將同組合成一列。PARTITION BY zone 在各區域內獨立計算，NULL zone 同組。OVER 的 ORDER BY 決定分析順序；最後 SELECT 的 ORDER BY 決定顯示順序，兩者是不同規則。

02｜建立正確查詢規則

ROW_NUMBER 每列不同序號，須加唯一鍵固定平手先後。RANK 對同值同名次且跳號，DENSE_RANK 不跳號；若排名值是 reading，不應再把 id 放入排名鍵造成平手消失。ROWS 明確依列位置選範圍，避免預設 RANGE 把同排序值的其他列一起納入。LAST_VALUE 若要整組最後值，需指定 UNBOUNDED FOLLOWING。

簡單範例｜全體序位

按 id 遞增給每列 1 起的連續序位。

先閱讀下方 fixture 的表名、欄位與缺值，再預測輸出。每個引擎使用自己的完整查詢；結果的欄名及列順序也要相同。

範例資料表（JSON）

{"tables": [{"name": "readings", "columns": [{"name": "id", "type": "INTEGER", "nullable": false}, {"name": "zone", "type": "TEXT", "nullable": true}, {"name": "reading", "type": "INTEGER", "nullable": true}, {"name": "weight", "type": "INTEGER", "nullable": true}, {"name": "flag", "type": "INTEGER", "nullable": true}, {"name": "day", "type": "INTEGER", "nullable": false}, {"name": "label", "type": "TEXT", "nullable": true}], "rows": [[10, "west", 0, null, 0, 15, "delta"], [6, null, null, 4, 1, 4, "gamma"], [12, "east", 1, 0, null, 30, "beta"], [3, "north", 5, 2, 1, 2, "alpha"], [9, "", 3, 3, 1, 8, ""], [1, null, null, null, null, 1, null], [11, "south", 9, 2, 1, 20, "atlas"], [8, "north", 9, 1, null, 8, "omega"], [2, "north", 0, 0, 0, 1, ""], [5, "east", -5, 5, 0, 3, "beta"], [4, "west", 5, -2, 1, 2, "ab"], [7, "south", -2, 0, 0, 4, "a"]]}]}

SQLite

SELECT id AS id, value AS value
FROM (
SELECT id AS id, ROW_NUMBER() OVER (ORDER BY id) AS value
FROM readings
) query_result
ORDER BY id ASC;

PostgreSQL

SELECT id AS id, value AS value
FROM (
SELECT id AS id, ROW_NUMBER() OVER (ORDER BY id) AS value
FROM readings
) query_result
ORDER BY id ASC;

MySQL

SELECT id AS id, value AS value
FROM (
SELECT id AS id, ROW_NUMBER() OVER (ORDER BY id) AS value
FROM readings
) query_result
ORDER BY id ASC;

預期查詢結果

id | value
1 | 1
2 | 2
3 | 3
4 | 4
5 | 5
6 | 6
7 | 7
8 | 8
9 | 9
10 | 10
11 | 11
12 | 12

中等範例｜累計平均

按 id 遞增，計算首列至目前列的非 NULL reading 平均，尚無有效值為 NULL。

先閱讀下方 fixture 的表名、欄位與缺值，再預測輸出。每個引擎使用自己的完整查詢；結果的欄名及列順序也要相同。

範例資料表（JSON）

{"tables": [{"name": "readings", "columns": [{"name": "id", "type": "INTEGER", "nullable": false}, {"name": "zone", "type": "TEXT", "nullable": true}, {"name": "reading", "type": "INTEGER", "nullable": true}, {"name": "weight", "type": "INTEGER", "nullable": true}, {"name": "flag", "type": "INTEGER", "nullable": true}, {"name": "day", "type": "INTEGER", "nullable": false}, {"name": "label", "type": "TEXT", "nullable": true}], "rows": [[10, "west", 0, null, 0, 15, "delta"], [6, null, null, 4, 1, 4, "gamma"], [12, "east", 1, 0, null, 30, "beta"], [3, "north", 5, 2, 1, 2, "alpha"], [9, "", 3, 3, 1, 8, ""], [1, null, null, null, null, 1, null], [11, "south", 9, 2, 1, 20, "atlas"], [8, "north", 9, 1, null, 8, "omega"], [2, "north", 0, 0, 0, 1, ""], [5, "east", -5, 5, 0, 3, "beta"], [4, "west", 5, -2, 1, 2, "ab"], [7, "south", -2, 0, 0, 4, "a"]]}]}

SQLite

SELECT id AS id, value AS value
FROM (
SELECT id AS id, AVG(reading) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS value
FROM readings
) query_result
ORDER BY id ASC;

PostgreSQL

SELECT id AS id, value AS value
FROM (
SELECT id AS id, AVG(reading) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS value
FROM readings
) query_result
ORDER BY id ASC;

MySQL

SELECT id AS id, value AS value
FROM (
SELECT id AS id, AVG(reading) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS value
FROM readings
) query_result
ORDER BY id ASC;

預期查詢結果

id | value
1 | NULL
2 | 0
3 | 2.5
4 | 3.333333333
5 | 1.25
6 | 1.25
7 | 0.6
8 | 2
9 | 2.142857143
10 | 1.875
11 | 2.666666667
12 | 2.5

困難範例｜後三筆數量

按 id 遞增計算目前列及後兩列的實際列數，末尾只算存在的列，NULL reading 也計入。

先閱讀下方 fixture 的表名、欄位與缺值，再預測輸出。每個引擎使用自己的完整查詢；結果的欄名及列順序也要相同。

範例資料表（JSON）

{"tables": [{"name": "readings", "columns": [{"name": "id", "type": "INTEGER", "nullable": false}, {"name": "zone", "type": "TEXT", "nullable": true}, {"name": "reading", "type": "INTEGER", "nullable": true}, {"name": "weight", "type": "INTEGER", "nullable": true}, {"name": "flag", "type": "INTEGER", "nullable": true}, {"name": "day", "type": "INTEGER", "nullable": false}, {"name": "label", "type": "TEXT", "nullable": true}], "rows": [[10, "west", 0, null, 0, 15, "delta"], [6, null, null, 4, 1, 4, "gamma"], [12, "east", 1, 0, null, 30, "beta"], [3, "north", 5, 2, 1, 2, "alpha"], [9, "", 3, 3, 1, 8, ""], [1, null, null, null, null, 1, null], [11, "south", 9, 2, 1, 20, "atlas"], [8, "north", 9, 1, null, 8, "omega"], [2, "north", 0, 0, 0, 1, ""], [5, "east", -5, 5, 0, 3, "beta"], [4, "west", 5, -2, 1, 2, "ab"], [7, "south", -2, 0, 0, 4, "a"]]}]}

SQLite

SELECT id AS id, value AS value
FROM (
SELECT id AS id, COUNT(*) OVER (ORDER BY id ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS value
FROM readings
) query_result
ORDER BY id ASC;

PostgreSQL

SELECT id AS id, value AS value
FROM (
SELECT id AS id, COUNT(*) OVER (ORDER BY id ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS value
FROM readings
) query_result
ORDER BY id ASC;

MySQL

SELECT id AS id, value AS value
FROM (
SELECT id AS id, COUNT(*) OVER (ORDER BY id ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS value
FROM readings
) query_result
ORDER BY id ASC;

預期查詢結果

id | value
1 | 3
2 | 3
3 | 3
4 | 3
5 | 3
6 | 3
7 | 3
8 | 3
9 | 3
10 | 3
11 | 2
12 | 1

WHEN SOMETHING GOES WRONG

遇到錯誤，先檢查這裡

排名鍵加入不該有的 id

只有要求不同序號或選唯一一列時才加 id；共享名次保留 reading 平手。

將視窗結果直接放 WHERE

先用 CTE／子查詢計算序號，再在外層篩選。

未指定 LAST_VALUE 完整範圍

若要求整組末列，範圍需到 UNBOUNDED FOLLOWING。

PAUSE AND TRY

先不要急著看別人的寫法

對 reading=[9,9,5] 分別寫 ROW_NUMBER、RANK、DENSE_RANK。
首列 LAG 與末列 LEAD 應為什麼？
對相同 day 的多列，比較 ROWS 與預設 RANGE 的累計差異。

先改一列資料，預測查詢結果，再切換引擎確認語法與排序。

把這幾件事帶走

分辨 ROW_NUMBER／RANK／DENSE_RANK
使用 LAG／LEAD 查相鄰列
指定 ROWS 完成累計及移動聚合

FROM READING TO PRACTICE

用三道免費題，練習本章觀念

從簡單題確認本章語法，再挑戰中等與困難題。三題都來自本章的免費 SQL 題庫，SQLite、PostgreSQL、MySQL 都可作答。

簡單

延伸閱讀：資料庫官方文件

需要查閱更完整的規則時，可參考以下官方文件。

01｜先理解資料與結果

02｜建立正確查詢規則

簡單範例｜全體序位

中等範例｜累計平均

困難範例｜後三筆數量

遇到錯誤，先檢查這裡

先不要急著看別人的寫法

把這幾件事帶走

用三道免費題，練習本章觀念

251. 全體序位

259. 累計平均

268. 後三筆數量

延伸閱讀：資料庫官方文件