MySQL:每天计算不同的行数

I have an interesting query I need to do. I have a table with an INT column containing ip address numbers (using INET_ATON), and a timestamp column. 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.

我需要做一个有趣的查询。我有一个包含ip地址号(使用INET_ATON)和时间戳列的INT列的表。我希望能够计算出每天唯一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

2 个解决方案

#1

SELECT  DATE(timestamp) Date, COUNT(DISTINCT ipNum) totalCOunt
FROM    tableName
GROUP   BY  DATE(timestamp)

• SQLFiddle Demo
• SQLFiddle演示

#2

$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