查询最近24小时数据：保留col1最新记录并标记new/update

针对你提出的需求，我们可以利用SQL的窗口函数来高效实现，这种方式简洁且性能优异，尤其适合处理分组内的排序和标记逻辑。下面以PostgreSQL为例（你的时间戳格式带时区，符合PG的特征），给出具体解决方案：

完整SQL代码 #

WITH ranked_data AS (
    SELECT 
        col1, 
        col2, 
        col3, 
        t_insert,
        -- 按col1分组，给组内记录按时间戳倒序排名，最新的记录排第1
        ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY t_insert DESC) AS rn,
        -- 统计每个col1对应的总记录数，用于判断是否存在重复
        COUNT(*) OVER (PARTITION BY col1) AS cnt
    FROM your_table_name
    -- 筛选出最近24小时内插入的记录
    WHERE t_insert >= NOW() - INTERVAL '24 hours'
)
SELECT 
    col1, 
    col2, 
    col3, 
    t_insert,
    -- 根据分组内的记录数标记类型：重复则为update，否则为new
    CASE WHEN cnt > 1 THEN 'update' ELSE 'new' END AS col4
FROM ranked_data
-- 只保留每个col1对应的最新记录
WHERE rn = 1
ORDER BY t_insert;

代码逻辑解释 #

1. 时间筛选：通过WHERE t_insert >= NOW() - INTERVAL '24 hours'直接过滤出最近24小时的记录，NOW()会取当前数据库服务器的时间，如果你需要用特定时间作为基准，可以替换成具体的时间值。
2. 分组排序与统计：
   • ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY t_insert DESC)：按col1分组，组内按t_insert倒序排列，给每条记录分配一个排名，最新的记录排名为1。
   • COUNT(*) OVER (PARTITION BY col1)：统计每个col1分组下的总记录数，用来判断该col1是否存在重复值。
3. 筛选与标记：主查询中只保留排名为1的记录（即每个col1的最新记录），再通过CASE语句根据分组总记录数判断标记：如果总记录数大于1，说明该col1有重复，标记为update；否则标记为new。

适配其他数据库说明 #

如果使用MySQL 8.0及以上版本，语法几乎一致，仅需把时间筛选条件改为WHERE t_insert >= NOW() - INTERVAL 24 HOUR；如果是MySQL 5.x版本（不支持窗口函数），可以用子查询关联的方式实现，但窗口函数的写法更简洁易维护。

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