I´m tring to make a query with a conditional SUM. The SUM needs to get more than 15, after that reset it. Like this:
A | 3 | 3
B | 7 | 10
C | 6 | 16 -- ====
D | 5 | 5
E | 9 | 14
F | 3 | 17 -- ====
G | 8 | 8
How can I make this?
3 Answers
As an alternative to recursive SQL, you can also use the SQL MODEL clause. Personally, I find this a little easier to read than recursive SQL, though it is harder to write (because most people, like me, need to look up the syntax).
-- "test_data" is just a substitute for your real table, which I don't have
-- it is just so people without your table can run this example and would
-- not be part of your real solution.
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 ),
-- Solution begins here
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 |
+----------+--------+--------------------+
2 Comments
Fábio Almeida
Thank you. I choose yours because it´s have the lower cost.
Lukasz Szozda
Nice, I used to like the best the way you calculate running total by using
MODEL clause. Please check my second approach with MATCH_RECOGNIZE.Using recursive cte:
DROP TABLE tab;
CREATE TABLE tab
AS
SELECT 'A' as col1, 3 AS col2 FROM dual UNION ALL
SELECT 'B' as col1, 7 AS col2 FROM dual UNION ALL
SELECT 'C' as col1, 6 AS col2 FROM dual UNION ALL
SELECT 'D' as col1, 5 AS col2 FROM dual UNION ALL
SELECT 'E' as col1, 9 AS col2 FROM dual UNION ALL
SELECT 'F' as col1, 3 AS col2 FROM dual UNION ALL
SELECT 'G' as col1, 8 AS col2 FROM dual;
Actual query:
WITH cte_r AS (
SELECT t.*, ROW_NUMBER() OVER(ORDER BY t.col1) AS rn FROM tab t
), cte(col1, col2, total, rn) AS (
SELECT col1, col2, col2 AS total, rn
FROM cte_r
WHERE rn = 1
UNION ALL
SELECT cte_r.col1, cte_r.col2,
CASE WHEN cte.total >= 15 THEN 0 ELSE cte.total END + cte_r.col2 AS total,
cte_r.rn
FROM cte
JOIN cte_r
ON cte.rn = cte_r.rn-1
)
SELECT col1, col2, total
FROM cte
ORDER BY rn;
Output:
┌──────┬──────┬───────┐
│ COL1 │ COL2 │ TOTAL │
├──────┼──────┼───────┤
│ A │ 3 │ 3 │
│ B │ 7 │ 10 │
│ C │ 6 │ 16 │
│ D │ 5 │ 5 │
│ E │ 9 │ 14 │
│ F │ 3 │ 17 │
│ G │ 8 │ 8 │
└──────┴──────┴───────┘
This solution is not limited to only Oracle but it will work on other RDBMSes such as SQL Server/PostgreSQL/MySQL 8.0/SQLite 3.25.
Comments
It is possible to achieve desired result much easier than recursive CTE.
Oracle 12c supports MATCH_RECOGNIZE and it is a good fit to solve "bin fitting" problem:
SELECT Col1, col2, rolling_sum, bin_num
FROM T
MATCH_RECOGNIZE (
ORDER BY col1
MEASURES SUM(col2) ROLLING_SUM, MATCH_NUMBER() AS bin_num
ALL ROWS PER MATCH
AFTER MATCH SKIP PAST LAST ROW
PATTERN ( A+ )
DEFINE A AS SUM(col2) < 15 + A.col2);
Output:
┌───────┬───────┬──────────────┬─────────┐
│ COL1 │ COL2 │ ROLLING_SUM │ BIN_NUM │
├───────┼───────┼──────────────┼─────────┤
│ A │ 3 │ 3 │ 1 │
│ B │ 7 │ 10 │ 1 │
│ C │ 6 │ 16 │ 1 │
│ D │ 5 │ 5 │ 2 │
│ E │ 9 │ 14 │ 2 │
│ F │ 3 │ 17 │ 2 │
│ G │ 8 │ 8 │ 3 │
└───────┴───────┴──────────────┴─────────┘
Extras: Capping a runnig total with MODEL
NOT NULL. Then, what should happen if there are duplicates in the first column (the ordering column)? Or is that also guaranteed not to happen?