如何在Snowflake中按条件获取首个失败事件时间戳？

我在Snowflake中有一张events表，每行对应一个事件。status字段取值为failed（表示错误）或completed（表示错误已解决）。需要为每个customer_id找到两个时间戳：resolved_at和first_failed_at，最终计算这两个时间戳之间的错误解决时长（天数）。

我已经通过如下SQL获取了resolved_at，但不知道如何获取first_failed_at。需要注意的是，可能存在连续多个失败事件的情况。

with windowed as (
    select 
        *,
        lag(status) over (partition by customer_id order by created_at) as prev_status,
        lead(status) over (partition by customer_id order by created_at) as next_status,
        lead(created_at) over (partition by customer_id order by created_at) as next_created_at
    from events
)

select 
    event_id,
    customer_id,
    status,
    created_at,
    case 
        when status = 'failed' and next_status = 'completed' then next_created_at
        else null
    end as resolved_at
from windowed

示例数据及期望结果 #

解决方案 #

要获取first_failed_at，核心是识别每个连续失败事件组的起始点，然后为组内所有失败事件关联该组的第一个时间戳。可以通过以下SQL实现：

with event_groups as (
    select 
        *,
        -- 标记每个失败组的起始：当前是failed且上一个不是failed（或上一个不存在）
        case 
            when status = 'failed' and (lag(status) over (partition by customer_id order by created_at) != 'failed' or lag(status) over (partition by customer_id order by created_at) is null)
            then 1
            else 0
        end as is_failure_start,
        lead(status) over (partition by customer_id order by created_at) as next_status,
        lead(created_at) over (partition by customer_id order by created_at) as next_created_at
    from events
),
grouped_failures as (
    select 
        *,
        -- 累积求和，为每个连续失败组分配唯一ID
        sum(is_failure_start) over (partition by customer_id order by created_at rows between unbounded preceding and current row) as failure_group_id
    from event_groups
),
failure_group_starts as (
    select 
        customer_id,
        failure_group_id,
        min(created_at) as first_failed_at
    from grouped_failures
    where status = 'failed'
    group by customer_id, failure_group_id
)
select 
    g.event_id,
    g.customer_id,
    g.status,
    g.created_at,
    -- 保留原有的resolved_at逻辑
    case 
        when g.status = 'failed' and g.next_status = 'completed' then g.next_created_at
        else null
    end as resolved_at,
    -- 关联对应失败组的起始时间
    fs.first_failed_at
from grouped_failures g
left join failure_group_starts fs 
    on g.customer_id = fs.customer_id 
    and g.failure_group_id = fs.failure_group_id
order by g.customer_id, g.created_at;

逻辑说明 #

1. 标记失败组起始：用lag()函数判断当前失败事件是否是连续失败组的第一个，标记为is_failure_start。
2. 生成失败组ID：通过累积求和is_failure_start，为每个连续失败事件组分配唯一的failure_group_id。
3. 获取组内最早时间：按customer_id和failure_group_id分组，取每组的最小created_at作为first_failed_at。
4. 关联结果：将分组后的起始时间关联回原事件，同时保留原有的resolved_at计算逻辑。

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