令和3年度 秋期 午前Ⅱ 問10
データ操作
集合関数に関する問題
ある電子商取引サイトでは，会員の属性を柔軟に変更できるように，“会員項目”表で管理することにした。“会員項目”表に対し，次の条件で 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 文〕
SELECT 会員番号,
[ a ] (CASE WHEN 項目名='会員名' THEN 項目値 END) AS 会員名,
[ a ] (CASE WHEN 項目名='最終購入年月日' THEN 項目値 END)
AS 最終購入年月日
FROM ( SELECT 会員番号, 項目名, 項目値 FROM 会員項目
WHERE 行番号 IN ( SELECT [ a ] (行番号) FROM 会員項目
GROUP BY 会員番号, 項目名 )
) T
GROUP BY 会員番号
ORDER BY 会員番号- アCOUNT
- イDISTINCT
- ウMAX
- エMIN
答えと解説を見る
✓ これが正解ウMAX
解説
同じ項目は行番号の大きい行を採るので、a には MAX が入ります。
条件(3)から、同じ会員番号で同じ項目名の行が複数あるときは、行番号の大きいほうを採用します。副問合せは会員番号と項目名でグループを作り、各グループから行番号を一つ選ぶので、ここに MAX を入れると行番号 1、2、3、5、6 が選ばれ、0112 の最終購入年月日は行番号5の 2021-02-01 になります。外側では会員番号でグループを作り、CASE 式で項目名に合う行だけ項目値を残し、他の行は NULL にします。各グループで値を持つ行は高々1行なので、MAX で NULL 以外の値を取り出せます。最終購入年月日の行が無い 0113 は NULL のままで、結果と一致します。行を縦に持つ表を横に並べ替えるときは、CASE 式と集合関数を組み合わせる形をひとまとまりで覚えておくと読み解けます。
ほかの選択肢はなぜ違うのか
- アCOUNT：COUNT を入れると、副問合せが返すのは各グループの行数になります。行数は1か2なので、行番号が1と2の行しか選ばれず、さらに外側も件数を返すため、会員名や日付の値は結果に現れません。
- イDISTINCT：DISTINCT は重複を除く指定であり、グループごとに一つの値へまとめる集合関数ではありません。会員番号と項目名でグループを作った副問合せの中で、グループに含まれない行番号を一つに絞れないため、意図した SQL 文として成り立ちません。
- エMIN：MIN を入れると、副問合せは各グループの小さいほうの行番号を選ぶため、0112 の最終購入年月日は行番号4の 2021-01-30 が残ります。大きい行番号を採用するという条件(3)に反し、結果の 2021-02-01 と一致しません。
この問題の用語
- 電子商取引インターネットなどを通じて、物やサービスを売り買いすることです。企業間のBtoBや、企業と消費者の間のBtoCなどの形があります。
出典：令和3年度 秋期 データベーススペシャリスト試験 午前Ⅱ 問10
この解説に誤りを見つけたら教えてください。直して、直した記録を残します。誤りを報告する（メールが開きます）