Гость
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Running total с отсечением / 4 сообщений из 4, страница 1 из 1
22.12.2020, 18:28
    #40030291
PuM256
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Running total с отсечением
Возникла "полупятничная" задачка - суммирование с накоплением вроде sum() over(order by), только если результат становится отрицательным, ставим 0 и со следующей строки продолжаем суммирование с нуля. Долго не думал, сделал с помощью MODEL. А какие ещё есть варианты?

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
with t(id, n) as 
(
    select 1, 1 from dual
    union all select 2, 2 from dual 
    union all select 3, -5 from dual
    union all select 4, 6 from dual
)
select * from t
model 
    dimension by (id)
    measures (n, 1 s)
    rules
    (
        s[1] = n[1],
        s[id>1] = case when s[CV()-1] + n[CV()] > 0 then  s[CV()-1] + n[CV()] else 0 end
    );



Код: plaintext
1.
2.
3.
4.
5.
6.
        ID          N          S
---------- ---------- ----------
         1          1          1
         2          2          3
         3         -5          0
         4          6          6
...
Рейтинг: 0 / 0
22.12.2020, 18:38
    #40030294
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Running total с отсечением
STFF reset running total
...
Рейтинг: 0 / 0
22.12.2020, 22:31
    #40030325
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Running total с отсечением
Код: 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(id, n) as 
(
    select 1, 1 from dual
    union all select 2, 2 from dual 
    union all select 3, -5 from dual
    union all select 4, 6 from dual
)
select  m.id,
        m.n,
        m.s
  from  t
  match_recognize(
                  order by id
                  measures
                    greatest(sum(n),0) s
                  all rows per match
                  pattern(r+)
                  define r as sum(n) - n >= 0
                 ) m
/

        ID          N          S
---------- ---------- ----------
         1          1          1
         2          2          3
         3         -5          0
         4          6          6

SQL>



SY.
...
Рейтинг: 0 / 0
23.12.2020, 12:39
    #40030423
PuM256
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Running total с отсечением
Спасибо за ключевые слова.
Сейчас понял, что с моделью я перемудрил, тут и recursive CTE достаточно:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
with t(id, n) as 
(
    select 1, 1 from dual
    union all select 2, 2 from dual 
    union all select 3, -5 from dual
    union all select 4, 6 from dual
),
r(id, n, s) as
(
select id, n, case when n > 0 then n else 0 end from t where id = 1
union all
select t.id, t.n, case when r.s+t.n > 0 then r.s+t.n else 0 end from t, r where r.id = t.id - 1 
)
select * from r


А вариант с match_recognize так вообще крутой. И в нём нет бага, который есть в моей модели, который вылазит, если в первой записи указать отрицательное число. Поправить несложно, конечно.
...
Рейтинг: 0 / 0
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Running total с отсечением / 4 сообщений из 4, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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