人 时间 动作 速度
A 17:30 起跑 10
A 17:31 加速 11
A 17:32 加速 12
A 17:33 加速 13
A 17:34 到达
B 17:30 起跑 10
B 17:33 到达
A 18:30 起跑 10
A 18:31 加速 11
A 18:34 到达 类似于这样的一个表,求一个sql语句能取出每个人每一次跑的速度,有加速的取加到最大的一个速度。如A第一次跑加到13,A第二次跑加到11,B没有加速,就是10。怎么取出这个数据,各位帮帮忙。问题补充:
实际上最主要是区分每次跑的最后速度,也就是每次到达前的那个速度,所以怎么区分第几次是关键啊
A 17:30 起跑 10
A 17:31 加速 11
A 17:32 加速 12
A 17:33 加速 13
A 17:34 到达
B 17:30 起跑 10
B 17:33 到达
A 18:30 起跑 10
A 18:31 加速 11
A 18:34 到达 类似于这样的一个表,求一个sql语句能取出每个人每一次跑的速度,有加速的取加到最大的一个速度。如A第一次跑加到13,A第二次跑加到11,B没有加速,就是10。怎么取出这个数据,各位帮帮忙。问题补充:
实际上最主要是区分每次跑的最后速度,也就是每次到达前的那个速度,所以怎么区分第几次是关键啊
SQL>
SQL> with tb as
2 (select 'A' name,'17:30' times, '起跑' action, 10 speed from dual union all
3 select 'A' ,'17:31' ,'加速', 11 from dual union all
4 select 'A' ,'17:32' ,'加速', 12 from dual union all
5 select 'A' ,'17:33' ,'加速', 13 from dual union all
6 select 'A' ,'17:34' ,'到达', null from dual union all
7 select 'B' ,'17:30' ,'起跑', 10 from dual union all
8 select 'B' ,'17:33' ,'到达', null from dual union all
9 select 'A' ,'18:30' ,'起跑', 10 from dual union all
10 select 'A' ,'18:31' ,'加速', 11 from dual union all
11 select 'A' ,'18:34' ,'到达',null from dual )
12 select name,max(speed)
13 from (select name,times,action,speed,row_number() over(partition by name order by times)-rownum rn
14 from tb)
15 group by name,rn
16 /
NAME MAX(SPEED)
---- ----------
A 13
B 10
A 11
什么都放到sql里 又麻烦 还影响效率
select name,times,action,speed,row_number() over(partition by name order by nvl(speed,0) desc) as speed_order from tb
)
where speed_order = 1
select name,times,action,speed,row_number() over(partition by name order by nvl(speed,0) desc) as speed_order from tb
)
where speed_order = 1
SQL>
SQL> with tb as
2 (select 'A' name,'17:30' times, '起跑' action, 10 speed from dual union all
3 select 'A' ,'17:31' ,'加速', 11 from dual union all
4 select 'A' ,'17:32' ,'加速', 12 from dual union all
5 select 'A' ,'17:34' ,'到达', null from dual union all
6 select 'A' ,'17:33' ,'加速', 13 from dual union all
7 select 'B' ,'17:30' ,'起跑', 10 from dual union all
8 select 'B' ,'17:33' ,'到达', null from dual union all
9 select 'A' ,'18:30' ,'起跑', 10 from dual union all
10 select 'A' ,'18:31' ,'加速', 11 from dual union all
11 select 'A' ,'18:34' ,'到达',null from dual )
12 select name,max(speed)
13 from (select name,times,action,speed,row_number() over(partition by name order by times)-rownum rn
14 from tb)
15 group by name,rn;
NAME MAX(SPEED)
---- ----------
A 12
A 13
A
B 10
A 11
SQL>
2 (select 'A' name,'17:30' times, '起跑' action, 10 speed from dual union all
3 select 'A' ,'17:31' ,'加速', 11 from dual union all
4 select 'A' ,'17:32' ,'加速', 12 from dual union all
5 select 'A' ,'17:33' ,'加速', 13 from dual union all
6 select 'A' ,'17:34' ,'到达', null from dual union all
7 select 'B' ,'17:30' ,'起跑', 10 from dual union all
8 select 'B' ,'17:33' ,'到达', null from dual union all
9 select 'A' ,'18:30' ,'起跑', 10 from dual union all
10 select 'A' ,'18:31' ,'加速', 11 from dual union all
11 select 'A' ,'18:34' ,'到达',null from dual )
12 SELECT NAME, times, speed
13 FROM (SELECT NAME,
14 times,
15 action,
16 speed,
17 row_number() over(PARTITION BY NAME, (SELECT MAX(times)
18 FROM tb b
19 WHERE b.NAME = a.NAME
20 AND b.action = '起跑'
21 AND b.times <=
22 a.times) ORDER BY times DESC) rn
23 FROM tb a
24 WHERE a.action != '到达')
25 WHERE rn = 1
26 ;
NAME TIMES SPEED
---- ----- ----------
A 17:33 13
A 18:31 11
B 17:30 10
SQL>
FROM (SELECT t.人, t.动作, MAX (t.速度) OVER (PARTITION BY t.人, t.动作) speed
FROM DUAL t) a
GROUP BY a.人, a.动作把dual换成你自己的表。
18 FROM tb b
19 WHERE b.NAME = a.NAME
20 AND b.action = '起跑'
21 AND b.times <=
22 a.times) ORDER BY times DESC) rn
这个能解释下吗,不太理解,他的具体作用是什么?