ROW_NUMBER request
I have a table:
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 another 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
The hard part is B, if B is next to each other, I want it to be the same sequence except for a newline.
I know
row_number over (partition by trip order by time)
row_number over (partition by trip, stop order by time)
None of them will fulfill the condition that I want. Is there a way to request this?
source to share
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 line and the next. If so, you want to increase the sequence. So use a delay to get the previous stop on the current line.
I used DECODE because of the way it handles NULL and it is more concise than CASE, but if you are following the tutorial you should probably use CASE.
Using SUM as an analytic function with an ORDER BY clause will give the answer you are looking for.
source to share
I think it's harder than it gets row_number()
. You need to identify groups of nearby stops and then list them.
You can identify groups using the line number difference. Then a dense_rank()
from difference does what you want, if there are no repeated stops on the trip:
select t.*,
dense_rank() over (partition by trip order by grp, stop)
from (select t.*,
(row_number() over (partition by trip order by time) -
row_number() over (partition by trip, stop order by time)
) as grp
from table t
) t;
If there's:
select t.*, dense_rank() over (partition by trip order by mintime)
from (select t.*,
min(time) over (partition by trip, grp, stop) as mintime
from (select t.*,
(row_number() over (partition by trip order by time) -
row_number() over (partition by trip, stop order by time)
) as grp
from table t
) t
) t;
source to share