Advanced CTE

Chapter Common Table Expressions memperkenalkan WITH sebagai cara memberi nama pada subquery agar query panjang terbaca bertahap. Chapter lanjutan ini mengangkat CTE dari “nama untuk subquery” menjadi arsitektur query sungguhan: rantai CTE sebagai pipeline perhitungan bertahap dengan tahap-tahap bernama, kombinasi rekursi dan agregasi beserta satu larangan pentingnya, CTE pengubah data yang memindahkan baris antar-tabel secara atomik dalam satu statement, dan kendali materialisasi MATERIALIZED / NOT MATERIALIZED yang mengubah cara planner mengoptimalkan semuanya. Seluruh demo memakai satu dataset log event 1.000 baris yang dibangkitkan deterministik.

Dataset: Log Event Seribu Baris #

Tabel event_log menyimpan tiga jenis event (login, klik, beli) dari empat pelanggan, tersebar di 14 hari pertama Januari 2026. Seribu barisnya tidak ditulis tangan — melainkan dibangkitkan generate_series dengan rumus tetap sehingga output setiap query di chapter ini reproducible. Tabel event_arsip berkolom sama dan masih kosong, menunggu demo pemindahan atomik:

sql
buat-tabel.sql
-- Tabel latihan chapter Advanced CTE: log event pelanggan Toko Maju Jaya.
-- 1000 baris dibangkitkan deterministik lewat generate_series; kolom waktu
-- tersebar di 14 hari pertama Januari 2026. event_arsip berbentuk sama dan
-- masih kosong -- sasaran demo pemindahan baris atomik.
CREATE TABLE event_log (
    id          integer PRIMARY KEY,
    pelanggan_id integer NOT NULL,
    jenis       text NOT NULL,
    waktu       timestamp NOT NULL,
    nilai       numeric(12,2) NOT NULL
);

CREATE TABLE event_arsip (
    id          integer PRIMARY KEY,
    pelanggan_id integer NOT NULL,
    jenis       text NOT NULL,
    waktu       timestamp NOT NULL,
    nilai       numeric(12,2) NOT NULL
);

INSERT INTO event_log (id, pelanggan_id, jenis, waktu, nilai)
SELECT i,
       (i % 4) + 1,
       CASE i % 3 WHEN 0 THEN 'login' WHEN 1 THEN 'klik' ELSE 'beli' END,
       TIMESTAMP '2026-01-01 00:00:00'
           + (i % 14) * INTERVAL '1 day'
           + (i % 8) * INTERVAL '3 hours',
       CASE WHEN i % 3 = 2 THEN 50000 + i * 100 ELSE 0 END
FROM generate_series(1, 1000) AS i;

ANALYZE event_log;

-- Ringkasan dataset: sebaran jenis dan rentang waktunya.
SELECT jenis,
       count(*) AS jumlah_event,
       min(waktu)::date AS mulai,
       max(waktu)::date AS selesai
FROM event_log
GROUP BY jenis
ORDER BY jenis;

Output yang Dihasilkan:

text
CREATE TABLE
CREATE TABLE
INSERT 0 1000
ANALYZE
 jenis | jumlah_event |   mulai    |  selesai   
-------+--------------+------------+------------
 beli  |          333 | 2026-01-01 | 2026-01-14
 klik  |          334 | 2026-01-01 | 2026-01-14
 login |          333 | 2026-01-01 | 2026-01-14
(3 rows)

Rantai CTE sebagai Pipeline Perhitungan #

Query analitik yang ditulis sebagai satu blok raksasa sulit diuji dan disurvei; query yang sama yang dipecah menjadi rantai CTE bernama terbaca seperti rencana kerja. Empat tahap berikut menjawab “bagaimana pendapatan harian dibanding rata-rata bergerak tiga harinya?”: sumber disaring, diagregasi, dihaluskan, lalu diberi label:

sql
pipeline-cte.sql
-- Rantai CTE sebagai pipeline perhitungan: empat tahap bernama, tiap tahap
-- membaca tahap sebelumnya dan hanya mengerjakan satu pekerjaan.
--   transaksi : saring hanya event 'beli' yang bernilai.
--   harian    : agregasi pendapatan per tanggal.
--   bergerak  : rata-rata bergerak 3 hari + perubahan harian.
--   ringkasan : label posisi tren yang siap dibaca manajemen.
WITH transaksi AS (
    SELECT waktu, nilai
    FROM event_log
    WHERE jenis = 'beli'
      AND nilai > 0
),
harian AS (
    SELECT waktu::date AS tanggal,
           sum(nilai) AS pendapatan
    FROM transaksi
    GROUP BY waktu::date
),
bergerak AS (
    SELECT tanggal,
           pendapatan,
           round(avg(pendapatan) OVER (
               ORDER BY tanggal ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
           ), 2) AS rata3_hari
    FROM harian
),
ringkasan AS (
    SELECT tanggal,
           pendapatan,
           rata3_hari,
           CASE
               WHEN pendapatan > rata3_hari THEN 'di atas tren'
               WHEN pendapatan < rata3_hari THEN 'di bawah tren'
               ELSE 'sejajar tren'
           END AS posisi_tren
    FROM bergerak
)
SELECT tanggal,
       pendapatan,
       rata3_hari,
       posisi_tren
FROM ringkasan
ORDER BY tanggal;

Output yang Dihasilkan:

text
  tanggal   | pendapatan | rata3_hari |  posisi_tren  
------------+------------+------------+---------------
 2026-01-01 | 2392800.00 | 2392800.00 | sejajar tren
 2026-01-02 | 2428800.00 | 2410800.00 | di atas tren
 2026-01-03 | 2364000.00 | 2395200.00 | di bawah tren
 2026-01-04 | 2400000.00 | 2397600.00 | di atas tren
 2026-01-05 | 2436000.00 | 2400000.00 | di atas tren
 2026-01-06 | 2371200.00 | 2402400.00 | di bawah tren
 2026-01-07 | 2407200.00 | 2404800.00 | di atas tren
 2026-01-08 | 2293100.00 | 2357166.67 | di bawah tren
 2026-01-09 | 2378400.00 | 2359566.67 | di atas tren
 2026-01-10 | 2414400.00 | 2361966.67 | di atas tren
 2026-01-11 | 2300000.00 | 2364266.67 | di bawah tren
 2026-01-12 | 2385600.00 | 2366666.67 | di atas tren
 2026-01-13 | 2421600.00 | 2369066.67 | di atas tren
 2026-01-14 | 2306900.00 | 2371366.67 | di bawah tren
(14 rows)

Setiap tahap punya satu pekerjaan dan namanya menjelaskan pekerjaan itu: transaksi menyaring 333 event beli, harian melipatnya menjadi 14 baris pendapatan, bergerak menghaluskan dengan window function, ringkasan menerjemahkan menjadi label. Keuntungan praktisnya bukan cuma keindahan: tahap mana pun bisa diuji sendiri dengan mengganti query utama menjadi SELECT * FROM harian, dan kolom antara bisa diperiksa satu per satu saat hasil akhir terlihat janggal. Versi subquery bersarang yang setara menumpuk empat lapis tanda kurung — benar secara hasil, tetapi tidak menyediakan titik pemeriksaan di tengah.

Rekursi dan Agregasi #

Recursive CTE dan agregasi sering dipertemukan oleh laporan: kalender dibangkitkan rekursif, lalu diagregasi bersama data. Pola yang sah: agregasi berada di atas hasil rekursi — deret 14 tanggal menjadi sisi kiri LEFT JOIN, event log menjadi sisi kanan, dan GROUP BY merangkai keduanya:

sql
rekursif-agregasi.sql
-- Rekursi + agregasi: deret tanggal dibangkitkan recursive CTE, lalu
-- diagregasi bersama event_log. Agregasi berada DI ATAS hasil rekursi --
-- pola yang sah dan sering dipakai (kalender kiri, data kanan).
WITH RECURSIVE kalender AS (
    SELECT DATE '2026-01-01' AS tanggal
    UNION ALL
    SELECT tanggal + 1
    FROM kalender
    WHERE tanggal < DATE '2026-01-14'
),
per_hari AS (
    SELECT k.tanggal,
           count(e.id) AS jumlah_event,
           coalesce(sum(e.nilai), 0) AS total_nilai
    FROM kalender k
    LEFT JOIN event_log e ON e.waktu::date = k.tanggal
    GROUP BY k.tanggal
)
SELECT tanggal,
       jumlah_event,
       total_nilai,
       round(100.0 * jumlah_event / sum(jumlah_event) OVER (), 1) AS pangsa_pct
FROM per_hari
ORDER BY tanggal;

Output yang Dihasilkan:

text
  tanggal   | jumlah_event | total_nilai | pangsa_pct 
------------+--------------+-------------+------------
 2026-01-01 |           71 |  2392800.00 |        7.1
 2026-01-02 |           72 |  2428800.00 |        7.2
 2026-01-03 |           72 |  2364000.00 |        7.2
 2026-01-04 |           72 |  2400000.00 |        7.2
 2026-01-05 |           72 |  2436000.00 |        7.2
 2026-01-06 |           72 |  2371200.00 |        7.2
 2026-01-07 |           72 |  2407200.00 |        7.2
 2026-01-08 |           71 |  2293100.00 |        7.1
 2026-01-09 |           71 |  2378400.00 |        7.1
 2026-01-10 |           71 |  2414400.00 |        7.1
 2026-01-11 |           71 |  2300000.00 |        7.1
 2026-01-12 |           71 |  2385600.00 |        7.1
 2026-01-13 |           71 |  2421600.00 |        7.1
 2026-01-14 |           71 |  2306900.00 |        7.1
(14 rows)

Karena setiap tanggal memang terisi event, LEFT JOIN di sini tidak menghasilkan baris kosong — tetapi polanya tetap penting: bila kalender membentang ke tanggal tanpa event, count(e.id) menjadi 0 dan coalesce(sum(...)) menjadi 0, bukan NULL. Satu-satunya larangan kombinasi ini ada di dalam recursive member: agregasi tidak boleh menyelubungi rujukan rekursifnya. Mengagregasi salah di dalam salah sendiri ditolak:

sql
agregat-rekursif-error.sql
-- Agregasi yang MENYELUBUNGI rujukan rekursif ditolak: sum(salah.n) di
-- recursive member berarti mengagregasi working table -- sesuatu yang
-- evaluasi per-iterasi recursive query tidak dukung.
WITH RECURSIVE salah AS (
    SELECT 1 AS n
    UNION ALL
    SELECT sum(n)::integer + 1
    FROM salah
)
SELECT n FROM salah LIMIT 5;

Output yang Dihasilkan:

text
psql:agregat-rekursif-error.sql:10: ERROR:  aggregate functions are not allowed in a recursive query's recursive term
LINE 4:     SELECT sum(n)::integer + 1
                   ^

Alasannya melekat pada mekanisme recursive query yang dibahas di chapter Recursive Query : recursive member dievaluasi berulang atas working table per iterasi, dan agregat mengacak makna iterasi itu — harus dijumlah dulu semua iterasinya, atau agregasi dilakukan di luar. Praktik yang benar persis seperti rekursif-agregasi.sql: biarkan rekursi membangkitkan baris, agregasi menyusul di CTE atau query berikutnya.

CTE Pengubah Data: Memindahkan Baris Secara Atomik #

WITH tidak terbatas pada SELECT — tiap CTE boleh berisi INSERT, UPDATE, DELETE, atau MERGE, dan RETURNING menjembatani hasilnya ke query utama. Satu perintah WITH ... SELECT berjalan dalam satu transaksi implisit: seluruh statement di dalamnya terjadi semua atau tidak sama sekali. Dua demo berikut: yang pertama menyisipkan satu baris dan langsung membacanya; yang kedua adalah pattern produksi “pindahkan baris lama ke arsip” — DELETE ... RETURNING mengalir ke INSERT ... SELECT, hasilnya dihitung query utama:

sql
cte-modifikasi-data.sql
-- CTE pengubah data: INSERT/DELETE dengan RETURNING yang hasilnya langsung
-- dipakai query utama. Semua statement dalam satu WITH berjalan dalam SATU
-- transaksi implisit -- pattern "pindahkan baris secara atomik".
WITH tambah AS (
    INSERT INTO event_arsip (id, pelanggan_id, jenis, waktu, nilai)
    VALUES (1001, 1, 'login', TIMESTAMP '2026-01-15 09:00:00', 0)
    RETURNING id, jenis, waktu
)
SELECT id, jenis, waktu
FROM tambah
ORDER BY id;

-- Pemindahan atomik: DELETE event lama -> RETURNING -> INSERT ke arsip
-- -> RETURNING -> ringkasan. Gagal di salah satu tahap membatalkan semuanya.
WITH pindah AS (
    DELETE FROM event_log
    WHERE waktu < TIMESTAMP '2026-01-05 00:00:00'
    RETURNING *
),
masuk AS (
    INSERT INTO event_arsip
    SELECT * FROM pindah
    RETURNING id
)
SELECT (SELECT count(*) FROM pindah) AS baris_dipindah,
       (SELECT count(*) FROM masuk) AS baris_masuk_arsip;

-- Verifikasi kondisi kedua tabel setelah pemindahan.
SELECT 'event_log' AS tabel, count(*) AS baris, min(waktu)::date AS tertua,
       max(waktu)::date AS termuda
FROM event_log
UNION ALL
SELECT 'event_arsip', count(*), min(waktu)::date, max(waktu)::date
FROM event_arsip
ORDER BY tabel;

Output yang Dihasilkan:

text
  id  | jenis |        waktu        
------+-------+---------------------
 1001 | login | 2026-01-15 09:00:00
(1 row)

 baris_dipindah | baris_masuk_arsip 
----------------+-------------------
            287 |               287
(1 row)

    tabel    | baris |   tertua   |  termuda   
-------------+-------+------------+------------
 event_arsip |   288 | 2026-01-01 | 2026-01-15
 event_log   |   713 | 2026-01-05 | 2026-01-14
(2 rows)

Mari kita bedah rantai pemindahannya:

CTE pindah menghapus 287 event ber-timestamp sebelum 5 Januari dan mengembalikan baris-barisnya lewat RETURNING * — tanpa RETURNING, baris yang dihapus hilang dari jangkauan CTE berikutnya.
CTE masuk membaca pindah seperti tabel biasa dan menyisipkannya ke event_arsip; RETURNING id membuat jumlahnya bisa dihitung.
Query utama melaporkan 287 dan 287 — dan verifikasi menutup cerita: event_log kini berumur 5–14 Januari (713 baris), event_arsip menyimpan 288 baris (287 hasil pemindahan + satu baris demo pertama).

Kegagalan di tahap mana pun — misalnya event_arsip menolak baris karena primary key bentrok — membatalkan DELETE juga; kondisi “hilang di tabel asal tetapi belum sampai arsip” tidak pernah terjadi. Chapter RETURNING Clause di section Manipulasi Data memperkenalkan klausanya; di sini klausa itu menjadi perekat antar-statement.

MATERIALIZED vs NOT MATERIALIZED #

Sejak PostgreSQL 12, CTE tidak lagi selalu menjadi pagar bagi optimizer. Aturan defaultnya: CTE non-rekursif tanpa efek samping yang dirujuk sekali digabungkan ke query utama (di-inline); yang dirujuk lebih dari sekali dihitung sekali lalu disimpan (dimaterialisasi). Kata kunci MATERIALIZED dan NOT MATERIALIZED menimpa default itu. Rencana eksekusinya terlihat langsung lewat EXPLAIN:

sql
materialized-hint.sql
-- MATERIALIZED vs NOT MATERIALIZED (PostgreSQL 12+): pagar optimisasi.
-- Rencana eksekusi memperlihatkan bedanya (EXPLAIN tanpa ANALYZE, tanpa biaya).

-- (1) MATERIALIZED: CTE dihitung penuh dulu (Seq Scan seluruh tabel),
-- filter id = 500 baru diterapkan pada CTE Scan -- pagar optimisasi.
EXPLAIN (COSTS OFF)
WITH semua AS MATERIALIZED (SELECT * FROM event_log)
SELECT * FROM semua WHERE id = 500;

-- (2) NOT MATERIALIZED: filter diturunkan ke dalam tabel -- planner bebas
-- memakai index primary key dan hanya mengambil satu baris.
EXPLAIN (COSTS OFF)
WITH semua AS NOT MATERIALIZED (SELECT * FROM event_log)
SELECT * FROM semua WHERE id = 500;

-- (3) Rujukan ganda TANPA hint (default PostgreSQL 12+): dirujuk dua kali
-- -> otomatis materialized, dihitung SEKALI (satu Seq Scan, dua CTE Scan).
EXPLAIN (COSTS OFF)
WITH semua AS (SELECT * FROM event_log)
SELECT count(*)
FROM semua a
INNER JOIN semua b ON a.id = b.id;

-- (4) Rujukan ganda dengan NOT MATERIALIZED: digabungkan ke tiap rujukan,
-- tabel dibaca ulang DUA kali (dua Seq Scan terpisah).
EXPLAIN (COSTS OFF)
WITH semua AS NOT MATERIALIZED (SELECT * FROM event_log)
SELECT count(*)
FROM semua a
INNER JOIN semua b ON a.id = b.id;

Output yang Dihasilkan:

text
          QUERY PLAN           
-------------------------------
 CTE Scan on semua
   Filter: (id = 500)
   CTE semua
     ->  Seq Scan on event_log
(4 rows)

                  QUERY PLAN                  
----------------------------------------------
 Index Scan using event_log_pkey on event_log
   Index Cond: (id = 500)
(2 rows)

              QUERY PLAN               
---------------------------------------
 Aggregate
   CTE semua
     ->  Seq Scan on event_log
   ->  Hash Join
         Hash Cond: (a.id = b.id)
         ->  CTE Scan on semua a
         ->  Hash
               ->  CTE Scan on semua b
(8 rows)

                     QUERY PLAN                      
-----------------------------------------------------
 Aggregate
   ->  Hash Join
         Hash Cond: (event_log.id = event_log_1.id)
         ->  Seq Scan on event_log
         ->  Hash
               ->  Seq Scan on event_log event_log_1
(6 rows)

Empat rencana itu menceritakan empat keputusan berbeda:

(1) MATERIALIZED memasang pagar: CTE Scan on semua dengan Filter: (id = 500) di atasnya, sementara Seq Scan on event_log membaca seluruh 1.000 baris — filter tidak turun ke tabel.
(2) NOT MATERIALIZED meruntuhkan pagar: tinggal Index Scan using event_log_pkey dengan Index Cond: (id = 500) — satu baris, biaya minimum.
(3) default dirujuk dua kali menampilkan CTE semua dihitung sekali (satu Seq Scan) lalu dipakai dua CTE Scan — penghematan yang dijanjikan materialisasi.
(4) NOT MATERIALIZED dirujuk dua kali memaksa penggabungan ke tiap rujukan: tabel dibaca dua kali oleh dua Seq Scan terpisah — penghematan berubah menjadi pemborosan.

Dua catatan tambahan menutup topik ini. Pertama, CTE yang mengandung fungsi volatil (misalnya random()) tidak akan pernah digabungkan walau diberi NOT MATERIALIZED — planner menjaga jumlah pemanggilan fungsi efek-sampingnya tetap satu per rujukan yang diminta materialisasinya. Kedua, sejarahnya penting untuk membaca query lama: sebelum PostgreSQL 12, semua CTE adalah pagar optimisasi — query peninggalan era itu kadang berkinerja buruk bukan karena salah desain, melainkan karena pagar yang dulu wajib kini bisa dilepas dengan NOT MATERIALIZED atau direstrukturisasi.

CTE kini menjadi arsitektur lengkap: pipeline bernama, mesin rekursif, pengubah data atomik, dengan kendali materialisasi di tangan Anda. Topik berikutnya membahas kapan menulis data secara berulang justru menjadi kebutuhan produksi: pola ON CONFLICT untuk sinkronisasi harian yang aman dijalankan berapa kali pun — Upsert Pattern .

Referensi #

WITH Queries (Common Table Expressions) — aturan materialisasi, CTE pengubah data, dan evaluasi recursive query.
SELECT Reference — sintaks WITH beserta opsi MATERIALIZED / NOT MATERIALIZED.

Berkas Sumber

Berkas pendukung halaman ini dapat diunduh dan dijalankan langsung sesuai penjelasan di atas.

sql
-- Agregasi yang MENYELUBUNGI rujukan rekursif ditolak: sum(salah.n) di
-- recursive member berarti mengagregasi working table -- sesuatu yang
-- evaluasi per-iterasi recursive query tidak dukung.
WITH RECURSIVE salah AS (
    SELECT 1 AS n
    UNION ALL
    SELECT sum(n)::integer + 1
    FROM salah
)
SELECT n FROM salah LIMIT 5;

sql
-- Tabel latihan chapter Advanced CTE: log event pelanggan Toko Maju Jaya.
-- 1000 baris dibangkitkan deterministik lewat generate_series; kolom waktu
-- tersebar di 14 hari pertama Januari 2026. event_arsip berbentuk sama dan
-- masih kosong -- sasaran demo pemindahan baris atomik.
CREATE TABLE event_log (
    id          integer PRIMARY KEY,
    pelanggan_id integer NOT NULL,
    jenis       text NOT NULL,
    waktu       timestamp NOT NULL,
    nilai       numeric(12,2) NOT NULL
);

CREATE TABLE event_arsip (
    id          integer PRIMARY KEY,
    pelanggan_id integer NOT NULL,
    jenis       text NOT NULL,
    waktu       timestamp NOT NULL,
    nilai       numeric(12,2) NOT NULL
);

INSERT INTO event_log (id, pelanggan_id, jenis, waktu, nilai)
SELECT i,
       (i % 4) + 1,
       CASE i % 3 WHEN 0 THEN 'login' WHEN 1 THEN 'klik' ELSE 'beli' END,
       TIMESTAMP '2026-01-01 00:00:00'
           + (i % 14) * INTERVAL '1 day'
           + (i % 8) * INTERVAL '3 hours',
       CASE WHEN i % 3 = 2 THEN 50000 + i * 100 ELSE 0 END
FROM generate_series(1, 1000) AS i;

ANALYZE event_log;

-- Ringkasan dataset: sebaran jenis dan rentang waktunya.
SELECT jenis,
       count(*) AS jumlah_event,
       min(waktu)::date AS mulai,
       max(waktu)::date AS selesai
FROM event_log
GROUP BY jenis
ORDER BY jenis;

sql
-- CTE pengubah data: INSERT/DELETE dengan RETURNING yang hasilnya langsung
-- dipakai query utama. Semua statement dalam satu WITH berjalan dalam SATU
-- transaksi implisit -- pattern "pindahkan baris secara atomik".
WITH tambah AS (
    INSERT INTO event_arsip (id, pelanggan_id, jenis, waktu, nilai)
    VALUES (1001, 1, 'login', TIMESTAMP '2026-01-15 09:00:00', 0)
    RETURNING id, jenis, waktu
)
SELECT id, jenis, waktu
FROM tambah
ORDER BY id;

-- Pemindahan atomik: DELETE event lama -> RETURNING -> INSERT ke arsip
-- -> RETURNING -> ringkasan. Gagal di salah satu tahap membatalkan semuanya.
WITH pindah AS (
    DELETE FROM event_log
    WHERE waktu < TIMESTAMP '2026-01-05 00:00:00'
    RETURNING *
),
masuk AS (
    INSERT INTO event_arsip
    SELECT * FROM pindah
    RETURNING id
)
SELECT (SELECT count(*) FROM pindah) AS baris_dipindah,
       (SELECT count(*) FROM masuk) AS baris_masuk_arsip;

-- Verifikasi kondisi kedua tabel setelah pemindahan.
SELECT 'event_log' AS tabel, count(*) AS baris, min(waktu)::date AS tertua,
       max(waktu)::date AS termuda
FROM event_log
UNION ALL
SELECT 'event_arsip', count(*), min(waktu)::date, max(waktu)::date
FROM event_arsip
ORDER BY tabel;

sql
-- MATERIALIZED vs NOT MATERIALIZED (PostgreSQL 12+): pagar optimisasi.
-- Rencana eksekusi memperlihatkan bedanya (EXPLAIN tanpa ANALYZE, tanpa biaya).

-- (1) MATERIALIZED: CTE dihitung penuh dulu (Seq Scan seluruh tabel),
-- filter id = 500 baru diterapkan pada CTE Scan -- pagar optimisasi.
EXPLAIN (COSTS OFF)
WITH semua AS MATERIALIZED (SELECT * FROM event_log)
SELECT * FROM semua WHERE id = 500;

-- (2) NOT MATERIALIZED: filter diturunkan ke dalam tabel -- planner bebas
-- memakai index primary key dan hanya mengambil satu baris.
EXPLAIN (COSTS OFF)
WITH semua AS NOT MATERIALIZED (SELECT * FROM event_log)
SELECT * FROM semua WHERE id = 500;

-- (3) Rujukan ganda TANPA hint (default PostgreSQL 12+): dirujuk dua kali
-- -> otomatis materialized, dihitung SEKALI (satu Seq Scan, dua CTE Scan).
EXPLAIN (COSTS OFF)
WITH semua AS (SELECT * FROM event_log)
SELECT count(*)
FROM semua a
INNER JOIN semua b ON a.id = b.id;

-- (4) Rujukan ganda dengan NOT MATERIALIZED: digabungkan ke tiap rujukan,
-- tabel dibaca ulang DUA kali (dua Seq Scan terpisah).
EXPLAIN (COSTS OFF)
WITH semua AS NOT MATERIALIZED (SELECT * FROM event_log)
SELECT count(*)
FROM semua a
INNER JOIN semua b ON a.id = b.id;

sql
-- Rantai CTE sebagai pipeline perhitungan: empat tahap bernama, tiap tahap
-- membaca tahap sebelumnya dan hanya mengerjakan satu pekerjaan.
--   transaksi : saring hanya event 'beli' yang bernilai.
--   harian    : agregasi pendapatan per tanggal.
--   bergerak  : rata-rata bergerak 3 hari + perubahan harian.
--   ringkasan : label posisi tren yang siap dibaca manajemen.
WITH transaksi AS (
    SELECT waktu, nilai
    FROM event_log
    WHERE jenis = 'beli'
      AND nilai > 0
),
harian AS (
    SELECT waktu::date AS tanggal,
           sum(nilai) AS pendapatan
    FROM transaksi
    GROUP BY waktu::date
),
bergerak AS (
    SELECT tanggal,
           pendapatan,
           round(avg(pendapatan) OVER (
               ORDER BY tanggal ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
           ), 2) AS rata3_hari
    FROM harian
),
ringkasan AS (
    SELECT tanggal,
           pendapatan,
           rata3_hari,
           CASE
               WHEN pendapatan > rata3_hari THEN 'di atas tren'
               WHEN pendapatan < rata3_hari THEN 'di bawah tren'
               ELSE 'sejajar tren'
           END AS posisi_tren
    FROM bergerak
)
SELECT tanggal,
       pendapatan,
       rata3_hari,
       posisi_tren
FROM ringkasan
ORDER BY tanggal;

sql
-- Rekursi + agregasi: deret tanggal dibangkitkan recursive CTE, lalu
-- diagregasi bersama event_log. Agregasi berada DI ATAS hasil rekursi --
-- pola yang sah dan sering dipakai (kalender kiri, data kanan).
WITH RECURSIVE kalender AS (
    SELECT DATE '2026-01-01' AS tanggal
    UNION ALL
    SELECT tanggal + 1
    FROM kalender
    WHERE tanggal < DATE '2026-01-14'
),
per_hari AS (
    SELECT k.tanggal,
           count(e.id) AS jumlah_event,
           coalesce(sum(e.nilai), 0) AS total_nilai
    FROM kalender k
    LEFT JOIN event_log e ON e.waktu::date = k.tanggal
    GROUP BY k.tanggal
)
SELECT tanggal,
       jumlah_event,
       total_nilai,
       round(100.0 * jumlah_event / sum(jumlah_event) OVER (), 1) AS pangsa_pct
FROM per_hari
ORDER BY tanggal;