SQLで期間をずらした集計処理

以下のようなテーブルがあったとします。

select * from T1;

+------------+------+
| date | num |
+------------+------+
| 2014-01-01 | 1 |
| 2014-01-02 | 2 |
| 2014-01-03 | 2 |
| 2014-01-05 | 1 |
| 2014-01-07 | 1 |
+------------+------+

このとき、「各日付を起点として、その日を含む過去3日分の合計num数を出力」させるためにはどのようなSQLを書けばいいのでしょうか。

以下のような結果が返ってくることを期待しています。

| date | sum_num |
+------------+------+
| 2014-01-01 | 1 |
| 2014-01-02 | 3 | ※ 1+2
| 2014-01-03 | 5 |　　※ 1+2+2
| 2014-01-05 | 3 |　　※ 2+1
| 2014-01-07 | 2 |　　※ 1+1
+------------+------+

PostgreSQL 8系なのですが、環境の問題でwith recursive句を使うことができません。

相関サブクエリをうまく使って取得する方法は無いでしょうか。

よろしくお願いいたします。

No.1 回答者： chie65535 回答日時： 2014/02/28 17:48

以下のクエリを「q1」として作成。

SELECT date, Sum(num) AS num
FROM (SELECT Table1.date, Table1.num
FROM Table1
union
SELECT Table1.date-1, 0 as num
FROM Table1
union
SELECT Table1.date-2, 0 as num
FROM Table1)
GROUP BY date;

以下のクエリで結果取得。

SELECT q1_2.date, q1.num+q1_1.num+q1_2.num AS sum_num
FROM q1, q1 AS q1_1, q1 AS q1_2
WHERE (((q1.date)=q1_1.date-1 And (q1.date)=q1_2.date-2) AND ((q1_2.num)<>0));

No.2 回答者： chie65535 回答日時： 2014/02/28 17:52

追記。

dateフィールドの値に「マイナス１」や「マイナス２」で、前日、前々日が作れない場合は、日付操作用の関数で前日、前々日を生成して下さい。

No.3 回答者： chie65535 回答日時： 2014/02/28 18:02

追記の追記。

ANo.1のクエリでは、レコード件数が多い場合は、非常に遅くなります。

何故なら「結果クエリが、総当りですべての組み合わせを生成してから、Where句で条件に合う物を抽出している」からです。

例えば、元のテーブルが１０００件あると、１０００×１０００×１０００で１０００００００００件のレコードを生成してから、条件に合う１０００件を抽出する事になります。

レコード件数が多い場合は、最初のq1を工夫して

該当日のdate、該当日の数量、１日前のdate、２日前のdate

を出力するようにして、結果クエリで参照するq1を、LEFT JOINで結合しましょう。

LEFT JOINで結合すれば「総当りですべての組み合わせを生成」って事が起きません。

No.4 ベストアンサー 回答者： chie65535 回答日時： 2014/02/28 18:34

INNER JOINに書き換えたのが以下。

q1クエリ

SELECT date, Sum(num) AS num, date1, date2
FROM (SELECT Table1.date, Table1.num,Table1.date-1 as date1,Table1.date-2 as date2
FROM Table1
union
SELECT Table1.date-1, 0 as num,Table1.date-2 as date1,Table1.date-3 as date2
FROM Table1
union
SELECT Table1.date-2, 0 as num,Table1.date-3 as date1,Table1.date-4 as date2
FROM Table1)
GROUP BY date, date1, date2;

結果クエリ

SELECT q1.date, q1.num+q1_1.num+q1_2.num AS sum_num
FROM (q1 INNER JOIN q1 AS q1_1 ON q1.date1 = q1_1.date) INNER JOIN q1 AS q1_2 ON q1_1.date1 = q1_2.date
WHERE q1.num<>0;