SQLでは様々なデータ集計をしますが、GROUP BYで集計した結果に別の集計結果を付けた場合など、異なる集計結果を同時に扱う場合はSQLが複雑になります。GROUP BYやUNIONやサブクエリーなど、駆使することでSQLを組めますが可読性が下がったり、のちのちメンテナンスするときにしんどかったりします。
OVER PARTITION BYで解決！
SQL Serverには、データの集計や分析を行う際に非常に便利な機能の1つであるPARTITION BYがあります。この機能を使うことで、データを特定の基準でグループ化し、各グループに対して集計関数を適用することができます。ビジネスにおいても、この機能はさまざまなシーンで活用できます。以下では、具体的なビジネスシーンを例に挙げながら、SQL ServerのPARTITION BYの活用方法を解説します。
実例をSQLで解説してるので、そのまま活用できると思います！
具体的な使用例
PARTITION BY とは？
分析関数（ウインドウ関数）です。リファレンスを読めば詳しい説明があるのでそちら参照してもらい、より実践的なクエリに触れてみて理解を深めて頂ければと思います。
ビジネスシーンで使えるSQLの具体例を次のテーブルを元に解説します。
得意先テーブル:
| 得意先コード | 得意先名 |
|---|---|
| 1 | 株式会社いちばん |
| 2 | 有限会社にばん |
| 3 | 合同会社さんばん |
売上テーブル :
| 売上番号 | 得意先コード | 売上金額 | 粗利金額 | 売上日 |
|---|---|---|---|---|
| 1 | 1 | 1000 | 500 | 2023-03-01 |
| 2 | 1 | 1500 | 700 | 2023-03-02 |
| 3 | 2 | 2000 | 1000 | 2023-03-03 |
| 4 | 2 | 2500 | 1250 | 2023-03-04 |
| 5 | 3 | 3000 | 1500 | 2023-03-05 |
| 6 | 3 | 3500 | 1750 | 2023-03-06 |
| 7 | 1 | 1200 | 600 | 2023-03-07 |
| 8 | 2 | 2200 | 1100 | 2023-03-08 |
| 9 | 3 | 3200 | 1600 | 2023-03-09 |
| 10 | 1 | 1800 | 900 | 2023-03-10 |
事例1: 各得意先の売上合計と粗利合計
この事例では、得意先ごとに売上と粗利の合計を計算します。PARTITION BYを使うことで、各得意先に分けて集計を行い、それぞれの得意先が期間内にどれだけの売上と粗利を生み出したかを一覧できます。特に、売上高と粗利高の比較を通じて、どの得意先が最も利益をもたらしているかを把握するのに役立ちます。
|
1 2 3 4 5 6 |
SELECT 得意先コード, 売上金額, 粗利金額, SUM(売上金額) OVER (PARTITION BY 得意先コード) AS 売上合計, SUM(粗利金額) OVER (PARTITION BY 得意先コード) AS 粗利合計 FROM 売上; |
出力結果
| 得意先コード | 売上金額 | 粗利金額 | 売上合計 | 粗利合計 |
|---|---|---|---|---|
| 1 | 1000 | 500 | 5500 | 2700 |
| 1 | 1500 | 700 | 5500 | 2700 |
| 1 | 1200 | 600 | 5500 | 2700 |
| 1 | 1800 | 900 | 5500 | 2700 |
| 2 | 2000 | 1000 | 6700 | 3350 |
| 2 | 2500 | 1250 | 6700 | 3350 |
| 2 | 2200 | 1100 | 6700 | 3350 |
| 3 | 3000 | 1500 | 9700 | 4850 |
| 3 | 3500 | 1750 | 9700 | 4850 |
| 3 | 3200 | 1600 | 9700 | 4850 |
事例2: 各得意先ごとの売上高ランキング
この事例では、得意先内での各売上記録に対するランキングを提供します。PARTITION BY句により得意先ごとにデータセットを分割し、ORDER BY句で売上金額の降順に各売上をランク付けすることができます。この分析は、各得意先の中でどの売上が最も高かったか、またどのような売上が多いかを理解するのに有効です。
|
1 2 3 4 |
SELECT 得意先コード, 売上金額, RANK() OVER (PARTITION BY 得意先コード ORDER BY 売上金額 DESC) AS ランキング FROM 売上; |
出力結果
| 得意先コード | 売上金額 | ランキング |
|---|---|---|
| 1 | 1800 | 1 |
| 1 | 1500 | 2 |
| 1 | 1200 | 3 |
| 1 | 1000 | 4 |
| 2 | 2500 | 1 |
| 2 | 2200 | 2 |
| 2 | 2000 | 3 |
| 3 | 3500 | 1 |
| 3 | 3200 | 2 |
| 3 | 3000 | 3 |
事例3: 前回の売上との差額
得意先ごとに、各売上記録の前回の売上との差額を計算する事例です。PARTITION BYで得意先コードごとにデータを区切り、LAG関数を使用して前の行の売上金額を取得します。これにより、売上の増減を個別に追跡し、特定の時期のパフォーマンス変動を分析することが可能になります。
|
1 2 3 4 |
SELECT 得意先コード, 売上金額, 売上金額 - LAG(売上金額) OVER (PARTITION BY 得意先コード ORDER BY 売上日) AS 前回差額 FROM 売上; |
出力結果
| 得意先コード | 売上金額 | 前回差額 |
|---|---|---|
| 1 | 1000 | NULL |
| 1 | 1500 | 500 |
| 1 | 1200 | -300 |
| 1 | 1800 | 600 |
| 2 | 2000 | NULL |
| 2 | 2500 | 500 |
| 2 | 2200 | -300 |
| 3 | 3000 | NULL |
| 3 | 3500 | 500 |
| 3 | 3200 | -300 |
事例4: 各得意先ごとの売上累計
得意先ごとに売上日を基準にした売上の累計を計算します。PARTITION BY句で得意先コードごとにセグメントを作り、ORDER BY句で売上日の順に並べた上で、SUM関数でその時点までの累計売上を算出します。この分析を通じて、各得意先の売上が時間とともにどのように推移しているかを把握できます。
|
1 2 3 4 5 |
SELECT 得意先コード, 売上日, 売上金額, SUM(売上金額) OVER (PARTITION BY 得意先コード ORDER BY 売上日) AS 累計売上 FROM 売上; |
出力結果
| 得意先コード | 売上日 | 売上金額 | 累計売上 |
|---|---|---|---|
| 1 | 2023-03-01 | 1000 | 1000 |
| 1 | 2023-03-02 | 1500 | 2500 |
| 1 | 2023-03-07 | 1200 | 3700 |
| 1 | 2023-03-10 | 1800 | 5500 |
| 2 | 2023-03-03 | 2000 | 2000 |
| 2 | 2023-03-04 | 2500 | 4500 |
| 2 | 2023-03-08 | 2200 | 6700 |
| 3 | 2023-03-05 | 3000 | 3000 |
| 3 | 2023-03-06 | 3500 | 6500 |
| 3 | 2023-03-09 | 3200 | 9700 |
まとめ
これらの事例は、得意先ごとにデータを区切り(PARTITION BY)、集計、ランキング、差額計算、累計計算を行う際の実用的なクエリになってます。。PARTITION BYを使用することで、複雑なデータ分析を簡単かつ効率的に実行できるため、ビジネスインテリジェンスやデータ分析の文脈で非常に価値があります。
これでシンプルかつ可読性の高いSQLになるので、はじめは使いにくさを感じると思いますが、使えば使うほど便利さがわかるので是非使ってみてください！
おすすめ情報
ある程度SQLに慣れてくるとより複雑なSQLを組むようになり、分析関数（ウインドウ関数）の壁にぶち当たります。本書は私が初級から抜け出すのになったきっかけの本です。比較的新しい関数なので古いSQLの本には載ってません。昔ながらのSQLを書く人はウインドウ関数の理解に苦しむケースが多いと思います。一歩先に進むためにも苦手意識を克服したいですね。
コメント