데이터베이스 질문과 답변
by 잼보야 [2016.06.10 15:56:26]
전년대비 증감율에 대한 쿼리문을 도움 받았는데도, 막상 적용하려니까 잘 모르겠어요.
염치없이 다시 질문을 드립니다.
WITH TAB1
AS (
SELECT '2015' AS YYYY, '서울' AS PROD, 189605 AS QTY FROM DUAL UNION ALL
SELECT '2015' AS YYYY, '경기' AS PROD, 211392 AS QTY FROM DUAL UNION ALL
SELECT '2015' AS YYYY, '부산' AS PROD, 618721 AS QTY FROM DUAL UNION ALL
SELECT '2015' AS YYYY, '대구' AS PROD, 86968 AS QTY FROM DUAL UNION ALL
SELECT '2015' AS YYYY, '인천' AS PROD, 29825 AS QTY FROM DUAL UNION ALL
SELECT '2015' AS YYYY, '광주' AS PROD, 49810 AS QTY FROM DUAL UNION ALL
SELECT '2015' AS YYYY, '대전' AS PROD, 144698 AS QTY FROM DUAL UNION ALL
SELECT '2015' AS YYYY, '울산' AS PROD, 307855 AS QTY FROM DUAL UNION ALL
SELECT '2016' AS YYYY, '서울' AS PROD, 376221 AS QTY FROM DUAL UNION ALL
SELECT '2016' AS YYYY, '경기' AS PROD, 223522 AS QTY FROM DUAL UNION ALL
SELECT '2016' AS YYYY, '부산' AS PROD, 430019 AS QTY FROM DUAL UNION ALL
SELECT '2016' AS YYYY, '대구' AS PROD, 180503 AS QTY FROM DUAL UNION ALL
SELECT '2016' AS YYYY, '인천' AS PROD, 32931 AS QTY FROM DUAL UNION ALL
SELECT '2016' AS YYYY, '광주' AS PROD, 149506 AS QTY FROM DUAL UNION ALL
SELECT '2016' AS YYYY, '대전' AS PROD, 221965 AS QTY FROM DUAL UNION ALL
SELECT '2016' AS YYYY, '울산' AS PROD, 260988 AS QTY FROM DUAL UNION ALL
SELECT '2016' AS YYYY, '충청' AS PROD, 0 AS QTY FROM DUAL
)
SELECT YYYY, PROD, QTY
FROM TAB1
;
YY PROD QTY
2015 서울 189,605
2015 경기 211,392
2015 부산 618,721
2015 대구 86,968
2015 인천 29,825
2015 광주 49,810
2015 대전 144,698
2015 울산 307,855
2016 서울 376,221
2016 경기 223,522
2016 부산 430,019
2016 대구 180,503
2016 인천 32,931
2016 광주 149,506
2016 대전 221,965
2016 울산 260,988
2016 충청
증감액 서울 186,616
증감액 경기 12,130
증감액 부산 - 188,702
증감액 대구 93,535
증감액 인천 3,106
증감액 광주 99,696
증감액 대전 77,267
증감액 울산 - 46,867
증감액 충청 -
위와같이 증감액을 보여줘야 하는데..
지역은 다른조건으로 바뀔 수 있습니다.(사용자가 지역별을 선택하면 위와같이 지역별 데이타가 나오고요, 담당별 선택하면 담당별 데이타가 조회가 되도록 만들 예정입니다. (PROD 항목의 값과 갯수는 변할 수 있습니다.))
1. 이런경우, 모델절을 어떻게 만들어야 될지 알려주세요.
2. 아래는 이전에 질문드렸던 글의 댓글내용을 응용하여 만들어본건데요,
ROWNUM을 만들고, ROWNUM값을 1부터 맥스까지 하나씩 증가시키려고 했는데 MAX값을 어떻게 표현해야 될지도 모르겠어요.
MODEL
DIMENSION BY (NVL(YY, '증감액') YY, ROWNUM )
MEASURES(QTY, PROD)
IGNORE NAV
RULES (
MM01['증감액', FOR ROWNUM FROM 1 TO MAX(???) INCREMENT 1] = MM01['2016', CV(RN)] - MM01['2015', CV(RN)] )
MAX(???) 이 부분에 어떤 값을 넣어야 되는지도 좀 부탁드립니다.
제가 지식이 부족해서 알려주셔도 제대로 적용을 못하네요. 혼자 고민하다가 다시 요청 드립니다.
감사합니다.
WITH TAB1 AS ( SELECT '2015' AS YYYY, '서울' AS PROD, 189605 AS QTY FROM DUAL UNION ALL SELECT '2015' AS YYYY, '경기' AS PROD, 211392 AS QTY FROM DUAL UNION ALL SELECT '2015' AS YYYY, '부산' AS PROD, 618721 AS QTY FROM DUAL UNION ALL SELECT '2015' AS YYYY, '대구' AS PROD, 86968 AS QTY FROM DUAL UNION ALL SELECT '2015' AS YYYY, '인천' AS PROD, 29825 AS QTY FROM DUAL UNION ALL SELECT '2015' AS YYYY, '광주' AS PROD, 49810 AS QTY FROM DUAL UNION ALL SELECT '2015' AS YYYY, '대전' AS PROD, 144698 AS QTY FROM DUAL UNION ALL SELECT '2015' AS YYYY, '울산' AS PROD, 307855 AS QTY FROM DUAL UNION ALL SELECT '2016' AS YYYY, '서울' AS PROD, 376221 AS QTY FROM DUAL UNION ALL SELECT '2016' AS YYYY, '경기' AS PROD, 223522 AS QTY FROM DUAL UNION ALL SELECT '2016' AS YYYY, '부산' AS PROD, 430019 AS QTY FROM DUAL UNION ALL SELECT '2016' AS YYYY, '대구' AS PROD, 180503 AS QTY FROM DUAL UNION ALL SELECT '2016' AS YYYY, '인천' AS PROD, 32931 AS QTY FROM DUAL UNION ALL SELECT '2016' AS YYYY, '광주' AS PROD, 149506 AS QTY FROM DUAL UNION ALL SELECT '2016' AS YYYY, '대전' AS PROD, 221965 AS QTY FROM DUAL UNION ALL SELECT '2016' AS YYYY, '울산' AS PROD, 260988 AS QTY FROM DUAL UNION ALL SELECT '2016' AS YYYY, '충청' AS PROD, 0 AS QTY FROM DUAL ) SELECT YYYY, PROD, QTYS FROM TAB1 MODEL PARTITION BY(PROD) DIMENSION BY(YYYY) MEASURES(QTY QTYS) RULES (QTYS ['증감'] = NVL(QTYS['2016'], 0) - NVL(QTYS['2015'], 0)) ORDER BY 1, 2
아.. 이렇게 간단하게 해결이 되는구나. 정말 감사합니다.
with t as
( select '2015' as yyyy, '서울' as prod, 189605 as qty from dual union all
select '2015' as yyyy, '경기' as prod, 211392 as qty from dual union all
select '2015' as yyyy, '부산' as prod, 618721 as qty from dual union all
select '2015' as yyyy, '대구' as prod, 86968 as qty from dual union all
select '2015' as yyyy, '인천' as prod, 29825 as qty from dual union all
select '2015' as yyyy, '광주' as prod, 49810 as qty from dual union all
select '2015' as yyyy, '대전' as prod, 144698 as qty from dual union all
select '2015' as yyyy, '울산' as prod, 307855 as qty from dual union all
select '2016' as yyyy, '서울' as prod, 376221 as qty from dual union all
select '2016' as yyyy, '경기' as prod, 223522 as qty from dual union all
select '2016' as yyyy, '부산' as prod, 430019 as qty from dual union all
select '2016' as yyyy, '대구' as prod, 180503 as qty from dual union all
select '2016' as yyyy, '인천' as prod, 32931 as qty from dual union all
select '2016' as yyyy, '광주' as prod, 149506 as qty from dual union all
select '2016' as yyyy, '대전' as prod, 221965 as qty from dual union all
select '2016' as yyyy, '울산' as prod, 260988 as qty from dual union all
select '2016' as yyyy, '충청' as prod, 0 as qty from dual
)
select decode(v_type, 'BFYY', '증감', yyyy) yyyy
, prod
, qty
from (select decode(grouping_id(prod, yyyy), 1, '소계', 3, '총계', yyyy) as yyyy
, prod
, sum(qty) as qty
, to_number(decode(grouping(yyyy)
, 0
, sum(qty) - lag(sum(qty), 1) over(partition by prod order by prod, yyyy))
) bfyy
from t
group by rollup(prod, yyyy)
)
unpivot(qty for v_type in (qty, bfyy))
where yyyy not in ('총계', '소계')
order by yyyy, prod
;
) 버튼을 클릭하여 작성 하시면 됩니다.