SQLの集計がうまくできない

SQLでの質問です。
料理のメニューをオーダーテーブルから日付ごととメニューごとに集計したいと考えています。

▼food（料理）
｜id｜name｜
｜1｜麻婆豆腐｜
｜2｜ラーメン｜
｜3｜ニラレバ｜
｜4｜唐揚げ｜
｜5｜エビチリ｜
｜6｜チャーハン｜
｜7｜ごまだんご｜

▼オーダー（件）
｜id｜date｜food_01｜food_02｜food_03｜food_04｜food_05｜
｜1｜20200520｜1｜2｜null｜null｜null｜
｜2｜20200520｜2｜3｜4｜5｜null｜
｜3｜20200520｜3｜5｜null｜null｜null｜
｜4｜20200521｜1｜6｜7｜null｜null｜
｜5｜20200521｜3｜4｜5｜null｜null｜
｜6｜20200521｜3｜null｜null｜null｜null｜
｜7｜20200521｜1｜2｜3｜4｜5｜
｜8｜20200522｜1｜null｜null｜null｜null｜
｜9｜20200522｜4｜null｜null｜null｜null｜
｜10｜20200522｜1｜2｜7｜null｜null｜
｜11｜20200522｜3｜4｜null｜null｜null｜

このようにデータがとりたいのです
★理想
｜id｜20200520_count｜20200521_count｜20200522_count
｜1｜1｜2｜2｜
｜2｜2｜1｜1｜
｜3｜2｜3｜1｜
｜4｜1｜2｜2｜
｜5｜2｜2｜0｜
｜6｜0｜1｜0｜
｜7｜0｜1｜1｜

試行錯誤していますができません。

takasima20

2020/06/28 11:22

それは SQLでむりやりやるハナシじゃないような?

行動規範の内容に同意します

回答3件

データの持ち方が悪いですね。改造していいならこう。

投稿2020/06/29 10:16

yambejp

総合スコア114775

yambejp

2020/06/29 10:17

create table tbl(pk int primary key auto_increment, id int, d date, food int, unique id_food(id,food), index d_food(d,food) ); insert into tbl(id,d,food) values (1,'20200520',1), (1,'20200520',2), (2,'20200520',2), (2,'20200520',3), (2,'20200520',4), (2,'20200520',5), (3,'20200520',3), (3,'20200520',5), (4,'20200521',1), (4,'20200521',6), (4,'20200521',7), (5,'20200521',3), (5,'20200521',4), (5,'20200521',5), (6,'20200521',3), (7,'20200521',1), (7,'20200521',2), (7,'20200521',3), (7,'20200521',4), (7,'20200521',5), (8,'20200522',1), (9,'20200522',4), (10,'20200522',1), (10,'20200522',2), (10,'20200522',7), (11,'20200522',3), (11,'20200522',4); /* 確認 */ select id ,sum((rank=1) * food) as f1 ,sum((rank=2) * food) as f2 ,sum((rank=3) * food) as f3 ,sum((rank=4) * food) as f4 ,sum((rank=5) * food) as f5 from(select id,food,(select count(*)+1 from tbl where id=t1.id and pk<t1.pk) as rank from tbl as t1 ) as sub group by id

yambejp

2020/06/29 10:23

品目x日付集計 select food ,sum(d='20200520') as d0520 ,sum(d='20200521') as d0521 ,sum(d='20200522') as d0522 from tbl group by food

hatenacchi

2020/06/29 13:36

テーブル設計からのご指摘、ありがとうございます。試してみたいと思います。

行動規範の内容に同意します

SQLで行うとしたら以下の様になりますが、日付毎の集計は、SQLでやるには動的なのでお勧めできません。

何れにしても、unionを使用して正規化します。

SQL
1select food_id
2     , sum(case when date=20200520 then 1 else 0 end) as `20200520_count`
3     , sum(case when date=20200521 then 1 else 0 end) as `20200521_count`
4     , sum(case when date=20200522 then 1 else 0 end) as `20200522_count`
5     , count(*) as food_count
6from (
7        select id, food_01 as food_id, date from `オーダー` where food_01 is not null
8        union all select id, food_02, date from `オーダー` where food_02 is not null
9        union all select id, food_03, date from `オーダー` where food_03 is not null
10        union all select id, food_04, date from `オーダー` where food_04 is not null
11        union all select id, food_05, date from `オーダー` where food_05 is not null
12) order
13group by food_id

オーダーが無いもの取得したいなら、foodに上記をインラインビューにしてleft joinします。

投稿2020/06/29 00:38

編集2020/06/29 00:49

sazi

総合スコア25173

ベストアンサー

せめてRDMSの種類やバージョンを書いてください。
あと★理想のidがfoodだとわかるように書いてください。
順当に言えば「テーブル設計からやり直してください」というのが筋だと思います。

リテラルにまみれますがこんな感じならいけそうですかね～DBもわからんので未検証です。

sql
1select A.id,A.name
2  ,(select count(*) from food_order B where B.date='20200520'
3      and A.id in (B.food_01,B.food_02,B.food_03,B.food_04,B.food_05)) 20200520_count
4  ,(select count(*) from food_order B where B.date='20200521'
5      and A.id in (B.food_01,B.food_02,B.food_03,B.food_04,B.food_05)) 20200521_count
6  ,(select count(*) from food_order B where B.date='20200522'
7      and A.id in (B.food_01,B.food_02,B.food_03,B.food_04,B.food_05)) 20200522_count
8from food A