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!