データサイエンス100本ノックでSQLとPythonを勉強していきます。
解答にあるウインドウ関数がすぐに理解できなかったので調べ直しました。
ウインドウ関数については、こちらの記事がわかりやすかったです。
以下が自分がぱっと見で理解できなかったSQLです。
S-029: レシート明細テーブル(receipt)に対し、店舗コード(store_cd)ごとに商品コード(product_cd)の最頻値を求めよ。
-- コード例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を計算する」というコメントが最初難しく感じていたのですが、自分でも理解しやすい言い回しにすると 、
「RANK()などのウインドウ関数を使ってstore_cdのグループでproduct_cdの件数が一番多いものを取得する」
という感じで解釈しました。
ウインドウ関数の動きを確かめたかったので、WITH句の中のクエリを抜き出し、PARTITION BYを外してみました。
SELECT store_cd, product_cd, count(1), rank() OVER(ORDER BY count(1) desc) AS rnk from receipt group by store_cd, product_cd limit 10;
これだとstore_cdも含めてrankがついてしまっています。
本来はstore_cdごとに一番product_cdの件数が多いものにrankで1を振りたいので、rank()関数にPARTITION BYをつける必要があります。
PARTITION BYをつけて実行しました。
SELECT store_cd, product_cd, count(1), rank() OVER(PARTITION BY store_cd ORDER BY count(1) desc) AS rnk from receipt group by store_cd, product_cd limit 10;
するとstore_cdごとに一番件数が多いproduct_cdにrankで1が振られました。
あとはこのテーブルをWITH句で使って、その後にrnk=1のWhere条件を使えば、store_cdごとにproduct_cdの最頻値が取れます。
あと自分の頭整理するために以下のクエリも実行してみました。
SELECT store_cd, count(product_cd) from receipt group by store_cd limit 10;
これだとstore_cdごとに持っているproduct_cdの総件数が取得され、product_cdが何かまでは判定できませんでした。
少しウインドウ関数の理解が深まったので繰り返し使っていきたいと思います。