Pola Kueri: Window Function, CTE & Upsert

Window Function

SELECT
    pengguna_id, id, total,
    row_number() OVER (PARTITION BY pengguna_id ORDER BY total DESC) AS urutan,
    sum(total)   OVER (PARTITION BY pengguna_id) AS total_pengguna,
    total - lag(total) OVER (PARTITION BY pengguna_id ORDER BY dibuat_pada)
        AS selisih_dari_sebelumnya
FROM pesanan;

-- Total berjalan & rata-rata bergerak
SELECT tanggal, jumlah,
       sum(jumlah) OVER (ORDER BY tanggal
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
           AS kumulatif,
       avg(jumlah) OVER (ORDER BY tanggal
                         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
           AS rata_7_hari
FROM penjualan_harian;

SELECT * FROM (
    SELECT *, row_number() OVER (PARTITION BY pengguna_id
                                 ORDER BY dibuat_pada DESC) AS rn
    FROM pesanan
) t WHERE rn <= 3;
-- 3 pesanan terbaru untuk SETIAP pengguna, dalam satu kueri.

CTE dan Jebakan Materialisasi

WITH pesanan_besar AS MATERIALIZED (...)      -- paksa materialisasi
WITH pesanan_besar AS NOT MATERIALIZED (...)  -- paksa inline

-- CTE rekursif: menelusuri hierarki
WITH RECURSIVE bawahan AS (
    SELECT id, nama, atasan_id, 1 AS tingkat
    FROM karyawan WHERE id = 1

    UNION ALL

    SELECT k.id, k.nama, k.atasan_id, b.tingkat + 1
    FROM karyawan k
    JOIN bawahan b ON k.atasan_id = b.id
    WHERE b.tingkat < 10       -- pengaman terhadap siklus
)
SELECT * FROM bawahan;

Upsert

-- Sisipkan, atau perbarui bila sudah ada
INSERT INTO stok (produk_id, jumlah, diubah_pada)
VALUES (1, 100, now())
ON CONFLICT (produk_id) DO UPDATE
SET jumlah = stok.jumlah + EXCLUDED.jumlah,
    diubah_pada = now()
RETURNING *;

-- Abaikan bila sudah ada (pola idempoten)
INSERT INTO peristiwa_diproses (kunci_idempotensi)
VALUES ('abc-123')
ON CONFLICT DO NOTHING
RETURNING id;
-- Bila tidak ada baris kembali, berarti sudah pernah diproses.

RETURNING & LATERAL

-- Ambil data hasil operasi tanpa kueri kedua
UPDATE pesanan SET status = 'dibatalkan'
WHERE dibuat_pada < now() - interval '7 days'
  AND status = 'menunggu'
RETURNING id, pengguna_id;

-- Memindahkan baris secara atomik
WITH dihapus AS (
    DELETE FROM antrean WHERE id = 1 RETURNING *
)
INSERT INTO arsip SELECT * FROM dihapus;

-- LATERAL: subkueri kanan bisa merujuk kolom kiri
SELECT u.nama, p.id, p.total
FROM pengguna u
CROSS JOIN LATERAL (
    SELECT id, total FROM pesanan
    WHERE pengguna_id = u.id
    ORDER BY dibuat_pada DESC
    LIMIT 3
) p;

Paginasi yang Benar

-- ❌ Harus membaca dan membuang 100.000 baris dulu
SELECT * FROM pesanan ORDER BY dibuat_pada DESC
LIMIT 20 OFFSET 100000;

-- ✅ Keyset pagination: selalu cepat, berapa pun halamannya
SELECT * FROM pesanan
WHERE (dibuat_pada, id) < ('2026-07-20 10:00:00+07', 48213)
ORDER BY dibuat_pada DESC, id DESC
LIMIT 20;