Grafana MySQL查询计算MTTR时count函数结果与固定除数结果偏差大

问题复现 #

• 固定整数除数计算：执行sum(seconds_to_resolve) / 15 /60 as "MTTR"得到预期结果25.4，结果截图：
  
• count聚合函数作为除数计算：执行sum(seconds_to_resolve) / count(seconds_to_resolve) / 60 as "MTTR"得到异常结果322，与预期偏差极大，结果截图：
  
• 原始查询语句：

SELECT
  $__timeGroupAlias(created_on,$__interval),
  sum(seconds_to_first_ack) AS "seconds_to_first_ack",
  sum(seconds_to_resolve) AS "seconds_to_first_resolve",
  count(seconds_to_resolve) AS "COUNT",
  sum(seconds_to_resolve) / count(seconds_to_resolve) /60  as "MTTR"
FROM alerts_report
WHERE
  $__timeFilter(created_on) AND
  service_name = 'RKSOL'
GROUP BY 1
ORDER BY $__timeGroup(created_on,$__interval)

根因分析 #

结果偏差来自两个核心问题：

1. Grafana面板聚合逻辑错误：原始查询按$__interval做时间分桶，会返回每个时间桶独立的MTTR值。如果面板对时间序列选择了「求和」聚合，会把所有时间桶的MTTR直接累加，远大于全局真实MTTR。手动用固定值15做除数时，本质是跳过了分桶聚合逻辑，直接用全局总告警数计算，因此结果符合预期。
2. SQL计算逻辑不严谨：没有过滤seconds_to_resolve为NULL/0的未解决无效告警，count统计的基数不符合计算要求；同时MySQL整数间除法会隐式做整数截断，进一步放大计算误差。

修复方案 #

根据展示需求二选一即可：

方案1：计算筛选时间范围内的全局MTTR #

不需要时间分桶，直接做全局聚合，显式处理浮点转换和无效值过滤：

SELECT
  NOW() AS time,
  sum(seconds_to_first_ack)/60 AS "avg_first_ack_minutes",
  count(CASE WHEN seconds_to_resolve > 0 THEN 1 END) AS "resolved_alert_count",
  -- 显式转浮点、除零保护、过滤无效告警
  IFNULL(
    sum(CASE WHEN seconds_to_resolve > 0 THEN seconds_to_resolve ELSE 0 END)
    / NULLIF(count(CASE WHEN seconds_to_resolve > 0 THEN 1 END), 0)
    / 60,
  0) AS "global_mttr_minutes"
FROM alerts_report
WHERE
  $__timeFilter(created_on) AND
  service_name = 'RKSOL'

方案2：按时间分桶展示MTTR趋势 #

保留时间分桶逻辑，同时必须将Grafana面板的时间序列聚合方式改为「平均值」（禁止选求和），SQL层面做容错处理：

SELECT
  $__timeGroupAlias(created_on,$__interval),
  sum(seconds_to_first_ack)/60 AS "avg_first_ack_minutes",
  count(CASE WHEN seconds_to_resolve > 0 THEN 1 END) AS "bucket_resolved_count",
  IFNULL(
    sum(CASE WHEN seconds_to_resolve > 0 THEN seconds_to_resolve ELSE 0 END)
    / NULLIF(count(CASE WHEN seconds_to_resolve > 0 THEN 1 END), 0)
    / 60,
  0) AS "bucket_mttr_minutes"
FROM alerts_report
WHERE
  $__timeFilter(created_on) AND
  service_name = 'RKSOL'
GROUP BY 1
ORDER BY 1

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