Redshift에서 CTE 사용

Redshift 입문

Jason Myers

Principal Architect

공통 테이블 표현식(CTE)

임시 결과 집합
쿼리 단순화
서브쿼리의 대안

서브쿼리와 CTE 구조

SELECT division_name,
       revenue_total
       -- Subquery for top 10 divions by revenue
  FROM (SELECT division_id,
               SUM(revenue) AS revenue_total
          FROM orders
         GROUP BY division_id
         ORDER BY revenue_total DESC
         LIMIT 10) top_ten_divisions_by_rev
 WHERE revenue_total > 100000;

-- CTE for top 10 divions by revenue
WITH top_ten_divisions_by_rev AS (
  SELECT division_id,
         SUM(revenue) AS revenue_total
    FROM orders
   GROUP BY division_id
   ORDER BY revenue_total DESC
   LIMIT 10)


-- Main query
SELECT division_id,
       revenue_total
       -- FROM our CTE
  FROM top_ten_divisions_by_rev
 WHERE revenue_total > 100000;

다중 CTE

-- Top 10 divisions by revenue CTE
WITH top_ten_divisions_by_rev AS(
  SELECT division_id,
         SUM(revenue) AS revenue_total
    FROM orders
   GROUP BY division_id
   ORDER BY revenue_total DESC
   LIMIT 10),

-- Division ID and Name CTE
division_names AS(
  SELECT id AS division_id,
         name AS division_name
    FROM divisions)

-- Main query
SELECT division_name,
       revenue_total
       -- FROM Top 10 CTE
  FROM top_ten_divisions_by_rev
  -- Joining so we can use the division name
  JOIN division_names USING (DIVISION_ID)
 WHERE revenue_total > 100000;

다중 CTE, CTE 조인

-- Selecting the division names
WITH division_names AS(
  SELECT id AS division_id,
         name AS division_name
    FROM divisions),
-- Selecting the top ten divisions by revenue
top_ten_divisions_by_rev AS(
  SELECT division_name,
         SUM(revenue) AS revenue_total
    FROM orders
    -- Joining in the division_names CTE
    JOIN divisions USING (division_id)
   GROUP BY division_name
   ORDER BY revenue_total DESC
   LIMIT 10);

커니핸의 법칙

디버깅은 처음에 프로그램을 작성하는 것보다 두 배 어렵다. 작성할 때 가능한 한 영리하게 만든다면, 어떻게 디버깅하겠는가?

CTE 성능

서브쿼리와 동일
재사용 시 서브쿼리보다 효율적

SQL 코드 vs 실행 순서

연습해 봅시다!

Redshift 입문