MySQL：每天计算不同的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14191021/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL: Count the distinct rows per day
提问by Sam
I have an interesting query I need to do. I have a table with an INTcolumn containing ip address numbers (using INET_ATON), and a timestampcolumn. I want to be able to count the number of unique ip address columns there are per day. That is, how many distinct ip rows there are in each day. So, for example, if an ip address is in the same day twice, it counts as 1 in the final count, however if the same ip address is in another day it'll be counted there will be a second count for it.
我有一个有趣的查询需要做。我有一个表，其中有一INT列包含 ip 地址编号（使用INET_ATON）和一timestamp列。我希望能够计算每天的唯一 IP 地址列的数量。也就是说，每天有多少个不同的 ip 行。因此，例如，如果一个 IP 地址在同一天两次，则在最终计数中计为 1，但是如果同一 IP 地址在另一天，则将对其进行第二次计数。
Example Data:
示例数据：
PK | FK | ipNum | timestamp
11 | 404 | 219395 | 2013-01-06 22:23:56
7 | 404 | 467719 | 2013-01-06 22:23:41
8 | 404 | 4718869 | 2013-01-06 22:23:42
10 | 404 | 16777224 | 2013-01-06 22:23:56
5 | 404 | 1292435475 | 2013-01-06 22:23:25
12 | 404 | 1526990605 | 2013-01-06 22:23:57
6 | 404 | 1594313225 | 2013-01-06 22:23:40
4 | 404 | 1610613001 | 2013-01-06 22:23:23
9 | 404 | 1628635192 | 2013-01-06 22:23:55
1 | 404 | 2130706433 | 2013-01-06 21:29:38
2 | 407 | 2130706433 | 2013-01-06 21:31:59
3 | 407 | 2130706433 | 2013-01-06 21:32:22
回答by John Woo
SELECT DATE(timestamp) Date, COUNT(DISTINCT ipNum) totalCOunt
FROM tableName
GROUP BY DATE(timestamp)
回答by giantNinja
Here's how you'd get counts per day for the last 7 days:
以下是过去 7 天每天计数的方法：
select
count(*) as count,
date(timestamp) as date
from
tablename
where
timestamp >= date_sub(curdate(), interval 7 day)
group by
date;
+-------------+------------+
| count | date |
+-------------+------------+
| #forThatDay | 2020-02-21 |
| #forThatDay | 2020-02-22 |
| #forThatDay | 2020-02-22 |
| #forThatDay | 2020-02-23 |
| #forThatDay | 2020-02-24 |
| #forThatDay | 2020-02-25 |
| #forThatDay | 2020-02-26 |
+-------------+------------+
7 rows in set (0.03 sec)
group by the ipNumcolumn first to get distinct counts of that column per day:
ipNum首先按列分组以获得每天该列的不同计数：
select
count(*) as count,
date(timestamp) as date
from
tablename
where
timestamp >= date_sub(curdate(), interval 7 day)
group by
ipNum, date;
回答by Babis K.
$log_date = date('Y-m-d H:i:s');
$log_date = date('Y-m-d H:i:s', strtotime($log_date.' -1 hour'));
SELECT ipNum, COUNT(ipNum), COUNT(DISTINCT ipNum), DATE(timestamp), timestamp FROM tableName WHERE `timestamp` > '".$log_date."' GROUP BY ipNum ORDER BY DATE(timestamp) DESC
THIS WILL GIVE YOU A RESULT LIKE
这会给你一个结果
ip TIME COUNTIPS
11.237.115.30 2018-01-27 19:13:51 1
21.744.133.52 2018-01-27 19:14:03 1
44.628.197.51 2018-01-27 19:48:12 14
