LoginSignup
21
12

More than 5 years have passed since last update.

@htnosm

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

Last updated at Posted at 2016-01-24

極めて限定的な要件で、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
21
12
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
21
12