如何筛选表中最新的连续1小时间隔DATETIME行？

这个需求我之前处理过类似的，完全可以用窗口函数来实现，核心思路是从最新的记录往前追溯，直到碰到间隔超过1小时的「断点」，然后保留断点之前的所有连续1小时的记录。下面给你具体的实现方案：

核心思路拆解 #

1. 按时间降序排序：把所有记录按时间从新到旧排列，这样最新的记录在最前面。
2. 计算相邻记录的时间间隔：用窗口函数LAG()获取前一条记录的时间，计算当前记录与它的小时差。
3. 标记断点：如果相邻时间差不等于1小时，标记为一个断点。
4. 分组连续段：通过累计断点标记，给每一段连续的记录分配唯一的组ID，最新的连续段会是第一个组。
5. 筛选目标组：取出组ID为初始值的所有记录，就是我们要的最新连续1小时的行。

具体SQL实现（以MySQL为例） #

假设你的表名为hourly_records，时间列是record_time，代码如下：

WITH ranked_records AS (
    SELECT
        record_time,
        -- 计算当前记录与前一条的小时间隔
        TIMESTAMPDIFF(HOUR, LAG(record_time) OVER (ORDER BY record_time DESC), record_time) AS hour_diff,
        -- 标记是否为断点：间隔不是1小时则标记为1，否则0
        CASE 
            WHEN TIMESTAMPDIFF(HOUR, LAG(record_time) OVER (ORDER BY record_time DESC), record_time) != 1 THEN 1 
            ELSE 0 
        END AS is_break
    FROM hourly_records
),
grouped_records AS (
    SELECT
        record_time,
        -- 累计断点标记，生成连续段的组ID
        SUM(is_break) OVER (ORDER BY record_time DESC) AS group_id
    FROM ranked_records
)
-- 筛选最新的连续段（group_id=0），并按时间升序返回
SELECT record_time
FROM grouped_records
WHERE group_id = 0
ORDER BY record_time ASC;

代码解释 #

• ranked_records CTE：这里用LAG()窗口函数拿到前一条记录的时间，计算和当前记录的小时差。第一条（最新的）记录没有前一条，LAG()返回NULL，所以is_break会被标记为0。
• grouped_records CTE：通过SUM(is_break)的累计求和，每遇到一个断点（is_break=1），组ID就会加1。这样最新的连续段里的所有记录，组ID都是0。
• 最终查询：筛选出group_id=0的记录，就是从最新记录开始，往前连续1小时间隔的所有行。

示例验证 #

用你给出的例子：记录为2018-01-28 10:00:00、2018-01-28 09:00:00、2018-01-28 05:00:00：

1. 按降序排序后顺序是10:00、09:00、05:00。
2. 10:00和09:00的间隔是1小时，is_break=0；09:00和05:00的间隔是4小时，is_break=1。
3. 累计求和后，10:00的group_id=0，09:00的group_id=0，05:00的group_id=1。
4. 最终筛选group_id=0的记录，就是10:00和09:00，完全符合需求。

其他数据库适配 #

如果用PostgreSQL，时间差计算可以换成EXTRACT(HOUR FROM (record_time - LAG(record_time) OVER (ORDER BY record_time DESC)))，整体逻辑完全一致，只需要调整时间差的计算语法即可。

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