SQL Query for players average hot streak duration

user_7571325 0
Viewed 93

Lets say I have a table with three columns and the following data:

* gamedate,playername,pointsScored
* 20180101,George,34
* 20180102,George,37
* 20180103,George,38
* 20180104,George,3
* 20180105,George,35
* 20180106,George,37
* 20180107,George,31
* 20180108,George,31
* 20180109,George,32
* 20180110,George,7

I'm looking for a sql query that tells me on average how many games in a row can this player maintain a score of 30+. I know it would have to be some sort of group by function but I'm kind of stuck on how to formulate this query. This is an Oracle table if that makes any difference.

Some additional date for testing:

with
inputs(gamedate,playername,pointsscored) as (
select to_date('20180912','yyyymmdd'), 'George',52 from dual union all
select to_date('20180907','yyyymmdd'), 'George',47 from dual union all
select to_date('20180829','yyyymmdd'), 'George',9 from dual union all
select to_date('20180823','yyyymmdd'), 'George',55 from dual union all
select to_date('20180818','yyyymmdd'), 'George',49 from dual union all
select to_date('20180811','yyyymmdd'), 'George',58 from dual union all
select to_date('20180805','yyyymmdd'), 'George',31 from dual union all
select to_date('20180730','yyyymmdd'), 'George',40 from dual union all
select to_date('20180720','yyyymmdd'), 'George',44 from dual union all
select to_date('20180712','yyyymmdd'), 'George',45 from dual union all
select to_date('20180707','yyyymmdd'), 'George',29 from dual union all
select to_date('20180701','yyyymmdd'), 'George',-5 from dual union all
select to_date('20180626','yyyymmdd'), 'George',46 from dual union all
select to_date('20180620','yyyymmdd'), 'George',22 from dual union all
select to_date('20180614','yyyymmdd'), 'George',49 from dual union all
select to_date('20180609','yyyymmdd'), 'George',40 from dual union all
select to_date('20180602','yyyymmdd'), 'George',40 from dual
)
oracle sql
2 Answers

In Oracle 12.1 or higher, the MATCH_RECOGNIZE clause can do quick work of such assignments:

with
  inputs(gamedate,playername,pointsscored) as (
    select to_date('20180101','yyyymmdd'), 'George', 34 from dual union all
    select to_date('20180102','yyyymmdd'), 'George', 37 from dual union all
    select to_date('20180103','yyyymmdd'), 'George', 38 from dual union all
    select to_date('20180104','yyyymmdd'), 'George',  3 from dual union all
    select to_date('20180105','yyyymmdd'), 'George', 35 from dual union all
    select to_date('20180106','yyyymmdd'), 'George', 37 from dual union all
    select to_date('20180107','yyyymmdd'), 'George', 31 from dual union all
    select to_date('20180108','yyyymmdd'), 'George', 31 from dual union all
    select to_date('20180109','yyyymmdd'), 'George', 32 from dual union all
    select to_date('20180109','yyyymmdd'), 'George',  7 from dual
  )
select playername, avg(cnt) as average_streak
from   inputs
match_recognize(
  partition by playername
  order by     gamedate
  measures     count(*) as cnt
  one row per match
  pattern ( a+ )
  define a as pointsscored >= 30
)
group by playername
;

PLAYER AVERAGE_STREAK
------ --------------
George              4

Explanation:

MATCH_RECOGNIZE partitions the input rows by player, and orders them within each partition by game date. A "match" is an occurrence of one or more consecutive rows (the a+ in the PATTERN sub-clause) with 30 or more points scored (see the DEFINE subclause). MATCH_RECOGNIZE returns one row for each match it finds, and specifically it returns the player name and the count of rows in the match (which is the length of the streak, in number of consecutive rows, or games). The outer query groups by player and takes the average streak length.

Admin1

Here is one way. It uses the Tabibitosan (or "fixed differences") method to recognize streaks - that's the difference of two ROW_NUMBER calls in the inner query. The rest is grouping and taking aggregate functions.

The WITH clause is to simulate the data; it is not part of the solution, it should be removed before testing the solution on actual data. (Use your actual table name instead of INPUTS in the inner query.)

with
  inputs(gamedate,playername,pointsscored) as (
    select to_date('20180101','yyyymmdd'), 'George', 34 from dual union all
    select to_date('20180102','yyyymmdd'), 'George', 37 from dual union all
    select to_date('20180103','yyyymmdd'), 'George', 38 from dual union all
    select to_date('20180104','yyyymmdd'), 'George',  3 from dual union all
    select to_date('20180105','yyyymmdd'), 'George', 35 from dual union all
    select to_date('20180106','yyyymmdd'), 'George', 37 from dual union all
    select to_date('20180107','yyyymmdd'), 'George', 31 from dual union all
    select to_date('20180108','yyyymmdd'), 'George', 31 from dual union all
    select to_date('20180109','yyyymmdd'), 'George', 32 from dual union all
    select to_date('20180109','yyyymmdd'), 'George',  7 from dual
  )
select   playername, avg(ct) as average_streak
from     (
           select playername, count(*) as ct
           from   (
                    select playername, pointsscored,
                           row_number() over (partition by playername 
                                                order by gamedate)
                           - row_number() over (partition by playername,
                                                 case when pointsscored >= 30 
                                                      then 0 end
                                                 order by gamedate) as grp
                    from   inputs
                  )
           group by playername, grp
           having   min(pointsscored) >= 30
         )
group by playername
order by playername
;

OUTPUT:

PLAYER AVERAGE_STREAK
------ --------------
George              4
Admin1
Related