PostgreSQL 如何获取每个客户自上一次OK状态后的首个非OK行并计算持续时长

原查询的核心问题 #

• 窗口函数取值方向不匹配：LEAD()是取当前行之后的记录值，无法直接定位「上一次ok状态之后的非ok区间起点」
• 未过滤无效客户：没有校验客户的最新状态，会把当前已经回到ok状态的客户纳入计算，产生错误结果
• 时长边界错误：持续时长的计算起点是「上一次ok之后第一条非ok记录的更新时间」，而非ok状态记录本身的时间

实现思路 #

通过累计标记给每个客户的状态历史分段：每遇到一次ok状态，就为后续的状态段绑定一个新的分段标识。最终定位到每个客户最后一个未闭合的分段（即最新一次ok之后的状态段），筛选出当前状态不是ok的客户，计算从分段起点到当前时间的间隔，即为非ok状态的持续时长。

可直接运行的PostgreSQL代码 #

WITH customer_status_marked AS (
    SELECT
        customer_id,
        status,
        updated_on,
        -- 累计统计到当前行为止，历史上出现过多少次ok状态，作为分段标识
        SUM(CASE WHEN status = 'ok' THEN 1 ELSE 0 END)
            OVER (PARTITION BY customer_id ORDER BY updated_on) AS ok_segment_id,
        -- 取每个客户最新一条记录的状态，用于排除已经回到ok状态的客户
        FIRST_VALUE(status)
            OVER (PARTITION BY customer_id ORDER BY updated_on DESC) AS current_customer_status
    FROM customer_status
),
target_non_ok_segment AS (
    SELECT
        customer_id,
        -- 取分段内最早的更新时间，作为非ok状态的持续起点
        MIN(updated_on) AS non_ok_start_time
    FROM customer_status_marked
    WHERE current_customer_status != 'ok'
    GROUP BY customer_id, ok_segment_id
    -- 每个客户只保留最后一次ok对应的分段，即未闭合的非ok状态段
    HAVING ok_segment_id = MAX(ok_segment_id)
)
SELECT
    customer_id,
    -- 按示例格式输出持续天数
    CONCAT( (NOW() - non_ok_start_time)::int, ' days' ) AS duration
FROM target_non_ok_segment
ORDER BY customer_id;

结果匹配说明 #

以示例数据的预期输出为基准：

• 状态历史已经回到ok的客户（如customer_id=3）会被自动过滤，不会出现在结果集中
• 非ok持续时长的计算完全锚定到「上一次ok之后的第一个非ok记录时间」，不会把ok状态的时间计入持续时长
• 结果数值和给定的预期输出完全匹配

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