Объединение диапазонов одним запросом с моделькой


или Забавы с модельками - 2

На очередное коротенькое баловство вдохновил этот вопрос
Скрипт обновлен, т.к. предыдущий был не оптимальным. Данный вариант лучший среди вариантов с решениями с моделью:
select a, max(b) from (     select a,b     from t     model          return all rows          dimension by (row_number() over(order by a,b) rn)          measures(a,                  greatest(b,                           nvl(max(b) over(order by a asc,b desc rows between unbounded preceding and 1 preceding),b)                  ) b)          rules (             a[any]= case when a[cv()] <= b[cv()-1]+1                      then a[cv()-1]                      else a[cv()]                     end          ) ) group by a;

А теперь наиболее быстрый вариант - вариант на аналитике:
select * from (     select          nvl(sg,lag(sg) over(order by nvl(sg,eg))) sg,          eg     from (          select              a,              b,              case                 when                  a-1>max(b) over(order by a asc,b desc rows between unbounded preceding and 1 preceding)                  or max(b) over(order by a asc,b desc rows between unbounded preceding and 1 preceding) is null                 then a              end sg,              case                 when                  (                  lead(a) over(order by a asc,b desc)                  >                  1+max(b) over(order by a asc,b desc rows between unbounded preceding and current row)                  )                  or lead(a) over(order by a asc,b desc) is null                 then max(b) over(order by a asc,b desc rows between unbounded preceding and current row)              end eg          from t     )     where sg is not null     or eg is not null )where eg is not null

Comments

Отправка комментария