I am looking to figure out the correct way to build this statement in SQL (oracle). What I have is a columns in a table that consists of "Date" and "Direction". In the direction column it either is a "North" or "South". I am trying to figure out how to output the # of times in a row before the row occurred (like a Win or Loss Streak) did "North" or "South appear". For example:

SELECT date, direction, ? As Streak FROM temptable order by date ASC;

Sample dataset of 8 rows:

1. 2022-01-01, North
2. 2022-01-02, North
3. 2022-01-03, North
4. 2022-01-04, North
5. 2022-01-05, South
6. 2022-01-06, South
7. 2022-01-07, South
8. 2022-01-08, North

The results would be:

date, Direction, Streak

1. 2022-01-01, North, 0;
2. 2022-01-02, North, 1;
3. 2022-01-03, North, 2;
4. 2022-01-04, North, 3;
5. 2022-01-05, South, 0;
6. 2022-01-06, South, 1;
7. 2022-01-07, South, 2;
8. 2022-01-08, North, 0;

CodePudding user response：

Not specifically Oracle but this is a standard approach using row_number to identify sequential groups, then to count within those groups:

with g as (
    select *, 
      Row_Number() over(order by date) 
       - Row_Number() over(partition by direction order by date) gn
    from t
)
select *, 
 Row_Number() over(partition by gn order by date) - 1 as Streak
from g
order by date;