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

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

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

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

Q&A

解決済

3回答

453閲覧

Mysqlでの集計方法について

u-sukesan

総合スコア156

MySQL

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

0グッド

0クリップ

投稿2023/02/22 07:34

編集2023/02/24 10:04

実現したいこと

Mysqlのデーターをより簡潔に集計して出力したい

CREATE TABLE IF NOT EXISTS `orders` ( `dat` date NOT NULL DEFAULT '0000-00-00', `user_no` int(11) DEFAULT NULL, `honsu` int(11) NOT NULL DEFAULT '1', `type` int(11) DEFAULT NULL, `price` int(11) DEFAULT NULL ) ENGINE=MyISAM AUTO_INCREMENT=216569 DEFAULT CHARSET=utf8; INSERT INTO `orders` (`dat`, `user_no`, `honsu`, `type`, `price`) VALUES ('2023-02-14', 3314, 3, 1, 3000), ('2023-02-14', 3314, 3, 1, 2000), ('2023-02-15', 3556, 1, 2, 2000), ('2023-02-16', 3540, 1, 2, 1000), ('2023-02-17', 3141, 1, 1, 8000), ('2023-02-18', 3561, 2, 2, 5000), ('2023-02-18', 3573, 1, 2, 1000), ('2023-02-18', 3314, 2, 1, 3000), ('2023-02-19', 3314, 1, 1, 4000)

上記のようなテーブルがあった際に
下記の集計を出したい

①user_noごとの2月のprice合計
②user_noごとの2月のtype=1のprice合計
③user_noごとの2月のtype=2のprice合計
④user_noごとの2月のhonsuの合計

ここがネックなのですが
⑤user_noごとで、1日あたりのhonsuの合計が2を超えている場合は+2000
この月間合計を出したい

 例) user_no=3314 の場合
2023-02-14のhonsuは3本と3本で計6本なので+2000
2023-02-18のhonsuは2本なので+2000
2023-02-19のhonsuは1本なので0
2023-02の合計は4000

発生している問題・エラーメッセージ

SELECT sum(price) AS total, sum( CASE WHEN type = 1 THEN price ELSE 0 END ) AS type1_total, sum( CASE WHEN type = 2 THEN price ELSE 0 END ) AS type2_total, sum(honsu) AS honsu_total, FROM orders WHERE DATE_FORMAT(dat,'%m') = '02' GROUP BY user_no

こちらで①〜④のデータは出力できたのですが
⑤に関して下記のように試しました。

SELECT sum(price) AS total, sum( CASE WHEN type = 1 THEN price ELSE 0 END ) AS type1_total, sum( CASE WHEN type = 2 THEN price ELSE 0 END ) AS type2_total, sum(honsu) AS honsu_total, CASE WHEN sum(honsu) >= 2 THEN 2000 ELSE 0 END AS honsu_plus, FROM orders WHERE DATE_FORMAT(dat,'%m') = '02' GROUP BY user_no

が、当然結果はGROUP BY user_noとしていますのでuser_noのhonsuトータルが2以上の場合は2000となるので、2000のみ出力される

次にGROUP BY datでまとめたサブクエリをねじ込んだ場合。エラーになりました。

SELECT sum(price) AS total, sum( CASE WHEN type = 1 THEN price ELSE 0 END ) AS type1_total, sum( CASE WHEN type = 2 THEN price ELSE 0 END ) AS type2_total, sum(honsu) AS honsu_total, sum( SELECT sum(CASE WHEN sum(honsu) >= 2 THEN 2000 ELSE 0 END) AS h_plus FROM orders WHERE DATE_FORMAT(dat,'%m') = '02' GROUP BY dat ) AS honsu_plus, FROM orders WHERE DATE_FORMAT(dat,'%m') = '02' GROUP BY user_no

試したこと

まずは単純に⑤だけを試すために下記を試すもエラー

SELECT SUM(CASE WHEN sum(honsu) >= 2000 THEN 1 ELSE 0 END ) AS honsu_plus FROM orders WHERE DATE_FORMAT(dat,'%m') = '02' AND user_no =3314 GROUP BY dat

下記を試した場合

SELECT dat, CASE WHEN sum(honsu) >= 2000 THEN 2000 ELSE 0 END AS honsu_plus FROM orders WHERE DATE_FORMAT(dat,'%m') = '02' AND user_no =3314 GROUP BY dat

下記のような結果は得られる。これを合計したいだけなんだが・・・

dat honsu_plus 2023-02-14 2000 2023-02-18 2000 2023-02-19 0

どのようにすれば希望の結果が得られますでしょうか

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

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

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

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

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

Orlofsky

2023/02/22 08:27

質問は修正できます。 >発生している問題・エラーメッセージ エラーメッセージを表示されたままの内容を編集せずにそのまま追記して、エラーメッセージを読んでください。
yambejp

2023/02/24 00:57

innoDBの性能があがった現在、 ENGINE=MyISAM を選択することはほぼありません。
u-sukesan

2023/02/24 01:03

Orlofsky さん 解決済みのためエラーの内容を忘れてしまったので 以後気をつけます。すいません。 SUMなんちゃらってエラーだったと・・・
u-sukesan

2023/02/24 01:04

yambejp さん innoDBの件も調べてみます。ありがとうござます
guest

回答3

0

1-4と5は集計が違うので別々に集計してjoinするのが妥当かも
まず1-4

SQL

1select user_no, 2sum(price) as p1, 3sum(price*(type=1)) as p2, 4sum(price*(type=2)) as p3, 5sum(honsu) as h 6from orders 7where dat between '2023-02-1' and '2023-02-28' 8group by user_no

そして5

SQL

1select distinct user_no, 2sum(case when sum(honsu)>=2 then 2000 else 0 end) over (partition by user_no) as h2 3from orders 4where dat between '2023-02-1' and '2023-02-28' 5group by dat,user_no

これを連結して

SQL

1select * from ( 2select user_no, 3sum(price) as p1, 4sum(price*(type=1)) as p2, 5sum(price*(type=2)) as p3, 6sum(honsu) as h 7from orders 8where dat between '2023-02-1' and '2023-02-28' 9group by user_no 10) as t1 11inner join ( 12select distinct user_no, 13sum(case when sum(honsu)>=2 then 2000 else 0 end) over (partition by user_no) as h2 14from orders 15where dat between '2023-02-1' and '2023-02-28' 16group by dat,user_no 17) as t2 18using(user_no)

調整

集計を2段階にすればいけました

SQL

1select distinct user_no, 2sum(sum(price)) over w1 as p1, 3sum(sum(price*(type=1))) over w1 as p2, 4sum(sum(price*(type=2))) over w1 as p3, 5sum(sum(honsu)) over w1 as h, 6sum((sum(honsu)>=2)*2000) over w1 as h2 7from orders 8where dat between '2023-02-1' and '2023-02-28' 9group by dat,user_no 10window w1 as (partition by user_no) 11order by user_no

投稿2023/02/22 08:18

編集2023/02/24 00:29
yambejp

総合スコア114574

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

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

u-sukesan

2023/02/22 09:12

ありがとうございます!参考にさせていただきます
yambejp

2023/02/22 09:37

調整版はサブクエリーがないので効率はあがっています
u-sukesan

2023/02/24 01:01

ありがとうございます。 一旦解決はしましたが 初めてみるクエリ文なので 試してみます
guest

0

インラインビューの中で

SQL

1SELECT 2 dat 3 , user_no 4 , price 5 , CASE WHEN type = 1 THEN price ELSE 0 END AS type1 6 , CASE WHEN type = 2 THEN price ELSE 0 END AS type2 7 , honsu 8 , CASE WHEN honsu >= 2 THEN 2000 ELSE 0 END AS honsu 9FROM orders 10 WHERE DATE_FORMAT(dat,'%m') = '02'

として、インラインビューの外で SUM ... GROUP BY しては?

もうひとつ、
FROM の前に , があるとエラーになるから削除しては?

投稿2023/02/22 08:44

Orlofsky

総合スコア16415

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

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

u-sukesan

2023/02/22 09:14

ありがとうございます!余分な、がありましたね
guest

0

ベストアンサー

user_noとdatの単位で集計した後、その集計結果をuser_noで再集計するイメージです。

SQL

1SELECT 2 user_no, 3 SUM(total_per_day) AS total, 4 SUM(type1_total_per_day) AS type1_total, 5 SUM(type2_total_per_day) AS type2_total, 6 SUM(honsu_total_per_day) AS honsu_total, 7 SUM(CASE WHEN honsu_total_per_day >= 2 THEN 2000 ELSE 0 END) AS honsu_plus 8FROM ( 9 SELECT 10 user_no, 11 dat, 12 SUM(price) AS total_per_day, 13 SUM(CASE WHEN type = 1 THEN price ELSE 0 END) AS type1_total_per_day, 14 SUM(CASE WHEN type = 2 THEN price ELSE 0 END) AS type2_total_per_day, 15 SUM(honsu) AS honsu_total_per_day 16 FROM orders 17 WHERE DATE_FORMAT(dat,'%m') = '02' 18 GROUP BY user_no, dat 19) AS T 20GROUP BY user_no

投稿2023/02/22 08:23

編集2023/02/22 08:25
neko_the_shadow

総合スコア2225

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

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

u-sukesan

2023/02/22 09:13

ありがとうございます!希望の結果が得られました!
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

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

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

質問する

関連した質問