56,678
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE `journey` (
`start_station` int(11) NOT NULL DEFAULT '0',
`start_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`end_station` int(11) NOT NULL DEFAULT '0',
`end_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00'
);
set @bin=10*60;
set @ml_time_bin=30;
select floor(time_to_sec(end_time)/@bin) time_bin, #时间片
floor(time_to_sec(timediff(end_time,start_time))/@ml_time_bin) ml_time, #通过用时片段
count(*) num #出现次数
from transponder.journey
where date(end_time)>='2010-01-01' and date(end_time)<'2010-01-02'
and start_station=1 and end_station=2 #限制在特定日子特定路段上
group by time_bin,ml_time; #获得各个时间片上 各个通过时间的出现次数
set @bin=10*60;
set @ml_time_bin=30;
select t.time_bin,ml_time,max(t.num) #出现时间,组内第一项的通过时间,最大的出现次数(第一项的出现次数)
from (select floor(time_to_sec(end_time)/@bin) time_bin, #时间片
floor(time_to_sec(timediff(end_time,start_time))/@ml_time_bin) ml_time, #通过用时片
count(*) num #出现次数
from journey
where date(end_time)>='2010-01-01' and date(end_time)<'2010-01-02'
and start_station=1 and end_station=2 #限制在特定日子特定路段上
group by time_bin,ml_time
order by count(*) desc #把出现次数最大的那项排在最前
) t
group by time_bin;