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

Conditional SUM in Oracle

When working with numeric data in Oracle, it often becomes necessary to perform conditional calculations based on certain criteria. One such requirement is to execute a SUM operation, but with a condition to reset the sum after it exceeds a specific threshold.

Problem:

Consider the following data table:

A	B
3	7
7	10
6	16
5	5
9	14
3	17
8	8

The objective is to perform a SUM operation on column 'B', but reset the sum to zero whenever it exceeds a value of 15. The desired output should appear as:

A	B	Sum
3	3	3
7	10	10
6	16	16
5	5	5
9	14	14
3	17	17
8	8	8

Solution 1: Recursive SQL

WITH recursive_sums AS (
    SELECT A, B, B AS SUM_VALUE
    FROM data_table
    UNION ALL
    SELECT A, B, CASE WHEN SUM_VALUE + B < 15 THEN SUM_VALUE + B ELSE 0 END AS SUM_VALUE
    FROM recursive_sums
    WHERE A < (SELECT MAX(A) FROM data_table)
)
SELECT A, B, SUM_VALUE AS Sum
FROM recursive_sums
ORDER BY A;

Solution 2: SQL MODEL Clause

WITH sorted_inputs AS (
    SELECT A, ROW_NUMBER() OVER (ORDER BY A) AS sort_order, B, 0 AS running_sum_max_15
    FROM data_table
),
sorted_results AS (
    SELECT A, B, running_sum_max_15
    FROM sorted_inputs
    MODEL
    DIMENSION BY (sort_order)
    MEASURES (A, B, running_sum_max_15)
    RULES UPDATE
    (
        running_sum_max_15[1] = B[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 + B[CV(sort_order)]
    )
)
SELECT A, B, running_sum_max_15 AS Sum
FROM sorted_results
ORDER BY A;

Both these solutions effectively perform the conditional SUM operation, resetting the sum to zero whenever it exceeds the threshold of 15.

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