Трюк дня. Агрегирование записей в PostgreSQL. Решение



WITH projects (task_id, start_date, end_date)

AS (VALUES

(1, CAST('10-01-20' AS date), CAST('10-02-20' AS date)),

(2, CAST('10-02-20' AS date), CAST('10-03-20' AS date)),

(3, CAST('10-03-20' AS date), CAST('10-04-20' AS date)),

(4, CAST('10-13-20' AS date), CAST('10-14-20' AS date)),

(5, CAST('10-14-20' AS date), CAST('10-15-20' AS date)),

(6, CAST('10-28-20' AS date), CAST('10-29-20' AS date)),

(7, CAST('10-30-20' AS date), CAST('10-31-20' AS date))),



-- получим такие даты начала, которых не существует в колонке даты окончания (это 'настоящие' даты начала проекта)



t1 AS (

SELECT start_date

FROM projects

WHERE start_date NOT IN (SELECT end_date FROM projects) ),



-- получим такие даты окончания, которые не существуют в колонке дат начала (это 'настоящие' даты окончания проекта)

t2 AS (

SELECT end_date

FROM projects

WHERE end_date NOT IN (SELECT start_date FROM projects) ),



--отфильтруем допустимые пары начало-окончание (начало < окончание), затем найдем правильную дату окончания для каждой даты начала (минимальная дата окончания, поскольку нет пересекающихся проектов)



t3 AS (

SELECT

start_date,

MIN(end_date) AS end_date

FROM t1, t2

WHERE start_date < end_date

GROUP BY 1 )

SELECT

*,

end_date - start_date AS project_duration

FROM t3

ORDER BY 3, 1



#tips