如何编写SQL基于事件变更表统计指定时间范围内各状态物品数量

问题分析 #

原有查询的核心问题是仅统计了每日发生颜色变更的物品记录，没有覆盖物品在非变更日的持续状态，同时存在表别名拼写错误，因此输出结果不符合需求。

解决思路 #

• 为每个物品的变更记录生成状态有效时间段，用LEAD窗口函数取同物品下一次变更时间作为当前状态的结束时间
• 关联需要统计的时间区间的日期序列，匹配每个日期下各物品对应的有效状态
• 按日期分组统计各颜色的物品数量

实现代码 #

WITH 
-- 步骤1：给每个物品的变更记录补充状态有效期
item_state_period AS (
    SELECT 
        id,
        color_end AS current_color,
        date_changed AS state_start_time,
        -- 取同id下一次的变更时间作为当前状态的结束时间，无后续变更则默认用当前时间作为结束
        LEAD(date_changed, 1, CURRENT_TIMESTAMP) OVER (PARTITION BY id ORDER BY date_changed) AS state_end_time
    FROM events
),
-- 步骤2：生成过去一年的日期序列，可根据需求调整时间范围
date_series AS (
    SELECT date_trunc('day', (current_date - offs)) AS stat_date
    FROM generate_series(0, 365, 1) AS offs
)
-- 步骤3：关联统计每日各颜色的物品数量
SELECT
    to_char(d.stat_date, 'YYYY-MM-DD') AS stat_date,
    COUNT(CASE WHEN s.current_color = 'green' THEN 1 END) AS green_count,
    COUNT(CASE WHEN s.current_color = 'yellow' THEN 1 END) AS yellow_count,
    COUNT(CASE WHEN s.current_color = 'red' THEN 1 END) AS red_count,
    COUNT(s.current_color) AS total_count
FROM date_series d
LEFT JOIN item_state_period s
    -- 统计日期落在状态有效期内即匹配成功
    ON d.stat_date >= date_trunc('day', s.state_start_time) 
    AND d.stat_date < date_trunc('day', s.state_end_time)
GROUP BY d.stat_date
ORDER BY d.stat_date;

注意事项 #

• 如需统计自定义时间范围，修改date_series中generate_series的参数即可
• 原示例数据中id=2的两条记录时间顺序颠倒，SQL已通过ORDER BY date_changed自动排序，无需提前处理源数据
• 若需要排除已失效/删除的物品，可在item_state_period中补充对应过滤条件

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