如何将含状态变更时间戳列的数据转换为近3个月逐日滚动统计结果

方案选择结论 #

优先选择「先生成滚动3个月的连续日历表，再和原数据关联聚合统计」的方案，相比多次自定义count查询后分组的方案，可维护性、执行效率更高，也能天然解决无数据日期补0的问题。

具体实现思路 #

以SQL实现为例：

1. 生成连续日期序列：用递归CTE生成最近3个月的所有自然日，避免日期断层。示例代码（MySQL 8.0+兼容）：

WITH RECURSIVE calendar AS (
    -- 起始日期为当前日期向前推3个月
    SELECT DATE_SUB(CURDATE(), INTERVAL 3 MONTH) AS calendar_date
    UNION ALL
    SELECT DATE_ADD(calendar_date, INTERVAL 1 DAY) 
    FROM calendar 
    WHERE calendar_date < CURDATE()
)

2. 关联原表统计各状态数量：用条件聚合统计每日各状态的进入次数，NULL值会自动被COUNT忽略，无需额外判断：

SELECT
    c.calendar_date AS `Date`,
    COUNT(CASE WHEN DATE(t.`Date Created`) = c.calendar_date THEN 1 END) AS `Created`,
    COUNT(CASE WHEN DATE(t.`Date Ready to Start`) = c.calendar_date THEN 1 END) AS `Ready to Start`,
    COUNT(CASE WHEN DATE(t.`Date In Progress`) = c.calendar_date THEN 1 END) AS `In Progress`,
    COUNT(CASE WHEN DATE(t.`Date Dev Complete`) = c.calendar_date THEN 1 END) AS `Dev Complete`,
    COUNT(CASE WHEN DATE(t.`Date Test Complete`) = c.calendar_date THEN 1 END) AS `Test Complete`,
    COUNT(CASE WHEN DATE(t.`Date Accepted`) = c.calendar_date THEN 1 END) AS `Accepted`
FROM calendar c
LEFT JOIN 你的原表名 t ON 1=1
GROUP BY c.calendar_date
ORDER BY c.calendar_date;

两种方案对比 #

• 多次count分组方案：需要针对每个状态列单独做group by统计，再把多组结果按日期join，代码冗余度高，新增状态列需要新增子查询，无数据的日期还需要额外处理补0逻辑，维护成本高。
• 日历表关联方案：代码结构清晰，新增状态仅需要加一行条件聚合逻辑，天然包含所有连续日期，无数据时自动输出0，完全符合输出要求。

注意事项 #

如果你使用的数据库不支持递归CTE（比如MySQL 5.x版本），可以预先创建一张静态的日期维度表，直接关联统计即可，执行效率比递归生成更高。

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