如何修改SQL实现按月份累计统计客户数量的需求

场景1：累计统计人次（同一客户多月多次出现重复计数） #

直接修改原有查询的CASE WHEN判断条件，将单月匹配改为小于等于目标月份即可，整体结构不需要调整：

select A.location as location
     , count(Case When SUBSTRING(A.base_date,5,2) <= '01' Then A.customer_no else null end) as "jan"
     , count(Case When SUBSTRING(A.base_date,5,2) <= '02' Then A.customer_no else null end) as "feb"
     , count(Case When SUBSTRING(A.base_date,5,2) <= '03' Then A.customer_no else null end) as "mar"
     -- 4月至11月按相同规则修改判断条件即可
     , count(Case When SUBSTRING(A.base_date,5,2) <= '12' Then A.customer_no else null end) as "dec"
from table_income A group by A.location;

提示：该写法兼容绝大多数SQL引擎，修改成本极低，执行逻辑和原有查询完全对齐

场景2：累计统计去重客户数（同一客户多次出现仅计数1次） #

如果需要统计截止到每个月的新增+历史累计去重客户量，避免同一客户被重复统计，可以先提前计算每个客户在对应地区的首次出现月份，再做累计统计：

with customer_first_month as (
    select 
        location,
        customer_no,
        min(SUBSTRING(base_date,5,2)) as first_appear_mth
    from table_income
    group by location, customer_no
)
select 
    location,
    count(case when first_appear_mth <= '01' then customer_no end) as jan,
    count(case when first_appear_mth <= '02' then customer_no end) as feb,
    count(case when first_appear_mth <= '03' then customer_no end) as mar,
    -- 4月至11月按相同规则补充
    count(case when first_appear_mth <= '12' then customer_no end) as dec
from customer_first_month
group by location;

注意：如果你的base_date存储格式不是yyyymmdd字符串类型，需要调整SUBSTRING函数的截取参数，确保能正确提取月份即可

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