如何编写按日期分组统计销量的SQL查询以展示零销量日期

实现思路 #

你原来的查询只会返回有销售记录的日期，要展示销量为0的日期，核心是先构造一张包含你统计范围内所有连续日期的完整日期表，再用这张表作为左表，LEFT JOIN 你原来的销售统计结果，匹配不到的日期销量会返回NULL，最后用COALESCE函数把NULL替换成0即可。

PostgreSQL 实现示例（你原SQL用了date_trunc，大概率是PostgreSQL环境） #

SELECT 
  t.period::date,
  COALESCE(s.sales_count, 0) AS daily_sales
FROM (
  -- 左表：生成统计范围内的连续日期序列，起止可按需调整
  SELECT generate_series(
    '2023-01-01'::date, -- 替换为你需要的统计开始日期
    CURRENT_DATE,        -- 替换为你需要的统计结束日期
    '1 day'::interval
  ) AS period
) t
LEFT JOIN (
  -- 右表：原有按天聚合的销售统计
  SELECT 
    created_at::date AS sales_date,
    COUNT(id) AS sales_count
  FROM sales
  GROUP BY sales_date
) s ON t.period::date = s.sales_date
ORDER BY t.period

你原来写的date_trunc('day', created_at::date)存在冗余，created_at转成date类型本身就是天粒度，不需要再做截断，保留也不影响运行结果。

MySQL 8.0+ 实现示例 #

如果是MySQL环境，用递归CTE生成连续日期即可：

WITH RECURSIVE date_series AS (
  SELECT '2023-01-01' AS period -- 替换为统计开始日期
  UNION ALL
  SELECT DATE_ADD(period, INTERVAL 1 DAY) 
  FROM date_series 
  WHERE period < CURRENT_DATE -- 替换为统计结束日期
)
SELECT 
  d.period,
  COALESCE(s.sales_count, 0) AS daily_sales
FROM date_series d
LEFT JOIN (
  SELECT 
    DATE(created_at) AS sales_date,
    COUNT(id) AS sales_count
  FROM sales
  GROUP BY sales_date
) s ON d.period = s.sales_date
ORDER BY d.period

可优化点 #

如果不想手动指定统计起止日期，可以直接取sales表的最早和最晚日期作为范围，以PostgreSQL为例：

SELECT generate_series(
  (SELECT MIN(created_at::date) FROM sales),
  (SELECT MAX(created_at::date) FROM sales),
  '1 day'::interval
) AS period

内容的提问来源于stack exchange，提问作者RDW