如何高效计算客户列表版本变化以生成瀑布图？（SQL Server 2016 SP2）

我明白你现在的痛点——每次新增客户列表都要手动修改一堆UNION ALL代码，太繁琐了！针对SQL Server 2016 SP2，我们可以用窗口函数+自关联的方式实现动态计算，以后再新增列表完全不用改代码，一起来看看怎么做：

核心思路 #

先给所有列表按创建时间排序，利用窗口函数自动关联每个列表的上一个版本，然后批量判断每个客户在前后列表中的存在状态，最后汇总出每个列表的初始量、新增量、流失量，全程无需手动指定列表名称。

完整解决方案代码 #

WITH list_order AS (
    -- 给每个列表按创建时间排序，自动获取上一个列表名称
    SELECT 
        list_name,
        create_dt,
        LAG(list_name) OVER (ORDER BY create_dt) AS prev_list_name
    FROM (
        SELECT DISTINCT list_name, create_dt 
        FROM #customers
    ) AS distinct_lists
),
customer_movement AS (
    -- 标记当前列表中客户的状态：新增/留存
    SELECT 
        c.list_name,
        c.cust_id,
        CASE 
            WHEN prev.cust_id IS NULL THEN 'Add'
            ELSE 'Retain'
        END AS current_status
    FROM #customers c
    LEFT JOIN #customers prev 
        ON c.cust_id = prev.cust_id
        AND prev.list_name = (
            SELECT prev_list_name 
            FROM list_order lo 
            WHERE lo.list_name = c.list_name
        )
    UNION ALL
    -- 标记上一个列表中流失的客户（当前列表无记录）
    SELECT 
        lo.list_name,
        prev.cust_id,
        'Remove' AS current_status
    FROM #customers prev
    JOIN list_order lo 
        ON prev.list_name = lo.prev_list_name
    LEFT JOIN #customers curr 
        ON prev.cust_id = curr.cust_id
        AND curr.list_name = lo.list_name
    WHERE curr.cust_id IS NULL
),
summary_stats AS (
    -- 汇总各类状态的客户数量
    SELECT 
        list_name,
        current_status,
        COUNT(*) AS cust_count
    FROM customer_movement
    GROUP BY list_name, current_status
    UNION ALL
    -- 加入第一个列表的初始客户量
    SELECT 
        list_name,
        'Starting' AS current_status,
        COUNT(*) AS cust_count
    FROM #customers
    WHERE list_name = (
        SELECT TOP 1 list_name 
        FROM list_order 
        ORDER BY create_dt
    )
    GROUP BY list_name
)
-- 整理成瀑布图需要的格式，流失量转为负数
SELECT 
    list_name,
    current_status AS Descrip,
    CASE 
        WHEN current_status = 'Remove' THEN -cust_count
        ELSE cust_count
    END AS Custs
FROM summary_stats
ORDER BY 
    (SELECT create_dt FROM list_order lo WHERE lo.list_name = summary_stats.list_name),
    CASE current_status 
        WHEN 'Starting' THEN 1
        WHEN 'Retain' THEN 2
        WHEN 'Add' THEN 3
        WHEN 'Remove' THEN 4
    END;

代码分步解释 #

1. list_order CTE：先去重得到每个列表的唯一创建时间，用LAG()窗口函数自动获取每个列表的前序列表名称，不管新增多少列表，这个逻辑都会自动适配。
2. customer_movement CTE：
   • 通过左关联当前客户和前序列表的客户，标记出当前列表的新增客户（前序列表无记录）和留存客户（前序列表有记录）。
   • 单独查询前序列表存在但当前列表无记录的客户，标记为流失客户。
3. summary_stats CTE：汇总各类状态的客户数量，同时加入第一个列表的初始客户量作为瀑布图的起点。
4. 最终输出：把流失量转为负数，按列表时间和状态类型排序，完全匹配瀑布图的数据需求。

测试结果（用你提供的测试数据） #

运行后会得到和手动写UNION ALL一致的结果，但新增列表时完全不用改代码：

注意事项 #

• 确保create_dt能准确代表列表的先后顺序（你的测试数据已经满足这个条件）。
• SQL Server 2016 SP2完全支持LAG()窗口函数，无需担心兼容性问题。

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