sql - 按星期几按小时计算的平均计数

Question

这question帮助我完成了部分工作:

SELECT 
    [Day], 
    [Hour], 
    [DayN], 
    AVG(Totals) AS [Avg]
FROM
(
    SELECT 
        w = DATEDIFF(WEEK, 0, ForDateTime),
        [Day]  = DATENAME(WEEKDAY, ForDateTime),
        [DayN] = DATEPART(WEEKDAY, ForDateTime),
        [Hour] = DATEPART(HOUR,    ForDateTime),
        Totals = COUNT(*)
    FROM 
        #Visit
    GROUP BY 
        DATEDIFF(WEEK, 0, ForDateTime),
        DATENAME(WEEKDAY, ForDateTime),
        DATEPART(WEEKDAY, ForDateTime),
        DATEPART(HOUR,    ForDateTime)
) AS q
GROUP BY 
    [Day], 
    [Hour], 
    [DayN]
ORDER BY 
    DayN; 

如何改变这而不是按小时显示平均值，例如9、10、11、12等，从09:30-10:30,10:30-11:30,11:30-12:30,12:30-13:30一直显示到23:30。

Answer 1

一个简单的方法是将 ForDateTime 偏移 30 分钟。基本上，您只需要在查询中用 dateadd(minute, 30, ForDateTime) 替换 ForDateTime 的 every 出现。

在结果集中，Hour 9 为您提供从 8:30 到 9:30 的时间段，依此类推。