SQL ServerのPARTITION BY活用術 ビジネスにおける効率的なデータ集計と分析方法

IT技術情報

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合同会社さんばん

売上テーブル :

売上番号得意先コード売上金額粗利金額売上日
1110005002023-03-01
2115007002023-03-02
32200010002023-03-03
42250012502023-03-04
53300015002023-03-05
63350017502023-03-06
7112006002023-03-07
82220011002023-03-08
93320016002023-03-09
10118009002023-03-10

事例1: 各得意先の売上合計と粗利合計

この事例では、得意先ごとに売上と粗利の合計を計算します。PARTITION BYを使うことで、各得意先に分けて集計を行い、それぞれの得意先が期間内にどれだけの売上と粗利を生み出したかを一覧できます。特に、売上高と粗利高の比較を通じて、どの得意先が最も利益をもたらしているかを把握するのに役立ちます。

出力結果

得意先コード売上金額粗利金額売上合計粗利合計
1100050055002700
1150070055002700
1120060055002700
1180090055002700
22000100067003350
22500125067003350
22200110067003350
33000150097004850
33500175097004850
33200160097004850

事例2: 各得意先ごとの売上高ランキング

この事例では、得意先内での各売上記録に対するランキングを提供します。PARTITION BY句により得意先ごとにデータセットを分割し、ORDER BY句で売上金額の降順に各売上をランク付けすることができます。この分析は、各得意先の中でどの売上が最も高かったか、またどのような売上が多いかを理解するのに有効です。

出力結果

得意先コード売上金額ランキング
118001
115002
112003
110004
225001
222002
220003
335001
332002
330003

事例3: 前回の売上との差額

得意先ごとに、各売上記録の前回の売上との差額を計算する事例です。PARTITION BYで得意先コードごとにデータを区切り、LAG関数を使用して前の行の売上金額を取得します。これにより、売上の増減を個別に追跡し、特定の時期のパフォーマンス変動を分析することが可能になります。

出力結果

得意先コード売上金額前回差額
11000NULL
11500500
11200-300
11800600
22000NULL
22500500
22200-300
33000NULL
33500500
33200-300

事例4: 各得意先ごとの売上累計

得意先ごとに売上日を基準にした売上の累計を計算します。PARTITION BY句で得意先コードごとにセグメントを作り、ORDER BY句で売上日の順に並べた上で、SUM関数でその時点までの累計売上を算出します。この分析を通じて、各得意先の売上が時間とともにどのように推移しているかを把握できます。

出力結果

得意先コード売上日売上金額累計売上
12023-03-0110001000
12023-03-0215002500
12023-03-0712003700
12023-03-1018005500
22023-03-0320002000
22023-03-0425004500
22023-03-0822006700
32023-03-0530003000
32023-03-0635006500
32023-03-0932009700

まとめ

これらの事例は、得意先ごとにデータを区切り(PARTITION BY)、集計、ランキング、差額計算、累計計算を行う際の実用的なクエリになってます。。PARTITION BYを使用することで、複雑なデータ分析を簡単かつ効率的に実行できるため、ビジネスインテリジェンスやデータ分析の文脈で非常に価値があります。
これでシンプルかつ可読性の高いSQLになるので、はじめは使いにくさを感じると思いますが、使えば使うほど便利さがわかるので是非使ってみてください!

おすすめ情報

ある程度SQLに慣れてくるとより複雑なSQLを組むようになり、分析関数(ウインドウ関数)の壁にぶち当たります。本書は私が初級から抜け出すのになったきっかけの本です。比較的新しい関数なので古いSQLの本には載ってません。昔ながらのSQLを書く人はウインドウ関数の理解に苦しむケースが多いと思います。一歩先に進むためにも苦手意識を克服したいですね。

著:ミック
¥2,750 (2024/04/19 17:37時点 | Amazon調べ)

コメント

タイトルとURLをコピーしました