[MySQL] 2時間毎にGROUP BYする方法

先日、タイムスタンプを含むデータをSQLで2時間毎に集計したいことがあった。

id	ts	value
ID_001	2022-04-01 00:00:01	1
ID_001	2022-04-01 00:05:01	2
ID_001	2022-04-01 00:10:01	3
ID_001	2022-04-01 00:15:01	4
ID_001	2022-04-01 00:20:01	5
...	...	...
ID_002	2022-04-01 00:00:02	2
ID_002	2022-04-01 00:05:02	3
ID_002	2022-04-01 00:10:02	4
ID_002	2022-04-01 00:15:02	5
ID_002	2022-04-01 00:20:02	6
...	...	...

こういうデータがあり、GROUP BYでid毎、2時間毎(00:00:00-, 02:00:00-, 04:00:00-, ...)に集計したいとする。
TIMESTAMP型のデータからHOUR()で時分秒の時だけ取り出せるらしいので、次のようにしてみたら、望み通りの集計はできたのだが、結果のタイムスタンプが2時間毎に切り下げられない（時分秒の時が偶数かつ分秒が00:00にならない）のが不満だった。

●コード

/* This doesn't return round timestamp */
SELECT id, ts, avg(value)
FROM testdata
GROUP BY id, DATE(ts), HOUR(ts) DIV 2;

●実行結果

id	ts	avg(value)
ID_001	2022-04-01 00:00:01	12.5000
ID_001	2022-04-01 02:00:01	36.5000
ID_001	2022-04-01 04:00:01	60.5000
ID_001	2022-04-01 06:00:01	84.5000
ID_002	2022-04-01 00:00:02	13.5000
ID_002	2022-04-01 02:00:02	37.5000
ID_002	2022-04-01 04:00:02	61.5000
ID_002	2022-04-01 06:00:02	85.5000
...	...	...

Webで探すと、例えば30分毎のGROUP BYならUNIX_TIMESTAMPを30分毎に切り下げるという方法がよく知られているようである。

●コード

/* Group by 30 min */
SELECT
  id,
  FROM_UNIXTIME((UNIX_TIMESTAMP(ts) DIV (30*60)) * 30*60) AS ts_new,
  avg(value)
FROM testdata
GROUP BY id, ts_new;

●実行結果

id	ts_new	avg(value)
ID_001	2022-04-01 00:00:00	3.5000
ID_001	2022-04-01 00:30:00	9.5000
ID_001	2022-04-01 01:00:00	15.5000
ID_001	2022-04-01 01:30:00	21.5000
ID_001	2022-04-01 02:00:00	27.5000
...	...	...

しかし、同じ方法で2時間毎にすると、例えばMySQLのタイムゾーン設定がJST（日本時間）だと、時分秒の時に奇数が加えられてしまうので、うまくいかない。

●コード

/* This doesn't work well because of odd time zone +09:00 */
SELECT
  id,
  FROM_UNIXTIME((UNIX_TIMESTAMP(ts) DIV (2*60*60)) * 2*60*60) as ts_new,
  avg(value)
FROM testdata
GROUP BY id, ts_new;

●実行結果

id	ts_new	avg(value)
ID_001	2022-03-31 23:00:00	6.5000
ID_001	2022-04-01 01:00:00	24.5000
ID_001	2022-04-01 03:00:00	48.5000
ID_001	2022-04-01 05:00:00	72.5000
ID_001	2022-04-01 07:00:00	90.5000
ID_002	2022-03-31 23:00:00	7.5000
ID_002	2022-04-01 01:00:00	25.5000
ID_002	2022-04-01 03:00:00	49.5000
ID_002	2022-04-01 05:00:00	73.5000
ID_002	2022-04-01 07:00:00	91.5000
...	...	...

結局、元の案でタイムスタンプを2時間単位に切り下げたものに作り直すようにした。

●コード

SELECT
  id,
  DATE_ADD(DATE(ts),
  INTERVAL HOUR(ts) DIV 2 * 2 HOUR) as ts,
  avg(value)
FROM testdata
GROUP BY id, DATE(ts), HOUR(ts) DIV 2;

●実行結果

id	ts	avg(value)
ID_001	2022-04-01 00:00:00	12.5000
ID_001	2022-04-01 02:00:00	36.5000
ID_001	2022-04-01 04:00:00	60.5000
ID_001	2022-04-01 06:00:00	84.5000
ID_002	2022-04-01 00:00:00	13.5000
ID_002	2022-04-01 02:00:00	37.5000
ID_002	2022-04-01 04:00:00	61.5000
ID_002	2022-04-01 06:00:00	85.5000
...	...	...

●テストデータ作成用コード

DROP TABLE IF EXISTS testdata;
CREATE TABLE testdata (
  id CHAR(10),
  ts TIMESTAMP,
  value INT,
  PRIMARY KEY (id, ts)
);

DROP PROCEDURE IF EXISTS make_testdata;
DELIMITER //
CREATE PROCEDURE make_testdata(IN id_num INT, data_num INT)
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE j INT DEFAULT 0;

  /* stored procedures with a lot of INSERT without this is 20x slower */
  SET autocommit = 0;

  /* make test data */
  SET i = 1;
  WHILE i <= id_num DO
    SET j = 0;
    WHILE j < data_num DO
      INSERT INTO testdata VALUES(
        CONCAT('ID_', LPAD(i, 3, '0')),
        DATE_ADD('2022-04-01 00:00:00', INTERVAL i + j*300 SECOND), /* every 5 minuts */
        i + j
      );
      SET j = j + 1;
    END WHILE;
  SET i = i + 1;
  END WHILE;

  SET autocommit = 1;
END
//
DELIMITER ;

CALL make_testdata(10, 96);

Weblog on mebius.tokaichiba.jp

[MySQL] 2時間毎にGROUP BYする方法

コメント