공통 테이블 식 (CTE)

BigQuery 입문

Matthew Forrest

Field CTO

CTE란?

-- WITH 키워드로 CTE를 시작합니다
WITH our_cte AS (
  SELECT column1, column2
  FROM table
)


/* CTE는 괄호로 감싸며, 아래 본문 쿼리에서
테이블처럼 사용할 수 있습니다 */
SELECT *
FROM our_cte

서브쿼리 vs. CTE

서브쿼리

SELECT *
FROM (
  SELECT order_id, status
  FROM ecommerce.ecomm_order_status
) AS subquery

CTE

WITH cte_name AS (
  SELECT order_id, status
  FROM ecommerce.ecomm_order_status
)
SELECT *
FROM cte_name

CTE 작성 방법

-- 모든 CTE는 WITH 키워드로 시작합니다
WITH cte_name AS


-- CTE는 괄호로 감쌉니다
(SELECT col_1, col_2
 FROM tablename WHERE col_1 > 10
)


-- 새 CTE 'cte_name'은 이 쿼리 범위에서만 접근 가능합니다

SELECT * FROM cte_name LIMIT 10

복수 CTE 사용

WITH orders AS (
  SELECT order_id, status
  FROM ecommerce.ecomm_order_status
),

-- 추가 CTE는 WITH를 반복하지 않고 쉼표로 구분합니다
order_details AS (
SELECT orders.*, details.order_items
FROM ecommerce.ecomm_orders details 
```

CTE로 데이터 필터링

WITH filtered_data AS (
  SELECT *
  FROM ecommerce.ecomm_orders
  -- 여기서는 CTE에서 데이터를 필터링합니다
  WHERE order_status = 'delivered'
)
SELECT order_id, order_status
FROM filtered_data

| order_id | order_status |
|----------|--------------|
| 1        | delivered    |
| 2        | delivered    |

CTE로 쿼리 최적화

WITH precomputed_data AS (
  SELECT order_id, SUM(payment_value) AS total
  FROM ecommerce.ecomm_payments
  GROUP BY order_id
)
SELECT *
FROM precomputed_data
WHERE total > 1000

| order_id | total   |
|----------|---------|
| 1        | 50000   |
| 2        | 8000    |

CTE로 데이터 조인

WITH cte1 AS (
  SELECT order_id, status
  FROM ecommerce.ecomm_order_status
),
cte2 AS (
  SELECT order_id, order_items
  FROM ecommerce.ecomm_orders
)
SELECT *
FROM cte1
JOIN cte2 USING (order_id)

연습해 봅시다!

BigQuery 입문