ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Как реализовать не очень аналитическое ранжирование?
18 сообщений из 18, страница 1 из 1
Как реализовать не очень аналитическое ранжирование?
    #39438627
veterskv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
veterskv
Гость
Добрый день,
Аналитические функции такие аналитические, что невозможно это обойти (я не придумала как).
Собственно вопрос.
Код: 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.
with t as (
    select 11 n, 100 m, 1111 p from dual union all
    select 11,   100,   1111   from dual union all
    select 11,   100,   2222   from dual union all
    select 11,   100,   2222   from dual union all
    select 11,   200,   3333   from dual union all
    select 11,   200,   3333   from dual union all
    select 11,   100,   4444   from dual union all
    select 11,   100,   4444   from dual union all
    select 11,   100,   1111   from dual union all
    select 11,   100,   1111   from dual union all
    select 11,   300,   4444   from dual union all
    select 11,   300,   4444   from dual union all
    select 55,   100,   2222   from dual union all
    select 55,   100,   2222   from dual union all
    select 55,   100,   1111   from dual union all
    select 55,   100,   1111   from dual union all
    select 55,   300,   3333   from dual union all
    select 55,   300,   3333   from dual union all
    select 55,   100,   1111   from dual union all
    select 55,   100,   1111   from dual
)
select t.*
     , rownum rn
     , dense_rank() over (order by t.n, t.m, t.p) dr
  from t
 order by 4
;


В результате:
(Необходимо получить красное поле)
NMPRNDR необходимо 11 100 1111 1 1 1 11 100 1111 2 1 1111002222322111002222422112003333543112003333643111004444734111004444834 11 100 1111 9 1 5 11 100 1111 10 1 51130044441156113004444125655100222213715510022221471 55 100 1111 15 6 2 55 100 1111 16 6 255300333317835530033331883 55 100 1111 19 6 4 55 100 1111 20 6 4
проблемные, для нужного ранжирования, записи выделены жирным
Спасибо.
P.S. Функцию lag тоже пробовала, но она берет для анализа все четыре значения 1111 в группе
...
Рейтинг: 0 / 0
14.04.2017, 12:34
| Ответить | Цитировать | Написать  
Как реализовать не очень аналитическое ранжирование?
    #39438630
Фотография -2-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
-2-
Участник
veterskv,

Аналитика требует порядок, а rownum - анархия.
...
Рейтинг: 0 / 0
14.04.2017, 12:37
| Ответить | Цитировать | Написать  
Как реализовать не очень аналитическое ранжирование?
    #39438633
veterskv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
veterskv
Гость
-2-,

возможно, но отсутствие rownum не повлияет на результат dense_rank
...
Рейтинг: 0 / 0
14.04.2017, 12:39
| Ответить | Цитировать | Написать  
Как реализовать не очень аналитическое ранжирование?
    #39438638
Фотография Egoр
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Egoр
Участник
veterskv,

Есть какое-нибудь описание семантики расстановки значений в столбце "необходимо" или просто "я так хочу"?
...
Рейтинг: 0 / 0
14.04.2017, 12:45
| Ответить | Цитировать | Написать  
Как реализовать не очень аналитическое ранжирование?
    #39438647
veterskv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
veterskv
Гость
Egoр,

суть в том, что
N - дата
M - зона склада
P - кладовщик

P в течении дня перемещается с зоны склада на зону склада и делает там какие-то операции

Необходимо из ориентировочно таких данных:
N,M,P,время,действие
12.04.2017100111112:30вход в зону 100 склада12.04.2017100111115:15вЫход из зоны 100 склада12.04.2017100111116:10вход в зону 100 склада12.04.2017100111118:05вЫход из зоны 100 склада
Получить такую группировку:
NMPвремя входавремя выхода12.04.2017100111112:3015:1512.04.2017100111116:1018:05
или кто-то на момент выполнения запроса вошел и не вышел, тогда "время выхода" пустое
...
как-то так
...
Рейтинг: 0 / 0
14.04.2017, 12:57
| Ответить | Цитировать | Написать  
Как реализовать не очень аналитическое ранжирование?
    #39438655
veterskv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
veterskv
Гость
Egoр,

просто, если бы мне удалось ранжировать так как в красном поле, то смогла бы получить итоговую группировку

и да... пусть поле времени будет вместо rownum, суть та же: время с секундами, отсортировано по возрастанию как rownum
...
Рейтинг: 0 / 0
14.04.2017, 13:05
| Ответить | Цитировать | Написать  
Как реализовать не очень аналитическое ранжирование?
    #39438657
ora601
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ora601
Участник
veterskv,

через start_of_group только вместо rownum как уже сказали нужно поле для сортировки (обычно таймстемп, да)



Код: 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.
with t as (
    select 11 n, 100 m, 1111 p from dual union all
    select 11,   100,   1111   from dual union all
    select 11,   100,   2222   from dual union all
    select 11,   100,   2222   from dual union all
    select 11,   200,   3333   from dual union all
    select 11,   200,   3333   from dual union all
    select 11,   100,   4444   from dual union all
    select 11,   100,   4444   from dual union all
    select 11,   100,   1111   from dual union all
    select 11,   100,   1111   from dual union all
    select 11,   300,   4444   from dual union all
    select 11,   300,   4444   from dual union all
    select 55,   100,   2222   from dual union all
    select 55,   100,   2222   from dual union all
    select 55,   100,   1111   from dual union all
    select 55,   100,   1111   from dual union all
    select 55,   300,   3333   from dual union all
    select 55,   300,   3333   from dual union all
    select 55,   100,   1111   from dual union all
    select 55,   100,   1111   from dual
)
SELECT t.*, SUM(grp) OVER (partition by n order by rn) FROM
(select t.*
     , rownum rn
     , case when lag (m) over (partition by n order by rownum) = m AND lag (p) over (partition by n order by rownum) =p  THEN 0 ELSE 1 END grp 
  from t
)
 t
...
Рейтинг: 0 / 0
14.04.2017, 13:05
| Ответить | Цитировать | Написать  
Как реализовать не очень аналитическое ранжирование?
    #39438667
veterskv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
veterskv
Гость
ora601,

да ))) это оно
спасибо, пойду въезжать и встраивать в запрос
...
Рейтинг: 0 / 0
14.04.2017, 13:14
| Ответить | Цитировать | Написать  
Как реализовать не очень аналитическое ранжирование?
    #39536009
racer
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
racer
Участник
Всем привет нужна помощь. Есть выборка данных:
Артикул id Операция Дата операции123 120 + 17.10.2012123 120 - 12.11.2012123 120 + 17.02.2014123 120 - 02.06.2014123 120 + 26.10.2015123 120 - 27.10.2015


Мне нужно выбрать периоды нахождения артикула в матрице
Например Артикул 123 был добавлен в матрицу ID 120 17.10.2012 а удален 12.11.2012,
затем 12.11 добавлен и 17.02. удален и т.д.
т.е. нужно получить вот такой результат:

Артикул Добавление (+) удаление(-)123 17.10.2012 12.11.2012123 17.02.2014 02.06.2014123 26.10.2015 27.10.2015

Помогите плиз, только осваиваю аналитические функции. Что-то мозг закипает уже...
...
Рейтинг: 0 / 0
13.10.2017, 14:02
| Ответить | Цитировать | Написать  
Как реализовать не очень аналитическое ранжирование?
    #39536017
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
racerВсем привет нужна помощь.Не стоит из-за этого гробокопать чужие темы. Создавай свои.racerЧто-то мозг закипает уже...STFF start_of_group
...
Рейтинг: 0 / 0
13.10.2017, 14:16
| Ответить | Цитировать | Написать  
Как реализовать не очень аналитическое ранжирование?
    #39536040
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
racer,
Код: 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.
with t(a,id,o,d) as (
select 123,	120,	'+',to_date('17.10.2012','dd.mm.yyyy') from dual union all
select 123,	120,	'-',to_date('12.11.2012','dd.mm.yyyy') from dual union all
select 123,	120,	'+',to_date('17.02.2014','dd.mm.yyyy') from dual union all
select 123,	120,	'-',to_date('02.06.2014','dd.mm.yyyy') from dual union all
select 123,	120,	'+',to_date('26.10.2015','dd.mm.yyyy') from dual union all
select 123,	120,	'-',to_date('27.10.2015','dd.mm.yyyy') from dual union all
select 124,	121,	'+',to_date('17.02.2016','dd.mm.yyyy') from dual union all
select 124,	121,	'-',to_date('02.06.2016','dd.mm.yyyy') from dual union all
select 124,	121,	'+',to_date('06.10.2017','dd.mm.yyyy') from dual
)
,tt as (
 select t.*
  ,ceil(row_number() over (partition by a order by d)/2) rn
 from t)
select a,id,rn,"plus","minus" from tt
pivot (max(d) for o in ('+' as "plus",'-' as "minus"))
order by 1,4
/
SQL> /

         A         ID         RN plus     minus
---------- ---------- ---------- -------- --------
       123        120          1 17.10.12 12.11.12
       123        120          2 17.02.14 02.06.14
       123        120          3 26.10.15 27.10.15
       124        121          1 17.02.16 02.06.16
       124        121          2 06.10.17




ps
+/- должны чередоватся
......
stax
...
Рейтинг: 0 / 0
13.10.2017, 14:51
| Ответить | Цитировать | Написать  
Как реализовать не очень аналитическое ранжирование?
    #39536047
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
перемудрил, счас испрвлюсь

.....
stax
...
Рейтинг: 0 / 0
13.10.2017, 15:06
| Ответить | Цитировать | Написать  
Как реализовать не очень аналитическое ранжирование?
    #39536059
racer
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
racer
Участник
Staxps
+/- должны чередоватся
......
stax

Stax , большое спасибо. То что нужно.
+/- чередуются
...
Рейтинг: 0 / 0
13.10.2017, 15:20
| Ответить | Цитировать | Написать  
Как реализовать не очень аналитическое ранжирование?
    #39536079
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
До кучи.
Аналитика:
1. Если +- чередуются строго:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
select a, id, min(d) add_dt, max(remove_dt) remove_dt
from (
  select a, id, o, d
       , count( case o when '+' then 1 end) over (partition by a, id order by d) g
       , case o when '-' then d end remove_dt
  from t
) tt
group by a,id, g



2. Если существуют последовательности ++ или --:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
select a, id, min(d) add_dt, max(case o when '-' then d end) remove_dt
from (
  select a, id, o, d
       , sum(lg) over(partition by a, id order by d) g
  from (
    select a, id, o, d
         , decode(o||lag(o,1,'-') over (partition by a, id order by d),'+-',1) lg
    from t
  )
  ) tt
group by a,id, g



12с (вариант также допускает ++ и --):
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
select * from t
match_recognize(
  partition by a,id
  order by d
  measures strt.d as add_dt
         , case last(o) when '-' then  last(d) end as remove_dt 
 one row per match
 pattern(strt intr* nxt*)
 define nxt as nxt.o = '-'
      , intr as intr.o = '+'
)
...
Рейтинг: 0 / 0
13.10.2017, 15:50
| Ответить | Цитировать | Написать  
Как реализовать не очень аналитическое ранжирование?
    #39536083
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
racerStaxps
+/- должны чередоватся
......
stax

Stax , большое спасибо. То что нужно.
+/- чередуются

перемудрил я с пивот
проще вариант
Код: 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 t(a,id,o,d) as (
select 123,        120,    '+',to_date('17.10.2012','dd.mm.yyyy') from dual union all
select 123,        120,    '-',to_date('12.11.2012','dd.mm.yyyy') from dual union all
select 123,        120,    '+',to_date('17.02.2014','dd.mm.yyyy') from dual union all
select 123,        120,    '-',to_date('02.06.2014','dd.mm.yyyy') from dual union all
select 123,        120,    '+',to_date('26.10.2015','dd.mm.yyyy') from dual union all
select 123,        120,    '+',to_date('27.10.2015','dd.mm.yyyy') from dual union all
select 123,        120,    '+',to_date('28.10.2015','dd.mm.yyyy') from dual union all
select 123,        120,    '-',to_date('29.10.2015','dd.mm.yyyy') from dual union all
select 123,        120,    '-',to_date('30.10.2015','dd.mm.yyyy') from dual union all
select 123,        120,    '-',to_date('31.12.2015','dd.mm.yyyy') from dual union all
select 124,        121,    '+',to_date('17.02.2016','dd.mm.yyyy') from dual union all
select 124,        121,    '-',to_date('02.06.2016','dd.mm.yyyy') from dual union all
select 124,        121,    '+',to_date('06.10.2017','dd.mm.yyyy') from dual
)
,tt as (
select t.*
,o||lag(o,1,'-') over (partition by a order by d) pm
,decode(o,'+',d)  "plus"
,case
  when o||lead(o,1,'-')  over (partition by a order by d)='+-'
       then  lead(d) over (partition by a order by d)
  when o||lag(o,1,'-') over (partition by a order by d) ='--'
       then d
  end  "minus"
from t
)
select a,id,"plus","minus" from tt
where o='+' or pm='--'
order by a,d
/
SQL> /

         A         ID plus     minus
---------- ---------- -------- --------
       123        120 17.10.12 12.11.12
       123        120 17.02.14 02.06.14
       123        120 26.10.15
       123        120 27.10.15
       123        120 28.10.15 29.10.15
       123        120          30.10.15
       123        120          31.12.15
       124        121 17.02.16 02.06.16
       124        121 06.10.17



и не чередуются


.....
stax
...
Рейтинг: 0 / 0
13.10.2017, 15:57
| Ответить | Цитировать | Написать  
Как реализовать не очень аналитическое ранжирование?
    #39536084
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
andrey_anonymous12с (вариант также допускает ++ и --):
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
select * from t
match_recognize(
  partition by a,id
  order by d
  measures strt.d as add_dt
         , nxt.d as remove_dt 
-- , case last(o) when '-' then  last(d) end as remove_dt -- перебор 
 one row per match
 pattern(strt intr* nxt*)
 define nxt as nxt.o = '-'
      , intr as intr.o = '+'
)
...
Рейтинг: 0 / 0
13.10.2017, 15:57
| Ответить | Цитировать | Написать  
Как реализовать не очень аналитическое ранжирование?
    #39536087
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
andrey_anonymous,

отличная задачка для match_recognize если +/- не чередуются

.....
stax
...
Рейтинг: 0 / 0
13.10.2017, 16:02
| Ответить | Цитировать | Написать  
Как реализовать не очень аналитическое ранжирование?
    #39536114
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
Staxandrey_anonymous,

отличная задачка для match_recognize если +/- не чередуются

.....
staxОтличная была бы если бы не было аналитического решения, а так просто типичная. :)
...
Рейтинг: 0 / 0
13.10.2017, 16:41
| Ответить | Цитировать | Написать  
18 сообщений из 18, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Как реализовать не очень аналитическое ранжирование?
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&msg=39438638&tid=1885111]:
 0ms
get settings:
 7ms
get forum list:
 16ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 156ms
get topic data:
 9ms
get forum data:
 2ms
get page messages:
 57ms
get tp. blocked users:
 1ms
others: 203ms
total:  457ms
созд. / загр.: 457ms
x
x
Закрыть


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