各グループ別の上位10件のランキングを作成するクエリ

先日公開した記事で、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件にできます。
グループ別にランキングを抽出するクエリを書きたい時は活用してみてください。