ROW_NUMBER 查询
我有一张桌子:
Trip Stop Time
-----------------
1 A 1:10
1 B 1:16
1 B 1:20
1 B 1:25
1 C 1:31
1 B 1:40
2 A 2:10
2 B 2:17
2 C 2:20
2 B 2:25
我想在查询输出中再添加一列:
I want to add one more column to my query output:
Trip Stop Time Sequence
-------------------------
1 A 1:10 1
1 B 1:16 2
1 B 1:20 2
1 B 1:25 2
1 C 1:31 3
1 B 1:40 4
2 A 2:10 1
2 B 2:17 2
2 C 2:20 3
2 B 2:25 4
最难的部分是 B，如果 B 彼此相邻，我希望它是相同的序列，如果不是，则算作一个新行.
The hard part is B, if B is next to each other I want it to be the same sequence, if not then count as a new row.
我知道
row_number over (partition by trip order by time)
row_number over (partition by trip, stop order by time)
他们都不会满足我想要的条件.有没有办法查询这个?
None of them will meet the condition I want. Is there a way to query this?
推荐答案
create table test
(trip number
,stp varchar2(1)
,tm varchar2(10)
,seq number);
insert into test values (1, 'A', '1:10', 1);
insert into test values (1, 'B', '1:16', 2);
insert into test values (1, 'B', '1:20', 2);
insert into test values (1 , 'B', '1:25', 2);
insert into test values (1 , 'C', '1:31', 3);
insert into test values (1, 'B', '1:40', 4);
insert into test values (2, 'A', '2:10', 1);
insert into test values (2, 'B', '2:17', 2);
insert into test values (2, 'C', '2:20', 3);
insert into test values (2, 'B', '2:25', 4);
select t1.*
,sum(decode(t1.stp,t1.prev_stp,0,1)) over (partition by trip order by tm) new_seq
from
(select t.*
,lag(stp) over (order by t.tm) prev_stp
from test t
order by tm) t1
;
TRIP S TM SEQ P NEW_SEQ
------ - ---------- ---------- - ----------
1 A 1:10 1 1
1 B 1:16 2 A 2
1 B 1:20 2 B 2
1 B 1:25 2 B 2
1 C 1:31 3 B 3
1 B 1:40 4 C 4
2 A 2:10 1 B 1
2 B 2:17 2 A 2
2 C 2:20 3 B 3
2 B 2:25 4 C 4
10 rows selected
您想查看停靠点在一行和下一行之间是否发生变化.如果是，您希望增加序列.因此，使用滞后将上一个停靠点放入当前行.
You want to see if the stop changes between one row and the next. If it does, you want to increment the sequence. So use lag to get the previous stop into the current row.
我使用 DECODE 是因为它处理 NULL 的方式，而且它比 CASE 更简洁，但如果您遵循教科书，您可能应该使用 CASE.
I used DECODE because of the way it handles NULLs and it is more concise than CASE, but if you are following the text book, you should probably use CASE.
使用 SUM 作为带有 ORDER BY 子句的分析函数将给出您正在寻找的答案.
Using SUM as an analytic function with an ORDER BY clause will give the answer you are looking for.
相关文章