优化可用性百分比计算：合并多复杂SQL查询并修正数据缺失问题

我之前提出过类似问题，但发现计算结果存在错误——原计算未将数据采集失败或传输丢失的缺失点计入停机时长，此类情况应被判定为停机。我编写了以下几个查询，但尝试合并为单个查询时出现错误，想请教是否有方法将这些计算简化并合并为一个优化的单查询？

非计划停机查询（UO） #

SELECT 
(SELECT 
  (count(outage_list.*) * 30)::float/60 AS "outage"
FROM
(SELECT
  time_bucket_gapfill('30.0s',time) AS "time",
  rtime
FROM query_response_time
WHERE
  $__timeFilter("time") AND
  cnode = '$cnode' AND   
  node = '$node' AND
  query = '$query' AND
  rtime < 0) AS "outage_list"
) + (SELECT 
(SELECT 
(count(*) * 30)::float/60 AS "reporting_period"
FROM (SELECT
  time_bucket_gapfill('30s',time) AS "time",
  avg(rtime)
FROM query_response_time
WHERE
  $__timeFilter("time") AND
  cnode = '$cnode' AND
  query = '$query' AND
  node = '$node'
GROUP BY 1) AS sub_query_rp) - (SELECT
  (count(*) * 30)::float/60 AS "probe_minutes"
FROM query_response_time
WHERE
  $__timeFilter("time") AND
  cnode = '$cnode' AND   
  node = '$node' AND
  query = '$query') AS "packet_lost") AS "unplan_outage"

统计周期查询（T） #

SELECT 
(count(*) * 30)::float/60 AS "reporting_period"
FROM (SELECT
  time_bucket_gapfill('30s',time) AS "time",
  avg(rtime)
FROM query_response_time
WHERE
  $__timeFilter("time") AND
  cnode = '$cnode' AND
  query = '$query' AND
  node = '$node'
GROUP BY 1) AS sub_query_rp

计划停机（PO） #

该值为Grafana中的整数变量。

可用性计算公式 #

可用性百分比 = [(T - (UO + PO)) / T] * 100

优化后的单查询方案 #

通过CTE（公共表表达式）提取重复逻辑，仅执行一次表扫描即可计算所有所需统计量，避免多层嵌套和重复查询：

WITH time_buckets AS (
    -- 生成统计周期内所有30秒时间桶，包含缺失填充点
    SELECT 
        time_bucket_gapfill('30s', time) AS bucket_time,
        count(rtime) AS record_count,
        count(CASE WHEN rtime < 0 THEN 1 END) AS bad_record_count
    FROM query_response_time
    WHERE
        $__timeFilter(time) AND
        cnode = '$cnode' AND
        node = '$node' AND
        query = '$query'
    GROUP BY 1
),
period_stats AS (
    -- 计算统计周期总时长(T)、实际有数据的时长
    SELECT
        count(*) * 30::float / 60 AS reporting_period,
        sum(CASE WHEN record_count > 0 THEN 30 ELSE 0 END)::float / 60 AS probe_minutes
    FROM time_buckets
),
outage_stats AS (
    -- 计算rtime<0对应的停机时长
    SELECT
        count(*) * 30::float / 60 AS outage_duration
    FROM time_buckets
    WHERE bad_record_count > 0
)
SELECT
    -- 计算非计划停机时长(UO)
    (os.outage_duration + (ps.reporting_period - ps.probe_minutes)) AS unplan_outage,
    ps.reporting_period AS reporting_period,
    -- 直接计算可用性百分比（按需保留）
    ((ps.reporting_period - (os.outage_duration + (ps.reporting_period - ps.probe_minutes) + $PO)) / ps.reporting_period) * 100 AS availability_percent
FROM period_stats ps, outage_stats os;

优化说明 #

1. time_buckets：一次性完成时间桶分组，同时统计每个桶的有效记录数、异常记录数，避免重复扫描表
2. period_stats：基于时间桶计算统计周期总时长，以及实际有数据的时长，两者差值即为缺失点对应的停机时长
3. outage_stats：筛选出存在异常响应时间的时间桶，计算这类情况的停机时长
4. 最终查询合并所有统计结果，直接输出非计划停机时长、统计周期时长，还可按需直接计算可用性百分比

内容的提问来源于stack exchange，提问作者Muhammad Al-iman Mohd Zain