[SQL]被誤會的函數 FIRST_VALUE 及 LAST_VALUE

[SQL]被誤會的函數 FIRST_VALUE 及 LAST_VALUE

最近一個同事想要轉型,因此決定去參加一個頗受好評的訓練中心上課,在休息時間跟他聊聊上課的情況,但似乎講師在部分指令上解釋的有些奇怪,因此決定幫忙整理一下相關的範例,希望解決同事的困擾。

 

此次遇到問題的主要是兩個函數 FIRST_VALUE 及 LAST_VALUE,這兩個函數主要是取得一組資料排序後的第一筆值和最後一筆值,該函數還要配合 OVER ( ) 來指定排序的欄位和資料的區間,因此在這裡我們可以先做一個簡單的 Sample 來測試一下

with data as
(
  select 1 as Id, 'A' as Value
  union all
  select 1 as Id, 'B' as Value
  union all
  select 1 as Id, 'C' as Value
)
  select
    Id, Value, 
    FIRST_VALUE(Value) OVER (PARTITION BY Id ORDER BY Value ) as FirstValue,
    LAST_VALUE (Value) OVER (PARTITION BY Id ORDER BY Value ) as LastValue
    from data    

image

 

從這樣的範例看起來,似乎 FIRST_VALUE 這個函數很正常,但 LAST_VALUE 這個函數怎麼跟我們預期的有點落差,正常說我們希望他要顯示 「C」才對,但怎麼沒有這樣顯示呢 ? 於是同事跟上課講師請教的原因是不知道為什麼,但後面加個處理就正常了,於是按照講師的建議,同事把上面的範例改一下後,果真就得到正確的答案了。

with data as
(
  select 1 as Id, 'A' as Value
  union all
  select 1 as Id, 'B' as Value
  union all
  select 1 as Id, 'C' as Value
)
  select
    Id, Value, 
    FIRST_VALUE(Value) OVER (PARTITION BY Id ORDER BY Value ) as FirstValue,
    LAST_VALUE (Value) OVER (PARTITION BY Id ORDER BY Value RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as LastValue
    from data

image

 

但是為什麼 FIRST_VALUE 不用加入 RANGE 就可以正確,LAST_VALUE 要加入 RANGE 的處理才會正確呢 ? 於是我把這個範例稍微改一下來做解釋

with data as
(
  select 2 as Id, 'E' as Value
  union all
  select 2 as Id, 'C' as Value
  union all
  select 2 as Id, 'D' as Value
  union all
  select 2 as Id, 'A' as Value
)
  select
    Id, Value, 
    FIRST_VALUE(Value) OVER (PARTITION BY Id ORDER BY Value ) as FirstValue1,
    FIRST_VALUE(Value) OVER (PARTITION BY Id ORDER BY Value RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as FirstValue2,
    LAST_VALUE (Value) OVER (PARTITION BY Id ORDER BY Value RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as LastValue
    from data    

 

不知道大家是否可以猜出答案,主要是當使用這類analytical函數時,如果沒有加入 RANGE 的區間,預設會是「RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW」,也就是說排序後的資料我們預設要取出從第一筆到目前我這一筆,所以當 FIRST_VALUE 沒有加入 RANGE 條件的時候,因為會從第一筆取,所以沒有問題。但 LAST_VALUE 沒有加入 RANGE,預設就是從第一筆抓到該筆資料,所以看起來會跟自己同樣的值。換言之,如果加入 RANGE 限制從目前我這一筆資料取到最後一筆的時候,則 FIRST_VALUE 出來的值就會類似 LAST_VALUE 沒有加入 RANGE 的狀況了。因此上述的語法會得到以下的結果。

image

 

當然,RANGE 不只能限制從頭開始,還可以限制要抓的資料筆數,如果要更清楚這個部分的使用,可以參考一下 MSDN 對於 OVER 的介紹,相信會讓大家更為清楚。