sql - TSQL:无法对 COUNT(*) 执行聚合函数 AVG 来查找一天中最繁忙的时间

Question

考虑一个保存日志数据的 SQL Server 表。重要的部分是:

CREATE TABLE [dbo].[CustomerLog](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [CustID] [int] NOT NULL,
    [VisitDate] [datetime] NOT NULL,
 CONSTRAINT [PK_CustomerLog] PRIMARY KEY CLUSTERED ([ID] ASC)) ON [PRIMARY]

这里的查询是关于查找当天按小时的访问分布情况。我们感兴趣的是查看给定日期范围内小时的平均访问次数的分布。

查询结果会是这样的:

HourOfDay   Avg.Visits.In.Hour0            241            165            326            897           823etc.etc.

The intention is to write a query like this:

SELECT  DATEPART(hh, VisitDate)
        ,AVG(COUNT(*))    
FROM    CustomerLog
WHERE   VisitDate   BETWEEN 'Jan 1 2009' AND 'Aug 1 2009'
GROUP BY   DATEPART(hh, VisitDate)

但是，这不是有效的查询:

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

问题:您将如何重写此查询来收集平均总数(即代替该小时的 AVG(COUNT(*))？

想象一下这个查询的结果将被传递给 PHB谁想知道一天中最繁忙的时间。

• SQL Server 2005+

Answer 1

使用内联 View :

SELECT DATEPART(hh, x.visitdate),
       AVG(x.num)
  FROM (SELECT t.visitdate,
               COUNT(*) 'num'
          FROM CUSTOMERLOG t
         WHERE t.visitdate BETWEEN 'Jan 1 2009' AND 'Aug 1 2009'
      GROUP BY t.visitdate) x
GROUP BY DATEPART(hh, x.visitdate)

使用 CTE(SQL Server 2005+)等效项:

WITH visits AS (
   SELECT t.visitdate,
          COUNT(*) 'num'
     FROM CUSTOMERLOG t
    WHERE t.visitdate BETWEEN 'Jan 1 2009' AND 'Aug 1 2009'
 GROUP BY t.visitdate)
   SELECT DATEPART(hh, x.visitdate),
         AVG(x.num)
    FROM visits x
GROUP BY DATEPART(hh, x.visitdate)