SQL Server 視窗函數 FIRST_VALUE 與 LAST_VALUE 實戰解析

不再需要 Self-Join：FIRST_VALUE / LAST_VALUE 取得分組首尾值

SQL Server 2012 引入的視窗函數 FIRST_VALUE、LAST_VALUE 讓我們能在單一掃描中取得分區內第一筆與最後一筆的值，大幅簡化「首次/最近一次購買」、「每日首筆/尾筆交易」等查詢。

語法回顧

FIRST_VALUE(scalar_expression) OVER ([PARTITION BY ...] ORDER BY ...)
LAST_VALUE(scalar_expression)  OVER ([PARTITION BY ...] ORDER BY ...)

• 預設視窗框架：RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。
• 關鍵陷阱：LAST_VALUE 預設只看到「目前列至開頭」，不會看到後面的列，必須顯式指定 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 才能取得真正的分區最後一筆。

範例資料與查詢

WITH test AS (
    SELECT N'樂可樂可的部落格' AS name, 10 AS score UNION ALL
    SELECT N'樂可樂可的部落格', 15 UNION ALL
    SELECT N'樂可樂可的部落格', 20 UNION ALL
    SELECT N'微軟認證', 30 UNION ALL
    SELECT N'微軟認證', 40 UNION ALL
    SELECT N'微軟認證', 40
)
SELECT
    name, score,
    FIRST_VALUE(score) OVER (PARTITION BY name ORDER BY score) AS first_score,
    LAST_VALUE(score)  OVER (
        PARTITION BY name ORDER BY score
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_score
FROM test;

結果解析

• FIRST_VALUE：依 ORDER BY score 取分區最小值（10、30）。
• LAST_VALUE：加上完整視窗框架後，取分區最大值（20、40）。

常見應用場景

1. 每位用戶首次/最近登入時間：PARTITION BY user_id ORDER BY login_time。
2. 每日股票開盤價/收盤價：PARTITION BY stock_id, trade_date ORDER BY trade_time。
3. 訂單狀態流轉：取建單狀態與最終狀態對比。

對比：LEAD/LAG vs FIRST/LAST_VALUE

總結

FIRST_VALUE 開箱即用；LAST_VALUE **務必加上 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING**，否則只會回傳目前列自身值。掌握這兩個函數，配合 PARTITION BY，能以極簡潔的 SQL 解決「分組首尾」類需求。