集計メモ

忘れないようにメモ。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とか初心者レベルですから。