AP過去問 令和6年度秋期 午前 問30
問30(問題文)
“成績” 表に対して、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 ( ａ ) AS 番号
FROM 成績) R1
WHERE R1.番号 = 1
ア ORDER BY 学生番号, 実施回
イ PARTITION BY 学生番号 ORDER BY 実施回
ウ PARTITION BY 学生番号 ORDER BY 得点 ASC
エ PARTITION BY 学生番号 ORDER BY 得点 DESC
回答・解説
なんだこの大掛かりな問題は！ってなりました。午後の問題レベルの長文問題ですね。やるなIPA。しかも、OVER句とPARTITION BY句を知っているかという問題。管理人は両方よくわかっていなかったな。
まずはSQL1文の結果がどうなるかを考えて、SQL2文の穴埋めに取り組む必要がありそうです。
SQL1文
(SELECT 学生番号, MIN(実施回) AS 初回 FROM 成績
GROUP BY 学生番号) R2
がどうなるかを考えます。GRUOP BY 学生番号なので学生番号ごとにしぼってMIN(実施回) AS 初回という絞り込みがあります。表R2は以下のようになります。
| 学生番号 | 初回 |
| S01 | 1 |
| S02 | 2 |
| S03 | 3 |
| S04 | 6 |
R1は最初に与えられた成績表そのものです。(R1) INNER JOIN (R2) ON R1.学生番号 = R2.学生番号ですので、学生番号が一致するものと内部結合処理がされ以下のような結果が得られます。
| R1.学生番号 | R1.実施回 | R1.得点 | R2.学生番号 | R2.初回 |
| S01 | 1 | 70 | S01 | 1 |
| S01 | 7 | 80 | S01 | 1 |
| S02 | 2 | 85 | S02 | 2 |
| S02 | 5 | 82 | S02 | 2 |
| S03 | 3 | 83 | S03 | 3 |
| S03 | 9 | 78 | S03 | 3 |
| S03 | 12 | 90 | S03 | 3 |
| S04 | 6 | 100 | S04 | 6 |
という表がINNER JOINによって構成されますが、更に AND R1.実施回 = R2.初回 という一致がみられる行だけになります。
| R1.学生番号 | R1.実施回 | R1.得点 | R2.学生番号 | R2.初回 |
| S01 | 1 | 70 | S01 | 1 |
| S02 | 2 | 85 | S02 | 2 |
| S03 | 3 | 83 | S03 | 3 |
| S04 | 6 | 100 | S04 | 6 |
また、表示する列は最初の1行目のSELECTの指定で、R1.学生番号, R1.実施回, R1.得点だけに射影されますので、以下のようになります。
| R1.学生番号 | R1.実施回 | R1.得点 |
| S01 | 1 | 70 |
| S02 | 2 | 85 |
| S03 | 3 | 83 |
| S04 | 6 | 100 |
これがSQL1文の結果です。これは、まだ問題の半分くらいのところです。これと同じ結果になるSQL2文を考えることが始まります。
SQL文2の全体を見ると SELECT 学生番号, 実施回, 得点, ROW_NUMBER() OVER ( ａ ) AS 番号 FROM 成績 の部分だけで実施回の若い順に並べる必要がありそうです。なぜなら、1行目のSELECT文は表示する列を指定しているだけですし、WHERE句の指定はR1.番号=1なので1行目だけを抜き出そうとしています。ROW_NUMBER()句はレコードに番号を付けて表示する行です。その行番号の付け方がOVER句で指定される部分となります。PARTITION BY句はGROUP BY句とはよく似ていますが、GROUP BY句はすでに存在する表の列ごとの値をまとめる機能であり、これから処理する内容について、まとめる扱いについて指定をするものです。
選択肢のアだと番号が連続して最後の行まで付与されてしまい。うまくいきません。ウやエでは学生番号ごとに番号が振られますが、得点順にならべても一番若い実施回を先頭にできません。イだとうまくいきそうです。OVER句は今回のように、グループ化するようなウィンドウ関数と呼ばれるものを扱うときに用いられる必須の句となっています。ROW_NUMBER句以外にもRANK、DENSE_RANK、NTILE(n)、SUM()、AVG()、MAX()、MIN()、LAG()、LEAD()、PERCENT_RANK()、CUME_DIST()のような句でもOVER句は合わせて使われます。つまりRANK付け関数・集計関数・行の相対参照・相対順位・移動ウィンドウ OVER( ウィンドウ関数 ) という組み合わせになります。
(SELECT 学生番号, 実施回, 得点, ROW_NUMBER() OVER ( PARTITION BY 学生番号 ORDER BY 実施回 ) AS 番号 FROM 成績) R1 は以下のような結果になります。
| R1.学生番号 | R1.実施回 | R1.得点 | R1.番号 |
| S01 | 1 | 70 | 1 |
| S01 | 7 | 80 | 2 |
| S02 | 2 | 85 | 1 |
| S02 | 5 | 82 | 2 |
| S03 | 3 | 83 | 1 |
| S03 | 9 | 78 | 2 |
| S03 | 12 | 90 | 3 |
| S04 | 6 | 100 | 1 |
上記の表から R1.番号 = 1 のものを抜き出すと、SQL1文の結果と同じ
| 学生番号 | 実施回 | 得点 |
| S01 | 1 | 70 |
| S02 | 2 | 85 |
| S03 | 3 | 83 |
| S04 | 6 | 100 |
が得られます。したがって
イ
が答えです。これが、午前の1問の得点にしかならないのは重すぎますね。