Как функцией SUM вернуть накапливающуюся сумму с обнулением по условию?

Question

Пытаюсь сделать запрос с функцией SUM, возвращающий накапливающуюся сумму с обнулением по условию. Суммы должна быть более 15, после чего она обнуляется.

Например:

A | 3 | 3 
B | 7 | 10 
C | 6 | 16  --<<< 
D | 5 | 5 
E | 9 | 14
F | 3 | 17  --<<<
G | 8 | 8

Сортировка по первому столбцу, а обнуление, где показано маркером: --<<<.

Как это можно сделать?

_{Свободный перевод вопроса Conditional SUM on Oracle от участника @Fábio Almeida}

Answer 1

Как альтернатива рекурсивному SQL - использовать клаузу MODEL.
Лично считаю, что это немного проще для чтения, чем рекурсивный SQL, хотя и сложнее для написания, потому что большинству, как и мне, приходится подглядывать в синтаксис.

with sorted (a, sort, b, running_sum_max_15) as ( 
    select a, row_number() over (order by a), b, 0 
    from t)
select a, b, running_sum_max_15
from sorted
model 
    dimension by (sort)
    measures (a, b, running_sum_max_15)
    rules update ( 
        running_sum_max_15[1] = b[1],
        running_sum_max_15[sort>1] = 
            case when running_sum_max_15[CV(sort)-1] < 15 
                 then running_sum_max_15[CV(sort)-1] 
                 else 0 end + b[CV(sort)]
    );

Результат:

A B RUNNING_SUM_MAX_15
- - ------------------
A 3                  3
B 7                 10
C 6                 16
D 5                  5
E 9                 14
F 3                 17
G 8                  8

db<>fiddle

_{Свободный перевод ответа от участника @Matthew McPeak}

Answer 2

Достичь желаемого результата можно гораздо проще, чем рекурсивным CTE. Начиная с версии 12c введена клауза MATCH_RECOGNIZE, которая хорошо подходит для решения проблемы Bin Fitting:

SELECT a, b, rolling_sum, bin_num
FROM t
MATCH_RECOGNIZE (
    ORDER BY a
    MEASURES SUM(b) rolling_sum, MATCH_NUMBER() AS bin_num
    ALL ROWS PER MATCH
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A+)
    DEFINE A AS SUM(b) < 15 + A.b);

Результат:

A B  ROLLING_SUM  BIN_NUM
- - ------------ --------
A 3            3        1
B 7           10        1
C 6           16        1
D 5            5        2
E 9           14        2
F 3           17        2
G 8            8        3

db<>fiddle

_{Свободный перевод ответа от участника @Lukasz Szozda}

БЛОГ НА HUSL

Как функцией SUM вернуть накапливающуюся сумму с обнулением по условию?

Ответы (2 шт):