Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Как реализовать не очень аналитическое ранжирование? / 18 сообщений из 18, страница 1 из 1
14.04.2017, 12:34
    #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:37
    #39438630
-2-
-2-
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как реализовать не очень аналитическое ранжирование?
veterskv,

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

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

Есть какое-нибудь описание семантики расстановки значений в столбце "необходимо" или просто "я так хочу"?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.04.2017, 12:57
    #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, 13:05
    #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:14
    #39438667
veterskv
veterskv
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как реализовать не очень аналитическое ранжирование?
ora601,

да ))) это оно
спасибо, пойду въезжать и встраивать в запрос
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.10.2017, 14:02
    #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:16
    #39536017
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как реализовать не очень аналитическое ранжирование?
racerВсем привет нужна помощь.Не стоит из-за этого гробокопать чужие темы. Создавай свои.racerЧто-то мозг закипает уже...STFF start_of_group
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.10.2017, 14:51
    #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, 15:06
    #39536047
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как реализовать не очень аналитическое ранжирование?
перемудрил, счас испрвлюсь

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

Stax , большое спасибо. То что нужно.
+/- чередуются
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.10.2017, 15:50
    #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:57
    #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, 16:02
    #39536087
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как реализовать не очень аналитическое ранжирование?
andrey_anonymous,

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

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

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

.....
staxОтличная была бы если бы не было аналитического решения, а так просто типичная. :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Как реализовать не очень аналитическое ранжирование? / 18 сообщений из 18, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 360ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1885111]:
 0ms
get settings:
 8ms
get forum list:
 13ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 33ms
get topic data:
 9ms
get forum data:
 3ms
get page messages:
 51ms
get tp. blocked users:
 2ms
others: 237ms
total:  360ms
x
x
Закрыть


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