How To Get Result Based On 10 Minutes Interval In Mysql

How to find ten most search distinct destinations for 10 minutes interval? user_id time action destination place 2017032000000000097 00:00:00 Click

Solution 1:

select destination , count(id) from your_table
whereMINUTE(time)>=MINUTE(now())-10andMINUTE(time)<minute(now()) 
groupby destination 
LIMIT 10

Solution 2:

Take the first three characters of each time, and aggregate over that substring.

So the first five times become 00:0 and the next three become 00:1

This way, any times within a ten min interval get truncated to the same thing.

selectsubstring(time,0,4) as truncTime, destination, count(*)
fromtablegroupby truncTime

Gives you

truncTime  destination  count
00:0       Rimini       400:0       Berlin       100:1       Berlin       2 

Solution 3:

i have found the solution by below query.

select a.time_column,group_concat(a.destination orderby ct desc) from  (selectcasewhentimebetween'00:00:00'and'00:10:00'then'00:10:00'whentimebetween'00:10:01'and'00:20:00'then'00:20:00'whentimebetween'00:20:01'and'00:30:00'then'00:30:00'else'00:00:00'endas time_column 
        , destination
        , count(destination) ct
from click
groupby time_column,destination
orderby time_column,count(destination) desc limit 10)a
groupby a.time_column;