スポンサーサイト

上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。

PL/pgSQLでカレンダー作成

業務からは直接でてこないんだけど、SQLを書いててよくお世話になるのがカレンダーマスタです。
例えばサイトのPVとかを集計しようとする時にPVが0の日は、その日のレコードが作成されません。
PVなくても0として、レコードを作りたい場合、役に立つのがカレンダーマスタです。

CREATE TABLE t_pv (
ins_ts timestamp with time zone
,PRIMARY KEY(ins_ts)
);
INSERT INTO t_pv (ins_ts) VALUES
(make_time('2009', '09', '01', '01'))
,(make_time('2009', '09', '01', '02'))
,(make_time('2009', '09', '03', '03'));

今回以前使ったmake_timeadd_timeを利用しています。
さて、これを普通に集計すると

SELECT
DATE_TRUNC('day', ins_ts) ins_ts
,COUNT(*) cnt
FROM
t_pv
GROUP BY
DATE_TRUNC('day', ins_ts);

"2009-09-01 00:00:00+09";2
"2009-09-03 00:00:00+09";1

となり、2日のレコードが作成されません。ここは

"2009-09-01 00:00:00+09";2
"2009-09-02 00:00:00+09";0
"2009-09-03 00:00:00+09";1

と出したいところです。そこでカレンダーマスタ。
カレンダーマスタとは日付だけのテーブルです。
早速カレンダーマスタを作るPL/pgSQLを書いてみます。

CREATE OR REPLACE FUNCTION make_calendar (
p_year text
,p_month text DEFAULT '00'
) RETURNS TABLE(r_date timestamp with time zone)
AS $$
DECLARE
w_start_ts timestamp with time zone;
w_end_ts timestamp with time zone;
BEGIN
IF '00' = p_month THEN
w_start_ts := make_time(p_year);
w_end_ts := add_time(w_start_ts, 1, 'year');
ELSE
w_start_ts := make_time(p_year, p_month);
w_end_ts := add_time(w_start_ts, 1, 'month');
END IF;
IF w_start_ts IS NULL THEN
RETURN;
END IF;
WHILE w_start_ts < w_end_ts LOOP
RETURN QUERY SELECT w_start_ts;
w_start_ts := add_time(w_start_ts, 1, 'day');
END LOOP;
END;
$$ LANGUAGE plpgsql

この関数は年と月を入れると、その月だけのカレンダーを作ります。
年だけ入れると、その年のカレンダーを作ります。
それでは、カレンダーマスタを使ったSQLを書いてみます。

SELECT
t1.r_date
,COALESCE(t2.cnt, 0)
FROM
make_calendar('2009', '09') AS t1
LEFT OUTER JOIN (
SELECT
DATE_TRUNC('day', ins_ts) ins_ts
,COUNT(*) cnt
FROM
t_pv
GROUP BY
DATE_TRUNC('day', ins_ts)
) AS t2 ON (
t1.r_date = t2.ins_ts
);

"2009-09-01 00:00:00+09";2
"2009-09-02 00:00:00+09";0
"2009-09-03 00:00:00+09";1
"2009-09-04 00:00:00+09";0
"2009-09-05 00:00:00+09";0
...

実際の結果は9/30まで出力されます。
カレンダーマスタを頻繁に使うようでしたら、きちんとテーブルを作った方がパフォーマンスはよいでしょう。
カレンダーマスタの更新て月バッチか年バッチでやるようになると思いますが、忘れそうで怖いんですよね。確認できるまで、一年かかるとか・・・
スポンサーサイト

COMMENTS

COMMENT FORM

TRACKBACK


この記事にトラックバックする(FC2ブログユーザー)

上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。