Oracle でリセットしきい値を使用して条件付き SUM を計算する方法

<code class="sql">-- Create a table to represent the sample data
CREATE TABLE test_data ( sort_col VARCHAR2(1), addend NUMBER );
-- Insert sample data
INSERT INTO test_data (sort_col, addend) VALUES ('A', 3), ('B', 7), ('C', 6), ('D', 5), ('E', 9), ('F', 3), ('G', 8);

-- Begin the actual solution
WITH sorted_inputs AS (
  SELECT sort_col,
         ROW_NUMBER() OVER (ORDER BY sort_col) AS sort_order,
         addend,
         0 AS running_sum_max_15  -- Initialize running sum to zero
  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],  -- Set initial running sum to the first addend
  running_sum_max_15[sort_order > 1] =  -- Calculate running sum for subsequent rows
  CASE
    WHEN running_sum_max_15[CV(sort_order) - 1] < 15 THEN
      running_sum_max_15[CV(sort_order) - 1]  -- Continue running sum if below threshold
    ELSE
      0  -- Reset running sum if threshold is reached or exceeded
  END + addend[CV(sort_order)]  -- Add current addend to running sum
);</code>

<code class="sql">+----------+--------+--------------------+
| 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 |
+----------+--------+--------------------+</code>