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

我使用MSSQL查询来计算过去30天的总唯一IP地址和总IP记录。

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

现在我很难计算每日平均点击量和每日平均访问者数量。 我试图使用AVG但它给了我一个错误消息，说“操作数数据类型varchar对于avg运算符无效”。
任何帮助是极大的赞赏。

---

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/31825.html

上一篇: MS SQL query to calculate average monthly unique visitors

下一篇: SQL: Finding the average number of days between runs for each runners