VIEW, INDEX, CTE & Window Functions

6.1 VIEW — Query yang Disimpan

VIEW adalah query yang disimpan sebagai "tabel virtual". Berguna untuk menyederhanakan query kompleks yang sering digunakan.

-- Buat view: ringkasan pelanggan dan pesanan mereka
CREATE VIEW IF NOT EXISTS v_ringkasan_pelanggan AS
SELECT p.id, p.nama, p.kota, COUNT(ps.id) AS total_pesanan, COALESCE(SUM(ps.total_harga), 0) AS total_belanja
FROM pelanggan p
LEFT JOIN pesanan ps ON p.id = ps.pelanggan_id
GROUP BY p.id, p.nama, p.kota;

-- Gunakan view seperti tabel biasa
SELECT * FROM v_ringkasan_pelanggan
WHERE total_belanja > 5000000
ORDER BY total_belanja DESC;

6.2 CTE (Common Table Expression)

CTE adalah subquery yang diberi nama. Lebih mudah dibaca dari nested subquery.

-- Cari pelanggan VIP (belanja > rata-rata)
WITH rata_belanja AS (
    SELECT AVG(total_harga) AS rata FROM pesanan
),
belanja_pelanggan AS (
    SELECT pelanggan_id, SUM(total_harga) AS total
    FROM pesanan GROUP BY pelanggan_id
)
SELECT p.nama, bp.total,
    CASE WHEN bp.total > rb.rata THEN 'VIP' ELSE 'Regular' END AS tier
FROM belanja_pelanggan bp
JOIN pelanggan p ON p.id = bp.pelanggan_id
CROSS JOIN rata_belanja rb
ORDER BY bp.total DESC;

6.3 Window Functions

Window functions menghitung agregat tapi tidak menggabungkan baris — setiap baris tetap ada.

-- Ranking produk berdasarkan harga per kategori
SELECT nama_produk, kategori_id, harga,
    RANK() OVER (PARTITION BY kategori_id ORDER BY harga DESC) AS rank_di_kategori
FROM produk
ORDER BY kategori_id, rank_di_kategori;

-- Running total penjualan berdasarkan tanggal
SELECT tanggal_pesan, total_harga,
    SUM(total_harga) OVER (ORDER BY tanggal_pesan) AS running_total
FROM pesanan
ORDER BY tanggal_pesan;