如何编写MySQL语句统计各密钥集时间区间内values表的记录数

DevOps 小满

2026-9-27

解决思路

首先对keys表的时间戳去重，得到所有密钥轮换的时间节点
使用窗口函数LEAD()获取每个密钥时间点对应的下一个轮换时间，作为当前密钥的失效时间点
最新的密钥没有下一个轮换时间，失效时间可以设为默认空值，关联时自动匹配所有大于当前起始时间的记录
将上述结果和values表做关联，统计落在每个密钥生效区间内的values记录数即可

适配MySQL的查询语句

MySQL 8.0及以上版本（支持窗口函数）

WITH key_intervals AS (
    SELECT 
        DISTINCT `timestamp` AS key_start,
        LEAD(`timestamp`) OVER (ORDER BY `timestamp` ASC) AS key_end
    FROM `keys`
)
SELECT 
    ki.key_start AS `Timestamp`,
    COUNT(v.`timestamp`) AS `Count`
FROM key_intervals ki
LEFT JOIN `values` v 
    ON v.`timestamp` >= ki.key_start 
    AND (ki.key_end IS NULL OR v.`timestamp` < ki.key_end)
GROUP BY ki.key_start
ORDER BY ki.key_start ASC;

MySQL 5.x兼容版本（无窗口函数支持）

SELECT 
    k1.`timestamp` AS `Timestamp`,
    COUNT(v.`timestamp`) AS `Count`
FROM (SELECT DISTINCT `timestamp` FROM `keys`) k1
LEFT JOIN (SELECT DISTINCT `timestamp` FROM `keys`) k2 
    ON k2.`timestamp` > k1.`timestamp`
LEFT JOIN `values` v 
    ON v.`timestamp` >= k1.`timestamp` 
    AND (k2.`timestamp` IS NULL OR v.`timestamp` < k2.`timestamp`)
GROUP BY k1.`timestamp`
ORDER BY k1.`timestamp` ASC;