Home > Net >  How to get # of times in a row an item occurred in SQL
How to get # of times in a row an item occurred in SQL

Time:02-22

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;
  • Related