二度と使用する事は無さそうなのですが、 極めて限定的な要件で、RDB上で5秒(30秒)単位で集計する方法です。 (と言っても、日時文字列の末尾を切り捨てているだけです)
環境はRedshiftです。(PostgreSQLでも使えます)
他RDBMS用はこちら(SQL 日時列を5秒、30秒単位で集計する(RDBMS別) – Qiita)。
尚、日時データはシステムによって、日付・文字列・数値型と色々な型で格納されますが、 日付型で格納されていることを想定しています。
参照用テーブル例
postgres=# d TIMESUMTEST
テーブル "public.timesumtest"
カラム | 型 | 修飾語
------------+-----------------------------+--------
_timestamp | timestamp without time zone |
_value | integer |
postgres=# select * from TIMESUMTEST LIMIT 10;
_timestamp | _value
---------------------+--------
2016-04-01 17:00:00 | 52
2016-04-01 17:00:01 | 61
2016-04-01 17:00:02 | 46
2016-04-01 17:00:03 | 48
2016-04-01 17:00:04 | 28
2016-04-01 17:00:05 | 59
2016-04-01 17:00:06 | 43
2016-04-01 17:00:07 | 61
2016-04-01 17:00:08 | 54
2016-04-01 17:00:09 | 49
(10 行)
postgres=#
SQL
LEFT関数の方が可読性が良いですが、環境によっては動作しなかったためSUBSTRINGを使用しています。
5秒単位
- 0〜4秒を5秒、5〜9秒を10(次の0秒)としてグルーピング
SELECT
CASE /* 5秒単位 */
-- 0〜4秒はhh:mm:s5
WHEN SUBSTRING(CAST(_timestamp AS VARCHAR), LENGTH(CAST(_timestamp AS VARCHAR)) - 1 + 1 ,1) <= '4'
THEN SUBSTRING(CAST(_timestamp AS VARCHAR), 1, 18) || '5'
-- 5〜9秒はhh:mm:s0
ELSE SUBSTRING(CAST((_timestamp +INTERVAL '10 SECOND') AS VARCHAR), 1, 18) || '0'
END AS _timestamp_nsec
,SUM(_value) AS sum_value ,MIN(_value) AS min_value ,MAX(_value) AS max_value ,AVG(_value) AS avg_value
FROM TIMESUMTEST GROUP BY _timestamp_nsec ORDER BY _timestamp_nsec
_timestamp_nsec | sum_value | min_value | max_value | avg_value ---------------------+-----------+-----------+-----------+--------------------- 2016-04-01 17:00:05 | 235 | 28 | 61 | 47.0000000000000000 2016-04-01 17:00:10 | 266 | 43 | 61 | 53.2000000000000000 2016-04-01 17:00:15 | 275 | 43 | 63 | 55.0000000000000000 2016-04-01 17:00:20 | 267 | 42 | 65 | 53.4000000000000000 2016-04-01 17:00:25 | 247 | 39 | 60 | 49.4000000000000000 2016-04-01 17:00:30 | 261 | 42 | 59 | 52.2000000000000000 2016-04-01 17:00:35 | 232 | 39 | 57 | 46.4000000000000000 2016-04-01 17:00:40 | 311 | 42 | 77 | 62.2000000000000000 2016-04-01 17:00:45 | 290 | 52 | 66 | 58.0000000000000000 2016-04-01 17:00:50 | 313 | 53 | 76 | 62.6000000000000000 ・・・略
30秒単位
0〜29秒を30秒、30〜59秒を1分(次の00秒)としてグルーピング
SELECT
CASE /* 30秒単位 */
-- 00〜29秒はhh:mm:30
WHEN SUBSTRING(CAST(_timestamp AS VARCHAR), LENGTH(CAST(_timestamp AS VARCHAR)) - 2 + 1 ,2) <= '29'
THEN SUBSTRING(CAST(_timestamp AS VARCHAR) ,1 ,17) || '30'
-- 30〜59秒はhh:mm:00
ELSE SUBSTRING(CAST((_timestamp + INTERVAL '30 SECOND') AS VARCHAR) ,1 ,17) || '00'
END AS _timestamp_nsec
,SUM(_value) AS sum_value ,MIN(_value) AS min_value ,MAX(_value) AS max_value ,AVG(_value) AS avg_value
FROM TIMESUMTEST GROUP BY _timestamp_nsec ORDER BY _timestamp_nsec
_timestamp_nsec | sum_value | min_value | max_value | avg_value ---------------------+-----------+-----------+-----------+--------------------- 2016-04-01 17:00:30 | 1551 | 28 | 65 | 51.7000000000000000 2016-04-01 17:01:00 | 1677 | 39 | 77 | 55.9000000000000000 2016-04-01 17:01:30 | 1785 | 44 | 74 | 59.5000000000000000 2016-04-01 17:02:00 | 1720 | 34 | 86 | 57.3333333333333333 2016-04-01 17:02:30 | 1779 | 38 | 77 | 59.3000000000000000 2016-04-01 17:03:00 | 1838 | 31 | 85 | 61.2666666666666667 2016-04-01 17:03:30 | 1890 | 41 | 93 | 63.0000000000000000 2016-04-01 17:04:00 | 1810 | 39 | 94 | 60.3333333333333333 2016-04-01 17:04:30 | 1698 | 35 | 78 | 60.6428571428571429 2016-04-01 17:05:00 | 1894 | 46 | 89 | 63.1333333333333333 2016-04-01 17:05:30 | 57 | 57 | 57 | 57.0000000000000000
その他
10秒単位、1分単位なら先頭からn文字取り出すだけで済みます。
/* 10秒単位 */ SELECT SUBSTRING(CAST(_timestamp AS VARCHAR) ,1 ,18) || '0' AS _timestamp_nsec ,SUM(_value) AS sum_value ,MIN(_value) AS min_value ,MAX(_value) AS max_value ,AVG(_value) AS avg_value FROM TIMESUMTEST GROUP BY _timestamp_nsec ORDER BY _timestamp_nsec /* 60秒(1分)単位 */ SELECT SUBSTRING(CAST(_timestamp AS VARCHAR) ,1 ,16) AS _timestamp_nsec ,SUM(_value) AS sum_value ,MIN(_value) AS min_value ,MAX(_value) AS max_value ,AVG(_value) AS avg_value FROM TIMESUMTEST GROUP BY _timestamp_nsec ORDER BY _timestamp_nsec
