MySQL：每天计算不同的行 [英] MySQL: Count the distinct rows per day

问题描述

我有一个有趣的查询，我需要做。我有一个表，其中包含包含ip地址编号（使用 INET_ATON ）和 INT > timestamp 列。我想要能够计数每天唯一的IP地址列的数量。也就是说，每天有多少不同的ip行。因此，例如，如果一个ip地址在同一天在同一天两次，它在最终计数中计数为1，但是如果相同的ip地址是在另一天，它会被计数，将有一个第二计数。 / p>

示例数据：

  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 
  

解决方案

  SELECT DATE（timestamp）Date，COUNT（DISTINCT ipNum）totalCOunt 
 FROM tableName 
 GROUP BY DATE（timestamp）
  pre> 
 
 
 
 
  SQLFiddle演示 
 
 
 
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.

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

解决方案
SELECT  DATE(timestamp) Date, COUNT(DISTINCT ipNum) totalCOunt
FROM    tableName
GROUP   BY  DATE(timestamp)



SQLFiddle Demo


                        
这篇关于MySQL：每天计算不同的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！