sql - Postgres : Defining the longest streak (in days) per developer

Question

有一个软件项目，其中包含一些数据，如下表所示。在此表中，我们有开发人员、他在系统中进行某些更改(管理请求)的日期以及这些日期之间的天数差异。

 SeqID  | developer |   MR date     |   dates diff
--------+-----------+---------------+----------------
1       |   Ivy     |   01/02/2012  |   0
2       |   Ivy     |   02/02/2012  |   1
3       |   Ivy     |   03/02/2012  |   1
4       |   Ivy     |   10/02/2012  |   7
5       |   Ivy     |   13/02/2012  |   3   
6       |   Ivy     |   14/02/2012  |   1
1       |   Ken     |   17/02/2012  |   0
2       |   Ken     |   20/02/2012  |   3
3       |   Ken     |   22/02/2012  |   2
4       |   Ken     |   23/02/2012  |   1
5       |   Ken     |   24/02/2012  |   1
6       |   Ken     |   25/02/2012  |   1
7       |   Ken     |   01/03/2012  |   4
8       |   Ken     |   05/03/2012  |   4
1       |   Bob     |   19/02/2012  |   0
2       |   Bob     |   23/02/2012  |   4
3       |   Bob     |   01/03/2012  |   6
4       |   Bob     |   02/03/2012  |   1
5       |   Bob     |   03/03/2012  |   1
6       |   Bob     |   05/03/2012  |   2

我想知道的是最长的连续变化是什么(连续变化最多相差 1 天)。与我们在每个开发人员的 github 统计数据中所拥有的非常相似。所以结果表会是这样的:

developer   |   longest streak  
------------+------------------------   
    Ivy     |   2   
    Ken     |   3   
    Bob     |   2   

我尝试的解决方案是计算开发人员使用 diff date = 1 分组的行数。但这不会返回想要的结果。
根据结果​​表，计数应该像这样完成: Dev = Ivy ;最长的连续记录 = 01/02/2012 到 03/02/2012 = 2 天，等等。
你们中的一些人可以帮助我解决这个问题吗？谢谢，

Answer 1

有一个技巧可以做到这一点。如果您从日期中减去递增的数字序列，那么对于连续的日期，它们将保持不变。然后我们可以使用它为每个开发人员定义组。

select developer, max(numdays) as maxseq
from (select developer, grp, min(MRDate) as MR_start, max(MRDate) as MR_end,
             count(distinct MRDate) as numdays
      from (select t.*,
                  (MRDate - dense_rank() over (partition by developer order by date)) as grp
            from t
           ) t
      group by developer, grp
     ) t
group by developer;

如果您知道每个日期最多有一条记录，那么您可以使用 row_number() 而不是 dense_rank() 和 count(*) 而不是 count(distinct MRDate)。