• ベストアンサー
  • 困ってます

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

  • 質問No.8493884
  • 閲覧数440
  • ありがとう数22
  • 気になる数0
  • 回答数4
  • コメント数0

お礼率 0% (0/4)

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

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.4
  • ベストアンサー

ベストアンサー率 43% (7609/17468)

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;

その他の回答 (全3件)

  • 回答No.3

ベストアンサー率 43% (7609/17468)

追記の追記。

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

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

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

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

該当日のdate、該当日の数量、1日前のdate、2日前のdate

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

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

ベストアンサー率 43% (7609/17468)

追記。

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

ベストアンサー率 43% (7609/17468)

以下のクエリを「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));
結果を報告する
このQ&Aにはまだコメントがありません。
あなたの思ったこと、知っていることをここにコメントしてみましょう。
関連するQ&A

その他の関連するQ&Aをキーワードで探す

ピックアップ

ページ先頭へ