SQL untuk Data Engineering — Data Engineering

SQL Advanced untuk Data Engineering Sebagai data engineer, SQL bukan hanya SELECT-WHERE-JOIN. Kamu butuh window functions, CTEs, dan analytical queries untuk…

SQL untuk data engineering adalah SQL yang menjawab pertanyaan tentang banyak baris sekaligus, bukan cuma membaca dan mengubah satu baris (CRUD). Bayangin buku nilai satu sekolah. GROUP BY melipat semua murid jadi satu baris per kelas: "rata-rata kelas 7A". Window function membiarkan tiap murid tetap di barisnya, lalu menuliskan di sebelahnya "peringkat 2 di kelas 7A". Dua-duanya dihitung per kelas, bedanya: yang satu melipat baris, yang lain enggak.

🧪 Tebakan cepat: query akhir pelajaran ini memberi peringkat dengan RANK() per minggu dan kota, lalu menyaring WHERE posisi = 1. Datanya 2 minggu dan 4 kota, jadi 8 kelompok. Hasilnya berapa baris: 8 atau 9? Tebak dulu, jawabannya ada di bagian "CTE: pertanyaan pemilik, langkah demi langkah".

Window function: menghitung tanpa melipat baris

Window function ditulis dengan OVER (...):

PARTITION BY membagi baris jadi kelompok (window), seperti GROUP BY, tapi barisnya gak dilipat.
ORDER BY di dalam OVER mengurutkan baris di tiap window, untuk peringkat dan jumlah berjalan.

Fungsinya antara lain ROW_NUMBER() (nomor urut 1, 2, 3), RANK() (peringkat, yang seri dapat angka sama), SUM(...) OVER (...) (jumlah berjalan), dan LAG(...) (nilai baris sebelumnya). Karena barisnya gak hilang, running total dan ranking bisa dibuat tanpa GROUP BY. Semua query di sini dijalankan di SQLite (sama dengan Lab dan Playground) pada data Toko Buku.

ROW_NUMBER: buang baris dobel

SELECT id, order_id, book_id, qty,
       ROW_NUMBER() OVER (PARTITION BY order_id, book_id ORDER BY id) AS ke
FROM order_items
WHERE order_id IN (5, 6);

id	order_id	book_id	qty	ke
7	5	2	1	1
8	6	2	2	1
9	6	2	2	2

PARTITION BY order_id, book_id membuat satu window per pasangan pesanan dan buku. Pesanan 6 dengan Bumi Manusia punya dua baris.
ORDER BY id menentukan siapa yang nomor 1: id terkecil, yang tersimpan duluan.
Baris yang ke = 1 dipertahankan, ke = 2 adalah baris dobel. Beda dengan DISTINCT di pelajaran pertama: ROW_NUMBER tetap jalan kalau baris kembarnya beda di kolom lain (misalnya id, atau jam dimuat), dan kamu yang memilih mana yang disimpan.

Bucket per minggu

Pertanyaan pemilik butuh tanggal dibulatkan ke awal minggu. Tiap database menulisnya beda:

Database	Senin awal minggu dari `created_at`
SQLite	`date(created_at, 'weekday 0', '-6 days')`
MySQL	`DATE_SUB(DATE(created_at), INTERVAL WEEKDAY(created_at) DAY)`
PostgreSQL, DuckDB	`date_trunc('week', created_at)`
BigQuery	`DATE_TRUNC(DATE(created_at), WEEK(MONDAY))`; `WEEK` saja mulai hari Minggu

Di SQLite, 'weekday 0' maju ke hari Minggu terdekat (atau tetap, kalau sudah Minggu), lalu '-6 days' mundur ke Seninnya. Hati-hati dengan hari Minggu: pesanan Maya 2026-10-04 20:30:00 jatuh hari Minggu. Dengan minggu yang mulai Senin, dia masuk minggu 28 September; dengan minggu yang mulai Minggu, dia masuk minggu 4 Oktober, dan jawabannya berubah. Sepakati dulu awal minggunya dengan pemilik.

CTE: pertanyaan pemilik, langkah demi langkah

CTE (Common Table Expression) adalah WITH nama AS (SELECT ...): satu langkah bernama yang cuma hidup selama query itu jalan. Beberapa CTE dipisah koma, dan tiap langkah boleh membaca langkah sebelumnya. Query yang tadinya subquery bersarang jadi bisa dibaca dari atas ke bawah, dan tiap langkah bisa dicek sendiri dengan SELECT * FROM nama_langkah.

WITH bersih AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY order_id, book_id ORDER BY id) AS ke
  FROM order_items
),
penjualan AS (
  SELECT date(o.created_at, 'weekday 0', '-6 days') AS minggu,
         COALESCE(c.city, 'Tidak diketahui') AS kota,
         b.title AS judul,
         i.qty
  FROM bersih i
  JOIN orders o ON o.id = i.order_id
  JOIN customers c ON c.id = o.customer_id
  JOIN books b ON b.id = i.book_id
  WHERE i.ke = 1
),
per_judul AS (
  SELECT minggu, kota, judul, SUM(qty) AS terjual
  FROM penjualan
  GROUP BY minggu, kota, judul
),
peringkat AS (
  SELECT *, RANK() OVER (PARTITION BY minggu, kota ORDER BY terjual DESC) AS posisi
  FROM per_judul
)
SELECT minggu, kota, judul, terjual, posisi
FROM peringkat
WHERE posisi = 1
ORDER BY minggu, kota, judul;

minggu	kota	judul	terjual	posisi
2026-09-28	Bandung	Bumi Manusia	2	1
2026-09-28	Jakarta	Laskar Pelangi	3	1
2026-09-28	Surabaya	Cantik Itu Luka	2	1
2026-09-28	Tidak diketahui	Laskar Pelangi	1	1
2026-10-05	Bandung	Ronggeng Dukuh Paruk	2	1
2026-10-05	Jakarta	Cantik Itu Luka	2	1
2026-10-05	Surabaya	Laskar Pelangi	1	1
2026-10-05	Surabaya	Ronggeng Dukuh Paruk	1	1
2026-10-05	Tidak diketahui	Bumi Manusia	1	1

bersih menomori baris kembar, penjualan cuma mengambil yang ke = 1 dan memberi kota NULL nama.
per_judul menjumlah per minggu, kota, dan judul. peringkat memberi posisi tanpa melipat baris.
WHERE posisi = 1 ditaruh di query luar. Window function dihitung setelah WHERE, jadi gak bisa disaring di WHERE query yang sama; itu gunanya langkah peringkat.

Hasilnya 9 baris. Itu jawaban tebakannya: Surabaya minggu 5 Oktober seri, Laskar Pelangi dan Ronggeng Dukuh Paruk sama-sama 1, dan RANK() memberi keduanya posisi 1. ROW_NUMBER() akan memberi 8 baris, memilih salah satu tanpa jaminan yang mana. DENSE_RANK() sama dengan RANK() untuk juara; bedanya di bawahnya: setelah dua juara seri, RANK lanjut ke 3, DENSE_RANK ke 2.

Running total

Omzet harian dan jumlah berjalannya, dari data yang sudah bersih:

WITH harian AS (
  SELECT date(o.created_at) AS tanggal, SUM(oi.qty * b.price) AS omzet
  FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY order_id, book_id ORDER BY id) AS ke FROM order_items) oi
  JOIN orders o ON o.id = oi.order_id
  JOIN books b ON b.id = oi.book_id
  WHERE oi.ke = 1
  GROUP BY tanggal
)
SELECT tanggal, omzet,
       SUM(omzet) OVER (ORDER BY tanggal) AS kumulatif
FROM harian
ORDER BY tanggal;

Empat baris pertamanya: 2026-09-28 | 324000 | 324000, 2026-09-29 | 220000 | 544000, 2026-09-30 | 273000 | 817000, 2026-10-01 | 375000 | 1192000. Baris terakhir, 2026-10-09, kumulatifnya 2214000: total omzet bersih. SUM(...) OVER (ORDER BY tanggal) menjumlah dari baris pertama sampai baris itu.

ROLLUP dan GROUPING SETS

ROLLUP menambahkan baris subtotal dan total ke hasil GROUP BY. GROUP BY ROLLUP (kota) sama dengan GROUP BY GROUPING SETS ((kota), ()): satu baris per kota, plus satu baris total yang kota-nya NULL. PostgreSQL, DuckDB, BigQuery, dan Snowflake menulisnya begitu; MySQL menulis GROUP BY kota WITH ROLLUP.

SQLite gak punya ROLLUP sama sekali. GROUP BY ROLLUP (c.city) dijawab no such function: ROLLUP, dan WITH ROLLUP dijawab near "WITH": syntax error. Gantinya, UNION ALL dengan query total:

WITH penjualan AS (
  SELECT COALESCE(c.city, 'Tidak diketahui') AS kota, i.qty
  FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY order_id, book_id ORDER BY id) AS ke FROM order_items) i
  JOIN orders o ON o.id = i.order_id
  JOIN customers c ON c.id = o.customer_id
  WHERE i.ke = 1
)
SELECT kota, SUM(qty) AS terjual FROM penjualan GROUP BY kota
UNION ALL
SELECT 'SEMUA KOTA', SUM(qty) FROM penjualan;

Hasilnya Bandung 5, Jakarta 9, Surabaya 5, Tidak diketahui 2, lalu SEMUA KOTA 21. DuckDB dengan ROLLUP memberi angka yang sama, dengan NULL di tempat SEMUA KOTA.

NULL artinya "gak tahu"

NULL bukan nilai kosong, tapi "gak diketahui". Perbandingan apa pun dengan NULL hasilnya juga gak diketahui, dan WHERE cuma meloloskan yang benar. Di 19 baris order_items yang disambung ke pelanggannya:

Ekspresi	Hasil
`COUNT(*)`	19
`COUNT(c.city)`	17: `COUNT(kolom)` gak menghitung `NULL`
baris dengan `c.city <> 'Jakarta'`	9, bukan 11: 2 baris Maya ikut terbuang
baris dengan `c.city IS NULL`	2: cek `NULL` harus pakai `IS NULL`, bukan `= NULL`

COALESCE(c.city, 'Tidak diketahui') mengganti NULL dengan nilai yang kamu pilih, seperti di CTE penjualan. GROUP BY mengumpulkan semua NULL jadi satu kelompok.

⚠️ Jebakan yang sering ketemu

Menyaring window function di WHERE yang sama. WHERE RANK() OVER (...) = 1 ditolak. Hitung di CTE, saring di query luar.
ROW_NUMBER untuk mencari juara. Seri jadi tersembunyi. Pakai RANK kalau yang seri harus tampil semua.
<> atau NOT IN di kolom yang bisa NULL. Barisnya hilang diam-diam. COALESCE dulu, atau tambah OR kolom IS NULL.
Awal minggu yang beda-beda. SQLite, MySQL, dan PostgreSQL di atas mulai Senin, WEEK di BigQuery mulai Minggu. Pesanan hari Minggu pindah minggu.

TL;DR: Window function (... OVER (PARTITION BY ... ORDER BY ...)) menghitung per kelompok tanpa melipat baris: ROW_NUMBER() untuk membuang baris dobel (ke = 1), RANK() untuk peringkat yang menampilkan seri, SUM() OVER (ORDER BY tanggal) untuk running total. CTE (WITH nama AS (...)) memecah query panjang jadi langkah bernama yang dibaca dari atas ke bawah dan bisa dicek satu per satu; window function disaring di query luarnya. Tanggal dibulatkan ke Senin dengan date(created_at, 'weekday 0', '-6 days') di SQLite atau date_trunc('week', ...) di PostgreSQL dan DuckDB. SQLite gak punya ROLLUP; pakai UNION ALL dengan baris total. NULL artinya gak diketahui: <> 'Jakarta' membuang baris NULL, COUNT(kolom) melewatinya. Jawaban pemilik: 9 baris, karena Surabaya minggu 5 Oktober seri.