SQL Server如何获取每日指定小时最新时间戳对应记录值

MS SQL Server 数据库中存在一张业务表，包含两个字段：

• Value：存储业务值
• insert_datetime：时间戳类型，存储记录插入时间
  需求为获取每日指定2个小时时段（示例为早7点、晚19点）内，最新时间戳对应的记录值。

示例源数据 #

预期输出 #

原尝试编写的SQL如下：

select * from tbl
where DATEPART(hour,[insert_datetime]) in ('07','19')
and DATEPART(minute,[insert_datetime]) in (select max(DATEPART(minute,[insert_datetime])) 
                                           from tbl 
                                           group by [insert_datetime]
                                           ) 

执行时提示关键字'group'附近有语法错误，本质是逻辑设计存在3个核心问题：

1. 分组维度错误：子查询按完整insert_datetime时间戳分组，每个独立时间值会单独成组，计算出的最大分钟值无业务意义
2. 匹配维度缺失：仅用分钟值做匹配条件，没有关联日期、小时维度，会出现跨天、跨小时的同分钟值记录被错误命中的问题
3. 精度缺失：仅比对分钟字段，忽略秒、毫秒部分的时间差，无法准确定位到真正最新的记录

方案1：窗口函数实现（推荐，SQL Server 2008及以上版本支持） #

用ROW_NUMBER()窗口函数，按「日期+小时」分区，每个分区内按时间倒序取第一条，就是对应时段的最新记录：

WITH TempRanked AS (
    SELECT
        Value,
        insert_datetime,
        ROW_NUMBER() OVER(
            PARTITION BY CAST(insert_datetime AS DATE), DATEPART(HOUR, insert_datetime)
            ORDER BY insert_datetime DESC
        ) AS row_rank
    FROM tbl
    WHERE DATEPART(HOUR, insert_datetime) IN (7, 19)
)
SELECT Value, insert_datetime
FROM TempRanked
WHERE row_rank = 1
ORDER BY insert_datetime

方案2：NOT EXISTS 关联实现（兼容所有SQL Server版本） #

通过自关联判断同日期、同时段下不存在更晚的记录，筛选出每个时段的最新值：

SELECT t1.Value, t1.insert_datetime
FROM tbl t1
WHERE DATEPART(HOUR, t1.insert_datetime) IN (7, 19)
AND NOT EXISTS (
    SELECT 1
    FROM tbl t2
    WHERE
        CAST(t2.insert_datetime AS DATE) = CAST(t1.insert_datetime AS DATE)
        AND DATEPART(HOUR, t2.insert_datetime) = DATEPART(HOUR, t1.insert_datetime)
        AND t2.insert_datetime > t1.insert_datetime
)
ORDER BY t1.insert_datetime

两个方案执行后都能得到预期的输出结果。

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