1. htnosm

    Posted

    htnosm
Changes in title
+SQL 日時列を5秒、30秒単位で集計する(RDBMS別)
Changes in tags
Changes in body
Source | HTML | Preview
@@ -0,0 +1,233 @@
+極めてニッチな要件で、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
+
+- 文字列抽出は **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
+
+- 文字列抽出は **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
+```