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

5471 ワード
Window関数 TreasureData Presto TreasureData テキストリンク

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は正しい。「最後」を取る系がだめ？
  • RANKやLEGなども問題ない。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にしたら正しくない値に戻った
  • でも実際使うときにこれくっつけるの長いし忘れそうなので、DESCとFIRST_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

よしよし

結論

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