今回もPrestoのWINDOW関数の話です。例によってトレジャーデータで動作を確認しています。参照するドキュメントは直近の数記事と同じこちら。
参考: Window Functions
All Aggregate Functions can be used as window functions by adding the OVER clause. The aggregate function is computed for each row over the rows within the current row’s window frame.
とドキュメントにある通り、Prestoの全ての集約関数はその後ろにOVER()をつけることで、WINDOW関数として動作させることができます。この場合、GROUP BY は要らないくなるので注意してください。
ちなみに、 Aggregate Functions (集約関数) の一覧はこちらのページにあります。
参考: Aggregate Functions
OVER() の中には他のWINDOW 関数と同じように、PARTITION BY と ORDER BY を指定できます。PARTITION BY を指定する場合はもちろん PARTITION BYで指定した列の値でグループ分けして、そのグループ内で集計が走ります。そしてさらに、 ORDER BY の有無によっても、集計される範囲が変わるので注意が必要です。この辺の概念を正しく理解するには、WINDOW関数の Frame という概念(というかオプション)について説明する必要があるので、別記事で紹介しようと思うのですが、今記事Frameを指定しない場合の挙動で説明します。
ORDER BY を指定しない場合、集計範囲は、PARTITION BYで指定されたグループ内の全行になります。PARTITION BYもORDER BYもどちらもしてしてない場合は単純にSELECTされた前レコードが集計範囲です。
ORDER BY を指定する場合、集計範囲は、PARTITION BYで指定されたグループ内のレコードをORDER BYでソートしたものの、「先頭からその行と同じ順位の行まで」になります。
このように言葉で書いてもわかりにくいと思うので、一番基本的な集約関数であるSUM() を使って、実際にやってみます。
WITH sample_table AS(
SELECT
id,
category,
value
FROM
UNNEST(
ARRAY[1, 2, 3, 4, 5, 6],
ARRAY['A', 'A', 'A', 'A', 'B', 'B'],
ARRAY[20, 10, 20, 30, 15, 30]
) AS t(
id,
category,
value
)
)
SELECT
id,
category,
value,
SUM(value) OVER() AS sum_over_1,
SUM(value) OVER(PARTITION BY category) AS sum_over_2,
SUM(value) OVER(PARTITION BY category ORDER BY id) AS sum_over_3,
SUM(value) OVER(PARTITION BY category ORDER BY value) AS sum_over_4
FROM
sample_table
-- 以下出力結果
id, category, value, sum_over_1, sum_over_2, sum_over_3
1, 'A', 20, 125, 80, 20
2, 'A', 10, 125, 80, 30
3, 'A', 20, 125, 80, 50
4, 'A', 30, 125, 80, 80
5, 'B', 15, 125, 45, 15
6, 'B', 30, 125, 45, 45OVERの中に何も指定していないsum_over_1 の列の値は全行等しく6行のvalue 列の値の合計である125になりましたね。
そして、PARTITON BY を指定した sum_over_2 列の値は、category列の値が ‘A’なのか ‘B’なのかによって変わり、それぞれ該当する行のvalue列の値になっています。
そして、注目するのは ORDER BY も指定した sum_over_3 列です。
これは、 id でソートした後のvalue列の累積和が返されています。
どちらの値にも用途はたくさんありそうですね。目的に応じて使い分けていきましょう。
例えば累積和の方は、日々の売り上げから累積売り上げを算出するといったことができますし、全体の集計値の方はもう少し工夫して、そのグループ内のシェアを計算するなどの用途で使えます。要するにSELECT句に次のように入れたりです。(1.0を最初に掛けるのは型をDOUBLEに変換するため。CASTしても良し)
1.0 * value / SUM(value) OVER(PARTITION BY category)
次のように、グループ内の平均との差分を求めるというのも良いです。
value – AVG(value) OVER(PARTITION BY category)
さて、ORDER BYした時の挙動について、もう一点補足です。
先ほど説明の中で、「先頭からその行と同じ順位の行まで」などというまどろこしい表現を使いました。「先頭からその行まで」ではないんですね。
先ほどの例では ORDER BY で id列を指定し、 id列には重複した値がなかったのであまり気にしなくてよかったのですが、値の重複があり、ソートした順位が同順になる行が複数あると少し気をつける必要があります。 ということで、 id ではなくvalue 列でソートしてみたのが次の結果です。
WITH sample_table AS(
SELECT
id,
category,
value
FROM
UNNEST(
ARRAY[1, 2, 3, 4, 5, 6],
ARRAY['A', 'A', 'A', 'A', 'B', 'B'],
ARRAY[20, 10, 20, 30, 15, 30]
) AS t(
id,
category,
value
)
)
SELECT
id,
category,
value,
SUM(value) OVER(PARTITION BY category ORDER BY value) AS sum_over_4
FROM
sample_table
-- 以下出力結果
id, category, value, sum_over_4
2, 'A', 10, 10
1, 'A', 20, 50
3, 'A', 20, 50
4, 'A', 30, 80
5, 'B', 15, 15
6, 'B', 30, 30さて、sum_over_4 列を順番に見ていきましょう。 1行目の結果はいいですね。 valueと同じ10です。そして、4行目の結果は1〜4行目の和で、 10+20+20+30=80 となります。
注目すべきは2行目と3行目です。これがどちらも50になっています。これは、10+20+20の結果です。
3行目が50なのはいいとして、2行目も50なのは少し違和感ありますね。
これが先ほど説明した、「先頭からその行と同じ順位の行まで」を集計するということです。
このような挙動になる理由を正しく理解するには、この記事の最初の方にも書きましたがFrameという概念を理解する必要があります。
別の記事でちゃんと書こうと思うのですが、Frameには行を基準に集計範囲を決めるROWモードと、値を基準に集計範囲を決めるRANGEモードというのがあります。
そして、Frameを指定しなかった場合の挙動は、値を基準とするRANGEモードなのです。
もう少し書くと、Frameを指定しないと、
RANGE UNBOUNDED PRECEDING
と指定するのと同じになり、これが「先頭からその行と同じ順位の行まで」を意味します。
もっと正確にいうと、 UNBOUNDED PRECEDING は、「先頭から」という意味で、「どこまで」は省略されており、「どこまで」を省略した時のデフォルトの挙動が「CURRENT ROW」を指定した場合に等しく、RANGEモードにおけるCURRENT ROWはその行ではなく、「その行と同じ値の行まで」を意味します。ややこしいですね。
とりあえず、Frameについては何も指定しなかった場合にどんな挙動になるのか、をしっかり理解しておくことが重要だと思います。想定と違った動きをしていた場合、SUMの場合は比較的気付きやすいですが平均(AVG)や分散(VAR_SAMP)の場合は見落としがちです。