Database Systems Engineer Examination

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

設問２（１）
とりあえず，やってみました。
ユーザ定義関数 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)

設問２（２）

COALESCE関数についてはこちらの記事を参照：SQL: COALESCE関数
解答例：設問２（２）
使用目的：年代，性別によっては利用履歴が存在せず，時間帯別利用者数の集計値がＮＵＬＬとなる所を０と出力するため。（５１文字）

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