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?
Conditional selection statements, which run different statements for different data values. The conditional selection statements are IF and CASE . Loop statements, which run the same statements with a series of different data values. The loop statements are the basic LOOP , FOR LOOP , and WHILE LOOP .
A CASE WHEN expression is often used with a SUM() function in more complex reports, which can be quite challenging for beginners. Even though you're probably used to using the SUM() function for summing values, it can also be used for counting. This example will help you understand the concept better.
Query 1 : Cumulative Sum in Oracle : You can calculate cumulative sum in Oracle SQL using straightforward function named Sum and order by together. If you want partitioned data then you can use partition by clause for the same.
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)]
)
+----------+--------+--------------------+
| 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 |
+----------+--------+--------------------+
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);
db<>fiddle demo
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With