我有一个包含访问控制日志条目的数据库:
time : datetime (this is the access timestamp)
src: text (this is the userid)
我想从中获取一个列表,显示过去 7 天内有多少天当天的用户已经访问过。结果应如下所示:
number of days with access | count
1 | 30
2 | 54
3 | 123
4 | 843
5 | 3490
6 | 71
7 | 23
到目前为止,我拥有的:
下面的查询返回日志条目在 2015-03-08 上也有条目在 2015-03-07 的用户数。
SELECT Count(DISTINCT a.src)
FROM contacts AS a
LEFT JOIN contacts AS b
ON a.src = b.src
WHERE a.time BETWEEN Cast('2015-03-08 05:00:00' AS DATETIME) AND Cast('2015-03-09 05:00:00' AS DATETIME)
AND b.time BETWEEN Cast('2015-03-07 05:00:00' AS DATETIME) AND Cast('2015-03-08 05:00:00' AS DATETIME)
但是我坚持如上所述每天按天数获取计数。如果没有"仅sql"解决方案,那么使用php的(高性能)方法也可以。感谢您的任何帮助..
我看不出您需要加入 b 表的任何理由。
SELECT
DAY(a.time),
COUNT(DISTINCT a.src)
FROM contacts AS a
WHERE a.time
BETWEEN (TIMESTAMP(CURDATE()) - INTERVAL 1 WEEK)
AND TIMESTAMP(CONCAT(CURDATE(),' 23:59:59'))
GROUP BY DAY(a.time)