AP過去問 令和6年度秋期 午前 問30

AP過去問 令和6年度秋期 午前 問題に戻る

AP過去問 令和6年度秋期 午前 問29へ

AP過去問 令和6年度秋期 午前 問31へ

問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は以下のようになります。

R2

学生番号	初回
S01	1
S02	2
S03	3
S04	6

　R1は最初に与えられた成績表そのものです。(R1) INNER JOIN (R2) ON R1.学生番号 = R2.学生番号ですので、学生番号が一致するものと内部結合処理がされ以下のような結果が得られます。

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 INNER JOIN R2 ON R1.学生番号 = R2.学生番号 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.得点だけに射影されますので、以下のようになります。

SQL1文の結果

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文の結果と同じ

SQL2文の結果

学生番号	実施回	得点
S01	1	70
S02	2	85
S03	3	83
S04	6	100

　が得られます。したがって

イ

　が答えです。これが、午前の1問の得点にしかならないのは重すぎますね。

　

AP過去問 令和6年度秋期 午前 問29へ

AP過去問 令和6年度秋期 午前 問31へ

AP過去問 令和6年度秋期 午前 問題に戻る