やりたいこと

WebサーバのログなどのTIMESTAMPがあるデータを、時系列でスライスして集計したい時がありますよね。例えばこんなクエリを良く書きます。

クエリ1

SELECT
    datepart(year,cast(nginx_log.timestamp as timestamp)),
    datepart(month,cast(nginx_log.timestamp as timestamp)),
    datepart(day,cast(nginx_log.timestamp as timestamp)),
    count(nginx_log.timestamp)
FROM
    nginx_log
GROUP BY
    datepart(year,cast(nginx_log.timestamp as timestamp)),
    datepart(month,cast(nginx_log.timestamp as timestamp)),
    datepart(day,cast(nginx_log.timestamp as timestamp))
ORDER BY
    datepart(year,cast(nginx_log.timestamp as timestamp)),
    datepart(month,cast(nginx_log.timestamp as timestamp)),
    datepart(day,cast(nginx_log.timestamp as timestamp))
;

このクエリ、年や月単位ならばあまり問題にならないのですが、日や時までスライスしていくと、ログが存在しない日や時だけ抜け落ちてしまうことがあります。それをそのままグラフにすると、間が詰まって正しい表現になりません。
それを手作業で修正していくのは極めて非効率です。そこでこいつをSQLでなんとかできないかと、色々調べてたどり着いた答えのメモです。Redshiftの制限もあって、なかなかアクロバティックな感じです……

基本方針

抜け落ちるデータを補間しつつ集計する方法といえば、すぐに思いつくのはLEFT JOINです。つまり、抜け落ちの無い完全な日時データをまず作成し、それに実際のログをLEFT JOINしてやればうまく行きそうです。

SELECT
    完全な日時データのテーブル.スライス表現,
    count(nginx_log.timestamp)
FROM
    完全な日時データのテーブル
LEFT JOIN
    ログの日時データのテーブル
ON
    完全な日時データのテーブル.スライス表現=ログの日時データのテーブル.スライス表現
ORDER BY
    完全な日時データのテーブル.スライス表現
;

こんなところですね。

完全な日時データを如何に作るか

まずは正攻法で

最終的にはRedshiftでやりたいので、ベースはPostgreSQLです。となると、カレンダーなどの作成で良く使うのは、generate_series関数です。

generate_seriesのサンプル

SELECT
    seq.n
FROM
    generate_series(0, 9) as seq(n)
;

これで自然数の数列はできましたから、あとはこれを基準となる日時に足していくだけです。つまりこういうクエリになります。

generate_seriesで日の数列

SELECT
    '2016-10-1'::Date + seq.n as d
FROM
    generate_series(0, 9) as seq(n)
;

          d
---------------------
 2016-10-01 00:00:00
 2016-10-02 00:00:00
 2016-10-03 00:00:00
 2016-10-04 00:00:00
 2016-10-05 00:00:00
 2016-10-06 00:00:00
 2016-10-07 00:00:00
 2016-10-08 00:00:00
 2016-10-09 00:00:00
 2016-10-10 00:00:00
(10 行)

時だと少し面倒だけどこんな感じ。

generate_seriesで時の数列

SELECT
    '2016-10-1 00:00'::Date + cast(seq.n || 'hours' as interval) as h
FROM
    generate_series(0, 9) as seq(n)
;

          h
---------------------
 2016-10-01 00:00:00
 2016-10-01 01:00:00
 2016-10-01 02:00:00
 2016-10-01 03:00:00
 2016-10-01 04:00:00
 2016-10-01 05:00:00
 2016-10-01 06:00:00
 2016-10-01 07:00:00
 2016-10-01 08:00:00
 2016-10-01 09:00:00
(10 行)

これでほぼ完成！って思ったのですが、Redshiftはそんなに甘くなかったです。上記のクエリはRedshiftでも動いたかに見えるのですが、実はこれRedshiftのデータをスキャンしない場合のみに動いているんですね。なので、JOINしたりINSERTしようとすると、こんな感じでエラー吐きます。

Redshiftでのgenerate_series

CREATE TABLE fullcal as
    SELECT
        '2016-10-1 00:00'::Date + cast(seq.n || 'hours' as interval) as h
    FROM
        generate_series(0, 9) as seq(n)
;

Function "generate_series(integer,integer)" not supported
ERROR: 0A000: Specified types or functions (one per INFO message) not supported on Redshift tables.

ということで、Redshiftでgenerate_seriesは一切使えないってことになりました……

代替策はアクロバティック

generate_seriesなしで如何にして自然数の数列を作るかがポイントです。RedshiftはPostgreSQLなので、Window関数が使えます。そう、Window関数の最初で習う連番の機能を使うことで自然数列が作れます。
何かの適当な充分に大きなテーブルをスキャンして、Windows関数の連番だけをSELECTしてやればOKです。充分に大きなテーブルってことで、集計しようとしているログのテーブルを使ってしまえばシンプルになります。

generate_seriesを使わない自然数列

SELECT
    row_number() over (order by true) as n
FROM
    nginx_log
LIMIT
    10
;

ちなみにorder byの句は省略も可能です。

generate_seriesを使わない自然数列

SELECT
    row_number() over () as n
FROM
    nginx_log
LIMIT
    10
;

何個作るかはLIMIT句で指定するのが最適化の面で都合が良いようです。これを使って、さきほどと同様に日の数列を作ってみます。

Window関数で日の数列

SELECT
    '2016-10-1'::Date + (row_number() over (order by true))-1 as d
FROM
    nginx_log
LIMIT
    10
;

          d
---------------------
 2016-10-01 00:00:00
 2016-10-02 00:00:00
 2016-10-03 00:00:00
 2016-10-04 00:00:00
 2016-10-05 00:00:00
 2016-10-06 00:00:00
 2016-10-07 00:00:00
 2016-10-08 00:00:00
 2016-10-09 00:00:00
 2016-10-10 00:00:00
(10 行)

このクエリはRedshiftでも問題なく動きます。

JOINしてあげる

それでは作った日の数列を使ってLEFT JOINしてみます。JOINする際には、年月日を文字列化したものを使ってみました。

JOINした結果

SELECT
    fullcal.d1,
    COALESCE(logcal.c, 0)
FROM
    (
        SELECT
            to_char('2016-10-1'::Date + (row_number() over (order by true))-1 , 'YYYYMMDD') as d1
        FROM
            nginx_log
        LIMIT
            10
    ) as fullcal
LEFT JOIN
    (
        SELECT
            to_char(cast(nginx_log.timestamp as timestamp), 'YYYYMMDD') as d2,
            count(nginx_log.timestamp) as c
        FROM
            nginx_log
        GROUP BY
            to_char(cast(nginx_log.timestamp as timestamp), 'YYYYMMDD')
        ORDER BY
            to_char(cast(nginx_log.timestamp as timestamp), 'YYYYMMDD')
    ) as logcal
ON
    fullcal.d1 = logcal.d2
ORDER BY
    fullcal.d1
;

JOINしてデータの無い行はNULLになってしまうので、COALESCEで0にしてあげます。

パフォーマンスですが、Redshiftの一番小さなマシンで、100万行のログを100日分で集計すると、だいたい1.1secで結果が返ってきます。

SQLで時系列データを日時の抜けなしで集計する（Redshift版）