How to Reset a Cumulative Sum in Oracle Based on a Condition?

Conditional Summing in Oracle

In Oracle, conditional summation refers to performing a cumulative sum operation while resetting the accumulated value based on a specified condition. This technique allows you to manipulate data in a way where the sum resets to a specific value upon meeting a certain threshold.

To achieve this, you can leverage the SQL MODEL clause. This clause enables you to define a hierarchical data model and specify calculations based on that model.

Consider the following example where you want to calculate a cumulative sum that resets every time it exceeds 15:

with test_data (sort_col, addend) as
(
  SELECT 'A', 3 FROM DUAL UNION ALL
  SELECT 'B', 7 FROM DUAL UNION ALL
  SELECT 'C', 6 FROM DUAL UNION ALL
  SELECT 'D', 5 FROM DUAL UNION ALL
  SELECT 'E', 9 FROM DUAL UNION ALL
  SELECT 'F', 3 FROM DUAL UNION ALL
  SELECT 'G', 8 FROM DUAL
),
sorted_inputs (sort_col, sort_order, addend, running_sum_max_15) as
(
  SELECT sort_col, row_number() over (order by sort_col) sort_order, addend, 0 from test_data
)
SELECT sort_col, addend, running_sum_max_15
from sorted_inputs
model 
dimension by (sort_order)
measures (sort_col, addend, running_sum_max_15)
rules update
(
  running_sum_max_15[1] = addend[1],
  running_sum_max_15[sort_order>1] = 
    case 
      when running_sum_max_15[CV(sort_order)-1] < 15 
      THEN running_sum_max_15[CV(sort_order)-1] 
      ELSE 0 END + addend[CV(sort_order)]
)

Results:

SORT_COL	ADDEND	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

As you can see, the running sum resets to 0 after exceeding 15 for rows 'C' and 'F'. This allows you to perform conditional summation based on specific criteria defined within your query.

The above is the detailed content of How to Reset a Cumulative Sum in Oracle Based on a Condition?. For more information, please follow other related articles on the PHP Chinese website!