Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: 1 2      [все]
 Задача определения "плавающих" групп  [new]
Energomarket
Member

Откуда:
Сообщений: 4
Доброго времени суток.
Помогите решить задачу с помощью SQL (желательно средствами oracle 10g), которая очень легко решается с помощью обычного цикла и переменной в PL/SQL.

Есть таблица:
with
  m_data as
  (
    select 86 as id, 0 as val from dual union all
    select 10 as id, 3.5 as val from dual union all
    select 104 as id, 3.75 as val from dual union all
    select 99 as id, 4.9 as val from dual union all
    select 97 as id, 5 as val from dual union all
    select 19 as id, 5.67 as val from dual union all
    select 8 as id, 5.83 as val from dual union all
    select 7 as id, 5.83 as val from dual union all
    select 15 as id, 6.25 as val from dual union all
    select 17 as id, 6.25 as val from dual
  )
select id, val from m_data
order by val


Все id необходимо отсортировать по группам в зависимости от значений val.
Разница между значением (поле val) последнего элемента группы и первого элемента группы не должно превышать 0.5.
То есть результирующий запрос должен выдать следующее (res - группа):

id	val	res
-------------------
86	0	1
10	3.5	2
104	3.75	2
99	4.9	3
97	5	3
19	5.67	4
8	5.83	4
7	5.83	4
15	6.25	5
17	6.25	5
27 сен 18, 22:15    [21688572]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 16899
В указанных ограничениях (10g, SQL) можете попробовать модельку:
https://www.oracle.com/technetwork/middleware/bi-foundation/10gr1-twp-bi-dw-sqlmodel-131067.pdf

Если смягчить условия на "чистый SQL" - то оформите pipelined функцией и не морочьте голову.
Если допустить более свежие версии rdbms - то добавится pattern matching

А так - творчество ради творчества - можно и на 9i SQL порешать, поищите на форуме "задачу о рюкзаке", "задачу коммивояжера"... Проникайтесь и Творите :)
27 сен 18, 23:47    [21688615]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
-2-
Member

Откуда:
Сообщений: 14089
select t2.id, t2.val, t1.g res
from (
 select m1.*, level g
 from m_data m1
 connect by val > prior val + 0.5 and (select max(val) from m_data where val < m1.val or val = m1.val and id < m1.id) <= prior val + 0.5
 start with val = 0
) t1, m_data t2 where t2.val between t1.val and t1.val + 0.5
order by t1.g, t2.val, t2.id;
28 сен 18, 02:07    [21688646]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
982183
Member

Откуда: VL
Сообщений: 2482
-2-,

Я Вашего алгоритма честно, не понял.
Но вроде все просто можно сделать.
сначала определяем границы перехода

select id, val, iif(val - (LAG(val, 1, val-1) OVER (order by val))<= 0.5, 0, 1) as  res


А потом любым методом посчитать нарастающий итог.
28 сен 18, 07:12    [21688669]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
982183
Member

Откуда: VL
Сообщений: 2482
тут же не разницу первого элемента предшествующей группы и первого элемента новой группы, а "последнего элемента группы и первого элемента группы"
28 сен 18, 07:15    [21688671]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
982183
Member

Откуда: VL
Сообщений: 2482
Хотя тестовый пример просчитан именно по алгоритму
"Разница между первым элементом предшествующей группы и первым элементом новой группы" <=0.5
28 сен 18, 07:18    [21688672]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
Павел Воронцов
Member

Откуда: Новосибирск
Сообщений: 2351
Блог
Только хардкор, никаких вам аналитик!
with
  m_data as
  (
    select 86 as id, 0 as val from dual union all
    select 10 as id, 3.5 as val from dual union all
    select 104 as id, 3.75 as val from dual union all
    select 99 as id, 4.9 as val from dual union all
    select 97 as id, 5 as val from dual union all
    select 19 as id, 5.67 as val from dual union all
    select 8 as id, 5.83 as val from dual union all
    select 7 as id, 5.83 as val from dual union all
    select 15 as id, 6.25 as val from dual union all
    select 17 as id, 6.25 as val from dual
  ),
a as (select md1.id, md1.val, count(md2.id) as ord from m_data md1 left join m_data md2 on md2.val<=md1.val and md2.id <> md1.id group by md1.id, md1.val),
b as (select a1.id, a1.val, a1.ord, nvl(min(a2.ord),a1.ord) as ord2 from a a1 left join a a2 on a2.ord < a1.ord and a2.val >= a1.val-0.5 group by a1.id, a1.val, a1.ord)
select * from b
order by val

Искомая колонка ord2. Дальше сами
28 сен 18, 08:11    [21688697]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
Павел Воронцов
Member

Откуда: Новосибирск
Сообщений: 2351
Блог
Павел Воронцов,

Не для всех данных работает корректно. Но разберитесь сами, там просто вроде.
28 сен 18, 08:28    [21688702]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
-2-
Member

Откуда:
Сообщений: 14089
Павел Воронцов
разберитесь сами
reset running total в sql решается через рекурсию connect by/with, model или match_recognize.
28 сен 18, 08:45    [21688712]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
Energomarket
Member

Откуда:
Сообщений: 4
-2-,

Отличный ответ.
Немного изменил запрос что-бы работал с любым первым элементом (не обязательно ноль).
with
  m_data as
  (
    select 86 as id, 0 as val from dual union all
    select 10 as id, 3.5 as val from dual union all
    select 104 as id, 3.75 as val from dual union all
    select 99 as id, 4.9 as val from dual union all
    select 97 as id, 5 as val from dual union all
    select 19 as id, 5.67 as val from dual union all
    select 8 as id, 5.83 as val from dual union all
    select 7 as id, 5.83 as val from dual union all
    select 15 as id, 6.25 as val from dual union all
    select 17 as id, 6.25 as val from dual union all
    select 170 as id, 6.31 as val from dual union all
    select 171 as id, 6.51 as val from dual union all
    select 2 as id, 6.89 as val from dual
  )
select t2.id, t2.val, t1.g res
from (
 select m1.*, level g
 from m_data m1
 connect by val > prior val + 0.5 and (select max(val) from m_data where val < m1.val or (val = m1.val and id < m1.id)) <= prior val + 0.5
 start with val IN (select min(val) from m_data)
) t1, m_data t2 where t2.val between t1.val and t1.val + 0.5
order by t1.g, t2.val, t2.id;


Немного поясню суть Вашей идеи.
Иерархическим подзапросом находятся все первые элементы групп (соответственно проставляется номер группы):
 select m1.*, level g
 from m_data m1
 connect by val > prior val + 0.5 and (select max(val) from m_data where val < m1.val or (val = m1.val and id < m1.id)) <= prior val + 0.5
 start with val IN (select min(val) from m_data)

id	val	g
-------------
86	0	1
10	3,5	2
99	4,9	3
19	5,67	4
15	6,25	5
2	6,89	6
28 сен 18, 09:52    [21688776]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
Energomarket
Member

Откуда:
Сообщений: 4
982183
-2-,

Я Вашего алгоритма честно, не понял.
Но вроде все просто можно сделать.
сначала определяем границы перехода

select id, val, iif(val - (LAG(val, 1, val-1) OVER (order by val))<= 0.5, 0, 1) as  res


А потом любым методом посчитать нарастающий итог.

Я так и пробовал. Но из-за того что нужно запоминать первый элемент группы (а не предыдущую запись), функция LAG не спасала ситуацию.
with
  m_data as
  (
    select 86 as id, 0 as val from dual union all
    select 10 as id, 3.5 as val from dual union all
    select 104 as id, 3.75 as val from dual union all
    select 99 as id, 4.9 as val from dual union all
    select 97 as id, 5 as val from dual union all
    select 19 as id, 5.67 as val from dual union all
    select 8 as id, 5.83 as val from dual union all
    select 7 as id, 5.83 as val from dual union all
    select 15 as id, 6.25 as val from dual union all
    select 17 as id, 6.25 as val from dual
  )
select id, val,
case when (val - (LAG(val, 1, val-1) OVER (order by val))<= 0.5) then 0 else 1 end as res 
from m_data
order by val;

id	val	res
-------------
86	0	1
10	3,5	1
104	3,75	0
99	4,9	1
97	5	0
19	5,67	1
8	5,83	0
7	5,83	0
15	6,25	0
17	6,25	0

Не определено начало группы 6,25.
28 сен 18, 10:01    [21688781]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
982183
Member

Откуда: VL
Сообщений: 2482
Energomarket
Но из-за того что нужно запоминать первый элемент группы (а не предыдущую запись),

ну так далее я и говорю о том, что в постановке не "первый элемент группы", а "последний"
28 сен 18, 10:25    [21688807]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
982183
Member

Откуда: VL
Сообщений: 2482
Energomarket
Не определено начало группы 6,25.

6,25-5,83=0,42
28 сен 18, 10:27    [21688809]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
Павел Воронцов
Member

Откуда: Новосибирск
Сообщений: 2351
Блог
-2-
Павел Воронцов
разберитесь сами
reset running total в sql решается через рекурсию connect by/with, model или match_recognize.
Я не спорю, но у меня вон решилось. Когда и если голова перестанет болеть, допилю для любого набора данных.

Хотя... Похоже, что без рекурсии не обойтись. Подумаю ещё.

В Вашем решении меня смущает
and id < m1.id
28 сен 18, 10:45    [21688828]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5149
Energomarket,

select * from m_data
match_recognize
(
  order by val
  measures match_number() res
  all rows per match
  pattern(x+)
  define
    x as x.val <= first(x.val) + 1/2
) mr;

       VAL        RES         ID
---------- ---------- ----------
         0          1         86
       3.5          2         10
      3.75          2        104
       4.9          3         99
         5          3         97
      5.67          4         19
      5.83          4          8
      5.83          4          7
      6.25          5         15
      6.25          5         17

10 rows selected.
28 сен 18, 11:08    [21688856]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5149
Павел Воронцов
-2-
пропущено...
reset running total в sql решается через рекурсию connect by/with, model или match_recognize.
Я не спорю, но у меня вон решилось. Когда и если голова перестанет болеть, допилю для любого набора данных.

Хотя... Похоже, что без рекурсии не обойтись. Подумаю ещё.[/src]
Когда голова перестанет болеть, возможно, придет понимание почему никакими SQL-методами, кроме перечисленных -2- задача нерешаема.

PS. Ну можно извратиться еще через XML.
28 сен 18, 11:15    [21688863]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
Energomarket
Member

Откуда:
Сообщений: 4
982183
Energomarket
Не определено начало группы 6,25.

6,25-5,83=0,42

Первый элемент группы 5,67.
6,25-5,67=0,58
28 сен 18, 11:32    [21688882]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
982183
Member

Откуда: VL
Сообщений: 2482
третий раз пишу, что в постановке
автор
Разница между значением (поле val) последнего элемента группы и первого элемента группы не должно превышать 0.5.

и исправлений постановки не было.
28 сен 18, 11:41    [21688899]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
982183
Member

Откуда: VL
Сообщений: 2482
Всё. До меня дошло.
28 сен 18, 11:42    [21688902]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
SkilledJunior
Member

Откуда:
Сообщений: 293
dbms_photoshop
никакими SQL-методами, кроме перечисленных -2- задача нерешаема.


rownum/group by/order by достаточно для решения этой задачи.
28 сен 18, 15:59    [21689245]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5149
SkilledJunior
dbms_photoshop
никакими SQL-методами, кроме перечисленных -2- задача нерешаема.


rownum/group by/order by достаточно для решения этой задачи.
Классно. Ты ж и показать сможешь, да?
28 сен 18, 16:25    [21689279]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 16899
dbms_photoshop
SkilledJunior
пропущено...
rownum/group by/order by достаточно для решения этой задачи.
Классно. Ты ж и показать сможешь, да?

Ну вот, оторвал человека от дел - он теперь пару часов угрохает на заведомую ерунду, а затем наспамит тут десяток datamagic-specific вариантов... в лучшем случае налепит свой вариант connect by/subquery factoring :)
28 сен 18, 16:42    [21689307]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9220
dbms_photoshop
почему никакими SQL-методами, кроме перечисленных -2- задача нерешаема.


-2- вроде "перечислил" всего один метод. andrey_anonymous подкинул еще. Recursive subquery factoring вроде не упоминали. Ну а в 12C как намекнул andrey_anonymous все куда проще:

with
  m_data as
  (
    select 86 as id, 0 as val from dual union all
    select 10 as id, 3.5 as val from dual union all
    select 104 as id, 3.75 as val from dual union all
    select 99 as id, 4.9 as val from dual union all
    select 97 as id, 5 as val from dual union all
    select 19 as id, 5.67 as val from dual union all
    select 8 as id, 5.83 as val from dual union all
    select 7 as id, 5.83 as val from dual union all
    select 15 as id, 6.25 as val from dual union all
    select 17 as id, 6.25 as val from dual
  )
select  id,
        val,
        res
  from  m_data
  match_recognize(
                  order by val
                  measures match_number() as res
                  all rows per match
                  pattern(one_half+)
                  define one_half as val - first(val) <= .5
                 )
order by val
/

        ID        VAL        RES
---------- ---------- ----------
        86          0          1
        10        3.5          2
       104       3.75          2
        99        4.9          3
        97          5          3
        19       5.67          4
         8       5.83          4
         7       5.83          4
        15       6.25          5
        17       6.25          5

10 rows selected.

SQL> 


SY.
28 сен 18, 17:22    [21689359]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5149
SY,

Я еще могу понять когда человек не в состоянии осилить тему.
Но когда это касается одного сообщения на которое он отвечает...

21688712
21688856
28 сен 18, 17:57    [21689400]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5149
andrey_anonymous
наспамит тут десяток datamagic-specific вариантов
Это очень лестная характеристика опытного Жуниора.
Я ставлю на то, что в его случае всё ограничится пустой болтовней.
28 сен 18, 17:58    [21689401]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
SkilledJunior
Member

Откуда:
Сообщений: 293
dbms_photoshop
Классно. Ты ж и показать сможешь, да?

Видишь суслика? Нет? А он есть))
28 сен 18, 21:11    [21689544]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
nullin
Member

Откуда: pullin
Сообщений: 32
SkilledJunior
dbms_photoshop
Классно. Ты ж и показать сможешь, да?

Видишь суслика? Нет? А он есть))

Судя по всему там джойн на таблицу чисел по копеечкам, применительно к данным ТС, но правда, если точность cap далеко за сотые, то...
29 сен 18, 01:40    [21689689]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
Павел Воронцов
Member

Откуда: Новосибирск
Сообщений: 2351
Блог
dbms_photoshop,

Да, сегодня голова не болит и я согласен - только рекурсия.
29 сен 18, 07:30    [21689728]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
SkilledJunior
Member

Откуда:
Сообщений: 293
SkilledJunior
Видишь суслика? Нет? А он есть))


Пятница прошла давно и пятничный суслик убежал, гениев SQL не обнаружено ((

Павел Воронцов
dbms_photoshop,

Да, сегодня голова не болит и я согласен - только рекурсия.


Задача не имеет не итеративных решений, рекурсия кривой метод реализации итераций, нормальный метод озвучил:
andrey_anonymous
оформите pipelined функцией и не морочьте голову.
30 сен 18, 00:09    [21690164]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
SkilledJunior
Member

Откуда:
Сообщений: 293
На вентилятор ...

create or replace package m_test as
  g_val_alt number;
  g_res_alt number;

 function get_res(p_val in number, p_rn in number) return number;
end m_test;
/

create or replace package body m_test as
  g_val number;
  g_res number;

 function get_res(p_val in number, p_rn in number) return number is
 PRAGMA UDF;
 begin
   if p_rn > 1 then
     if p_val > g_val + 0.5 then
       g_val := p_val;
       g_res := g_res + 1;
     end if;
   else 
     g_val := p_val;
     g_res := 1;
   end if;
   return g_res;
 end;
 
end m_test;
/


with
 function get_res_alt(p_val in number, p_rn in number) return number is
 begin
   if p_rn > 1 then
     if p_val > m_test.g_val_alt + 0.5 then
       m_test.g_val_alt := p_val;
       m_test.g_res_alt := m_test.g_res_alt + 1;
     end if;
   else 
     m_test.g_val_alt := p_val;
     m_test.g_res_alt := 1;
   end if;
   return m_test.g_res_alt;
 end;
  m_data as
  (
    select 86 as id, 0 as val from dual union all
    select 10 as id, 3.5 as val from dual union all
    select 104 as id, 3.75 as val from dual union all
    select 99 as id, 4.9 as val from dual union all
    select 97 as id, 5 as val from dual union all
    select 19 as id, 5.67 as val from dual union all
    select 8 as id, 5.83 as val from dual union all
    select 7 as id, 5.83 as val from dual union all
    select 15 as id, 6.25 as val from dual union all
    select 17 as id, 6.25 as val from dual union all
    select 170 as id, 6.31 as val from dual union all
    select 171 as id, 6.51 as val from dual union all
    select 2 as id, 6.89 as val from dual
  )
  select rownum,
         id,
         val,
         m_test.get_res(val, rownum),
         get_res_alt(val, rownum)
  from (select id, val from m_data order by val)
30 сен 18, 01:11    [21690187]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 16899
Energomarket
средствами oracle 10g


SkilledJunior
На вентилятор ...

 PRAGMA UDF;


with
 function get_res_alt(p_val in number, p_rn in number) return number is
 begin
1 окт 18, 15:41    [21691403]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
SkilledJunior
Member

Откуда:
Сообщений: 293
andrey_anonymous,

Без PRAGMA UDF пакетный вариант будет работать и в десятке, больше ничего в таком решении не смущает?
1 окт 18, 22:56    [21691959]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
Elic
Member

Откуда: 1984. Выбраковка финно-угром продолжается. КЯЗ
Сообщений: 28476
SkilledJunior
больше ничего в таком решении не смущает?
SQL сам накажет самовлюблённых гениёв за использование функций с побочными эффектами.
2 окт 18, 07:41    [21692134]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
SkilledJunior
Member

Откуда:
Сообщений: 293
Elic,

Да, для пользовательских функций Oracle не гарантирует ни нужную последовательность ни количество вызовов для каждой строки, хотя сам вполне себе генерирует псевдостолбец rownu, т.е. механизм имеется.
3 окт 18, 23:22    [21694682]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
Elic
Member

Откуда: 1984. Выбраковка финно-угром продолжается. КЯЗ
Сообщений: 28476
SkilledJunior
хотя сам вполне себе генерирует псевдостолбец rownu, т.е. механизм имеется.
Бред.
4 окт 18, 07:53    [21694764]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
-2-
Member

Откуда:
Сообщений: 14089
SkilledJunior
для пользовательских функций
если уж браться за plsql, то за обычный или полиморфный pipeline.
4 окт 18, 08:46    [21694791]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
-2-
Member

Откуда:
Сообщений: 14089
+ PTF
create or replace package reset_running_distance_ptf is
function describe(
  tab  in out dbms_tf.table_t,
  col  in     dbms_tf.columns_t,
  dist number default 1.0
) return dbms_tf.describe_t;
procedure fetch_rows(dist number default 1.0);
end reset_running_distance_ptf;
/

create or replace package body reset_running_distance_ptf is 
function describe(
  tab  in out dbms_tf.table_t,
  col  in     dbms_tf.columns_t,
  dist number default 1.0
) return dbms_tf.describe_t is
  ok boolean;
begin
  if col.count <> 1 then
    raise_application_error(-20001, 'Должна быть указана одна колонка скольжения');
  end if;
  ok := false;
  for i in 1..tab.column.count loop
    if tab.column(i).description.name = col(1) and tab.column(i).description.type = dbms_tf.type_number then
      ok := true;
      tab.column(i).for_read := true;
      exit;
    end if;
  end loop;
  if not ok then
    raise_application_error(-20002, 'Колонка скольжения должна иметь тип НУМБЕР');
  end if;
  --
  return dbms_tf.describe_t(
    new_columns => dbms_tf.columns_new_t(1=>dbms_tf.column_metadata_t(name=>'DISTANCE_GROUP', type=>dbms_tf.type_number)));
end describe; 
procedure fetch_rows(dist number default 1.0) is
  dg_num  number;
  dg_end  number;
  ru_col  dbms_tf.tab_number_t;
  dg_col  dbms_tf.tab_number_t;
begin
  dbms_tf.get_col(1, ru_col);
  dg_num := 0;             dbms_tf.xstore_get('dg_num', dg_num);
  dg_end := ru_col(1) - 1; dbms_tf.xstore_get('dg_end', dg_end);
  for i in 1..ru_col.count loop
      if ru_col(i) > dg_end then
        dg_num := dg_num + 1;       dbms_tf.xstore_set('dg_num', dg_num);
        dg_end := ru_col(i) + dist; dbms_tf.xstore_set('dg_end', dg_end);
      end if;        
      dg_col(i) := dg_num;
  end loop;
  dbms_tf.put_col(1, dg_col);
end fetch_rows;
end reset_running_distance_ptf; 
/

create or replace function reset_running_distance(
  tab table,
  col columns,
  dist number default 1.0
) return table pipelined table polymorphic using reset_running_distance_ptf; 
/
with m_data as (...)
select * from reset_running_distance(m_data order by val, columns(val), 0.5);

ID	VAL	DISTANCE_GROUP
86	0	1
10	3.5	2
104	3.75	2
99	4.9	3
97	5	3
19	5.67	4
8	5.83	4
7	5.83	4
15	6.25	5
17	6.25	5
4 окт 18, 10:55    [21694895]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 16899
-2-
with m_data as (...)
select * from reset_running_distance(m_data order by val, columns(val), 0.5);

Записал, спасибо.
4 окт 18, 18:32    [21695646]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
SkilledJunior
Member

Откуда:
Сообщений: 293
Elic
Бред.
Сформировать rownum можно либо имея доступ к тому же столбцу предыдущей строки, либо сохраняя последний присвоенный номер в переменную, при этом необходимо гарантировать последовательность прохода по выборке и отсутствие увеличения счетчика более одного раза для одной и той же строки. Оба механизма реализуют итеративный инкремент по выборке и подошли бы для решения задачи.

-2-
если уж браться за plsql, то за обычный или полиморфный pipeline.

Да я и не спорю:
SkilledJunior
Задача не имеет не итеративных решений, рекурсия кривой метод реализации итераций, нормальный метод озвучил:
andrey_anonymous
оформите pipelined функцией и не морочьте голову.


Полиморфный метод зачетный, кинул в избранное.
5 окт 18, 14:06    [21696381]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5149
SkilledJunior,

Ты точно понял первые два-три слова отсюда, формирователь?
dbms_photoshop
никакими SQL-методами, кроме перечисленных -2- задача нерешаема


Хорошо хоть сам написал, что Оракл не гарантирует последовательность вызова функций.
Даже лень проверять будет ли устойчив эффект при параллельном выполнении. Слишком тухлый вброс.
5 окт 18, 14:30    [21696417]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
SkilledJunior
Member

Откуда:
Сообщений: 293
dbms_photoshop
Даже лень проверять будет ли устойчив эффект при параллельном выполнении.

Банальный пример, конечное не будет устойчив, другие примеры нарушения последовательности были бы более интересны, а примеры многократного вызова функции для одной и той же строки еще интереснее.

Еще интересный вопрос, что сделает rownum без сортировки и rownum from (select ... order by) с параллельным выполнением запроса.
5 окт 18, 15:25    [21696492]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
-2-
Member

Откуда:
Сообщений: 14089
SkilledJunior
другие примеры нарушения последовательности
  select rownum,
         id,
         row_number() over(order by id) r_id,
         val,
         m_test.get_res(val, rownum) g1,
         get_res_alt(val, rownum) g2
  from (select id, val from m_data order by val)
  order by val
;

    ROWNUM         ID       R_ID        VAL         G1         G2
---------- ---------- ---------- ---------- ---------- ----------
         1         86          8          0          1          1
         2         10          4        3.5          4          4
         3        104         11       3.75          2          2
         4         99         10        4.9          2          2
         5         97          9          5          2          2
         6         19          7       5.67          4          4
         7          8          3       5.83          4          4
         8          7          2       5.83          4          4
         9         15          5       6.25          4          4
        10         17          6       6.25          4          4
        11        170         12       6.31          3          3
        12        171         13       6.51          3          3
        13          2          1       6.89          4          4
5 окт 18, 16:02    [21696553]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5149
SkilledJunior
примеры многократного вызова функции для одной и той же строки еще интереснее.
Вспоминаются примеры недовызова. 16679179 (хотя rownum это как раз "лечит")
SkilledJunior
Еще интересный вопрос, что сделает rownum без сортировки и rownum from (select ... order by) с параллельным выполнением запроса.
При параллельности ты не управляешь тем какие порции данных идут в какой slave не говоря уже про то, что любые попытки использовать stateful пакеты бессмысленны.
5 окт 18, 16:04    [21696556]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 16899
dbms_photoshop
При параллельности ты не управляешь тем какие порции данных идут в какой slave не говоря уже про то, что любые попытки использовать stateful пакеты бессмысленны.

Эээ... Вообще-то это, скажем так, не совсем правда по обоим пунктам.
По крайней мере, я и управлял распределением по слейвам, и успешно использовал statefull...
Но не в контексте решения юниора, ессно :)
5 окт 18, 16:35    [21696593]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
SkilledJunior
Member

Откуда:
Сообщений: 293
-2-,

Хороший пример, однако rownum Oracle формирует правильно))

dbms_photoshop
Вспоминаются примеры недовызова. 16679179 (хотя rownum это как раз "лечит")

Ох уж этот экономный Oracle, но все же в примере одна и та же функция с одними и теми же параметрами в разных столбцах, но не в одном столбце разных строк.

SkilledJunior
При параллельности ты не управляешь тем какие порции данных идут в какой slave не говоря уже про то, что любые попытки использовать stateful пакеты бессмысленны.

Вопрос был несколько иной, даст ли rownum и rownum на отсортированной выборке использовать параллельность.
5 окт 18, 16:41    [21696599]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 9220
dbms_photoshop
При параллельности ты не управляешь тем какие порции данных идут в какой slave


Что говорит лишь о том что только master мoжет вычислять аналитику. Вернее только master мoжет производить сортировки указанные в аналитике. А можeт ли он потом опять наплодить slaves? Мне пока не удалось создать такой план но это ничего не значит. Это может появится в новых версиях а может я просто плохой повар. Но в любом случае я с тобой полностью согласен только ORDER BY гарантирует порядок строк.

SY.
5 окт 18, 16:45    [21696606]     Ответить | Цитировать Сообщить модератору
 Re: Задача определения "плавающих" групп  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5149
andrey_anonymous
dbms_photoshop
При параллельности ты не управляешь тем какие порции данных идут в какой slave не говоря уже про то, что любые попытки использовать stateful пакеты бессмысленны.

Эээ... Вообще-то это, скажем так, не совсем правда по обоим пунктам.
По крайней мере, я и управлял распределением по слейвам, и успешно использовал statefull...
Но не в контексте решения юниора, ессно :)
Речь шла про решение Жуниора single row function, а не про любителей явно указывать как распределять данные в пайплайне или еще чего.
5 окт 18, 17:05    [21696635]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: 1 2      [все]
Все форумы / Oracle Ответить