#oracle #date #range #aggregate
#Oracle #Дата #диапазон #агрегировать
Вопрос:
Мне нужно агрегировать диапазоны дат с учетом интервалов не более 2 дней для каждого идентификатора. Любая помощь была бы высоко оценена
create table tt ( id int, startdate date, stopdate date);
Insert into TT values (1,'24/05/2010', '29/05/2010');
Insert into TT values (1,'30/05/2010', '22/06/2010');
Insert into TT values (10,'26/06/2012', '28/06/2012');
Insert into TT values (10,'29/06/2012', '30/06/2012');
Insert into TT values (10,'01/07/2012', '30/07/2012');
Insert into TT values (10,'03/08/2012', '30/12/2012');
insert into TT values (90,'08/03/2002', '16/03/2002');
insert into TT values (90,'31/01/2002', '15/02/2002');
insert into TT values (90,'15/02/2002', '28/02/2002');
insert into TT values (90,'31/01/2002', '15/02/2004');
insert into TT values (90,'15/02/2004', '15/04/2004');
insert into TT values (90,'01/03/2002', '07/03/2002');
ожидаемый результат будет:
1 24/05/2010 22/06/2010
10 26/06/2012 30/07/2012
10 03/08/2012 30/12/2012
90 31/01/2002 15/04/2004
Комментарии:
1. не могли бы вы, пожалуйста, добавить ожидаемый результат также к вопросу. ?
Ответ №1:
Если вы используете 12c, вы можете использовать одну из моих любимых функций SQL: сопоставление с шаблоном (match_recognize).
Для этого вам нужно определить переменную шаблона. Здесь вы проверите, что дата начала текущей строки находится в пределах двух дней от даты окончания для предыдущей строки. Который:
startdate <= prev ( stopdate ) 2
Шаблон, который вы ищете, — это любая строка, за которой следует ноль или более строк, соответствующих этому критерию.
Таким образом, у вас есть «всегда истинная» strt
переменная, за которой следует * (квантификатор регулярного выражения с нулевым значением или более) вхождений переменной within2:
( strt within2* )
Я предполагаю, что вам также нужно разделить диапазоны по идентификатору. Итак, я добавил раздел by для этого.
Соедините все это вместе, и вы получите:
select *
from tt match_recognize (
partition by id
order by startdate, stopdate
measures
first ( startdate ) startdate,
last ( stopdate ) stopdate
pattern ( strt within2* )
define
within2 as startdate <= prev ( stopdate ) 2
);
ID STARTDATE STOPDATE
1 24/05/2010 22/06/2010
10 26/06/2012 30/07/2012
10 03/08/2012 30/12/2012
Если вы хотите узнать больше об этом, вы можете найти несколько примеров match_recognize здесь .
Комментарии:
1.Однако это не сработает для следующего подмножества, где stopdate = startdate = null, см. Пример:
create table tt (id VARCHAR(8), startdate date, stopdate date);
insert into TT values ('ALH002','01/09/2010 00:00', '21/02/2011 00:00');
insert into TT values ('ALH002','21/02/2011 00:00', '06/06/2011 00:00');
insert into TT values ('ALH002','06/06/2011 00:00', '');
это должно привести к следующей строке:ALH002 01/09/2010 00:00:00 06/06/2011 00:00:00
2. Это всего лишь вопрос обновления stopdate в предложении measures, чтобы возвращать желаемое значение; либо
max ( stopdate )
, либоlast (nvl ( stop, start ))
3. Спасибо! это решит эту конкретную проблему. Однако для диапазонов, содержащихся в другой строке, скрипт выдаст неверный вывод. Я обновил приведенный выше запрос
4. Я не уверен, в чем здесь проблема. Выполнение ответа со значениями, которые вы добавили к вопросу, дает запрошенный вами результат.
5. моя ошибка в том, что я исправил входные данные, чтобы отразить проблему, описанную ранее