MySQL SQLで遊ぶ - Database JUNKY

SQLを最近忘れてきている・・・よく使うかどうがわかりませんが、集計系でちょこちょこ、「あれ？どうするんだっけ？」と毎回毎回調べるのも面倒になってきたのでメモがてら掲載することにしました。MySQLを例に書いておりますが、別のほかのDBでもいけるかなー？とか思っています。また、今回は日付関数系と、相関サブクエリを中心に掲載していきたいと思います。

・今回の検証では、以下のテーブルを利用します。 [SQL] CREATE TABLE test_users ( id int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id', user_name varchar(255) DEFAULT NULL COMMENT '名', gender varchar(255) DEFAULT NULL COMMENT '性別', birthday varchar(255) DEFAULT NULL COMMENT '誕生日', created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '作成時間', updated timestamp NULL DEFAULT NULL COMMENT '更新時間', PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='テストユーザー'; [/SQL]

・上記のテーブルに以下のようなデータを投入します。 [SQL] insert into test_users (user_name,gender,birthday,created) values ('test_user1','male','04/11/1976','2011-08-01 12:12:12'); insert into test_users (user_name,gender,birthday,created) values ('test_user2','female','12/10/1982','2011-06-01 12:12:12'); insert into test_users (user_name,gender,birthday,created) values ('test_user3','female','09/20/1978','2011-08-03 12:12:12'); insert into test_users (user_name,gender,birthday,created) values ('test_user4','male','08/20/1983','2011-08-05 12:12:12'); insert into test_users (user_name,gender,birthday,created) values ('test_user5','male','01/26/1979','2011-08-08 12:12:12'); [/SQL]

・登録の結果は以下の通りとなっております。 [SQL] mysql> select * from test_users; +----+------------+--------+------------+---------------------+---------+ | id | user_name | gender | birthday | created | updated | +----+------------+--------+------------+---------------------+---------+ | 1 | test_user1 | male | 04/11/1976 | 2011-08-01 12:12:12 | NULL | | 2 | test_user2 | female | 12/10/1982 | 2011-06-01 12:12:12 | NULL | | 3 | test_user3 | female | 09/20/1978 | 2011-08-03 12:12:12 | NULL | | 4 | test_user4 | male | 08/20/1983 | 2011-08-05 12:12:12 | NULL | | 5 | test_user5 | male | 01/26/1979 | 2011-08-08 12:12:12 | NULL | +----+------------+--------+------------+---------------------+---------+ 5 rows in set (0.00 sec) [/SQL] ・updatedを全て、今に変更します [SQL] update test_users set updated = current_timestamp; [/SQL] ・更新の結果は以下のようになりました。 [SQL] +----+------------+--------+------------+---------------------+---------------------+ | id | user_name | gender | birthday | created | updated | +----+------------+--------+------------+---------------------+---------------------+ | 1 | test_user1 | male | 04/11/1976 | 2011-08-01 12:12:12 | 2011-08-09 18:22:37 | | 2 | test_user2 | female | 12/10/1982 | 2011-06-01 12:12:12 | 2011-08-09 18:22:37 | | 3 | test_user3 | female | 09/20/1978 | 2011-08-03 12:12:12 | 2011-08-09 18:22:37 | | 4 | test_user4 | male | 08/20/1983 | 2011-08-05 12:12:12 | 2011-08-09 18:22:37 | | 5 | test_user5 | male | 01/26/1979 | 2011-08-08 12:12:12 | 2011-08-09 18:22:37 | +----+------------+--------+------------+---------------------+---------------------+ 5 rows in set (0.00 sec) [/SQL]

・そして本日の日付は以下の通りです [SQL] mysql> select current_timestamp; +---------------------+ | current_timestamp | +---------------------+ | 2011-08-09 13:08:56 | +---------------------+ [/SQL]

上記のテーブルをベースにSQLで色々遊んでみたいと思います。

▼2日前から現在までに登録したデータを抽出する [SQL] select id, user_name, gender, birthday from test_users where date(created) >= date_add(current_date, interval -2 day); [/SQL]

【説明】日付の計算にがdate_add関数を利用します。上記のカラムは、timestamp型のフィールドなので一度DATE型にキャストしております。

▼ユーザーの登録日（created)から更新日(updated)まで何日経過しているかを算出する [SQL] SELECT id, user_name, gender, birthday, created, updated, DATEDIFF(updated,created) as w_days from test_users; [/SQL]

【結果】 [SQL] +----+------------+--------+------------+---------------------+---------------------+--------+ | id | user_name | gender | birthday | created | updated | w_days | +----+------------+--------+------------+---------------------+---------------------+--------+ | 1 | test_user1 | male | 04/11/1976 | 2011-08-01 12:12:12 | 2011-08-09 18:22:37 | 8 | | 2 | test_user2 | female | 12/10/1982 | 2011-06-01 12:12:12 | 2011-08-09 18:22:37 | 69 | | 3 | test_user3 | female | 09/20/1978 | 2011-08-03 12:12:12 | 2011-08-09 18:22:37 | 6 | | 4 | test_user4 | male | 08/20/1983 | 2011-08-05 12:12:12 | 2011-08-09 18:22:37 | 4 | | 5 | test_user5 | male | 01/26/1979 | 2011-08-08 12:12:12 | 2011-08-09 18:22:37 | 1 | +----+------------+--------+------------+---------------------+---------------------+--------+ [/SQL] 【説明】 DATEDIFF関数を利用し、createdとupdatedの日数を返しております。

▼ユーザーの年齢を算出する

SQLは以下の通りです [SQL] select id, user_name, gender, birthday, truncate((date_format(current_date,'%Y%m%d') - date_format(STR_TO_DATE(rtrim(birthday),'%m/%d/%Y'),'%Y%m%d') )/10000,0) as age from test_users; [/SQL]

【結果】 [SQL] +----+------------+--------+------------+------+ | id | user_name | gender | birthday | age | +----+------------+--------+------------+------+ | 1 | test_user1 | male | 04/11/1976 | 35 | | 2 | test_user2 | female | 12/10/1982 | 28 | | 3 | test_user3 | female | 09/20/1978 | 32 | | 4 | test_user4 | male | 08/20/1983 | 27 | | 5 | test_user5 | male | 01/26/1979 | 32 | +----+------------+--------+------------+------+ [/SQL]

【説明】カラム名、birthdayはchar型のフィールド且つ、日付の形式が、mm/dd/yyyy である。上記例では、文字形式であるbirthdayを一旦、DATE型に変換したあと、date_formatにて、yyyy/mm/dd形式に変換したものに、現在の日付（current_date）からマイナスしたものを10000で割った数字（小数点以下切捨て）が年齢になるといった具合です。

▼ユーザーの日別、年齢層の登録者数を集計する（男女別） [SQL] select Y.summary_date, Y.gender, Y.age_range, count(*) as cnt from ( select X.summary_date, case when X.gender = 'female' then '女性' when X.gender = 'male' then '男性' else 'unknown' end as gender, case when X.age <= 17 then '13-17' when X.age <= 24 then '18-24' when X.age <= 34 then '25-34' when X.age <= 44 then '35-44' when X.age <= 54 then '45-54' when X.age <= 200 then '55-' else 'unknown' end as age_range, X.age from ( select date(created) as summary_date, gender, truncate((date_format(current_date,'%Y%m%d') - date_format(STR_TO_DATE(rtrim(birthday),'%m/%d/%Y'),'%Y%m%d') )/10000,0) as age from test_users ) AS X ) AS Y group by Y.summary_date, Y.gender, Y.age_range; [/SQL]

【結果】 [SQL] +--------------+--------+-----------+-----+ | summary_date | gender | age_range | cnt | +--------------+--------+-----------+-----+ | 2011-06-01 | 女性 | 25-34 | 1 | | 2011-08-01 | 男性 | 35-44 | 1 | | 2011-08-03 | 女性 | 25-34 | 1 | | 2011-08-05 | 男性 | 25-34 | 1 | | 2011-08-08 | 男性 | 25-34 | 1 | +--------------+--------+-----------+-----+ [/SQL]

【説明】関数的には前回とかわりませんが、今回のSQLでは、拡張CASE式と、相関サブクエリを利用しております。相関サブクエリっていうと難しく聞こえるかもしれませんが、簡単に説明すると、一旦、下記SQLでテンポラリ的なテーブルを作成してから、そのテンポラリテーブルに対して集計を実施しているといった感じです。どのような順序で、上記の結果に至ったのかを、SQLをSQLで分割して説明してみたいと思います。

[SQL] select date(created) as summary_date, gender, truncate((date_format(current_date,'%Y%m%d') - date_format(STR_TO_DATE(rtrim(birthday),'%m/%d/%Y'),'%Y%m%d') )/10000,0) as age from test_users [/SQL] 上記SQLを実行した場合の出力結果は以下の通りです。これをXという名前のテンポラリテーブルにしております [SQL] +--------------+--------+------+ | summary_date | gender | age | +--------------+--------+------+ | 2011-08-01 | male | 35 | | 2011-06-01 | female | 28 | | 2011-08-03 | female | 32 | | 2011-08-05 | male | 27 | | 2011-08-08 | male | 32 | +--------------+--------+------+ [/SQL] 上記Xというテーブルに対して、再度集計を実行します。これによって生成されたデータをYというテーブル名にしております。 [SQL] select X.summary_date, case when X.gender = 'female' then '女性' when X.gender = 'male' then '男性' else 'unknown' end as gender, case when X.age <= 17 then '13-17' when X.age <= 24 then '18-24' when X.age <= 34 then '25-34' when X.age <= 44 then '35-44' when X.age <= 54 then '45-54' when X.age <= 200 then '55-' else 'unknown' end as age_range, X.age from X [/SQL] 上記SQLを実行した場合の出力結果は以下の通りです。これをYという名前のテンポラリテーブルにしております [SQL] +--------------+--------+-----------+------+ | summary_date | gender | age_range | age | +--------------+--------+-----------+------+ | 2011-08-01 | 男性 | 35-44 | 35 | | 2011-06-01 | 女性 | 25-34 | 28 | | 2011-08-03 | 女性 | 25-34 | 32 | | 2011-08-05 | 男性 | 25-34 | 27 | | 2011-08-08 | 男性 | 25-34 | 32 | +--------------+--------+-----------+------+ [/SQL] 上記Yというテーブルに対して、再度集計を実行を実行しているだけです。 [SQL] select Y.summary_date, Y.gender, Y.age_range, count(*) as cnt from Y; [/SQL] そしてこれが最終的なアウトプット [SQL] +--------------+--------+-----------+-----+ | summary_date | gender | age_range | cnt | +--------------+--------+-----------+-----+ | 2011-06-01 | 女性 | 25-34 | 1 | | 2011-08-01 | 男性 | 35-44 | 1 | | 2011-08-03 | 女性 | 25-34 | 1 | | 2011-08-05 | 男性 | 25-34 | 1 | | 2011-08-08 | 男性 | 25-34 | 1 | +--------------+--------+-----------+-----+ [/SQL]

長いSQLも小さく刻めばわかり易くなります。といった例です。

▼日付と年齢をランダムで生成する

[sql]

select FLOOR( REVERSE( RAND() ) ) %(100 - 1 + 1) + 1 as age , STR_TO_DATE( concat( '2011-',FLOOR( REVERSE( RAND() ) ) %(12 - 1 + 1) + 1, '-', FLOOR( REVERSE( RAND() ) ) %(30 - 1 + 1) + 1 ), '%Y-%m-%d' ) as wdate, current_timestamp

[/sql]

※年齢（age）は、1歳から100歳までをランダムに出力します。 ※日付は、2011年固定で、1月～12月、1日～30日を返しております、この際、concat関数を利用し、文字列の日付を生成した後、STR_TO_DATEにて日付形式に変換しております。実際のサービスでこんなＳＱＬはやらないと思いますが、テストデータを生成する場合などに重宝したりするかもしれません。

以上、中途半端な記事になってしまいましたが、他にも色々思いつき次第、追記していきますので・・・。