Power BI按分组计算客户连续消费年数

原始数据 #

期望结果 #

实现方案（SQL） #

通过窗口函数标记连续年份分组，再在分组内计算连续年数：

WITH ranked_data AS (
    SELECT 
        ID,
        Year,
        -- 为每个连续年份段生成唯一分组ID
        SUM(CASE WHEN Year = LAG(Year) OVER (PARTITION BY ID ORDER BY Year) + 1 THEN 0 ELSE 1 END) 
            OVER (PARTITION BY ID ORDER BY Year) AS group_id
    FROM your_table
)
SELECT 
    ID,
    Year,
    -- 在分组内生成从0开始的连续计数
    ROW_NUMBER() OVER (PARTITION BY ID, group_id ORDER BY Year) - 1 AS Yrs_Cust
FROM ranked_data
ORDER BY ID, Year;

逻辑说明 #

1. 标记连续分组：用LAG(Year) OVER (PARTITION BY ID ORDER BY Year)获取当前客户上一条记录的年份，判断当前年份是否是上一年的连续年份（差值为1）。如果不连续，则生成新的分组，通过SUM()累计得到每个连续段的group_id。
2. 计算连续年数：在每个客户的每个连续分组内，用ROW_NUMBER()生成递增序号，减1后得到从0开始的连续会员年数。

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