我有一个应用,场景是这样的:
我知道了一段路上面的车辆经过时间,我把时间切成时间片,然后希望知道每个时间片上面车辆通过时间的最大似然值(就是出现次数最多的通过时间,为了简化计算,通过时间也被切成了片)。假设这个表是journey: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'
);
我用了下面的sql语句获得了各个时间片上面各个通过时间(离散化后)的出现次数。
可以认为时间片为a,通过时间为b,出现次数为c。
那么我需要知道的是各个a值相同的组的内部,c最大的那一项所对应的b值。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; #获得各个时间片上 各个通过时间的出现次数
可是根据time_bin进行了group之后,我不知道要怎么在组内根据num来选择ml_time?我知道一般情况下对于group之后如果使用非聚集函数那么返回的是表中第一项的值。我现在投机取巧的实现方法是这样的: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;但是我想知道,如果不用这种方法,或者对于c列的选择条件不是最大值或者最小值(没有办法把它挪到第一个),那比较通用的方法是什么呢?
SQLgroup
我知道了一段路上面的车辆经过时间,我把时间切成时间片,然后希望知道每个时间片上面车辆通过时间的最大似然值(就是出现次数最多的通过时间,为了简化计算,通过时间也被切成了片)。假设这个表是journey: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'
);
我用了下面的sql语句获得了各个时间片上面各个通过时间(离散化后)的出现次数。
可以认为时间片为a,通过时间为b,出现次数为c。
那么我需要知道的是各个a值相同的组的内部,c最大的那一项所对应的b值。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; #获得各个时间片上 各个通过时间的出现次数
可是根据time_bin进行了group之后,我不知道要怎么在组内根据num来选择ml_time?我知道一般情况下对于group之后如果使用非聚集函数那么返回的是表中第一项的值。我现在投机取巧的实现方法是这样的: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;但是我想知道,如果不用这种方法,或者对于c列的选择条件不是最大值或者最小值(没有办法把它挪到第一个),那比较通用的方法是什么呢?
SQLgroup
解决方案 »
- 问一条简单的sql语句效率问题
- 数据库连接
- MySQL触发器能不能取得的列名?
- Mysql与JSP开发好,还是与php更好?
- 请问SQL和MYSQL有什么区别呢?学哪个难点,学哪个吃香点呢?谢谢回答啊
- 如何限制一个表的两个字段内容 分别 都 不能重复
- mysqldump 导出中文显示?的问题
- 用JAVA链接MYSQL时,驱动程序已经配置完成的情况下找不到驱动的问题,请帮忙解决一下...
- 求教:MYSQL正则表达式的字段是否需要索引,求高手指教
- mysql服务已开启,密码也是对的,但是登录时会闪退,而且本地连接不上,提示1130的错误
- 隐藏MySQL 版本信息
- 请教mysql里面int类型时间字段为什么相差8小时的问题。
from table1 t
where not exists (select 1 from table1 where a列=t.a列 and b列>t.b列);参考下贴中的多种方法http://blog.csdn.net/acmain_chm/article/details/4126306
[征集]分组取最大N条记录方法征集,及散分....
但是我的需求更复杂一些。
我的table1表是在一个大表里面做了很多操作group出来的,如果采用您提到的这种方法需要遍历3次这个大表,即使有索引还是很慢。
我之前实现了一个2次遍历的版本(外层枚举各个时间片并且传入内层,内层计算这个时间片内的出现次数最多的世界),用时378s,这还只是一条路一天的技术,而且的我粒度还很粗。
我用我那个投机取巧的方法只有3秒。
但是我的需求更复杂一些。
我的table1表是在一个大表里面做了很多操作group出来的,如果采用您提到的这种方法需要遍历3次这个大表,即使有索引还是很慢。
我之前实现了一个2次遍历的版本(外层枚举各个时间片并且传入内层,内层计算这个时间片内的出现次数最多的世界),用时378s,这还只是一条路一天的技术,而且的我粒度还很粗。
我用我那个投机取巧的方法只有3秒。
出现次数做多的通行时间