差分

このページの2つのバージョン間の差分を表示します。

--- documents:proglang:sql:sql-0008 [2026/05/08 12:19] – ↷ 移動操作に合わせてリンクを書き換えました。 k896951
+++ documents:proglang:sql:sql-0008 [2026/05/16 11:35] (現在) – [SQLでTOP3ランキングをやりたい] k896951
@@ 行 1: / 行 1: @@
+====== 003.SQLでTOP3ランキングをやりたい ======
+年8月14日\\
+PostgreSQL 9.5.4 で説明してますが、Oracle でも可能な筈。
+夏休み直前に後輩Hシー氏から振られた話題を記録しておく。
+{{description>RANK関数でTOP3ランキングをやってみる}}
+===== 下準備 =====
+今回のデータは、[[documents:proglang:sql:sql-0001|SQLでデータベーステーブルの縦･横変換]]で使っていたデータを流用します。
+<code sql>
+$ psql -h 192.168.1.28 kouhai kouhai
+psql (9.5.3, サーバー 9.5.4)
+"help" でヘルプを表示します.
+kouhai=> \d
+         リレーションの一覧
+ スキーマ | 名前 |    型    | 所有者
+----------+------+----------+--------
+ public   | 明細 | テーブル | kouhai
+(1 行)
+kouhai=> \d 明細
+         テーブル "public.明細"
+   列   |          型           | 修飾語
+--------+-----------------------+--------
+ 売上年 | character varying(4)  |
+ 商品   | character varying(10) |
+ 売上   | numeric(5,0)          |
+kouhai=> select * from 明細;
+ 売上年 |    商品    | 売上
+--------+------------+------
+   | みかん     |   40
+   | りんご     |   37
+   | ばなな     |   24
+   | すもも     |   15
+   | ぶどう     |    5
+   | みかん     |   60
+   | ばなな     |   30
+   | りんご     |   29
+   | すもも     |   10
+   | ぶどう     |    3
+   | みかん     |   90
+   | まんごー   |   88
+   | さくらんぼ |   70
+   | ぶどう     |   10
+   | めろん     |    6
+   | ここあ     |  290
+   | だいず     |  100
+(17 行)
+kouhai=>
+</code>
+===== ベースとなるクエリ =====
+^ 商品毎の売上合計  ^ 売上年毎の売上合計  ^
+|<code sql>
+SELECT 商品
+      ,SUM(売上) AS 合計
+  FROM 明細
+ GROUP BY 商品
+ ORDER BY 合計 DESC
+;
+</code>|<code sql>
+SELECT 売上年
+      ,SUM(売上) AS 合計
+  FROM 明細
+ GROUP BY 売上年
+ ORDER BY 合計 DESC
+;
+</code>|
+|<code sql>
+    商品    | 合計
+------------+------
+ ここあ     |  290
+ みかん     |  190
+ だいず     |  100
+ まんごー   |   88
+ さくらんぼ |   70
+ りんご     |   66
+ ばなな     |   54
+ すもも     |   25
+ ぶどう     |   18
+ めろん     |    6
+(10 行)
+</code>|<code sql>
+ 売上年 | 合計
+--------+------
+   |  390
+   |  264
+   |  132
+   |  121
+(4 行)
+</code>|
+これらのクエリがベースとなります。
+===== 商品毎のランキング =====
+商品毎の結果にランキングを付与してみます。
+^ 売上トップのランキング  ^ 売上ワーストのランキング  ^
+|<code sql>
+SELECT 商品
+      ,SUM(売上) AS 合計
+      ,RANK() OVER ( ORDER BY SUM(売上) DESC) AS ランキング
+  FROM 明細
+ GROUP BY 商品
+;
+</code>|<code sql>
+SELECT 商品
+      ,SUM(売上) AS 合計
+      ,RANK() OVER ( ORDER BY SUM(売上) ) AS ランキング
+  FROM 明細
+ GROUP BY 商品
+;
+</code>|
+|<code sql>
+    商品    | 合計 | ランキング
+------------+------+------------
+ ここあ     |  290 |          1
+ みかん     |  190 |          2
+ だいず     |  100 |          3
+ まんごー   |   88 |          4
+ さくらんぼ |   70 |          5
+ りんご     |   66 |          6
+ ばなな     |   54 |          7
+ すもも     |   25 |          8
+ ぶどう     |   18 |          9
+ めろん     |    6 |         10
+(10 行)
+</code>|<code sql>
+    商品    | 合計 | ランキング
+------------+------+------------
+ めろん     |    6 |          1
+ ぶどう     |   18 |          2
+ すもも     |   25 |          3
+ ばなな     |   54 |          4
+ りんご     |   66 |          5
+ さくらんぼ |   70 |          6
+ まんごー   |   88 |          7
+ だいず     |  100 |          8
+ みかん     |  190 |          9
+ ここあ     |  290 |         10
+(10 行)
+</code>|
+RANK()関数はウインドウ関数で、指定された範囲内(ウインドウ内)での現在行の位置に対応した値を返します。
+ウインドウの範囲はOVER句で指定されますが、このクエリにはPARTITION BY句による範囲指定がないので、テーブル内のレコード全体が対象になります。
+ウインドウ範囲の行並びについては、OVER句の指定にある ORDER BY句でソートをさせて決めています。この例ではSUM(売上) の結果をソートさせています。
+このソートはあくまでウインドウ範囲内でのソートで、この例ではウインドウ範囲がテーブル内のレコード全体なので、結果は合計でソートされていますが、慎重を期すならベースクエリにあった“ORDER BY 合計 DESC”の記述も行った方が良いです。
+===== 売上年別でランキング =====
+次に売上年別の結果にランキングを付与してみます。
+^ 売上年別のランキング  ^
+|<code sql>
+SELECT 売上年
+      ,SUM(売上) AS 合計
+      ,RANK() OVER ( ORDER BY SUM(売上) DESC) AS ランキング
+  FROM 明細
+ GROUP BY 売上年
+;
+</code>|
+|<code sql>
+ 売上年 | 合計 | ランキング
+--------+------+------------
+   |  390 |          1
+   |  264 |          2
+   |  132 |          3
+   |  121 |          4
+(4 行)
+</code>|
+集計単位を商品から売上年に切り替えただけで、結果は予想通りですね。
+こちらも、慎重を期すならベースクエリにあった“ORDER BY 合計 DESC”の記述も行った方が良いです。
+===== 売上年別の商品のランキング =====
+売上年別の商品売上ランキングを出してみます。
+^ 売上年別の商品売上ランキング  ^
+|<code sql>
+SELECT 売上年
+      ,商品
+      ,SUM(売上) AS 合計
+      ,RANK() OVER ( PARTITION BY 売上年 ORDER BY SUM(売上) DESC) AS ランキング
+  FROM 明細
+ GROUP BY 売上年,商品
+;
+</code>|
+|<code sql>
+ 売上年 |    商品    | 合計 | ランキング
+--------+------------+------+------------
+   | みかん     |   40 |          1
+   | りんご     |   37 |          2
+   | ばなな     |   24 |          3
+   | すもも     |   15 |          4
+   | ぶどう     |    5 |          5
+   | みかん     |   60 |          1
+   | ばなな     |   30 |          2
+   | りんご     |   29 |          3
+   | すもも     |   10 |          4
+   | ぶどう     |    3 |          5
+   | みかん     |   90 |          1
+   | まんごー   |   88 |          2
+   | さくらんぼ |   70 |          3
+   | ぶどう     |   10 |          4
+   | めろん     |    6 |          5
+   | ここあ     |  290 |          1
+   | だいず     |  100 |          2
+(17 行)
+</code>|
+ORVER句で指定しているPARTITION BY句で、RANK()関数が算出する行位置の範囲を指定します。
+ここでは 売上年 を指定するので、売上年毎にSUM(売上)でソートが行われ、RANK()関数は売上年が変わると改めて行位置を1から計算して返すことになります。
+===== TOP3を出すにはどうするのさ？ =====
+もうお分かりの通り、RANK()関数の結果でフィルターすれば良いのです。
+例えば売上年別の商品売上ランキングTOP3なら以下のようなクエリがあります。
+^  サブクエリ版  ^ WITH句利用版  ^
+|<code sql>
+SELECT M.*
+  FROM (SELECT 売上年
+              ,商品
+              ,SUM(売上) AS 合計
+              ,RANK() OVER (
+                        PARTITION BY 売上年
+                        ORDER BY SUM(売上) DESC) AS ランキング
+          FROM 明細
+         GROUP BY 売上年,商品
+       ) AS M
+ WHERE M.ランキング < 4
+;
+</code>|<code sql>
+WITH M AS (
+  SELECT 売上年
+        ,商品
+        ,SUM(売上) AS 合計
+        ,RANK() OVER (
+                  PARTITION BY 売上年
+                  ORDER BY SUM(売上) DESC) AS ランキング
+    FROM 明細
+   GROUP BY 売上年,商品
+)
+SELECT *
+  FROM M
+ WHERE M.ランキング < 4
+;
+</code>|
+|<code sql>
+ 売上年 |    商品    | 合計 | ランキング
+--------+------------+------+------------
+   | みかん     |   40 |          1
+   | りんご     |   37 |          2
+   | ばなな     |   24 |          3
+   | みかん     |   60 |          1
+   | ばなな     |   30 |          2
+   | りんご     |   29 |          3
+   | みかん     |   90 |          1
+   | まんごー   |   88 |          2
+   | さくらんぼ |   70 |          3
+   | ここあ     |  290 |          1
+   | だいず     |  100 |          2
+(11 行)
+</code>|<code sql>
+ 売上年 |    商品    | 合計 | ランキング
+--------+------------+------+------------
+   | みかん     |   40 |          1
+   | りんご     |   37 |          2
+   | ばなな     |   24 |          3
+   | みかん     |   60 |          1
+   | ばなな     |   30 |          2
+   | りんご     |   29 |          3
+   | みかん     |   90 |          1
+   | まんごー   |   88 |          2
+   | さくらんぼ |   70 |          3
+   | ここあ     |  290 |          1
+   | だいず     |  100 |          2
+(11 行)
+</code>|
+===== RANK()じゃ駄目な場合 =====
+売上合計が同じ値になる商品だって当然出てきます。そうした場合、RANK()関数を使うと意図した出力にならないかもしれません。
+例えば、データに漏れがあったことが判明し、‟すいか”、‟もも”の情報を追加したとしましょう。
+<code sql>
+kouhai=> INSERT INTO 明細 VALUES(2006,'すいか',88);
+INSERT 0 1
+kouhai=> INSERT INTO 明細 VALUES(2006,'もも',88);
+INSERT 0 1
+kouhai=> WITH M AS (
+kouhai(>   SELECT 売上年
+kouhai(>         ,商品
+kouhai(>         ,SUM(売上) AS 合計
+kouhai(>         ,RANK() OVER ( PARTITION BY 売上年 ORDER BY SUM(売上) DESC) AS ランキング
+kouhai(>     FROM 明細
+kouhai(>    GROUP BY 売上年,商品
+kouhai(> )
+kouhai-> SELECT *
+kouhai->   FROM M
+kouhai->  WHERE M.ランキング < 4
+kouhai-> ;
+ 売上年 |   商品   | 合計 | ランキング
+--------+----------+------+------------
+   | みかん   |   40 |          1
+   | りんご   |   37 |          2
+   | ばなな   |   24 |          3
+   | みかん   |   60 |          1
+   | ばなな   |   30 |          2
+   | りんご   |   29 |          3
+   | みかん   |   90 |          1
+   | もも     |   88 |          2
+   | まんごー |   88 |          2
+   | すいか   |   88 |          2
+   | ここあ   |  290 |          1
+   | だいず   |  100 |          2
+(12 行)
+kouhai=>
+</code>
+あれ？さくらんぼう……
+売上年が 2006 のものを見てみます。ついでなので、DENSE_RANK()関数、ROW_NUMBER()関数、についても確認してみます。
+^ RANK()関数  ^ DENSE_RANK()関数  ^ ROW_NUMBER()関数 ^
+|<code sql>
+SELECT
+ 売上年
+,商品
+,SUM(売上) AS 合計
+,RANK() OVER (
+   PARTITION BY 売上年
+   ORDER BY SUM(売上) DESC) AS ランキング
+  FROM 明細
+ WHERE 売上年='2006'
+ GROUP BY 売上年,商品
+;
+</code>|<code sql>
+SELECT
+ 売上年
+,商品
+,SUM(売上) AS 合計
+,DENSE_RANK() OVER (
+   PARTITION BY 売上年
+   ORDER BY SUM(売上) DESC) AS ランキング
+  FROM 明細
+ WHERE 売上年='2006'
+ GROUP BY 売上年,商品
+;
+</code>|<code sql>
+SELECT
+ 売上年
+,商品
+,SUM(売上) AS 合計
+,ROW_NUMBER() OVER (
+   PARTITION BY 売上年
+   ORDER BY SUM(売上) DESC) AS ランキング
+  FROM 明細
+ WHERE 売上年='2006'
+ GROUP BY 売上年,商品
+;
+</code>|
+|<code sql>
+ 売上年 |    商品    | 合計 | ランキング
+--------+------------+------+------------
+   | みかん     |   90 |          1
+   | すいか     |   88 |          2
+   | まんごー   |   88 |          2
+   | もも       |   88 |          2
+   | さくらんぼ |   70 |          5
+   | ぶどう     |   10 |          6
+   | めろん     |    6 |          7
+(7 行)
+kouhai=>
+</code>|<code sql>
+ 売上年 |    商品    | 合計 | ランキング
+--------+------------+------+------------
+   | みかん     |   90 |          1
+   | すいか     |   88 |          2
+   | まんごー   |   88 |          2
+   | もも       |   88 |          2
+   | さくらんぼ |   70 |          3
+   | ぶどう     |   10 |          4
+   | めろん     |    6 |          5
+(7 行)
+kouhai=>
+</code>|<code sql>
+ 売上年 |    商品    | 合計 | ランキング
+--------+------------+------+------------
+   | みかん     |   90 |          1
+   | すいか     |   88 |          2
+   | まんごー   |   88 |          3
+   | もも       |   88 |          4
+   | さくらんぼ |   70 |          5
+   | ぶどう     |   10 |          6
+   | めろん     |    6 |          7
+(7 行)
+kouhai=>
+</code>|
+すいか、まんごー、もも、の売上合計が同一値のため同順タイになり、
+  * RANK()関数では同順タイの部分の幅を飛ばしてナンバリングするので、さくらんぼうが5位になってしまいました。
+  * DENSE_RANK()関数を使った場合は同順タイがあっても飛ばさないでナンバリングするため、さくらんぼうは3位となります。
+  * ROW_NUMBER()関数はレコードの並びにそのまま連番を振るので、さくらんぼうは5位になります。\\ また、商品名によるソートが行われないので、ROW_NUMBER()関数の時は、すいか、まんごー、もも、の順位が変わる可能性が出てきます。
+どういった結果を望むのかで関数をチョイスする必要があります。
+===== 追記：ソート順が変わる話 =====
+ROW_NUMBER()の説明で「すいか、まんごー、もも、の順位が変わる可能性」のところがよう分からん、と言われたので。
+先のSQL実行結果の並びをもう一度確認します。
+<code sql>
+kouhai=> SELECT
+kouhai->  売上年
+kouhai-> ,商品
+kouhai-> ,SUM(売上) AS 合計
+kouhai-> ,ROW_NUMBER() OVER (
+kouhai(>    PARTITION BY 売上年
+kouhai(>    ORDER BY SUM(売上) DESC) AS ランキング
+kouhai->   FROM 明細
+kouhai->  WHERE 売上年='2006'
+kouhai->  GROUP BY 売上年,商品
+kouhai-> ;
+ 売上年 |    商品    | 合計 | ランキング
+--------+------------+------+------------
+   | みかん     |   90 |          1
+   | すいか     |   88 |          2    ←
+   | まんごー   |   88 |          3    ←
+   | もも       |   88 |          4    ←
+   | さくらんぼ |   70 |          5
+   | ぶどう     |   10 |          6
+   | めろん     |    6 |          7
+(7 行)
+kouhai=>
+</code>
+次に、売上年でのフィルターを外して実行します。
+<code sql>
+kouhai=> SELECT
+kouhai->  売上年
+kouhai-> ,商品
+kouhai-> ,SUM(売上) AS 合計
+kouhai-> ,ROW_NUMBER() OVER (
+kouhai(>    PARTITION BY 売上年
+kouhai(>    ORDER BY SUM(売上) DESC) AS ランキング
+kouhai->   FROM 明細
+kouhai->  GROUP BY 売上年,商品
+kouhai-> ;
+ 売上年 |    商品    | 合計 | ランキング
+--------+------------+------+------------
+   | みかん     |   40 |          1
+   | りんご     |   37 |          2
+   | ばなな     |   24 |          3
+   | すもも     |   15 |          4
+   | ぶどう     |    5 |          5
+   | みかん     |   60 |          1
+   | ばなな     |   30 |          2
+   | りんご     |   29 |          3
+   | すもも     |   10 |          4
+   | ぶどう     |    3 |          5
+   | みかん     |   90 |          1
+   | もも       |   88 |          2    ←
+   | まんごー   |   88 |          3    ←
+   | すいか     |   88 |          4    ←
+   | さくらんぼ |   70 |          5
+   | ぶどう     |   10 |          6
+   | めろん     |    6 |          7
+   | ここあ     |  290 |          1
+   | だいず     |  100 |          2
+(19 行)
+kouhai=>
+</code>
+表示された 売上年=2006 の並びに差異があることがわかります。
+^ フィルタあり  ^ フィルタなし  ^
+|<code sql>
+   | すいか     |   88 |          2    ←
+   | まんごー   |   88 |          3    ←
+   | もも       |   88 |          4    ←
+</code>|<code sql>
+   | もも       |   88 |          2    ←
+   | まんごー   |   88 |          3    ←
+   | すいか     |   88 |          4    ←
+</code>|
+これは表示順を明示していないので、実行するSQLによって表示順は変わる、という例です。\\
+表示順は変わらないかもしれませんし、暗黙でソートがかかっているデータベース製品もあるかもしれません。
+{{tag> SQL PostgreSQL Oracle rank dense_rank database 技術資料}}