finding total and unique hits for each product
I have table structure as follows
id productid ip hittime
---------------------------------------------------------------------------
1 5 1.1.1.1 2011-05-03 06:55:11
2 5 1.1.1.1 开发者_运维问答 2011-05-03 06:57:11
3 6 2.2.2.2 2011-05-03 07:30:00
4 4 1.1.1.1 2011-05-03 07:32:54
5 5 2.2.2.2 2011-05-03 07:55:00
Now I need query such that, it output me total and unique hits for each product
productid totalhits uniquehits
------------------------------------------------------------------
4 1 1
5 3 2
6 1 1
Criteria for
Total Hits = all the records that belong to particular product
Unique Hits = 2 hits are identified as unique hits if (1) IP is different or (2) for same ip, there is difference of 5 mins in hittime
How can I achieve this?
rMX was extremely close with his solution, it's quite clever. He should really get the credit, I just tweaked it slightly to add in a couple missing pieces:
select productid, count(*) totalhits,
count(distinct
concat(ip,
date_format(hittime, '%Y%m%d%H'),
round(date_format(hittime, '%i') / 5) * 5)
) uniquehits
from table
group by productid
Changes I made to rMX's idea:
- Changed ceil() to round() because ceil/floor will cause edge cases to be treated improperly
- Multiply the results of the round() by 5. I think rMX meant to do this and just forgot to type it.
EDIT: The multiplying by 5 really isn't necessary. My brain was just muddled. Changing ceil() to round() still matters though.
UPD>
select productid, count(*) totalhits,
count(distinct
concat(ip,
date_format(hittime, '%Y%m%d%H'),
ceil(date_format(hittime, '%i') / 5))
) uniquehits
from table
group by productid
I think, this should work. Sorry, had no time to test it.
精彩评论