2005年05月03日

[ テクニカルエンジニア(データベース)/H17 ]

H17午後I 問3設問2

H17午後I 問3
会員管理システムのSQL文

設問2(1)
とりあえず,やってみました。
ユーザ定義関数 GetAge10( )は,作成せずテーブル「会員」に列「年代」を追加しました。
あと,
「 BETWEEN taikan '1200' and '1700' 」がうまく動作しなかったので
「 taikan >= '1200' and taikan <='1700' 」としています。
※初めてMySQLをマトモ(?)に使いました。。。

mysql> select * from kaiin;
+---------+------+------------+------------+--------+--------+
| kaiinno | sex  | birth      | nyukai     | taikai | nendai |
+---------+------+------------+------------+--------+--------+
|   10001 | 男   | 1980-01-10 | 2004-09-07 | NULL   | 20     |
|   10002 | 男   | 1972-02-15 | 2004-10-06 | NULL   | 30     |
|   10003 | 女   | 1972-03-20 | 2004-11-05 | NULL   | 30     |
|   10004 | 男   | 1968-04-25 | 2004-12-04 | NULL   | 30     |
|   10005 | 女   | 1973-05-01 | 2005-01-03 | NULL   | 30     |
|   10006 | 男   | 1969-06-05 | 2005-02-02 | NULL   | 30     |
|   10007 | 女   | 1981-07-01 | 2005-03-01 | NULL   | 20     |
+---------+------+------------+------------+--------+--------+
7 rows in set (0.00 sec)
mysql> select * from riyourireki;
+------------+---------+--------+--------+
| riyou      | kaiinno | nyukan | taikan |
+------------+---------+--------+--------+
| 2005-03-06 |   10003 | 1030   | 1300   |
| 2005-03-07 |   10005 | 1200   | 1430   |
| 2005-03-07 |   10002 | 2000   | 2200   |
| 2005-03-07 |   10007 | 1900   | 2200   |
| 2005-03-08 |   10005 | 1200   | 1430   |
| 2005-03-08 |   10004 | 1700   | 1900   |
| 2005-03-09 |   10005 | 1200   | 1430   |
| 2005-03-09 |   10007 | 1900   | 2200   |
+------------+---------+--------+--------+
8 rows in set (0.00 sec)
mysql> select nendai,sex,count(a.kaiinno) as a1, coalesce(sum(b1),0) as a2,
    -> coalesce(sum(b2),0) as a3, coalesce(sum(b3),0) as a4
    -> from(select nendai,sex,kaiinno from kaiin
    -> where (taikai is null or taikai > '2005-03-31')
    -> and nyukai <= '2005-03-31') as a left outer join
    -> ( select kaiinno,
    -> sum(case when nyukan < '1200' then 1 else 0 end ) as b1,
    -> sum(case when taikan >= '1200' and taikan <='1700' then 1 else 0 end) as b2,
    -> sum(case when taikan > '1700' then 1 else 0 end) as b3
    -> from riyourireki where riyou between '2005-03-01' and '2005-03-31'
    -> group by kaiinno) as b
    -> on a.kaiinno = b.kaiinno
    -> group by nendai,sex
    -> order by nendai,sex;
+--------+------+----+------+------+------+
| nendai | sex  | a1 | a2   | a3   | a4   |
+--------+------+----+------+------+------+
| 20     | 男   |  1 |    0 |    0 |    0 |
| 20     | 女   |  1 |    0 |    0 |    2 |
| 30     | 男   |  3 |    0 |    0 |    2 |
| 30     | 女   |  2 |    1 |    4 |    0 |
+--------+------+----+------+------+------+
4 rows in set (0.00 sec)
と,いうことで。 解答例:設問2(1) h:2  i:1  j:4  k:0

設問2(2)

COALESCE関数についてはこちらの記事を参照:SQL: COALESCE関数
解答例:設問2(2)
使用目的:年代,性別によっては利用履歴が存在せず,時間帯別利用者数の集計値がNULLとなる所を0と出力するため。(51文字)


意図した状況が発生する年代と性別。
利用履歴が存在せずA2,A3,A4のすべてがNULLとなり,0に置き換えられるのは20代男性である。
解答例:設問2(2)
年代:20
性別:男

Posted by g@kko at 2005/05/03 00:18 | 個別記事表示 | コメントを見る (4) |
この記事をLicWikiに埋め込む:
コメント
2 :おくやみ:05/05/03 08:21:52 [RES]

うーん。やっぱりhは2なんですねぇ。調査ありがとうございます。
まだ4になる気がしてるのですが、あとは自分で調べてみます。


3 :おくやみ:05/05/03 10:26:57 [RES]

B中のGROUP BYを忘れていました。Bは5行になるわけですね。
MYSQL入れる必要なかった。。。。


4 :g@kko:05/05/03 15:40:54 [RES]

>>2-3 :おくやみ
私も本試験でhを4にしたクチです。はい。

Aは「FROM 会員」なのに,慌てて「利用履歴」を数えて間違ったんだろうと思っていましたが。。。もう少し奥があったようですね。


Aが会員そのままの7行
Bが「GROUP BY 会員番号」されることで,5行+外結合によるNULL行が2行

この状態であれば,A1に限定すれば,

mysql> select nendai,sex,count(a.kaiinno) as a1
-> from (select nendai,sex,kaiinno from kaiin
-> where (taikai is null or taikai > '2005-03-31')
-> and nyukai <= '2005-03-31' ) as a
-> group by nendai,sex
-> order by nendai,sex;

と等価になり,

+--------+------+----+
| nendai | sex  | a1 |
+--------+------+----+
| 20     | 男   |  1 |
| 20     | 女   |  1 |
| 30     | 男   |  3 |
| 30     | 女   |  2 |
+--------+------+----+
4 rows in set (0.28 sec)
となるわけですな。

GROUP BY 会員番号 ) AS B の行は何気に見ていたような気がする。。


5 :おくやみ:05/05/03 17:05:14 [RES]

最初作成者がCOUNT句にDISTINCTを忘れたのかなと思ってましたが、 過去問で DISTINCT つける問題で、GROUP BYでもいける問題がありました。思い出していれば。。。。という感じですが、午後1はまあ大丈夫かなと。
問題は午後2なんですよねー。