質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.35%
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

Q&A

解決済

2回答

2623閲覧

SQLでの累積和の集計

y.hata

総合スコア1

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

0グッド

0クリップ

投稿2021/10/07 06:22

前提・実現したいこと

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

orders

idordered_at
12021-01-01
22021-01-02
32021-02-01
42021-02-02

item_order

idorder_iditem_idprice
1135500
2158300
3212600
4218200
5237900
6335500
7358300
8412600
9418200

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

ordered_monthmonthly_salessales
2021-0125002500
2021-0216004100

試したこと

SQL

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

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

ordered_monthmonthly_sales
2021-012500
2021-021600

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

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

MySQL8

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答2

0

ベストアンサー

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

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

SQL

1create table orders(id int primary key,ordered_at date); 2insert into orders values 3(1,'2021-01-01'), 4(2,'2021-01-02'), 5(3,'2021-02-01'), 6(4,'2021-02-02'); 7 8create table item_order (id int primary key,order_id int,itme_id int,price int); 9insert into item_order values 10(1,1,35,500), 11(2,1,58,300), 12(3,2,12,600), 13(4,2,18,200), 14(5,2,37,900), 15(6,3,35,500), 16(7,3,58,300), 17(8,4,12,600), 18(9,4,18,200); 19 20select d,sum(price) as mon 21,(select @a:=@a+sum(price) from (select @a:=0) as dummy) as total 22 from item_order as t1 23inner join (select id as order_id,date_format(ordered_at,'%y-%m') as d from orders) as t2 24using( order_id) 25group by d 26order by d;

投稿2021/10/07 06:50

yambejp

総合スコア116724

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

yambejp

2021/10/07 06:51

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

2021/10/07 07:28

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

2021/10/07 07:35

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

0

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

SQL

1SELECT 2 T.ordered_month, 3 T.monthly_sales, 4 SUM(T.monthly_sales) OVER (ORDER BY T.ordered_month) AS sales 5FROM ( 6 SELECT 7 DATE_FORMAT(O.ordered_at, '%Y-%m') AS ordered_month, 8 SUM(I.price) AS monthly_sales 9 FROM orders O 10 JOIN item_order I ON O.id = I.order_id 11 GROUP BY ordered_month 12) AS T;

投稿2021/10/07 08:32

neko_the_shadow

総合スコア2349

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.35%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問