collapse.sql

WITH 
	-- схлопываем интервалы в максимально длинные непрерывные
	-- так чтобы из такого:
	--
	--  id  | aircraft_id | from_date  |  to_date   |
	-- -----+-------------+------------+------------+
	--  793 |         145 | 2017-06-01 | 2017-09-30 |
	--  841 |         145 | 2017-09-30 | 2017-12-31 |
	--  842 |         145 | 2017-10-03 | 2017-12-31 |
	--  794 |         145 | 2018-01-01 | ¤          |
	-- (4 rows)
	--
	-- получить вот такое:
	--
	--     min     |    max     
	-- ------------+------------
	--  2017-06-01 | 2017-12-31
	--  2018-01-01 | ¤
	-- (2 rows)

	aircraft_audit_intervals_steps as (
		SELECT *, lag(to_date) OVER (ORDER BY from_date) < from_date OR NULL AS step
		FROM aircraft_audit_intervals
		WHERE aircraft_id=145
	),
	aircraft_audit_intervals_groups AS (
	   SELECT *, count(step) OVER (ORDER BY from_date) AS grp
	   FROM aircraft_audit_intervals_steps
	),
	aircraft_audit_intervals_collapsed as (
		SELECT min(from_date) from_date, coalesce(max(to_date), 'infinity'::date) to_date
		FROM aircraft_audit_intervals_groups
		GROUP BY grp
		ORDER BY 1
)

select * from aircraft_audit_intervals_collapsed;