如何编写从当前分钟起按1小时间隔分组的SQL查询？

需求场景 #

• 数据表包含Created（UTC时间戳）、Code、Percentage列
• 每分钟定时插入数据，不存在日期间隙
• 需要统计近24小时的数据：以查询时的当前UTC分钟（通过GETUTCDATE()获取）为基准，按1小时间隔分组，计算每组的AVG(Percentage)
• 当前实现的是按整小时向下取整分组（如2023-03-01 15:00:00），使用的GROUP BY语句：

GROUP BY 
Code,
DATEADD(hour, DATEDIFF(hour, 0, Created), 0)

• 期望效果：若当前UTC时间为2023-03-01 15:27:48，分组时间点应为2023-03-01 15:27:00、2023-03-01 14:27:00、2023-03-01 13:27:00……按1小时递减，每个分组对应前1小时的数据集。

解决方案 #

核心思路 #

1. 提取当前UTC时间的分钟偏移（如15:27的偏移为27分钟），确定分组基准点@BaseTime（当前时间的整分钟，如15:27:00）
2. 对每条Created记录，计算其所属的分组时间点：以基准点为终点，向前每1小时划分一个区间，区间终点即为分组标识

完整SQL语句 #

DECLARE @CurrentUtcTime DATETIME = GETUTCDATE();
-- 生成分组基准点：当前UTC时间的整分钟（如15:27:00）
DECLARE @BaseTime DATETIME = DATEADD(minute, DATEPART(minute, @CurrentUtcTime), DATEADD(hour, DATEDIFF(hour, 0, @CurrentUtcTime), 0));
-- 近24小时统计的起始时间：基准点往前推24小时
DECLARE @StartDate DATETIME = DATEADD(hour, -24, @BaseTime);

SELECT
    Code,
    -- 计算分组时间点
    DATEADD(hour, CEILING(DATEDIFF(minute, @BaseTime, Created) / 60.0), @BaseTime) AS GroupTime,
    AVG(Percentage) AS AvgPercentage
FROM YourTableName
-- 限定统计近24小时且不超过基准点的数据
WHERE Created >= @StartDate
  AND Created < @BaseTime
GROUP BY
    Code,
    DATEADD(hour, CEILING(DATEDIFF(minute, @BaseTime, Created) / 60.0), @BaseTime)
-- 按分组时间倒序排列，最新的分组在前
ORDER BY GroupTime DESC;

逻辑说明 #

• @BaseTime：锁定当前查询的分钟偏移，确保分组始终以当前分钟为基准，而非整小时
• CEILING(DATEDIFF(minute, @BaseTime, Created) / 60.0)：计算当前记录与基准点的小时差并向上取整，将记录映射到对应的分组终点（如14:30属于15:27分组，14:20属于14:27分组）
• WHERE条件：严格限定统计范围为近24小时，避免包含基准点之后的未生成数据

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