위 구성으로 된 테이블이 있습니다.
전체 COUNT가 데이터 쌓임에 따라 누적이 되고 있는데
TYPE에 따라 별도로 누적값을 새로 쌓을려고 합니다.
아래와 같이 표현하려 해요
그런데 문제가, 가능한 서브쿼리를 통해서 해결해야 하는데, 도움을 요청드립니다.
서브쿼리로 많이 번거롭다면 다른 방법도 좋습니다.
Comment 5
-
fawkjfa
2022.12.07 15:38
-
문과장
2022.12.15 10:44
CREATE TABLE inc_tb
(TYP varchar2(10), cnt NUMBER);
INSERT INTO inc_tb
values('AAA',900);
INSERT INTO inc_tb
values('AAA',800);
INSERT INTO inc_tb
values('AAA',700);
INSERT INTO inc_tb
values('BBB',700);
INSERT INTO inc_tb
values('BBB',600);
INSERT INTO inc_tb
values('BBB',500);
INSERT INTO inc_tb
values('CCC',400);
INSERT INTO inc_tb
values('CCC',300);
INSERT INTO inc_tb
values('CCC',200);
INSERT INTO inc_tb
values('CCC',100);
COMMIT;
SELECT rownum, TYP, cnt, sum(100) OVER(partition BY typ ORDER BY cnt DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) sum_typ_cnt FROM inc_tb ORDER BY rownum desc;
cmd_comment_vote_user Upvote0 Downvote0 Comment Update Delete
-
문부장
2022.12.15 11:41
아니요
-
문부장
2022.12.15 11:58
with TYPE_SUM as (
select 9 as [R_NUM], 'AAA' as [TYP], 900 as [CNT] union all
select 8,'AAA',800 union all
select 7,'BBB',700 union all
select 6,'BBB',600 union all
select 5,'BBB',500 union all
select 4,'CCC',400 union all
select 3,'CCC',300 union all
select 2,'CCC',200 union all
select 1,'CCC',100
)
SELECT B.R_NUM AS [R_NUM]
,B.TYP AS [TYP]
,B.CNT AS [CNT]
,SUM(B.T_SUM) OVER(PARTITION BY B.TYP ORDER BY R_NUM) AS [T_SUM]
FROM (
SELECT A.R_NUM AS [R_NUM]
,A.TYP AS [TYP]
,A.CNT AS [CNT]
,ISNULL(A.CNT - LEAD(A.CNT) OVER(PARTITION BY A.TYP ORDER BY A.R_NUM DESC, A.TYP), 100) AS [T_SUM]
FROM TYPE_SUM A
) B
ORDER BY [R_NUM] DESC=> 원하시는게 이거일까요?
-
문과장
2022.12.15 12:57
SELECT
rowrn,
a.typ,
a.cnt,
a.cnt - b.cnt + 100 SUM_TYPE_COUNT
FROM
(
SELECT rownum rowrn, typ, cnt
FROM (SELECT typ, cnt
FROM inc_tb ORDER BY typ DESC , cnt )
ORDER BY
rowrn
) a,
(
SELECT
typ,
cnt,
rn
FROM
(
SELECT
typ,
cnt,
ROW_NUMBER() OVER (PARTITION BY typ
ORDER BY
cnt) rn
FROM
inc_tb )
WHERE rn = 1) b
WHERE
a.typ = b.typ
ORDER BY
rowrn DESC;
cmd_comment_vote_user Upvote0 Downvote0 Comment Update Delete
다음처럼 변경