How to find ten most search distinct destinations for 10 minutes interval?
如何在10分钟间隔内找到十个搜索不同的十个目的地?
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;
3 个解决方案
#1
0
select destination , count(id) from your_table
where MINUTE(time)>= MINUTE(now())-10 and MINUTE(time)< minute(now())
group by destination
LIMIT 10
#2
0
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
因此前五次变为00:0,接下来的三次变为00:1
This way, any times within a ten min interval get truncated to the same thing.
这样,在十分钟间隔内的任何时间都被截断为相同的东西。
select substring(time,0,4) as truncTime, destination, count(*)
from table
group by truncTime
Gives you
truncTime destination count
00:0 Rimini 4
00:0 Berlin 1
00:1 Berlin 2
#3
0
i have found the solution by below query.
我通过以下查询找到了解决方案。
select a.time_column,group_concat(a.destination order by ct desc) from (select case
when time between '00:00:00' and '00:10:00' then '00:10:00'
when time between '00:10:01' and '00:20:00' then '00:20:00'
when time between '00:20:01' and '00:30:00' then '00:30:00'
else '00:00:00' end as time_column
, destination
, count(destination) ct
from click
group by time_column,destination
order by time_column,count(destination) desc limit 10)a
group by a.time_column;
#1
0
select destination , count(id) from your_table
where MINUTE(time)>= MINUTE(now())-10 and MINUTE(time)< minute(now())
group by destination
LIMIT 10
#2
0
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
因此前五次变为00:0,接下来的三次变为00:1
This way, any times within a ten min interval get truncated to the same thing.
这样,在十分钟间隔内的任何时间都被截断为相同的东西。
select substring(time,0,4) as truncTime, destination, count(*)
from table
group by truncTime
Gives you
truncTime destination count
00:0 Rimini 4
00:0 Berlin 1
00:1 Berlin 2
#3
0
i have found the solution by below query.
我通过以下查询找到了解决方案。
select a.time_column,group_concat(a.destination order by ct desc) from (select case
when time between '00:00:00' and '00:10:00' then '00:10:00'
when time between '00:10:01' and '00:20:00' then '00:20:00'
when time between '00:20:01' and '00:30:00' then '00:30:00'
else '00:00:00' end as time_column
, destination
, count(destination) ct
from click
group by time_column,destination
order by time_column,count(destination) desc limit 10)a
group by a.time_column;