メインコンテンツへスキップ
見出し画像

グループごとの順位をSQL1本で!OVER句の考え方|vol.18

    この記事は、
    グループごとの順位や累計を行を残したまま出すOVER句を、
    図を使って整理した記事です。


    お疲れ様です。花冷です🌸

    前回は、ONとWHERE、WHEREとHAVINGを見ました!
    条件の位置で結果が大きく変化するのできちんと理解しておきましょう✨

    今回は、行をまとめずにグループごとの集計や順位を列挙するSQLです。


    🔍 OVER句は行を残したままグループを作る


    ⭐GROUP BYはグループごとに行を1行にまとめます。

    ⭐OVER (PARTITION BY XXX)は、
     XXXごとにグループを作りますが、行はまとめません。
     XXXごとの合計を、すべての行の横に並べられます。

    この形の関数を、Oracleでは分析関数、
    一般にはウィンドウ関数と呼びます。

    画像

    ⭐OVERの中のORDER BYはグループの中での並びを決めます。
     よく出力順序を変えるために最後に付けるORDER BYとは別のものです。

    🌸🌸🌸
    OVERがウィンドウ関数と呼ばれることを知りませんでした(笑)
    ORDER BYはよく使いますが、PARTITION BYは苦手…と
    思っていましたが、改めて見てみるとそんなに難しい話じゃない!!

    ぱっと見強そうなSQLですが、上図のようにしてしまえば
    直感的に理解できますね!

    🌸🌸🌸


    ⚠️ 順位の3つは、同点の扱いが違う

    • ROW_NUMBER:同点でも、重ならない番号を付ける

    • RANK:同点は同じ順位。次の順位が飛ぶ(1、1、3)

    • DENSE_RANK:同点は同じ順位。飛ばない(1、1、2)

    ※ROW_NUMBERは同点だと順序が保証されません

    各グループの1位だけを取るには、
    順位を付けたSELECTをサブクエリにします。
    内側で、順位の列に rn という別名を付けておき、
    外側で、WHERE rn = 1 と書きます。

    SELECT 地域, 担当, 売上
    FROM (
        SELECT 地域, 担当, 売上,
               ROW_NUMBER() OVER (
                   PARTITION BY 地域      -- 地域ごとにグループを作る
                   ORDER BY 売上 DESC     -- グループの中で、売上の大きい順
               ) AS rn                    -- ← 内側で付ける別名
        FROM 売上表
    )
    WHERE rn = 1;                         -- ← 外側で絞る
    画像

    🔧 累計と前の行は、SUMとLAG

    累計は、SUMにOVERを付けて出します。
    『SUM(売上) OVER (PARTITION BY XXX ORDER BY 年月)』

    ちなみに上記でORDER BYを書かないと
    グループ全体の合計が全行に入ってしまいます💦

    LAGは、1つ前の行の値を返します。
    カレンダー上の前月ではなく、並んだ順の前の行なので、
    仮に4,5,7,8と6月が欠けるデータだと7月のデータのLAGは
    5月になるので注意が必要ですね。

    🌸🌸🌸
    累計や前月との比較は、私はSQLではあまりしたことがなく…

    というのも、機能に必要なのではなく影響範囲やデータ分析に
    必要で求められることが多かった
    です🤔

    そのため、私の職場だと必要な行をSQLで出力してExcelで分析!
    ってするほうが主流
    でした。
    正直に言うと、LAGは、使ったことがありません(笑)

    🌸🌸🌸


    🎉 おわりに

    🔴OVER:
     本来の行を残したまま、グループを作る
    🔴PARTITION BY:
     グループに分ける。行はまとまらない。
    🔴ROW_NUMBER・RANK・DENSE_RANK:
     同点の扱いが違う

    ⭐「GROUP BYは、まとめて集計
    ⭐「OVER&PARTITIONは、行を残したまま集計する」
    。


    さて、よく使うSELECTってありますよね🤔
    あれを毎回書かずに保存しておく方法はあるのでしょうか。

    次回は、ビューとマテリアライズドビューを覗いていきます!

    今日もお疲れ様でした✨
    また次回の記事でお会いしましょう!


    ⭐次回の記事はこちら!

    ⭐マガジンはこちら!

    📩質問はこちらまでどうぞ!


    この記事が参加している募集

     
     

    花冷

     
     
    花冷(はなびえ)といいます。システムエンジニアです。 ”やりたいこと”の過程を赤裸々に記録しています。 ・開発ログ/学習ログ/メンターログ いつでも質問・依頼待ってます📩 ⭐現在、メンター経験を積みたいため、モニター募集中です!

    あなたへのおすすめ