ROW_NUMBER を使った SQL
出典: 令和6年度 秋期 応用情報技術者試験 午前 問30 (IPA)
“成績”表に対して，SQL 文 1 と同一の結果を得るために，SQL 文 2 の a に入れる字句はどれか。
成績
| 学生番号 | 実施回 | 得点 |
|---|---|---|
| S01 | 1 | 70 |
| S01 | 7 | 80 |
| S02 | 2 | 85 |
| S02 | 5 | 82 |
| S03 | 3 | 83 |
| S03 | 9 | 78 |
| S03 | 12 | 90 |
| S04 | 6 | 100 |
［SQL 文 1］
SELECT R1.学生番号, R1.実施回, R1.得点 FROM 成績 R1
INNER JOIN
(SELECT 学生番号, MIN(実施回) AS 初回 FROM 成績
GROUP BY 学生番号) R2
ON R1.学生番号 = R2.学生番号
AND R1.実施回 = R2.初回［SQL 文 2］
SELECT 学生番号, 実施回, 得点
FROM (SELECT 学生番号, 実施回, 得点, ROW_NUMBER() OVER ( a ) AS 番号
FROM 成績) R1
WHERE R1.番号 = 1- ア ORDER BY 学生番号，実施回
- イ PARTITION BY 学生番号 ORDER BY 実施回
- ウ PARTITION BY 学生番号 ORDER BY 得点 ASC
- エ PARTITION BY 学生番号 ORDER BY 得点 DESC
正解と解説を見る
正解: イ
- ア: PARTITION BY がないと、表全体で 1 つの番号になり、番号 1 は全体で最初の 1 行 (S01 の 1 回目) だけになります。
- イ: 学生番号ごとに実施回の小さい順で番号を振るので、番号 1 は各学生の初回の行になります。SQL 文 1 と同じ結果です。
- ウ: 得点の低い順に番号を振ると、番号 1 は各学生の最低点の回になります。初回とは限りません。
- エ: 得点の高い順に番号を振ると、番号 1 は各学生の最高点の回になります。初回とは限りません。
ポイント
- SQL 文 1 は、学生ごとに最小の実施回 (初回) を求め、その回の行を取り出しています。つまり「学生ごとの初回の成績」です。
- ROW_NUMBER() OVER (PARTITION BY 列 ORDER BY 列) は、PARTITION BY で分けたグループごとに、ORDER BY の順で 1, 2, 3 … と番号を振ります。
- 学生番号ごとに分けて、実施回の小さい順に番号を振れば、番号 1 の行が各学生の初回になります。