Oracle의 조건부 SUM

Oracle의 조건부 SUM

조건부 SUM으로 질문을 하려고 합니다.SUM은 재설정 후 15개 이상을 받아야 합니다.다음과 같은 경우:

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보다 조금 읽기 쉽다고 생각합니다. (저와 같은 대부분의 사람들은 구문을 찾아야 하기 때문에) 쓰기는 더 어렵습니다.

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

재귀적 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;

실제 쿼리:

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;

출력:

┌──────┬──────┬───────┐
│ COL1 │ COL2 │ TOTAL │
├──────┼──────┼───────┤
│ A    │    3 │     3 │
│ B    │    7 │    10 │
│ C    │    6 │    16 │
│ D    │    5 │     5 │
│ E    │    9 │    14 │
│ F    │    3 │    17 │
│ G    │    8 │     8 │
└──────┴──────┴───────┘

db<> fid 데모

---

이 솔루션은 Oracle에만 국한되는 것이 아니라 다른 RDB에서도 작동합니다.SQL Server/PostgreSQL/MySQL 8.0/SQLite 3.25와 같은 MS.

db<>fiddle 데모 - PostgreSQL

재귀적 CTE보다 원하는 결과를 훨씬 쉽게 얻을 수 있습니다.

Oracle 12c 지원MATCH_RECOGNIZE"빈 피팅" 문제를 해결하는 데 적합합니다.

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<> fid 데모

출력:

┌───────┬───────┬──────────────┬─────────┐
│ 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 │
└───────┴───────┴──────────────┴─────────┘

---

엑스트라: 다음과 같이 총 러닝타임 상한 설정MODEL

언급URL : https://stackoverflow.com/questions/52936012/conditional-sum-on-oracle