如何根据指定日期范围统计多款产品每日的单位使用总量

现有存储产品信息的数据表，包含Product_id、Start_date、End_date、Units四个字段，分别对应产品ID、生效开始日期、生效结束日期、单位使用量。需要基于指定的完整统计日期范围，计算区间内每一天所有处于生效周期内的产品Units总和，没有生效产品的日期统计结果记为0。

示例输入数据 #

期望输出结果 #

通用实现逻辑（SQL版） #

核心思路是先生成统计区间内的完整连续日期序列，再和产品表关联匹配生效日期，最后分组聚合即可，示例代码如下：

-- 1. 递归生成统计区间的连续日期序列，示例统计范围为2021-07-07至2021-07-12
WITH RECURSIVE date_range AS (
    SELECT STR_TO_DATE('07/07/2021', '%m/%d/%Y') AS stat_date
    UNION ALL
    SELECT stat_date + INTERVAL 1 DAY
    FROM date_range
    WHERE stat_date < STR_TO_DATE('07/12/2021', '%m/%d/%Y')
)
-- 2. 左关联产品表匹配生效日期，求和无数据时补0
SELECT 
    DATE_FORMAT(d.stat_date, '%m/%d/%Y') AS Date,
    COALESCE(SUM(p.Units), 0) AS Units
FROM date_range d
LEFT JOIN 你的产品表名 p 
    ON d.stat_date BETWEEN STR_TO_DATE(p.Start_date, '%m/%d/%Y') AND STR_TO_DATE(p.End_date, '%m/%d/%Y')
GROUP BY d.stat_date
ORDER BY d.stat_date

注意事项 #

• 如果使用不支持递归CTE的数据库，可以提前维护一张日期维度表替换上述的date_range临时表
• 日期格式化、日期运算函数可根据所用数据库调整，比如SQL Server用CONVERT、PostgreSQL用TO_CHAR
• 注意统一关联条件的日期格式，避免字符串格式不匹配导致关联错误

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