CODEWITHSUNDEEP
mysqlsql
I-M-JM
I-M-JM

Reputation: 15960

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?

Upvotes: 3

Views: 184

Answers (2)

ean5533
ean5533

Reputation: 8994

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:

  1. Changed ceil() to round() because ceil/floor will cause edge cases to be treated improperly
  2. 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.

Upvotes: 2

rMX
rMX

Reputation: 1090

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.

Upvotes: 1

Related Questions