TSQL：无法对COUNT（*）执行聚合函数AVG以查找一天中最繁忙的时间 [英] TSQL: Cannot perform an aggregate function AVG on COUNT(*) to find busiest hours of day

问题描述

考虑一个保存日志数据的SQL Server表。重要部分包括：

Consider a SQL Server table that holds log data. The important parts are:

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]

此处的查询围绕查找访问分布按小时。我们希望查看在给定日期范围内小时 的平均访问次数分布。

The query here is around finding the distribution of visits BY HOUR of the day. We're interested in seeing the distribution of the average number of visits for the hour in a given date range.

查询结果类似于

HourOfDay   Avg.Visits.In.Hour
0            24
1            16
5            32
6            89
7           823
etc.etc.

目的是编写一个这样的查询：

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

一个有效的查询，但是：

This is not a valid query, however:

不能对包含聚集查询或子查询的表达式执行聚集函数。

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

问题：如何重新编写此查询以收集平均总数（即代替 AVG （COUNT（*））小时？

Question: how would you re-write this query to gather the average totals (i.e. in place of AVG(COUNT(*)) for the hour?

想象一下，此查询的结果将被传递到 PHB ，他想知道一天中最繁忙的时段是。

Imagine this query's results would be handed to a PHB who wants to know what the busiest hours of the day are.

• SQL Server 2005 +

推荐答案

使用内联视图：

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 +）：

Using CTE (SQL Server 2005+) equivalent:

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)

这篇关于TSQL：无法对COUNT（*）执行聚合函数AVG以查找一天中最繁忙的时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！