日付列に入っている値までの金額合計を求めたい

Question

実現したいこと

テーブルにある日付列に入っている日付からこの日付の月初めまでの金額の合計を求めたいです。

前提

下記のテーブルを作成し、データーを挿入します。

CREATE TABLE d_test (
  processing_date date NOT NULL,
  price int NOT NULL
) ENGINE=InnoDB;

INSERT INTO d_test (processing_date, price) VALUES
('2025-01-01', 100),
('2025-01-03', 300),
('2025-01-05', 300),
('2025-01-09', 450);

求めたい値

processing_date	price	sub_total
2025-01-01	100	100
2025-01-03	300	400
2025-01-05	300	700
2025-01-09	450	1150

試したSQL

SELECT * ,
(SELECT SUM(price)
FROM d_test
WHERE processing_date BETWEEN str_to_date(date_format(processing_date, '%Y-%m-01'), '%Y-%m-%d')  AND  processing_date) AS sub_total
FROM `d_test`;

実際の値

processing_date	price	sub_total
2025-01-01	100	1150
2025-01-03	300	1150
2025-01-05	300	1150
2025-01-09	450	1150

sub_total には全体の合計が入ってしまいます。

質問内容

どうのようなSQLで求めたい値のデーターを取得できるようになるでしょうか？

環境

• データーベース
  MySQL
• バージョン
  8以降

Comments

[yambejp]

質問がダブっているのでどちらか削除してください
また想定しているRDBの種類とバージョンがあれば提示ください

[shumai.gyoza]

コメントありがとうございます。質問を修正したのでご確認よろしくお願いいたします。

Answer 1

MySQLだと仮定して

SELECT * ,
(SELECT SUM(price)
FROM d_test
WHERE processing_date<=t1.processing_date)
AS sub_total
FROM d_test as t1

とか

SELECT t1.processing_date,
t1.price,
sum(t2.price) AS sub_total
FROM d_test as t1
inner join d_test as t2 on t1.processing_date>=t2.processing_date
group by t1.processing_date,
t1.price
order by processing_date

ただし同じ日付があるときはどうしたいかによります

window関数

window関数がつかえるならもっと楽です

SELECT processing_date,
price,
sum(price) over(order by processing_date)  AS subprice
FROM d_test

月ごとに集計もろもろ

INSERT INTO d_test (processing_date, price) VALUES
('2025-01-01', 100),
('2025-01-03', 300),
('2025-01-05', 300),
('2025-01-09', 450),
('2025-02-01', 400),
('2025-02-03', 200);

として

SELECT processing_date,
price,
sum(price) over(partition by LAST_DAY(processing_date) order by processing_date)  AS sub_price,
sum(price) over(partition by LAST_DAY(processing_date)) AS month_total_price,
sum(price) over() AS total_price
FROM d_test

Comments

[shumai.gyoza]

回答ありがとうございます。
試したところ問題が解決しました！
ベストアンサーに選ばせていただきました。

月ごとでsub_totalを割り出したかったので下記のSQLにしました。

SELECT * ,
(SELECT SUM(price)
FROM d_test
WHERE
processing_date >= str_to_date(date_format(t1.processing_date, '%Y-%m-01'), '%Y-%m-%d')
AND
processing_date<=t1.processing_date)
AS sub_total
FROM d_test as t1;

[yambejp]

一応window関数版も追記しておきました

>月ごとでsub_totalを割り出したかった
この辺サンプルデータがほしいかったです

[shumai.gyoza]

回答ありがとうございます。こちらの回答も非常に参考になりました。