問題描述

我有一張桌子:

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 查詢的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！