忘れないようにメモ。MySQLの集計の例
(root@localhost) [test]> select * from access_count; +----+---------+-----------+------------+ | id | user_id | num_count | cdate | +----+---------+-----------+------------+ | 1 | 1 | 10 | 2010-08-19 | | 2 | 2 | 1 | 2010-08-19 | | 3 | 4 | 5 | 2010-08-19 | | 4 | 1 | 4 | 2010-08-20 | | 5 | 3 | 3 | 2010-08-20 | | 6 | 4 | 2 | 2010-08-20 | | 7 | 2 | 4 | 2010-08-21 | | 8 | 1 | 7 | 2010-08-22 | | 9 | 3 | 20 | 2010-08-23 | | 10 | 2 | 100 | 2010-08-24 | +----+---------+-----------+------------+ 10 rows in set (0.00 sec)
user_idでグループ化
(root@localhost) [test]> select user_id, sum(num_count) as count_all from access_count group by user_id; +---------+-----------+ | user_id | count_all | +---------+-----------+ | 1 | 21 | | 2 | 105 | | 3 | 23 | | 4 | 7 | +---------+-----------+ 4 rows in set (0.00 sec)
範囲を制限
(root@localhost) [test]> select * from access_count where cdate between '2010-08-19' and '2010-08-21'; +----+---------+-----------+------------+ | id | user_id | num_count | cdate | +----+---------+-----------+------------+ | 1 | 1 | 10 | 2010-08-19 | | 2 | 2 | 1 | 2010-08-19 | | 3 | 4 | 5 | 2010-08-19 | | 4 | 1 | 4 | 2010-08-20 | | 5 | 3 | 3 | 2010-08-20 | | 6 | 4 | 2 | 2010-08-20 | | 7 | 2 | 4 | 2010-08-21 | +----+---------+-----------+------------+ 7 rows in set (0.00 sec)
範囲制限+グループ化
(root@localhost) [test]> select user_id, sum(num_count) as count_3day from access_count where cdate between '2010-08-19' and '2010-08-21' group by user_id; +---------+------------+ | user_id | count_3day | +---------+------------+ | 1 | 14 | | 2 | 5 | | 3 | 3 | | 4 | 7 | +---------+------------+ 4 rows in set (0.01 sec)
2つの結果セットを結合する
(root@localhost) [test]> select A.user_id, A.count_all, A.count_3day from ( -> (select user_id, sum(num_count) as count_all, 0 as count_3day from access_count group by user_id) -> union all -> (select user_id, 0, sum(num_count) from access_count where cdate between '2010-08-19' and '2010-08-21' group by user_id) -> ) A; +---------+-----------+------------+ | user_id | count_all | count_3day | +---------+-----------+------------+ | 1 | 21 | 0 | | 2 | 105 | 0 | | 3 | 23 | 0 | | 4 | 7 | 0 | | 1 | 0 | 14 | | 2 | 0 | 5 | | 3 | 0 | 3 | | 4 | 0 | 7 | +---------+-----------+------------+ 8 rows in set (0.00 sec)
2つの結果セットを結合+user_idでグループ化
(root@localhost) [test]> select A.user_id, sum(A.count_all) as count_all, sum(A.count_3day) as count_3day from ( -> (select user_id, sum(num_count) as count_all, 0 as count_3day from access_count group by user_id) -> union all -> (select user_id, 0, sum(num_count) from access_count where cdate between '2010-08-19' and '2010-08-21' group by user_id) -> ) A group by A.user_id; +---------+-----------+------------+ | user_id | count_all | count_3day | +---------+-----------+------------+ | 1 | 21 | 14 | | 2 | 105 | 5 | | 3 | 23 | 3 | | 4 | 7 | 7 | +---------+-----------+------------+ 4 rows in set (0.00 sec)
0で埋めるのがポイントらしい。
最終的に求めたいのが1週間の集計だったりとかで、こういう書き方があるのかーと調べてた。
や、俺、SQLとか初心者レベルですから。