Help us understand the problem. What is going on with this article?

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

More than 3 years have passed since last update.

一日あたりの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で指定した値以上のレコード数を持っていることが条件になります。
これで分析が捗る!

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした