如何用SQL统计各process_name的不健康状态累计时长？含未恢复场景

需求与规则 #

• 仅通过SQL统计每个唯一process_name处于不健康状态的累计时长
• 状态判定规则：
  • 当value为false时，进程处于不健康状态，该状态持续到下一条value为true的记录时间点
  • 如果某进程最后一条记录的value仍为false，则默认其不健康状态持续至指定的查询结束时间（请将SQL中的'2022-06-30 09:34:00-04'替换为实际查询结束时间）
• 期望输出格式示例：(process_name=A 5seconds | process_name=B 5seconds | ...)

原始数据 #

SQL实现代码 #

WITH process_clean AS (
    -- 去重：同一进程同一时间同一状态只保留一条记录
    SELECT DISTINCT ts, process_name, value
    FROM your_table_name
),
process_next_ts AS (
    -- 获取每个不健康状态对应的下一条记录时间
    SELECT 
        process_name,
        ts AS start_time,
        -- 用LEAD获取下一条记录的时间，没有则用查询结束时间
        LEAD(ts, 1, '2022-06-30 09:34:00-04') OVER (PARTITION BY process_name ORDER BY ts) AS end_time,
        value
    FROM process_clean
)
SELECT 
    process_name,
    -- 计算总时长，只统计value为false的时间段
    SUM(EXTRACT(EPOCH FROM (end_time - start_time))) AS unhealthy_duration_seconds,
    -- 格式化输出为示例格式
    CONCAT('process_name=', process_name, ' ', SUM(EXTRACT(EPOCH FROM (end_time - start_time))), 'seconds') AS formatted_output
FROM process_next_ts
WHERE value = false
GROUP BY process_name
ORDER BY process_name;

代码说明 #

1. process_clean CTE：先对原始数据去重，避免同一进程同一时间同一状态的重复记录影响计算
2. process_next_ts CTE：使用窗口函数LEAD，为每条记录获取同进程下一条记录的时间；如果是最后一条记录，就用指定的查询结束时间填充
3. 主查询：筛选出value为false的记录，计算每个进程所有不健康时间段的总时长，并格式化输出

最终统计结果 #

按照示例格式整理的结果：
(process_name=A 5seconds | process_name=B 5seconds | process_name=C 5seconds | process_name=D 25seconds | process_name=E 5seconds | process_name=F 7seconds | process_name=G 2seconds | process_name=X 5seconds | process_name=Z 5seconds)

（注：F的时长为查询结束时间09:34:00减去09:33:52.562954，约7秒；G的时长为09:34:00减去09:33:57.988884，约2秒）

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