日別の売上を GROUP BY で集計すると、注文が1件もない日は結果に出てきません。グラフにすると日付が飛んでしまい、「0件だった」のか「集計漏れ」なのかも見分けがつかなくなります。この記事では、再帰CTE（WITH RECURSIVE）で連続した日付を作り、LEFT JOINで実データとつないで0件の日も表示する方法を紹介します。あわせて、日付を前もって入れておくカレンダーテーブルとの使い分けと、COUNTやWHERE句で起きやすい落とし穴も整理します。
このシナリオで考える
題材は、ネットショップの注文テーブル orders です。注文日時は DATETIME で持っています。2026年10月1日〜7日の1週間で、3日と5日は注文がありませんでした。
CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
ordered_at DATETIME NOT NULL,
amount INT NOT NULL,
INDEX idx_ordered_at (ordered_at)
);
INSERT INTO orders (ordered_at, amount) VALUES
('2026-10-01 10:15:00', 1200),
('2026-10-01 14:30:00', 800),
('2026-10-02 09:00:00', 1500),
('2026-10-04 18:20:00', 2000),
('2026-10-06 11:45:00', 500),
('2026-10-06 20:10:00', 700),
('2026-10-07 13:00:00', 1000);まずは素直に日別で集計してみます。
SELECT DATE(ordered_at) AS d, COUNT(*) AS cnt, SUM(amount) AS total
FROM orders
GROUP BY DATE(ordered_at)
ORDER BY d;+------------+-----+-------+
| d | cnt | total |
+------------+-----+-------+
| 2026-10-01 | 2 | 2000 |
| 2026-10-02 | 1 | 1500 |
| 2026-10-04 | 1 | 2000 |
| 2026-10-06 | 2 | 1200 |
| 2026-10-07 | 1 | 1000 |
+------------+-----+-------+3日と5日の行がありません。GROUP BY は「存在する行」をまとめる処理です。存在しない日の行を作り出すことはできません。そこで、日付の一覧を別に用意して、そこに注文データをつなぐ発想に切り替えます。
再帰CTEで連続した日付を作る
再帰CTEを使うと、テーブルがなくても連続した日付を作れます。再帰CTEは、自分自身を参照するWITH句です。MySQLでは 8.0 から使えます。
WITH RECURSIVE calendar AS (
SELECT DATE '2026-10-01' AS d -- 起点（1回だけ実行）
UNION ALL
SELECT d + INTERVAL 1 DAY FROM calendar -- 前の行に1日足す
WHERE d < '2026-10-07' -- 終点で止める
)
SELECT d FROM calendar;+------------+
| d |
+------------+
| 2026-10-01 |
| 2026-10-02 |
| 2026-10-03 |
| 2026-10-04 |
| 2026-10-05 |
| 2026-10-06 |
| 2026-10-07 |
+------------+動きは次の3ステップで理解できます。
UNION ALLより上の部分（非再帰部分）が1回だけ実行され、10月1日の行ができる- 下の部分（再帰部分）が、直前にできた行をもとに「1日後」の行を作る
- WHERE 条件を満たさなくなり、新しい行が0件になった時点で終わる
WHERE 句は無限ループを防ぐブレーキ役です。書き忘れると止まりません。とはいえMySQLには安全装置があり、既定では1000回を超えた時点でエラーになります（詳しくは落とし穴の節で触れます）。
期間を固定値で書きたくない場合は、起点と終点をテーブルから取ることもできます。公式リファレンスにも、MIN(date) から MAX(date) までの日付を作る例が載っています。
LEFT JOINで0件の日を埋める
日付の一覧ができたら、それを左側に置いて注文データを LEFT JOIN します。LEFT JOIN は左側の行をすべて残すので、注文がない日も1行として残ります。
WITH RECURSIVE calendar AS (
SELECT DATE '2026-10-01' AS d
UNION ALL
SELECT d + INTERVAL 1 DAY FROM calendar
WHERE d < '2026-10-07'
)
SELECT
c.d,
COUNT(o.id) AS cnt,
COALESCE(SUM(o.amount), 0) AS total
FROM calendar AS c
LEFT JOIN orders AS o
ON o.ordered_at >= c.d
AND o.ordered_at < c.d + INTERVAL 1 DAY
GROUP BY c.d
ORDER BY c.d;+------------+-----+-------+
| d | cnt | total |
+------------+-----+-------+
| 2026-10-01 | 2 | 2000 |
| 2026-10-02 | 1 | 1500 |
| 2026-10-03 | 0 | 0 |
| 2026-10-04 | 1 | 2000 |
| 2026-10-05 | 0 | 0 |
| 2026-10-06 | 2 | 1200 |
| 2026-10-07 | 1 | 1000 |
+------------+-----+-------+3日と5日が0件として表示されました。ポイントは3つあります。
| 書き方 | 理由 |
|---|---|
FROM calendar LEFT JOIN orders |
日付の一覧を主役にして、全日付を残す |
COUNT(o.id) |
注文がない日は o.id が NULL なので 0 になる |
COALESCE(SUM(o.amount), 0) |
SUM は対象がないと NULL を返すので 0 に置き換える |
結合条件を DATE(o.ordered_at) = c.d ではなく範囲で書いている点にも注目してください。列を関数で包むと、ordered_at のインデックスが使えなくなります。範囲で書けば、データが増えても速さを保てます。
カレンダーテーブルを用意する方法
同じ処理をあちこちで書くなら、日付だけを入れた「カレンダーテーブル」を作っておく方法もあります。祝日や営業日のフラグを持たせられるのが大きな利点です。
CREATE TABLE calendar (
cal_date DATE PRIMARY KEY,
is_holiday TINYINT NOT NULL DEFAULT 0
);
-- 10年分（約3650日）を入れるので再帰の上限を引き上げる
SET SESSION cte_max_recursion_depth = 5000;
INSERT INTO calendar (cal_date)
WITH RECURSIVE seq AS (
SELECT DATE '2026-01-01' AS d
UNION ALL
SELECT d + INTERVAL 1 DAY FROM seq
WHERE d < '2035-12-31'
)
SELECT d FROM seq;あとは集計クエリの FROM を calendar テーブルに変え、WHERE cal_date BETWEEN '2026-10-01' AND '2026-10-07' で期間を絞るだけです。2つの方法は次のように使い分けます。
| 観点 | 再帰CTE | カレンダーテーブル |
|---|---|---|
| 準備 | 不要（クエリだけで完結） | テーブル作成とデータ投入が必要 |
| 祝日・営業日 | 扱いにくい | フラグ列で簡単に扱える |
| 長い期間 | 再帰の上限に注意 | 上限を気にせず使える |
| 向いている場面 | 単発の分析、権限がない環境 | 定期レポート、ダッシュボード |
月単位で埋めたいときも考え方は同じです。再帰部分を d + INTERVAL 1 MONTH に変えれば、月初日の一覧が作れます。PostgreSQLでは generate_series('2026-10-01'::date, '2026-10-07'::date, interval '1 day') で同じ一覧が1行で作れます。
よくある落とし穴 / 注意点
- COUNT(*) だと0件の日が「1」になる：LEFT JOIN で残った行は、注文側の列が NULL の1行として存在します。
COUNT(*)はその行も数えます。必ずCOUNT(o.id)のように右側テーブルの列を数えてください - 右側テーブルの条件を WHERE に書くと日付が消える：
WHERE o.amount >= 1000と書くと、NULL の行が落ちて INNER JOIN と同じ結果になります。今回のデータなら3日・5日に加え、6日も消えてしまいます。右側の条件はON ... AND o.amount >= 1000のように ON 句へ入れましょう - 再帰の上限は既定で1000回：
cte_max_recursion_depthの既定値は1000です。3年分の日付を作ろうとすると上限を超えてエラーになります。セッション単位で引き上げるか、カレンダーテーブルに切り替えてください - 起点は DATE 型で書く：再帰CTEの列の型は、非再帰部分だけで決まります。
SELECT '2026-10-01'と文字列で書くと列が文字列型になり、比較や結合で思わぬ型変換が起きます。DATE '2026-10-01'やCAST(... AS DATE)で型を明示すると安全です
まとめ
GROUP BY だけでは、データが存在しない日は表に出せません。再帰CTEかカレンダーテーブルで「日付の一覧」を用意し、それを左に置いて LEFT JOIN するのが定番の解き方です。集計では COUNT(右側の列) と COALESCE(SUM(...), 0) を使い、右側の条件は ON 句に書くと覚えてください。単発の分析なら再帰CTE、定期レポートならカレンダーテーブルがおすすめです。
参考リンク
- MySQL 8.0 リファレンス: WITH (Common Table Expressions)
- MySQL 8.0 リファレンス: cte_max_recursion_depth
- MySQL 8.0 リファレンス: Date and Time Functions
- PostgreSQL ドキュメント: Set Returning Functions（generate_series）
アイキャッチ画像: Photo by Towfiqu barbhuiya on Unsplash
