MS SQL query to calculate average monthly unique visitors

I made MSSQL query that calculates total unique IP address and total IP records for past 30 days.

SELECT count(IPAddress) AS totalPageHits, count(DISTINCT IPAddress) AS totalVisitors, count(DateTime)
FROM recordTable
WHERE DateTime > GETDATE()-30

Now I am having a hard time to calculate average daily page hits and average daily unique visitors. I have tried to use AVG but it gives me an error message saying "Operand data type varchar is invalid for avg operator."
Any help is greatly appreciated.

---

SELECT  AVG(HitCount) as AverageHits,
    AVG(UniqueHitCount) As AverageUniqueHits
FROM
(
SELECT  COUNT(IPAddress) as HitCount,
        COUNT(DISTINCT IPAddress) as UniqueHitCount,
        DATEDIFF(d, GETDATE(), DateTime) as Day
FROM tblHitCounter
WHERE DateTime > GETDATE() - 30
GROUP BY DATEDIFF(d, GETDATE(), DateTime)
) sub

---

您需要按天进行分组，然后再进行平均：

select avg(totalPageHits) as avgDailyTotalPageHits,
       avg(totalVisitors) as avgDailyTotalVisitors
from (SELECT cast(DateTime as date) as thedate,
             count(IPAddress) AS totalPageHits,
             count(DISTINCT IPAddress) AS totalVisitors, count(DateTime) as cnt
      FROM recordTable
      WHERE DateTime > GETDATE()-30
      group by cast(DateTime as date)
     ) t

链接地址: http://www.djcxy.com/p/31826.html

上一篇: 粒子效果的混合

下一篇: MS SQL查询来计算平均每月唯一访问者数