データサイエンス100本ノックで勉強(29)ウインドウ関数

データサイエンス100本ノックでSQLPythonを勉強していきます。

github.com

解答にあるウインドウ関数がすぐに理解できなかったので調べ直しました。

ウインドウ関数については、こちらの記事がわかりやすかったです。

www.projectgroup.info

以下が自分がぱっと見で理解できなかった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;

f:id:JunpeiNakasone:20220219055259p:plain

これだと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;

f:id:JunpeiNakasone:20220219055333p:plain

すると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;

f:id:JunpeiNakasone:20220219055537p:plain

これだとstore_cdごとに持っているproduct_cdの総件数が取得され、product_cdが何かまでは判定できませんでした。

少しウインドウ関数の理解が深まったので繰り返し使っていきたいと思います。