Postgres：定义每个开发人员最长的连胜（以天为单位）

本文介绍了Postgres：定义每个开发人员最长的连胜（以天为单位）的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

There is a software project with some data as exemplified in the table below. In this table we have the developer, the dates he did some changes (management requests) in the system and the difference between each one of these dates in days.

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统计中提供的内容非常相似。因此，结果表如下所示：

What I want to know is what is the longest streak (consecutive changes done in max 1 day of difference). Something very similar from what we have in github statistics per developer. So the resultant table would be like:

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

我尝试的解决方案是计算差异日期中有多少行= 1按开发人员分组。但这不会返回所需的结果。 根据结果表，计数应如下所示：Dev = Ivy;最长连胜= 2012年1月2日至2012年3月2日= 2天，等等。 你们中的一些人可以在这件事上为我提供帮助吗？谢谢，

The solution I tried was to count how many lines we have with diff date = 1 grouping by developer. But this does not return the wanted result. As per the resultant table the count should be done like: Dev = Ivy ; longest streak = 01/02/2012 to 03/02/2012 = 2 days , etc. Can some of you assist me on this matter? Thanks,

推荐答案

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

There is a trick to doing this. If you subtract an increasing sequence of numbers from the dates, then they will be constant for dates that are sequential. We can then use this for defining groups for each developer.

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（不同的MRDate）。

If you know there is at most one record per date, then you can use row_number() instead of dense_rank() and count(*) instead of count(distinct MRDate).