SQLのウィンドウ関数で粒度の異なるグルーピングを同時に行う
背景
SQLで分析をしていると、粒度の異なるグルーピングを同時に行いたくなる場合がある。
例えば何らかの割合を出すために「分子はカラムAとカラムBでGROUP BYしてSUM、分母はカラムBだけでGROUP BYしてSUMしたい」という場合がある。
このような場合に、教科書どおりにGROUP BY句を使うだけではうまくいかないことが多い。
この記事では、ウィンドウ関数を用いてそのような集計をパパっと行う方法を問題形式で学ぶ。
問題
設定
あなたはとあるデジタルコンテンツ販売企業のアナリストであり、このたびマンガ部門の分析を受け持つことになった。
この部門では、将来的にユーザーごとにパーソナライズされたオススメ機能を実装することを考えている
しかし今はまだそのリソースがないため、当面は「性別ごとに人気のあるカテゴリをメールでオススメする」というアナログな手法を採用することになった。
分析に使えるテーブルは販売テーブル（sales）、ユーザーマスタ（users）、商品マスタ（items）の3つで、ER図は下記の通りだ。
また、サンプルデータはGithubにあげておいたので、データに対して実際にクエリを叩きたい方はこちらをお好きなDBに入れてご利用いただきたい。
問題1
上記のような状況下で、マネージャーから下記のように依頼された。
- 性別ごとのカテゴリ別構成比を出してほしい
- 計算方法としては、性別ごとに総販売個数に占める各カテゴリの販売個数の割合を算出してほしい
- 成果物は、SQLで算出した下図左のような表と、それを元にした右図のようなグラフ
それでは、早速集計してみよう。
正しいクエリが書ければ、上記の表と同じ結果が返ってくるはずだ。
マネージャーの言った「性別ごとのカテゴリ別販売構成比」の定義は、下記のようになるだろう。
ここで、分母と分子とで集計の粒度が異なることが分かるだろうか。分子は「性別＋カテゴリ」で、分母は「性別」で、それぞれグルーピングする必要がある。
GROUP BYに指定できるカラムの組み合わせは一つなので、教科書どおりにGROUP BY句を使用しても、このような集計は行うことができない。
では、どうするか。やり方にはいくつか考えられるが、最も簡潔なのがウィンドウ関数を使う方法だ。
-- 性別ごとの販売構成比 SELECT DISTINCT sex , category , COUNT(sales_id) OVER(PARTITION BY sex, category) / COUNT(sales_id) OVER(PARTITION BY sex) AS category_rate FROM ${sales_table} INNER JOIN ${user_table} USING(user_id) INNER JOIN ${item_table} USING(item_id) ORDER BY sex , category ;
このクエリのポイントは3つある。
- GROUP BY句を使わず、ウィンドウ関数を使う
- ウィンドウ関数のOVER句にはORDER BYを書かない
- SELECT句にDISTINCTを書く
一つ一つ解説する。
1. GROUP BY句を使わず、ウィンドウ関数を使う
GROUP BY句はグルーピングの粒度を1つしか指定できないが、ウィンドウ関数は一つのSELECT句の中に異なる粒度のグルーピングを共存させることができる（PARTITION BYで指定するカラムを変えれば良い）。
こうすることで、分母と分子で異なる粒度で集計をすることができる。
2. ウィンドウ関数のOVER句にはORDER BYを書かない
通常、ウィンドウ関数は累積和等の順序の概念のある計算を行うために利用される。
しかし、ウィンドウ関数のOVER句にORDER BYを書かないと、通常のGROUP BY句と同様に順序を意識しない計算をしてくれる。
つまり、次の2つのSQLは等価になる。
- ウィンドウ関数を使う場合
SELECT DISTINCT user_id , COUNT(sales_id) OVER(PARTITION BY user_id) FROM sales ;
- 通常のGROUP BY句を利用する場合
SELECT user_id , COUNT(sales_id) FROM sales GROUP BY user_id ;
3. SELECT句にDISTINCTを書く
ウィンドウ関数はGROUP BY句と異なり、レコードの集約をしない。
そのため、DISTINCTをつけないと元のレコード数と同じレコード数の結果が返ってきて冗長になる。
問題2
それでは、異なる粒度のグルーピングをもう1問やってみよう。
問題1で計算した「性別ごとのカテゴリ別販売構成比」の表とグラフを再掲すると、下記のようになる。
これを見ると、男性でも女性でも少女漫画が一番人気という風に見える。
ここで「なるほど、最近の男性の間では少女漫画が流行っているんだな」などと早合点してはいけない。
直観に反する結果が出た時は、必ずドリルダウンして本当にそうなっているのかを検証するべきだ。
確認が不十分なまま報告を行うと、後で恥をかくことも多い。
試しに、性別ごとの販売個数から一歩ドリルダウンし、ユーザーごとの販売個数を男性に関して見てみよう。
- クエリ
-- 男性のカテゴリ別販売構成比 SELECT user_id , category , COUNT(sales_id) AS sales_cnt FROM sales INNER JOIN users USING(user_id) INNER JOIN items USING(item_id) WHERE sex = ‘男’ GROUP BY user_id , category ORDER BY user_id , category ;
- 結果
これを見ると、ユーザーu00001だけが猛烈な勢いで少女漫画を読んでいることが分かる。
そもそも他のユーザーがたかだかマンガを3~4冊しか購入しないのに対し、このユーザーだけ7冊も買っているので、集計結果に対して与える影響は極めて大きい。
この外れ値のせいで、危うく「少女漫画が男性に人気」という誤った解釈を出すところだった。
この例は極端だが、一部の並外れた行動をするユーザーによって全体の集計結果が引っ張られるというのは、分析をしているとよく起こることだ。
このことを踏まえると、現状の定義は性別ごとの各カテゴリの人気を図る指標としては不十分であると言える。
購入数の多い一部ユーザーに引っ張られないために、「ユーザーごとに販売構成比を出してから、その値を性別ごとに平均する」というアプローチを取ることにしよう。
イメージとしては、下図のようになる（値はダミー）。
例えば男性における少年漫画の販売構成比を出したい場合、男性の少年漫画の面積（青色部分）の総和を、男性ユーザーの棒グラフの総面積で割れば良い
このような方法なら、個人ごとの分母の差に結果が引きずられない。
さて、このような結果を出すクエリを書くにはどうすれば良いだろうか？
先に答えだけ掲載すると、下記のようになる。
実際に集計をしてみよう。
「ユーザーごとの集計」「性別ごとの集計」という2段階の集計を経る必要があるので、WITH句を使って段階的にクエリを書くのが良いだろう。
それぞれの段階において、異なる粒度のグルーピングのテクニックを使うことになる。
解答は下記の通りだ。
-- 性別ごとのカテゴリ別販売構成比 WITH -- ユーザーごとの販売構成比 category_rate_per_user as ( SELECT DISTINCT user_id , sex , category , COUNT(sales_id) OVER(PARTITION BY user_id, category) / COUNT(sales_id) OVER(PARTITION BY user_id) AS category_rate FROM sales INNER JOIN users USING(user_id) INNER JOIN items USING(item_id) ) -- 性別ごとに平均 , category_rate_avg as ( SELECT DISTINCT sex , category , SUM(category_rate) OVER(PARTITION BY sex, category) / SUM(category_rate) OVER(PARTITION BY sex) as category_rate FROM category_rate_per_user ORDER BY sex , category ) SELECT * FROM category_rate_avg ;
このようにウィンドウ関数をグルーピングに使うと集計の幅が広がるので、ぜひ実践してみていただきたい。
