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

Question

我有以下数据按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已经出现3次，因此在2014-04-19进行的4次针对Player
1的排名预计为1，但排名为4）。同样，在2014-04-19上，玩家2的23奔跑有望获得等级1，但获得排名5，因为已经有4次出现此玩家23奔跑。

当跑步次数的值从上一行改变时，如何将排名 重置 为1？

模式，数据，SQL和输出可在 SQLFiddle 上
获得 。

Answer 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;

关键的观察结果是“按顺序运行”具有此属性：如果按日期枚举（针对每个玩家）行，并且按日期枚举每个玩家和行的行，那么当运行时差异是恒定的都是一样的，井井有条。这形成了一个组，您可以使用该组进行汇总以标识所需的播放器。

这是SQL Fiddle。