求教一个有趣的sql有关问题:对a列group,求各个group中b列值最大(满足某条件)的一项的c值
求教一个有趣的sql问题:对a列group,求各个group中b列值最大(满足某条件)的一项的c值
我有一个应用,场景是这样的:
我知道了一段路上面的车辆经过时间,我把时间切成时间片,然后希望知道每个时间片上面车辆通过时间的最大似然值(就是出现次数最多的通过时间,为了简化计算,通过时间也被切成了片)。
假设这个表是journey:
我用了下面的sql语句获得了各个时间片上面各个通过时间(离散化后)的出现次数。
可以认为时间片为a,通过时间为b,出现次数为c。
那么我需要知道的是各个a值相同的组的内部,c最大的那一项所对应的b值。
可是根据time_bin进行了group之后,我不知道要怎么在组内根据num来选择ml_time?
我知道一般情况下对于group之后如果使用非聚集函数那么返回的是表中第一项的值。我现在投机取巧的实现方法是这样的:
但是我想知道,如果不用这种方法,或者对于c列的选择条件不是最大值或者最小值(没有办法把它挪到第一个),那比较通用的方法是什么呢?
------解决方案--------------------
推荐一个不错的SQL函数: row_number()
------解决方案--------------------
我猜你想用如下的方法:
我有一个应用,场景是这样的:
我知道了一段路上面的车辆经过时间,我把时间切成时间片,然后希望知道每个时间片上面车辆通过时间的最大似然值(就是出现次数最多的通过时间,为了简化计算,通过时间也被切成了片)。
假设这个表是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列的选择条件不是最大值或者最小值(没有办法把它挪到第一个),那比较通用的方法是什么呢?
SQL
group
选择
------解决方案--------------------
推荐一个不错的SQL函数: row_number()
------解决方案--------------------
我猜你想用如下的方法:
--按某一字段分组取最大(小)值所在行的数据
--(爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2007-10-23于浙江杭州)
/*
数据如下:
name val memo
a 2 a2(a的第二个值)
a 1 a1--a的第一个值
a 3 a3:a的第三个值
b 1 b1--b的第一个值
b 3 b3:b的第三个值
b 2 b2b2b2b2
b 4 b4b4
b 5 b5b5b5b5b5
*/
--创建表并插入数据:
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a', 2, 'a2(a的第二个值)')
insert into tb values('a', 1, 'a1--a的第一个值')
insert into tb values('a', 3, 'a3:a的第三个值')
insert into tb values('b', 1, 'b1--b的第一个值')
insert into tb values('b', 3, 'b3:b的第三个值')
insert into tb values('b', 2, 'b2b2b2b2')
insert into tb values('b', 4, 'b4b4')
insert into tb values('b', 5, 'b5b5b5b5b5')
go
--一、按name分组取val最大的值所在行的数据。
--方法1:
select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name
--方法2:
select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val)
--方法3:
select a.* from tb a,(select name,max(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4:
select a.* from tb a inner join (select name , max(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name