如何计算每天具有不同IP地址的行? [英] How can I count rows having distinct IP address per day?

问题描述

如何计算每天在我的网站上有多少个唯一地址? 我的桌子看起来像这样:

How can I count how many unique addresses have been on my site each day? My table looks like this:

行名:id，名称，实体，ip_address，日期

Row names: id, name, entity, ip_address, date

1，"Baldur"，"EntityA"，"85.221.18.251"，"2012-01-09 17:32:52" 2，"Baldur"，"EntityB"，"85.221.18.251"，"2012-01-09 17:32:57" 3，"Baldur"，"EntityB"，"85.221.18.252"，"2012-01-09 17:33:01" 4，"Baldur"，"EntityA"，"85.221.18.253"，"2012-01-10 17:33:12" 5，"Mango"，"EntityA"，"85.221.18.257"，"2012-01-10 17:32:52" 6，"Baldur"，"EntityB"，"85.221.18.251"，"2012-01-10 17:32:57" 7，"Mango"，"EntityB"，"85.221.18.253"，"2012-01-11 17:33:01" 8，"Mango"，"EntityA"，"85.221.18.251"，"2012-01-11 17:33:12" 9，"Mango"，"EntityA"，"85.221.18.253"，"2012-01-11 17:32:52" 10，"Baldur"，"EntityB"，"85.221.18.255"，"2012-01-11 17:32:57" 11，"Mango"，"EntityB"，"85.221.18.254"，"2012-01-11 17:33:01" 12，"Mango"，"EntityA"，"85.221.18.251"，"2012-01-12 17:33:12"

1,"Baldur","EntityA","85.221.18.251","2012-01-09 17:32:52" 2,"Baldur","EntityB","85.221.18.251","2012-01-09 17:32:57" 3,"Baldur","EntityB","85.221.18.252","2012-01-09 17:33:01" 4,"Baldur","EntityA","85.221.18.253","2012-01-10 17:33:12" 5,"Mango","EntityA","85.221.18.257","2012-01-10 17:32:52" 6,"Baldur","EntityB","85.221.18.251","2012-01-10 17:32:57" 7,"Mango","EntityB","85.221.18.253","2012-01-11 17:33:01" 8,"Mango","EntityA","85.221.18.251","2012-01-11 17:33:12" 9,"Mango","EntityA","85.221.18.253","2012-01-11 17:32:52" 10,"Baldur","EntityB","85.221.18.255","2012-01-11 17:32:57" 11,"Mango","EntityB","85.221.18.254","2012-01-11 17:33:01" 12,"Mango","EntityA","85.221.18.251","2012-01-12 17:33:12"

我正在考虑以下方面的内容:

I'm thinking something along these lines:

SELECT date, COUNT(ip)
FROM mytable
GROUP BY date

这只会给我计数为1的行.

This only gives me rows with count of one.

推荐答案

您不是按日期(意味着每天输入一个条目)进行分组，而是按实际时间戳进行分组(在您的情况下为1秒钟).您必须应用date函数从中提取实际日期.

You are not grouping by a date (meaning one entry per day), but on the actual timestamp, which in your case comes down to a second. You have to apply the date function to extract the actual date from it.

另一方面，添加DISTINCT关键字以实际计算不同的ip并忽略重复的条目.

On the other hand add the DISTINCT keyword to actually count different ips and ignore duplicate entries.

SELECT DATE( `date` ), COUNT( DISTINCT ip_address )
  FROM mytable
  GROUP BY DATE( `date` )

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