How to find ten most search distinct destinations for 10 minutes interval?
user_id time action destination place
2017032000000000097 00:00:00 Click Rimini Regina Elena 57
2017032000000000097 00:03:53 Click Sant Regina Elena 571
2017032000000000097 00:01:16 Click Regina Regina Elena 572
2017032000000000097 00:04:34 Click Rimini Regina Elena 57
2017032000000000129 00:07:32 Click Berlin M??ggelsee Berlin
2017032000000000129 00:18:36 Click GRC SensCity Berlin Spandau
2017032000000000129 00:16:12 Click Berlin Azimut Berlin City South
expected output/similar output
time destination(top 10 during 10 minute interval)
------------- ----
00:00:00 NULL
00:10:00 Rimini,Sant,Regina
00:20:00 Berlin,Grc
00:30:00 NULL
i have tried below code,
select destination , count(user_id),time from click
where MINUTE(time)>= MINUTE(now())-10 and MINUTE(time)< minute(now()) and destination is not null
group by destination,MINUTE(time)>= MINUTE(now())-10 and MINUTE(time)< minute(now()) order by count(user_id) desc;
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…