如何修改SQL实现1-10月每位用户每月订单数及消费额统计（含0订单用户）

原SQL存在的问题 #

• 把orders.created_at的过滤条件写在WHERE子句中，会过滤掉左关联后没有匹配订单的用户（这类用户的orders.created_at为NULL，不满足时间范围条件），实际效果等价于内关联，自然无法返回订单数为0的用户
• 仅限定了1月的时间范围，没有按月份分组，无法输出多月份的统计结果

修正方案 #

1. 先生成2020年1月到10月的月份维度序列，和全量用户做笛卡尔积，保证每个用户都对应10个月份的统计行，从根本上避免漏掉无订单的用户
2. 将订单表的时间过滤条件写在LEFT JOIN的关联条件中，而非WHERE子句，避免过滤无订单的用户
3. 统计时对空值做兼容处理，确保无订单时统计结果为0而非NULL
4. 最终按用户ID、统计月份分组即可得到每月明细

以下是适配MySQL的修正后SQL：

SELECT 
    u.id,
    u.first_name,
    u.last_name,
    m.stat_month,
    COUNT(o.id) AS num_of_orders,
    IFNULL(SUM(o.total), 0) AS total_spent
FROM users u
-- 生成2020年1-10月的月份维度
CROSS JOIN (
    SELECT '2020-01' AS stat_month UNION ALL
    SELECT '2020-02' UNION ALL SELECT '2020-03' UNION ALL
    SELECT '2020-04' UNION ALL SELECT '2020-05' UNION ALL
    SELECT '2020-06' UNION ALL SELECT '2020-07' UNION ALL
    SELECT '2020-08' UNION ALL SELECT '2020-09' UNION ALL
    SELECT '2020-10'
) m
-- 关联对应用户、对应月份的订单，时间过滤写在关联条件里
LEFT JOIN orders o 
ON u.id = o.user_id 
AND DATE_FORMAT(o.created_at, '%Y-%m') = m.stat_month
GROUP BY u.id, m.stat_month
ORDER BY u.id, m.stat_month, num_of_orders ASC

如果使用PostgreSQL等其他数据库，仅需要调整日期格式化函数和月份生成语法即可，核心逻辑不变。

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