如何在MySQL中单查询获取按时间均匀分布的n条记录

实现思路 #

核心逻辑是先计算全表数据的时间（或自增ID）跨度，将跨度平均切分为n-1个等距区间，每个区间取1条最接近区间节点的记录，最终得到n条均匀分布的结果。 #

方案1：MySQL 8.0+ 基于时间维度实现（精准匹配时间分布需求） #

假设表名为metric_records，字段为id(自增主键)、datetime(时间字段)、val(数值字段)：

-- 定义需要返回的记录数n
SET @n = 3;

WITH global_stats AS (
    SELECT
        MIN(`datetime`) AS min_time,
        MAX(`datetime`) AS max_time,
        TIMESTAMPDIFF(SECOND, MIN(`datetime`), MAX(`datetime`)) AS total_seconds
    FROM metric_records
),
record_interval AS (
    SELECT
        `datetime`,
        `val`,
        -- 计算当前记录所属的区间编号
        FLOOR(
            TIMESTAMPDIFF(SECOND, (SELECT min_time FROM global_stats), `datetime`)
            / (SELECT total_seconds FROM global_stats) * (@n - 1)
        ) AS interval_no
    FROM metric_records
    WHERE (SELECT total_seconds FROM global_stats) > 0
)
-- 每个区间取1条记录
SELECT `datetime`, `val` FROM record_interval GROUP BY interval_no
UNION ALL
-- 所有记录时间相同时直接返回n条
SELECT `datetime`, `val` FROM metric_records WHERE (SELECT total_seconds FROM global_stats) = 0 LIMIT @n
ORDER BY `datetime`;

方案2：MySQL 8.0+ 基于自增主键实现（性能更优） #

如果自增ID和时间维度完全正相关（无时间回写场景），可直接用ID计算，性能更高：

SET @n = 3;

WITH global_stats AS (
    SELECT MIN(id) AS min_id, MAX(id) AS max_id, COUNT(*) AS total_cnt FROM metric_records
)
SELECT `datetime`, `val`
FROM metric_records
WHERE id IN (
    -- 生成n个等距的ID节点
    SELECT ROUND((max_id - min_id) * k / (@n - 1) + min_id)
    FROM (
        -- 这里的数字序列长度要大于等于你可能用到的最大n值
        SELECT 0 AS k UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
    ) AS num_seq
    WHERE k < @n
) OR @n >= (SELECT total_cnt FROM global_stats)
ORDER BY `datetime`
LIMIT @n;

方案3：MySQL 5.x 兼容版本 #

不支持CTE和窗口函数的低版本MySQL可用用户变量实现：

SET @n = 3;
SELECT MIN(`datetime`) INTO @min_time FROM metric_records;
SELECT MAX(`datetime`) INTO @max_time FROM metric_records;
SET @total_seconds = TIMESTAMPDIFF(SECOND, @min_time, @max_time);
SET @step = IF(@total_seconds > 0, @total_seconds / (@n - 1), 1);

SELECT `datetime`, `val`
FROM (
    SELECT
        `datetime`,
        `val`,
        @cur_interval := FLOOR(TIMESTAMPDIFF(SECOND, @min_time, `datetime`) / @step) AS interval_no
    FROM metric_records
    ORDER BY `datetime`
) AS t
WHERE @total_seconds > 0
GROUP BY interval_no
UNION ALL
SELECT `datetime`, `val` FROM metric_records WHERE @total_seconds = 0 LIMIT @n
ORDER BY `datetime`;

注意事项 #

• 若表中总记录数小于n，所有方案都会直接返回全表数据
• 时间维度方案不受数据删除、ID不连续的影响，适配所有场景
• ID维度方案性能更高，但要求ID和时间严格正相关，不存在历史数据插入、时间回改的情况
• 若某区间无对应数据，会自动合并相邻区间，最终返回结果最多为n条

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