複数のテーブルを結合し、さらに指定された期間内の半月ごとの集計を出したい

Question

前提・実現したいこと

お店テーブル、予約テーブル、注文テーブルの3つのテーブルがあります。
以下の3つのテーブルに対して、半月ごとのそれぞれのお店のamountを集計したいです。

お店テーブル

id(bigint(20))	shop_name(varchar(50))
1	お店A
2	お店B
3	お店C

予約テーブル

id(bigint(20))	created_at(datetime)
1	2020-10-05 11:25:25
2	2020-10-20 11:25:25
3	2020-11-05 11:25:25
4	2020-11-20 11:25:25

注文テーブル

id(bigint(20))	amount(decimal(10,0))	予約id(bigint(20))	お店id(bigint(20))
1	100	1	1
2	250	2	2
3	350	3	3
4	500	4	1

例えば2020年10月1日から2020年11月30日までの期間で集計する場合、期待する出力結果としては以下を期待してます。
|dt_from|dt_to|shop_name|total_amount|
|:--|:--|:--|
|2020-10-01|2020-10-15|お店A|100|
|2020-10-01|2020-10-15|お店B|0|
|2020-10-01|2020-10-15|お店C|0|
|2020-10-16|2020-10-31|お店A|0|
|2020-10-16|2020-10-31|お店B|250|
|2020-10-16|2020-10-31|お店C|0|
|2020-11-01|2020-11-15|お店A|0|
|2020-11-01|2020-11-15|お店B|0|
|2020-11-01|2020-11-15|お店C|350|
|2020-11-16|2020-11-30|お店A|500|
|2020-11-16|2020-11-30|お店B|0|
|2020-11-16|2020-11-30|お店C|0|

クエリ

下記を参考にして書きました。
https://ja.stackoverflow.com/questions/21510/%E7%89%B9%E5%AE%9A%E3%81%AE%E6%9C%9F%E9%96%93%E3%81%94%E3%81%A8%E3%81%AB%E9%9B%86%E8%A8%88%E3%81%99%E3%82%8B%E6%96%B9%E6%B3%95

SELECT
c.dt_from,
c.dt_to,
s.shop_name,
IFNULL(sum(o.amount), 0) AS total_amount
FROM
(SELECT
   @f:='2020-10-01' AS dt_from,
   DATE_ADD(@f, INTERVAL 14 DAY) AS dt_to
 UNION ALL
 SELECT
   @f:=DATE_ADD(@f, INTERVAL 15 DAY) AS dt_from,
   DATE_ADD(@f, INTERVAL 14 DAY) AS dt_to
 FROM
   reservations
 WHERE
   @f < '2020-11-15'
) AS c -- cはcalendarのc
LEFT OUTER JOIN reservations r
ON c.dt_from <= DATE(r.created_at) AND DATE(r.created_at) <= c.dt_to
LEFT JOIN orders o
ON o.reservation_id = r.id 
INNER JOIN shops s
ON r.shop_id = s.id
GROUP BY c.dt_from, c.dt_to, s.shop_name
ORDER BY c.dt_from

クエリ実行結果

|dt_from|dt_to|shop_name|total_amount|
|:--|:--|:--|
|2020-10-01|2020-10-15|お店A|100|
|2020-10-16|2020-10-31|お店B|250|
|2020-11-01|2020-11-15|お店C|350|
|2020-11-16|2020-11-30|お店A|500|

注文があったときだけしか返ってきません。

ご回答よろしくお願いします。

補足情報

MySQL 5.7.12

Comments

[Orlofsky]

URLは https://teratail.com/help/question-tips#questionTips3-7 の [リンク] で [リンク先の説明](http...) に修正してください。

[yambejp]

とりあえずサンプルテーブルをcreate table+insertで書いてください

Answer 1

日付の期間とお店の組み合わせを軸にしていないからだと思います。
shopsとcをcross join したものからreservationsやordersをleft joinする事ですね。

ただリンク先にあるように

MySQLのユーザ変数は評価順序が未定義です。

について、覚悟が必要だと思います。

Answer 2

dt_from 	dt_to
2020-10-01 	2020-10-15
2020-10-16 	2020-10-31
2020-11-01 	2020-11-15
2020-11-16 	2020-11-30

のテーブルをあらかじめ作っておくか、プロシージャで作ればよいと思います