๋ณธ๋ฌธ ๋ฐ”๋กœ๊ฐ€๊ธฐ
ORACLE/SQL

[Oracle] ์˜ค๋ผํด WITH, RECURSIVE WITH ์žฌ๊ท€ ์ฟผ๋ฆฌ ์‚ฌ์šฉ :: ๋งˆ์ด์ž๋ชฝ

by ๐ŸŒปโ™š 2020. 3. 23.
๋‚˜๋ผ๋ณ„ ๊ธ‰์—ฌ ํ‰๊ท ์˜ ํ‰๊ท ๋ณด๋‹ค ๋งŽ์ด ๊ธ‰์—ฌ๋ฅผ ์ฃผ๊ณ  ์žˆ๋Š” ๋‚˜๋ผ์™€ ๋‚˜๋ผ์˜ ํ‰๊ท  ๊ธ‰์—ฌ๋ฅผ ๊ตฌํ•˜์‹œ์˜ค. (๋‚˜๋ผ๋ช…๊ณผ ๋‚˜๋ผ์˜ ํ‰๊ท  ๊ธ‰์—ฌ๋งŒ ์ถœ๋ ฅ)

์œ„์˜ ๋ฌธ์ œ๋ฅผ ํ’€๊ธฐ ์œ„ํ•ด ์–ด๋–ป๊ฒŒ ํ•ด์•ผํ• ๊นŒ์š”? ๋‚˜๋ผ๋ณ„ ํ‰๊ท  ๊ธ‰์—ฌ๋ฅผ ์กฐ์ธ๋ฌธ์œผ๋กœ ์ด์šฉํ•ด์„œ ๊ตฌํ•˜๋Š” ์ฟผ๋ฆฌ๋ฅผ ์ฟผ๋ฆฌA๋ผ๊ณ  ํ•˜๊ฒ ์Šต๋‹ˆ๋‹ค. ํ‰๊ท ์˜ ํ‰๊ท ๊ฐ’์„ ๊ตฌํ•˜๊ธฐ ์œ„ํ•ด์„œ๋Š” ์ฟผ๋ฆฌA๋ฅผ SUBQUERY๋กœ ์‚ฌ์šฉํ•ด์„œ ํ‰๊ท ์„ ๊ตฌํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. ํ‰๊ท ์˜ ํ‰๊ท ๊ฐ’๋ณด๋‹ค ๋†’์€ ๊ฒฐ๊ณผ๋ฅผ ์ถœ๋ ฅํ•˜๊ธฐ ์œ„ํ•ด์„œ ์ฟผ๋ฆฌA ๊ฒฐ๊ณผ์˜ PROJECTION์ด ํ•„์š”ํ•ฉ๋‹ˆ๋‹ค.

 

์œ„ ๊ณผ์ •์„ ์ƒ๊ฐํ•ด๋ดค์„๋•Œ ์ฟผ๋ฆฌA๋Š” ์ „์ฒด ์ฟผ๋ฆฌ์—์„œ 2๋ฒˆ ์‚ฌ์šฉํ•ด์•ผํ•ฉ๋‹ˆ๋‹ค. 2๋ฒˆ ์‚ฌ์šฉํ•˜๊ฒŒ๋˜๋ฉด ํผํฌ๋จผ์Šค๋„ ๋‚ฎ์•„์ง€๊ณ  ์ฟผ๋ฆฌ์˜ ๊ฐ€๋…์„ฑ๋„ ๋–จ์–ด์ง€๊ฒŒ ๋ฉ๋‹ˆ๋‹ค. ์ด๋Ÿฌํ•œ ๋ฌธ์ œ๋ฅผ ํ•ด๊ฒฐํ•˜๊ธฐ ์œ„ํ•ด์„œ WITH์ ˆ์„ ์‚ฌ์šฉํ•ด์„œ ์ฟผ๋ฆฌ๋ฌธ์„ ์žฌ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

 

์‹ค์Šต์€ Oracle HR ๊ณ„์ •์œผ๋กœ ์ง„ํ–‰ํ–ˆ์Šต๋‹ˆ๋‹ค.

WITH์ ˆ ์‚ฌ์šฉ

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
WITH
COUNTRY_AVG AS (
    SELECT
        L.COUNTRY_ID
        ,ROUND(AVG(E.SALARY)) AS COUNTRY_SAL_AVG
    FROM EMPLOYEES E, DEPARTMENTS D, LOCATIONS L
    WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
    AND D.LOCATION_ID = L.LOCATION_ID
    GROUP BY L.COUNTRY_ID
),
ALL_AVG AS (
    SELECT
        ROUND(AVG(COUNTRY_SAL_AVG)) AS COUNTRY_SAL_AVG
    FROM COUNTRY_AVG
)
SELECT
    COUNTRY_ID
    ,COUNTRY_SAL_AVG
FROM COUNTRY_AVG
WHERE COUNTRY_SAL_AVG > (
    SELECT
        COUNTRY_SAL_AVG
    FROM ALL_AVG
);
 

 

WITH ์ ˆ์„ ํ†ตํ•ด์„œ COUNTRY_AVG์™€ ALL_AVG์— ์ฟผ๋ฆฌ๋ฅผ ๋ฏธ๋ฆฌ ์ •์˜ ํ–ˆ์Šต๋‹ˆ๋‹ค.

 

COUNTRY_AVG = ์ฟผ๋ฆฌA --> ๊ฐ ๋‚˜๋ผ๋ณ„ ํ‰๊ท ๊ธ‰์—ฌ๋ฅผ ๊ตฌํ•˜๋Š” ์ฟผ๋ฆฌ

ALL_AVG --> COUNTRY_AVG๋ฅผ ๋‹ค์‹œ ํ™œ์šฉํ•ด์„œ ๋‚˜๋ผ๋ณ„ ๊ธ‰์—ฌ ํ‰๊ท ์˜ ํ‰๊ท ๊ฐ’์„ ์ •์˜ํ–ˆ์Šต๋‹ˆ๋‹ค.

 

์ตœ์ข…์ ์œผ๋กœ COUNTRY_AVG์—์„œ ALL_AVG๊ฒฐ๊ณผ๋ณด๋‹ค ๋†’์€ ๊ธ‰์—ฌํ‰๊ท ์„ ๊ฐ–๊ณ  ์žˆ๋Š” ๋‚˜๋ผ๋“ค๋งŒ ์ถœ๋ ฅํ–ˆ์Šต๋‹ˆ๋‹ค.

 

์ด๋ ‡๊ฒŒ ๋ฏธ๋ฆฌ ์ •์˜ํ•œ ์ฟผ๋ฆฌ๋ฅผ ํ•œ๋ฒˆ ์ž‘์„ฑํ•˜์—ฌ 2๋ฒˆ์ด์ƒ ์‚ฌ์šฉํ•˜๊ฒŒ ๋˜๋Š” ๊ฒฝ์šฐ์—์„œ ๊ฐ€๋…์„ฑ์„ ๋†’ํž ์ˆ˜ ์žˆ๊ณ  ์‹ค์ œ๋กœ ์‹คํ–‰ ๊ฒฐ๊ณผ๋ฅผ ๊ฐ–๊ณ  ์žˆ์–ด ํผํฌ๋จผ์Šค๋„ ๋†’์—ฌ์ค๋‹ˆ๋‹ค.

 

 

RECURSIVE WITH ์žฌ๊ท€ ์ฟผ๋ฆฌ

์—ฌ๋Ÿฌ ํ”„๋กœ๊ทธ๋žจ ์–ธ์–ด๋“ค์€ ์žฌ๊ท€ํ•จ์ˆ˜์˜ ๊ฐœ๋…์ด ์žˆ์Šต๋‹ˆ๋‹ค. ์ž์‹ ์˜ ํ•จ์ˆ˜๋ฅผ ํ•จ์ˆ˜๋กœ์ง ์•ˆ์—์„œ ๋‹ค์‹œ ๋ถˆ๋Ÿฌ ์‚ฌ์šฉํ•˜๋Š” ๊ธฐ์ˆ ์ž…๋‹ˆ๋‹ค. ์žฌ๊ท€ํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•œ ๋Œ€ํ‘œ์ ์ธ ์•Œ๊ณ ๋ฆฌ์ฆ˜์œผ๋กœ ํ”ผ๋ณด๋‚˜์น˜ ์ˆ˜์—ด, ํŒฉํ† ๋ฆฌ์–ผ ๊ตฌํ˜„, ์—ฐ์†๋œ ์ˆ˜์˜ ํ•ฉ์ด ์žˆ์Šต๋‹ˆ๋‹ค.

 

 

์—ฐ์†๋œ ์ˆ˜์˜ ํ•ฉ

์—ฐ์†๋œ ์ˆ˜์˜ ํ•ฉ์„ ๊ทธ๋ฆผ์œผ๋กœ ํ‘œํ˜„ํ•ด๋ดค์Šต๋‹ˆ๋‹ค. ์ธ์ž๋กœ 1๊ณผ 9๊ฐ€ ๋“ค์–ด๊ฐ€๋ฉด 1๋ถ€ํ„ฐ 9๊นŒ์ง€ ๋ชจ๋“  ์ˆซ์ž๋ฅผ ๋”ํ•ด์„œ 45๋ผ๋Š” ๊ฒฐ๊ณผ๋ฅผ ์ถœ๋ ฅํ•ฉ๋‹ˆ๋‹ค. ์ด ์•Œ๊ณ ๋ฆฌ์ฆ˜์„ ์žฌ๊ท€ํ•จ์ˆ˜์‹์œผ๋กœ ํ‘œํ˜„ํ•˜๋ฉด ์•„๋ž˜์™€ ๊ฐ™์Šต๋‹ˆ๋‹ค.

 

 

์ด์ „์˜ ๊ฒฐ๊ณผ๋ฅผ ๊ณ„์†ํ•ด์„œ ์ธ์ž๋กœ ๊ฐ’์œผ๋กœ ๋„ฃ์–ด์ฃผ๊ณ  ๋” ์ด์ƒ ๋„ฃ์–ด์ค„ ์ธ์ž๊ฐ€ ์—†์„ ๋•Œ ๋‚˜๋จธ์ง€ ๋‚˜๋จธ์ง€ ์ฟผ๋ฆฌ๊ฐ€ ์ง„ํ–‰๋ฉ๋‹ˆ๋‹ค.

 

 

์žฌ๊ท€ ์ฟผ๋ฆฌ

1
2
3
4
5
6
7
8
9
10
11
12
13
WITH CONTINUOUS(NUM, RESULT) AS(
    SELECT 1,1 FROM DUAL
    UNION ALL
    SELECT
        NUM+1
        ,(NUM+1+ RESULT
    FROM CONTINUOUS
    WHERE NUM < 9
)
SELECT
    NUM
    ,RESULT
FROM CONTINUOUS;

ORACLE RECURSIVE WITH๋ฅผ ์‚ฌ์šฉํ•ด์„œ ์žฌ๊ท€ ์ฟผ๋ฆฌ๋ฅผ ์ •์˜ํ•ด๋ดค์Šต๋‹ˆ๋‹ค.

 

๋Œ“๊ธ€