mysql - 使用访问者数据表，我如何获得总访问者和独立访问者的每小时总数？

Question

我有一个 Visits 表，结构如下:

+--------------------------------------+
| ID | Date       | Time     | Session |
+--------------------------------------+
| 1  | 05-18-2014 | 20:15:10 | 1       |
| 2  | 05-18-2014 | 20:15:20 | 1       |
| 3  | 05-18-2014 | 21:40:20 | 2       |
| 4  | 05-18-2014 | 21:45:30 | 1       |
| 5  | 05-18-2014 | 21:50:50 | 3       |
+--------------------------------------+

session 列是用户的 session ID。我想查询该表以获取每小时的总访客数和唯一身份访客数，以获得如下结果:

+-----------------------+
| Time | Total | Unique |
+-----------------------+
| 20   | 2     | 1      |
| 21   | 3     | 2      |
+-----------------------+

唯一身份访问者是在“访问”表中的任何位置具有以前从未出现过的 session 的访问者。

以下仅选择每小时内的唯一身份访问者:

SELECT COUNT(*) Total, COUNT(DISTINCT Session) Unique, HOUR(Time) Time
WHERE Date = '05-18-2014'
FROM Visits
GROUP BY HOUR(Time)

以下似乎可行，但需要两个查询和一个子查询:

SELECT COUNT(*) Total, HOUR(Time) Time
FROM Visits
GROUP BY HOUR(Time);

SELECT COUNT(*) Unique, HOUR(Time) Time
FROM (
    SELECT *
    FROM Visits
    GROUP BY Session
    ORDER BY Date, Time DESC
) UniqueVisits
WHERE Date = '05-18-2014'
GROUP BY HOUR(Time);

有没有更简单的方法来获得这两个总数？

Answer 1

我认为“不同”是指您只希望将一个 session 计算一次(在第一个小时内)。如果是这样，您可以这样做:

select max(h.total) as total, count(firstvisit.session) as Firsts, h.hr
from (select hour(time) as hr, count(*) as total
      from visits v
      where Date = '05-18-2014'
      group by hour(time)
     ) h left outer join
     (select session, min(hour(time))as hr
      from visits v
      where Date = '05-18-2014'
      group by session
     ) firstvisit
     on h.hr = firstvisit.hr
GROUP BY h.hr;