#sql
Вопрос:
У нас есть таблица проектов со следующими данными:
| идентификатор проекта | дата начала | дата окончания |
|---|---|---|
| 101 | 2020-01-01 | 2020-01-05 |
| 102 | 2020-01-06 | 2020-01-08 |
| 103 | 2020-01-09 | 2020-01-13 |
| 104 | 2020-01-16 | 2020-01-17 |
| 105 | 2020-01-18 | 2020-01-20 |
| 106 | 2020-01-24 | 2020-01-28 |
| 107 | 2020-01-29 | 2020-01-30 |
Определенный набор проектов относится к общей эпопее, если дата начала следующего проекта составляет 1 день после даты окончания предыдущего. Например, 101,102 и 103 принадлежат одному эпосу, поскольку дата начала 102-2020-01-06, то есть через 1 день после даты окончания 101-2020-01-05. Аналогично, дата начала 103-2020-01-09, то есть через 1 день после даты окончания 102-2020-01-08
Необходимо получить следующую таблицу результатов
| эпический | Проекты | дата начала | дата окончания |
|---|---|---|---|
| 1 | 101;102;103 | 2020-01-01 | 2020-01-13 |
| 2 | 104;105 | 2020-01-16 | 2020-01-20 |
| 3 | 106;107 | 2020-01-24 | 2020-01-30 |
Я почему-то чувствую, что это проблема пробелов и островов, но я не могу их изолировать. Спасибо за помощь!
Комментарии:
1. Я удалил несогласованные теги базы данных. Пожалуйста, отмечайте только ту базу данных, которую вы используете.
2. Представьте, что у вас есть 3 проекта. 1-й заканчивается в 2020-12-31, 2-й и 3-й начинаются в 2021-01-01. Что такое эпопея — 1,2 или 1,3? И почему?
Ответ №1:
Определенный набор проектов относится к общей эпопее, если дата начала следующего проекта составляет 1 день после даты окончания предыдущего.
Вы можете использовать lag() и накопительную сумму. Ниже приведен синтаксис SQL Server:
select epic,
string_agg(project_id, ',') within group (order by project_id) as project_ids,
min(start_date), max(end_date)
from (select p.*,
sum(case when prev_end_date is null or prev_end_date = dateadd(day, -1, start_date
then 0 else 1
end) over (order by start_date) as epic
from (select p.*,
lag(end_date) over (order by start_date) as prev_end_date
from projects p
) p
) p
group by epic
order by epic;