CTE

Klausa WITH untuk menamai hasil antara dan menyusun query yang terbaca.

Daftar isi

Klausa WITH menamai hasil antara suatu query agar query utamanya dapat dibaca sebagai langkah demi langkah, dan nama itu disebut common table expression atau CTE. Topik ini membahas penulisan satu CTE, rantai beberapa CTE yang saling merujuk, CTE yang direferensikan lebih dari satu kali, serta keputusan perencana query SQLite untuk melebur atau mematerialisasi CTE. Dataset tabel buku dan kategori yang sama dengan topik lain di modul ini dipakai sepanjang contoh, dan setiap blok membuat tabelnya sendiri sehingga dapat dijalankan secara terpisah.

Menamai hasil antara dengan WITH#

Sebuah CTE ditulis setelah WITH dengan bentuk nama AS (SELECT ...), dan query utama kemudian memperlakukan nama itu seperti sebuah tabel. CTE hanya hidup selama satu pernyataan, sehingga tidak ada artefak samping di basis data seperti tabel sementara. Nilai utamanya terletak pada urutan baca, karena pembaca menemukan hasil antara terlebih dahulu sebelum menyaksikan penggunaannya.

sql CTE tunggal untuk hasil antara

CREATE TABLE kategori (
  id       INTEGER PRIMARY KEY,
  nama     TEXT NOT NULL,
  id_induk INTEGER REFERENCES kategori(id)
);
CREATE TABLE buku (
  id          INTEGER PRIMARY KEY,
  judul       TEXT NOT NULL,
  tahun       INTEGER NOT NULL,
  id_kategori INTEGER REFERENCES kategori(id)
);
INSERT INTO kategori (id, nama, id_induk) VALUES
  (1, 'Ilmu Komputer', NULL),
  (2, 'Sastra',        NULL),
  (3, 'Sejarah',       NULL),
  (4, 'Algoritma',     1),
  (5, 'Jaringan',      1),
  (6, 'Puisi',         2),
  (7, 'Prosa',         2);
INSERT INTO buku (id, judul, tahun, id_kategori) VALUES
  (1, 'Dasar Algoritma',        2018, 4),
  (2, 'Struktur Data Terapan',  2021, 4),
  (3, 'Analisis Kompleksitas',  2019, 4),
  (4, 'Jaringan Komputer',      2020, 5),
  (5, 'Protokol Internet',      2023, 5),
  (6, 'Kumpulan Puisi Lama',    2012, 6),
  (7, 'Antologi Puisi Baru',    2022, 6),
  (8, 'Kamus Istilah Komputer', 2024, 1),
  (9, 'Ensiklopedia Sastra',    2009, NULL);

WITH baru AS (
  SELECT judul, tahun, id_kategori
  FROM buku
  WHERE tahun >= 2020
)
SELECT k.nama AS kategori, b.judul, b.tahun
FROM baru AS b
JOIN kategori AS k ON k.id = b.id_kategori
ORDER BY b.tahun;

text Keluaran

Jaringan|Jaringan Komputer|2020
Algoritma|Struktur Data Terapan|2021
Puisi|Antologi Puisi Baru|2022
Jaringan|Protokol Internet|2023
Ilmu Komputer|Kamus Istilah Komputer|2024

CTE baru menyaring buku terbitan 2020 dan seterusnya, kemudian query utama menggabungkannya dengan kategori. Tanpa CTE, penyaringan itu harus ditulis sebagai subquery di dalam FROM dan justru menumpuk pembacaan dari dalam ke luar. Kelima buku pada hasil memenuhi syarat tahun, sedangkan Kumpulan Puisi Lama dan Ensiklopedia Sastra tersaring karena terbit sebelum 2020.

Beberapa CTE yang saling merujuk#

Satu pernyataan WITH boleh menamai beberapa CTE, dipisahkan koma, dan CTE yang ditulis kemudian boleh merujuk CTE yang ditulis lebih dahulu. Pola berantai ini memecah query rumit menjadi tahapan yang masing-masing berukuran kecil dan bernama.

sql Dua CTE yang saling merujuk

CREATE TABLE kategori (
  id       INTEGER PRIMARY KEY,
  nama     TEXT NOT NULL,
  id_induk INTEGER REFERENCES kategori(id)
);
CREATE TABLE buku (
  id          INTEGER PRIMARY KEY,
  judul       TEXT NOT NULL,
  tahun       INTEGER NOT NULL,
  id_kategori INTEGER REFERENCES kategori(id)
);
INSERT INTO kategori (id, nama, id_induk) VALUES
  (1, 'Ilmu Komputer', NULL),
  (2, 'Sastra',        NULL),
  (3, 'Sejarah',       NULL),
  (4, 'Algoritma',     1),
  (5, 'Jaringan',      1),
  (6, 'Puisi',         2),
  (7, 'Prosa',         2);
INSERT INTO buku (id, judul, tahun, id_kategori) VALUES
  (1, 'Dasar Algoritma',        2018, 4),
  (2, 'Struktur Data Terapan',  2021, 4),
  (3, 'Analisis Kompleksitas',  2019, 4),
  (4, 'Jaringan Komputer',      2020, 5),
  (5, 'Protokol Internet',      2023, 5),
  (6, 'Kumpulan Puisi Lama',    2012, 6),
  (7, 'Antologi Puisi Baru',    2022, 6),
  (8, 'Kamus Istilah Komputer', 2024, 1),
  (9, 'Ensiklopedia Sastra',    2009, NULL);
WITH per_kategori AS (
  SELECT id_kategori, COUNT(*) AS jumlah
  FROM buku
  GROUP BY id_kategori
),
ramai AS (
  SELECT id_kategori, jumlah
  FROM per_kategori
  WHERE jumlah >= 2
)
SELECT k.nama, r.jumlah
FROM ramai AS r
JOIN kategori AS k ON k.id = r.id_kategori
ORDER BY r.jumlah DESC, k.nama;

text Keluaran

Algoritma|3
Jaringan|2
Puisi|2

CTE per_kategori menghitung jumlah buku per kategori, kemudian CTE ramai menyaring yang berisi dua buku atau lebih, dan query utama menggabungkannya dengan nama kategori. Kategori Ilmu Komputer dan grup tanpa nama kategori tersaring karena jumlahnya di bawah dua, sedangkan Sejarah dan Prosa tidak muncul karena tidak memiliki buku sama sekali.

CTE yang direferensikan lebih dari satu kali#

CTE boleh dirujuk beberapa kali dalam satu pernyataan, dan keunggulannya terasa saat hasil antara yang sama diperlukan di dua tempat. Contoh berikut membandingkan jumlah buku tiap kategori dengan rata-rata jumlah tersebut, sehingga per_kategori dipakai sekali untuk menghitung rata-rata dan sekali lagi sebagai baris yang dinilai.

sql CTE yang direferensikan dua kali

CREATE TABLE kategori (
  id       INTEGER PRIMARY KEY,
  nama     TEXT NOT NULL,
  id_induk INTEGER REFERENCES kategori(id)
);
CREATE TABLE buku (
  id          INTEGER PRIMARY KEY,
  judul       TEXT NOT NULL,
  tahun       INTEGER NOT NULL,
  id_kategori INTEGER REFERENCES kategori(id)
);
INSERT INTO kategori (id, nama, id_induk) VALUES
  (1, 'Ilmu Komputer', NULL),
  (2, 'Sastra',        NULL),
  (3, 'Sejarah',       NULL),
  (4, 'Algoritma',     1),
  (5, 'Jaringan',      1),
  (6, 'Puisi',         2),
  (7, 'Prosa',         2);
INSERT INTO buku (id, judul, tahun, id_kategori) VALUES
  (1, 'Dasar Algoritma',        2018, 4),
  (2, 'Struktur Data Terapan',  2021, 4),
  (3, 'Analisis Kompleksitas',  2019, 4),
  (4, 'Jaringan Komputer',      2020, 5),
  (5, 'Protokol Internet',      2023, 5),
  (6, 'Kumpulan Puisi Lama',    2012, 6),
  (7, 'Antologi Puisi Baru',    2022, 6),
  (8, 'Kamus Istilah Komputer', 2024, 1),
  (9, 'Ensiklopedia Sastra',    2009, NULL);
WITH per_kategori AS (
  SELECT id_kategori, COUNT(*) AS jumlah
  FROM buku
  WHERE id_kategori IS NOT NULL
  GROUP BY id_kategori
),
rata_jumlah AS (
  SELECT AVG(jumlah) AS rata
  FROM per_kategori
)
SELECT k.nama, p.jumlah
FROM per_kategori AS p
JOIN kategori AS k ON k.id = p.id_kategori
CROSS JOIN rata_jumlah
WHERE p.jumlah > rata_jumlah.rata
ORDER BY p.jumlah DESC;

text Keluaran

Algoritma|3

Penyaringan id_kategori IS NOT NULL membuat rata-rata dihitung dari empat kategori berisi buku, yaitu 3, 2, 2, dan 1, sehingga rata-ratanya dua dan hanya Algoritma dengan tiga buku yang melampauinya. Tanpa CTE, perhitungan grup tersebut harus disalin dua kali sebagai tabel turunan, dan setiap perbaikan pada definisinya harus dilakukan di dua tempat. Bentuk CTE menjadikan definisi tunggal dengan dua penggunaan, sehingga query lebih mudah diaudit.

Melebur atau mematerialisasi#

Nama CTE bukan jaminan bahwa SQLite benar-benar menghitungnya lebih dahulu. Sejak versi 3.35, perencana query boleh melebur (inline) CTE yang hanya dirujuk satu kali, yaitu menyisipkan definisinya langsung ke query utama bila cara itu dinilai menguntungkan. Rencana query pada CTE dengan satu rujukan berikut tidak memperlihatkan CO-ROUTINE sama sekali, hanya pemindaian buku dan pencarian kategori.

sql Rencana query CTE dengan satu rujukan

CREATE TABLE kategori (
  id       INTEGER PRIMARY KEY,
  nama     TEXT NOT NULL,
  id_induk INTEGER REFERENCES kategori(id)
);
CREATE TABLE buku (
  id          INTEGER PRIMARY KEY,
  judul       TEXT NOT NULL,
  tahun       INTEGER NOT NULL,
  id_kategori INTEGER REFERENCES kategori(id)
);
INSERT INTO kategori (id, nama, id_induk) VALUES
  (1, 'Ilmu Komputer', NULL),
  (2, 'Sastra',        NULL),
  (3, 'Sejarah',       NULL),
  (4, 'Algoritma',     1),
  (5, 'Jaringan',      1),
  (6, 'Puisi',         2),
  (7, 'Prosa',         2);
INSERT INTO buku (id, judul, tahun, id_kategori) VALUES
  (1, 'Dasar Algoritma',        2018, 4),
  (2, 'Struktur Data Terapan',  2021, 4),
  (3, 'Analisis Kompleksitas',  2019, 4),
  (4, 'Jaringan Komputer',      2020, 5),
  (5, 'Protokol Internet',      2023, 5),
  (6, 'Kumpulan Puisi Lama',    2012, 6),
  (7, 'Antologi Puisi Baru',    2022, 6),
  (8, 'Kamus Istilah Komputer', 2024, 1),
  (9, 'Ensiklopedia Sastra',    2009, NULL);
EXPLAIN QUERY PLAN
WITH baru AS (
  SELECT judul, tahun, id_kategori
  FROM buku
  WHERE tahun >= 2020
)
SELECT k.nama, b.judul
FROM baru AS b
JOIN kategori AS k ON k.id = b.id_kategori;

text Keluaran

QUERY PLAN
|--SCAN buku
`--SEARCH k USING INTEGER PRIMARY KEY (rowid=?)

Bandingkan dengan CTE per_kategori yang dirujuk dua kali pada contoh sebelumnya. Rencananya memperlihatkan baris MATERIALIZE, yaitu hasil CTE dihitung satu kali, disimpan sementara, lalu dipakai ulang oleh setiap rujukan.

sql Rencana query CTE dengan dua rujukan

CREATE TABLE kategori (
  id       INTEGER PRIMARY KEY,
  nama     TEXT NOT NULL,
  id_induk INTEGER REFERENCES kategori(id)
);
CREATE TABLE buku (
  id          INTEGER PRIMARY KEY,
  judul       TEXT NOT NULL,
  tahun       INTEGER NOT NULL,
  id_kategori INTEGER REFERENCES kategori(id)
);
INSERT INTO kategori (id, nama, id_induk) VALUES
  (1, 'Ilmu Komputer', NULL),
  (2, 'Sastra',        NULL),
  (3, 'Sejarah',       NULL),
  (4, 'Algoritma',     1),
  (5, 'Jaringan',      1),
  (6, 'Puisi',         2),
  (7, 'Prosa',         2);
INSERT INTO buku (id, judul, tahun, id_kategori) VALUES
  (1, 'Dasar Algoritma',        2018, 4),
  (2, 'Struktur Data Terapan',  2021, 4),
  (3, 'Analisis Kompleksitas',  2019, 4),
  (4, 'Jaringan Komputer',      2020, 5),
  (5, 'Protokol Internet',      2023, 5),
  (6, 'Kumpulan Puisi Lama',    2012, 6),
  (7, 'Antologi Puisi Baru',    2022, 6),
  (8, 'Kamus Istilah Komputer', 2024, 1),
  (9, 'Ensiklopedia Sastra',    2009, NULL);
EXPLAIN QUERY PLAN
WITH per_kategori AS (
  SELECT id_kategori, COUNT(*) AS jumlah
  FROM buku
  WHERE id_kategori IS NOT NULL
  GROUP BY id_kategori
),
rata_jumlah AS (
  SELECT AVG(jumlah) AS rata
  FROM per_kategori
)
SELECT k.nama, p.jumlah
FROM per_kategori AS p
JOIN kategori AS k ON k.id = p.id_kategori
CROSS JOIN rata_jumlah
WHERE p.jumlah > rata_jumlah.rata;

text Keluaran

QUERY PLAN
|--MATERIALIZE per_kategori
|  |--SCAN buku
|  `--USE TEMP B-TREE FOR GROUP BY
|--MATERIALIZE rata_jumlah
|  `--SCAN per_kategori
|--SCAN p
|--SEARCH k USING INTEGER PRIMARY KEY (rowid=?)
`--SCAN rata_jumlah

Keluaran tersebut memperlihatkan perbedaan perilaku yang paling praktis, yaitu perhitungan grup hanya berjalan satu kali meskipun namanya dipakai dua kali.

Ringkasan#

WITH menamai hasil antara sebagai CTE yang hanya hidup selama satu pernyataan, sehingga query utama terbaca sebagai rangkaian tahap.
Beberapa CTE boleh dirangkai berurutan dan saling merujuk, dan setiap tahap dapat diuji secara terpisah.
CTE yang dirujuk beberapa kali ditulis sekali dan dipakai berkali-kali, dan perencana mematerialisasinya satu kali.
CTE dengan satu rujukan kerap melebur ke query utama, sedangkan tulisan MATERIALIZED dan NOT MATERIALIZED tersedia untuk menguncinya.

Referensi#

WITH — Dokumentasi SQLite — Sintaks CTE, peleburan, materialisasi, dan petunjuk MATERIALIZED.
SELECT — Dokumentasi SQLite — Sintaks SELECT yang menjadi isi setiap CTE dan query utamanya.