Xpoint
   [напомнить пароль]

Выделение непрерывных последовательностей

Метки: [без меток]
2007-09-24 18:28:58 [обр] Thirteensmay(3/157)[досье]

Есть набор:

 id   section      date
---- --------- ------------
 1       1      01.01.2007
 2       1      02.01.2007
 3       2      03.01.2007
 4       2      04.01.2007
 5       2      05.01.2007
 6       3      06.01.2007
 7       2      07.01.2007
 8       2      08.01.2007
 9       2      10.01.2007
10       2      11.01.2007

Требуется выделить непрерывные последовательности по section и date. Т.е. в результате получить набор записей где для каждой непрерывной секции указан начальный и конечный id. Под непрерывностью понимается строгая последовательность дат, день за днем, в рамках одной секции.

Например по данному набору должно быть:

 section   start   end
--------- ------- -----
    1        1      2
    2        3      5
    3        6      6
    2        7      8
    2        9     10

Я конечно могу наворотить тут пару вложенных циклов, подфункцию проверки "последовательности" даты, временные буферы и десяток ифов, но это работает на большой таблице в одном из 19 ее триггеров и используется в т.ч. с целью рихтовки ошибок ввода, конечно надо бы пересмотреть архитектуру но пока вопрос не в этом. Подозреваю что есть хоть какието специальные средства, встроенные функции типа аналитического SQL и т.п., возможно специфичные Oracle. Подскажите плиз...

спустя 6 часов [обр] GRAy(0/259)[досье]
select sect "section", dat "date",  st "start", nvl(ld,en) "end" 
from (
select tt.sect, tt.dat,
  decode(dlt2, null,id,null) st, 
  decode(dlt,null,tt.id,null) en
  ,lead(id,1,null) over(partition by sect order by dat) ld
from (
select t.*,
  case 
    when lead(t.dat,1,null) over(partition by sect order by dat, sect) - t.dat = 2 
      then to_date(null) 
    else lead(t.dat,1,null) over(partition by sect order by dat,sect)
  end dlt,
  case 
    when t.dat - lag(t.dat,1,null) over(partition by sect order by dat,sect) = 2  
      then to_date(null)
    else lag(t.dat,1,null) over(partition by sect order by dat,sect)
  end dlt2
from tst_tbl t
) tt
where dlt is null or dlt2 is null
)
where en is null or (en is not null and st is not null)
order by dat
Дату оставил только для того чтобы отсортировать так же как у вас в контрольном примере. Т.к. я экспериментировал со своей таблицей - колонки назвал по-своему, чтобы не конфликтовали с зарезерированными словами.
спустя 8 часов [обр] Thirteensmay(3/157)[досье]
Ага, Спасибо. Да возблагодарились ваши труды ;)
буду разбираться...
спустя 59 минут [обр] GRAy(0/259)[досье]
Только один нюанс я вчера упустил - там должно везде быть не =2 а >1 т.к. разрыв в общем случае может быть больше двух дней.
спустя 2 часа 39 минут [обр] Thirteensmay(3/157)[досье]

Если поля id, section, date назвать соответственно id, sect, dat
то:

select sect, last_value(id)
over (partition by sect order by sect) from tst_tbl order by dat

а также если использовать first_value() возвращает почти то что нужно, только непрерывность не учитывает. Почитал теорию, понял что это из-за того что не указано окно, т.е. используется весь набор. Получается что если указать окно - только текущую секцию, то должно быть все ок. Вот только незадача, ненайду синтаксиса как можно указать такое окно, встречаю только ROWS BETWEEN X PRECEDING AND X FOLLOWING но это похоже не то. Возможно ли указать это самое окно ?

спустя 1 час 9 минут [обр] Thirteensmay(3/157)[досье]
Э... понял что окна тут нипричем...
спустя 46 минут [обр] GRAy(0/259)[досье]
Thirteensmay[досье] Окно можно указать только как смещение в количестве строк от текущей при заданных условиях сортировки. Вычислить это смещение по какому-либо критерию в ваших данных я не смог ;) хотя думал долго. Основная проблема в том, что в каждой конкретной строке невозможно вычислить сколько ей предшествует и последует других строк до разрыва в последовательности дат. Мой селект работает достаточно просто на самом деле:
  1. Для начала (в подселекте tt) я вычисляю в каждой секции где проходят границы дырок в датах при помощи lag и lead функций, т.е. у строчек, которые стоят на таком "перескоке" или в начале и конце секции одна из двух функций обязательно выдаст null.
  2. Полученную на предыдущем этапе выборку я фильтрую по признаку наличия null в одной из двух колонок полученных при помощи lag и lead where dlt is null or dlt2 is null
  3. Для того чтобы "схопнуть" полученный результат (ведь в нём будет по две записи на каждый диапазон - начало и конец) записи опять фильтуются по признаку начал диапазонов (т.е. те, у которых en is null) но теперь ещё в дополнительную колонку выбирается следующая запись, которая всегда, в рамках этой секции, будет являтся концом диапазона.
спустя 21 минуту [обр] Thirteensmay(3/157)[досье]
За комментарии отдельное спасибо ;)
Powered by POEM™ Engine Copyright © 2002-2005