SQL 스터디 3주차!
서브쿼리
- 장점 : 서브쿼리는 조인보다 이해하기 쉽다.
- 단점 : 서브쿼리 성능이 좋지 못할 수도 있다. sql 실행계획이 특정된 방법으로 제약될 가능성이 있기 때문이다. 모든 조인을 서브쿼리로 해결하려 해서는 절대 안된다.
SELECT절의 상관 서브쿼리
서브쿼리가 아닌 조인으로도 해결할 수 있지만,
코드 명칭을 가져오는 쿼리의 경우, 코드처럼 값의 종류가 많지 않은 경우 캐싱 효과로 서브쿼리를 사용하는 것이 성능이 더 좋을 수도 있다.
코드값을 가져오는 SELECT 절 상관 서브쿼리
1
2
3
4
5
6
7
-- 코드값을 가져오는 SELECT 절 상관 서브쿼리
SELECT T1.ITM_TP
,(SELECT A.BAS_CD_NM
FROM C_BAS_CD A
WHERE A.BAS_CD_DV = 'ITM_TP' AND A.BAS_CD = T1.ITM_TP AND A.LNG_CD = 'KO') ITM_TP_NM
,T1.ITM_ID ,T1.ITM_NM
FROM M_ITM T1;
예제들) 서브쿼리와 인라인뷰를 다양하게 활용해보기
17년8월 총 주문금액, 주문일자의 주문금액비율 구하기 – SELECT절 단독 서브쿼리
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- ************************************************
-- PART I - 4.1.2 SQL2
-- ************************************************
-- 17년8월 총 주문금액, 주문일자의 주문금액비율 구하기 – SELECT절 단독 서브쿼리
-- 주문금액 비율 = 주문일자별 주문금액(ORD_AMT) / 17년8월 주문 총 금액(TOTAL_ORD_AMT) * 100.00
SELECT TO_CHAR(T1.ORD_DT, 'YYYYMMDD') ORD_YMD
,SUM(T1.ORD_AMT) ORD_AMT
,(
SELECT SUM(A.ORD_AMT)
FROM T_ORD A
WHERE A.ORD_DT >= TO_DATE('20170801','YYYYMMDD')
AND A.ORD_DT < TO_DATE('20170901','YYYYMMDD')
) TOTAL_ORD_AMT
, ROUND(SUM(T1.ORD_AMT) / (
SELECT SUM(A.ORD_AMT)
FROM T_ORD A
WHERE A.ORD_DT >= TO_DATE('20170801', 'YYYYMMDD')
AND A.ORD_DT < TO_DATE('20170901', 'YYYYMMDD')
) * 100.00, 2 ) 주문금액비율
FROM T_ORD T1
WHERE T1.ORD_DT >= TO_DATE('20170801','YYYYMMDD')
AND T1.ORD_DT < TO_DATE('20170901','YYYYMMDD')
GROUP BY TO_CHAR(T1.ORD_DT, 'YYYYMMDD');
인라인-뷰를 사용해 반복 서브쿼리를 제거하는 방법
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT T1.ORD_YMD
,T1.ORD_AMT
,T1.TOTAL_ORD_AMT
,ROUND(T1.ORD_AMT / T1.TOTAL_ORD_AMT * 100,2) ORD_AMT_RT
FROM (
SELECT TO_CHAR(A.ORD_DT, 'YYYYMMDD') ORD_YMD
,SUM(A.ORD_AMT) ORD_AMT
,(SELECT SUM(B.ORD_AMT)
FROM T_ORD B
WHERE B.ORD_DT >= TO_DATE('20170801','YYYYMMDD')
AND B.ORD_DT < TO_DATE('20170901','YYYYMMDD')
) TOTAL_ORD_AMT
FROM T_ORD A
WHERE A.ORD_DT >= TO_DATE('20170801','YYYYMMDD')
AND A.ORD_DT < TO_DATE('20170901','YYYYMMDD')
GROUP BY TO_CHAR(A.ORD_DT, 'YYYYMMDD')
) T1;
인라인-뷰 안에서 SELECT 절 서브쿼리를 사용한 예
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- 인라인-뷰 안에서 SELECT 절 서브쿼리를 사용한 예
SELECT T1.CUS_ID
,SUBSTR(T1.ORD_YMD,1,6) ORD_YM
,MAX(T1.CUS_NM)
,MAX(T1.CUS_GD)
,T1.ORD_ST_NM
,T1.PAY_TP_NM
,SUM(T1.ORD_AMT) ORD_AMT
FROM (
SELECT T1.CUS_ID ,TO_CHAR(T1.ORD_DT,'YYYYMMDD') ORD_YMD ,T2.CUS_NM ,T2.CUS_GD
,(SELECT A.BAS_CD_NM
FROM C_BAS_CD A
WHERE A.BAS_CD_DV = 'ORD_ST'
AND A.BAS_CD = T1.ORD_ST
AND A.LNG_CD = 'KO') ORD_ST_NM
,(SELECT A.BAS_CD_NM
FROM C_BAS_CD A
WHERE A.BAS_CD_DV = 'PAY_TP'
AND A.BAS_CD = T1.PAY_TP
AND A.LNG_CD = 'KO') PAY_TP_NM
,T1.ORD_AMT
FROM T_ORD T1
,M_CUS T2
WHERE T1.ORD_DT >= TO_DATE('20170801','YYYYMMDD')
AND T1.ORD_DT < TO_DATE('20170901','YYYYMMDD')
AND T1.CUS_ID = T2.CUS_ID
) T1
GROUP BY T1.CUS_ID ,SUBSTR(T1.ORD_YMD,1,6) ,T1.ORD_ST_NM ,T1.PAY_TP_NM;
- 인라인-뷰 안의 결과 건수가 1000건이고, 인라인뷰 바깥의 GROUP BY까지 실행된 최종 결과 건수가 100건이라고 치면,
- 인라인뷰 안에서 상관 서브쿼리를 사용하면 서브쿼리는 1000번 실행됨
- 인라인뷰 바깥에서 상관 서브쿼리를 사용하면 서브쿼리는 100번 만 실행됨
- SELECT절의 서브쿼리는 가장 바깥의 SELECT절에만 사용하도록 !
평소에 많이 쓰는 듯한 스타일 (마지막 ORD_SEQ가져오기)
1
2
3
4
5
6
7
8
9
10
11
12
13
-- 고객별 마지막 ORD_SEQ의 주문금액 – 중첩된 서브쿼리
SELECT T1.CUS_ID
,T1.CUS_NM
,(
SELECT B.ORD_AMT
FROM T_ORD B
WHERE B.ORD_SEQ =
(SELECT MAX(A.ORD_SEQ)
FROM T_ORD A
WHERE A.CUS_ID = T1.CUS_ID)
) LAST_ORD_AMT
FROM M_CUS T1
ORDER BY T1.CUS_ID;
반복 출현을 줄여서 성능 개선하기
1
2
3
4
-- 마지막 주문 한 건을 조회하는 SQL, ORD_SEQ가 가장 큰 데이터가 마지막 주문이다.
SELECT *
FROM T_ORD T1
WHERE T1.ORD_SEQ = (SELECT MAX(A.ORD_SEQ) FROM T_ORD A);
1
2
3
4
5
6
7
8
-- 마지막 주문 한 건을 조회하는 SQL, ORDER BY와 ROWNUM을 사용
SELECT *
FROM (
SELECT *
FROM T_ORD T1
ORDER BY T1.ORD_SEQ DESC
) A
WHERE ROWNUM <= 1;
- ORD_SEQ (PK)에 인덱스가 있다는 상황으로 보면 성능이 개선된 건 (PK에는 기본적으로 UNIQUE인덱스 생성됨)
EXISTS
데이터의 존재 여부를 파악할 때.
반대의 경우는 NOT EXISTS
1
2
3
4
5
6
7
8
9
10
-- 3월에 주문이 존재하는 고객들을 조회
SELECT *
FROM M_CUS T1
WHERE EXISTS(
SELECT *
FROM T_ORD A
WHERE A.CUS_ID = T1.CUS_ID
AND A.ORD_DT >= TO_DATE('20170301','YYYYMMDD')
AND A.ORD_DT < TO_DATE('20170401','YYYYMMDD')
);
MERGE
- 한 문장으로 INSERT와 UPDATE를 동시에 처리할 수 있는 문법
- MERGE 대상이 이미 존재하면 → UPDATE
- 대상이 존재하지 않으면 → INSERT
1
2
3
4
MERGE INTO 테이블명 /* update되거나 insert될 테이블 */
USING ( /* MERGE대상의 처리방법을 결정할 비교 데이터 집합 */
/* 여러 건을 비교 대상으로 정의할 수 있다 */
)
WITH
- 인라인 뷰와 비슷한데, SQL 가장 윗부분에서 사용할 때.
- WITH에서 정의된 SQL블록들은 같은 SQL내에서 테이블처럼 사용할 수 있다.
- 장점 : 반복되는 인라인 뷰를 제거해 성능을 개선 & 가독성 개선
- 단점 : 무분별하게 사용시 성능이 나쁠 수도 있음.
- 인라인뷰 vs WITH는 실행계획에 따라 효율을 보고 적용해야함.
reference
SQL BOOSTER
