Created
December 17, 2020 12:00
-
-
Save nikodemrafalski/ee07ebe6dc31925e3804ddba8416e2fc to your computer and use it in GitHub Desktop.
Aggregate overlapping intervals
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| with input as ( | |
| select * | |
| from ( | |
| values ('1', '2012-01-01' :: timestamp, '2012-01-20':: timestamp), | |
| ('1', '2012-01-09':: timestamp, '2012-01-26':: timestamp), | |
| ('2', '2012-01-02':: timestamp, '2012-01-07':: timestamp) | |
| ) as t(id, date_from, date_to) | |
| ), | |
| date_points as ( | |
| select id, date_from as p | |
| from input | |
| union select id, date_to | |
| from input | |
| ), | |
| inter as ( | |
| select id, tsrange (lag(p) over (partition by id order by p), p) i | |
| from date_points | |
| ), | |
| overlap as ( | |
| select distinct d.id, i as interval | |
| from inter i | |
| cross join input d | |
| where i <@ tsrange(d.date_from, d.date_to) and i.id = d.id | |
| ) | |
| select id, sum(upper(interval) - lower(interval)) from overlap | |
| group by id | |
| order by id | |
Author
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
All credits to https://stackoverflow.com/a/44139381