sql - Postgres获取具有不同值的下一行

我有下表：

+----------+------------------------+-----------+---------------------+
| PersonId |          Role          |  TeamId   |    EffectiveDate    |
+----------+------------------------+-----------+---------------------+
|  0001813 | admin                  | 005aba1ec | 2019-05-01 00:00:00 |
|  0001813 | scrum master           | 005aba1ec | 2019-05-01 00:00:00 |
|  0001813 | team captain           | 005aba1ec | 2019-05-01 00:00:00 |
|  0001813 | admin                  | 005aba1ec | 2019-06-01 00:00:00 |
|  0001813 | scrum master           | 005aba1ec | 2019-06-01 00:00:00 |
|  0001813 | team captain           | 005aba1ec | 2019-06-01 00:00:00 |
|  0001813 | delivery lead          | 005aba1ec | 2019-06-01 00:00:00 |
|  0002817 | product lead           | 007aba338 | 2019-07-01 00:00:00 |
|  0002817 | finance partner        | 007aba338 | 2019-07-01 00:00:00 |
|  0002817 | individual contributor | 007aba338 | 2019-07-01 00:00:00 |
|  0002817 | product lead           | 007aba338 | 2019-08-01 00:00:00 |
|  0002817 | finance partner        | 007aba338 | 2019-08-01 00:00:00 |
|  0002817 | individual contributor | 007aba338 | 2019-08-01 00:00:00 |
|  0002817 | admin                  | 007aba338 | 2019-08-01 00:00:00 |
+----------+------------------------+-----------+---------------------+

我想知道每一行的下一个生效日期。对于具有相同生效日期的行，我想获取下一个更大的生效日期。从本质上讲，我希望达到以下结果：

+----------+------------------------+-----------+---------------------+---------------------+
| PersonId |          Role          |  TeamId   |    EffectiveDate    |  NextEffectiveDate  |
+----------+------------------------+-----------+---------------------+---------------------+
|  0001813 | admin                  | 005aba1ec | 2019-05-01 00:00:00 | 2019-06-01 00:00:00 |
|  0001813 | scrum master           | 005aba1ec | 2019-05-01 00:00:00 | 2019-06-01 00:00:00 |
|  0001813 | team captain           | 005aba1ec | 2019-05-01 00:00:00 | 2019-06-01 00:00:00 |
|  0001813 | admin                  | 005aba1ec | 2019-06-01 00:00:00 | 9999-12-31 23:59:59 |
|  0001813 | scrum master           | 005aba1ec | 2019-06-01 00:00:00 | 9999-12-31 23:59:59 |
|  0001813 | team captain           | 005aba1ec | 2019-06-01 00:00:00 | 9999-12-31 23:59:59 |
|  0001813 | delivery lead          | 005aba1ec | 2019-06-01 00:00:00 | 9999-12-31 23:59:59 |
|  0002817 | product lead           | 007aba338 | 2019-07-01 00:00:00 | 2019-08-01 00:00:00 |
|  0002817 | finance partner        | 007aba338 | 2019-07-01 00:00:00 | 2019-08-01 00:00:00 |
|  0002817 | individual contributor | 007aba338 | 2019-07-01 00:00:00 | 2019-08-01 00:00:00 |
|  0002817 | product lead           | 007aba338 | 2019-08-01 00:00:00 | 9999-12-31 23:59:59 |
|  0002817 | finance partner        | 007aba338 | 2019-08-01 00:00:00 | 9999-12-31 23:59:59 |
|  0002817 | individual contributor | 007aba338 | 2019-08-01 00:00:00 | 9999-12-31 23:59:59 |
|  0002817 | admin                  | 007aba338 | 2019-08-01 00:00:00 | 9999-12-31 23:59:59 |
+----------+------------------------+-----------+---------------------+---------------------+

我尝试在Postgres中使用LEAD函数，但我认为PARTITION BY的工作方式与我认为的不同：

LEAD(EffectiveDate) OVER (PARTITION BY EffectiveDate ORDER BY EffectiveDate) AS NextEffectiveDate

关于如何实现这个目标有什么想法吗？提前谢谢。

最佳答案

demo:db<>fiddle

SELECT
    *,
    COALESCE (
        MAX("EffectiveDate") OVER
             (PARTITION BY "PersonId" ORDER BY "EffectiveDate" GROUPS BETWEEN 1 FOLLOWING AND 1 FOLLOWING)
        ,
        '9999-12-31 23:59:59'
    )
FROM mytable

PostgreSQL 11增加了对窗口内函数的支持。这正是它的目的。您希望始终获得GROUPS组的值。这可以与EffectiveDate分区结合使用，因为您只希望每个人都具有PersonId功能。Further reading
然后，GROUPS函数将最后一个COALESCE()结果的空值重置为默认值。

关于sql - Postgres获取具有不同值的下一行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/57372404/