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:
- 2022-01-01, North
- 2022-01-02, North
- 2022-01-03, North
- 2022-01-04, North
- 2022-01-05, South
- 2022-01-06, South
- 2022-01-07, South
- 2022-01-08, North
The results would be:
date, Direction, Streak
- 2022-01-01, North, 0;
- 2022-01-02, North, 1;
- 2022-01-03, North, 2;
- 2022-01-04, North, 3;
- 2022-01-05, South, 0;
- 2022-01-06, South, 1;
- 2022-01-07, South, 2;
- 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;