ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / reset running total / Задача определения "плавающих" групп
25 сообщений из 47, страница 1 из 2
  1  все
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
| Ответить | Цитировать | Написать  
25 сообщений из 47, страница 1 из 2
  1  все
Форумы / Oracle [игнор отключен] [закрыт для гостей] / reset running total / Задача определения "плавающих" групп
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&msg=39709554&tid=1883366]:
 0ms
get settings:
 10ms
get forum list:
 21ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 188ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 87ms
get tp. blocked users:
 2ms
others: 244ms
total:  575ms
созд. / загр.: 575ms
x
x
Закрыть


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