先日公開した記事で、SQLを使った上位10件や上位N%のランキングを抽出するクエリを紹介しました
その中で全体の10位ではなく、各グループ別で10位ずつ抜き出したいお問い合わせを頂いたので今回はグループ別に上位10件を抜き出すクエリを紹介していきます。
www.ikuo00uk.dev
グループ別に上位N件を抜き出したい。
店舗の売上ランキングを抽出する際に、全体での売上ランキングではなく、特定カテゴリー別のランキングを出したいときなどあると思います。
都道府県別や、クラス・営業チーム別など様々ケースが想定できるかと思います。
例としてカテゴリーと店名、売上が管理されているデータベースを想定します。
| カテゴリー | 店名 | 売上 |
|---|---|---|
| レストラン | レストランA | 10000 |
| レストラン | レストランB | 20000 |
| カフェ | カフェA | 5000 |
| カフェ | カフェB | 8000 |
| バー | バーA | 7000 |
| カフェ | カフェC | 3000 |
| レストラン | レストランC | 15000 |
| バー | バーB | 9000 |
| バー | バーC | 13000 |
サブクエリとRANK()を使って実現する
サンプルとして各カテゴリー別に売上が高い店舗を2つ抜き出すことを想定したクエリを紹介します。
SELECT カテゴリー, 店名, 売上 FROM ( SELECT カテゴリー, 店名, 売上, RANK() OVER (PARTITION BY カテゴリー ORDER BY 売上 DESC) AS 売上ランキング FROM {テーブル名} ) t WHERE 売上ランキング <= 2 ORDER BY カテゴリー, 売上ランキング
ポイントとなるのは、直接テーブルからデータを抜き出すのではなく、一度ランキングをRANK()関数で作成したうえでサブクエリで呼び出すところになります。
RANK()関数
RANK() OVER (PARTITION BY カテゴリー ORDER BY 売上 DESC) AS 売上ランキング
ここでテーブルの中の売上をカテゴリー別にランキングを作成しています。
RANK()関数は、グループ化された結果に対して、ランキングを計算するのに使用されます。RANK()関数は、同じ値の行に対して同じランクを返し、ランクが飛び越されることはありません。たとえば、1, 2, 2, 3, 4という値がある場合、ランクは、1, 2, 2, 3, 4となります。2が2つあるため、2の次のランクは3となります。この関数を使用して、売上ランキングを計算することができます。
サブクエリ
サブクエリとは、SQL文の中に別のSQL文を入れることで、より複雑な検索条件を設定するための機能です。 SELECT文の中に、SELECT文を入れるなどのケースで利用します。
処理を分かりやすくフローチャートにすると下記のようになります。
複雑なクエリを考えるときには、得たい結果を得るにはどういう手順が必要か？書き出すとわかりやすいです。
最後に今回紹介したクエリの実行結果は下記になります。
| カテゴリー | 店名 | 売上ランキング |
|---|---|---|
| カフェ | カフェB | 1 |
| カフェ | カフェA | 2 |
| バー | バーC | 1 |
| バー | バーB | 2 |
| レストラン | レストランB | 1 |
| レストラン | レストランC | 2 |
今回は分かりやすくするために上位2件のランキングのみ紹介しましたが、 WHERE 売上ランキング <= 2の部分を10などに変えることで上位10件にできます。
グループ別にランキングを抽出するクエリを書きたい時は活用してみてください。
最近良くSQLの入門書を聞かれるのでお答えすると、個人的にはSQLの入門はゼロからはじめるデータベース操作がおすすめです。プロダクトマネージャーやプロジェクトマネージャーの入門にももちろんですし、エンジニアを目指している方も参考になる情報が多いと思います。筆者がデータベース操作に携わる時に先輩のエンジニアからお勧めされた本で古いですが概念を理解しやすいですし、分かりやすく書かれていると思います。
