1. PIVOT()
우선 그림을 통해 적용 전 후 테이블, 쿼리와의 관계성을 파악하고 글을 읽는것을 추천한다.
PIVOT()의 중간집계 (== PIVOT과 대등한 depth의 FROM 절에 사용되는 테이블)와 같이 중간집계가 된 테이블에서, PIVOT을 적용하였을 때 나타나는 결과가 PIVOT() 사용결과 로 나타난 상황이다.
-
PIVOT절 (빨강색)
PIVOT() 사용결과에서 value로써 뿌리고자 하는, PIVOT()의 중간집계의 컬럼. -
PIVOT FOR 절 (초록색)
열 단위 GROUPING을 하고자 하는 PIVOT()의 중간집계에서의 컬럼. -
PIVOT IN 절 (노랑색)
PIVOT()의 중간집계에서 PIVOT FOR 절 컬럼 아래있는 값들 중, PIVOT() 사용결과에서 컬럼으로 나타나길 원하는 값들을 지정.
그런데 내가 원하는 것은 TX_TYPE 기준의 GROUPING이었는데, 도대체 PIVOT절 어디에서 TX_TYPE을 넣어줘야 하는 지 매우 고민했다.
해답은 바로 PIVOT절 어디에도 있지 않은 컬럼이 GROUPING의 기준이 된다는 것이었다!
중간집계 테이블과 PIVOT-FOR-IN절을 잘 비교해 보면, PIVOT절, PIVOT FOR 절에서 언급되지 않는 남은 컬럼이 있다. (보라색)
이 컬럼이 바로 PIVOT 사용 결과에서 행 단위로 GROUPING되는 기준이 된다!
2. UNPIVOT()
마찬가지로, 우선 그림을 통해 적용 전 후 테이블과 쿼리와의 관계성을 파악하고 글을 읽는것을 추천한다.
-
UNPIVOT절(빨강색)
UNPIVOT절(빨강색)은 현재 UNPIVOT()의 중간집계에서 뿌려져 표시되어지고 있는 값에 대해 ALIAS를 걸어, Column의 이름으로 지정해주는 부분이다. 그래서 UNPIVOT() 사용 결과에서 RSLT_VAL라는 이름으로 컬럼이 생성되었고, 기존에 여러 컬럼에 뿌려져 있던 값들이 한 컬럼(RSLT_VAL)에 들어간 것을 볼 수 있다. -
UNPIVOT IN 절(노랑색)
UNPIVOT IN 절(노랑색)은, UNPIVOT() 사용 결과에서 컬럼이 해제되어 화면에 value로써 뿌려질, UNPIVOT()의 중간집계에서 컬럼으로 사용되는 이름들이 자리하게 된다. -
UNPIVOT FOR 절(초록색)
UNPIVOT FOR 절(초록색)은 위에서 IN으로 묶은 데이터들의 컬럼의 ALIAS가 된다.
해서 UNPIVOT() 사용 결과에서 TX_TYPE이라는 컬럼아래, CUST_NUM, INQR_NUM, APRV_Y, APRV_N이 value로써 뿌려진 것을 알 수 있다. -
UNPIVOT 전체에서 언급이 없는 컬럼 (보라색)
UNPIVOT 절에 포함되지 않는 UNPIVOT()의 중간집계에서 남는 컬럼(보라색)인 INQR_YM은 흩뿌려지면서, GROUP BY를, TX_TYPE으로 한번 한 것에 INQR_YM으로 한번 더 한 것 같은 결과를 보이게 된다.
-> 비유하자면, GROUP BY TX_TYPE, INQR_YM 한 것 같은 형태다!
개인적으로, 글로 어떻게 표현을 하든 그림을 보고 이해하는 게 빠를 것 같다.
3. PIVOT()과 UNPIVOT()을 활용한 월별집계 과정
원본 테이블 (LN_LIMT_INQR)
CREATE TABLE ln_limt_inqr
(
ln_scrn_no VARCHAR2(20) DEFAULT ' ' NOT NULL,
time_stamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
cust_id VARCHAR2(20) DEFAULT ' ' NOT NULL,
brth_dt VARCHAR2(8) DEFAULT ' ' NOT NULL,
indv_rehab_app_yn VARCHAR2(1) DEFAULT ' ' NOT NULL,
kcb_score NUMBER(4) DEFAULT 0 NOT NULL,
css_grade NUMBER(1) DEFAULT 5 NOT NULL,
annu_inco NUMBER(20) DEFAULT 0 NOT NULL,
pni_rpay_amt NUMBER(20) DEFAULT 0 NOT NULL,
dsr NUMBER(6, 4) DEFAULT 0 NOT NULL,
ln_odue_yn VARCHAR2(1) DEFAULT ' ' NOT NULL,
ln_amt NUMBER(20) DEFAULT 0 NOT NULL,
ln_bal NUMBER(20) DEFAULT 0 NOT NULL,
nstg_loan_exis_cnt NUMBER(1) DEFAULT 0 NOT NULL,
rslt_aprv_yn VARCHAR2(1) DEFAULT ' ' NOT NULL,
rslt_limt_amt NUMBER(20) DEFAULT 0 NOT NULL,
rslt_ln_intr NUMBER(6, 4) DEFAULT 0 NOT NULL,
rslt_denl_cd VARCHAR2(3) DEFAULT ' ' NOT NULL,
tx_dt VARCHAR2(8) DEFAULT ' ' NOT NULL,
CONSTRAINT pk_lnlimtinqr PRIMARY KEY (ln_scrn_no)
);첫번째 집계 결과
SELECT
to_char(to_date(tx_dt), 'yyyy-mm') as INQR_YM,
count(distinct cust_id) as CUST_NUM,
count(*) as INQR_NUM,
count(case when rslt_aprv_yn='Y' then 1 end) as APRV_Y,
count(case when rslt_aprv_yn='N' then 1 end) as APRV_N
FROM LN_LIMT_INQR
GROUP BY to_char(to_date(tx_dt), 'yyyy-mm')
ORDER BY to_char(to_date(tx_dt), 'yyyy-mm');
UNPIVOT을 이용한 두 번째 중간 집계
SELECT INQR_YM, TX_TYPE, RSLT_VAL
FROM (
SELECT
to_char(to_date(tx_dt), 'yyyy-mm') as INQR_YM,
count(distinct cust_id) as CUST_NUM,
count(*) as INQR_NUM,
count(case when rslt_aprv_yn='Y' then 1 end) as APRV_Y,
count(case when rslt_aprv_yn='N' then 1 end) as APRV_N
FROM LN_LIMT_INQR
GROUP BY to_char(to_date(tx_dt), 'yyyy-mm')
ORDER BY to_char(to_date(tx_dt), 'yyyy-mm')
)
UNPIVOT (
RSLT_VAL
FOR TX_TYPE
IN (CUST_NUM, INQR_NUM, APRV_Y, APRV_N)
);
PIVOT을 이용한 마지막 집계 (원하는 거래 년월별 집계)
SELECT *
FROM (
SELECT INQR_YM, TX_TYPE, RSLT_VAL
FROM (
SELECT
to_char(to_date(tx_dt), 'yyyy-mm') as INQR_YM,
count(distinct cust_id) as CUST_NUM,
count(*) as INQR_NUM,
count(case when rslt_aprv_yn='Y' then 1 end) as APRV_Y,
count(case when rslt_aprv_yn='N' then 1 end) as APRV_N
FROM LN_LIMT_INQR
GROUP BY to_char(to_date(tx_dt), 'yyyy-mm')
ORDER BY to_char(to_date(tx_dt), 'yyyy-mm')
)
UNPIVOT (
RSLT_VAL
FOR TX_TYPE
IN (CUST_NUM, INQR_NUM, APRV_Y, APRV_N)
)
)
PIVOT (
SUM(RSLT_VAL)
FOR (INQR_YM)
IN ('2021-12', '2022-01', '2022-03')
);
