공통 테이블 식

SQL에서의 데이터 조작

Mona Khalil

Data Scientist, Greenhouse Software

서브쿼리를 추가하면...

쿼리 복잡도는 빠르게 증가합니다!
- 정보 추적이 어려울 수 있습니다

해결: 공통 테이블 식(CTE)!

공통 테이블 식

공통 테이블 식(CTE)

메인 쿼리 전에 선언하는 테이블
이름을 지정하고 이후 FROM에서 참조

CTE 설정

WITH cte AS (
    SELECT col1, col2
    FROM table)

SELECT
    AVG(col1) AS avg_col
FROM cte;

FROM의 서브쿼리를 가져오기

SELECT
  c.name AS country,
  COUNT(s.id) AS matches
FROM country AS c
INNER JOIN (
  SELECT country_id, id 
  FROM match
  WHERE (home_goal + away_goal) >= 10) AS s
ON c.id = s.country_id
GROUP BY country;

| country     | matches |
|-------------|---------|
| England     | 3       |
| Germany     | 1       |
| Netherlands | 1       |
| Spain       | 4       |

시작 부분에 배치

(
  SELECT country_id, id 
  FROM match
  WHERE (home_goal + away_goal) >= 10
)

시작 부분에 배치

WITH s AS (
  SELECT country_id, id 
  FROM match
  WHERE (home_goal + away_goal) >= 10
)

CTE를 확인해 봅시다

WITH s AS (
  SELECT country_id, id 
  FROM match
  WHERE (home_goal + away_goal) >= 10
)
SELECT
  c.name AS country,
  COUNT(s.id) AS matches
FROM country AS c
INNER JOIN s
ON c.id = s.country_id
GROUP BY country;

| country     | matches |
|-------------|---------|
| England     | 3       |
| Germany     | 1       |
| Netherlands | 1       |
| Spain       | 4       |

모든 CTE를 사용해 봅시다

WITH s1 AS (
  SELECT country_id, id 
  FROM match
  WHERE (home_goal + away_goal) >= 10),
s2 AS (                              -- New subquery
  SELECT country_id, id 
  FROM match
  WHERE (home_goal + away_goal) <= 1
)
SELECT
  c.name AS country,
  COUNT(s1.id) AS high_scores,
  COUNT(s2.id) AS low_scores         -- New column
FROM country AS c
INNER JOIN s1
ON c.id = s1.country_id
INNER JOIN s2                        -- New join
ON c.id = s2.country_id
GROUP BY country;

왜 CTE를 쓰나요?

한 번만 실행됨
- CTE는 메모리에 저장됨
- 쿼리 성능 향상
쿼리 구조 정리에 도움
다른 CTE 참조 가능
자기 자신 참조 가능(SELF JOIN)

연습해 봅시다!

SQL에서의 데이터 조작