sql - 按半小时间隔计算调用次数

Question

我试图获取每半小时间隔的调用次数。想不通。

select 
       count(call_id) as '#Calls',
       1/2 h(date_time) as 'Call_Interval'
from My_Table

Answer 1

一种按不同时间间隔聚合的方法是使用 DATEADD 和 DATEDIFF:

SELECT 
       COUNT(*) as '#Calls',
       DATEADD(minute, (DATEDIFF(minute, '', date_time) / 30) * 30, '') as Call_Interval
FROM dbo.My_Table
GROUP BY DATEADD(minute, (DATEDIFF(minute, '', date_time) / 30) * 30, '')
ORDER BY Call_Interval;