[Oracle/SQL] With 절

WITH 절

최근에 프로그래머스를 풀면서

WITH절로 해결하신 풀이를 보고 포스팅을 해보려 합니다.

1. with절

서브쿼리 블록을 with절 블록으로 만들어서 별칭으로 간단하게 관리하는 방식
즉, 이름이 있는 서브쿼리(12c이후로는 함수,*프로시져도 가능)

*프로시져 는 with절내부함수에서만 호출가능하다.

추가 지식) WITH 절은 CTE(Common Table Expression)을 정의하는 데 사용됩니다.
CTE는 SQL 쿼리에서 임시 결과 집합을 정의하여 쿼리의 가독성을 높이고 복잡한 쿼리를 단순화하는 데 유용

---

2. 사용방법

2-1. 기본형식

with sql별칭 [(컬럼명,컬럼명2)] as ( 
        SQL쿼리
    )
    select * from sql별칭; -- 메인쿼리

-- 다중 WITH
WITH  sql별칭 as ( 
            SQL쿼리
    )
    , sql별칭2 as ( 
            SQL쿼리2
    ) 
    select * from sql별칭;

---

2-2. 사용예시

(1-1) 사용전 -사용후 예시1

 

WITH절을 사용 전 쿼리는 from문에 서브쿼리를 통해서 작성했습니다. 

SELECT year,
       total_sales
FROM (
    SELECT TO_CHAR(SALES_DATE, 'YYYY') AS year,
           SUM(sales_amount) AS total_sales
    FROM sales
    GROUP BY TO_CHAR(SALES_DATE, 'YYYY')
)
WHERE total_sales > 10000;

 

With절을 사용 후

WITH SalesSummary AS (
    SELECT TO_CHAR(SALES_DATE, 'YYYY') AS year,
           SUM(sales_amount) AS total_sales
    FROM sales
    GROUP BY TO_CHAR(SALES_DATE, 'YYYY')
)
SELECT year,
       total_sales
FROM SalesSummary
WHERE total_sales > 10000;

 

메인쿼리에 간략화 된 차이밖에 없지만 살짝 복잡한 쿼리의 경우를 한번 보겟습니다

아래 예시는 프로그래머스 에서 푼 문제를 WITH문을 적용해보았습니다

(1-2) 사용전 -사용후 예시2

--사용전
SELECT       TO_CHAR(o.SALES_DATE,'YYYY') AS year 
           , TO_NUMBER(TO_CHAR(o.SALES_DATE,'MM')) AS month 
           , COUNT(DISTINCT o.user_id) as puchased_users
           , ROUND(COUNT(DISTINCT o.user_id)/(SELECT count(user_id)
                                                FROM user_info 
                                               WHERE TO_CHAR(joined,'YYYY')  = '2021'),1) as puchased_ratio

  FROM       ONLINE_SALE o , (SELECT user_id 
                                FROM user_info 
                               WHERE TO_CHAR(joined,'YYYY')  = '2021') u

 WHERE    u.user_id = o.user_id
 GROUP BY TO_CHAR(o.SALES_DATE,'YYYY'), TO_NUMBER(TO_CHAR(o.SALES_DATE,'MM')) 
 ORDER BY year, month
;

 

-- 사용후
WITH user_2021 as  (SELECT user_id 
                      FROM user_info 
                     WHERE TO_CHAR(joined,'YYYY')  = '2021')
                     
SELECT       TO_CHAR(o.SALES_DATE,'YYYY') AS year 
           , TO_NUMBER(TO_CHAR(o.SALES_DATE,'MM')) AS month 
           , COUNT(DISTINCT o.user_id) as puchased_users
           , ROUND(COUNT(DISTINCT o.user_id)/(SELECT count(*) FROM user_2021),1) as puchased_ratio

  FROM    ONLINE_SALE o , user_2021 u 
 WHERE    u.user_id = o.user_id
 GROUP BY TO_CHAR(o.SALES_DATE,'YYYY'), TO_NUMBER(TO_CHAR(o.SALES_DATE,'MM')) 
 ORDER BY year, month
;

 

with절을 이용하여 SELECT에는 직접 참조를 통해 SELECT문을 통해 서브쿼리를 작성이 가능해집니다.

 

(2) sql별칭 [(컬럼명,컬럼명2)]

WITH SalesSummary (year, total_sales) AS (
    SELECT        TO_CHAR(SALES_DATE, 'YYYY') AS year
            ,  SUM(sales_amount)           AS total_sales
    FROM sales
    GROUP BY TO_CHAR(SALES_DATE, 'YYYY')
)
SELECT   year
       , total_sales
FROM SalesSummary
WHERE total_sales > 10000
ORDER BY year;

 

 

(3) 다중 WITH

굳이 할 필요는 없지만 한번 위에 문을 다중 WITH로 또 설계해보겠습니다

해당 쿼리에서는 ROUND에서 서브쿼리로 직접 넣는게 좋지만 다중WITH를 쓰기위해서 억지로 넣어봤습니다.

-- 다중WITH
WITH user_2021 as  (SELECT user_id 
                      FROM user_info 
                     WHERE TO_CHAR(joined,'YYYY')  = '2021')
  ,  count_2021 as (SELECT count(*) as counting 
                      FROM user_2021)
SELECT       TO_CHAR(o.SALES_DATE,'YYYY') AS year 
           , TO_NUMBER(TO_CHAR(o.SALES_DATE,'MM')) AS month 
           , COUNT(DISTINCT o.user_id) as puchased_users
           , ROUND(COUNT(DISTINCT o.user_id)/c.counting,1) as puchased_ratio

  FROM    ONLINE_SALE o , user_2021 u ,count_2021 c
 WHERE    u.user_id = o.user_id
 GROUP BY TO_CHAR(o.SALES_DATE,'YYYY'), TO_NUMBER(TO_CHAR(o.SALES_DATE,'MM')) , c.counting 
 ORDER BY year, month
;

 

보면 with절 자체에서 또 with를 참조할 수 있다는 것을 볼 수 있습니다.

---

3. with 심화

3-1. with 와 서브쿼리 차이 (참조)

위에 코드에서 알 수 있는 점은 with절 -> 서브쿼리 참조가능하다
또한 다중 with절에서도  with절간 참조도 가능하다 (with->with)
하지만  서브쿼리 -> 서브쿼리 간에는 참조가 불가능합니다. 

하단 예시 코드는 서브쿼리간 참조를 시도했을경우 나오는 오류예시입니다.

-- 참조불가능 예시일부 1(서브 -> 서브)
SELECT    (SELECT count(*) FROM u) 
  FROM    (SELECT user_id 
             FROM user_info 
            WHERE TO_CHAR(joined,'YYYY')  = '2021') u

-- SQL 실행 중 오류가 발생하였습니다.
-- ORA-00942: table or view does not exist

-- 참조불가능 예시일부 2 (서브 -> 서브)
FROM    (SELECT user_id  FROM user_info WHERE TO_CHAR(joined,'YYYY')  = '2021') u
      , (SELECT count(*) FROM u) c 
-- SQL 실행 중 오류가 발생하였습니다.
-- ORA-00942: table or view does not exist

 

3-2. with 동작방식

with절은 무조건 쓰면 좋은 것 같은데 어떠한 방식으로 동작하는지 한번 확인해보고 

주의해야할 점을 이야기 해보겠습니다.

 

(1) inline view

Inline View는 WITH 절을 사용하여 정의된 서브쿼리가 실제로 임시 테이블을 생성하지 않고,
쿼리의 일부분으로 직접 삽입되는 방식입니다.

• 특징
  • 비물리적 저장
    • 물리적으로 저장되지않음
  • 단일 사용
    • 메인쿼리에서 단 한번만 사용(참조)되는 경우엔 서브쿼리처럼 작동
    • 여러번이여도 상황에 따라 inline view가 될 때 있음

(2) Materialize

Materialize에서는 WITH 절로 정의된 결과가 물리적으로 저장됩니다.
결과가 임시 테이블이나 메모리 내의 구조체로 저장되어,
여러 번 참조되거나 복잡한 쿼리에서 사용될 때 성능을 개선할 수 있습니다.

• 특징
  • 물리적 저장
    • 결과가 물리적으로 저장되어, 여러 번 참조되는 경우에도 결과가 재사용
  • 단일 실행
    • 결과는 단 한 번 계산되며, 이 결과를 재사용할 수 있습니다.
      따라서 여러 번 참조되거나 복잡한 쿼리에서 사용할 때 성능이 향상될 수 있습니다.

 

3-3. 성능고려(주의할점)

위에서 보다시피 Materialize를 보면 결과가 물리적으로 저장이 된다는 것을 알 수 있습니다.
즉슨, 한번 오래걸리고 많이 참조하는 서브쿼리일 경우에는 효율적일 것 입니다.

 

그럼 비슷한 view를 안쓰고 쓰면 모든 쿼리작성할 때 with를 쓰면 될 것 같지만 차이점은 존재합니다.

VIEW는 한번 만들어놓으면 DROP 할 때까지 없어지지 않지만 WITH절의 경우는 늘 생성, 삭제를 반복하게 됩니다.

이 사소한 차이는 만약 결과데이터가 많은 경우에는 WITH를 사용시 오히려 저장장치에 과부화를 줄 수 있습니다.

 

또한 with 절은 한번 돌릴경우 물리적으로 저장하기 위해(임시테이블생성) 모든 절을 한번씩 돌기 때문에 서브쿼리로만 작성된 구문보다는 구문을 돌릴때마다 메모리사용량이 높을 수 있습니다

 

• 요약 하자면 
• 사용이 많이되는 서브쿼리거나 오래걸리되 종종 쓰는의 경우는 with절로 사용할 경우에는 좋다 (서브쿼리는 각각 실행됨)
• 사용도가 적거나 재사용하기 힘든 서브쿼리이거나 결과값(테이블이 큼)이 많은경우인 경우는 with절 보다 서브쿼리 사용고려

 

---

4. 요약

WITH절이란?

• 이름이 부여된 서브쿼리(12c이후 함수,프로시저)

WITH절을 사용하는 이유

• 쿼리가독성(쿼리블록화)
• 서브쿼리간 참조가 안되는데 with절간 참조가능 (가독성)
• 성능향상(많이 호출 되는 경우 고려후 사용하면 성능의 개선)

성능고려

• 실행오래걸리되 결과데이터가 적은경우 좋음
• 데이터가 많으면 과부화 문제가 있음
• 단순 사용시 쿼리가독성에는 좋음