window関数が苦手だという話

「累計ならEXCELでやればいいでしょ」「GROUP BYでどうにかなるよね? ならない? なれよ」が口癖だった(わけではない)ので正直window関数にはなるべく触りたくなかった。わからないことはないけどいちいち調べないとダメな程度の知識量で、その「調べる」が面倒くさいのでやっぱり使わなかった。

とはいえ集計に必要なタイミングというのは往々にして訪れる。たとえば「その日までのユーザー累計購入額で分布を作って欲しい」とか。これを出すには一度「ユーザーごと、デイリーの累計購入額」を出すテーブル(サブクエリ)を作る必要があり、そのために長々クエリを組み立てていくのも面倒だしパフォーマンスも不安だというので、結局はwindow関数が近道ということに思い至る。

毎回毎回いやだーいやだーと思いながら調べるのもそれはそれで嫌なので、一度整理することにした。のが以下である。

そもそもwindow関数って何よ

「重複レコードを省かないグループ化」とは上司談である。「レコードをそのままに保ったグループ化」でも良いかもしれない。GROUP BYでまとめる場合は重複内容が1レコードにまとめられてしまうが、window関数のPARTITION BYでまとめる場合はレコードを元の件数のままで残してくれる。

それをすると何が便利か、というと、「その日の購入額を月平均購入額と比較したい場合」だとか、「累計を行いたい場合」だとかに対応できる。

覚えておいて損はない。やろう。

window関数の構成

といって文章で書けることは少ない。

SELECT
    SUM(target_column) OVER (PARTITION BY hoge ORDER BY piyo)
-- ①集計関数②OVER③PARTITION BY 仕切のカラム④ORDER BY 処理順のカラム

基本的にこの形。RANK()だと引数を取らずにPARTITION BYだけでよかったり、AVG()だと処理順は関係ないのでORDER BYが抜けたりする。

帰ってくるテーブルのレコード数が元のレコード数と同じため、ほぼレコード単位で処理の順番を指定できること、が大きい。「この人がこの日までに買った一番高い商品はなんだろう?」とかも、WHERE句で一回一回指定することなく一覧にできる。

注意点として、こうやって出す結果はもとのレコードと同じ数なので、集計関数としては扱ってもらえない。つまり同じ階層の他カラムを(重複削除のためとかで)GROUP BYに突っ込んだりしていると、これも入れなさいよと怒られる。はず(うろ覚えなので責任は取りません)。階層を変えるなりしないとダメらしい。

まとめこそすれ

やっぱり正直面倒くさいので、他の関数でできることなら他の関数でやったほうがいいと思う。

でもwindow関数は痒い所に手が届くというか、「質」を上げてくれる感じがする。これじゃなきゃだめ、という場合は少ないけど確実にあるし、使いこなせれば冗長なクエリが短縮できる場合もあるかもしれない。曖昧なことしか言えないのがまさに苦手であることの証明になってしまった。