SQLによる時系列データの集約

BigQueryで１分粒度のデータを５分や３０分に集約したくて、試した結果。

軽く調べたけど、タイムスタンプを文字列として…っていうやり方がちょっと面倒だったので自己流で。何かデメリットがあるのかも知れない。

やりたいこと

細かい粒度のデータを集約（合算/平均など）したい。

前提

とりあえず以下の前提です。

• データは欠損していない
• キーとして扱っているフィールド（DATA_TIMESTAMP、ID）で重複はない
  なおスキーマは以下の通り

列名	データ型
ID	STRING
VALUE	FLOAT64
DATA_TIMESTAMP	TIMESTAMP

実現方法

単純で、DATA_TIMESTAMPを秒で表現し、集約単位での余りを引く。 　
でその値で集約する。

# 1分値→5分値
SELECT 
    ID, VALUE, DATA_TIMESTAMP
FROM
    (SELECT 
            TIMESTAMP_SECONDS(UNIX_SECONDS(DATA_TIMESTAMP) - MOD(UNIX_SECONDS(DATA_TIMESTAMP), 300)) AS DATA_TIMESTAMP,
            ID,
            SUM(VALUE) AS VALUE
    FROM
        `series_data.xxx_01min`
    GROUP BY ID, DATA_TIMESTAMP) M
    ORDER BY ID, DATA_TIMESTAMP;

# 5分値→30分値
SELECT 
    ID, VALUE, DATA_TIMESTAMP
FROM
    (SELECT 
            TIMESTAMP_SECONDS(UNIX_SECONDS(DATA_TIMESTAMP) - MOD(UNIX_SECONDS(DATA_TIMESTAMP), 1800)) AS DATA_TIMESTAMP,
            ID,
            SUM(value) AS VALUE
    FROM
        `series_data.xxx_05min`
    GROUP BY ID, DATA_TIMESTAMP) M
    ORDER BY ID, DATA_TIMESTAMP;

ちょっとした注意

より粒度の小さいデータ、この場合１分データがあれば３０分集約も作れるけれど、BigQueryのお金を節約するなら、1分→５分→30分のようにちょっとずつ再利用した方がいい。