SQL Server：按小时和星期几计算平均数量

Question

背景

我在SQL Server环境中设置了一个表，用于记录我正在跟踪的各种活动的日志。特定的日志项目使用唯一代码来分类正在发生的活动，日期时间字段跟踪活动发生的时间。

问题

我希望使用单个查询或存储过程获得按星期几分组的每小时活动计数的平均值。例如：

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

目前，我已经设置了一个查询，可以按小时和日期输出计数，但我的问题是进一步按星期几获取平均值。这是我的当前查询：

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

有什么建议可以帮助我完成这个任务吗？

先谢谢了！

- mbeasley

---

只需按weekday分组即可。 - Mosty Mostacho

那样会平均计数吗？ - mbeasley

不，如果您想要计算平均数，则必须先运行计数，然后对结果运行平均数操作：select field, avg(countField) from ( select field, count(*) as countField group by field) result group by field - Mosty Mostacho

如果您提供一些源数据（例如，您如何得到5），我们将更好地了解您想要获取总数、平均数或总平均数的确切方式。这个问题有点模糊，没有源数据很难确定5、5、9等数字的来源。 - Aaron Bertrand

Answer 1

我猜:

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; 

再次强调，如果没有数据支持，我可能只是在乱猜而已，但也许你需要的是：

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)。

- Aaron Bertrand

---

@Siva 你是对的。我最初只有前两个，后来在测试时添加了外部 while 循环，但最终并不需要它。 - Aaron Bertrand

@AaronBertrand 简单而优雅。非常好的答案，按预期工作。很抱歉没有提供原始数据，因为有很多数据（我在问题中发布的数字是虚假的 - 因为我还没有实现平均值）。但最终，数据可以是过去几周内任意随机时间/日期的任意一组日志条目...如果这有意义的话。无论如何 - 非常感谢！ - mbeasley

@AaronBertrand 我以为我们解决了这个问题（但是一周之后发现情况并非如此）……其实内部和外部语句在返回结果方面没有任何区别。外部查询已经按星期几分组 - 因此仅返回一个值/每小时/每周的某一天（无论我们是否检查多个星期）。因此，外部查询只需对单个数字进行平均，该数字始终与单个计数相同……这有意义吗？ - mbeasley

@mbeasley 目前无法解决您提供的 T-SQL 词语问题，因此需要您提供一些样本数据和期望结果。但是，如果您能够提供一个数据问题，我可以尝试着解决它。我在4月26日的评论中已经请求了这个数据，如果您能够提供我所需的数据，那么我就有更好的机会来提供您需要的查询。如果问题有变化，也许值得提出新的问题。 - Aaron Bertrand

Answer 2

; WITH a AS (
    SELECT CAST([time] AS date) AS ForDate
       , DATEPART(hour, [time]) AS OnHour
       , txtW=DATENAME(WEEKDAY,[time])
       , intW=DATEPART(WEEKDAY,[time])
       , Totals=COUNT(*)
    FROM [log] WHERE [code] = 'tib_imp.8'
    GROUP BY CAST(time AS date)
    , DATENAME(WEEKDAY,[time])
    , DATEPART(WEEKDAY,[time])
    , DATEPART(hour,[time])
)
SELECT [Day]=txtW
, [Hour]=OnHour
, [Average Count]=AVG(Totals)
FROM a
GROUP BY txtW, intW, OnHour
ORDER BY intW, OnHour

- John Dewey

---

John，尽管你的执行计划与这里的其他两个解决方案相比有一个额外的排序运算符，但输出似乎没有正确排序（日期似乎是随机排序的）。 - Aaron Bertrand

ORDER BY 能否修复结果？ - John Dewey

不，现在它按照星期几的名称字母顺序排序（星期五，星期一，星期四，星期二，星期六，星期日，星期三）。 - Aaron Bertrand

也许将函数调用放入CTE中会有所帮助。 - John Dewey

谢谢，我之前是用iPad编辑的，有时候效果不太好。现在回到电脑上了。 - John Dewey