データサイエンス100本ノックでSQLとPythonを勉強していきます。
S-029: レシート明細テーブル（receipt）に対し、店舗コード（store_cd）ごとに商品コード（product_cd）の最頻値を求めよ。
この問題の解答のSQLでわからないこと多かったので調べました。
解答のSQLは以下のようになっています。
%%sql
-- コード例1: 分析関数でmodeを計算する
WITH product_mode AS (
SELECT store_cd,product_cd, COUNT(1) as mode_cnt,
RANK() OVER(PARTITION BY store_cd ORDER BY COUNT(1) DESC) AS rnk
FROM receipt
GROUP BY store_cd,product_cd
)
SELECT store_cd,product_cd, mode_cnt
FROM product_mode
WHERE rnk = 1
ORDER BY store_cd,product_cd;
コメントにある分析関数とはウインドウ関数のことでした。
modeは最頻値ですね。
WITH句で最頻値を計算しproduct_modeテーブルに入れて、そこからrnkが1になっているデータを取得しているようです。
ちなみにWITH句を使わないとrnkが存在していないのでエラーになっていました。
解答のWITH句ないのクエリだけ(limitはつけて)実行すると以下の結果になっていました。
store_cdがS12007だとproduct_cdの最頻値(rnk=1)はP060303001となっています。
これでrnkが1のデータを取得できれば最頻値が取れます。
調べたところmode()という関数でも最頻値を取得できるようでした。
最頻値を取る時の最適なクエリは状況によるかも知れません。
あとはウインドウ関数がまだとっつきにくさを感じているので、次回ウインドウ関数について勉強したいと思います。
