PostgreSQL基于系统状态记录计算每日平均正常运行时间

核心计算逻辑 #

• 先将表中分离的日期、时间字段拼接为标准UTC时间戳，通过窗口函数补全每个状态段的起止边界，自动承接跨天的延续状态
• 对跨自然日的状态段做拆分，分别计算每个状态在对应日期内的有效时长
• 按日期分组统计正常运行（status=0）时长占当日总统计时长的比例，保留2位小数即为当日平均正常运行率

实现代码 #

假设存储状态的表名为system_status，可直接执行以下SQL：

WITH status_segments AS (
    SELECT
        (interval_date || ' ' || interval_time_utc)::TIMESTAMP AT TIME ZONE 'UTC' AS start_time,
        status,
        COALESCE(
            LEAD((interval_date || ' ' || interval_time_utc)::TIMESTAMP AT TIME ZONE 'UTC') OVER (ORDER BY interval_date, interval_time_utc),
            -- 最后一条状态默认统计到当日23:59:59，若需统计到SQL执行时的当前UTC时间，可将下方值替换为 NOW() AT TIME ZONE 'UTC'
            (interval_date || ' 23:59:59')::TIMESTAMP AT TIME ZONE 'UTC'
        ) AS end_time
    FROM system_status
),
all_segments AS (
    SELECT start_time, end_time, status FROM status_segments
    UNION ALL
    -- 补全每日0点的延续状态：每日第一个状态点前的状态承接前一天最后一次记录的状态
    SELECT
        (d::DATE || ' 00:00:00')::TIMESTAMP AT TIME ZONE 'UTC' AS start_time,
        MIN(start_time) AS end_time,
        (SELECT status FROM status_segments s WHERE s.start_time < (d::DATE || ' 00:00:00')::TIMESTAMP AT TIME ZONE 'UTC' ORDER BY start_time DESC LIMIT 1) AS status
    FROM GENERATE_SERIES((SELECT MIN(interval_date) FROM system_status), (SELECT MAX(interval_date) FROM system_status), '1 day'::INTERVAL) d
    WHERE NOT EXISTS (SELECT 1 FROM status_segments s WHERE s.start_time::DATE = d::DATE AND s.start_time::TIME = '00:00:00')
    GROUP BY d
),
daily_duration AS (
    SELECT
        GREATEST(start_time, day::DATE::TIMESTAMP AT TIME ZONE 'UTC') AS seg_start,
        LEAST(end_time, (day::DATE + 1)::TIMESTAMP AT TIME ZONE 'UTC') AS seg_end,
        status,
        day::DATE AS stat_date
    FROM all_segments
    JOIN GENERATE_SERIES(
        (SELECT MIN(interval_date) FROM system_status),
        (SELECT MAX(interval_date) FROM system_status),
        '1 day'::INTERVAL
    ) day ON start_time < (day::DATE + 1)::TIMESTAMP AT TIME ZONE 'UTC' AND end_time > day::DATE::TIMESTAMP AT TIME ZONE 'UTC'
)
SELECT
    stat_date AS interval_date,
    ROUND(
        SUM(CASE WHEN status = 0 THEN EXTRACT(EPOCH FROM (seg_end - seg_start)) ELSE 0 END)
        / SUM(EXTRACT(EPOCH FROM (seg_end - seg_start)))::NUMERIC,
        2
    ) AS average_uptime
FROM daily_duration
GROUP BY stat_date
ORDER BY stat_date;

适配说明 #

• 代码默认按秒级粒度统计，若业务使用分钟级状态记录，只需将状态段结束时间统一减1秒，即可完全匹配分钟级人工计算结果
• 可通过修改ROUND函数的第二个参数调整可用率的小数保留位数
• 针对给出的样例数据，执行代码后除2022-07-07因人工计算四舍五入偏差外，其余日期结果完全匹配预期值

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