공통 테이블 식 (CTE)

Snowflake에서 데이터 조작

Jake Roach

Field Data Engineer

공통 테이블 식 (CTE)

공통 테이블 식(CTE)은 쿼리 결과를 일시 저장하여 다른 쿼리에서 사용합니다

CTE는 쿼리 시작 부분에 정의합니다
WITH <cte-name> AS ( <query> )
서브쿼리와 유사

-- CTE 이름을 지정하고
-- 괄호 안에 쿼리를 작성합니다
WITH <cte-name> AS (
    <query>
)


SELECT
    ...
FROM <cte-name>
... ;

위험 학생 보고서 작성

WITH at_risk AS (
    SELECT
        student_id
        course_name,
        teacher_name,
        grade
    FROM student_courses
    WHERE grade < 70 AND is_required
)


SELECT
    students.student_name,
    at_risk.*
FROM at_risk
JOIN students ON at_risk.student_id = students.id;

임시 결과가 at_risk에 저장됨
레코드의 하위 집합 선택
at_risk를 조회하여 보고서 생성

위험 학생 보고서 작성

하위 집합 결과와 최종 보고서를 보여주는 CTE 결과

서브쿼리와 CTE 비교

SELECT
    month_num,
    AVG(differential) AS avg_differential
    MIN(differential) AS most_differential
FROM (

    SELECT
        month_num,
        windchill - temperature AS differential
    FROM weather
    WHERE 
        season = 'Winter' AND
        temperature < 32

)
GROUP BY month_num;

WITH daily_temperature_differential AS (
    SELECT
        month_num,
        windchill - temperature AS differential
    FROM weather
    WHERE 
        season = 'Winter' AND 
        temperature < 32
)

SELECT
    month_num,
    AVG(differential) AS avg_differential
    MIN(differential) AS most_differential
FROM daily_temperature_differential
GROUP BY month_num;

기온 차이 계산

WITH daily_temperature_differential AS (
    SELECT
        month_num,
        windchill - temperature AS differential
    FROM weather
    WHERE 
        season = 'Winter' AND 
        temperature < 32
)


SELECT
    month_num,
    AVG(differential) AS avg_differential
    MIN(differential) AS most_differential
FROM daily_temperature_differential
GROUP BY month_num;

          | month_num | differential |
          | --------- | ------------ |
          |     12    |      -12     |
          |     1     |      -3      |
          |     1     |       0      |
          |     2     |      -7      |

흐름이 서브쿼리보다 더 자연스럽습니다.

| month_num | avg_differential | most_differential |
| --------- | ---------------- | ----------------- |
|     12    |       -5.77      |        -14        |
|     1     |       -1.91      |        -8         |
|     2     |       -8.13      |        -22        |

연습해 봅시다!

Snowflake에서 데이터 조작