如何从每5分钟时间区间筛选最优ping_response值？

数据集 #

需求 #

从每5分钟时间区间获取最优的ping_response值，优先级规则：200 > 400 > 其他值（如0），只要区间内存在200，就优先返回200。

尝试的SQL及问题 #

我先写了内层排序查询：

SELECT ping_response, time
FROM esn_ping
WHERE time <= DATE_SUB(NOW(), INTERVAL 1 HOUR)
ORDER BY time, CASE ping_response
  WHEN 200 THEN 0
  WHEN 400 THEN 1
  ELSE 2
END ASC

这个查询能得到按时间和优先级排序的结果，但在外层按time分组后，内层的排序完全失效，比如2024-01-04 21:35:00区间本该返回200，结果却得到了0。

错误原因 #

GROUP BY的逻辑不会继承内层查询的排序结果。当你对time分组时，数据库会按照自身的存储或优化规则随机选取每组中的一条记录，完全不考虑内层的排序顺序，所以才会出现选中0的情况。

修正方案 #

方案1：窗口函数（推荐） #

用ROW_NUMBER()窗口函数给每个5分钟区间内的记录按优先级标记行号，然后取行号为1的记录，这是最直观的解决方式。

支持CTE的数据库（如MySQL 8+、PostgreSQL等）

WITH ranked_pings AS (
  SELECT
    ping_response,
    -- 把时间截断到最近的5分钟起始点，比如21:37:00转为21:35:00
    DATE_FORMAT(time, '%Y-%m-%d %H:%i:00') - INTERVAL (MINUTE(time) % 5) MINUTE AS interval_start,
    ROW_NUMBER() OVER (
      PARTITION BY DATE_FORMAT(time, '%Y-%m-%d %H:%i:00') - INTERVAL (MINUTE(time) % 5) MINUTE
      ORDER BY CASE ping_response
        WHEN 200 THEN 0
        WHEN 400 THEN 1
        ELSE 2
      END ASC
    ) AS rn
  FROM esn_ping
  WHERE time <= DATE_SUB(NOW(), INTERVAL 1 HOUR)
)
SELECT interval_start, ping_response AS optimal_ping_response
FROM ranked_pings
WHERE rn = 1;

不支持CTE的数据库（如MySQL 5.x）

SELECT interval_start, ping_response AS optimal_ping_response
FROM (
  SELECT
    ping_response,
    DATE_FORMAT(time, '%Y-%m-%d %H:%i:00') - INTERVAL (MINUTE(time) % 5) MINUTE AS interval_start,
    ROW_NUMBER() OVER (
      PARTITION BY DATE_FORMAT(time, '%Y-%m-%d %H:%i:00') - INTERVAL (MINUTE(time) % 5) MINUTE
      ORDER BY CASE ping_response
        WHEN 200 THEN 0
        WHEN 400 THEN 1
        ELSE 2
      END ASC
    ) AS rn
  FROM esn_ping
  WHERE time <= DATE_SUB(NOW(), INTERVAL 1 HOUR)
) AS ranked_pings
WHERE rn = 1;

方案2：分组聚合 #

直接通过聚合函数判断区间内是否存在目标值，按优先级返回结果，不需要排序：

SELECT
  DATE_FORMAT(time, '%Y-%m-%d %H:%i:00') - INTERVAL (MINUTE(time) % 5) MINUTE AS interval_start,
  CASE
    -- 先检查有没有200，有就返回200
    WHEN SUM(CASE WHEN ping_response = 200 THEN 1 ELSE 0 END) > 0 THEN 200
    -- 没有200的话检查有没有400
    WHEN SUM(CASE WHEN ping_response = 400 THEN 1 ELSE 0 END) > 0 THEN 400
    -- 都没有的话返回其他值（这里用MIN，可根据需求调整）
    ELSE MIN(ping_response)
  END AS optimal_ping_response
FROM esn_ping
WHERE time <= DATE_SUB(NOW(), INTERVAL 1 HOUR)
GROUP BY interval_start;

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