PostgreSQL如何按月聚合跨月重叠的SCD2活跃实体数据

问题根源 #

• 原查询以DATE_TRUNC('Month', h.start)分组，仅能统计history表记录生效首月的数据，所有早于统计月生效、统计月内仍未失效的跨月活跃实体都会被遗漏
• 测试的generate_series版本时间匹配逻辑错误：没有按照「记录有效期与统计自然月存在时间交集」的规则做关联，部分关联表的有效期判断条件缺失，无法得到正确聚合结果

活跃判定规则 #

所有带生效/失效时间的表（history、project、stage），判定记录在某自然月内处于活跃状态，统一使用区间重叠逻辑：

记录生效时间 < 统计月次月1日，且记录失效时间（空值按永久有效填充为'2099-01-01'） > 统计月首日

修正后可直接运行的SQL #

select
    count(1) as enitities_agg,
    mt as date,
    sum(h.price) filter (where c.name='HIGH') as sum_total
    -- 其余聚合指标按原有业务逻辑补充
from 
    -- 生成待统计的连续自然月序列，mt返回值为对应月份的首日
    generate_series('2022-04-01', '2022-05-01', INTERVAL '1 month') as mt
    join project as p 
        on COALESCE(p.end_date, '2099-01-01') > mt
        and p.start_date < mt + INTERVAL '1 month'
    join class as c on p.class_id = c.id
    join stage as s 
        on s.project_id = p.id
        and COALESCE(s.end, '2099-01-01') > mt
    join stage_info as si on si.stage_id = s.id 
    join history as h 
        on h.stage_info_id = si.id 
        and h.start < mt + INTERVAL '1 month'
        and COALESCE(h.end_date, '2099-01-01') > mt
where 
    h.price is not null 
    and h.price != 0 
group by mt
order by mt;

使用说明 #

• 若enitities_agg需要统计去重后的实体数量，将count(1)替换为count(distinct 对应实体主键字段)即可，避免多表关联产生的重复记录导致计数虚高
• generate_series的起止时间可按实际统计范围调整，例如统计2022全年数据可改为generate_series('2022-01-01', '2022-12-01', INTERVAL '1 month')
• 所有有效期判断条件统一写在JOIN子句中，不要放到WHERE子句，避免后续调整为左连接时出现逻辑错误

内容的提问来源于stack exchange，提问作者Simonas Petkevičius