データ分析で必要な連続した値を生成する

一日あたりのCV数を分析したいとかいう時に、CV数が少ないチャネルだったりすると、1CVも発生していない時に日付が連続せず、またそれに気づかないで分析してしまうことがよくあるのでメモを残しておきます。

MySQLの場合はユーザー変数を使う

MySQLでは使用頻度は高くありませんがユーザー変数を使用することができます。

# 初期化する場合はSET句を使う
SET @var = 0;

# SQL文中で使う時には := 演算子を使う
# 1から100まで出力するループは以下のようにかける
SELECT @var := @var + 1
FROM hoge
LIMIT 100
；

上記を応用すると、連続した日付は以下のようなSQLで出力することができます。

days.sql

SET @i = 0;
SELECT
  DATE_ADD('2015-12-31', INTERVAL @i := @i + 1 DAY) AS date
FROM
  hoge
LIMIT 366
;

hogeテーブルは実在するテーブルなら何でもよいのですが、出力する行数以上のレコードが存在するテーブルである必要があります。
あとはこれを外部結合したり、使用頻度が高そうであればCREATE TEMPORARY TABLE句などを使って一時テーブルにしておくと良いでしょう。

PostgreSQLの場合はgenerate_series関数を使う

PostgreSQLにはGENERATE_SERIES()関数が用意されています。
使い方は以下のような感じ。

SELECT
    n,
    sin(n) sinn,
    n*sin(n) nsinn,
    -n nn
FROM generate_series(1, 100) n
;

これを応用すれば、MySQLよりも簡単に連続した日付を出力することができます。

days.sql

SELECT '2016-01-01'::date +  i AS days
FROM generate_series(0, 365) i
;

あとはこれをWITH句とかに入れて外部結合とかすればよさげ。

redshift ではgenerate_series()が使えない

@maru3 氏よりご指摘いただきましたので加筆です。ありがとうございますmm
redshiftではgenerate_series() 関数は使用不可です。
その代わりにwindow関数を使って連番を作ることができます。使うwindow関数はROW_NUMBER()です。
詳しい使い方は、
http://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_Examples_of_WF_ROW_NUMBER_WF.html
を参照してください。

days.sql

SELECT 
  ('2015-12-31' + ROW_NUMBER() OVER (ORDER BY id))::date AS date
FROM hoge.fuga
LIMIT 366;

これもMySQLの時と同様、FROM句のテーブルはLIMITで指定した値以上のレコード数を持っていることが条件になります。
これで分析が捗る！