Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Можно ли аналитикой? / 25 сообщений из 25, страница 1 из 1
30.01.2018, 13:58
    #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, 15:45
    #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:49
    #39593421
MaximaXXL
MaximaXXL
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Можно ли аналитикой?
j2k,

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

почему
300 0 150 ?

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

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

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

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

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

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

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

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

И это правильно, поскольку в приведённом примере баланс никогда не уходил в минус, не было
просроченных платежей.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
31.01.2018, 14:48
    #39594028
j2k
j2k
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Можно ли аналитикой?
Dimitry Sibiryakov, вы какую-то другую задачу решаете. В приведенном мной примере отражены только фактические платежи, отдельно только по ним невозможно определить погашен платеж полностью или нет (т.к. неизвестно сколько нужно было по плану и когда)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
31.01.2018, 17:25
    #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:29
    #39594215
MazoHist
MazoHist
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Можно ли аналитикой?
MazoHistНе будет работать если подряд пойдут две суммы с минусами
, если их сумма окажется больше положительной суммы, которую они гасят.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
31.01.2018, 23:30
    #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:56
    #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
| Ответить | Цитировать | Написать  
01.02.2018, 01:14
    #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:52
    #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, 03:06
    #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, 09:36
    #39594619
j2k
j2k
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Можно ли аналитикой?
dbms_photoshop, к сожалению пока только 11 версия. Я уже понял, что просто не получится, поэтому пошли другим путем. Всем спасибо.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
01.02.2018, 16:08
    #39595034
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Можно ли аналитикой?
j2k,

Трехслойный вариант XXL допиливается до двухслойного,
хотя по читабельности, конечно, значительно уступает pattern matching/model.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Можно ли аналитикой? / 25 сообщений из 25, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 332ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1884493]:
 0ms
get settings:
 8ms
get forum list:
 13ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 35ms
get topic data:
 7ms
get forum data:
 2ms
get page messages:
 38ms
get tp. blocked users:
 1ms
others: 224ms
total:  332ms
x
x
Закрыть


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