从mysql获取今天访问的所有时间的独特IP

Question

In mysql table, I have ips, views, and the date that they visited. Everyday new ips would be inserted, and only the views would be updated on that date. So, from day to day, the ips are ALWAYS distinct. However, that is not the case when it's weekly, monthly, or yearly, since the same ip might come back.

I'm trying to select the distinct IPs from all time that visited today, and it returned 39 rows, but it's suppose to be less, since some of the IPs aren't distinct since they visited from before today. What am I doing wrong?

SELECT DISTINCT (ip), DATE AS tempDate
FROM  `ipAll` 
HAVING tempDate =  '2015-12-29'
LIMIT 100

Note:

I don't want to select the distinct date from today. I want to select the distinct date from ALL TIME, that VISITED today.

Answer 1

You can find out which IP addresses were only logged today, and never before, with MIN and HAVING:

SELECT ip, Min(DATE) AS tempDate
FROM  `ipAll` 
HAVING Min(DATE) = '2015-12-29'
GROUP BY ip

It takes the first date ( => min ) that an IP address was logged, and then takes only those IP addresses for which that date is 2015-12-29.

To do this for the last 7 days, you would do the same, but with the first date of that week, and a >= comparison:

SELECT ip, Min(DATE) AS tempDate
FROM  `ipAll` 
HAVING Min(DATE) >= '2015-12-22'
GROUP BY ip

You mentioned you also register number of views (per day), so you could add something like SUM(views) to the select list, to have the total number of views for each selected IP. And you could also add COUNT(*) which would give the number of days in which these visits took place. For the day report that would always be 1 of course, but for the weekly it will be between 1 and 7. When you leave out the WHERE clause, it will give those results for all IP addresses, and the views and day counts can then be much higher.

As requested in comments, here is a query that gives the IP addresses that logged in today, but had logged in before:

SELECT ip, Min(DATE) AS tempDate
FROM  `ipAll` 
HAVING Max(DATE) = CURDATE()
AND    Count(*) > 1
GROUP BY ip

This can be understood as: select IP addresses whose last login date was today, but for which there is more than one day that they logged in.

In short, you can use these aggregate functions on DATE:

• Min(DATE): the first date the IP was logged ever
• Max(DATE): the most recent date the IP was logged
• Count(*): the number of distinct days the IP was logged
• Sum(views): the number of views made from the IP over all days

The above will apply to the complete history of logged visits, except if you also add a WHERE clause limiting the dates you are looking at.

Understand the difference between WHERE and HAVING: WHERE applies a filter to individual records, while HAVING filters groups as a whole, in this case groups of records with same IP addresses. HAVING must filter using aggregate functions like COUNT, MIN, MAX, while WHERE can filter on individual DATE values.