如何计算每天的平均用户访问量

I have a requests table which include request data for my my page requests. I have field including Id, UserAgent, Ip, EventDateUtc, etc. How to write SQL which give me average requests per day.

我有一个请求表,其中包含我的页面请求的请求数据。我有字段,包括Id,UserAgent,Ip,EventDateUtc等。如何编写SQL,它给我每天的平均请求。

2 个解决方案

#1

You can get a count grouped by UserAgent,EventDateUtc and then do a AVG on top of it.

您可以获取按UserAgent,EventDateUtc分组的计数,然后在其上面执行AVG。

SELECT AVG(reccount) avg_count,EventDateUtc
FROM
(
SELECT COUNT(*) reccount,UserAgent, CONVERT(DATE,EventDateUtc) EventDateUtc
FROM PageVisit
GROUP BY CONVERT(DATE,EventDateUtc),UserAgent
)T
GROUP BY EventDateUtc

If you want a total average for all days, don't do a GROUP BY in the outer query.

如果您想要所有日期的总平均值,请不要在外部查询中执行GROUP BY。

SELECT AVG(reccount) avg_count
FROM
(
SELECT COUNT(*) reccount,UserAgent, CONVERT(DATE,EventDateUtc) EventDateUtc
FROM PageVisit
GROUP BY CONVERT(DATE,EventDateUtc),UserAgent
)T

#2

The inner select counts visits / day, the outer the average:

内部选择计算访问次数/天,外部平均值:

select avg(cnt)
from (select count(*) as cnt
      from tablename
      group by EventDateUtc)

#1