How to Perform Conditional Summation in Oracle with a Resetting Threshold?

Conditional SUMming in Oracle: Resetting Values When the Limit Exceeds 15

In Oracle, you may encounter scenarios where you need to conditionally sum values and reset the sum when it reaches a specific threshold. For instance, suppose you have data represented in a table as follows:

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

Objective: Write a query that calculates the running sum of values, resetting it to zero whenever the sum exceeds 15.

Solution Using the SQL MODEL Clause

An alternative to recursive SQL for this problem is to employ the SQL MODEL clause. The syntax may seem more complex, but it can offer clarity in some cases:

-- Create a temporary table for demonstration purposes
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 ),

-- Implementation of the conditional summation using MODEL clause
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 |
+----------+--------+--------------------+

This query effectively performs the conditional summation, resetting the running sum to zero whenever it exceeds the threshold of 15.

The above is the detailed content of How to Perform Conditional Summation in Oracle with a Resetting Threshold?. For more information, please follow other related articles on the PHP Chinese website!