SQLでの累積和の集計

Question

前提・実現したいこと

下記のようなDBから月ごとの売上、月時点での累積和を集計したいです。

orders

id	ordered_at
1	2021-01-01
2	2021-01-02
3	2021-02-01
4	2021-02-02

item_order

id	order_id	item_id	price
1	1	35	500
2	1	58	300
3	2	12	600
4	2	18	200
5	2	37	900
6	3	35	500
7	3	58	300
8	4	12	600
9	4	18	200

###理想の集計結果
monthly_saleが月ごとの売上、salesが月時点での累積和

ordered_month	monthly_sales	sales
2021-01	2500	2500
2021-02	1600	4100

試したこと

SELECT
DATE_FORMAT(orders.ordered_at,'%Y-%m') as ordered_month,
SUM(price) as monthly_sales,
FROM item_order
INNER JOIN orders ON item_order.order_id=orders.id
WHERE orders.ordered_at BETWEEN '2021/01/01' AND '2021/12/31'
GROUP BY DATE_FORMAT(orders.ordered_at, '%Y-%m');

上記SQLで月ごとの売上集計までは出来ています。

ordered_month	monthly_sales
2021-01	2500
2021-02	1600

アプリケーション側で合わせたほうが良いことはわかっていますが、
どうしてもSQLで完結させたいです。
スマートな方法でなくても構いません。

補足情報（FW/ツールのバージョンなど）

MySQL8

Answer 1

item_order からordersを正規化しようとしていることが問題だと思います
item_orderの集計をするのだからitem_order内に集計単位がなければ
データが肥大化したとき致命的なボトルネックになるでしょう

命題の解法としては以下のようにすれば可能ですがあまりおすすめできません

create table orders(id int primary key,ordered_at date);
insert into orders values
(1,'2021-01-01'),
(2,'2021-01-02'),
(3,'2021-02-01'),
(4,'2021-02-02');

create table item_order (id int primary key,order_id int,itme_id int,price int);
insert into item_order values
(1,1,35,500),
(2,1,58,300),
(3,2,12,600),
(4,2,18,200),
(5,2,37,900),
(6,3,35,500),
(7,3,58,300),
(8,4,12,600),
(9,4,18,200);

select d,sum(price) as mon
,(select @a:=@a+sum(price) from (select @a:=0) as dummy) as total
 from item_order as t1
inner join (select id as order_id,date_format(ordered_at,'%y-%m') as d from orders) as t2
using( order_id)
group by d
order by d;

Comments

[yambejp]

とくに変数をつかった行単位の加算は微妙です

[y.hata]

ありがとうございます！！
出来ました！！
ゴリ押しの方法になることはわかっていたものの、
どうしてもSQLで完結したかったので助かりました！

[yambejp]

MySQL8なのでおそらくウィンドウ関数が使えると思います
（MySQL8導入していないので回答できません、あしからず）

Answer 2

MySQL8以降であれば、ウィンドウ関数が利用できるので、そちらを利用されてもよいかもしれません。

SELECT 
  T.ordered_month,
  T.monthly_sales,
  SUM(T.monthly_sales) OVER (ORDER BY T.ordered_month) AS sales
FROM (
  SELECT
    DATE_FORMAT(O.ordered_at, '%Y-%m') AS ordered_month,
    SUM(I.price) AS monthly_sales
  FROM orders O
  JOIN item_order I ON O.id = I.order_id
  GROUP BY ordered_month
) AS T;