PostgreSQL SQL实现带防抖逻辑的最早记录查询

这个问题挺有意思的，本质是要追踪一条连续的时间链——从最早的记录开始，只要后续有记录和当前选中的时间差在1小时内，就切换到这条后续记录，直到找不到符合条件的为止，最终取这条链的最后一环。我推荐用递归CTE来实现，因为它天生适合这种迭代式的逻辑：

解决方案代码（以PostgreSQL为例） #

WITH RECURSIVE record_chain AS (
    -- 锚点：先获取最早创建的那条记录
    SELECT id, "Value", "Created"
    FROM your_table
    WHERE "Created" = (SELECT MIN("Created") FROM your_table)
    
    UNION ALL
    
    -- 递归步骤：从上一步的记录出发，找下一个符合时间间隔的最新记录
    SELECT t.id, t."Value", t."Created"
    FROM record_chain rc
    JOIN your_table t 
      ON t."Created" > rc."Created" 
      AND t."Created" <= rc."Created" + INTERVAL '1 hour'
    -- 确保每次只取符合条件的最新那条，避免多条时重复迭代
    WHERE t."Created" = (
        SELECT MAX("Created") 
        FROM your_table 
        WHERE "Created" > rc."Created" 
          AND "Created" <= rc."Created" + INTERVAL '1 hour'
    )
)
-- 取递归链的最后一条记录
SELECT id, "Value", "Created"
FROM record_chain
ORDER BY "Created" DESC
LIMIT 1;

逻辑解释 #

咱们一步步看这个查询怎么工作：

1. 锚点阶段：先找到表中创建时间最早的记录（也就是示例里的Apple），作为链的起点。
2. 第一次递归：从Apple的时间出发，找它之后1小时内的最新记录——Blueberry符合条件，被加入链中。
3. 第二次递归：接着从Blueberry的时间出发，找它之后1小时内的最新记录——Cranberry符合条件，加入链中。
4. 递归终止：再尝试从Cranberry的时间找后续1小时内的记录，Durian的时间差远超过1小时，没有符合条件的结果，递归停止。
5. 最终结果：把整个链按创建时间倒序排列，取第一条就是我们要的Cranberry。

适配其他数据库 #

如果用的不是PostgreSQL，只需要小调整时间计算部分：

• MySQL：把INTERVAL '1 hour'改成INTERVAL 1 HOUR
• SQL Server：用DATEADD(HOUR, 1, rc."Created")替换rc."Created" + INTERVAL '1 hour'

用你给出的示例数据测试这个查询，会精准返回id=3、Value为'Cranberry'的记录，完全符合需求。

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