CODEWITHSUNDEEP
sqlsql-server
baronming
baronming

Reputation: 258

SQL find a sequence count for a sport

I'm searching for a SELECT that will gives me a "sequence count". Take a sport of your choice and I want to know how many games a player is having a goal (hockey, soccer) or a hit (baseball) for example.

So I have this table DaysData

INSERT @DaysData(PlayerID, GameDate, GoalsInGame)
VALUES
(100, '20180105', 0),
(100, '20180107', 1),
(100, '20180109', 1),
(100, '20180111', 2),
(100, '20180113', 1),
(100, '20180115', 0),
(100, '20180117', 0),
(100, '20180118', 1),
(100, '20180120', 1),
(100, '20180122', 3),
(100, '20180124', 2),
(100, '20180125', 1),
(100, '20180127', 0),
(100, '20180129', 0),
(100, '20180130', 1);

So I want to have something telling me: Player 100 

    CountGameSequence , DateFrom ,  DateTo 
    4 ,                 20180107 , 20180113 
    5 ,                 20180118 , 20180125
    1 ,                 20180130 , 20180130

I try many things with LEAD, LAG , RowNumber(), etc...I never find a way to get the good query.

Upvotes: 1

Views: 152

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270011

This is a gaps-and-islands problem. In your case, I think a difference of row numbers works well:

select playerid, min(gamedate), max(gamedate), count(*) as sequence_length
from (select dd.*,
             row_number() over (partition by playerid order by gamedate) as seqnum,
             row_number() over (partition by playerid, sign(goalsingame) order by gamedate) as seqnum_2
      from @DaysData dd
     ) dd
where goalsingame > 0
group by playerid, (seqnum - seqnum_2)
order by sequence_length desc;

The one trick to this is using sign() to capture games where the player has a score. That is a convenience. The code (case when goalsingame > 0 then 1 else 0 end) would do the same thing.

Why does this work? That is a bit hard to explain. However, I find that if you look at the results from the subquery you will quickly see how the difference in the two sequence numbers identifies adjacent values.

Upvotes: 4

Related Questions