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

Откуда: Москва
Сообщений: 16771
В указанных ограничениях (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

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

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

Откуда: Новосибирск
Сообщений: 2338
Блог
Только хардкор, никаких вам аналитик!
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

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

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

Откуда:
Сообщений: 13826
Павел Воронцов
разберитесь сами
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
Сообщений: 2366
Energomarket
Но из-за того что нужно запоминать первый элемент группы (а не предыдущую запись),

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

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

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

Откуда: Новосибирск
Сообщений: 2338
Блог
-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
Сообщений: 5142
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
Сообщений: 5142
Павел Воронцов
-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
Сообщений: 2366
третий раз пишу, что в постановке
автор
Разница между значением (поле val) последнего элемента группы и первого элемента группы не должно превышать 0.5.

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

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

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


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

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


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

Откуда: Москва
Сообщений: 16771
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
Сообщений: 9112
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
Сообщений: 5142
SY,

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

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

Откуда: sqlmdx.net
Сообщений: 5142
andrey_anonymous
наспамит тут десяток datamagic-specific вариантов
Это очень лестная характеристика опытного Жуниора.
Я ставлю на то, что в его случае всё ограничится пустой болтовней.
28 сен 18, 17:58    [21689401]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Oracle Ответить