考虑一个包含日志数据的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.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)
但这不是有效的查询：
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
问题：如何重新编写此查询以收集平均总数(即代替小时的AVG(COUNT(*))？
想象一下,这个查询的结果将被交给一个想知道一天中最忙碌时间是什么的PHB.
> 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)等效：
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,AVG(x.num)
FROM visits x
GROUP BY DATEPART(hh,x.visitdate)
