데이터베이스 질문과 답변
WITH A AS (
select 1 as d_ord, 16 as val from dual
union all
select 2 as d_ord, 17 as val from dual
union all
select 3 as d_ord, 18 as val from dual
union all
select 4 as d_ord, 19 as val from dual
union all
select 5 as d_ord, 20 as val from dual
)
select * from A;
위 같은 데이터가 있다고 가정했을 경우 아래와 같은 결과를 얻고 싶은데...
RATE 값은 SUM(VAL) 값 중 해당 ORD가 차지하는 누적비율(?)을 의미합니다.
예를 들어서 2라인의 37은 아래와 같은 공식으로 나온 값입니다.
ROUND((16 + 17) / (16+17+18+19+20) * 100, 0) = 37
D_ORD VAL RATE
1 16 18
2 17 37
3 18 57
4 19 78
5 20 100
해당 row와 이전 row의 값을 누적하려니까.. 어떻게 누적을 해야할지 모르겠네요. ㅠㅠ
고수님들의 도움 부탁드립니다.
by 우리집아찌
[2018.03.26 19:27:08]
WITH A AS (
select 1 as d_ord, 16 as val from dual
union all
select 2 as d_ord, 17 as val from dual
union all
select 3 as d_ord, 18 as val from dual
union all
select 4 as d_ord, 19 as val from dual
union all
select 5 as d_ord, 20 as val from dual
)
SELECT D_ORD
, VAL
, ROUND(SUM(VAL) OVER(ORDER BY D_ORD) / SUM(VAL) OVER() * 100) AS RATE
FROM A;
) 버튼을 클릭하여 작성 하시면 됩니다.