ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Можно ли аналитикой?
25 сообщений из 25, страница 1 из 1
Можно ли аналитикой?
    #39593331
j2k
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
j2k
Участник
Всем доброго дня.
Есть данные о суммах.
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
with t as ( select 1 rn, 100 val from dual
  union all select 2 rn, 300 val from dual
  union all select 3 rn, 200 val from dual
  union all select 4 rn, 100 val from dual
  union all select 5 rn, -350 val from dual
  union all select 6 rn, 100 val from dual
  union all select 7 rn, 100 val from dual
  union all select 8 rn, -300 val from dual
  union all select 9 rn, 800 val from dual
  union all select 10 rn, -600 val from dual
      ) 
select * from t


Отрицательные числа - это возврат части ранее пришедших сумм
Нужно получить суммы которые пришли с учетом этих корректировок, т.е. строка под номером 5 должна отменить сумму в строке 4, 3 и часть суммы из строки 2

Сделал это через модельку, но интересует можно ли такое выполнить аналитикой
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
with t as ( select 1 rn, 100 val from dual
  union all select 2 rn, 300 val from dual
  union all select 3 rn, 200 val from dual
  union all select 4 rn, 100 val from dual
  union all select 5 rn, -350 val from dual
  union all select 6 rn, 100 val from dual
  union all select 7 rn, 100 val from dual
  union all select 8 rn, -300 val from dual
  union all select 9 rn, 800 val from dual
  union all select 10 rn, -600 val from dual
      )
select val, nakop, greatest(newval,0) from t
model
dimension by (row_number() over (order by rn desc) as i)
measures (val, 0 as nakop, 0 as newval)
ignore nav
rules(nakop[any] order by i = least(nakop[cv()-1] + val[cv()], 0), 
      newval[any] = nakop[cv()-1] + val[cv()]
      )
order by i desc
...
Рейтинг: 0 / 0
30.01.2018, 13:58
| Ответить | Цитировать | Написать  
Можно ли аналитикой?
    #39593419
MaximaXXL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MaximaXXL
Участник
j2k,

Пока так:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
with t as ( select 1 rn, 100 val from dual
  union all select 2 rn, 300 val from dual
  union all select 3 rn, 200 val from dual
  union all select 4 rn, 100 val from dual
  union all select 5 rn, -350 val from dual
  union all select 6 rn, 100 val from dual
  union all select 7 rn, 100 val from dual
  union all select 8 rn, -300 val from dual
  union all select 9 rn, 800 val from dual
  union all select 10 rn, -600 val from dual
      )

select rn, val, row_number() over (partition by gr_id order by rn ) row_n,
                sum(val) over (partition by gr_id order by rn desc) res
  from ( select rn, val, count(decode(sign(val),-1,0)) over (order by rn desc) gr_id
           from t
        )       
order by rn



Но это не Ваш результат, будет время - может доделаю =)
...
Рейтинг: 0 / 0
30.01.2018, 15:45
| Ответить | Цитировать | Написать  
Можно ли аналитикой?
    #39593421
MaximaXXL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MaximaXXL
Участник
j2k,

Я просто не очень понял почему минус передается выше(учитывается) тогда как + останавливает этот учет.
...
Рейтинг: 0 / 0
30.01.2018, 15:49
| Ответить | Цитировать | Написать  
Можно ли аналитикой?
    #39593477
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
j2k,

почему
300 0 150 ?

я так розумию 100+300+200+100
коррекция -350
будет 100+250=350

.....
stax
...
Рейтинг: 0 / 0
30.01.2018, 17:02
| Ответить | Цитировать | Написать  
Можно ли аналитикой?
    #39593484
MaximaXXL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MaximaXXL
Участник
Stax,

Там условие странное, как по мне, и -100 добавляется с набора который ниже 100+100-300
Хотя +200 которое осталось с операции 800-600 не пробросилось выше ....

Надо уточнять задачу
...
Рейтинг: 0 / 0
30.01.2018, 17:23
| Ответить | Цитировать | Написать  
Можно ли аналитикой?
    #39593490
j2k
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
j2k
Участник
MaximaXXL,
ну вот смотрите, вам пришли платежи
 
rnval1100р2300р3200р4100р

После этого говорят, мы сделали перерасчет и оказывается перечислили лишних 350р, верните их нам. Мы делаем корректировку, которая должна аннулировать платежи начиная с последних пришедших. Т.е. с учетом корректировки платежи должны выглядеть вот так:
 
rnval1100р2250р30р40р

т.е. полностью вернули 100р, 200р и остаточек 50 рублей из второго платежа
Дальше нам продолжают перечислять деньги:
 
rnval1100р2250р30р40р6100р7100р

Затем снова делают перерасчет и говорят, что были еще лишние 300р - откатываем их опять по порядку, начиная с последнего, получаем:
 
rnval1100р2150р30р40р60р70р

т.е. полностью вернули 7 и 6 платежи по 100р и оставшейся сотней уменьшили 2 платеж
Дальше нам продолжают платить:
 
rnval1100р2150р30р40р60р70р9800р

В очередной раз присылают корректировку на 600р, делаем опять возврат:
 
rnval1100р2150р30р40р60р70р9200р

на этот раз предыдущего транша оплаты хватило, чтоб целиком учесть корректировку
...
Рейтинг: 0 / 0
30.01.2018, 17:29
| Ответить | Цитировать | Написать  
Можно ли аналитикой?
    #39593515
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
j2k,

через рекурсивный with
Код: 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.
SQL> ed
Wrote file afiedt.buf

  1  with t as ( select 1 rn, 100 val from dual
  2    union all select 2 rn, 300 val from dual
  3    union all select 3 rn, 200 val from dual
  4    union all select 4 rn, 100 val from dual
  5    union all select 5 rn, -350 val from dual
  6    union all select 6 rn, 100 val from dual
  7    union all select 7 rn, 100 val from dual
  8    union all select 8 rn, -300 val from dual
  9    union all select 9 rn, 800 val from dual
 10    union all select 10 rn, -600 val from dual
 11        )
 12  ,tt(rn,val,nakop,p) as (
 13  select rn,val,(case when val<0 then val else 0 end) nakop,val
 14  from t where rn=(select max(rn) from t)
 15  union all
 16  select t.rn,t.val,(case when (t.val+tt.nakop)<0 then (t.val+tt.nakop) else 0 end) nakop
 17        ,t.val+tt.nakop
 18  from tt,t where t.rn=tt.rn-1)
 19  select rn,val,nakop,(case when p>0 then p else 0 end) p from tt
 20* order by rn
SQL> /

        RN        VAL      NAKOP          P
---------- ---------- ---------- ----------
         1        100          0        100
         2        300          0        150
         3        200       -150          0
         4        100       -350          0
         5       -350       -450          0
         6        100       -100          0
         7        100       -200          0
         8       -300       -300          0
         9        800          0        200
        10       -600       -600          0

10 rows selected.



.....
stax
...
Рейтинг: 0 / 0
30.01.2018, 18:12
| Ответить | Цитировать | Написать  
Можно ли аналитикой?
    #39593520
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimitry Sibiryakov
Участник
А не проще тупо посчитать сумму и выдать текущую задолженность/переплату?..
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
30.01.2018, 18:19
| Ответить | Цитировать | Написать  
Можно ли аналитикой?
    #39593727
j2k
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
j2k
Участник
Dimitry Sibiryakov,
Ну если бы нужно было просто посчитать долг, то проще. Но здесь другая цель.
Это упрощенная часть вьюхи, в которой эти факты соединяются с плановыми выплатами, по которой потом смотрятся сроки на сколько просрочен тот или иной платеж.
...
Рейтинг: 0 / 0
31.01.2018, 09:39
| Ответить | Цитировать | Написать  
Можно ли аналитикой?
    #39593885
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimitry Sibiryakov
Участник
j2kсроки на сколько просрочен тот или иной платеж.

Это период в который сумма с нарастающим итогом была отрицательной. Да, тривиально
делается аналитикой.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
31.01.2018, 13:01
| Ответить | Цитировать | Написать  
Можно ли аналитикой?
    #39593932
j2k
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
j2k
Участник
Dimitry Sibiryakov, Не совсем понял, без корректировок - да это тривиально и делается аналитикой, и она сейчас сделана так. Теперь нужно к ней прикрутить учет вот таких корректировок. Вот как раз корректировка у меня и не вписывается только в аналитику - либо рекурсивный запрос, либо моделька. Но в ту же модельку например у меня не получается, чтоб проталкивался предикат, когда на вью накладывают фильтры
...
Рейтинг: 0 / 0
31.01.2018, 13:50
| Ответить | Цитировать | Написать  
Можно ли аналитикой?
    #39593934
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
Dimitry Sibiryakovj2kсроки на сколько просрочен тот или иной платеж.

Это период в который сумма с нарастающим итогом была отрицательной. Да, тривиально
делается аналитикой .

можно код глянуть?

.....
stax
...
Рейтинг: 0 / 0
31.01.2018, 13:51
| Ответить | Цитировать | Написать  
Можно ли аналитикой?
    #39593939
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimitry Sibiryakov
Участник
Staxможно код глянуть?

Как-то так:
Код: sql
1.
2.
3.
select * from
(select sum(val) over (order by rn) s, rn-lag(rn) over (order by rn) from t)
where s < 0


Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
31.01.2018, 13:57
| Ответить | Цитировать | Написать  
Можно ли аналитикой?
    #39593951
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
Dimitry Sibiryakov,
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
SQL> with t as ( select 1 rn, 100 val from dual
  2     union all select 2 rn, 300 val from dual
  3     union all select 3 rn, 200 val from dual
  4     union all select 4 rn, 100 val from dual
  5     union all select 5 rn, -350 val from dual
  6     union all select 6 rn, 100 val from dual
  7     union all select 7 rn, 100 val from dual
  8     union all select 8 rn, -300 val from dual
  9     union all select 9 rn, 800 val from dual
 10     union all select 10 rn, -600 val from dual
 11   )
 12  select * from
 13  (select sum(val) over (order by rn) s, rn-lag(rn) over (order by rn) from t)
 14  where s < 0
 15  /

no rows selected



.....
stax
...
Рейтинг: 0 / 0
31.01.2018, 14:07
| Ответить | Цитировать | Написать  
Можно ли аналитикой?
    #39594002
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimitry Sibiryakov
Участник
Staxno rows selected

И это правильно, поскольку в приведённом примере баланс никогда не уходил в минус, не было
просроченных платежей.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
31.01.2018, 14:31
| Ответить | Цитировать | Написать  
Можно ли аналитикой?
    #39594028
j2k
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
j2k
Участник
Dimitry Sibiryakov, вы какую-то другую задачу решаете. В приведенном мной примере отражены только фактические платежи, отдельно только по ним невозможно определить погашен платеж полностью или нет (т.к. неизвестно сколько нужно было по плану и когда)
...
Рейтинг: 0 / 0
31.01.2018, 14:48
| Ответить | Цитировать | Написать  
Можно ли аналитикой?
    #39594206
MazoHist
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MazoHist
Участник
Сделал без рекурсии и модели. Не будет работать если подряд пойдут две суммы с минусами
Код: 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 1 rn, 100 val from dual
  union all select 2 rn, 300 val from dual
  union all select 3 rn, 200 val from dual
  union all select 4 rn, 100 val from dual
  union all select 5 rn, -350 val from dual
  union all select 6 rn, 100 val from dual
  union all select 7 rn, 100 val from dual
  union all select 8 rn, -300 val from dual
  union all select 9 rn, 800 val from dual
  union all select 10 rn, -600 val from dual
      )
select rn
     , val
     , least( greatest(val,0), greatest(sum(val) over (partition by g3 order by rn desc),0) ) p
  from (select rn
             , val
             , gx
             , nvl(gx, lead(gx ignore nulls) over (order by rn desc)) g3
          from (select rn
                     , val
                     , sum(val) over (partition by grp order by rn desc) s2 
                     , case when sum(val) over (partition by grp order by rn desc) >= 0 then grp else null end gx
                  from (select rn
                             , val
                             , count(case when sign(val) < 0 then 1 end) over (order by rn desc) grp
                          from t 
                       )
               )
       )
...
Рейтинг: 0 / 0
31.01.2018, 17:25
| Ответить | Цитировать | Написать  
Можно ли аналитикой?
    #39594215
MazoHist
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MazoHist
Участник
MazoHistНе будет работать если подряд пойдут две суммы с минусами
, если их сумма окажется больше положительной суммы, которую они гасят.
...
Рейтинг: 0 / 0
31.01.2018, 17:29
| Ответить | Цитировать | Написать  
Можно ли аналитикой?
    #39594487
MaximaXXL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MaximaXXL
Участник
MazoHist,

А чего так сложно?
Если без рекурсии, хотя она сюда просто просилась после объяснения ... а то тестить сложно, надо тесткейсы писать =)
nakop, я прикинул будет так:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
with t as ( select 1 rn, 100 val from dual
  union all select 2 rn, 300 val from dual
  union all select 3 rn, 200 val from dual
  union all select 4 rn, 100 val from dual
  union all select 5 rn, -350 val from dual
  union all select 6 rn, 100 val from dual
  union all select 7 rn, 100 val from dual
  union all select 8 rn, -300 val from dual
  union all select 9 rn, 800 val from dual
  union all select 10 rn, -600 val from dual
   -- union all select 11 rn, 1600 val from dual             
            )

  select rn, val, sm - greatest(max(sm) over (order by rn desc),0) nakop
  from(
    select rn, val, sum(val) over (order by rn desc) sm
    from t
  )
order by rn
...
Рейтинг: 0 / 0
31.01.2018, 23:30
| Ответить | Цитировать | Написать  
Можно ли аналитикой?
    #39594500
MaximaXXL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MaximaXXL
Участник
MaximaXXL,

Прикрутил newval, но надо тестить рекурсия в этом случае проще (нагляднее)

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
with t as ( select 1 rn, 100 val from dual
  union all select 2 rn, 300 val from dual
  union all select 3 rn, 200 val from dual
  union all select 4 rn, 100 val from dual
  union all select 5 rn, -350 val from dual
  union all select 6 rn, 100 val from dual
  union all select 7 rn, 100 val from dual
  union all select 8 rn, -300 val from dual
  union all select 9 rn, 800 val from dual
  union all select 10 rn, -600 val from dual
   -- union all select 11 rn, 1600 val from dual             
            )

select rn, val, nakop, greatest(0,val + lag(nakop) over (order by rn desc)) newval
from (
  select rn, val, sm - greatest(max(sm) over (order by rn desc),0) nakop
  from(
    select rn, val, sum(val) over (order by rn desc) sm
    from t
      )
    )
order by rn
...
Рейтинг: 0 / 0
31.01.2018, 23:56
| Ответить | Цитировать | Написать  
Можно ли аналитикой?
    #39594514
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
j2k,

WINDOW SORT + MATCH RECOGNIZE SORT
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
select rn,
       val,
       least(sum(val) over(partition by grp order by rn desc), 0) nakop,
       greatest(sum(val) over(partition by grp order by rn desc), 0) new_val
  from t
match_recognize
( order by rn desc
  measures match_number() grp
  all rows per match
  pattern (x* y)
  define
    x as sum(x.val) <= 0
) mr
order by 1;
...
Рейтинг: 0 / 0
01.02.2018, 01:14
| Ответить | Цитировать | Написать  
Можно ли аналитикой?
    #39594523
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
MaximaXXLнадо теститьУ тебя небольшой косяк в вычислении второй колонки.

 
заполнение таблицы
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
exec dbms_random.seed(666);

create table j2k as
select rn, decode(s, 1, 2 * v, -1 * v) val
  from (select rownum rn,
               trunc(dbms_random.value(1, 3 + 1)) s,
               trunc(dbms_random.value(1, 1000 + 1)) v
          from dual
        connect by rownum <= 1e6);


quick perf 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.
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.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
SQL> alter session set workarea_size_policy = manual;

Session altered.

SQL> alter session set sort_area_size = 2147483647;

Session altered.

SQL> alter session set statistics_level = all;

Session altered.

SQL> set lines 120 pages 120
SQL> column s1 format 999999999999
SQL>
SQL> select sum(nakop) s1, sum(newval) s2 from
  2  (
  3  select rn,
  4         val,
  5         nakop,
  6         greatest(0, val + lag(nakop) over(order by rn desc)) newval
  7    from (select rn,
  8                 val,
  9                 sm - greatest(max(sm) over(order by rn desc), 0) nakop
 10            from (select rn, val, sum(val) over(order by rn desc) sm from j2k))
 11  );

           S1         S2
------------- ----------
-373544046284    1443157

SQL> select * from table(dbms_xplan.display_cursor(null,null,'IOSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  cd053zwpftpgr, child number 1
-------------------------------------
select sum(nakop) s1, sum(newval) s2 from ( select rn,        val,
  nakop,        greatest(0, val + lag(nakop) over(order by rn desc))
newval   from (select rn,                val,                sm -
greatest(max(sm) over(order by rn desc), 0) nakop           from
(select rn, val, sum(val) over(order by rn desc) sm from j2k)) )

Plan hash value: 2520631841

-------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |      1 |        |      1 |00:00:04.61 |    2038 |
|   1 |  SORT AGGREGATE          |      |      1 |      1 |      1 |00:00:04.61 |    2038 |
|   2 |   VIEW                   |      |      1 |   1000K|   1000K|00:00:04.47 |    2038 |
|   3 |    WINDOW BUFFER         |      |      1 |   1000K|   1000K|00:00:04.09 |    2038 |
|   4 |     VIEW                 |      |      1 |   1000K|   1000K|00:00:02.98 |    2038 |
|   5 |      WINDOW BUFFER       |      |      1 |   1000K|   1000K|00:00:02.67 |    2038 |
|   6 |       VIEW               |      |      1 |   1000K|   1000K|00:00:01.56 |    2038 |
|   7 |        WINDOW SORT       |      |      1 |   1000K|   1000K|00:00:01.42 |    2038 |
|   8 |         TABLE ACCESS FULL| J2K  |      1 |   1000K|   1000K|00:00:00.08 |    2038 |
-------------------------------------------------------------------------------------------


24 rows selected.

SQL>
SQL> select sum(nakop) s1, sum(newval) s2 from
  2  (
  3  select rn,
  4         val, grp,
  5         least(sum(val) over(partition by grp order by rn desc), 0) nakop,
  6         greatest(sum(val) over(partition by grp order by rn desc), 0) newval
  7    from j2k
  8  match_recognize
  9  ( order by rn desc
 10    measures match_number() grp
 11    all rows per match
 12    pattern (x* y)
 13    define
 14      x as sum(x.val) <= 0
 15  ) mr
 16  );

           S1         S2
------------- ----------
-373544046284    1444591

SQL> select * from table(dbms_xplan.display_cursor(null,null,'IOSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  5w4vs0ch3r3sw, child number 1
-------------------------------------
select sum(nakop) s1, sum(newval) s2 from ( select rn,        val, grp,
       least(sum(val) over(partition by grp order by rn desc), 0)
nakop,        greatest(sum(val) over(partition by grp order by rn
desc), 0) newval   from j2k match_recognize ( order by rn desc
measures match_number() grp   all rows per match   pattern (x* y)
define     x as sum(x.val) <= 0 ) mr )

Plan hash value: 2436773399

--------------------------------------------------------------------------------------------
| Id  | Operation                 | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |      1 |        |      1 |00:00:03.41 |    2038 |
|   1 |  SORT AGGREGATE           |      |      1 |      1 |      1 |00:00:03.41 |    2038 |
|   2 |   VIEW                    |      |      1 |   1000K|   1000K|00:00:03.29 |    2038 |
|   3 |    WINDOW SORT            |      |      1 |   1000K|   1000K|00:00:02.97 |    2038 |
|   4 |     VIEW                  |      |      1 |   1000K|   1000K|00:00:01.71 |    2038 |
|   5 |      BUFFER SORT          |      |      1 |   1000K|   1000K|00:00:01.58 |    2038 |
|   6 |       MATCH RECOGNIZE SORT|      |      1 |   1000K|   1000K|00:00:01.26 |    2038 |
|   7 |        TABLE ACCESS FULL  | J2K  |      1 |   1000K|   1000K|00:00:00.08 |    2038 |
--------------------------------------------------------------------------------------------


24 rows selected.

SQL>
SQL> select sum(nakop) s1, sum(newval) s2 from
  2  (
  3  select val, nakop, greatest(newval,0) newval from j2k
  4  model
  5  dimension by (row_number() over (order by rn desc) as i)
  6  measures (val, 0 as nakop, 0 as newval)
  7  ignore nav
  8  rules(nakop[any] order by i = least(nakop[cv()-1] + val[cv()], 0),
  9        newval[any] = nakop[cv()-1] + val[cv()]
 10        )
 11  );

           S1         S2
------------- ----------
-373544046284    1444591

SQL> select * from table(dbms_xplan.display_cursor(null,null,'IOSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  43v8ugs5dds16, child number 1
-------------------------------------
select sum(nakop) s1, sum(newval) s2 from ( select val, nakop,
greatest(newval,0) newval from j2k model dimension by (row_number()
over (order by rn desc) as i) measures (val, 0 as nakop, 0 as newval)
ignore nav rules(nakop[any] order by i = least(nakop[cv()-1] +
val[cv()], 0),       newval[any] = nakop[cv()-1] + val[cv()]       ) )

Plan hash value: 3756361718

----------------------------------------------------------------------------------------
| Id  | Operation             | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |      1 |        |      1 |00:00:08.40 |    2038 |
|   1 |  SORT AGGREGATE       |      |      1 |      1 |      1 |00:00:08.40 |    2038 |
|   2 |   VIEW                |      |      1 |   1000K|   1000K|00:00:08.28 |    2038 |
|   3 |    SQL MODEL ORDERED  |      |      1 |   1000K|   1000K|00:00:08.09 |    2038 |
|   4 |     WINDOW SORT       |      |      1 |   1000K|   1000K|00:00:00.87 |    2038 |
|   5 |      TABLE ACCESS FULL| J2K  |      1 |   1000K|   1000K|00:00:00.09 |    2038 |
----------------------------------------------------------------------------------------


21 rows selected.

SQL>
...
Рейтинг: 0 / 0
01.02.2018, 01:52
| Ответить | Цитировать | Написать  
Можно ли аналитикой?
    #39594537
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
dbms_photoshopj2k,

WINDOW SORT + MATCH RECOGNIZE SORT
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
select rn,
       val,
       least(sum(val) over(partition by grp order by rn desc), 0) nakop,
       greatest(sum(val) over(partition by grp order by rn desc), 0) new_val
  from t
match_recognize
( order by rn desc
  measures match_number() grp
  all rows per match
  pattern (x* y)
  define
    x as sum(x.val) <= 0
) mr
order by 1;

Аналитику натягивать нет необходимости. Вот это будет еще в два раза быстрее (и самое прозрачное решение имхо).
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
select rn,
       val,
       least(s, 0) nakop,
       greatest(s, 0) newval
  from t
match_recognize
( order by rn desc
  measures sum(val) s
  all rows per match
  pattern (x* y)
  define
    x as sum(x.val) <= 0
) mr
order by rn;

Небольшая проблемка с order by только.
Код: 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.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
SQL> with t as ( select 1 rn, 100 val from dual
  2    union all select 2 rn, 300 val from dual
  3    union all select 3 rn, 200 val from dual
  4    union all select 4 rn, 100 val from dual
  5    union all select 5 rn, -350 val from dual
  6    union all select 6 rn, 100 val from dual
  7    union all select 7 rn, 100 val from dual
  8    union all select 8 rn, -300 val from dual
  9    union all select 9 rn, 800 val from dual
 10    union all select 10 rn, -600 val from dual)
 11  select rn,
 12         val,
 13         least(s, 0) nakop,
 14         greatest(s, 0) newval
 15    from t
 16  match_recognize
 17  ( order by rn desc
 18    measures sum(val) s
 19    all rows per match
 20    pattern (x* y)
 21    define
 22      x as sum(x.val) <= 0
 23  ) mr
 24  order by rn;

        RN        VAL      NAKOP     NEWVAL
---------- ---------- ---------- ----------
        10       -600       -600          0
         9        800          0        200
         8       -300       -300          0
         7        100       -200          0
         6        100       -100          0
         5       -350       -450          0
         4        100       -350          0
         3        200       -150          0
         2        300          0        150
         1        100          0        100

10 rows selected.

SQL> with t as ( select 1 rn, 100 val from dual
  2    union all select 2 rn, 300 val from dual
  3    union all select 3 rn, 200 val from dual
  4    union all select 4 rn, 100 val from dual
  5    union all select 5 rn, -350 val from dual
  6    union all select 6 rn, 100 val from dual
  7    union all select 7 rn, 100 val from dual
  8    union all select 8 rn, -300 val from dual
  9    union all select 9 rn, 800 val from dual
 10    union all select 10 rn, -600 val from dual)
 11  select rn,
 12         val,
 13         least(s, 0) nakop,
 14         greatest(s, 0) newval
 15    from t
 16  match_recognize
 17  ( order by rn desc
 18    measures sum(val) s
 19    all rows per match
 20    pattern (x* y)
 21    define
 22      x as sum(x.val) <= 0
 23  ) mr
 24  order by rn + 0;

        RN        VAL      NAKOP     NEWVAL
---------- ---------- ---------- ----------
         1        100          0        100
         2        300          0        150
         3        200       -150          0
         4        100       -350          0
         5       -350       -450          0
         6        100       -100          0
         7        100       -200          0
         8       -300       -300          0
         9        800          0        200
        10       -600       -600          0

10 rows selected.
...
Рейтинг: 0 / 0
01.02.2018, 03:06
| Ответить | Цитировать | Написать  
Можно ли аналитикой?
    #39594619
j2k
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
j2k
Участник
dbms_photoshop, к сожалению пока только 11 версия. Я уже понял, что просто не получится, поэтому пошли другим путем. Всем спасибо.
...
Рейтинг: 0 / 0
01.02.2018, 09:36
| Ответить | Цитировать | Написать  
Можно ли аналитикой?
    #39595034
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
j2k,

Трехслойный вариант XXL допиливается до двухслойного,
хотя по читабельности, конечно, значительно уступает pattern matching/model.
...
Рейтинг: 0 / 0
01.02.2018, 16:08
| Ответить | Цитировать | Написать  
25 сообщений из 25, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Можно ли аналитикой?
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&msg=39594537&tid=1884493]:
 0ms
get settings:
 10ms
get forum list:
 17ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 100ms
get topic data:
 15ms
get forum data:
 4ms
get page messages:
 79ms
get tp. blocked users:
 2ms
others: 223ms
total:  456ms
созд. / загр.: 456ms
x
x
Закрыть


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