ROW_NUMBER 查询 [英] ROW_NUMBER query

问题描述

我有一张桌子:

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.

这篇关于ROW_NUMBER 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！