使用 DATE_FORMAT 别名的 MySQL 子查询计数

Question

在过去的几个小时里，我一直在用头撞墙，试图让这个单一的查询起作用，但没有成功。

我有一个名为 viewer_log 的表。

每一行都是一个带有 session_id 和时间戳的页面浏览。

我无法将时间戳格式化为一天，按那一天分组，然后计算每天的 UNIQUE session 数。

到目前为止，该查询(也需要很长时间才能开始工作)是:

SELECT day, COUNT(*) as unique_sessions from (SELECT COUNT(viewer_session_id) AS sessions, DATE_FORMAT(timestamp, "%Y-%m-%d") AS day FROM viewer_log GROUP BY `viewer_session_id` HAVING day='2011-03-23') AS sessions;

你会注意到这一天是硬编码的，给

mysql> SELECT day, COUNT(*) as unique_sessions from (SELECT COUNT(viewer_session_id) AS sessions, DATE_FORMAT(timestamp, "%Y-%m-%d") AS day FROM viewer_log GROUP BY `viewer_session_id` HAVING day='2011-03-23') AS sessions;
+------------+-----------------+
| day        | unique_sessions |
+------------+-----------------+
| 2011-03-23 |              21 |
+------------+-----------------+

我想扩展这些结果而不必指定日期，这是我遇到问题并且无法在互联网上找到答案的地方......我确定它在那里，但不确定我是什么'正在寻找。

如:

+------------+-----------------+
| day        | unique_sessions |
+------------+-----------------+
| 2011-03-21 |              14 |
| 2011-03-22 |              11 |
| 2011-03-23 |              21 |
+------------+-----------------+

越过这一点我遇到了很多麻烦，我尝试用它自己的副本加入表，但通常会遇到未知的列名，或者我认为应该显示的结果是错误的:

mysql> select DATE_FORMAT(v.timestamp, "%Y-%m-%d") AS date, count(sessions) as rows from (SELECT COUNT(viewer_session_id) AS sessions, DATE_FORMAT(timestamp, "%Y-%m-%d") AS date FROM viewer_log GROUP BY viewer_session_id) AS s JOIN viewer_log v ON (s.date=date) GROUP BY date;

+------------+--------+
| date       | rows   |
+------------+--------+
| 0000-00-00 |   2551 |
| 0000-00-00 |  20408 |
| 0000-00-00 |  20408 |
| 0000-00-00 |   5102 |
| 0000-00-00 |   2551 |
| 0000-00-00 |   2551 |
| 0000-00-00 |  63775 |
| 0000-00-00 | 211733 |
| 0000-00-00 |  53571 |
| 0000-00-00 |  53571 |
| 0000-00-00 |  53571 |
| 0000-00-00 |  51020 |
| 0000-00-00 |  48469 |
| 0000-00-00 |  91836 |
| 0000-00-00 |  86734 |
| 0000-00-00 |  33163 |
| 0000-00-00 |  45918 |
| 0000-00-00 |  28061 |
| 0000-00-00 |  86734 |
| 0000-00-00 |  76530 |
| 0000-00-00 |  53571 |
| 0000-00-00 |  33163 |
+------------+--------+
22 rows in set, 1 warning (0.63 sec)

Answer 1

尝试

  SELECT DATE(timestamp) AS day,
         COUNT(0) as unique_sessions
    FROM viewer_log
GROUP BY DATE(timestamp), viewer_session_id

您可以添加 WHERE 条件，例如

  SELECT DATE(timestamp) AS day,
         COUNT(0) as unique_sessions
    FROM viewer_log
   WHERE DATE(timestamp) >= '2011-03-01'
     AND DATE(timestamp) <= '2011-03-21'
GROUP BY DATE(timestamp), viewer_session_id

这是假设时间戳是 mysql 时间戳而不是 unix 时间戳