3.1 원하는 정보만 골라담아서 SUM을 하겠다는 거다.
통상적으로 Table에는 아랫방향 (Row)로 정보가 저장되어 있는데, 이것을 좌우방향 (Column) 으로 전개하는 곳에도 활용된다.
 | 이 Table을 횡으로 전개하고 싶으면 이렇게 해주면 된다.SELECT ITEM, DECODE(YYMM, '9801', QTY) M1, DECODE(YYMM, '9802', QTY) M2, ... DECODE(YYMM, '9806', QTY) M6 FROM SALES; |  |
이 결과를 가지고 각 월별 출고량의 합계를 보고 싶으면 SELECT ITEM, SUM(M1), SUM(M2), ... SUM(M6) FROM ...; GROUP BY ITEM | 의 모양이 될거니깐, 이거랑 원래 SQL문을 합치면SELECT ITEM, SUM(DECODE(YYMM, '9801', QTY)), SUM(DECODE(YYMM, '9802', QTY)), ... SUM(DECODE(YYMM, '9806', QTY)) FROM SALES GROUP BY ITEM; |
이런 SQL문이 된다. 이런식으로 하나씩 하나씩 생각을 하는 연습을 꾸준히 해야한다.
| 조금만 더 응용해보자. 1월 ~ 12월까지의 총계와 각 부서별 년간 판매량 총계까지 보고 싶으면 어떻게 해야 할까 ? 그냥 마지막에 SUM() 에 DECODE없이 묻지마 합계를 하나 추가하면 된다.  | SELECT DEPT_NAME, M01, M02, ..., M12, TOTAL FROM ( SELECT DEPT, SUM(DECODE(SUBSTR(YYMM,3,2), '01', AMT)) M01, SUM(DECODE(SUBSTR(YYMM,3,2), '02', AMT)) M02, ... SUM(DECODE(SUBSTR(YYMM,3,2), '12', AMT)) M12, SUM(AMT) TOTAL FROM SALES WHERE YYMM LIKE '96%' GROUP BY DEPT ) X INNER JOIN DEPT_TAB Y ON X.DEPT = Y.DEPT |
1권 내용에서 배웠듯이 Nested Loop JOIN에서 성능에 영향을 미치는 것은 Driving 되는 Table의 크기이다.
JOIN을 하기 전에 Driving 되는 조건을 최대한으로 줄여라.
대표적인 방법으로는 위에서 했듯 GROUP BY로 줄이는 것도 있을 수 있고,
그리고 또 중요한 것이 위에 예제에는 없지만 WHERE 절에서의 CHECK 조건
A, B Table을 JOIN 하는데 A Table이 Driving 되어야 한다.
그런데 WHERE 절에 CHECK 조건 중 해당 조건은 A,B 두 Table 중 아무때나 CHECK가 가능하다면,
A Table에서 하는게 JOIN 전에 Data를 더 줄여 줄수가 있다는 거다.
3.2 전체 집합의 확장
어떻게 Table 들을 JOIN 하고 UNION, MINUS 하느냐에 따라서 보여지는 Data들의 모양이 바뀐다.
아래 그림이 지금은 바로 이해가 안될지 몰라도, SUM(DECODE) 를 많이 활용하고 난 뒤에 다시 보면 한눈에 들어올 것이다.
 | 이 예제 SQL문의 이해는 어렵지 않을 것이다. 전체집합 확장은 얼마든지 가능하다. 그러나, 어떤 절차에 따라 어떻게 확장하느냐가 중요하다. 그것 순서가 바뀌면 출력되는 값 자체가 바뀔수도 있으며, 성능 차이도 어마어마 하다. |
3.3 중분류의 확장
그림을 자세히 한번 보자.
제품 - 등급으로 2단계로 분류가 이루어져있는데,
- 제품이 HP면 등급에 상관없이 모두 같은 제품으로 분류
- 제품이 LD면 등급별로 분류
- 제품이 PP면 P530C인 것끼리 분류, 그 나머지들은 모두 기타로 분류
조금은 복잡하게 GROUP BY로 계산해야하는데 이게 프로그램 Logic을 전혀 쓰지않고 SQL로 가능할까 ?
물론 가능하지. SQL에 익숙하지 않은 사람이라면 INLINE VIEW를 여러 단계 활용하는 식으로 하는 것은 누구나 다 할 수있다.
근데, 그렇게 말고 INLINE VIEW를 쓰지 않고 한번에 SQL로도 가능하다.
 | SELECT DECODE(PRODUCT, 'HP', PRODUCT, 'LD', GRADE, DECODE(GRADE, 'P530C', GRADE, 'ETC')), SUM(QTY), SUM(SALES) FROM SALES_TABLE WHERE REG_DATE LIKE '201408%' GROUP BY DECODE(PRODUCT, 'HP', PRODUCT, 'LD', GRADE, DECODE(GRADE, 'P530C', GRADE, 'ETC')) |
SELECT 의 첫번째와 GROUP BY의 DECODE를 한번 뚤어지라 보면서 해석해봐라.
PRODUCT가 HP면 PRODUCT로 GROUP BY
LD면 GRADE로 GROUP BY
ELSE GRADE가 P530C면 GRAGE로 GROUP BY
ELSE 나머지는 다 ETC 로 GROUP BY
GROUP BY 가 가능하다.
문제는 저렇게 생각을 할 수 있을 만큼의 집합적 사고능력을 기르는 것이지...
3.4 ROW단위 처리의 확장
1번만 읽고, 그걸 여러번 사용하는 것이 가능하다.
이걸 잘 활용하지 않으면, 당일분 읽는다고 SELECT, 주간합계 계산하나고 SELECT, 월간합계 구한다고 SELECT...
그런식의 SQL문들이 지금도 아주 많은 곳에서 사용되고 있다.
이제부터 이걸 본 분들이라면 절대로 그러지 말자.
봐야할 가장 넓은 범위를 1번만 읽어서 그중 필요한것만 골라서 SUM(DECOCE)하면 된다.
이미 앞에서도 많이 소개된 SQL 방식이라서 앞에 글들을 다 보신 분들이라면 다 알것이다.
SELECT SALEDEPT, SUM(DECODE(saledate, TO_CHAR(SYSDATE,'yyyymmdd'),saleqty)), -- 당일분 SUM(DECODE(SIGN(8 - (SYSDATE - TO_DATE(saledate,'yyyymmdd'))), 1, saleqty)), -- 1주일분 SUBSTR(MAX(saledate||qty),9,15), -- 가장 최근 처리분량 SUM(saleqty) -- 1달 합계 FROM SALES_TABLE WHERE saledate LIKE '201407%' GROUP BY SALEDEPT;3.5 주의사항
3.5.1 ELSE 없는 IF를 사용하라.
SELECT SUM(DECODE(COL1, 1, QTY, 0 )), ...COL1이 1이면 QTY를 아니면 0 ? 0을 왜 넣지 그냥 비워두고 NULL 처리하지 왜 굳이 0을 ?
NULL은 SUM 함수에서 아무런 역할을 하지 않지만 0은 연산을 수행하게 된다.
차라리 0 보다는 NULL이 훨씬 더 좋은 선택이며 뒤에 ,0 은 굳이 쓸 필요가 없다.
3.5.2 가능한 GROUP FUNC. 내에서는 NVL를 사용하지 마라.
SELECT SUM(DECODE(COL1, 1, NVL(QTY, 0) )), ...왜 굳이 NVL ? NULL은 SUM 함수에서 아무런 역할을 하지 않는다는건 바로 위에서 말했고,
혹시 QTY가 전부다 NULL 일때 0이 나오게 하고 싶어서 ?
근데 왜 하필 SUM 안에서 매번 NVL ?
그냥 SUM 다하고 밖에서 한번만 NVL 해라.
SELECT NVL(SUM(DECODE(COL1, 1, QTY)),0), ...아래 다른 예제를 하나 더 보자.
SELECT SUM(DECODE(COL1, 1, NVL(QTY1,0) + NVL(QTY2,0) )), ...흠...
물론 DBMS에 따라 다르지만 A + NULL = A 인 것도 있고, A + NULL = NULL인 것도 있으니깐 어떤 생각으로 저렇게 한지는 이해가지만 그냥 저렇게 하지말고 각각의 SUM을 구해서 그것들만 NVL 하면 끝 !
SELECT NVL(SUM(DECODE(COL1, 1, QTY1)),0) + NVL(SUM(DECODE(COL1, 1, QTY2)),0), ...DBMS는 혼자쓰는게 아니다. 혼자쓰는거라면 뭐 어떻게 쓰던 상관할바 아니지만, 내가 0.01초라더 더 D/B에 부하를 주면 그만큼 다른 사람들은 그 만큼의 자원을 사용하지 못하게 된다.
* NULL 값의 적용 기준
- NULL 이란 ? 모르는 값도 하나의 값이다. NULL도 의미가 있는 값이어야 한다.
미확정 값이라는 것을 표현하고자 할 때. 아직은 미확정이지만 언젠가는 확정된 값이 들어갈 경우
특정값이 지나치게 많을 경우, 이 값은 FULL-SCAN 하고 나머지 값만 INDEX-SCAN 하고 싶을 경우
- 결합INDEX의 구성 Column이 된다던가,
입력조건으로 자주 사용된다면 NOT NULL로 사용하는걸 추천한다. DEFAULT 로라도 어떤 값을 넣어라.
3.5.3 가능한 중첩 DECODE는 피해라.
DECODE 안에 있는 새끼 DECODE는 엄청 느리다. 특히 3단계 정도까지 가는걸 피하자.
절차형 사고에서는 IF ... ELSE 안에 중첩으로 많이 사용하다보니 SQL에서도 그렇게 쓰는 경우가 많은데,
SQL은 절차형 사고로 접근을 하는게 아니라 집합적 사고로 접근을 해야한다.
3.5.3.1 컬럼결합법
SELECT SUM(DECODE(market, 'D', DECODE(type, '1', DECODE(unit, 'A', 0.2 * col, 'B', 0.5 * col, ...위를 보면 어떤 뜻인지 바로 한눈에 보일 것이다.
D-1-A 면 0.2를 곱하고, D-1-B면 0.5를 곱한다는 뜻이다.
저것을 말 그대로 D 면서 1이면서 A 면 0.2 로 보지말고, D1A 면 0.2, D1B면 0.2라고 생각을 할 수 있다면 아래와 같이 콩깍지해서 비교하면 된다.
SELECT SUM(DECODE(market||type||unit, 'D1A', 0.2, 'D1B', 0.5, ...) * col) ...* 사례
SELECT DECODE(대인,'Y', DECODE(대물,'N', DECODE(자손,'N', DECODE(자차,'N', DECODE(무보험,'N',1, 'Y', 2), 'Y',DECODE(무보험,'N',3, 'Y', 4)), 'Y',DECODE(자차,'N', DECODE(무보험,'N',5, 'Y', 6), 'Y',DECODE(무보험,'N',7, 'Y', 8))), 'Y',DECODE(자손,'N', DECODE(자차,'N', DECODE(무보험,'N', 9, 'Y',10), 'Y', DECODE(무보험,'N',11,'Y',12)), 'Y',DECODE(자차,'N', DECODE(무보험,'N',13, 'Y',14), 'Y', DECODE(무보험,'N',15, 'Y',16))), ... FROM ...위에 SQL이 보기 편한가 ? 절차형에 익숙하다면 위에 그림도 익숙하겠지만 엄청난 중첩 DECODE 다.
딱 보고 감이 와야 한다.
저렇게 조건을 줄려면 DW Modeling Pattern 으로 Data Modeling 되어 있어야 한다는 정도는 생각을 해야 한다.
그럼 당연 STAR JOIN 으로 하는 것이 효율적이다.
그런데 Table의 Data Modeling이 STAR JOIN 에 맞는 형식으로 안되어 있다면 ?
그래도 STAR JOIN 방식으로 처리를 하면 된다.
SELECT ... FROM ( SELECT 대인||대물||자손||자차||무보험 형태, count(*) 총가입자, count(decode(substr(가입일,5,2), '01',1)) 1월가입자, count(decode(substr(가입일,5,2), '02',1)) 2월가입자, count(decode(substr(가입일,5,2), '03',1)) 3월가입자, count(decode(substr(가입일,5,2), '04',1)) 4월가입자, count(decode(substr(가입일,5,2), '05',1)) 5월가입자, count(decode(substr(가입일,5,2), '06',1)) 6월가입자 FROM 가입내역 WHERE 가입일자 between '19980101' and '19980630' and 가입구분 = '신규' GROUP BY 대인||대물||자손||자차||무보험 );전형적인 STAR JOIN 형태로 고쳤다. DECODE(형태,'YNNNNNN', ... 이런식으로 사용하면 된다.
3.5.3.2 함수활용법
* 1등급 : 1억이하 * 2등급 : 1억1원 ~ 5억이하 * 3등급 : 5억1원 ~ 10억이하 * 4등급 : 10억1원 ~ 15억이하 * 5등급 : 15억1원이상조금만 생각을 해보면 규칙성을 발견할 수 있다.
1억이하면 1등급. 이거하나 재껴놓고 나머지는 5억으로 나눠서 몫이 1,2,3 인 경우 2,3,4 등급 그 이상은 5등급이다.
그럼 CEIL 함수를 사용하면 된다.
SELECT DECODE(CEIL(col1 / 100000000), 1, '1', DECODE(CEIL(col1 / 500000000), 1, '2', 2, '3', 3, '4', 4, '5'))||'등급', ... FROM ... WHERE ... GROUP BY DECODE(CEIL(col1 / 100000000), 1, '1', DECODE(CEIL(col1 / 500000000), 1, '2', 2, '3', 3, '4', 4, '5'));3.5.4 펼칠 Column이 많을 때는 먼저 GROUP BY 한 후 처리하라.
SELECT deptno, SUM(DECODE( SUBSTR(work_dt,7,2), '01', amt )), /* 1 일자 특근비 */ SUM(DECODE( SUBSTR(work_dt,7,2), '02', amt )), /* 2 일자 특근비 */ SUM(DECODE( SUBSTR(work_dt,7,2), '03', amt )), /* 3 일자 특근비 */ SUM(DECODE( SUBSTR(work_dt,7,2), '04', amt )), /* 4 일자 특근비 */ ... SUM(DECODE( SUBSTR(work_dt,7,2), '31', amt )) /* 31일자 특근비 */ FROM 근무실적테이블 WHERE work_dt like '199810%' GROUP BY deptno ;SUBSTR(work_dt,7,2), 를 31번 다 쓸라고 ?
그냥 INLINE VIEW에서 한번만 쓰자.
SELECT deptno, SUM(DECODE( dd, '01', amt )), /* 1 일자 특근비 */ SUM(DECODE( dd, '02', amt )), /* 2 일자 특근비 */ ... SUM(DECODE( dd, '31', amt )) /* 31일자 특근비 */ FROM ( SELECT deptno, substr(work_dt,7,2) dd, SUM(amt) amt FROM 근무실적테이블 WHERE work_dt like '199810%' GROUP BY deptno, substr(work_dt,7,2) ) GROUP BY deptno;* 사례 1
0 ~ 1 , 2 ~ 7 , 8 ~ 15 , 16 ~ 30 , 31 ~ 60 일 로 구분해야 된다.
SELECT 거래처, SUM(decode(nvl(입고일,trunc(sysdate)) – 발주일, 0, 수량, 1, 수량)), ... SUM(decode(nvl(입고일,trunc(sysdate)) – 발주일, 15, 수량, 16, ..., 30, 수량)), SUM(decode(SIGN(61 - (nvl(입고일,trunc(sysdate)) – 발주일)), 1, decode(SIGN(30 - (nvl(입고일,trunc(sysdate)) – 발주일)), -1, 수량)), ... FROM ... WHERE ... GROUP BY 거래처 ;위의 의미는 이제 다들 파악이 될 것이다. 근데 보면 뭔가 좀 효율적인지 않게 보인다.
decode(nvl(입고일,trunc(sysdate)) – 발주일
부분이 반복적으로 사용되었다.
공통분모는 1번만 사용하자.
수학시간에 인수분해해서 공통분모를 괄호 밖으로 빼는 것을 배웠다.
SELECT 거래처, SUM(decode(일수, 0, 수량, 1, 수량)), ... SUM(decode(abs(31-일수) + abs(60-일수), 29, 수량 )), ... FROM ( SELECT 거래처, (nvl(입고일,trunc(sysdate)) – 발주일) 일수, SUM(수량) 수량 FROM ... WHERE ... GROUP BY 거래처, (nvl(입고일,trunc(sysdate)) – 발주일) ) GROUP BY 거래처;* 사례 2
SELECT y.지역, substr(종목,1,1), sum(decode(greatest(가입일,'199312'),'199312', 외화*decode(substr(종목,1,1),'1',0.25,1)*z.기준율)) , ..., sum(decode(substr(가입일,1,4), '1998', 외화*decode(substr(종목,1,1),'1',0.25,1)*z.기준율)) FROM 보험가입실적 x, 국가 y, 환율 z WHERE y.국가코드 = x.국가코드 and z.적용일자 = to_char(x.가입일,1,4)||'1231' and z.통화코드 = y.통화코드 and x.가입일자 between '199001' and '199812' GROUP BY y.지역, substr(종목,1,1);Star Schema 로 만들어진 것을 눈치채야 한다.
구분기준을 살펴보자.
일자는 년도만으로 구분하고, 종목은 앞에 1글자만 보고, 국가는 안보고 대륙별로 보고 있다는게 보일것이다.
SELECT y.지역, x.구분, sum(decode(greatest(년,'1993'),'1993',금액*decode(구분,'1',0.25,1)*기준율)), sum(decode(년, '1994', 금액*decode(구분,'1',0.25,1)*기준율)) , ..., sum(decode(년, '1998', 금액*decode(구분,'1',0.25,1)*기준율)) FROM ( SELECT 국가, substr(종목,1,1) 구분, substr(가입일자,1,4) 년, sum(외화) 금액 FROM 보험가입실적 WHERE 가입일 between '199001' and '199812' GROUP BY 국가, substr(종목,1,1), substr(가입일,1,4) ) x, 국가 y, 환율 z WHERE y.국가코드 = x.국가코드 and z.적용일자 = 년||'1231' and z.통화코드 = y.통화코드 GROUP BY y.지역, 구분;
댓글 없음:
댓글 쓰기