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

Question

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

我有一个名为viewer_log 的表。

每行都是一个页面视图，附有一个 session_id 和时间戳。

我无法将时间戳格式化为一天，按该天分组，然后计算每天的唯一会话数。

到目前为止，对于该查询（也花了很长时间才能工作）是：

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 |
+------------+-----------------+

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

EG：

+------------+-----------------+
| 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 时间戳

Answer 2

谢谢，修改你的建议我最终做的是：

mysql> SELECT day, COUNT(*) AS unique_sessions FROM (SELECT DATE(timestamp) AS day, COUNT(0) as unique_sessions FROM viewer_log GROUP BY day, viewer_session_id) as blah GROUP BY blah.day ORDER BY day DESC;
+------------+-----------------+
| day        | unique_sessions |
+------------+-----------------+
| 2011-03-24 |              14 |
| 2011-03-23 |              23 |
| 2011-03-22 |              30 |
| 2011-03-21 |              35 |
| 2011-03-20 |              11 |
| 2011-03-19 |              18 |
| 2011-03-18 |              15 |
| 2011-03-17 |              34 |
| 2011-03-16 |              37 |
| 2011-03-15 |              21 |
| 2011-03-14 |              20 |
| 2011-03-13 |              21 |
| 2011-03-12 |              21 |
| 2011-03-11 |              21 |
| 2011-03-10 |              84 |
| 2011-03-09 |              26 |
| 2011-03-07 |               1 |
| 2011-03-05 |               1 |
| 2011-03-04 |               2 |
| 2011-03-03 |               9 |
| 2011-03-02 |               9 |
| 0000-00-00 |               1 |
+------------+-----------------+

干杯！