데이터베이스 질문과 답변
| ROW | Col1 | Col2 | Col3 |
| 1 | A | 202306 | 5000 |
| 2 | B | 202306 | 2000 |
| 3 | C | 202306 | 50 |
해당 테이블에서 A+B를 더한 Col3값을 추가하고
총 4개의 ROW를 이용해 다시 A대비 (A+B), A대비 C의 비율을 각각 추가하여 표현하고 싶습니다.
| ROW | Col1 | Col2 | Col3 |
| 1 | A | 202306 | 5000 |
| 2 | B | 202306 | 2000 |
| 3 | C | 202306 | 50 |
| 4 | A+B | 202306 | 3000 |
| 5 | (A+B)/A | 202306 | 60% |
| 6 | C/A | 202606 | 1% |
기존의 데이터에 연산된 ROW를 추가하여 함께 보여줄 방법이 없을까요?
by 뉴비디비
[2023.06.16 18:44:41]
-- 결과 기준이면 'B'가 -2000 이 맞겠네요.
WITH tbl AS (
select 1 as vRow, 'A' as vCol1, '202306' as vCOl2, 5000 as vCol3 from Dual union all
select 2 as vRow, 'B' as vCol1, '202306' as vCOl2, -2000 as vCol3 from Dual union all
select 3 as vRow, 'C' as vCol1, '202306' as vCOl2, 50 as vCol3 from Dual
)
SELECT
ROW_NUMBER() over(ORDER BY A.vRow, B.vRow) AS colRow
, CASE WHEN A.vRow IN (1) THEN B.vCol1
WHEN ROW_NUMBER() over(ORDER BY A.vRow, B.vRow) = 4 THEN 'A+B'
WHEN ROW_NUMBER() over(ORDER BY A.vRow, B.vRow) = 5 THEN '(A+B)/A'
WHEN ROW_NUMBER() over(ORDER BY A.vRow, B.vRow) = 6 THEN 'C/A'
END col1
, B.vCOl2 col2
, CASE WHEN A.vRow IN (1) THEN TO_CHAR(B.vCol3)
WHEN ROW_NUMBER() over(ORDER BY A.vRow, B.vRow) = 4 THEN TO_CHAR(B.vCol3 + A.vCol3)
WHEN ROW_NUMBER() over(ORDER BY A.vRow, B.vRow) = 5 THEN TO_CHAR((B.vCol3 + lag(B.vCol3) over(ORDER BY A.vRow, B.vRow))/lag(B.vCol3) over(ORDER BY A.vRow, B.vRow)*100)||'%'
WHEN ROW_NUMBER() over(ORDER BY A.vRow, B.vRow) = 6 THEN TO_CHAR((A.vCol3 / B.vCol3)*100)||'%'
END Col3
FROM tbl A, tbl B
WHERE A.vRow IN (1) OR ( A.vCol1 = 'B' AND B.vCol1 IN ('A', 'B') ) OR (A.vCol1 = 'C' AND B.vCol1 = 'A')
) 버튼을 클릭하여 작성 하시면 됩니다.