Oracle 上的条件求和 [英] Conditional SUM on Oracle

问题描述

我正在尝试使用条件 SUM 进行查询.SUM 需要超过 15，然后重置它.像这样:

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

我该怎么做?

推荐答案

作为递归 SQL 的替代方案，您还可以使用 SQL MODEL 子句.就我个人而言，我觉得这比递归 SQL 更容易阅读，尽管它更难编写(因为大多数人，像我一样，需要查找语法).

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

这篇关于Oracle 上的条件求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！