Menguasai SQL Tingkat Lanjut: CTE dan Window Functions untuk Query yang Lebih Cerdas dan Efisien

1. Pendahuluan

Sebagai developer web, kita sering berinteraksi dengan database, terutama database relasional seperti PostgreSQL atau MySQL. Query dasar seperti SELECT, INSERT, UPDATE, DELETE, dan JOIN mungkin sudah jadi makanan sehari-hari. Namun, pernahkah Anda merasa query yang Anda tulis mulai membengkak, sulit dibaca, atau bahkan menjadi sarang subquery bertingkat yang membingungkan? 😫

Masalah ini umum terjadi ketika kita perlu melakukan analisis data yang lebih kompleks, seperti menghitung rata-rata bergerak, peringkat, atau total kumulatif. Di sinilah SQL tingkat lanjut, khususnya Common Table Expressions (CTE) dan Window Functions, datang sebagai penyelamat.

Artikel ini akan membawa Anda menyelami dua fitur SQL powerful ini. Kita akan belajar bagaimana CTE dapat menyederhanakan query yang kompleks dan bagaimana Window Functions memungkinkan Anda melakukan perhitungan analitik yang canggih tanpa perlu bolak-balik ke aplikasi. Siap meningkatkan skill SQL Anda? Mari kita mulai! 🚀

2. Apa itu CTE (Common Table Expression)?

Bayangkan Anda sedang membuat resep masakan yang rumit. Daripada menulis semua langkah dari awal sampai akhir dalam satu paragraf panjang, Anda mungkin memecahnya menjadi bagian-bagian kecil: “Siapkan bumbu dasar”, “Potong sayuran”, “Tumis bahan”. Setiap bagian ini adalah langkah sementara yang hasilnya akan digunakan di langkah berikutnya.

Common Table Expression (CTE) bekerja dengan cara yang mirip di SQL. CTE adalah result set sementara yang bisa Anda referensikan dalam query SELECT, INSERT, UPDATE, atau DELETE berikutnya. Ia hanya ada selama eksekusi query tersebut.

📌 Manfaat Utama CTE:

1. Meningkatkan Keterbacaan (Readability): Query kompleks dipecah menjadi bagian-bagian logis yang lebih kecil.
2. Reusability: Anda bisa mereferensikan CTE yang sama beberapa kali dalam query utama.
3. Memudahkan Query Rekursif: CTE adalah kunci untuk menulis query yang memproses data hierarkis (misalnya, struktur organisasi, daftar kategori bersarang).
4. Menggantikan Subquery Bersarang: Seringkali, CTE bisa membuat query multi-langkah lebih mudah dipahami daripada subquery yang saling menumpuk.

✅ Struktur Dasar CTE:

WITH nama_cte_pertama AS (
    -- Query untuk CTE pertama
    SELECT kolom1, kolom2
    FROM tabel_sumber
    WHERE kondisi
),
nama_cte_kedua AS (
    -- Query untuk CTE kedua, bisa mereferensikan nama_cte_pertama
    SELECT kolomA, kolomB
    FROM nama_cte_pertama
    WHERE kondisi_lain
)
-- Query utama yang menggunakan CTEs
SELECT *
FROM nama_cte_kedua
WHERE kondisi_akhir;

💡 Contoh Praktis: Menghitung Rata-rata Penjualan per Kategori

Misalkan kita punya tabel products dan orders seperti ini:

-- Tabel products
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(50),
    price DECIMAL(10, 2)
);

-- Tabel order_items (menghubungkan order dengan product)
CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    item_price DECIMAL(10, 2)
);

-- Contoh data
INSERT INTO products (product_name, category, price) VALUES
('Laptop Pro', 'Electronics', 1200.00),
('Mechanical Keyboard', 'Electronics', 150.00),
('Mouse Ergonomic', 'Electronics', 75.00),
('Novel Fantasy', 'Books', 25.00),
('Cookbook', 'Books', 30.00);

INSERT INTO order_items (order_id, product_id, quantity, item_price) VALUES
(1, 1, 1, 1200.00),
(1, 2, 1, 150.00),
(2, 3, 2, 75.00),
(3, 4, 3, 25.00),
(3, 5, 1, 30.00),
(4, 1, 1, 1200.00);

Kita ingin mencari total penjualan untuk setiap kategori dan kemudian menghitung rata-rata penjualan per kategori tersebut.

Tanpa CTE (menggunakan subquery):

SELECT
    p.category,
    SUM(oi.quantity * oi.item_price) AS total_penjualan_per_kategori
FROM
    products p
JOIN
    order_items oi ON p.product_id = oi.product_id
GROUP BY
    p.category;

Query di atas hanya menghitung total penjualan per kategori. Jika kita ingin lebih lanjut, misalnya rata-rata penjualan per order untuk setiap kategori, maka query bisa jadi lebih rumit.

Dengan CTE:

WITH CategorySales AS (
    SELECT
        p.category,
        SUM(oi.quantity * oi.item_price) AS total_sales
    FROM
        products p
    JOIN
        order_items oi ON p.product_id = oi.product_id
    GROUP BY
        p.category
)
SELECT
    cs.category,
    cs.total_sales,
    AVG(cs.total_sales) OVER () AS rata_rata_penjualan_global -- Ini sudah menggunakan Window Function
FROM
    CategorySales cs;

Dalam contoh ini, CategorySales adalah CTE yang pertama kali menghitung total penjualan per kategori. Kemudian, query utama menggunakan CTE ini untuk menampilkan hasilnya. Meskipun contoh ini sederhana, Anda bisa bayangkan bagaimana CTE akan sangat membantu jika CategorySales ini sendiri adalah query yang sangat kompleks atau perlu direferensikan berkali-kali.

3. Memanfaatkan Window Functions: Analisis Data Lebih Mendalam

Jika CTE membantu kita mengorganisir query secara horizontal (memecah query besar jadi bagian-bagian), maka Window Functions membantu kita menganalisis data secara vertikal atau berdasarkan jendela (window) data tertentu.

Pikirkan Window Function sebagai fungsi agregat (seperti SUM, AVG, COUNT) tetapi dengan perbedaan krusial: ❌ Fungsi Agregat Biasa: Mengelompokkan baris dan menghasilkan satu baris hasil per kelompok. Baris-baris asli hilang. ✅ Window Functions: Melakukan perhitungan agregat atau analitik pada sekumpulan baris terkait (disebut “window” atau “jendela”) tetapi tetap mengembalikan baris-baris asli.

📌 Struktur Dasar Window Functions:

FUNGSI_WINDOW() OVER (
    [PARTITION BY kolom1, kolom2, ...] -- Opsional: Membagi data menjadi kelompok
    [ORDER BY kolom3, kolom4, ...]    -- Opsional: Mengurutkan data dalam setiap kelompok
    [ROWS/RANGE BETWEEN ...]         -- Opsional: Menentukan "frame" dalam window
)

💡 Konsep Penting: PARTITION BY dan ORDER BY

• PARTITION BY: Ini seperti GROUP BY untuk Window Functions. Ia membagi result set menjadi partisi-partisi atau kelompok independen. Window function akan dihitung secara terpisah untuk setiap partisi.
• ORDER BY: Ini menentukan urutan baris dalam setiap partisi (atau seluruh result set jika tidak ada PARTITION BY). Urutan ini sangat penting untuk fungsi-fungsi seperti ROW_NUMBER(), LAG(), atau LEAD().

✅ Jenis-jenis Window Functions yang Umum:

1. Ranking Functions:

   • ROW_NUMBER(): Memberikan nomor baris unik dalam setiap partisi, dimulai dari 1.
   • RANK(): Memberikan peringkat dalam setiap partisi. Baris dengan nilai yang sama mendapatkan peringkat yang sama, dan peringkat berikutnya akan dilewati (misal: 1, 1, 3).
   • DENSE_RANK(): Mirip RANK(), tetapi tidak melewati peringkat (misal: 1, 1, 2).
   • NTILE(N): Membagi baris dalam setiap partisi menjadi N kelompok, memberikan nomor kelompok.

   Contoh: Menentukan peringkat produk terlaris di setiap kategori.

   WITH ProductSales AS (
       SELECT
           p.product_name,
           p.category,
           SUM(oi.quantity * oi.item_price) AS total_product_sales
       FROM
           products p
       JOIN
           order_items oi ON p.product_id = oi.product_id
       GROUP BY
           p.product_name, p.category
   )
   SELECT
       product_name,
       category,
       total_product_sales,
       ROW_NUMBER() OVER (PARTITION BY category ORDER BY total_product_sales DESC) AS rank_per_category,
       RANK() OVER (PARTITION BY category ORDER BY total_product_sales DESC) AS dense_rank_per_category
   FROM
       ProductSales;

   Output akan menunjukkan setiap produk dengan total penjualannya, plus peringkatnya di dalam kategorinya masing-masing.

2. Analytic Functions:

   • LAG(kolom, offset, default): Mengambil nilai kolom dari baris sebelumnya dalam window.
   • LEAD(kolom, offset, default): Mengambil nilai kolom dari baris berikutnya dalam window.
   • FIRST_VALUE(kolom): Mengambil nilai kolom dari baris pertama dalam window.
   • LAST_VALUE(kolom): Mengambil nilai kolom dari baris terakhir dalam window.

   Contoh: Menghitung selisih penjualan antara bulan ini dan bulan sebelumnya. (Membutuhkan data time-series, tapi bisa diilustrasikan dengan urutan produk).

   -- Asumsi ada kolom sales_date di order_items untuk waktu
   -- Contoh sederhana dengan urutan produk dalam kategori
   WITH ProductSalesRanked AS (
       SELECT
           p.product_name,
           p.category,
           SUM(oi.quantity * oi.item_price) AS total_product_sales,
           ROW_NUMBER() OVER (PARTITION BY category ORDER BY SUM(oi.quantity * oi.item_price) DESC) AS rn
       FROM
           products p
       JOIN
           order_items oi ON p.product_id = oi.product_id
       GROUP BY
           p.product_name, p.category
   )
   SELECT
       product_name,
       category,
       total_product_sales,
       LAG(total_product_sales, 1, 0) OVER (PARTITION BY category ORDER BY rn) AS previous_product_sales
   FROM
       ProductSalesRanked
   ORDER BY category, rn;

   Ini akan menampilkan penjualan produk sebelumnya dalam kategori yang sama berdasarkan urutan peringkat.

3. Aggregate Window Functions:

   • SUM() OVER (...), AVG() OVER (...), COUNT() OVER (...), MIN() OVER (...), MAX() OVER (...)
   • Ini adalah fungsi agregat biasa, tetapi diterapkan pada window, bukan seluruh grup.

   Contoh: Menghitung running total penjualan per kategori.

   SELECT
       p.product_name,
       p.category,
       (oi.quantity * oi.item_price) AS item_total,
       SUM(oi.quantity * oi.item_price) OVER (PARTITION BY p.category ORDER BY p.product_name) AS running_total_category
   FROM
       products p
   JOIN
       order_items oi ON p.product_id = oi.product_id
   ORDER BY p.category, p.product_name;

   running_total_category akan mengakumulasi total penjualan item dalam setiap kategori, diurutkan berdasarkan nama produk.

4. Sinergi CTE dan Window Functions: Solusi untuk Query Kompleks

Kekuatan sebenarnya muncul ketika Anda menggabungkan CTE dan Window Functions. CTE dapat digunakan untuk menyiapkan data awal yang bersih atau hasil agregasi perantara, yang kemudian bisa dianalisis lebih lanjut menggunakan Window Functions.

🎯 Contoh Real-World: Analisis Pelanggan Loyal

Misalkan Anda ingin menemukan 3 produk terlaris dari setiap kategori.

WITH ProductSales AS (
    SELECT
        p.product_name,
        p.category,
        SUM(oi.quantity * oi.item_price) AS total_product_sales
    FROM
        products p
    JOIN
        order_items oi ON p.product_id = oi.product_id
    GROUP BY
        p.product_name, p.category
),
RankedProductSales AS (
    SELECT
        product_name,
        category,
        total_product_sales,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY total_product_sales DESC) AS rn
    FROM
        ProductSales
)
SELECT
    product_name,
    category,
    total_product_sales
FROM
    RankedProductSales
WHERE
    rn <= 3
ORDER BY
    category, rn;

Di sini:

1. ProductSales (CTE pertama) menghitung total penjualan untuk setiap produk dan kategori.
2. RankedProductSales (CTE kedua) mengambil hasil dari ProductSales dan menerapkan ROW_NUMBER() untuk memberikan peringkat produk dalam setiap kategori.
3. Query utama kemudian memilih produk-produk dengan peringkat 1 sampai 3 dari setiap kategori.

Ini adalah pola yang sangat kuat untuk laporan dan analisis data yang seringkali rumit jika dilakukan dengan subquery bertingkat.

5. Tips Praktis dan Best Practices

• Pikirkan Logika Berurutan: Sebelum menulis query, pecah masalah menjadi langkah-langkah logis. Setiap langkah bisa menjadi sebuah CTE.
• Kapan Menggunakan CTE vs. Subquery:
  • Gunakan CTE ketika Anda perlu mereferensikan hasil sementara lebih dari sekali dalam query yang sama, atau ketika subquery menjadi terlalu dalam dan sulit dibaca.
  • Gunakan CTE untuk query rekursif.
  • Untuk subquery sederhana yang hanya digunakan sekali, subquery mungkin lebih ringkas.
• Performa: Meskipun CTE meningkatkan keterbacaan, mereka tidak selalu secara otomatis meningkatkan performa. Terkadang, database mungkin mengoptimalkan subquery dengan cara yang sama. Selalu gunakan EXPLAIN (atau EXPLAIN ANALYZE di PostgreSQL) untuk memahami rencana eksekusi query Anda dan mengidentifikasi bottleneck.
• Indeks: Pastikan kolom yang digunakan dalam PARTITION BY dan ORDER BY di Window Functions memiliki indeks yang sesuai untuk performa optimal.
• Hindari CTE Berlebihan: Terlalu banyak CTE yang sangat kecil juga bisa membuat query sulit diikuti. Temukan keseimbangan yang tepat.
• Dokumentasi: Tambahkan komentar pada CTE Anda, terutama jika logikanya kompleks.

Kesimpulan

Menguasai Common Table Expressions (CTE) dan Window Functions adalah lompatan besar dalam kemampuan SQL Anda. CTE memungkinkan Anda menulis query yang lebih terstruktur dan mudah dibaca, memecah kompleksitas menjadi bagian-bagian yang lebih kecil. Sementara itu, Window Functions membuka pintu ke analisis data yang lebih mendalam, memungkinkan Anda melakukan perhitungan agregat dan analitik pada “jendela” data tanpa kehilangan detail baris asli.

Dengan menggabungkan keduanya, Anda akan mampu mengatasi tantangan query yang paling rumit sekalipun, menghasilkan laporan yang lebih cerdas, dan membuat aplikasi web Anda berinteraksi dengan database secara lebih efisien dan powerful. Jadi, jangan ragu untuk bereksperimen dan mengintegrasikan teknik-teknik ini ke dalam proyek Anda berikutnya!

🔗 Baca Juga

• Database Indexing Strategies: Kunci Performa Aplikasi Web Anda
• Optimasi Query Database: Jurus Rahasia Aplikasi Web yang Cepat dan Efisien
• Materialized Views: Meningkatkan Performa Query Kompleks dan Laporan di Aplikasi Web Anda
• Membangun Ekstensi Browser Modern: Menguasai API Chrome/Firefox untuk Produktivitas dan Kustomisasi Web Anda