如何查找每个客户阶段的首次出现记录及对应变更时间

核心思路 #

你遇到的重复问题是因为直接使用LAG函数仅完成了相邻行的阶段比对，但没有过滤连续相同阶段的冗余行，我们可以通过阶段变化标记+过滤的方式实现需求：

1. 按客户ID分组、按时间升序排序所有客户记录
2. 给每一行打阶段变化标记：若为该客户第一条记录、或当前行阶段与上一行阶段不一致，则标记为1，否则为0
3. 仅保留标记为1的行，对应时间即为切换至当前阶段的起始时间

参考实现代码 #

通用ANSI SQL（适配MySQL 8.0+/PostgreSQL/Oracle等支持窗口函数的数据库） #

WITH phase_change_marker AS (
    SELECT
        customer_id,
        customer_phase,
        operation_time, -- 替换为你表中记录生成时间的实际字段名
        -- 生成阶段变化标记
        CASE
            WHEN LAG(customer_phase) OVER (PARTITION BY customer_id ORDER BY operation_time) IS NULL
                OR LAG(customer_phase) OVER (PARTITION BY customer_id ORDER BY operation_time) <> customer_phase
            THEN 1
            ELSE 0
        END AS is_phase_change
    FROM customer_data_table -- 替换为你的客户数据表实际表名
)
SELECT
    customer_id,
    customer_phase,
    operation_time AS phase_switch_time
FROM phase_change_marker
WHERE is_phase_change = 1
ORDER BY customer_id, operation_time;

低版本MySQL（无CTE支持）适配写法 #

SELECT
    customer_id,
    customer_phase,
    operation_time AS phase_switch_time
FROM (
    SELECT
        customer_id,
        customer_phase,
        operation_time,
        CASE
            WHEN LAG(customer_phase) OVER (PARTITION BY customer_id ORDER BY operation_time) IS NULL
                OR LAG(customer_phase) OVER (PARTITION BY customer_id ORDER BY operation_time) <> customer_phase
            THEN 1
            ELSE 0
        END AS is_phase_change
    FROM customer_data_table
) AS phase_change_marker
WHERE is_phase_change = 1
ORDER BY customer_id, operation_time;

注意事项 #

如果存在同一客户同一时间点有多条不同阶段记录的情况，可以在窗口函数的ORDER BY后补充第二排序字段（比如日志自增ID、行号等）保证排序逻辑稳定，避免结果异常。

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