令和3年度 秋期 データベーススペシャリスト試験 午前Ⅱ 問10

テクノロジデータベースSQL・関係代数

ある電子商取引サイトでは，会員の属性を柔軟に変更できるように，“会員項目”表で管理することにした。“会員項目”表に対し，次の条件でSQL文を実行して結果を得る場合，SQL文の a に入れる字句はどれか。ここで，実線の下線は主キーを，NULLは値がないことを表す。

〔条件〕

  1. 同一“会員番号”をもつ複数の行によって，1人の会員の属性を表す。
  2. 新規に追加する行の行番号は，最後に追加された行の行番号に1を加えた値とする。
  3. 同一“会員番号”で同一“項目名”の行が複数ある場合，より大きい行番号の項目値を採用する。
会員項目
図の説明テキスト

表のキャプション: 会員項目

行番号 会員番号 項目名 項目値
1 0111 会員名 情報太郎
2 0111 最終購入年月日 2021-02-05
3 0112 会員名 情報花子
4 0112 最終購入年月日 2021-01-30
5 0112 最終購入年月日 2021-02-01
6 0113 会員名 情報次郎

※「行番号」には主キーを示す実線の下線がある（「会員番号」には下線がない）。

〔結果〕
図の説明テキスト

表のキャプション: 〔結果〕

会員番号 会員名 最終購入年月日
0111 情報太郎 2021-02-05
0112 情報花子 2021-02-01
0113 情報次郎 NULL

※見出しとデータ行の間に点線が引かれている。

〔SQL 文〕
図の説明テキスト

キャプション: 〔SQL 文〕
SQL文のコードブロック。四角枠で囲まれた空欄「a」が3箇所存在する。

SELECT 会員番号,
       <span id="q10_blank_a" class="blank-label">a</span> (CASE WHEN 項目名='会員名' THEN 項目値 END) AS 会員名,
       <span id="q10_blank_a" class="blank-label">a</span> (CASE WHEN 項目名='最終購入年月日' THEN 項目値 END)
       AS 最終購入年月日
FROM   ( SELECT 会員番号, 項目名, 項目値 FROM 会員項目
         WHERE 行番号 IN ( SELECT <span id="q10_blank_a" class="blank-label">a</span> (行番号) FROM 会員項目
                           GROUP BY 会員番号, 項目名 )
       ) T
GROUP BY 会員番号
ORDER BY 会員番号
解答・解説

正解: ウ. MAX

SQL文の構造と抽出条件

空欄 a を含むSQL文は、FROM 句に指定されたサブクエリ（表 T）で対象となる行を絞り込み、外側のクエリで横持ちの表（会員番号、会員名、最終購入年月日）に変換する構造になっています。
サブクエリ内の WHERE 句では、以下の条件で行を絞り込んでいます。

  • WHERE 行番号 IN (SELECT a(行番号) FROM 会員項目 GROUP BY 会員番号, 項目名)

これは「会員番号」と「項目名」ごとにグループ化し、その中の a(行番号) に合致する行番号を持つ行を抽出するという意味になります。

条件と図表からの特定

〔条件〕の「3. 同一“会員番号”で同一“項目名”の行が複数ある場合，より大きい行番号の項目値を採用する。」の処理を考えます。

  • 【図表: 会員項目】を見ると、会員番号 0112 は項目名「最終購入年月日」の行を2つ（行番号4と5）持っています。
  • 【図表: 〔結果〕】を見ると、会員番号 0112 の最終購入年月日は、行番号5の項目値である 2021-02-01 が出力されています。

より大きい行番号を取得してこの結果を得るためには、会員番号と項目名でグループ化した中で最大の行番号を求める必要があります。したがって、空欄 a には最大値を求める集約関数 MAX が入ります。

外側のクエリにおける集約

外側のクエリにおいても、MAX を適用することで意図した結果が得られます。
CASE 式は条件に一致するときだけ項目値を返し、一致しないときは NULL となります。GROUP BY 会員番号 で集約して MAX 関数を適用すると、NULL を無視して抽出された項目値を取り出すことができます。

各選択肢の解説

  • ア COUNT: 行数を数える関数です。より大きい行番号を取得することはできないため誤りです。
  • イ DISTINCT: 重複を排除するキーワードです。最大値を抽出することはできないため誤りです。
  • ウ MAX: 正解です。グループ内の最大の行番号を取得し、より大きい行番号の項目値を採用するという条件を満たします。
  • エ MIN: 最小値を求める関数です。条件の「より大きい行番号の項目値を採用する」に反して、より小さい行番号を取得してしまうため誤りです。

Spineチーム