[Oracle] 오라클 WITH, RECURSIVE WITH 재귀 쿼리 사용 :: 마이자몽

나라별 급여 평균의 평균보다 많이 급여를 주고 있는 나라와 나라의 평균 급여를 구하시오. (나라명과 나라의 평균 급여만 출력)

위의 문제를 풀기 위해 어떻게 해야할까요? 나라별 평균 급여를 조인문으로 이용해서 구하는 쿼리를 쿼리A라고 하겠습니다. 평균의 평균값을 구하기 위해서는 쿼리A를 SUBQUERY로 사용해서 평균을 구할 수 있습니다. 평균의 평균값보다 높은 결과를 출력하기 위해서 쿼리A 결과의 PROJECTION이 필요합니다.

 

위 과정을 생각해봤을때 쿼리A는 전체 쿼리에서 2번 사용해야합니다. 2번 사용하게되면 퍼포먼스도 낮아지고 쿼리의 가독성도 떨어지게 됩니다. 이러한 문제를 해결하기 위해서 WITH절을 사용해서 쿼리문을 재사용할 수 있습니다.

 

실습은 Oracle HR 계정으로 진행했습니다.

WITH절 사용

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라는 결과를 출력합니다. 이 알고리즘을 재귀함수식으로 표현하면 아래와 같습니다.

 

 

이전의 결과를 계속해서 인자로 값으로 넣어주고 더 이상 넣어줄 인자가 없을 때 나머지 나머지 쿼리가 진행됩니다.

 

 

재귀 쿼리

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를 사용해서 재귀 쿼리를 정의해봤습니다.