Dùng CTE với Redshift

Giới thiệu về Redshift

Jason Myers

Principal Architect

Common table expressions (CTE)

Tập kết quả tạm thời
Đơn giản hóa truy vấn
Thay thế cho truy vấn lồng

Cấu trúc subquery và 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;

Nhiều 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;

Nhiều CTE, join 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);

Định luật Kernighan

Ai cũng biết gỡ lỗi khó gấp đôi viết chương trình ngay từ đầu. Vậy nếu bạn viết thông minh hết mức, làm sao bạn gỡ lỗi được?

Hiệu năng CTE

Giống truy vấn lồng
Tốt hơn nếu tái sử dụng

Mã SQL vs Thứ tự thực thi

Ayo berlatih!

Giới thiệu về Redshift