PostgreSQL中如何筛选上一年12月数据以统计年末活跃客户数

要统计年末活跃客户数量，只需把原有SQL中筛选过去12个月的WHERE条件，替换成精准匹配上一年12月的逻辑即可。

修改要点 #

原筛选条件：

WHERE year_month >= to_char ((current_date - INTERVAL '12 months'), 'YYYY-MM')

替换为：

WHERE year_month = to_char(date_trunc('year', current_date) - interval '1 month', 'YYYY-MM')

这个条件的逻辑拆解：

• date_trunc('year', current_date) 获取当前年份的第一天（比如2024年就是2024-01-01）
• 减去1个月得到上一年的12月（2023-12-01）
• 用to_char格式化为YYYY-MM字符串，和你的year_month字段格式完全匹配

修改后的完整SQL #

select *
from (
    with data as (
        select 
            a.brand,
            a.d,
            a.activations,
            t.terminations,
            a.activations - t.terminations count
        from (
            select 
                c.brand, 
                dd.year_month d,
                COALESCE(case when dd.year_month is not null then count(c.customer_number) else 0 end, 0) as activations
            from generate_series(current_date - interval '8 years', current_date, '1 day') d
            left join dim_date dd on dd."date" = d.d
            left join r_contracts_report c on to_date(c.service_start_date, 'dd.mm.yyy') = d 
            where c.contract_status in ('aktiv', 'Kündigung vorgemerkt', 'gekündigt')
              and c.contract in ('3048', '3049', '3050', '3055', '3056')
            group by dd.year_month, brand
        ) a,
        (
            select 
                c.brand, 
                dd.year_month d,
                COALESCE(case when dd.year_month is not null then count(c.customer_number) else 0 end, 0) as terminations
            from generate_series(current_date - interval '8 years', current_date, '1 day') d
            left join dim_date dd on dd."date" = d.d
            left join r_contracts_report c on to_date(c.termination_date, 'dd.mm.yyy') = d 
            where c.contract_status in ('aktiv', 'Kündigung vorgemerkt', 'gekündigt')
              and c.contract in ('3048', '3049', '3050', '3055', '3056')
            group by dd.year_month, brand
        ) t
        where a.d = t.d
          and a.brand = t.brand
    )
    select
        d.d year_month,
        d.brand,
        sum(count) over (order by d.d asc rows between unbounded preceding and current row) eop
    from data d
    where d.brand = '3'
) as foo 
WHERE year_month = to_char(date_trunc('year', current_date) - interval '1 month', 'YYYY-MM')

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