CODEWITHSUNDEEP
mysql
Joey.Z
Joey.Z

Reputation: 4780

count the number of rows between intervals

My table is like:

+---------+---------+------------+-----------------------+---------------------+
| visitId | userId  | locationId | comments              | time                |
+---------+---------+------------+-----------------------+---------------------+
|       1 |    3    |     12     | It's a good day here! | 2012-12-12 11:50:12 |
+---------+---------+------------+-----------------------+---------------------+
|       2 |    3    |     23     | very beautiful        | 2012-12-12 12:50:12 |
+---------+---------+------------+-----------------------+---------------------+
|       3 |    3    |     52     | nice                  | 2012-12-12 13:50:12 |
+---------+---------+------------+-----------------------+---------------------+

which records visitors' trajectory and some comments on the places visited.

I want to count the numbers of visitors that visit a specific place (say id=3227) from 0:00 to 23:59, over some interval (ie. 30mins)

I was trying to do this by :

SELECT COUNT(*) FROM visits 
GROUP BY HOUR(time), SIGN( MINUTE(time) - 30 )// if they are in the same interval this will yield the same result
WHERE locationId=3227

The problem is that if there is no record that falls in some interval, this will NOT return that interval with count 0. For example, there are no visitors visiting the location from 02:00 to 03:00, this will not give me the intervals of 02:00-02:29 and 02:30-2:59.

I want a result with an exact size of 48 (one for every half hour), how can I do this?

Upvotes: 0

Views: 227

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270061

You have to create a table with the 48 rows that you want and use left outer join:

select n.hr, n.hr, coalesce(v.cnt, 0) as cnt
from (select 0 as hr, -1 as sign union all
      select 0, 1 union all
      select 1, -1 union all
      select 1, 1 union all
      . . .
      select 23, -1 union all
      select 23, 1 union all
     ) left outer join
     (SELECT HOUR(time) as hr, SIGN( MINUTE(time) - 30 ) as sign, COUNT(*) as cnt
      FROM visits 
      WHERE locationId=3227
      GROUP BY HOUR(time), SIGN( MINUTE(time) - 30 )
     ) v
     on n.hr = v.hr and n.sign = v.sign
order by n.hr, n.hr

Upvotes: 2

Related Questions