SQL按小时分组统计后如何实现Hour列的正确排序

数据库表结构 #

dbo.Zaehler表结构及示例数据如下：

ID  gesamtzaehler   gutzaehler  timestamp
1   7119529 7007905 2023-03-01 14:09:15.043
2   7119553 7007929 2023-03-01 14:10:15.013
3   7119577 7007953 2023-03-01 14:11:15.043
4   7119601 7007977 2023-03-01 14:12:15.030
5   7119625 7008001 2023-03-01 14:13:15.047
6   7119649 7008025 2023-03-01 14:14:15.010
7   7119673 7008049 2023-03-01 14:15:15.010
8   7119697 7008073 2023-03-01 14:16:15.027
9   7119721 7008097 2023-03-01 14:17:15.027
10  7119731 7008107 2023-03-01 14:18:15.043
11  7119731 7008107 2023-03-01 14:19:15.057
12  7119731 7008107 2023-03-01 14:20:15.040
13  7119731 7008107 2023-03-01 14:21:15.010

当前统计SQL语句 #

用于统计每小时内gesamtzaehler与gutzaehler差值的最大波动值（最大值减最小值）的SQL：

SELECT        MAX(gesamtzaehler - gutzaehler) - MIN(gesamtzaehler - gutzaehler) AS Ausschuss, CAST(DATEPART(Hour, timestamp) AS varchar) + ':00' AS Hour
FROM            dbo.Zaehler
WHERE        (timestamp >= DATEADD(hour, - 24, GETDATE()))
GROUP BY DATEPART(hh, timestamp)

当前执行结果 #

执行上述SQL后得到的结果（未按时间顺序排序）：

Ausschuss Hour
12  0:00
13  23:00
6   15:00
14  3:00
3   6:00
11  21:00
63  7:00
4   1:00
14  18:00
19  4:00
25  19:00
7   5:00
0   22:00
5   16:00
15  2:00
29  17:00
4   20:00
2   14:00

问题 #

由于Hour列是字符串格式，无法按0-24的时间顺序正确排序，需调整SQL实现需求。

解决方案 #

提供两种可行方案：

方案1：按分组的数值型小时排序 #

直接在SQL末尾添加ORDER BY DATEPART(hh, timestamp)，利用分组时的数值型小时字段排序，无需修改Hour列的格式：

SELECT        MAX(gesamtzaehler - gutzaehler) - MIN(gesamtzaehler - gutzaehler) AS Ausschuss, CAST(DATEPART(Hour, timestamp) AS varchar) + ':00' AS Hour
FROM            dbo.Zaehler
WHERE        (timestamp >= DATEADD(hour, - 24, GETDATE()))
GROUP BY DATEPART(hh, timestamp)
ORDER BY DATEPART(hh, timestamp)

方案2：格式化Hour列为带前导零的字符串，同时按数值排序 #

如果需要Hour列的字符串格式更规范（如00:00、01:00），可以用RIGHT函数补前导零，此时字符串本身也能按时间顺序排序，同时搭配数值排序确保正确性：

SELECT        MAX(gesamtzaehler - gutzaehler) - MIN(gesamtzaehler - gutzaehler) AS Ausschuss, RIGHT('0' + CAST(DATEPART(Hour, timestamp) AS varchar), 2) + ':00' AS Hour
FROM            dbo.Zaehler
WHERE        (timestamp >= DATEADD(hour, - 24, GETDATE()))
GROUP BY DATEPART(hh, timestamp)
ORDER BY DATEPART(hh, timestamp)

这样Hour列会显示为00:00、01:00...23:00，既满足可视化的格式需求，也能正确排序。

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