Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Intervals via MODEL  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10040
Not sure if MODEL solutions for intervals were already posted on this forum. Anyway:

SQL> with t as (
  2              select 1 id from dual
  3             union all
  4              select 3 from dual
  5             union all
  6              select 4 from dual
  7             union all
  8              select 5 from dual
  9             union all
 10              select 7 from dual
 11             union all
 12              select 9 from dual
 13             union all
 14              select 10 from dual
 15            )
 16  select  case int_from
 17            when int_to then '[' || int_from || ']'
 18            else '[' || int_from || ' - ' || int_to || ']'
 19          end int
 20    from  t
 21    model
 22      return updated rows
 23      dimension by(
 24                   row_number() over(order by id) as rn
 25                  )
 26       measures(
 27                id,
 28                0 int_from,
 29                0 int_to
 30               )
 31       rules
 32         iterate (1000000000) until (id[iteration_number+2] is null)
 33         (
 34          int_from[id[iteration_number+1]-iteration_number-1] = case id[iteration_number]+1
 35                                                                  when id[iteration_number+1]
 36                                                                    then int_from[id[iteration_number+1]-iteration_number-1]
 37                                                                     else id[iteration_number+1]
 38                                                                   end,
 39          int_to[id[iteration_number+1]-iteration_number-1] = id[iteration_number+1]
 40         )
 41    order by int_from
 42  /

INT
--------------------------------------------------------------------------------
[1]
[3 - 5]
[7]
[9 - 10]

SQL> 

However, it will not work if there are duplicates. Below is MODEL solution that will work with duplicates:

SQL> with t as (
  2              select 1 id from dual
  3             union all
  4              select 3 from dual
  5             union all
  6              select 4 from dual
  7             union all
  8              select 5 from dual
  9             union all
 10              select 7 from dual
 11             union all
 12              select 9 from dual
 13             union all
 14              select 10 from dual
 15             union all
 16              select 1 id from dual
 17             union all
 18              select 3 from dual
 19             union all
 20              select 4 from dual
 21             union all
 22              select 5 from dual
 23             union all
 24              select 7 from dual
 25             union all
 26              select 9 from dual
 27             union all
 28              select 10 from dual
 29            )
 30  select  case int_from
 31            when int_to then '[' || int_from || ']'
 32            else '[' || int_from || ' - ' || int_to || ']'
 33          end int
 34    from  t
 35    model
 36      return updated rows
 37      dimension by(
 38                   row_number() over(order by id) as rn,
 39                   nvl(id - lag(id) over(order by id),2) gap_lag
 40                  )
 41       measures(
 42                id int_from,
 43                id int_to,
 44                lag(id) over(order by id) prev_id
 45               )
 46       rules
 47         (
 48          int_to[rn,gap_lag > 1] = nvl(min(prev_id)[rn > cv(rn),gap_lag > 1],max(int_from)[any,any])
 49         )
 50    order by int_from
 51  /

INT
--------------------
[1]
[3 - 5]
[7]
[9 - 10]

SQL> 

SY.
29 окт 07, 20:18    [4853092]     Ответить | Цитировать Сообщить модератору
 Re: Intervals via MODEL  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18337
rules iterate (1000000000)
iterate - это инструкция "размножь мне пожалуйста блок rules миллиард раз" - крайне неудачно, на мой взгляд.
Второе решение много лучше независимо от duplicates.
IMHO.
29 окт 07, 20:32    [4853128]     Ответить | Цитировать Сообщить модератору
 Re: Intervals via MODEL  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10040
andrey_anonymous
rules iterate (1000000000)
iterate - это инструкция "размножь мне пожалуйста блок rules миллиард раз" - крайне неудачно, на мой взгляд.
Второе решение много лучше независимо от duplicates.
IMHO.


You missed

until (id[iteration_number+2] is null)

which limits iterations to table row count.

SY.
29 окт 07, 20:38    [4853147]     Ответить | Цитировать Сообщить модератору
 Re: Intervals via MODEL  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10040
SY
You missed

until (id[iteration_number+2] is null)

which limits iterations to table row count.

SY.


I am assuming id is not null. If id is nullable, dimension should include nulls last:

row_number() over(order by id nulls last) as rn

SY.

Сообщение было отредактировано: 29 окт 07, 20:41
29 окт 07, 20:41    [4853152]     Ответить | Цитировать Сообщить модератору
 Re: Intervals via MODEL  [new]
Elic
Member

Откуда:
Сообщений: 29976
SY
Not sure if MODEL solutions for intervals were already posted on this forum. Anyway:
Тоже не уверен, но STFF Пятничная задачка: объединение точек в интервалы + вывод одной строкой
30 окт 07, 08:54    [4853816]     Ответить | Цитировать Сообщить модератору
 Re: Intervals via MODEL  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18337
SY
andrey_anonymous
rules iterate (1000000000)
iterate - это инструкция "размножь мне пожалуйста блок rules миллиард раз" - крайне неудачно, на мой взгляд.
Второе решение много лучше независимо от duplicates.
IMHO.
You missed
until (id[iteration_number+2] is null)

Да нет, не пропустил.
Фокус в том, что, если я не ошибаюсь, iterate - это не цикл. Это именно размножение конструкции rules.
Статическое.
30 окт 07, 10:01    [4854031]     Ответить | Цитировать Сообщить модератору
 Re: Intervals via MODEL  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10040
andrey_anonymous

Фокус в том, что, если я не ошибаюсь, iterate - это не цикл. Это именно размножение конструкции rules.
Статическое.


Well, I am no expert in MODELs, but I did not get such impression. According to docs:

Using the ITERATE option of the MODEL clause, you can evaluate rules iteratively for a certain number of times, which you can specify as an argument to the ITERATE clause. ITERATE can be specified only for SEQUENTIAL ORDER models and such models are referred to as iterative models.
.
.
.
In some cases, you may want the termination condition to be based on the change, across iterations, in value of a cell. Oracle provides a mechanism to specify such conditions in that it enables you to access cell values as they existed before and after the current iteration in the UNTIL condition. Oracle's PREVIOUS function takes a single cell reference as an argument and returns the measure value of the cell as it existed after the previous iteration. You can also access the current iteration number by using the system variable ITERATION_NUMBER, which starts at value 0 and is incremented after each iteration. By using PREVIOUS and ITERATION_NUMBER, you can construct complex termination conditions.



So to me it looks like a a loop. And I am not sure exactly what you mean by "размножение конструкции rules"? If you mean code behind rules is statically rereated 1000000000 times, I would doubt it - it would run out of memory, and event if it would not, elapsed time would be quite noticeable.

SY.
30 окт 07, 15:01    [4855958]     Ответить | Цитировать Сообщить модератору
 Re: Intervals via MODEL  [new]
Velaskez
Member

Откуда: Киев
Сообщений: 26
[/quot]
Да нет, не пропустил.
Фокус в том, что, если я не ошибаюсь, iterate - это не цикл. Это именно размножение конструкции rules.
Статическое.[/quot]

Похоже здесь вы ошибаетесь. Это - цикл. Хотя я тоже не эксперт в MODEL :)
30 окт 07, 15:24    [4856085]     Ответить | Цитировать Сообщить модератору
 Re: Intervals via MODEL  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18337
Velaskez
Похоже здесь вы ошибаетесь. Это - цикл. Хотя я тоже не эксперт в MODEL :)

Одна из тех вещей, где буду рад оказаться неправ.
К сожалению, не могу вспомнить, откуда вынес такую идею, однако помню с чего начались изыскания - с неудачи при попытке bind-ить количество итераций.
Тогда (более года тому, подробности забылись, осталось лишь убеждение) пришел к заключению, что iterate приводит к размножению rules.
Если неправ - хорошо :)
30 окт 07, 15:51    [4856249]     Ответить | Цитировать Сообщить модератору
 Re: Intervals via MODEL  [new]
Сергей Дорошенко
Member

Откуда: киев
Сообщений: 224
andrey_anonymous
Velaskez
Похоже здесь вы ошибаетесь. Это - цикл. Хотя я тоже не эксперт в MODEL :)

Одна из тех вещей, где буду рад оказаться неправ.
К сожалению, не могу вспомнить, откуда вынес такую идею, однако помню с чего начались изыскания - с неудачи при попытке bind-ить количество итераций.
Тогда (более года тому, подробности забылись, осталось лишь убеждение) пришел к заключению, что iterate приводит к размножению rules.
Если неправ - хорошо :)

да, тоже столкнулся с тем, что bind для iterations не проходит. там нужно указывать точное количество итераций :(
поэтому в model от iterations пришлость отказаться.
хотя я тоже не эксперт :)
30 окт 07, 16:15    [4856413]     Ответить | Цитировать Сообщить модератору
 Re: Intervals via MODEL  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10040
Сергей Дорошенко

поэтому в model от iterations пришлость отказаться.
хотя я тоже не эксперт :)


Why отказаться. Yes, you can not bind to model clause directly but you can do something like:

measures(
         .
         .
         .
         :iteration_count - 1 as max_iteration_number
        )
rules
  iterations(1000000000) until(iteration_number = max_iteration_number[1])
.
.
.

SY.
30 окт 07, 18:03    [4857259]     Ответить | Цитировать Сообщить модератору
 Re: Intervals via MODEL  [new]
Сергей Дорошенко
Member

Откуда: киев
Сообщений: 224
SY
Сергей Дорошенко

поэтому в model от iterations пришлость отказаться.
хотя я тоже не эксперт :)


Why отказаться. Yes, you can not bind to model clause directly but you can do something like:

measures(
         .
         .
         .
         :iteration_count - 1 as max_iteration_number
        )
rules
  iterations(1000000000) until(iteration_number = max_iteration_number[1])
.
.
.

SY.

тогда еще не допер :), а потом реализовал что-то наподобие этого, но без итераций
30 окт 07, 18:17    [4857340]     Ответить | Цитировать Сообщить модератору
 Re: Intervals via MODEL  [new]
Volder
Member

Откуда: Москва
Сообщений: 474
не разбирался, лучше / хуже.
просто как альтернативный вариант:

SQL> with t as (
  2                select 1 id from dual
  3               union all
  4                select 3 from dual
  5               union all
  6                select 4 from dual
  7               union all
  8                select 5 from dual
  9               union all
 10                select 7 from dual
 11               union all
 12                select 9 from dual
 13               union all
 14                select 10 from dual
 15               union all
 16                select 1 id from dual
 17               union all
 18                select 3 from dual
 19               union all
 20                select 4 from dual
 21               union all
 22                select 5 from dual
 23               union all
 24                select 7 from dual
 25               union all
 26                select 9 from dual
 27               union all
 28                select 10 from dual
 29              )
 30              --
 31              select max(decode(int,p||'-'||p,p,int)) int_final from
 32              (select int, regexp_substr(int,'^\d+') p from t
 33               model
 34                dimension by (row_number() over (order by id) rn)
 35                measures (id, cast(id as varchar2(10)) int)
 36                 rules(int[any] order by rn = case when id[CV()-1] is null or id[CV()]-id[CV()-1]>1  then int[CV()]
 37                                                   when id[CV()+1] is null or (id[CV()+1]-id[CV()]>1) then int[CV()-1]||'-'||int[CV()]
 38                                                   else int[CV()-1]
 39                                              end))
 40               group by p
 41  /

INT_FINAL
----------
1
3-5
7
9-10

SQL> 
31 окт 07, 11:17    [4859109]     Ответить | Цитировать Сообщить модератору
 Re: Intervals via MODEL  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10040
Volder
не разбирался, лучше / хуже.
просто как альтернативный вариант:


Volder, GROUP BY solution is simple enough without MODEL:

SQL> with t as (
  2              select 1 id from dual
  3             union all
  4              select 3 from dual
  5             union all
  6              select 4 from dual
  7             union all
  8              select 5 from dual
  9             union all
 10              select 7 from dual
 11             union all
 12              select 9 from dual
 13             union all
 14              select 10 from dual
 15             union all
 16              select 1 id from dual
 17             union all
 18              select 3 from dual
 19             union all
 20              select 4 from dual
 21             union all
 22              select 5 from dual
 23             union all
 24              select 7 from dual
 25             union all
 26              select 9 from dual
 27             union all
 28              select 10 from dual
 29            )
 30  select  case min(id) when max(id) then '[' || min(id) || ']' else '[' || min(id) || ' - ' || max(id) || ']' end int
 31    from  (
 32           select  id,
 33                   dense_rank() over(order by id) drnk
 34             from  t
 35          )
 36    group by id - drnk
 37    order by min(id)
 38  /

INT
--------------------------------------------------------------------------------
[1]
[3 - 5]
[7]
[9 - 10]

I was trying MODEL as altrernative to GROUP BY.

SY.
31 окт 07, 15:46    [4861008]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить