MySQLでデータが存在しない集計方法

Question

MySQLでデータの集計をおこなっています。

[date] [price]
2017/05/01 12000
2017/05/02 12000
2017/05/02 8000
2017/05/02 8000
2017/05/05 15000
2017/05/05 6000
とあった場合、
2017/05/01 12000
2017/05/02 28000
2017/05/03 0
2017/05/04 0
2017/05/05 21000
とレコードを出せればと思っております。

これらを実現させるには、
カレンダーテーブルを作成し、dataでjoinという形がよいのでしょうか。

お分かりの方がいらっしゃいましたらご教授お願いいたします。

Answer 1

SELECT
  DATE
  ,SUM(PRICE) AS TOTAL_PRICE
FROM
  テーブル
GROUP BY
 DATE

これでいけるはずです。

Comments

[yambejp]

命題は存在しない日付を0にするということなので少しズレているかもしれませんね

Answer 2

そうですね
集計したテーブルをサブクエリにして
カレンダーテーブルにleft joinしてあげてください

追記

ちょっとわかりにくいかもしれないのでプロシージャの書き方を追記しておきます。

• 元データ

create table sales(date date,price int);
insert into sales values
('2017-05-01',12000),
('2017-05-02',12000),
('2017-05-02',8000),
('2017-05-02',8000),
('2017-05-05',15000),
('2017-05-05',6000);

• プロシージャを作ります。

drop procedure if exists temp_cal;
delimiter //
create procedure temp_cal(in min date ,in max date)
begin 
create temporary table temp(date date);
set @d=min;
while @d<=max do
insert into temp values(@d);
set @d:=@d + interval 1 day;
end while;
end
//
delimiter ;

• プロシージャをcallしつつ、カレンダーに連結します

call temp_cal('2017-05-01','2017-05-05');
select t1.date,coalesce(sum_price,0) as sum_price from temp as t1
left join (select date,sum(price) as sum_price from sales group by date) as t2
on t1.date=t2.date
order by date;

Comments

[yambejp]

プロシージャを利用した処理を追記しておきました。
日付ついては任意の範囲を指定できます

[h_h0323]

こちらで実装を試してみようと思います。
有難う御座います。