Edited at

Window関数のLAST_VALUEの罠にハマった

Window関数便利ですよね。

便利すぎてめっちゃ使ってたら最近あることにハマったので記録しておきます。


ぶつかった問題

LAST_VALUEを使って、ユーザごとの最後のアクションを知りたい。

ということでこんなクエリを出したけど、なぜかLAST_VALUEの値が自分自身になる

SELECT

user_id
,td_url
,time
,LAST_VALUE(td_url) OVER (
PARTITION BY user_id
ORDER BY time
) AS last_url
FROM pageviews

予想

user_id | time   | td_url   | last_url

------- | ------ | -------- | --------
AAA | 00:01 | url_1 | url_3
AAA | 00:02 | url_2 | url_3
AAA | 00:03 | url_3 | url_3
BBB | 00:01 | url_1 | url_1

結果

user_id | time   | td_url   | last_url

------- | ------ | -------- | --------
AAA | 00:01 | url_1 | url_1
AAA | 00:02 | url_2 | url_2
AAA | 00:03 | url_3 | url_3
BBB | 00:01 | url_1 | url_1

・・・?!

いろいろ試行錯誤したところ、



  • FIEST_VALUEを確認しが、それはうまくいく。なぜFIRSTがいけてLASTがいけないのか...

  • 同じようにMAXも値が意図しないものになったけどMINは正しい。「最後」を取る系がだめ?


  • RANKLEGなども問題ない。PARTITIONの設定などはおかしくなさそう


解決策



  • RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWをつける

SELECT

user_id
,LAST_VALUE(td_url) OVER (
PARTITION BY user_id
ORDER BY time
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS last_url
FROM pageviews

答えは、公式リファレンス:3つめの黒ポチ にありました。



  • OVER内のクエリには、デフォルトでRANGE UNBOUNDED PRECEDINGがつく(明示しないとこれになる)

  • これはRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWと同義で、PARTITIONで区切ったうちの「1つ目」から「自分」までしか調べませんよ、という意味


  • FIRST_VALUEが成功したのは、FIRST=「1つ目」さえ取れていれば正しい値が取れるから

  • だからTreasureDataの人もなるべくFIRST_VALUE使ってねって言ってくれている



  • 実際CURRENT ROWにしたら正しくない値に戻った

  • でも実際使うときにこれくっつけるの長いし忘れそうなので、DESCFIRST_VALUEが良さそう

SELECT

user_id
,td_url
,time
,FIRST_VALUE(td_url) OVER (
PARTITION BY user_id
ORDER BY time DESC
) AS last_url
FROM pageviews

結果

user_id | time   | td_url   | last_url

------- | ------ | -------- | --------
AAA | 00:01 | url_1 | url_3
AAA | 00:02 | url_2 | url_3
AAA | 00:03 | url_3 | url_3
BBB | 00:01 | url_1 | url_1

よしよし:grinning:


結論

ドキュメントをよく読もうな