Menggunakan CTE dengan Redshift

Pengantar Redshift

Jason Myers

Principal Architect

Common table expression (CTE)

Himpunan hasil sementara
Menyederhanakan query
Alternatif subquery

Struktur Subquery dan CTE

SELECT division_name,
       revenue_total
       -- Subquery untuk 10 divisi teratas berdasarkan 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 untuk 10 divisi teratas berdasarkan 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)


-- Query utama
SELECT division_id,
       revenue_total
       -- DARI CTE kita
  FROM top_ten_divisions_by_rev
 WHERE revenue_total > 100000;

Beberapa CTE

-- CTE 10 divisi teratas berdasarkan 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),

-- CTE ID dan Nama divisi
division_names AS(
  SELECT id AS division_id,
         name AS division_name
    FROM divisions)

-- Query utama
SELECT division_name,
       revenue_total
       -- DARI CTE Top 10
  FROM top_ten_divisions_by_rev
  -- Join agar dapat memakai nama divisi
  JOIN division_names USING (DIVISION_ID)
 WHERE revenue_total > 100000;

Beberapa CTE, join CTE

-- Memilih nama divisi
WITH division_names AS(
  SELECT id AS division_id,
         name AS division_name
    FROM divisions),
-- Memilih 10 divisi teratas berdasarkan revenue
top_ten_divisions_by_rev AS(
  SELECT division_name,
         SUM(revenue) AS revenue_total
    FROM orders
    -- Join CTE division_names
    JOIN divisions USING (division_id)
   GROUP BY division_name
   ORDER BY revenue_total DESC
   LIMIT 10);

Hukum Kernighan

Semua orang tahu debugging dua kali lebih sulit daripada menulis program sejak awal. Jadi jika Anda sudah secerdas mungkin saat menulisnya, bagaimana Anda akan men-debugnya?

Kinerja CTE

Sama seperti subquery
Lebih baik dari subquery jika dipakai ulang

Kode SQL vs Urutan eksekusi

Ayo berlatih!

Pengantar Redshift