LoginSignup
14

More than 5 years have passed since last update.

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

Last updated at Posted at 2018-12-23

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:

結論

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

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
14