查找分区内最大的连续数字组

我有以下按player_id 和match_date 排序的数据。我想找出连续运行次数最多的记录组（从2014-04-03到2014-04-12连续3次运行4次）

 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

但是这个继续之前连续运行的排名（玩家 1 在 2014 年 4 月 19 日进行的 4 次运行预计将获得排名 1，但由于已出现 3 次相同的分区，因此获得排名 4）。同样，2014 年 4 月 19 日球员 2 的 23 分预计将获得排名 1，但由于该球员已经出现 4 次 23 分，因此获得排名 5。

我如何reset当 run 的值较前一行发生变化时，排名会回到 1 吗？

架构、数据、SQL 和输出可在SQL小提琴 http://sqlfiddle.com/#!15/10ff1/1.

您可以使用窗口函数来做到这一点。

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;

关键的观察是“按顺序运行”具有此属性：如果您按日期枚举（每个玩家）的行，并按日期枚举每个玩家和运行的行，那么当运行时差异是恒定的都是一样的并且是有序的。这形成了一个组，您可以使用该组进行聚合来识别您想要的玩家。

Here http://sqlfiddle.com/#!15/10ff1/39是 SQL Fiddle。