Как сгруппировать записи по имени и интервалу дат?
Есть данные в таблице, где есть имена и даты в следующем виде:
Name From To
------ ---------- ----------
Name 1 2021-06-07 2021-07-16
Name 1 2021-07-17 2021-07-23
Name 1 2021-07-24 2021-08-31
Name 2 2020-12-23 2021-02-19
Name 3 2021-01-22 2021-04-18
Name 3 2021-04-27 2021-05-14
Name 3 2021-05-15 2021-05-21
Name 3 2021-05-22 2021-05-28
Name 3 2021-05-29 2021-05-31
Name 4 2021-03-18 2021-04-30
Name 5 2021-07-30 2021-08-13
Name 5 2021-08-14 2021-08-20
Name 5 2021-08-21 2021-08-27
Name 5 2021-08-28 2021-09-03
Name 5 2021-09-04 2021-09-10
Name 5 2021-09-11 2021-09-24
В большинстве случаев для одного имени дата To на единицу больше даты From и является непрерывной, но в некоторых случаях даты прерываются.
Ожидаемый вывод должен выглядеть так:
Name From To
------ ---------- ----------
Name 1 2021-06-07 2021-08-31
Name 2 2020-12-23 2021-02-19
Name 3 2021-01-22 2021-04-18
Name 3 2021-04-27 2021-05-31
Name 4 2021-03-18 2021-04-30
Name 5 2021-07-30 2021-09-24
Пробовал в запросе с min(from) и max(to), но так теряются некоторые записи, например, для Name 3.
Как написать запрос, чтобы получить ожидаемые данные?
Тестовые данные:
with t (name, date_from, date_to) as (
select 'Name 1', date'2021-06-07', date'2021-07-16' from dual union all
select 'Name 1', date'2021-07-17', date'2021-07-23' from dual union all
select 'Name 1', date'2021-07-24', date'2021-08-31' from dual union all
select 'Name 2', date'2020-12-23', date'2021-02-19' from dual union all
select 'Name 3', date'2021-01-22', date'2021-04-18' from dual union all
select 'Name 3', date'2021-04-27', date'2021-05-14' from dual union all
select 'Name 3', date'2021-05-15', date'2021-05-21' from dual union all
select 'Name 3', date'2021-05-22', date'2021-05-28' from dual union all
select 'Name 3', date'2021-05-29', date'2021-05-31' from dual union all
select 'Name 4', date'2021-03-18', date'2021-04-30' from dual union all
select 'Name 5', date'2021-07-30', date'2021-08-13' from dual union all
select 'Name 5', date'2021-08-14', date'2021-08-20' from dual union all
select 'Name 5', date'2021-08-21', date'2021-08-27' from dual union all
select 'Name 5', date'2021-08-28', date'2021-09-03' from dual union all
select 'Name 5', date'2021-09-04', date'2021-09-10' from dual union all
select 'Name 5', date'2021-09-11', date'2021-09-24' from dual
) select * from tСвободный перевод вопроса Grouping data by name and date ranges от участника @Auguster
Ответы (2 шт):
Это можно решить с помощью, так называемого, Tabibitosan метода.
Запрос:
select name, min(date_from) date_from, max(date_to) date_to
from (
select name,
date_from, date_to,
date_to - sum(date_to - date_from + 1) over (
partition by name order by date_from) as grp
from t)
group by name, grp
order by name, date_from
Результат:
NAME DATE_FROM DATE_TO
------ ---------- ----------
Name 1 2021-06-07 2021-08-31
Name 2 2020-12-23 2021-02-19
Name 3 2021-01-22 2021-04-18
Name 3 2021-04-27 2021-05-31
Name 4 2021-03-18 2021-04-30
Name 5 2021-07-30 2021-09-24
6 rows selected.
Начиная с версии 12.1 клауза MATCH_RECOGNIZE позволяет более эффективно решать подобные задачи.
Коротко, как это работает: выражение b* это своего рода регулярное выражение, только b это не одиночный символ, а запись из таблицы, отвечающая условию в define.
Сам запрос:
select name, date_from, date_to
from t
match_recognize (
partition by name
order by date_from
measures
a.date_from as date_from,
last (date_to) as date_to
pattern (a b*)
define b as date_from = prev (date_to) + 1
)
Результат:
NAME DATE_FROM DATE_TO
------ ---------- ----------
Name 1 2021-06-07 2021-08-31
Name 2 2020-12-23 2021-02-19
Name 3 2021-01-22 2021-04-18
Name 3 2021-04-27 2021-05-31
Name 4 2021-03-18 2021-04-30
Name 5 2021-07-30 2021-09-24
6 rows selected.