SQL如何统计指定日期范围内每日处于活跃状态的订单数量

1. 生成连续日期序列：首先生成你指定的统计区间（2021-10-04 至 2021-10-09）的所有日期，避免无订单的日期（比如2021-10-05）从结果中消失
2. 关联订单表匹配规则：将每个日期和订单表做关联，匹配规则为 日期 >= CreatedOn AND 日期 <= DeliveredOn，满足规则的订单即属于当日活跃订单
3. 分组统计计数：按日期分组，统计匹配到的订单数量，无匹配的日期计数为0

-- 递归CTE生成连续日期序列
WITH RECURSIVE date_range AS (
    SELECT '2021-10-04' AS date
    UNION ALL
    SELECT DATE_ADD(date, INTERVAL 1 DAY)
    FROM date_range
    WHERE date < '2021-10-09'
)
SELECT 
    dr.date AS `Date`,
    COUNT(o.ID) AS `ActiveCount`
FROM date_range dr
LEFT JOIN Orders o 
    ON dr.date >= o.CreatedOn 
    AND dr.date <= o.DeliveredOn
GROUP BY dr.date
ORDER BY dr.date;

• 开头WITH RECURSIVE date_range部分是递归生成指定区间的所有连续日期，后续调整统计区间直接修改里面的起止日期即可
• LEFT JOIN Orders为左关联，保证所有生成的日期都会保留，不会因为当天没有活跃订单就被过滤
• 关联条件完全匹配你的统计规则，只要日期在订单的创建和送达时间之间就计入统计
• COUNT(o.ID)统计匹配到的订单数，没有匹配到的日期自动返回0，和你预期的输出结果完全一致

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