Edited at

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

More than 3 years have passed since last update.

極めて限定的な要件で、RDB上で5秒(30秒)単位で集計する方法です。

(と言っても、日時文字列の末尾を切り捨てているだけです)


※バージョンによっては動作しない可能性があります。

日時データはシステムによって、日付・文字列・数値型と色々な型で格納されますが、

日付型で格納されていることを想定しています。

尚、この方法だと最小値(0)と最大値(9)の扱いが5,30と10,60では異なってきます。


  • 5秒単位


    • 0〜4秒を5秒、5〜9秒を10(次の0秒)としてグルーピング



  • 10秒単位


    • 0〜9秒を0(直前の0秒)としてグルーピング



  • 30秒単位


    • 0〜29秒を30秒、30〜59秒を1分(次の00秒)としてグルーピング



  • 60秒(1分)単位


    • 0〜59秒を分(直前の00秒)としてグルーピング



テーブル定義


_timestamp
TIMESTAMP

_value
INTEGER


使用関数比較

見事にバラバラです。

用途
Redshift
PostgreSQL
MySQL
Oracle
SQLServer

文字列抽出(*1)
SUBSTRING
SUBSTRING
SUBSTRING
SUBSTR
SUBSTRING

型変換(*2)
CAST
CAST/TO_*
CAST
TO_*
CONVERT

文字列結合
||
||
CONCAT
||
CONCAT

日時計算(*2)
INTERVAL
INTERVAL
INTERVAL
INTERVAL
DATEADD

文字列数
LENGTH
LENGTH
LENGTH
LENGTH
LEN

(*1) LEFT/RIGHT関数の方が可読性が良いが、環境によって型チェックエラーに引っかかったのでSUBSTRING系を使用

(*2) 指定書式は様々


Redshift / PostgreSQL

Redshift: 1.0

postgreSQL: 8.4.20


  • 文字列抽出は SUBSTRING


    • LEFT関数の方が可読性が良いが、環境によって型チェックエラーに引っかかったのでSUBSTRINGを使用



  • 型変換は CAST


    • Redshiftは TO_* 関数未サポート



  • 文字列結合は "||"

  • INTERVAL値はクォート要

/* 5秒単位 */

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

-- 以下、単位計算部分のみ抜粋

/* 10秒単位 */
SUBSTRING(CAST(_timestamp AS VARCHAR) ,1 ,18) || '0' AS _timestamp_nsec

/* 30秒単位 */
CASE
-- 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

/* 60秒(1分)単位 */
SUBSTRING(CAST(_timestamp AS VARCHAR) ,1 ,16) AS _timestamp_nsec


MySQL

MySQL: 5.1.73


  • 文字列抽出は SUBSTRING

  • 型変換は CAST


    • char指定(varchar指定は不可)



  • 文字列結合は CONCAT

  • INTERVAL値はクォート不要

/* 5秒単位 */

SELECT
CASE
-- 0〜4秒はhh:mm:s5
WHEN SUBSTRING(CAST(_timestamp AS CHAR), LENGTH(CAST(_timestamp AS CHAR)) - 1 + 1 ,1) <= '4'
THEN CONCAT(SUBSTRING(CAST(_timestamp AS CHAR), 1, 18), '5')
-- 5〜9秒はhh:mm:s0
ELSE CONCAT(SUBSTRING(CAST((_timestamp + INTERVAL 10 SECOND) AS CHAR), 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

-- 以下、単位計算部分のみ抜粋

/* 10秒単位 */
CONCAT(SUBSTRING(CAST(_timestamp AS CHAR) ,1 ,18) ,'0') AS timestamp_nsec

/* 30秒単位 */
CASE
-- 00〜29秒はhh:mm:30
WHEN SUBSTRING(CAST(_timestamp AS CHAR), LENGTH(CAST(_timestamp AS CHAR)) - 2 + 1 ,2) <= '29'
THEN CONCAT(SUBSTRING(CAST(_timestamp AS CHAR), 1, 17), '30')
-- 30〜59秒はhh:mm:00
ELSE CONCAT(SUBSTRING(CAST((_timestamp + INTERVAL 30 SECOND) AS CHAR), 1, 17), '00')
END AS timestamp_nsec

/* 60秒(1分)単位 */
CONCAT(SUBSTRING(CAST(_timestamp AS CHAR) ,1 ,16) ,'0') AS timestamp_nsec


Oracle / PostgreSQL

Oracle : 11.2.0.4


  • 文字列抽出は SUBSTR

  • 型変換は TO_*

  • 文字列結合は "||"

  • INTERVAL値はクォート要(値部のみ)

  • GROUP BY句に別名使用不可

  • 互換性を謳っているだけあってPostgreSQLでも使用可

/* 5秒単位 */

SELECT
CASE
-- 0〜4秒はhh:mm:s5
WHEN SUBSTR(TO_CHAR(timestamp_ ,'YYYY/MM/DD HH24:MI:SS'), LENGTH(TO_CHAR(timestamp_ ,'YYYY/MM/DD HH24:MI:SS')) - 1 + 1 ,1) <= '4'
THEN SUBSTR(TO_CHAR(timestamp_ ,'YYYY/MM/DD HH24:MI:SS'), 1, 18) || '5'
-- 5〜9秒はhh:mm:s0
ELSE SUBSTR(TO_CHAR(timestamp_ + INTERVAL '10' SECOND ,'YYYY/MM/DD HH24:MI:SS'), 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
CASE
-- 0〜4秒はhh:mm:s5
WHEN SUBSTR(TO_CHAR(timestamp_ ,'YYYY/MM/DD HH24:MI:SS'), LENGTH(TO_CHAR(timestamp_ ,'YYYY/MM/DD HH24:MI:SS')) - 1 + 1 ,1) <= '4'
THEN SUBSTR(TO_CHAR(timestamp_ ,'YYYY/MM/DD HH24:MI:SS'), 1, 18) || '5'
-- 5〜9秒はhh:mm:s0
ELSE SUBSTR(TO_CHAR(timestamp_ + INTERVAL '10' SECOND ,'YYYY/MM/DD HH24:MI:SS'), 1, 18) || '0'
END
ORDER BY
CASE
-- 0〜4秒はhh:mm:s5
WHEN SUBSTR(TO_CHAR(timestamp_ ,'YYYY/MM/DD HH24:MI:SS'), LENGTH(TO_CHAR(timestamp_ ,'YYYY/MM/DD HH24:MI:SS')) - 1 + 1 ,1) <= '4'
THEN SUBSTR(TO_CHAR(timestamp_ ,'YYYY/MM/DD HH24:MI:SS'), 1, 18) || '5'
-- 5〜9秒はhh:mm:s0
ELSE SUBSTR(TO_CHAR(timestamp_ + INTERVAL '10' SECOND ,'YYYY/MM/DD HH24:MI:SS'), 1, 18) || '0'
END

-- 以下、単位計算部分のみ抜粋

/* 10秒単位 */
SUBSTR(TO_CHAR(timestamp_ ,'YYYY/MM/DD HH24:MI:SS'), 1, 18) || '0' timestamp_nsec

/* 30秒単位 */
CASE
-- 00〜29秒はhh:mm:30
WHEN SUBSTR(TO_CHAR(timestamp_ ,'YYYY/MM/DD HH24:MI:SS'), LENGTH(TO_CHAR(timestamp_ ,'YYYY/MM/DD HH24:MI:SS')) - 2 + 1 ,2) <= '29'
THEN SUBSTR(TO_CHAR(timestamp_ ,'YYYY/MM/DD HH24:MI:SS'), 1, 17) || '30'
-- 30〜59秒はhh:mm:00
ELSE SUBSTR(TO_CHAR(timestamp_ + INTERVAL '30' SECOND ,'YYYY/MM/DD HH24:MI:SS'), 1, 17) || '00'
END AS timestamp_nsec

/* 60秒(1分)単位 */
SUBSTR(TO_CHAR(timestamp_ ,'YYYY/MM/DD HH24:MI:SS'), 1, 16) timestamp_nsec


SQLServer

SQLServer : 12.00.4422.0.v1


  • 文字列抽出は SUBSTRING

  • 型変換は CONVERT


    • CASTの場合書式設定不可



  • 文字列結合は CONCAT

  • GROUP BY句に別名使用不可

  • 日付計算は DATEADD 文字列数は LEN

SELECT

CASE
-- 0〜4秒はhh:mm:s5
WHEN SUBSTRING(CONVERT(VARCHAR ,_timestamp ,120), LEN(CONVERT(VARCHAR ,_timestamp ,120)) - 1 + 1 ,1) <= '4'
THEN CONCAT(SUBSTRING(CONVERT(VARCHAR ,_timestamp ,120), 1, 18), '5')
-- 5〜9秒はhh:mm:s0
ELSE CONCAT(SUBSTRING(CONVERT(VARCHAR ,DATEADD(second ,10 ,_timestamp) ,120), 1, 18), '0')
END AS timestamp_sec
,SUM(_value) AS sum_value ,MIN(_value) AS min_value ,MAX(_value) AS max_value ,AVG(_value) AS avg_value
FROM TIMESUMTEST
GROUP BY
CASE
-- 0〜4秒はhh:mm:s5
WHEN SUBSTRING(CONVERT(VARCHAR ,_timestamp ,120), LEN(CONVERT(VARCHAR ,_timestamp ,120)) - 1 + 1 ,1) <= '4'
THEN CONCAT(SUBSTRING(CONVERT(VARCHAR ,_timestamp ,120), 1, 18), '5')
-- 5〜9秒はhh:mm:s0
ELSE CONCAT(SUBSTRING(CONVERT(VARCHAR ,DATEADD(second ,10 ,_timestamp) ,120), 1, 18), '0')
END
ORDER BY
CASE
-- 0〜4秒はhh:mm:s5
WHEN SUBSTRING(CONVERT(VARCHAR ,_timestamp ,120), LEN(CONVERT(VARCHAR ,_timestamp ,120)) - 1 + 1 ,1) <= '4'
THEN CONCAT(SUBSTRING(CONVERT(VARCHAR ,_timestamp ,120), 1, 18), '5')
-- 5〜9秒はhh:mm:s0
ELSE CONCAT(SUBSTRING(CONVERT(VARCHAR ,DATEADD(second ,10 ,_timestamp) ,120), 1, 18), '0')
END

-- 以下、単位計算部分のみ抜粋

/* 10秒単位 */
CONCAT(SUBSTRING(CONVERT(VARCHAR ,_timestamp ,120), 1, 18) ,'0') AS timestamp_nsec

/* 30秒単位 */
CASE
-- 00〜29秒はhh:mm:30
WHEN SUBSTRING(CONVERT(VARCHAR ,_timestamp ,120), LEN(CONVERT(VARCHAR ,_timestamp ,120)) - 2 + 1 ,2) <= '29'
THEN CONCAT(SUBSTRING(CONVERT(VARCHAR ,_timestamp ,120), 1, 17), '30')
-- 30〜59秒はhh:mm:00
ELSE CONCAT(SUBSTRING(CONVERT(VARCHAR ,DATEADD(second ,30 ,_timestamp) ,120), 1, 17), '00')
END AS timestamp_nsec

/* 60秒(1分)単位 */
SUBSTRING(CONVERT(VARCHAR ,_timestamp ,120), 1, 16) AS timestamp_nsec