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

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

```postgres:クエリ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してやればうまく行きそうです。

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

こんなところですね。

# 完全な日時データを如何に作るか
### まずは正攻法で
最終的には**Redshift**でやりたいので、ベースは**PostgreSQL**です。となると、カレンダーなどの作成で良く使うのは、**generate_series**関数です。

```postgres:generate_seriesのサンプル
SELECT
	seq.n
FROM
	generate_series(0, 9) as seq(n)
;
```
```
 n
-----
   0
   1
   2
   3
   4
   5
   6
   7
   8
   9
(10 行)
```

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

```postgres: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 行)
```

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

```postgres: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しようとすると、こんな感じでエラー吐きます。

```postgres: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です。充分に大きなテーブルってことで、集計しようとしているログのテーブルを使ってしまえばシンプルになります。

```postgres:generate_seriesを使わない自然数列
SELECT
	row_number() over (order by true) as n
FROM
	nginx_log
LIMIT
	10
;
```
```
 n
-----
   1
   2
   3
   4
   5
   6
   7
   8
   9
  10
(10 行)
```

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

```postgres:generate_seriesを使わない自然数列
SELECT
	row_number() over () as n
FROM
	nginx_log
LIMIT
	10
;
```

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

```postgres: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する際には、年月日を文字列化したものを使ってみました。

```postgres: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で結果が返ってきます。