SQLでグループごとに上位数件を「同率を考慮して」取得する方法

1. やりたいこと

次のようなテーブル 算数のテストの点数表 がある。

出席番号	係	点数
3201	黒板係	60
3202	給食係	60
3203	給食係	75
:	:	:

これを係ごとに点数上位を5名ずつ取得することを考える。

算数のテストの点数表 の全体を見る

SELECT * FROM 算数のテストの点数表;

出席番号	係	点数
3201	黒板係	60
3202	給食係	60
3203	給食係	75
3204	給食係	75
3205	給食係	63
3206	給食係	75
3207	黒板係	40
3208	給食係	75
3209	給食係	51
3210	飼育係	48
3211	飼育係	80
3212	飼育係	86
3213	給食係	75
3214	黒板係	100
3215	給食係	75
3216	黒板係	74
3217	黒板係	90
3218	飼育係	80
3219	黒板係	97
3220	飼育係	80
3221	黒板係	47
3222	飼育係	52
3223	飼育係	80
3224	黒板係	56
3225	飼育係	62
3226	黒板係	52
3227	黒板係	56
3228	飼育係	80
3229	飼育係	98
3230	給食係	41

( 算数のテストの点数表 の全体を見る ここまで)

係ごとに、点数が高い順に並べたものを見る

SELECT * FROM 算数のテストの点数表
ORDER BY 係 ASC, 点数 DESC;

出席番号	係	点数
3203	給食係	75
3204	給食係	75
3206	給食係	75
3208	給食係	75
3213	給食係	75
3215	給食係	75
3205	給食係	63
3202	給食係	60
3209	給食係	51
3230	給食係	41
3229	飼育係	98
3212	飼育係	86
3211	飼育係	80
3218	飼育係	80
3220	飼育係	80
3223	飼育係	80
3228	飼育係	80
3225	飼育係	62
3222	飼育係	52
3210	飼育係	48
3214	黒板係	100
3219	黒板係	97
3217	黒板係	90
3216	黒板係	74
3201	黒板係	60
3224	黒板係	56
3227	黒板係	56
3226	黒板係	52
3221	黒板係	47
3207	黒板係	40

(係ごとに、点数が高い順に並べたものを見る ここまで)

CREATE TABLE文, INSERT INTO文 を見る

mysql

CREATE TEMPORARY TABLE 算数のテストの点数表 (
    出席番号 INT PRIMARY KEY,
    係       TEXT,
    点数     INT
);

INSERT INTO 算数のテストの点数表 VALUES
(3201, "黒板係", 60),
(3202, "給食係", 60),
(3203, "給食係", 75),
(3204, "給食係", 75),
(3205, "給食係", 63),
(3206, "給食係", 75),
(3207, "黒板係", 40),
(3208, "給食係", 75),
(3209, "給食係", 51),
(3210, "飼育係", 48),
(3211, "飼育係", 80),
(3212, "飼育係", 86),
(3213, "給食係", 75),
(3214, "黒板係", 100),
(3215, "給食係", 75),
(3216, "黒板係", 74),
(3217, "黒板係", 90),
(3218, "飼育係", 80),
(3219, "黒板係", 97),
(3220, "飼育係", 80),
(3221, "黒板係", 47),
(3222, "飼育係", 52),
(3223, "飼育係", 80),
(3224, "黒板係", 56),
(3225, "飼育係", 62),
(3226, "黒板係", 52),
(3227, "黒板係", 56),
(3228, "飼育係", 80),
(3229, "飼育係", 98),
(3230, "給食係", 41);

(CREATE TABLE文, INSERT INTO文 を見る ここまで)

2. 直面する問題

一般に、「上位◯件を取得する」ことを考えるとき、「同率順位」の問題が発生する。

今回は「上位5名を取得する」が、例えば同率5位が2名以上いた場合にどう扱うか悩むことになる。

• 同率5位を取得しない → 上位4名しか取得できない
• 同率5位をすべて取得する → 上位6名以上を取得せざるを得ない

となり、 同率5位を平等に扱う限り、「上位ピッタリ5名を取得する」ことが出来ない というジレンマに陥る。

一般に、「上位$n$件を取得する」ことを考えるとき...

一般に、「上位$n$件を取得する」ことを考えるとき、
同率$i(\leq n)$位 が $n-i+2$件以上 ($=t$件) ある場合に

• 同率$i$位を取得しない → 上位 $i-1$ 件しか取得できない
  • $i\leq n$ なので、
    $i-1 \leq n-1$ である。
    つまり、 $n-1$件以下しか取得できない
• 同率$i$位をすべて取得する → 上位 $i+t-1$ 件も取得することになる
  • $t \geq n-i+2$ なので、
    $i+t-1 \geq i+(n-i+2)-1 = n+1$ である。
    つまり、 $n+1$件以上取得することになる

となり、 同率$i(\leq n)$位を平等に扱う限り、「上位ピッタリ$n$件を取得する」ことが出来ない。

(一般に、「上位$n$件を取得する」ことを考えるとき... ここまで)

3. 3つの妥協方法

妥協方法は3つ考えられる。それぞれ「縮小主義」、「拡大主義」、「固定主義」と名付けることにする。

3-1. 縮小主義

縮小主義は、同率5位が2名以上いる場合、同率5位を一切取得しない方法だ。
この方法では、上位4名しか取得できない。

一般的に言えば、「上位$n$件以下」を取得する方法だ。
同率$i(\leq n)$位が $n-i+2$件以上ある場合に、
同率$i$位を取得せず、上位 $i-1$ 件 ($n$件未満)を取得する。

3-2. 拡大主義

拡大主義は、同率5位が2名以上いる場合、同率5位をすべて取得する方法だ。
この方法では、上位6名以上を取得することになる。

一般的に言えば、「上位$n$件以上」を取得する方法だ。
同率$i(\leq n)$位が $n-i+2$件以上 ($=t$件) ある場合に、
同率$i$位をすべて取得して、上位 $i+t-1$ 件 ($n$件超え)を取得する。

3-3. 固定主義

固定主義は、同率5位が2名以上いる場合、同率5位から何らかの方法で1名だけ選んで取得する方法だ。
この方法では、上位5名が取得できる。

一般的に言えば、「上位$n$件ちょうど」を取得する方法だ。
同率$i(\leq n)$位が $n-i+2$件以上ある場合に、
同率$i$位から何らかの方法で$n-i+1$名だけ選んで取得して、上位 $n$ 件を取得する。

3-4. 3つの方法の比較

下の表は、上位5名を取りたい場合で、同率5位が2名以上いる場合の比較である。

	縮小主義	拡大主義	固定主義
同率5位に対して平等か	はい	はい	いいえ
常にピッタリ上位5名を取れるか※	いいえ	いいえ	はい
取得人数	4名	6名以上	5名
(5位が同率でなかった場合の取得人数)	5名	5名	5名
同率5位の扱い	一切含めない	すべて含める	1名だけ選んで含める
何位まで含めるか	4位	5位	部分的に5位

※ 元の人数が5名未満の場合を除く

一般に...

	縮小主義	拡大主義	固定主義
同率$i(\leq n)$位に対して平等か	はい	はい	いいえ
常にピッタリ上位$n$件を取れるか※	いいえ	いいえ	はい
$\exists i$ 同率$i$位が $n-i+2$件以上($=t$)の時の取得件数	$i-1$	$i+t-1$	$n$
↑は$n$件...	未満	超え	ちょうど
$\forall i$ 同率$i$位が $n-i+1$件以下の時の取得件数は$n$件...	ちょうど	ちょうど	件ちょうど
$n-i+2$件以上ある時の同率$i$位の扱い	一切含めない	すべて含める	$n-i+1$件だけ選んで含める
同率$i$位が $n-i+2$件以上の時、何位まで含めるか	$i-1$位	$i$位	部分的に$i$位

※ 元のレコード数が$n$件未満の場合を除く

(一般に... ここまで)

4. SQL でそれぞれの方法を試す

SQL を用いて、拡大主義、縮小主義、固定主義のそれぞれの方法で、
算数のテストの点数表 から 係 ごとに 点数 上位を5名ずつ取得してみよう。

4-1. 拡大主義

拡大主義のSQLは、次のようにすればよい。

拡大主義

SELECT * 
FROM (
    SELECT
        出席番号, 係, 点数,
        RANK() OVER (
            PARTITION BY 係
            ORDER BY 点数 DESC
        ) AS 順位
    FROM 算数のテストの点数表
) AS 表1
WHERE 順位 <= 5
ORDER BY 係, 順位;

上記SQLの結果は次のとおり。

出席番号	係	点数	順位
3203	給食係	75	1
3204	給食係	75	1
3206	給食係	75	1
3208	給食係	75	1
3213	給食係	75	1
3215	給食係	75	1
3229	飼育係	98	1
3212	飼育係	86	2
3211	飼育係	80	3
3218	飼育係	80	3
3220	飼育係	80	3
3223	飼育係	80	3
3228	飼育係	80	3
3214	黒板係	100	1
3219	黒板係	97	2
3217	黒板係	90	3
3216	黒板係	74	4
3201	黒板係	60	5

拡大主義の方針通り、各係について同率によって上位5名を超えてしまう場合、その同率順位をすべてランキングに含めている。

• 給食係は同率1位が6名いる。
• 飼育係は同率3位が5名いる。
• 黒板係は同率5位がいないので、5位まで含めて上位5名が表示されている。

4-2. 縮小主義

縮小主義のSQLは、次のようにすればよい。

縮小主義

WITH 拡大主義 AS (
    SELECT * 
    FROM (
        SELECT
            出席番号, 係, 点数,
            ROW_NUMBER() OVER (
                PARTITION BY 係
                ORDER BY 点数 DESC
            ) AS 連番,
            RANK() OVER (
                PARTITION BY 係
                ORDER BY 点数 DESC
            ) AS 順位
        FROM 算数のテストの点数表
    ) AS 表1
    WHERE 順位 <= 5
),
係ごとのBEST5の最低順位 AS (
    SELECT DISTINCT
        表1.係,
        ( /* 5人目の順位 */
            SELECT MAX(表2.順位)
            FROM 拡大主義 AS 表2
            WHERE   表1.係 = 表2.係
                AND 表2.連番 = 5
        ) 
        - 
        ( /* 5人目と6人目が同じ順位なら1, そうでなければ0 */
            (
                SELECT MAX(表3.連番) 
                FROM 拡大主義 AS 表3
                WHERE 表1.係 = 表3.係
            ) > 5
        ) AS 最低順位
    FROM 拡大主義 AS 表1
)
SELECT 
    表1.出席番号, 表1.係, 表1.点数, 表1.順位
FROM 拡大主義 AS 表1
WHERE
    表1.順位 <= (
        SELECT MAX(表2.最低順位)
        FROM 係ごとのBEST5の最低順位 AS 表2
        WHERE 表2.係 = 表1.係
    )
ORDER BY 表1.係, 表1.順位;

上記SQLの結果は次のとおり。

出席番号	係	点数	順位
3229	飼育係	98	1
3212	飼育係	86	2
3214	黒板係	100	1
3219	黒板係	97	2
3217	黒板係	90	3
3216	黒板係	74	4
3201	黒板係	60	5

縮小主義の方針通り、各係について同率によって上位5名を超えてしまう場合、その同率順位をランキングから落とされている。

• 給食係は同率1位が6名いて、これを含めると上位5名を超えてしまうため、一切表示されない(=0位までが表示されている)。
• 飼育係は同率3位が5名いて、これを含めると上位5名を超えてしまうため、同率3位を落として2位までが表示されている。
• 黒板係は同率5位がいないので、5位まで含めて上位5名が表示されている。

4-2-1. クエリの解説

まず、サブクエリで

• 係ごとの点数上位5位 (拡大主義)
• 係ごとの、縮小主義の結果に表示されるための「最低順位」

を求め、最後にメインクエリで、拡大主義の結果に対して、最低順位以内の行だけ表示するようにフィルタをかけて縮小主義の結果を得ている。

例えば "飼育係" は、点数が高い順に (1位), (2位), (3位), (3位), (3位), (3位), (3位) と続き、3位まで含めると上位5名を超えてしまう。そのため、縮小主義では上位2位までしか表示しない。
これを最低順位が2位であるという。

4-2-1-1. 拡大主義 サブクエリ

拡大主義

SELECT * 
FROM (
    SELECT
        出席番号, 係, 点数,
        ROW_NUMBER() OVER (
            PARTITION BY 係
            ORDER BY 点数 DESC
        ) AS 連番,
        RANK() OVER (
            PARTITION BY 係
            ORDER BY 点数 DESC
        ) AS 順位
    FROM 算数のテストの点数表
) AS 表1
WHERE 順位 <= 5

拡大主義 サブクエリ では、係ごとの点数上位5位以内を拡大主義の方法で取得している。
同時に、係ごとに、点数の高い順に各生徒の「連番」と「順位」 が計算されている。

• 順位 は通常のランキングと同じで、同率 $i$ 位に対して一律に $i$ が与えられる。
  • MySQL 8.0以降では RANK() OVER (PARTITION BY (〇〇ごと) ORDER BY (ランキングしたいスコア) DESC) で与えられる
• 連番 は、通し番号であり、同率 $i$ 位の$j$名 に対して、 $i$, $i+1$, $i+2$, ..., $i+j-1$ のように、異なる番号が与えられる。
  • MySQL 8.0以降では ROW_NUMBER() OVER (PARTITION BY (〇〇ごと) ORDER BY (ランキングしたいスコア) DESC) で与えられる

SELECT * FROM 拡大主義; の結果は次のとおり

出席番号	係	点数	連番	順位
3203	給食係	75	1	1
3204	給食係	75	2	1
3206	給食係	75	3	1
3208	給食係	75	4	1
3213	給食係	75	5	1
3215	給食係	75	6	1
3229	飼育係	98	1	1
3212	飼育係	86	2	2
3211	飼育係	80	3	3
3218	飼育係	80	4	3
3220	飼育係	80	5	3
3223	飼育係	80	6	3
3228	飼育係	80	7	3
3214	黒板係	100	1	1
3219	黒板係	97	2	2
3217	黒板係	90	3	3
3216	黒板係	74	4	4
3201	黒板係	60	5	5

4-2-1-2. 係ごとのBEST5の最低順位 サブクエリ

係ごとのBEST5の最低順位

SELECT DISTINCT
    表1.係,
    ( /* 5人目の順位 */
        SELECT MAX(表2.順位)
        FROM 拡大主義 AS 表2
        WHERE   表1.係 = 表2.係
            AND 表2.連番 = 5
    ) 
    - 
    ( /* 5人目と6人目が同じ順位なら1, そうでなければ0 */
        (
            SELECT MAX(表3.連番) 
            FROM 拡大主義 AS 表3
            WHERE 表1.係 = 表3.係
        ) > 5
    ) AS 最低順位
FROM 拡大主義 AS 表1

係ごとのBEST5の最低順位 サブクエリでは、各係について、縮小主義で上位5名を表示する場合、何位までを表示すればいいかを求めている。

最低順位カラムは、 (5人目の順位) - (5人目と6人目が同じ順位なら1, そうでなければ0) という引き算で求められている。

例えば

• 3～7人目が同率3位, 8人目が8位 の場合...
  • (5人目の順位) = 3
  • (5人目と6人目が同じ順位なら1, そうでなければ0) = 1
  • よって引き算の結果は 2
  • よって最低順位は 2 (縮小主義では2位まで表示)
• 3～5人目が同率3位, 6人目が6位 の場合...
  • (5人目の順位) = 3
  • (5人目と6人目が同じ順位なら1, そうでなければ0) = 0
  • よって引き算の結果は 3
  • よって最低順位は 3 (縮小主義では3位まで表示)

となる。

(5人目の順位)について

(5人目の順位)

/* 表1は外側の「拡大主義」テーブル */
SELECT MAX(表2.順位)
FROM 拡大主義 AS 表2
WHERE   表1.係 = 表2.係
    AND 表2.連番 = 5

(5人目の順位) は、相関サブクエリとなっている。
相関サブクエリとは、 例えば

相関サブクエリ

SELECT 
    (SELECT f(外側.x, 内側.y) FROM 内側)
FROM 外側;

のように、「内側のSQLで、外側のカラムを利用する」ような内側のSQLを言う。
今回は、 WHERE 表1.係 = 表2.係 にて、 内側(表2)のSQLで外側のカラム(表1.係)を利用しているため、相関サブクエリと言える。

相関サブクエリは、外側の各行に対して評価される。
例えば、外側 (表1) の 係 カラムが '給食係' のとき、(5人目の順位) は

(表1.係 = '給食係' のとき)

SELECT MAX(表2.順位)
FROM 拡大主義 AS 表2
WHERE   '給食係' = 表2.係
    AND 表2.連番 = 5

のようになり、 '給食係'の、連番=5 における 順位, つまり、給食係の上位5人目の順位をとってくることが出来る。

これだけなら、 単に SELECT 表2.順位 FROM ... とすればよいようにも思えるが、
結果が スカラ (1行1列) であることを明らかにするため、結果を強制的に1行に出来る集約関数 MAX を付けて、 SELECT MAX(表2.順位) FROM ... としている。

(5人目と6人目が同じ順位なら1, そうでなければ0)について

(5人目と6人目が同じ順位なら1, そうでなければ0)

/* 表1は外側の「係ごとの連番と順位BEST5」テーブル */
(
    SELECT MAX(表3.連番) 
    FROM 拡大主義 AS 表3
    WHERE 表1.係 = 表3.係
) > 5

(5人目と6人目が同じ順位なら1, そうでなければ0) も、相関サブクエリである。
例えば、外側 (表1) の 係 カラムが '飼育係' のとき、(5人目と6人目が同じ順位なら1, そうでなければ0) は

(表1.係 = '飼育係' のとき)

(
    SELECT MAX(表3.連番) 
    FROM 拡大主義 AS 表3
    WHERE '飼育係' = 表3.係
) > 5

のようになり、 ('飼育係'の、連番 の最大値, つまり、飼育係の上位5位の人数) が 5 を超えるなら 1 を、超えないなら 0 をとってくることが出来る。

飼育係の「上位5位の人数が 5 を超える／超えない」とは、飼育係の「上位5人目と6人目が同じ順位である／ない」ことを意味する。
なぜなら、

• もし5人目と6人目が同じ順位であれば、
  • 6人目も上位5位に入ってくるので、
  • 上位5位は少なくとも 6 名以上になるし、
• もし5人目と6人目が別順位であれば、
  • 6人目は上位5位には入ってこないから、
  • 上位5位は 5 名になる

からだ。

SELECT * FROM 係ごとのBEST5の最低順位; の結果は次のとおり

係	最低順位
給食係	0
飼育係	2
黒板係	5

4-2-1-3. メインクエリ

縮小主義のメインクエリ

SELECT 
    表1.出席番号, 表1.係, 表1.点数, 表1.順位
FROM 拡大主義 AS 表1
WHERE
    表1.順位 <= (
        SELECT MAX(表2.最低順位)
        FROM 係ごとのBEST5の最低順位 AS 表2
        WHERE 表1.係 = 表2.係
    )
ORDER BY 表1.係, 表1.順位;

WHERE句 の比較では、相関サブクエリを利用している。
例えば、外側 (表1) の 係 カラムが '黒板係' のとき、WHERE句 は

(表1.係 = '黒板係' のとき)

WHERE
    表1.順位 <= (
        SELECT MAX(表2.最低順位)
        FROM 係ごとのBEST5の最低順位 AS 表2
        WHERE '黒板係' = 表2.係
    )

つまり

WHERE
    表1.順位 <= 5

となり、黒板係における最低順位と、表1の 順位 を比較することが出来る。
SELECT MAX(最低順位) FROM としているのは、相関サブクエリがスカラであることを明示するためである。

4-3. 固定主義

固定主義のSQLは、次のようにすればよい。

固定主義

WITH 拡大主義 AS (
    SELECT * 
    FROM (
        SELECT
            出席番号, 係, 点数,
            ROW_NUMBER() OVER (
                PARTITION BY 係
                ORDER BY 
                    点数 DESC,
                    出席番号 ASC /* 同率の際の優先順位 */
            ) AS 連番,
            RANK() OVER (
                PARTITION BY 係
                ORDER BY 点数 DESC
            ) AS 順位
        FROM 算数のテストの点数表
    ) AS 表1
    WHERE 順位 <= 5
)
SELECT
    出席番号, 係, 点数, 順位
FROM
    拡大主義
WHERE
    連番 <= 5
ORDER BY 係, 順位;

上記SQLの結果は次のとおり。

出席番号	係	点数	順位
3203	給食係	75	1
3204	給食係	75	1
3206	給食係	75	1
3208	給食係	75	1
3213	給食係	75	1
3229	飼育係	98	1
3212	飼育係	86	2
3211	飼育係	80	3
3218	飼育係	80	3
3220	飼育係	80	3
3214	黒板係	100	1
3219	黒板係	97	2
3217	黒板係	90	3
3216	黒板係	74	4
3201	黒板係	60	5

固定主義の方針通り、各係について同率によって上位5名を超えてしまう場合でも、強制的に5名を表示している。

• 給食係は同率1位が6名いて、これを含めると上位5名を超えてしまうが、5名を選んで表示している。
• 飼育係は同率3位が5名いて、これを含めると上位5名を超えてしまうが、同率3位から3名を選んで表示している。
• 黒板係は同率5位がいないので、5位まで含めて上位5名が表示されている。

4-3-1. クエリの解説

まず、サブクエリで

• 係ごとの点数上位5位 (拡大主義)

を求め、最後にメインクエリで、拡大主義の結果に対して、5名だけ表示するようにフィルタをかけて固定主義の結果を得ている。

4-3-1-1. 拡大主義 サブクエリ

拡大主義

SELECT * 
FROM (
    SELECT
        出席番号, 係, 点数,
        ROW_NUMBER() OVER (
            PARTITION BY 係
            ORDER BY 
                点数 DESC,
                出席番号 ASC /* 同率の際の優先順位 */
        ) AS 連番,
        RANK() OVER (
            PARTITION BY 係
            ORDER BY 点数 DESC
        ) AS 順位
    FROM 算数のテストの点数表
) AS 表1
WHERE 順位 <= 5

拡大主義 サブクエリ では、係ごとの点数上位5位以内を拡大主義の方法で取得している。
同時に、係ごとに、点数の高い順に各生徒の「連番」と「順位」 が計算されている。

• 順位 は通常のランキングと同じで、同率 $i$ 位に対して一律に $i$ が与えられる。
  • MySQL 8.0以降では RANK() OVER (PARTITION BY (〇〇ごと) ORDER BY (ランキングしたいスコア) DESC) で与えられる
• 連番 は、通し番号であり、同率 $i$ 位の$j$名 に対して、 $i$, $i+1$, $i+2$, ..., $i+j-1$ のように、異なる番号が与えられる。
  • MySQL 8.0以降では ROW_NUMBER() OVER (PARTITION BY (〇〇ごと) ORDER BY (ランキングしたいスコア) DESC) で与えられる

また、 連番 カラムに出てくる /* 同率の際の優先順位 */ の行 は、点数が同じ生徒同士の、固定主義の結果への表示されやすさを決めている。
今回は 出席番号 ASC となっているため、 点数が同じ生徒同士では、出席番号が若いほど、若い連番が与えられることになる。
4-3-1-2節でも解説するとおり、連番が 5 以内の生徒を固定主義の結果に表示するため、連番が若ければ若いほど、表示されやすい。
例えば同率5位が複数人いる場合、最も出席番号の若い1名が、固定主義の結果に表示される。

SELECT * FROM 拡大主義; の結果は次のとおり

出席番号	係	点数	連番	順位
3203	給食係	75	1	1
3204	給食係	75	2	1
3206	給食係	75	3	1
3208	給食係	75	4	1
3213	給食係	75	5	1
3215	給食係	75	6	1
3229	飼育係	98	1	1
3212	飼育係	86	2	2
3211	飼育係	80	3	3
3218	飼育係	80	4	3
3220	飼育係	80	5	3
3223	飼育係	80	6	3
3228	飼育係	80	7	3
3214	黒板係	100	1	1
3219	黒板係	97	2	2
3217	黒板係	90	3	3
3216	黒板係	74	4	4
3201	黒板係	60	5	5

4-3-1-2. メインクエリ

固定主義のメインクエリ

SELECT
    出席番号, 係, 点数, 順位
FROM
    拡大主義
WHERE
    連番 <= 5
ORDER BY 係, 順位;

連番が 5 以内の生徒を表示することで、同率がどうであれ、係ごとに上位5名を表示している