Блог

У меня есть следующие данные, упорядоченные по идентификатору игрока и дате совпадения. Я хотел бы узнать группу записей, которая имеет максимальное количество последовательных запусков (4 запуска с 2014-04-03 по 2014-04-12 3 раза подряд)

  player_id  match_date  runs
    1       2014-04-01    5
    1       2014-04-02    55       
    1       2014-04-03    4       
    1       2014-04-10    4       
    1       2014-04-12    4       
    1       2014-04-14    3       
    1       2014-04-19    4       
    1       2014-04-20    44               
    2       2014-04-01    23
    2       2014-04-02    23       
    2       2014-04-03    23       
    2       2014-04-10    23       
    2       2014-04-12    4       
    2       2014-04-14    3       
    2       2014-04-19    23       
    2       2014-04-20    1   
  

Я придумал следующий SQL:

 select *,row_number() over (partition by ranked.player_id,ranked.runs
order by ranked.match_date) as R from (
select player_id ,match_date,runs from players order by 1,2 desc )
ranked order by ranked.player_id, match_date asc
  

Но это продолжает ранг из предыдущих последовательных запусков (ожидается, что 4 запуска в 2014-04-19 для игрока 1 получат ранг 1, но получат ранг 4, поскольку уже было 3 появления одного и того же раздела). Аналогично, ожидается, что 23 пробега для игрока 2 в 2014-04-19 получат ранг 1, но получат ранг 5, поскольку для этого игрока уже было 4 случая из 23 пробегов.

Как мне сбросить ранг обратно на 1, когда значение runs изменяется по сравнению с предыдущей строкой?

Схема, данные, SQL и выходные данные доступны в SQLFiddle.

Вы можете сделать это с помощью оконных функций.

 select player_id, runs, count(*) as numruns
from (select p.*,
             (row_number() over (partition by player_id order by match_date) -
              row_number() over (partition by player_id, runs order by match_date)
             ) as grp
      from players p
     ) pg
group by grp, player_id, runs
order by numruns desc
limit 1;
  

Ключевое замечание заключается в том, что «прогоны в последовательности» обладают следующим свойством: если вы перечисляете строки (для каждого игрока) по дате и перечисляете строки для каждого игрока и по прогонам по дате, то разница постоянна, когда все прогоны одинаковы и упорядочены. Это формирует группу, которую вы можете использовать для агрегирования, чтобы определить нужного вам игрока.

Вот скрипка SQL.

 select p1.player_id, p1.match_date, p1.runs, count(p2.match_date) from players p1
join players p2 on p1.player_id = p2.player_id
    and p1.match_date >= p2.match_date
    and p1.runs = p2.runs
    and not exists (
        select 1 from players p3
        where p3.runs <> p2.runs
        and p3.player_id = p2.player_id
        and p3.match_date < p1.match_date
        and p3.match_date > p2.match_date
    )
group by p1.player_id, p1.match_date, p1.runs
order by p1.player_id, p1.match_date
  

http://sqlfiddle.com /#!15/78a77/1