제 1 절 고급 SQL 활용

1. CASE문 활용

INSERT INTO 월별요금납부실적

(고객번호, 납입월, 지로, 자동이체, 신용카드, 핸드폰, 인터넷)

SELECT K.고객번호,

'200903' 납입월,

A.납입금액 지로,

B.납입금액 자동이체,

C.납입금액 신용카드,

D.납입금액 핸드폰,

E.납입금액 인터넷

FROM 고객K ,

(SELECT 고객번호, 납입금액

FROM 월별납입방법별집계

WHERE 납입월= '200903'

AND 납입방법코드= 'A') A ,

(SELECT 고객번호, 납입금액

FROM 월별납입방법별집계

WHERE 납입월= '200903'

AND 납입방법코드= 'B') B ,

(SELECT 고객번호, 납입금액

FROM 월별납입방법별집계

WHERE 납입월= '200903'

AND 납입방법코드= 'C') C ,

(SELECT 고객번호, 납입금액

FROM 월별납입방법별집계

WHERE 납입월= '200903'

AND 납입방법코드= 'D') D ,

(SELECT 고객번호, 납입금액

FROM 월별납입방법별집계

WHERE 납입월= '200903'

AND 납입방법코드= 'E') E

WHERE A.고객번호(+) = K.고객번호

AND B.고객번호(+) = K.고객번호

AND C.고객번호(+) = K.고객번호

AND D.고객번호(+) = K.고객번호

AND E.고객번호(+) = K.고객번호

AND NVL(A.납입금액,0)+NVL(B.납입금액,0)+NVL(C.납입금액,0)+NVL(D.납입금액,0)+NVL(E.납입금액,0) > 0

위 SQL은 월별납입방법별집계 테이블을 납입방법코드 분류별로 반복적으로 Access 하고 있음.
I/O 효율을 고려하여 아래와 같이 수정해야 함.

INSERT INTO 월별요금납부실적

(고객번호, 납입월, 지로, 자동이체, 신용카드, 핸드폰, 인터넷)

SELECT 고객번호,

납입월,

NVL(SUM(CASE WHEN 납입방법코드= 'A' THEN 납입금액END), 0) 지로,

NVL(SUM(CASE WHEN 납입방법코드= 'B' THEN 납입금액END), 0) 자동이체,

NVL(SUM(CASE WHEN 납입방법코드= 'C' THEN 납입금액END), 0) 신용카드,

NVL(SUM(CASE WHEN 납입방법코드= 'D' THEN 납입금액END), 0) 핸드폰,

NVL(SUM(CASE WHEN 납입방법코드= 'E' TJEM 납입금액END), 0) 인터넷

FROM 월별납입방법별집계

WHERE 납입월= '200903'

GROUP BY 고객번호, 납입월;

2. 데이터 복제 기법 활용

<< 알티베이스 예제 스크립트 >>

데이터복제를 통한 소계 구하기

iSQL> create table copy_t(no number, no2 varchar(2));

Create success.

elapsed time : 0.00

iSQL> insert into copy_t

2 select rownum, lpad(rownum, 2, '0') from system_.sys_tables_ limit 31;

31 rows inserted.

elapsed time : 0.01

iSQL> alter table copy_t add constraint copy_t primary key(no2);

Alter success.

elapsed time : 0.02

iSQL> alter table copy_t drop primary key;

Alter success.

elapsed time : 0.01

iSQL>

iSQL> alter table copy_t add constraint copy_t primary key(no);

Alter success.

elapsed time : 0.00

iSQL> create unique index copy_t_no2_idx on copy_t(no2);

Create success.

elapsed time : 0.01

iSQL> select count(*) from copy_t;

COUNT

-----------------------

1 row selected.

elapsed time : 0.00

iSQL>

iSQL> select * from copy_t;

NO NO2

--------------------

1 01

2 02

3 03

4 04

5 05

6 06

7 07

8 08

9 09

10 10

11 11

12 12

13 13

14 14

15 15

16 16

17 17

18 18

19 19

20 20

21 21

22 22

23 23

24 24

25 25

26 26

27 27

28 28

29 29

30 30

31 31

31 rows selected.

elapsed time : 0.00

iSQL> select count(*) from employees;

COUNT

-----------------------

1 row selected.

elapsed time : 0.00

iSQL> select count(*)

2 from (select * from employees a, copy_t b

3 where b.no > 2);

COUNT

-----------------------

580

1 row selected.

elapsed time : 0.01

iSQL> select count(*)

2 from (select * from employees a, (select rownum no from dual connect by level <= 2) b);

COUNT

-----------------------

1 row selected.

elapsed time : 0.00

iSQL> select dno "부서번호"

2 , decode(no, 1, to_char(eno), 2, '부서계') "사원번호"

3 , sum(salary) "급여합계", round(avg(salary)) "급여평균"

4 from employees a, (select rownum no from dual connect by level <= 2)

5 group by dno, no, decode(no, 1, to_char(eno), 2, '부서계')

6 order by 1, 2;

부서번호 사원번호 급여합계 급여평균

-----------------------------------------------------------------------------------

1001 16 2300 2300

1001 3 2000 2000

1001 부서계 4300 2150

1002 13 980 980

1002 6 1700 1700

1002 부서계 2680 1340

1003 10 4000 4000

1003 11 2750 2750

1003 14 2003 2003

1003 15 1000 1000

1003 부서계 9753 2438

2001 17 1400 1400

2001 부서계 1400 1400

3001 4 1800 1800

3001 부서계 1800 1800

3002 1

3002 5 2500 2500

3002 부서계 2500 2500

4001 18 1900 1900

4001 8

4001 9 1200 1200

4001 부서계 3100 1550

4002 12 1890 1890

4002 19 1800 1800

4002 20

4002 7 500 500

4002 부서계 4190 1397

2 1500 1500

부서계 1500 1500

29 rows selected.

elapsed time : 0.00

iSQL> select decode(no, 3, null, to_char(dno)) "부서번호"

2 , decode(no, 1, to_char(eno), 2, '부서계', 3, '총계') "사원번호"

3 , sum(salary) "급여합계", round(avg(salary)) "급여평균"

4 from employees a, (select rownum no from dual connect by level <= 3)

5 group by decode(no, 3, null, to_char(dno))

6 , no, decode(no, 1, to_char(eno), 2, '부서계', 3, '총계')

7 order by 1, 2;

부서번호 사원번호 급여합계 급여평균

-----------------------------------------------------------------------------------------------------------------

1001 16 2300 2300

1001 3 2000 2000

1001 부서계 4300 2150

1002 13 980 980

1002 6 1700 1700

1002 부서계 2680 1340

1003 10 4000 4000

1003 11 2750 2750

1003 14 2003 2003

1003 15 1000 1000

1003 부서계 9753 2438

2001 17 1400 1400

2001 부서계 1400 1400

3001 4 1800 1800

3001 부서계 1800 1800

3002 1

3002 5 2500 2500

3002 부서계 2500 2500

4001 18 1900 1900

4001 8

4001 9 1200 1200

4001 부서계 3100 1550

4002 12 1890 1890

4002 19 1800 1800

4002 20

4002 7 500 500

4002 부서계 4190 1397

2 1500 1500

부서계 1500 1500

총계 31223 1837

30 rows selected.

elapsed time : 0.00

iSQL> select dno "부서번호"

2 , case when grouping(eno) = 1 and grouping(dno) = 1 then '총계'

3 when grouping(eno) = 1 then '부서계'

4 else to_char(eno) end "사원번호"

5 , sum(salary) "급여합계", round(avg(salary)) "급여평균"

6 from employees

7 group by rollup(dno, eno)

8 order by 1, 2;

부서번호 사원번호 급여합계 급여평균

-----------------------------------------------------------------------------------

1001 16 2300 2300

1001 3 2000 2000

1001 부서계 4300 2150

1002 13 980 980

1002 6 1700 1700

1002 부서계 2680 1340

1003 10 4000 4000

1003 11 2750 2750

1003 14 2003 2003

1003 15 1000 1000

1003 부서계 9753 2438

2001 17 1400 1400

2001 부서계 1400 1400

3001 4 1800 1800

3001 부서계 1800 1800

3002 1

3002 5 2500 2500

3002 부서계 2500 2500

4001 18 1900 1900

4001 8

4001 9 1200 1200

4001 부서계 3100 1550

4002 12 1890 1890

4002 19 1800 1800

4002 20

4002 7 500 500

4002 부서계 4190 1397

2 1500 1500

부서계 1500 1500

총계 31223 1837

30 rows selected.

elapsed time : 0.00

<< 알티베이스 예제 스크립트 >>

데이터복제를 통한 소계 구하기

iSQL> create table copy_t(no number, no2 varchar(2));

Create success.

elapsed time : 0.00

iSQL> insert into copy_t

2 select rownum, lpad(rownum, 2, '0') from system_.sys_tables_ limit 31;

31 rows inserted.

elapsed time : 0.01

iSQL> alter table copy_t add constraint copy_t primary key(no);

Alter success.

elapsed time : 0.00

iSQL> create unique index copy_t_no2_idx on copy_t(no2);

Create success.

elapsed time : 0.01

iSQL> select count(*) from copy_t;

COUNT

-----------------------

1 row selected.

elapsed time : 0.00

iSQL>

iSQL> select * from copy_t;

NO NO2

--------------------

1 01

2 02

3 03

4 04

5 05

6 06

7 07

8 08

9 09

10 10

11 11

12 12

13 13

14 14

15 15

16 16

17 17

18 18

19 19

20 20

21 21

22 22

23 23

24 24

25 25

26 26

27 27

28 28

29 29

30 30

31 31

31 rows selected.

elapsed time : 0.00

iSQL> select count(*) from employees;

COUNT

-----------------------

1 row selected.

elapsed time : 0.00

iSQL> select count(*)

2 from (select * from employees a, copy_t b

3 where b.no > 2);

COUNT

-----------------------

580

1 row selected.

elapsed time : 0.01

iSQL> select count(*)

2 from (select * from employees a, (select rownum no from dual connect by level <= 2) b);

COUNT

-----------------------

1 row selected.

elapsed time : 0.00

iSQL> select dno "부서번호"

2 , decode(no, 1, to_char(eno), 2, '부서계') "사원번호"

3 , sum(salary) "급여합계", round(avg(salary)) "급여평균"

4 from employees a, (select rownum no from dual connect by level <= 2)

5 group by dno, no, decode(no, 1, to_char(eno), 2, '부서계')

6 order by 1, 2;

부서번호 사원번호 급여합계 급여평균

-----------------------------------------------------------------------------------

1001 16 2300 2300

1001 3 2000 2000

1001 부서계 4300 2150

1002 13 980 980

1002 6 1700 1700

1002 부서계 2680 1340

1003 10 4000 4000

1003 11 2750 2750

1003 14 2003 2003

1003 15 1000 1000

1003 부서계 9753 2438

2001 17 1400 1400

2001 부서계 1400 1400

3001 4 1800 1800

3001 부서계 1800 1800

3002 1

3002 5 2500 2500

3002 부서계 2500 2500

4001 18 1900 1900

4001 8

4001 9 1200 1200

4001 부서계 3100 1550

4002 12 1890 1890

4002 19 1800 1800

4002 20

4002 7 500 500

4002 부서계 4190 1397

2 1500 1500

부서계 1500 1500

29 rows selected.

elapsed time : 0.00

iSQL> select decode(no, 3, null, to_char(dno)) "부서번호"

2 , decode(no, 1, to_char(eno), 2, '부서계', 3, '총계') "사원번호"

3 , sum(salary) "급여합계", round(avg(salary)) "급여평균"

4 from employees a, (select rownum no from dual connect by level <= 3)

5 group by decode(no, 3, null, to_char(dno))

6 , no, decode(no, 1, to_char(eno), 2, '부서계', 3, '총계')

7 order by 1, 2;

부서번호 사원번호 급여합계 급여평균

-----------------------------------------------------------------------------------------------------------------

1001 16 2300 2300

1001 3 2000 2000

1001 부서계 4300 2150

1002 13 980 980

1002 6 1700 1700

1002 부서계 2680 1340

1003 10 4000 4000

1003 11 2750 2750

1003 14 2003 2003

1003 15 1000 1000

1003 부서계 9753 2438

2001 17 1400 1400

2001 부서계 1400 1400

3001 4 1800 1800

3001 부서계 1800 1800

3002 1

3002 5 2500 2500

3002 부서계 2500 2500

4001 18 1900 1900

4001 8

4001 9 1200 1200

4001 부서계 3100 1550

4002 12 1890 1890

4002 19 1800 1800

4002 20

4002 7 500 500

4002 부서계 4190 1397

2 1500 1500

부서계 1500 1500

총계 31223 1837

30 rows selected.

elapsed time : 0.00

iSQL> select dno "부서번호"

2 , case when grouping(eno) = 1 and grouping(dno) = 1 then '총계'

3 when grouping(eno) = 1 then '부서계'

4 else to_char(eno) end "사원번호"

5 , sum(salary) "급여합계", round(avg(salary)) "급여평균"

6 from employees

7 group by rollup(dno, eno)

8 order by 1, 2;

부서번호 사원번호 급여합계 급여평균

-----------------------------------------------------------------------------------

1001 16 2300 2300

1001 3 2000 2000

1001 부서계 4300 2150

1002 13 980 980

1002 6 1700 1700

1002 부서계 2680 1340

1003 10 4000 4000

1003 11 2750 2750

1003 14 2003 2003

1003 15 1000 1000

1003 부서계 9753 2438

2001 17 1400 1400

2001 부서계 1400 1400

3001 4 1800 1800

3001 부서계 1800 1800

3002 1

3002 5 2500 2500

3002 부서계 2500 2500

4001 18 1900 1900

4001 8

4001 9 1200 1200

4001 부서계 3100 1550

4002 12 1890 1890

4002 19 1800 1800

4002 20

4002 7 500 500

4002 부서계 4190 1397

2 1500 1500

부서계 1500 1500

총계 31223 1837

30 rows selected.

elapsed time : 0.00

3. Union All을 활용한 M:M 관계의 조인

위 두 테이블을 이용해 월별로 각 상품의 계획 대비 판매 실적을 집계하려 함.
부서별 판매계획 테이블과 채별별 판매실적은 M:M 관계.

아래와 같이 상품과 연월을 기준으로 group by 하면 1:1 관계가 되어 Full outer join을 통해 결과 도출 가능함.

select nvl(a.상품, b.상품) as 상품

,nvl(a.계획연월, b.판매연월) as 연월

, nvl(계획수량, 0) 계획수량

, nvl(판매수량, 0) 판매수량

from ( select 상품, 계획연월, sum(계획수량) 계획수량

from 부서별판매계획

where 계획연월between '200901' and '200903'

group by 상품, 계획연월) a

full outer join

( select 상품, 판매연월, sum(판매수량) 판매수량

from 채널별판매실적

where 판매연월between '200901' and '200903'

group by 상품, 판매연월) b

on a.상품= b.상품

and a.계획연월= b.판매연월

오라클 버전에 따라 Full outer join시 각 테이블을 2번씩 처리하기도 함.

Execution Plan

-------------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=8 Bytes=352)

1 0 VIEW (Cost=14 Card=8 Bytes=352)

2 1 UNION-ALL

3 2 HASH JOIN (OUTER) (Cost=8 Card=7 Bytes=308)

4 3 VIEW (Cost=4 Card=7 Bytes=154)

5 4 SORT (GROUP BY) (Cost=4 Card=7 Bytes=98)

6 5 TABLE ACCESS (FULL) OF '부서별판매계획' (Cost=2 Card=7 Bytes=98)

7 3 VIEW (Cost=4 Card=6 Bytes=132)

8 7 SORT (GROUP BY) (Cost=4 Card=6 Bytes=84)

9 8 TABLE ACCESS (FULL) OF '채널별판매실적' (Cost=2 Card=6 Bytes=84)

10 2 SORT (GROUP BY) (Cost=4 Card=1 Bytes=14)

11 10 FILTER

12 11 TABLE ACCESS (FULL) OF '채널별판매실적' (Cost=2 Card=1 Bytes=14)

13 11 SORT (GROUP BY NOSORT) (Cost=2 Card=1 Bytes=14)

14 13 FILTER
15 14 TABLE ACCESS (FULL) OF '부서별판매계획' (Cost=2 Card=1 Bytes=14)

select '계획' as 구분,

상품,

계획연월as 연월,

판매부서,

null as 판매채널,

계획수량,

to_number(null) as 실적수량

from 부서별판매계획

where 계획연월between '200901' and '200903'

union all

select '실적',

상품,

판매연월as 연월,

null as 판매부서,

판매채널,

to_number(null) as 계획수량,

판매수량

from 채널별판매실적

where 판매연월between '200901' and '200903'

구분	상품	연월	판매부서	판매채널	계획수량	실적수량
----	----	----	------	------	------	------
계획	상품A	200901	10		10000
계획	상품A	200902	20		5000
계획	상품A	200903	10		20000
계획	상품B	200901	10		20000
계획	상품B	200902	30		15000
계획	상품C	200901	30		15000
계획	상품C	200903	20		20000
실적	상품A	200901		대리점		7000
실적	상품A	200903		온라인		8000
실적	상품B	200902		온라인		12000
실적	상품B	200903		위탁		19000
실적	상품C	200901		대리점		13000
실적	상품C	200902		위탁		18000

select 상품,

연월,

nvl(sum(계획수량), 0) as 계획수량,

nvl(sum(실적수량), 0) as 실적수량

from ( select 상품,

계획연월as 연월,

계획수량,

to_number(null) as 실적수량

from 부서별판매계획

where 계획연월between '200901' and '200903'

union all

select 상품,

판매연월as 연월,

to_number(null) as 계획수량,

판매수량

from 채널별판매실적

where 판매연월between '200901' and '200903' ) a

group by 상품, 연월;

상품	연월	계획수량	실적수량
----	------	-------	------
상품A	200901	10000	7000
상품A	200902	5000	0
상품A	200903	20000	8000
상품B	200901	20000	0
상품B	200902	15000	12000
상품B	200903	0	19000
상품C	200901	15000	13000
상품C	200902	0	18000
상품C	200903	20000	0

# M:M 관계의 조인이나, Full Outer join을 Union all을 사용해 쉽게 해결 가능함.

4. 페이징 처리

# 관심 종목에 대해 사용자가 입력한 거래 일시 이후 거래 데이터를 페이징 처리 방식으로 조회할 경우

가. 일반적인 페이징 처리용 SQL

SELECT *

FROM (

SELECT ROWNUM NO, 거래일시, 체결건수

, 체결수량, 거래대금, COUNT(*) OVER () CNT ……………………………………… ①

FROM (

SELECT 거래일시, 체결건수, 체결수량, 거래대금

FROM 시간별종목거래

WHERE 종목코드= :isu_cd -- 사용자가 입력한 종목코드

AND 거래일시>= :trd_time -- 사용자가 입력한 거래일자 또는 거래일시

ORDER BY 거래일시……………………………………………………………………………………… ②

) WHERE ROWNUM <= :page*:pgsize+1 ………………………………………………………………… ③

) WHERE NO BETWEEN (:page-1)*:pgsize+1 AND :pgsize*:page …………………………… ④

Execution Plan

-------------------------------------------------------------

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=1 Bytes=75)

1 0 FILTER

2 1 VIEW (Cost=5 Card=1 Bytes=75)

3 2 WINDOW (BUFFER) (Cost=5 Card=1 Bytes=49)

4 3 COUNT (STOPKEY)

5 4 VIEW (Cost=5 Card=1 Bytes=49)

6 5 TABLE ACCESS (BY INDEX ROWID) OF '시간별종목거래' (TABLE) (Card=1 Bytes=56)

7 6 INDEX (RANGE SCAN) OF '시간별종목거래_PK' (INDEX (UNIQUE)) (Card=1)

① '다음' 페이지에 있을 데이터가 더 있는지 확인하는 용도. 결과 집합에서 CNT 값을 읽었을 때, :pgsize * :page 보다 크면 '다음' 페이지에 출력할 데이터가 더 있음을 알 수 있다. 전체 건수를 세지 않고도, '다음' 버튼을 활성화할지를 판단할 수 있어서 유용함. 이 기능이 필요치 않을 때는 ③번 라인에서 +1 을 제거하면 됨.(왜??? 이해 안감.)

② [종목코드 + 거래일시]순으로 정렬된 인덱스가 있을 때는 자동으로 sort 오퍼레이션이 생략됨. NOSORT를 위해 활용 가능한 인덱스가 없으면 결과 집합 전체를 읽는 비효율은 어쩔 수 없지만, TOP-N 쿼리 알고리즘이 작동하므로 SORT 부하만큼은 최소화할 수 있다.

③ :pgsize = 10 이고, :page = 3일 때, 거리일시순으로 31건만 읽는다.

④ :pgsize = 10이고 :page = 3 일 때, 안쪽 인라인뷰에서 읽은 31건중 21 ~ 30번째 데이터, 즉 3 페이지만 리턴한다.

나. 뒤쪽 페이지까지 자주 조회할 때

<< 첫번째 페이지를 출력하고 나서 다음(▶) 버튼을눌렀을때>>

SELECT 거래일시, 체결건수, 체결수량, 거래대금

FROM (

SELECT 거래일시, 체결건수, 체결수량, 거래대금

FROM 시간별종목거래A

WHERE :페이지이동= 'NEXT'

AND 종목코드= :isu_cd

AND 거래일시>= :trd_time <-- '이전' 페이지에서 출력한 마지막 거래일시 입력

ORDER BY 거래일시

)

WHERE ROWNUM <= 11 <-- 10건씩 출력하는데 왜 11??

Execution Plan

-------------------------------------------------------------

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=1 Bytes=49)

1 0 COUNT (STOPKEY)

2 1 VIEW (Cost=5 Card=1 Bytes=49)

3 2 FILTER

4 3 TABLE ACCESS (BY INDEX ROWID) OF '시간별종목거래' (TABLE) (Card=1 Bytes=56)

5 4 INDEX (RANGE SCAN) OF '시간별종목거래_PK' (INDEX (UNIQUE)) (Card=1)

< 이전(◀) 버튼을 클릭 했을 때>>

SELECT 거래일시, 체결건수, 체결수량, 거래대금

FROM (

SELECT 거래일시, 체결건수, 체결수량, 거래대금

FROM 시간별종목거래A

WHERE :페이지이동= 'PREV'

AND 종목코드= :isu_cd

AND 거래일시<= :trd_time <-- 이전 페이지에서 출력한 첫 번째 거래 일시 입력

ORDER BY 거래일시 DESC

)

WHERE ROWNUM <= 11

ORDER BY 거래일시 <-- 인덱스를 거꾸로 읽었지만, 화면상에서는 오름차순으로 출력 용도

Execution Plan

-------------------------------------------------------------

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=49)

1 0 SORT (ORDER BY) (Cost=1 Card=1 Bytes=49)

2 1 COUNT (STOPKEY)

3 2 VIEW (Cost=5 Card=1 Bytes=49)

4 3 FILTER

5 4 TABLE ACCESS (BY INDEX ROWID) OF '시간별종목거래' (TABLE) (Card=1 Bytes=56)

6 5 INDEX (RANGE SCAN DESCENDING) OF '시간별종목거래_PK' (INDEX (UNIQUE)) (Card=1)

다. Union All 활용

SELECT 거래일시, 체결건수, 체결수량, 거래대금

FROM (

SELECT 거래일시, 체결건수, 체결수량, 거래대금

FROM 시간별종목거래

WHERE :페이지이동= 'NEXT' -- 첫 페이지 출력 시에도'NEXT' 입력

AND 종목코드= :isu_cd

AND 거래일시>= :trd_time

ORDER BY 거래일시

) WHERE ROWNUM <= 11

UNION ALL

SELECT 거래일시, 체결건수, 체결수량, 거래대금

FROM (

SELECT 거래일시, 체결건수, 체결수량, 거래대금

FROM 시간별종목거래

WHERE :페이지이동= 'PREV'

AND 종목코드= :isu_cd

AND 거래일시<= :trd_time

ORDER BY 거래일시DESC

) WHERE ROWNUM <= 11

ORDER BY 거래일시

5. 윈도우 함수 활용

# 일련번호를 1씩 증가시키면서 측정값을 입력하고 , 상태 코드는 장비상태가 바뀔 때만 저장
# 상태 코드가 NULL이면 가장 최근에 상태코드가 바뀐 레코드의 값을 보여줌

select 일련번호, 측정값
   ,(select max(상태코드)
   from 장비측정
   where 일련번호 <= o.일련번호
   and 상태코드 is not null) 상태코드
from 장비측정 o
order by 일련번호

# 일련버호에 인덱스가 있어야 하고, [ 일련번호 + 상태코드]로 구성된 인덱스가 최적임.

select 일련번호, 측정값
   ,(select /*+ index_desc(장비측정 장비측정_idx) */ 상태코드
   from 장비측정
   where 일련번호 <= o.일련번호
   and 상태코드 is not null
   and rownum <= 1) 상태코드
from 장비측정 o
order by 일련번호
# 부분범위 처리 방식으로 앞쪽 일부만 보다가 멈출 경우 위 쿼리가 최적

select 일련번호, 측정값
   ,last_value(상태코드 ignore nulls)
        over(order by 일련번호 rows between unbounded preceding and current row) 상태코드
from 장비측정
order by 일련번호

# 전체 결과를 다 읽어야 할 경우 위 쿼리가 최적임.
# 파티션별 윈도우에서 가장 나중에 나온 값을 구함.
# rows between unbounded preceding and current row : 현재 행을 포함해서 파티션 내의 마지막 행까지의 범위 지정

6. WITH 구문 활용

- Materialize 방식 : 내부적으로 임시 테이블을 생성함으로써 반복 재사용
- Inline 방식 : 물리적으로 임시 테이블을 생성하지 않으며, 참조된 횟수만큼 런타임시 반복 수행.
SQL문에서 반복적으로 참조되는 집합을 미리 선언함으로써 코딩을 단순화하는 용도
(인라인 뷰와는, 메인 쿼리에서 여러 번 참조가 가능하다는 점에서 다름)

알티베이스는 materialize 방식으로 작동함.

with 위험고객카드as ( select 카드.카드번호, 고객.고객번호

from 고객.카드

where 고객.위험고객여부= 'Y'

and 고객여부= 'Y'

and 고객.고객번호= 카드발급.고객번호)

select v.*

from (

select a.카드번호as 카드번호

, sum(a.거래금액) as 거래금액

, null as 현금서비스잔액

, null as 해외거래금액

from 카드거래내역a

, 위험고객카드b

where 조건

group by a.카드번호

union all

select a.카드번호as 카드번호

, null as 현금서비스잔액

, sum(amt) as 현금서비스금액

, null as 해외거래금액

from (

select a.카드번호as 카드번호

, sum(a.거래금액) as amt

from 현금거래내역a

, 위험고객카드b

where 조건

group by a.카드번호

union all

select a.카드번호as 카드번호

, sum(a.결재금액) * -1 as amt

from 현금결재내역a

, 위험고객카드b

where 조건

group by a.카드번호) a

group by a.카드번호

union all

select a.카드번호as 카드번호

, null as 현금서비스잔액

, null as 현금서비스금액

, sum(a.거래금액) as 해외거래금액

from 해외거래내역a

, 위험고객카드b

where 조건

group by a.카드번호) v

Execution Plan

-------------------------------------------------------------

TEMP TABLE TRANSFORMATION → 임시테이블 생성

LOAD AS SELECT

VIEW (Cost=94K Card=5K Bytes=345K)

UNION-ALL

SORT (GROUP BY) (Cost=57K Card=1 Bytes=120)

HASH JOIN (Cost=57K Card=1 Bytes=120)

PARTITION RANGE (SINGLE)

PARTITION HASH (ALL)

TABLE ACCESS (FULL) OF '카드거래내역'

VIEW (Cost=50 Card=833K Bytes=13M)

TABLE ACCESS (FULL) OF 'SYS.SYS_TEMP_0FD9D6B4E_4C0C42BA' → 임시 테이블 사용

SORT (GROUP BY) (Cost=36K Card=746 Bytes=20K)

VIEW (Cost=36K Card=746 Bytes=20K)

UNION-ALL

SORT (GROUP BY) (Cost=34K Card=1 Bytes=74)

HASH JOIN (Cost=34K Card=1 Bytes=74)

PARTITION RANGE (ITERATOR)

PARTITION HASH (ALL)

TABLE ACCESS (FULL) OF '현금거래내역' (Cost=34K Card=1 Bytes=58)

VIEW (Cost=50 Card=833K Bytes=13M)

TABLE ACCESS (FULL) OF 'SYS.SYS_TEMP_0FD9D6B4E_4C0C42BA' → 임시 테이블 사용

SORT (GROUP BY) (Cost=2K Card=745 Bytes=38K)

HASH JOIN (Cost=2K Card=746 Bytes=38K)

…

Willy's IT knowledge base

2014년 7월 10일 목요일

Using Advanced SQL

제 1 절 고급 SQL 활용

1. CASE문 활용

2. 데이터 복제 기법 활용

3. Union All을 활용한 M:M 관계의 조인

4. 페이징 처리

가. 일반적인 페이징 처리용 SQL

나. 뒤쪽 페이지까지 자주 조회할 때

다. Union All 활용

5. 윈도우 함수 활용

6. WITH 구문 활용

댓글 없음:

댓글 쓰기