share facebook facebook2 twitter menu hatena pocket slack

2016.03.22 TUE

SQL 日時列を5秒、30秒単位で集計する

WRITTEN BY大住 孝之

二度と使用する事は無さそうなのですが、 極めて限定的な要件で、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

元記事はこちら

SQL 日時列を5秒、30秒単位で集計する

大住 孝之

構築運用担当。 個性的な面々の中で無個性という個性を打ち出していこうと画策中。