PostgreSQL查询每个客户最新订阅状态变更记录的实现方法

业务表存储客户订阅状态，其中订阅状态字段只有Y、N两个取值，现在需要检索每个客户最新一次订阅状态发生切换对应的完整行数据，两类状态切换都需覆盖：

• 此前订阅状态为Y，后续变更为N
• 此前订阅状态为N，后续变更为Y

两个典型业务场景：

• 客户状态按时间顺序为Y→Y→N，返回第3行记录（Y切N的最新变更记录）
• 客户状态按时间顺序为Y→N→N，返回第2行记录（Y切N的最新变更记录）
  从未发生过状态切换的客户无需返回。

以下示例默认表核心字段为：customer_id（客户唯一标识）、sub_status（订阅状态，取值Y/N）、create_time（记录生成/生效时间，用于判断记录先后顺序），你可以根据实际表结构替换字段名。

方案1：窗口函数实现（优先推荐） #

支持MySQL 8.0+、PostgreSQL、Hive、Spark SQL、ClickHouse等所有兼容窗口函数的数据库，性能最优。
核心逻辑：用LAG函数拿到每个客户上一条记录的状态，和当前状态比对筛选出所有切换记录，再按时间倒序取每个客户最新的1条即可。

WITH change_log AS (
    SELECT
        *,
        -- 按时间正序，取同客户上一条记录的订阅状态
        LAG(sub_status) OVER (PARTITION BY customer_id ORDER BY create_time) AS pre_status
    FROM your_subscription_table
),
ranked_record AS (
    SELECT
        *,
        -- 对所有切换记录按时间倒序打行号
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY create_time DESC) AS rn
    FROM change_log
    -- 筛选状态发生变化的记录，排除客户第一条无前置状态的记录
    WHERE pre_status IS NOT NULL AND pre_status != sub_status
)
-- 返回每个客户最新的切换记录
SELECT * FROM ranked_record WHERE rn = 1;

方案2：低版本MySQL兼容实现 #

适用于MySQL 5.x等不支持窗口函数的场景，数据量较大时性能弱于方案1。

SELECT t1.*
FROM your_subscription_table t1
WHERE t1.create_time = (
    SELECT MAX(t2.create_time)
    FROM your_subscription_table t2
    LEFT JOIN your_subscription_table t3
        ON t2.customer_id = t3.customer_id
        AND t3.create_time < t2.create_time
        -- 定位t2记录的直接上一条状态记录
        AND NOT EXISTS (
            SELECT 1 FROM your_subscription_table t4
            WHERE t4.customer_id = t2.customer_id
              AND t4.create_time < t2.create_time
              AND t4.create_time > t3.create_time
        )
    WHERE t2.customer_id = t1.customer_id
      AND t3.sub_status IS NOT NULL
      AND t3.sub_status != t2.sub_status
);

• 如果你的业务表用自增主键ID判断记录先后顺序，不需要依赖时间字段，直接把上述SQL里的create_time替换为自增主键字段即可，逻辑完全一致
• 上述SQL会自动过滤掉从开户至今从未发生过状态切换的客户，无需额外加判断条件

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