CODEWITHSUNDEEP
sqlpostgresqldatewindow-functions
Alistair
Alistair

Reputation: 641

Postgres get next row with different value

I have the following table:

+----------+------------------------+-----------+---------------------+
| 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 |
+----------+------------------------+-----------+---------------------+

And I want to get the next effective date for each row. For rows that have the same effective date, I want to grab the next effective date that is greater. In essence, I want to achieve the following result:

+----------+------------------------+-----------+---------------------+---------------------+
| 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 |
+----------+------------------------+-----------+---------------------+---------------------+

I tried using the LEAD function in Postgres but I don't think the PARTITION BY works the way I think it does:

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

Any ideas on how to achieve this? Thanks in advance.

Upvotes: 1

Views: 880

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271191

Starting in Postgres 11, you can also do this using a window function defined with range:

MIN(EffectiveDate) OVER
    (PARTITION BY PersonId
     ORDER BY EffectiveDate
     RANGE BETWEEN INTERVAL '1 SECOND' FOLLOWING AND UNBOUNDED FOLLOWING
    ) AS NextEffectiveDate

In earlier versions, you can use two levels of window functions:

select t.*,
       nullif(max(next_id) over (partition by personid, effectivedate), effectivedate) as next_effectivedate
from (select t.*,
             lead(effectivedate) over (partition by personid order by effectivedate) as next_ed
      from t
     ) t

Upvotes: 1

S-Man
S-Man

Reputation: 23766

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 adds the support for GROUPS within window functions. This is exactly what it was made for. You want to get always the value of the EffectiveDate group. This can be combined with the PersonId partition because you want the GROUPS functionality only within each person. Further reading

Afterwards, the COALESCE() function resets the NULL value of the last GROUPS result into your default value.

Upvotes: 1

Related Questions