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

極めて限定的な要件で、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値はクォート要

```sql
/* 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値はクォート不要

```sql
/* 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でも使用可

```sql
/* 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
```