FIRST_VALUE及LAST_VALUE分析函數的使用

在一組已排序資料集中，FIRST_VALUE函數取得視窗框架(WINDOW)中第一筆資料，依執運算式定義計算並傳回結果；LAST_VALUE函數為傳回視窗框架中最後一筆記錄的運算結果。MSSQL及ORACLE函數語法如下，請注意，ORDER BY 子句對MSSQL為必要參數，ORACLE則否。但ORACLE額外可指定RESPECT NULLS（預設值）及IGNORE NULLS，後續將以休假日引用前一營業日結算匯率為例說明。本文將以班上成績排名為例。

MSSQL 2012以上

FIRST_VALUE | LAST_VALUE ( expression )   OVER ( [PARTITION BY ] ORDER BY <order_list>  [ rows_range_clause ] )

ORACLE

FIRST_VALUE | LAST_VALUE ( expression [ RESPECT NULLS | IGNORE NULLS ] ) OVER ( [ PARTITION BY ] [ ORDER BY [windowing_clause]] )

ORACLE /MSSQL的SQL語法與執行結果相同，如下。但ORACLE的SELECT指令中需要包含FROM子句，DUAL儘包含DUMMY一個欄位的虛擬資料表，為簡化範例中語法，煩請MSSQL先執行以建立此資料庫。

SELECT CAST('X' AS varchar(1)) DUMMY        INTO DUAL

DB	SQL
ORACLE	SELECT No   , Name   , Score   , Class   , FIRST_VALUE(Name) OVER(PARTITION BY Class ORDER BY Score DESC)     "First_One"   , FIRST_VALUE(Name) OVER(PARTITION BY Class ORDER BY Score)           "Last_One"   , LAST_VALUE(Name)  OVER(PARTITION BY Class ORDER BY Score DESC)     "Last_One(X)"   , LAST_VALUE(Name)  OVER(PARTITION BY Class ORDER BY Score DESC                            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )                                                        "Last_One"   ---ORACLE                , NTH_VALUE(Name, 2) OVER(PARTITION BY Class ORDER BY Score DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)  "No.2"    FROM     (     SELECT 1 No, '小夫' Name, 90 Score, '多拉班' Class FROM DUAL UNION ALL     SELECT 2,    '大雄',      30,       '多拉班'       FROM DUAL UNION ALL     SELECT 3,    '靜香',      90,       '多拉班'       FROM DUAL UNION ALL     SELECT 4,    '小杉',     100,       '多拉班'       FROM DUAL UNION ALL         SELECT 5,    '胖虎',      50,       '多拉班'       FROM DUAL UNION ALL     SELECT 1,  '小丸子',      70,       '櫻桃班'       FROM DUAL UNION ALL     SELECT 2,    '小玉',      90,       '櫻桃班'       FROM DUAL UNION ALL     SELECT 3,    '花輪',      80,       '櫻桃班'       FROM DUAL UNION ALL     SELECT 4,    '永澤',      60,       '櫻桃班'       FROM DUAL   ) A
MSSQL	SELECT No   , Name   , Score   , Class   , FIRST_VALUE(Name) OVER(PARTITION BY Class ORDER BY Score DESC)     "First_One"   , FIRST_VALUE(Name) OVER(PARTITION BY Class ORDER BY Score)           "Last_One"   , LAST_VALUE(Name)  OVER(PARTITION BY Class ORDER BY Score DESC)      "Last_One(X)"   , LAST_VALUE(Name)  OVER(PARTITION BY Class ORDER BY Score DESC                            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )                                                              "Last_One"   ---ORACLE                --, NTH_VALUE(Name, 2) --OVER(PARTITION BY Class --ORDER BY Score DESC --ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) "No.2"    FROM     (     SELECT 1 No, '小夫' Name, 90 Score, '多拉班' Class FROM DUAL UNION ALL     SELECT 2,    '大雄',      30,       '多拉班'       FROM DUAL UNION ALL     SELECT 3,    '靜香',      90,       '多拉班'       FROM DUAL UNION ALL     SELECT 4,    '小杉',     100,       '多拉班'       FROM DUAL UNION ALL         SELECT 5,    '胖虎',      50,       '多拉班'       FROM DUAL UNION ALL     SELECT 1,  '小丸子',      70,       '櫻桃班'       FROM DUAL UNION ALL     SELECT 2,    '小玉',      90,       '櫻桃班'       FROM DUAL UNION ALL     SELECT 3,    '花輪',      80,       '櫻桃班'       FROM DUAL UNION ALL     SELECT 4,    '永澤',      60,       '櫻桃班'       FROM DUAL   ) A

將使用的ORDER BY子句及WINDOW子句及執行結果說明，整理如下表。如使用FIRST_VALUE取得第一、最後一名均正確；但使用LAST_VALUE時，未設定視窗框架（WINDOW）之執行結果則不正確，分析原因視窗框架預設值所致，預設為RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (自資料分割中的第一筆資料列(Row)為起始點，至目前資料列為結束點)，當使用LAST_VALUE函數取得最後一筆資料時，則將取目前資料列，如上圖所示資料排列及實際結果均可得知。由於可由排序方向即清楚定義第一筆資料內容為第一名或最後一名，為避免誤用可僅採用FIRST_VALUE函數。此範例的第二名為二人同分並列，但受限於僅傳回一筆，而無法確知何者將被挑選，使用時請多注意。

#	查詢	函數	ORDER BY	WINDOW	正確
1	第一名	FIRST_VALUE	Score DESC		Y
2	最後一名	FIRST_VALUE	Score		Y
3	最後一名	LAST_VALUE	Score DESC	未設定(使用預設值) RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW	N 預設值
4	最後一名	LAST_VALUE	Score DESC	ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING	Y
5	第二名	NTH_VALUE	Score DESC	ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING (如未指定，則結果不正確)	Y

視窗框架（windowing_clause）	說明
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW	視窗框架自資料分割中的第一筆資料列(Row)為起始點，至目前資料列為結束點（預設）。最後1筆依目前資料列變更而改變。
RANGE BETWEEN CURRENT ROW AND UNBOUNDED PRECEDING	視窗框架以目前資料列（Row）為起始點，而以資料分割的最後一筆資料列作為結束點。第1筆資料列依目前資料列的變更而改變。
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING	資料分割中所有資料列（Row）均包含於視窗框架中。

由FIRST_VALUE、LAST_VALUE函數取得排序資集中第一筆或最後一筆資料，但如欲取得其他排名資料時，則需採用其他作法，但ORACLE於11gR2版本提供取得指定名次的NTH_VALUE函數，為原有FIRST_VALUE、LAST_VALUE之通用函數，可更為直接取得任意名次，將於後續文章中說明。