6記事連続ですが今回もPrestoのWINDOW関数の記事です。例によって僕はトレジャーデータで検証しています。
実は前回のframeの記事でWINDOW関数シリーズは終わりにしようかと思っていたのですが、ドキュメントを見ると、まだ紹介してない関数はあと3つだけになっており、試しに動かしてみたら予想とちょっと違う動きも見られたので切りよくこれらまで紹介することにしました。
ドキュメントはこの記事もここです。
参考: Window Functions
この記事で紹介する関数は次の3つです。- first_value(x)- last_value(x)- nth_value(x, offset)
first_value の説明は、 Returns the first value of the window. とあり、
last_value の説明は、Returns the last value of the window. とどちらも簡潔に書かれています。WINDOW内の最初の値と最後の値を返してくれると読めますね。
そして、 nth_value は offset で指定した数値番目(数値は1以上)の値を返してくれます。
さて、いつもみたいにダミーデータを使ってやってみましょう。
試したコードは下記です。
WITH
sample_table AS(
SELECT
id,
category,
value
FROM
UNNEST(
ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
ARRAY['A', 'A', 'A', 'A', 'A', 'A', 'B', 'B', 'B', 'B'],
ARRAY[20, 10, 20, 30, 5, 50, 15, 30, 20, 40]
) AS t(
id,
category,
value
)
)
SELECT
id,
category,
value,
first_value(value) OVER(PARTITION BY category ORDER BY value) AS first_value_,
last_value(value) OVER(PARTITION BY category ORDER BY value) AS last_value_,
nth_value(value, 4) OVER(PARTITION BY category ORDER BY value) AS nth_value_4
FROM
sample_tableさて、この結果はどうなるでしょうか？3関数とも、OVER(PARTITION BY category ORDER BY value) をつけたので、 categoryの値で、’A’ と ‘B’ に分けられ、 value 列の値でソートされた上で、関数が実行されそうですね。
僕は、 first_value(value) の結果は、category ‘A’と’B’ でそれぞれ一番小さい値である、5と15が返され、 last_value(value) の結果は、それぞれのカテゴリで一番大きい、50と40が返されると予想していました。
そして、nth_value(value, 4) は’A’,’B’それぞれの小さい方から4番目の値である、20と40が帰ってくると思っていました。
ところが実行結果は以下の通りです。
id, category, value, first_value_, last_value_, nth_value_4
5, 'A', 5, 5, 5, NULL
2, 'A', 10, 5, 10, NULL
1, 'A', 20, 5, 20, 20
3, 'A', 20, 5, 20, 20
4, 'A', 30, 5, 30, 20
6, 'A', 50, 5, 50, 20
7, 'B', 15, 15, 15, NULL
9, 'B', 20, 15, 20, NULL
8, 'B', 30, 15, 30, NULL
10, 'B', 40, 15, 40, 40いかがでしょうか。この結果が予想通りだった！って人は(元々仕様を正確に理解していた人以外に)いらっしゃいますか？
first_value_ 列はいいですね。行をcategory列でグループ分けして、valueでソートした後、それぞれのcategoryのグループの最初の値である 5と15 を返してくれています。
問題は、last_value_ 列と、 nth_value_4 列です。
last_value_ の方なんて、同じ行の value 列の値をそのまま返してきていて、関数を使った意味がないです。
なぜこのような挙動になるのかを理解するには、前回の記事で紹介したframeについて知る必要があります。
参考: WINDOW関数のframeのROWSモードについて
WINDOW関数には frameというオプションがあり、何も指定しないとこれが、
RANGE UNBOUNDED PRECEDING
になるのでした。
つまり、PARTITION BY で区切ったグループ内の、最初の行からその行と同じ値の行までを関数の適用範囲とするわけです。これにより、 last_value が指し示す最後の行とは、その行と(ORDER BYで指定した行の値が)同じ値の行まで、の範囲で見た場合の最後の行となり、その結果として同じ行のvalue列の値をそのまま返してきてきたわけです。
ちなみに、 last_value(value) ではなく、 last_value(id) とするとまた微妙に結果が変わります。
category Aで、value列が20の行が二つありますが、2行とも 3が返ってきます。
nth_value_4 の方も原理は同じです。最初の行からその行と同じ値の行まで、の範囲に含まれる行数が4行未満の場合、返す値がないのでNULLになってしまうのです。
未指定の場合 frame は ROWSモードではなくRANGEモードなので、 その行までに4行含まなくても、その行と同じ値の行まで含めて4行確保できれば値を返してくれます。
だから、結果の3行目(id:1の行)は20が返ってきているのです。
ここまでの説明で、 last_value と nth_value って使えない、もしくは使いにくいなと感じられた人も多いかと思います。
これらの関数でイメージ通りに PARTITION BY で区切った範囲の最終行や、 n番目の行の値を素直に出力したい場合は、 合わせて frameを指定してあげることで可能になります。
PARTITION BY で区切った範囲の最初から最後までなので、
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
ですね。
要するに次のように書けば良いです。
(記述量が多くなるので、SELECT句内の該当行だけ書きました)
last_value(VALUE) OVER(
PARTITION BY
category
ORDER BY
VALUE
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS last_value_,
nth_value(VALUE, 4) OVER(
PARTITION BY
category
ORDER BY
VALUE
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS nth_value_4これで出力結果を載せる前に書いてた、イメージしてた通りの結果が得られます。