How can I implement a conditional SUM in Oracle that resets to zero when the running total exceeds 15?

Conditional SUM on Oracle: Resetting When Value Exceeds 15

In Oracle, you may encounter the need to perform a cumulative sum but restrict it to a maximum threshold. For example, you may want to calculate a running total that resets to zero whenever it exceeds a value of 15. This is known as a conditional SUM.

One approach to achieving this is through the use of a recursive SQL query, which allows you to iteratively update running totals based on a defined condition. However, an alternative method involves utilizing the Oracle SQL MODEL clause.

The SQL MODEL clause offers a more readable solution, albeit with a slightly more complex syntax. By creating a model based on the input data, you can specify rules to govern how running totals are calculated.

To implement this conditional SUM using the SQL MODEL clause:

1. Define a CTE (Common Table Expression) named sorted_inputs to rank the input data in ascending order.
2. Introduce a column named running_sum_max_15 to track the running total, initialized to 0 for all rows.

3. Use the RULES UPDATE clause to define the calculation rules:

   • For the first row, set running_sum_max_15 to addend.
   • For subsequent rows, check if the previous value of running_sum_max_15 is less than 15. If true, add the current addend to it; otherwise, reset the count to 0 and add the current addend.

The output of this query will provide you with a running total that resets to zero whenever it exceeds 15.

Example:

<code class="sql">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)]
);</code>

Output:

+----------+--------+--------------------+
| 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 |
+----------+--------+--------------------+

The above is the detailed content of How can I implement a conditional SUM in Oracle that resets to zero when the running total exceeds 15?. For more information, please follow other related articles on the PHP Chinese website!