MENU
アイデアを形にする( プログラミング / クラウド / フレームワーク / ライブラリ / デザイン / IT豆知識 )
  1. ホーム
  2. MySQL
  3. 日時別の集計(日ごと, 月ごと, 曜日ごと)

日時別の集計(日ごと, 月ごと, 曜日ごと)

MySQL

日別、曜日別、月別といった具合に、時間軸別にデータを集計する方法について紹介します。

目次

テスト環境

テーブル作成

CREATE TABLE `tests` (
  `column` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

テスト用に1万レコード挿入

まず、下記クエリを実行して、10レコード格納します。

INSERT INTO `tests`
(`column`)
VALUES
('2000-01-01'),
('2000-01-01'),
('2000-01-01'),
('2000-01-01'),
('2000-01-01'),
('2000-01-01'),
('2000-01-01'),
('2000-01-01'),
('2000-01-01'),
('2000-01-01');

単純結合を6回することで1万レコードになります。
(10 × 10 × 10 × 10 = 10,000)

mysql> SELECT COUNT(*) FROM `tests` t1, `tests` t2, `tests` t3, `tests` t4;
+----------+
| COUNT(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.01 sec)

単純結合を利用して1万レコード追加します。

mysql> INSERT INTO `tests`
    -> (`column`)
    -> SELECT `t1`.`column` FROM `tests` t1, `tests` t2, `tests` t3, `tests` t4;
Query OK, 10000 rows affected (0.19 sec)
Records: 10000  Duplicates: 0  Warnings: 0

ランダム日付に更新

下記クエリを実行して、2015-01-01 から730日間の間でランダムな日付に更新します。

UPDATE `tests`
SET `column` = ADDTIME(CONCAT_WS(' ','2015-01-01' + INTERVAL RAND() * 730 DAY, '00:00:00'),
                       SEC_TO_TIME(FLOOR(0 + (RAND() * 86401))));

集計

年別に集計

SELECT DATE_FORMAT(`column`, '%Y') as `grouping_column`,
       COUNT(`column`) as count
FROM `tests`
GROUP BY `grouping_column`;
mysql> SELECT DATE_FORMAT(`column`, '%Y') as `grouping_column`,
    ->        COUNT(`column`) as count
    -> FROM `tests`
    -> GROUP BY `grouping_column`;
+-----------------+-------+
| grouping_column | count |
+-----------------+-------+
| 2015            |  5007 |
| 2016            |  5003 |
+-----------------+-------+
2 rows in set (0.01 sec)

月別に集計

SELECT DATE_FORMAT(`column`, '%Y-%m') as `grouping_column`,
       COUNT(`column`) as count
FROM `tests`
GROUP BY `grouping_column`;
mysql> SELECT DATE_FORMAT(`column`, '%Y-%m') as `grouping_column`,
    ->        COUNT(`column`) as count
    -> FROM `tests`
    -> GROUP BY grouping_column;
+-----------------+-------+
| grouping_column | count |
+-----------------+-------+
| 2015-01         |   417 |
| 2015-02         |   434 |
| 2015-03         |   454 |
| 2015-04         |   422 |
| 2015-05         |   453 |
| 2015-06         |   388 |
| 2015-07         |   462 |
| 2015-08         |   403 |
| 2015-09         |   374 |
| 2015-10         |   403 |
| 2015-11         |   378 |
| 2015-12         |   419 |
| 2016-01         |   411 |
| 2016-02         |   399 |
| 2016-03         |   414 |
| 2016-04         |   426 |
| 2016-05         |   413 |
| 2016-06         |   406 |
| 2016-07         |   412 |
| 2016-08         |   432 |
| 2016-09         |   412 |
| 2016-10         |   427 |
| 2016-11         |   406 |
| 2016-12         |   445 |
+-----------------+-------+
24 rows in set (0.03 sec)

曜日別に集計

SELECT DATE_FORMAT(`column`, '%w') as `grouping_column`,
       COUNT(`column`) as count
FROM `tests`
GROUP BY `grouping_column`;
mysql> SELECT DATE_FORMAT(`column`, '%w') as `grouping_column`,
    ->        COUNT(`column`) as count
    -> FROM `tests`
    -> GROUP BY `grouping_column`;
+-----------------+-------+
| grouping_column | count |
+-----------------+-------+
| 0               |  1455 |
| 1               |  1400 |
| 2               |  1436 |
| 3               |  1374 |
| 4               |  1436 |
| 5               |  1474 |
| 6               |  1435 |
+-----------------+-------+
7 rows in set (0.02 sec)

0 が日曜日です。

日別に集計

SELECT DATE_FORMAT(`column`, '%Y-%m-%d') as `grouping_column`,
       COUNT(`column`) as count
FROM `tests`
WHERE `column` >= "2015-02-01" AND `column` < "2015-03-01"
GROUP BY grouping_column;
mysql> SELECT DATE_FORMAT(`column`, '%Y-%m-%d') as `grouping_column`,
    ->        COUNT(`column`) as count
    -> FROM `tests`
    -> WHERE `column` >= "2015-02-01" AND `column` < "2015-03-01"
    -> GROUP BY grouping_column;
+-----------------+-------+
| grouping_column | count |
+-----------------+-------+
| 2015-02-01      |    18 |
| 2015-02-02      |    17 |
| 2015-02-03      |    14 |
| 2015-02-04      |    18 |
| 2015-02-05      |    12 |
| 2015-02-06      |    18 |
| 2015-02-07      |    15 |
| 2015-02-08      |    18 |
| 2015-02-09      |    17 |
| 2015-02-10      |    20 |
| 2015-02-11      |     9 |
| 2015-02-12      |    25 |
| 2015-02-13      |    14 |
| 2015-02-14      |    13 |
| 2015-02-15      |    19 |
| 2015-02-16      |    11 |
| 2015-02-17      |    14 |
| 2015-02-18      |    13 |
| 2015-02-19      |    17 |
| 2015-02-20      |    16 |
| 2015-02-21      |    15 |
| 2015-02-22      |    13 |
| 2015-02-23      |    13 |
| 2015-02-24      |    14 |
| 2015-02-25      |    13 |
| 2015-02-26      |    17 |
| 2015-02-27      |    19 |
| 2015-02-28      |    12 |
+-----------------+-------+
28 rows in set (0.00 sec)

時間別に集計

SELECT DATE_FORMAT(`column`, '%H') as `grouping_column`,
       COUNT(`column`) as count
FROM `tests`
GROUP BY `grouping_column`;
mysql> SELECT DATE_FORMAT(`column`, '%H') as `grouping_column`,
    ->        COUNT(`column`) as count
    -> FROM `tests`
    -> GROUP BY `grouping_column`;
+-----------------+-------+
| grouping_column | count |
+-----------------+-------+
| 00              |   392 |
| 01              |   415 |
| 02              |   422 |
| 03              |   435 |
| 04              |   421 |
| 05              |   418 |
| 06              |   420 |
| 07              |   422 |
| 08              |   407 |
| 09              |   383 |
| 10              |   398 |
| 11              |   458 |
| 12              |   434 |
| 13              |   381 |
| 14              |   444 |
| 15              |   470 |
| 16              |   417 |
| 17              |   432 |
| 18              |   417 |
| 19              |   422 |
| 20              |   410 |
| 21              |   388 |
| 22              |   394 |
| 23              |   410 |
+-----------------+-------+
24 rows in set (0.01 sec)
MySQL
よかったらシェアしてね！

関連記事

目次