指定テーブルと、現在日付以前のデータをサマリーして取得するSQL

Question

前提・実現したいこと

指定時刻テーブルを使ってその日以前のデータを集計して一覧にするデータを取得したいのですが
その際、指定日テーブル＋現在時刻の以前のデータでサマリーするにはどのようなSQLをかけば一番簡潔でしょうか

本日日付　2019-10-10 23:59:59
指定日付テーブル
id,date
1 2019-10-01 00:00:00
2 2019-10-05 23:59:59

対象レコード
id,amount,date
1 4500yen 2019-10-01
2 500yen 2019-10-02
3 6000yen 2019-10-05
4 7000yen 2019-10-10
...

結果

2019-10-01 4500yen
2019-10-05 11000yen
2019-10-10 18000yen

試したこと

指定日付テーブルを取得して
そのデータ＋現在日付で以下のsqlをループ

select sum(amount)　from 対象レコード　where 対象レコード.date <= 　[指定日]　

という複数sqlを走らせる形しか思いつきませんでしたが
sql一本で実現可能でしょうか？

Comments

[maisumakun]

UNIONしようにも、指定日付テーブルにamountは存在しません。どのような形のデータが必要か、もう少し明確にしてください。

[ms5025]

すいません、質問したい内容が変わりましたので、変更しました

[sazi]

指定日付テーブルは日時で、対象データは日付なのですか？
その場合は指定日付の日付部分だけを条件にするのでしょうか？

[ms5025]

すいません、そこまで細かく見ていませんでした
どちらも同じDateTime型です

[Orlofsky]

SQLで使われるテーブルはCREATE TABLEされていなければなりません。テーブル内のデータはINSERTされていなければなりません。ですから、質問にCREATE TABLEやINSERTを https://teratail.com/help/question-tips#questionTips3-7 の [コード] に修正した方が適切な回答が付き易いです。

[Orlofsky]

DATEは 予約語 https://www.postgresql.jp/document/9.6/html/sql-keywords-appendix.html ですから、テーブル名や列名などには使わないでください。

[ms5025]

??

すいません、ちょっと意味がわかりませんでした。
テーブルや列名は例ですのでもちろん適当です・・・。
質問の要点は
任意の日付が格納されているテーブルの日付単位で
トランザクション系のテーブルをサマリーするSQLについてです。
よろしくお願いします。

[sazi]

結果からみると、指定日付テーブルは日付の範囲を指定しているように見えます。
もう少し質問を精査願います。

[ms5025]

saziさん
ありがとうございます。
この辺も質問内容を作るために適当な条件を指定してるのですが、
一応日付指定テーブル以前の価格のサマリーというイメージで質問していました。
わかりにくくてすいません。

[sazi]

例だと、日付指定は2019-10-01と2019-10-05の２つの日付ですけど、集計結果の方は2019-10-10が含まれています。
現在日以前＋日付指定テーブルで日付指定テーブルの内容が現在日以前なら日付指定テーブルの内容は条件として重なっていますから、現在日以前のみとした場合と同じ結果になりますか？
意味があるなら日付指定テーブルが現在日以降の日付がある場合という事なのでしょうか？

[ms5025]

すいません、その辺はなんとなく適当で結構です。（日付サマリーでも以前日サマリーでも・・・）
知りたかったのはこの指定日付テーブルの日付でデータを一発で取ることのできるunionの方法の方です。
詳細な条件句の方は、正直どうでもいいというか、こちらで適当に欲しい情報を付与します

[sazi]

> 知りたかったのはこの指定日付テーブルの日付でデータを一発で取ることのできるunionの方法の方です。
その為の条件が質問内容では不明瞭なので、質問しています。

[Orlofsky]

UNION と UNION ALL の違いも気にされていないようですし、
> PostgreSQL SQL 入門
でぐぐってきちんと勉強しないと砂上の楼閣システムを作るだけかと。

[ms5025]

saziさん
うまく言えないのですがそこの条件は本当いうともう少し複雑ですし、
もちろんテーブルもカラムも複数あり、当たり前ですが実際のシステムは質問内容と大幅に異なります。
けどそれって特に質問内容に関係ないと思うのです。
完全なSQLが欲しいのではなく、
・「日付テーブル（仮名）の全ての日付を条件とするサマリーの一覧が欲しい」
ということです。
この「条件」は日付以前でもその日のみでもそれはどうでもいいです。
イコールなのか、それ以前なのかはSQLを必要に応じて変えるだけなので、そこはなんでもいいですよ。
もしよかったら「試したこと」の所を見て頂いていいですか？

[sazi]

矛盾もあるしさっぱり分かりませんが、指定日付テーブルを対象レコードの抽出条件にしたSQLで回答しておきます。

Answer 1

重いので、データ数が多いテーブルであればおススメはしませんが、こういう書き方で取得できます、というご紹介です。
（指定日テーブルは

-- 「指定日までの分全て」を取得
SELECT
	CAST(td.target_date AS DATE) AS target_date,
	-- 指定日テーブル（target_dates）の日付り対象レコード（amount_target_date）の日付が大きかったらamountを足す、それ以外は0を足す
	-- 日付比較をするために「date型にキャスト」していますが、もし時間も比較対象にしたいなら、CASTを外してください（ここ以外も同様）
	SUM(
		CASE WHEN CAST(td.target_date AS DATE) >= CAST(atd.target_date AS DATE) THEN amount
		ELSE 0
		END
	)
FROM
	target_dates AS td
-- 全レコード、条件を指定せずに結合する
CROSS JOIN
	amount_target_date AS atd
GROUP BY
	CAST( td.target_date AS DATE )

-- 取得結果結合
UNION ALL

-- 「今日までの分全て」を取得
SELECT current_date AS target_date, SUM(amount) FROM amount_target_date;

Comments

[ms5025]

ありがとうございます！参考になります。
確かにデータ件数と速度の検証は必要なので
確認しながらこちらで大丈夫そうならこのやり方で取得してみます！

Answer 2

指定日付テーブルに登録されている日付に一致する対象レコードを対象に集計(sum)する。

select date, sum(amount)
from 対象レコード t1 inner join 指定日付テーブル t2
     on t1.date=t2.date
group by date

Comments

[ms5025]

ありがとうございます！
すいません、そうか、、単純なsqlでしたね。
申し訳ありません。
こちらと、システム日付で取得したものをunionして取得したいと思います。
ありがとうございました！

[nak]

こちらのコードだと、その日「の」合計値しか取れないのではないかと思われます。
質問はその日「までの」合計値なので（対象日が10/5の場合、10/1や10/2の数値も足す必要がある）、JOINの場合はクロス結合が必要になってしまうかなと……。

[sazi]

仕様を確認しても答えてくれないので、テーブルの結合がどのようになるかを単に示したサンプルです。

[nak]

saziさん
失礼いたしました。
こちら、私のコメントの直前にあった「ms5025さんのコメント」の返信になります（宛先を書いておけば良かったです）。

> こちらと、システム日付で取得したものをunionして取得したいと思います。
と書かれていたので「このクエリだと目的のデータが取れないのでは」という趣旨で記載させていただきました。