SQL Server：按小时和星期几的平均计数 [英] SQL Server: Average counts by hour and day of week

问题描述

我在SQL Server环境中建立了一个表，其中包含我正在跟踪的各种活动的日志。特定的日志项目使用唯一的代码对正在发生的活动进行分类，并使用日期时间字段跟踪该活动的发生时间。

I have a table set up in a SQL Server environment that contains a log of various activity that I'm tracking. Particular log items use unique codes to categorize what activity is taking place and a datetime field tracks when that activity occurred.

我想，使用单个查询或存储过程，即可获取每小时平均活动计数，并按一周中的星期几进行分组。示例：

I would like to, using either a single query or a stored procedure, get an average of hourly counts of activity, grouped by day of the week. Example:

Day      | Hour | Average Count
-------------------------------
Monday   | 8    | 5
Monday   | 9    | 5
Monday   | 10   | 9
...
Tuesday  | 8    | 4
Tuesday  | 9    | 3
...etc

现在，我有一个查询设置，可以显示每小时的计数每天，但我的问题是要更进一步，并且要在一周的某天达到平均水平。这是我当前的查询：

Right now I've got a query setup that spits out the counts per hour per day, but my problem is taking it a step further and getting average by day of week. Here's my current query:

SELECT CAST([time] AS date) AS ForDate,
   DATEPART(hour, [time]) AS OnHour,
   COUNT(*) AS Totals
FROM [log] WHERE [code] = 'tib_imp.8'
GROUP BY CAST(time AS date),
   DATEPART(hour,[time])
   ORDER BY ForDate Asc, OnHour Asc

关于如何完成此操作的任何建议？

Any suggestions as to how I might accomplish this?

预先感谢！

推荐答案

在这里猜测：

SELECT [Day], [Hour], [DayN], AVG(Totals) AS [Avg]
FROM
  (
        SELECT 
          [Day]  = DATENAME(WEEKDAY, [time]),
          [DayN] = DATEPART(WEEKDAY, [time]),
          [Hour] = DATEPART(HOUR,    [time]),
          Totals = COUNT(*)
        FROM dbo.[log] 
            WHERE [code] = 'tib_imp.8'
        GROUP BY 
          DATENAME(WEEKDAY, [time]),
          DATEPART(WEEKDAY, [time]),
          DATEPART(HOUR,    [time])
  ) AS q
GROUP BY [Day], [Hour], [DayN]
ORDER BY DayN; 

再次，如果没有数据，我可能会再次在墙上扔一些泥土，希望它棒，但也许您需要的是：

Again, without data, I might once again be throwing a handful of mud at the wall and hoping it sticks, but perhaps what you need is:

SELECT [Day], [Hour], [DayN], AVG(Totals) AS [Avg]
FROM
(
    SELECT 
  w = DATEDIFF(WEEK, 0, [time]),
      [Day]  = DATENAME(WEEKDAY, [time]),
      [DayN] = DATEPART(WEEKDAY, [time]),
      [Hour] = DATEPART(HOUR,    [time]),
      Totals = COUNT(*)
    FROM dbo.[log] 
      WHERE [code] = 'tib_imp.8'
    GROUP BY 
  DATEDIFF(WEEK, 0, [time]),
      DATENAME(WEEKDAY, [time]),
      DATEPART(WEEKDAY, [time]),
      DATEPART(HOUR,    [time])
  ) AS q
GROUP BY [Day], [Hour], [DayN]
ORDER BY DayN; 

这也会产生基于整数的平均值，因此您可能希望将Totals别名转换为内部查询到DECIMAL（something，something）。

This is also going to produce integer-based averages, so you may want to cast the Totals alias on the inner query to DECIMAL(something,something).

这篇关于SQL Server：按小时和星期几的平均计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！