ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / reset running total / Задача определения "плавающих" групп
47 сообщений из 47, показаны все 2 страниц
  все  
reset running total / Задача определения "плавающих" групп
    #39709554
Energomarket
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Energomarket
Гость
Доброго времени суток.
Помогите решить задачу с помощью SQL (желательно средствами oracle 10g), которая очень легко решается с помощью обычного цикла и переменной в PL/SQL.

Есть таблица:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
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 - группа):

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
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
...
Рейтинг: 0 / 0
27.09.2018, 22:15
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39709581
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
В указанных ограничениях (10g, SQL) можете попробовать модельку:
https://www.oracle.com/technetwork/middleware/bi-foundation/10gr1-twp-bi-dw-sqlmodel-131067.pdf

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

А так - творчество ради творчества - можно и на 9i SQL порешать, поищите на форуме "задачу о рюкзаке", "задачу коммивояжера"... Проникайтесь и Творите :)
...
Рейтинг: 0 / 0
27.09.2018, 23:47
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39709597
Фотография -2-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
-2-
Участник
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
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;
...
Рейтинг: 0 / 0
28.09.2018, 02:07
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39709614
982183
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
982183
Участник
-2-,

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

Код: sql
1.
select id, val, iif(val - (LAG(val, 1, val-1) OVER (order by val))<= 0.5, 0, 1) as  res



А потом любым методом посчитать нарастающий итог.
...
Рейтинг: 0 / 0
28.09.2018, 07:12
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39709615
982183
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
982183
Участник
тут же не разницу первого элемента предшествующей группы и первого элемента новой группы, а "последнего элемента группы и первого элемента группы"
...
Рейтинг: 0 / 0
28.09.2018, 07:15
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39709616
982183
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
982183
Участник
Хотя тестовый пример просчитан именно по алгоритму
"Разница между первым элементом предшествующей группы и первым элементом новой группы" <=0.5
...
Рейтинг: 0 / 0
28.09.2018, 07:18
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39709630
Фотография Павел Воронцов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Павел Воронцов
Участник
Только хардкор, никаких вам аналитик!
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
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. Дальше сами
...
Рейтинг: 0 / 0
28.09.2018, 08:11
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39709635
Фотография Павел Воронцов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Павел Воронцов
Участник
Павел Воронцов,

Не для всех данных работает корректно. Но разберитесь сами, там просто вроде.
...
Рейтинг: 0 / 0
28.09.2018, 08:28
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39709643
Фотография -2-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
-2-
Участник
Павел Воронцовразберитесь самиreset running total в sql решается через рекурсию connect by/with, model или match_recognize.
...
Рейтинг: 0 / 0
28.09.2018, 08:45
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39709673
Energomarket
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Energomarket
Гость
-2-,

Отличный ответ.
Немного изменил запрос что-бы работал с любым первым элементом (не обязательно ноль).
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
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;



Немного поясню суть Вашей идеи.
Иерархическим подзапросом находятся все первые элементы групп (соответственно проставляется номер группы):
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
 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
...
Рейтинг: 0 / 0
28.09.2018, 09:52
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39709676
Energomarket
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Energomarket
Гость
982183-2-,

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

Код: sql
1.
select id, val, iif(val - (LAG(val, 1, val-1) OVER (order by val))<= 0.5, 0, 1) as  res



А потом любым методом посчитать нарастающий итог.
Я так и пробовал. Но из-за того что нужно запоминать первый элемент группы (а не предыдущую запись), функция LAG не спасала ситуацию.
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
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.
...
Рейтинг: 0 / 0
28.09.2018, 10:01
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39709687
982183
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
982183
Участник
EnergomarketНо из-за того что нужно запоминать первый элемент группы (а не предыдущую запись),
ну так далее я и говорю о том, что в постановке не "первый элемент группы", а "последний"
...
Рейтинг: 0 / 0
28.09.2018, 10:25
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39709688
982183
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
982183
Участник
EnergomarketНе определено начало группы 6,25.
6,25-5,83=0,42
...
Рейтинг: 0 / 0
28.09.2018, 10:27
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39709699
Фотография Павел Воронцов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Павел Воронцов
Участник
-2-Павел Воронцовразберитесь самиreset running total в sql решается через рекурсию connect by/with, model или match_recognize.Я не спорю, но у меня вон решилось. Когда и если голова перестанет болеть, допилю для любого набора данных.

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

В Вашем решении меня смущает
Код: plsql
1.
and id < m1.id
...
Рейтинг: 0 / 0
28.09.2018, 10:45
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39709717
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
Energomarket,

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
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.
...
Рейтинг: 0 / 0
28.09.2018, 11:08
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39709722
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
Павел Воронцов-2-пропущено...
reset running total в sql решается через рекурсию connect by/with, model или match_recognize.Я не спорю, но у меня вон решилось. Когда и если голова перестанет болеть, допилю для любого набора данных.

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

PS. Ну можно извратиться еще через XML.
...
Рейтинг: 0 / 0
28.09.2018, 11:15
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39709732
Energomarket
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Energomarket
Гость
982183EnergomarketНе определено начало группы 6,25.
6,25-5,83=0,42
Первый элемент группы 5,67.
6,25-5,67=0,58
...
Рейтинг: 0 / 0
28.09.2018, 11:32
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39709744
982183
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
982183
Участник
третий раз пишу, что в постановке
авторРазница между значением (поле val) последнего элемента группы и первого элемента группы не должно превышать 0.5.
и исправлений постановки не было.
...
Рейтинг: 0 / 0
28.09.2018, 11:41
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39709746
982183
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
982183
Участник
Всё. До меня дошло.
...
Рейтинг: 0 / 0
28.09.2018, 11:42
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39709959
SkilledJunior
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SkilledJunior
Гость
dbms_photoshopникакими SQL-методами, кроме перечисленных -2- задача нерешаема.

rownum/group by/order by достаточно для решения этой задачи.
...
Рейтинг: 0 / 0
28.09.2018, 15:59
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39709976
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
SkilledJuniordbms_photoshopникакими SQL-методами, кроме перечисленных -2- задача нерешаема.

rownum/group by/order by достаточно для решения этой задачи.Классно. Ты ж и показать сможешь, да?
...
Рейтинг: 0 / 0
28.09.2018, 16:25
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39709993
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
dbms_photoshopSkilledJuniorпропущено...
rownum/group by/order by достаточно для решения этой задачи.Классно. Ты ж и показать сможешь, да?
Ну вот, оторвал человека от дел - он теперь пару часов угрохает на заведомую ерунду, а затем наспамит тут десяток datamagic-specific вариантов... в лучшем случае налепит свой вариант connect by/subquery factoring :)
...
Рейтинг: 0 / 0
28.09.2018, 16:42
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39710027
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
dbms_photoshopпочему никакими SQL-методами, кроме перечисленных -2- задача нерешаема.


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

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
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.
...
Рейтинг: 0 / 0
28.09.2018, 17:22
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39710057
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
SY,

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

21688712
21688856
...
Рейтинг: 0 / 0
28.09.2018, 17:57
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39710058
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
andrey_anonymousнаспамит тут десяток datamagic-specific вариантовЭто очень лестная характеристика опытного Жуниора.
Я ставлю на то, что в его случае всё ограничится пустой болтовней.
...
Рейтинг: 0 / 0
28.09.2018, 17:58
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39710135
SkilledJunior
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SkilledJunior
Гость
dbms_photoshopКлассно. Ты ж и показать сможешь, да?
Видишь суслика? Нет? А он есть))
...
Рейтинг: 0 / 0
28.09.2018, 21:11
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39710196
nullin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
nullin
Гость
SkilledJuniordbms_photoshopКлассно. Ты ж и показать сможешь, да?
Видишь суслика? Нет? А он есть))
Судя по всему там джойн на таблицу чисел по копеечкам, применительно к данным ТС, но правда, если точность cap далеко за сотые, то...
...
Рейтинг: 0 / 0
29.09.2018, 01:40
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39710219
Фотография Павел Воронцов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Павел Воронцов
Участник
dbms_photoshop,

Да, сегодня голова не болит и я согласен - только рекурсия.
...
Рейтинг: 0 / 0
29.09.2018, 07:30
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39710415
SkilledJunior
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SkilledJunior
Гость
SkilledJuniorВидишь суслика? Нет? А он есть))

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

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

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

Задача не имеет не итеративных решений, рекурсия кривой метод реализации итераций, нормальный метод озвучил:
andrey_anonymousоформите pipelined функцией и не морочьте голову.
...
Рейтинг: 0 / 0
30.09.2018, 00:09
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39710428
SkilledJunior
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SkilledJunior
Гость
На вентилятор ...

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
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;
/



Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
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)
...
Рейтинг: 0 / 0
30.09.2018, 01:11
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39710997
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
Energomarketсредствами oracle 10g

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

Код: plsql
1.
 PRAGMA UDF;



Код: plsql
1.
2.
3.
with
 function get_res_alt(p_val in number, p_rn in number) return number is
 begin
...
Рейтинг: 0 / 0
01.10.2018, 15:41
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39711327
SkilledJunior
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SkilledJunior
Гость
andrey_anonymous,

Без PRAGMA UDF пакетный вариант будет работать и в десятке, больше ничего в таком решении не смущает?
...
Рейтинг: 0 / 0
01.10.2018, 22:56
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39711408
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
SkilledJuniorбольше ничего в таком решении не смущает?SQL сам накажет самовлюблённых гениёв за использование функций с побочными эффектами.
...
Рейтинг: 0 / 0
02.10.2018, 07:41
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39712728
SkilledJunior
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SkilledJunior
Гость
Elic,

Да, для пользовательских функций Oracle не гарантирует ни нужную последовательность ни количество вызовов для каждой строки, хотя сам вполне себе генерирует псевдостолбец rownu, т.е. механизм имеется.
...
Рейтинг: 0 / 0
03.10.2018, 23:22
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39712770
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
SkilledJuniorхотя сам вполне себе генерирует псевдостолбец rownu, т.е. механизм имеется.Бред.
...
Рейтинг: 0 / 0
04.10.2018, 07:53
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39712784
Фотография -2-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
-2-
Участник
SkilledJuniorдля пользовательских функций если уж браться за plsql, то за обычный или полиморфный pipeline.
...
Рейтинг: 0 / 0
04.10.2018, 08:46
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39712851
Фотография -2-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
-2-
Участник
 
PTF
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
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; 
/

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
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
...
Рейтинг: 0 / 0
04.10.2018, 10:55
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39713207
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
-2-
Код: plsql
1.
2.
with m_data as (...)
select * from reset_running_distance(m_data order by val, columns(val), 0.5);


Записал, спасибо.
...
Рейтинг: 0 / 0
04.10.2018, 18:32
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39713584
SkilledJunior
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SkilledJunior
Гость
ElicБред.Сформировать rownum можно либо имея доступ к тому же столбцу предыдущей строки, либо сохраняя последний присвоенный номер в переменную, при этом необходимо гарантировать последовательность прохода по выборке и отсутствие увеличения счетчика более одного раза для одной и той же строки. Оба механизма реализуют итеративный инкремент по выборке и подошли бы для решения задачи.

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

Полиморфный метод зачетный, кинул в избранное.
...
Рейтинг: 0 / 0
05.10.2018, 14:06
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39713608
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
SkilledJunior,

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

Хорошо хоть сам написал, что Оракл не гарантирует последовательность вызова функций.
Даже лень проверять будет ли устойчив эффект при параллельном выполнении. Слишком тухлый вброс.
...
Рейтинг: 0 / 0
05.10.2018, 14:30
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39713652
SkilledJunior
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SkilledJunior
Гость
dbms_photoshopДаже лень проверять будет ли устойчив эффект при параллельном выполнении.
Банальный пример, конечное не будет устойчив, другие примеры нарушения последовательности были бы более интересны, а примеры многократного вызова функции для одной и той же строки еще интереснее.

Еще интересный вопрос, что сделает rownum без сортировки и rownum from (select ... order by) с параллельным выполнением запроса.
...
Рейтинг: 0 / 0
05.10.2018, 15:25
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39713692
Фотография -2-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
-2-
Участник
SkilledJuniorдругие примеры нарушения последовательности
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
  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
...
Рейтинг: 0 / 0
05.10.2018, 16:02
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39713695
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
SkilledJuniorпримеры многократного вызова функции для одной и той же строки еще интереснее.Вспоминаются примеры недовызова. 16679179 (хотя rownum это как раз "лечит")
SkilledJuniorЕще интересный вопрос, что сделает rownum без сортировки и rownum from (select ... order by) с параллельным выполнением запроса.При параллельности ты не управляешь тем какие порции данных идут в какой slave не говоря уже про то, что любые попытки использовать stateful пакеты бессмысленны.
...
Рейтинг: 0 / 0
05.10.2018, 16:04
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39713722
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
dbms_photoshopПри параллельности ты не управляешь тем какие порции данных идут в какой slave не говоря уже про то, что любые попытки использовать stateful пакеты бессмысленны.
Эээ... Вообще-то это, скажем так, не совсем правда по обоим пунктам.
По крайней мере, я и управлял распределением по слейвам, и успешно использовал statefull...
Но не в контексте решения юниора, ессно :)
...
Рейтинг: 0 / 0
05.10.2018, 16:35
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39713727
SkilledJunior
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SkilledJunior
Гость
-2-,

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

dbms_photoshopВспоминаются примеры недовызова. 16679179 (хотя rownum это как раз "лечит")
Ох уж этот экономный Oracle, но все же в примере одна и та же функция с одними и теми же параметрами в разных столбцах, но не в одном столбце разных строк.

SkilledJuniorПри параллельности ты не управляешь тем какие порции данных идут в какой slave не говоря уже про то, что любые попытки использовать stateful пакеты бессмысленны.
Вопрос был несколько иной, даст ли rownum и rownum на отсортированной выборке использовать параллельность.
...
Рейтинг: 0 / 0
05.10.2018, 16:41
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39713731
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
dbms_photoshopПри параллельности ты не управляешь тем какие порции данных идут в какой slave

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

SY.
...
Рейтинг: 0 / 0
05.10.2018, 16:45
| Ответить | Цитировать | Написать  
reset running total / Задача определения "плавающих" групп
    #39713744
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
andrey_anonymousdbms_photoshopПри параллельности ты не управляешь тем какие порции данных идут в какой slave не говоря уже про то, что любые попытки использовать stateful пакеты бессмысленны.
Эээ... Вообще-то это, скажем так, не совсем правда по обоим пунктам.
По крайней мере, я и управлял распределением по слейвам, и успешно использовал statefull...
Но не в контексте решения юниора, ессно :)Речь шла про решение Жуниора single row function , а не про любителей явно указывать как распределять данные в пайплайне или еще чего.
...
Рейтинг: 0 / 0
05.10.2018, 17:05
| Ответить | Цитировать | Написать  
47 сообщений из 47, показаны все 2 страниц
  все  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / reset running total / Задача определения "плавающих" групп
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?all=1&fid=52&tid=1883366]:
 0ms
get settings:
 7ms
get forum list:
 12ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 147ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 78ms
get tp. blocked users:
 1ms
others: 231ms
total:  490ms
созд. / загр.: 490ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]