平成31年度 春期 午前Ⅱ 問14
データ操作
集約関数に関する問題
ある電子商取引サイトでは，会員の属性を柔軟に変更できるように，“会員項目”表で管理することにした。“会員項目”表に対し，次の条件でSQL文を実行して結果を得る場合，SQL文のaに入れる字句はどれか。ここで，実線の下線は主キーを，NULLは値がないことを表す。
〔条件〕
(1) 同一“会員番号”をもつ複数の行によって，一人の会員の属性を表す。
(2) 新規に追加する行の行番号は，最後に追加された行の行番号に1を加えた値とする。
(3) 同一“会員番号”で同一“項目名”の行が複数ある場合，より大きい行番号の項目値を採用する。
（原典の実線の下線は …、破線の下線は〔破線の下線〕で表した）
会員項目
| 行番号 | 会員番号 | 項目名 | 項目値 |
|---|---|---|---|
| 1 | 0111 | 会員名 | 情報太郎 |
| 2 | 0111 | 最終購入年月日 | 2019-02-05 |
| 3 | 0112 | 会員名 | 情報花子 |
| 4 | 0112 | 最終購入年月日 | 2019-01-30 |
| 5 | 0112 | 最終購入年月日 | 2019-02-01 |
| 6 | 0113 | 会員名 | 情報次郎 |
〔結果〕
| 会員番号 | 会員名 | 最終購入年月日 |
|---|---|---|
| 0111 | 情報太郎 | 2019-02-05 |
| 0112 | 情報花子 | 2019-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
解説
行番号の最大で最新の行を選び、CASE式の値もMAXで1行にまとめます。
aは3か所すべてに同じ字句が入ります。まず内側の副問合せで、会員番号と項目名のグループごとに行番号の最大を取ると、1,2,3,5,6が得られます。会員0112の最終購入年月日は行4と行5のうち大きい行5が選ばれ、条件(3)のとおり後から追加した値が採用されます。次に外側では会員番号ごとにまとめ、CASE式で項目名が一致する行だけ項目値を、それ以外はNULLを返させます。集約関数のMAXはNULLを無視するので、各会員の会員名と最終購入年月日が一つずつ取り出されます。会員0113には最終購入年月日の行がないのでNULLになり、結果と一致します。縦に並んだ項目を横の列に並べ替えるこの書き方では、CASE式と集約関数を組で使うと覚えておくと読み解けます。
ほかの選択肢はなぜ違うのか
- アCOUNT：COUNTは件数を返すので、内側の副問合せは行番号ではなく1や2といった件数を返し、行番号1と2の行しか選ばれません。外側でも項目値ではなく件数が並ぶため、結果の会員名や日付は得られません。
- イDISTINCT：DISTINCTは重複を除く指定で、グループごとに一つの値を選び出す集約関数ではありません。GROUP BYでまとめたグループから行番号や項目値を一つに絞る働きをしないので、この問合せは結果の形になりません。
- エMIN：MINを使うと、内側で各グループの最小の行番号が選ばれ、会員0112の最終購入年月日には先に追加された行4の2019-01-30が残ります。より大きい行番号の項目値を採用するという条件(3)に反します。
この問題の用語
- 電子商取引インターネットなどを通じて、物やサービスを売り買いすることです。企業間のBtoBや、企業と消費者の間のBtoCなどの形があります。
出典：平成31年度 春期 データベーススペシャリスト試験 午前Ⅱ 問14
この解説に誤りを見つけたら教えてください。直して、直した記録を残します。誤りを報告する（メールが開きます）