応用情報技術者 2024年秋期午前2 問30

問題文

“成績”表に対して、SQL文1と同一の結果を得るために、SQL文2のaに入れる字句はどれか。

選択肢

ア：ORDER BY 学生番号、実施回

イ：PARTITION BY 学生番号 ORDER BY 実施回（正解）

ウ：PARTITION BY 学生番号 ORDER BY 得点 ASC

エ：PARTITION BY 学生番号 ORDER BY 得点 DESC

SQLで学生の初回実施回を抽出する問題【午前2 解説】

要点まとめ

結論：SQL文2の「OVER (a)」には「PARTITION BY 学生番号 ORDER BY 実施回」を指定し、学生ごとに実施回の昇順で番号付けする必要があります。
根拠：ROW_NUMBER関数はPARTITIONでグループ分けし、ORDER BYで並び替えた順に連番を付与するため、初回実施回を1番目にできます。
差がつくポイント：PARTITION BYの有無やORDER BYの列・順序を正しく理解しないと、意図した初回の抽出ができず誤答になります。

正解の理由

イ: PARTITION BY 学生番号 ORDER BY 実施回が正解です。
この指定により、学生番号ごとにグループ化し、実施回の昇順で並べ替えた中で最初の行に番号1が付与されます。
そのため、WHERE句で番号=1を指定すると、各学生の最初の実施回の成績だけを抽出できます。
SQL文1のJOINで初回実施回を求める方法と同じ結果を得られます。

よくある誤解

ROW_NUMBER関数にORDER BYだけ指定しても、学生ごとのグループ分けがされず全体で連番が振られます。
また、得点で並べ替えると初回実施回とは異なる行が番号1になるため誤りです。

解法ステップ

SQL文1の動作を理解する：学生ごとに最小の実施回を求めている。
SQL文2のROW_NUMBER関数の役割を確認：グループ内で順序付けし連番を付ける。
学生ごとにグループ化するためPARTITION BYを指定。
実施回の昇順で並べ替え、最初の行に番号1を付けるためORDER BY 実施回を指定。
WHERE句で番号=1を指定し、初回実施回の成績を抽出。

選択肢別の誤答解説

ア: ORDER BY 学生番号、実施回
→ PARTITION BYがないため、全体で連番が振られ、学生ごとの初回抽出にならない。
イ: PARTITION BY 学生番号 ORDER BY 実施回
→ 正解。学生ごとに実施回昇順で番号付けし初回を抽出。
ウ: PARTITION BY 学生番号 ORDER BY 得点 ASC
→ 得点順で並べ替えるため、初回実施回とは異なる行が番号1になる。
エ: PARTITION BY 学生番号 ORDER BY 得点 DESC
→ 得点の降順で並べ替え、初回実施回の抽出には不適切。

補足コラム

ROW_NUMBER関数はウィンドウ関数の一種で、PARTITION BYでグループ分けし、ORDER BYで並べ替えた順に連番を付けます。
これにより、グループ内の順位付けや特定の条件に合う行の抽出が容易になります。
SQL文1のようなサブクエリとJOINを使う方法よりも、ウィンドウ関数を使う方法は可読性と効率性が高い場合があります。