SQL Server按5分钟间隔提取每组首个DateTime、Value和ID的求助

你的现有SQL存在两个核心问题，导致无法得到期望结果：

• 分组逻辑错误：FORMAT([DateTime], 'dd-MM.yyyy HH:mm')是按每分钟分组，而非需求的5分钟间隔，会把8:55、8:56、8:58分到不同组，不符合要求。
• 字段别名冲突：将格式化后的字段命名为DateTime，和原表字段重名，导致窗口函数的PARTITION BY逻辑出错，且丢失原始时间的毫秒精度，无法准确筛选每组最早的记录。

正确解法 #

要实现按5分钟间隔分组并提取每组首个记录，关键是先将所有时间截断到最近的5分钟时间边界，再按ID和该边界值分组，最后用窗口函数筛选每组最早的记录。

完整SQL语句：

WITH grouped_data AS (
    SELECT
        [ID],
        [DateTime],
        [Value],
        -- 将时间截断到5分钟间隔的起始点
        DATEADD(MINUTE, DATEDIFF(MINUTE, 0, [DateTime]) / 5 * 5, 0) AS FiveMinuteWindow,
        -- 按ID和5分钟窗口分组，给每组内的记录按时间升序排序
        ROW_NUMBER() OVER(PARTITION BY [ID], DATEADD(MINUTE, DATEDIFF(MINUTE, 0, [DateTime]) / 5 * 5, 0) 
                          ORDER BY [DateTime] ASC) AS row_num
    FROM 
        [dbo].[table]
)
SELECT
    [ID],
    -- 格式化输出时间以匹配期望格式（去掉毫秒）
    FORMAT([DateTime], 'yyyy-MM-dd HH:mm:ss') AS DateTime,
    [Value]
FROM 
    grouped_data
WHERE 
    row_num = 1
ORDER BY
    [DateTime];

关键逻辑说明 #

• 时间截断表达式：DATEADD(MINUTE, DATEDIFF(MINUTE, 0, [DateTime]) / 5 * 5, 0)
  这个表达式会把任意时间归到最近的5分钟起始点，例如：
  • 2022-05-03 08:50:00.037 → 2022-05-03 08:50:00
  • 2022-05-03 08:58:08.031 → 2022-05-03 08:55:00
  • 2022-05-03 09:00:00.042 → 2022-05-03 09:00:00
• 窗口函数分组：按ID和截断后的5分钟窗口分组，每组内按原始时间升序排序，row_num=1即为每组的首个记录。
• 格式输出：用FORMAT函数将时间转为期望的无毫秒格式，若保留原始时间精度可直接使用[DateTime]字段。

执行结果 #

运行上述SQL后，会得到与你期望一致的输出：

ID   DateTime              Value
--------------------------------
ID1  2022-05-03 08:50:00    100
ID1  2022-05-03 08:55:05    100
ID1  2022-05-03 09:00:00    100

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