Halo! Selamat datang. Saya di sini untuk memandu Anda menguasai SQL, dan kita akan melakukannya dengan cara yang praktis. Lupakan teori yang membosankan. Kita akan membangun satu kueri canggih, langkah demi langkah, dari nol.
Tujuan kita sederhana namun powerful: Kita punya data penjualan harian, tapi ada beberapa tanggal yang hilang. Kita tidak hanya akan mengisi tanggal yang hilang itu, tapi kita juga akan membuat estimasi penjualan yang cerdas untuk tanggal-tanggal tersebut dengan mengambil rata-rata penjualan dari hari sebelum dan sesudahnya.
Melalui proses ini, Anda akan menguasai 12 konsep SQL canggih. Mari kita mulai!
🎯 Studi Kasus: Mengisi Data Penjualan yang Hilang
Bayangkan kita punya tabel sales seperti ini:
| dt | num_sales |
| 2025-01-01 | 61 |
| 2025-01-02 | 72 |
| 2025-01-04 | 84 |
| 2025-01-05 | 90 |
| 2025-01-07 | 65 |
Perhatikan, tanggal 3 dan 6 Januari hilang.
Hasil akhir yang kita inginkan adalah tabel lengkap seperti ini, di mana penjualan tanggal 3 adalah rata-rata dari tanggal 2 (72) dan 4 (84), yaitu 78.
| dt | estimated_sales |
| 2025-01-01 | 61 |
| 2025-01-02 | 72 |
| 2025-01-03 | 78 |
| 2025-01-04 | 84 |
| 2025-01-05 | 90 |
| 2025-01-06 | 77.5 |
| 2025-01-07 | 65 |
Mari kita bangun kuerinya.
1. UNION vs. UNION ALL: Menumpuk Kumpulan Data
Langkah pertama adalah membuat daftar tanggal yang seharusnya ada (1 s.d. 7 Januari). Kita bisa membuatnya secara manual menggunakan UNION.
SQL
SELECT '2025-01-01' AS dt UNION SELECT '2025-01-02' UNION SELECT '2025-01-03' -- ... dan seterusnya
UNION menumpuk hasil dari beberapa SELECT statement. Tapi ada jebakan:
- UNION: Menghapus baris duplikat. Jika Anda UNION '2025-01-02' dua kali, hasilnya hanya akan muncul satu kali.
- UNION ALL: Menumpuk semua hasil apa adanya, termasuk duplikat.
Insight (Sudut Pandang yang Jarang Dilihat):
Orang sering memilih UNION karena "lebih aman" (menghapus duplikat). Tapi mereka lupa bahwa untuk menghapus duplikat, database harus melakukan operasi sort atau hash yang mahal di belakang layar. UNION ALL jauh lebih cepat dan efisien. Jika Anda tahu data Anda unik (seperti seri tanggal yang kita buat), atau jika Anda tidak peduli ada duplikat, selalu gunakan UNION ALL demi performa.
👉 Apa yang bisa kamu lakukan sekarang:
Buat dua SELECT statement yang menghasilkan data yang sama. Gabungkan menggunakan UNION dan lihat hasilnya. Lalu, ganti menjadi UNION ALL dan lihat perbedaannya.
2. Subquery & JOIN: Menggabungkan Data
Sekarang kita punya dua "tabel":
- Tabel sales asli kita (yang datanya bolong).
- Seri tanggal lengkap yang kita buat dengan UNION (kita sebut saja all_dates).
Kita perlu menggabungkan keduanya. Caranya, kita jadikan kueri UNION kita sebagai Subquery (kueri di dalam kueri) dan memberinya nama alias (misal SQ).
SQL
SELECT
SQ.dt,
sales.num_sales
FROM
( -- Ini adalah Subquery
SELECT '2025-01-01' AS dt
UNION
SELECT '2025-01-02'
-- ... dan seterusnya
) AS SQ
LEFT JOIN sales
ON SQ.dt = sales.dt;
Kenapa LEFT JOIN?
- INNER JOIN: Hanya akan menampilkan baris yang ada di kedua tabel. Ini akan menghilangkan tanggal 3 dan 6 (karena tidak ada di tabel sales), yang justru ingin kita perbaiki.
- LEFT JOIN: Akan menyimpan semua baris dari tabel "kiri" (yaitu SQ, seri tanggal lengkap kita) dan mengambil data yang cocok dari tabel "kanan" (sales). Jika tidak ada yang cocok (seperti pada tanggal 3 dan 6), kolom num_sales akan diisi dengan NULL. Dan NULL inilah yang kita cari!
Insight (Sudut Pandang yang Jarang Dilihat):
Berpikirlah tentang JOIN bukan sebagai perintah, tapi sebagai pertanyaan logika. INNER JOIN bertanya: "Data apa yang sama di kedua sisi?" LEFT JOIN bertanya: "Beri aku semua data dari sisi kiri, dan jika ada yang cocok di sisi kanan, tempelkan datanya. Kalau tidak, bilang saja NULL." 90% bug SQL pemula berasal dari kesalahan memilih tipe JOIN.
👉 Apa yang bisa kamu lakukan sekarang:
Ambil dua tabel yang Anda miliki (misal, users dan transactions). Lakukan LEFT JOIN dari users ke transactions. Anda akan menemukan semua pengguna, termasuk yang tidak pernah bertransaksi (mereka akan memiliki NULL di kolom transaksi).
3. CTE (Common Table Expression): Merapikan Kueri
Subquery di atas berfungsi, tapi membuat kueri kita "bersarang" dan sulit dibaca. Ada cara yang lebih modern dan bersih: CTE atau Common Table Expression.
Kita memindahkan definisi Subquery ke atas kueri utama menggunakan WITH:
SQL
-- Definisikan CTE di sini WITH all_dates AS ( SELECT '2025-01-01' AS dt UNION SELECT '2025-01-02' -- ... dan seterusnya ) -- Baru jalankan kueri utama SELECT all_dates.dt, sales.num_sales FROM all_dates LEFT JOIN sales ON all_dates.dt = sales.dt;
Hasilnya 100% identik dengan Subquery, tapi lihat betapa jauh lebih rapi!
Insight (Sudut Pandang yang Jarang Dilihat):
Subquery membuat kueri Anda ditulis dari "dalam ke luar". CTE membuat Anda menulis dari "atas ke bawah", seperti sebuah resep. Anda mendefinisikan "bahan-bahan" (WITH ...) terlebih dahulu, lalu Anda "meraciknya" di SELECT utama. Ini adalah game-changer untuk readability (keterbacaan). Jika kueri Anda memiliki lebih dari satu langkah, CTE adalah wajib. Anda bahkan bisa merantai beberapa CTE (WITH cte1 AS ..., cte2 AS ...).
👉 Apa yang bisa kamu lakukan sekarang:
Ambil kueri apa pun yang Anda miliki yang menggunakan Subquery di klausa FROM. Tulis ulang menggunakan CTE. Bandingkan tampilannya. Mana yang lebih mudah Anda jelaskan ke rekan kerja?
4. Recursive CTE & Fungsi Tanggal: Otomatisasi Seri
Menulis UNION 7 kali itu melelahkan. Bagaimana jika kita butuh 100 hari? Kita bisa menggunakan Recursive CTE. Ini adalah CTE canggih yang bisa "memanggil dirinya sendiri" untuk membuat data sekuensial.
Kita juga akan butuh dua fungsi tanggal:
- CAST(... AS DATE): Mengubah teks '2025-01-01' menjadi tipe data tanggal sungguhan agar bisa dihitung.
- DATE_ADD / INTERVAL: Sintaksis untuk menambahkan 1 hari ke tanggal (sintaksis bisa berbeda antar database, misal DATE_ADD(dt, INTERVAL 1 DAY) di MySQL).
SQL
-- Tambahkan keyword RECURSIVE
WITH RECURSIVE all_dates (dt) AS (
-- 1. Anchor (Titik Awal)
SELECT CAST('2025-01-01' AS DATE)
UNION ALL
-- 2. Recursive Step (Langkah Perulangan)
SELECT
DATE_ADD(dt, INTERVAL 1 DAY)
FROM
all_dates -- Memanggil dirinya sendiri!
WHERE
dt < '2025-01-07' -- 3. Termination (Kondisi Berhenti)
)
SELECT * FROM all_dates;
Kueri ini secara ajaib menghasilkan 7 tanggal tanpa perlu hardcoding.
Insight (Sudut Pandang yang Jarang Dilihat):
Orang awam melihat Recursive CTE dan takut karena terlihat seperti looping tak terbatas. Padahal, ini adalah alat paling efisien untuk menghasilkan data on-the-fly (seperti kalender, bagan organisasi, atau hierarki). Kuncinya ada di tiga bagian: Anchor (titik awal), Recursive Step (logika penambahan), dan Termination (kondisi berhenti). Lupakan satu saja, kueri Anda akan crash.
👉 Apa yang bisa kamu lakukan sekarang:
Tantangan: Coba buat seri angka dari 1 sampai 100 menggunakan Recursive CTE. Mulai dengan SELECT 1 AS num (Anchor), lalu UNION ALL dengan SELECT num + 1 FROM cte_name WHERE num < 100 (Recursive Step & Termination).
5. COALESCE & ROUND: Menangani NULL
Sekarang kita kembali ke kueri utama kita, yang menghasilkan NULL pada tanggal 3 & 6.
NULL itu "racun" dalam analitik. 5 + NULL hasilnya NULL. Kita harus menanganinya.
Opsi 1: Isi dengan 0
Gunakan COALESCE, fungsi yang mengembalikan nilai pertama yang tidak NULL.
COALESCE(num_sales, 0): "Coba ambil num_sales. Jika NULL, pakai 0."
Opsi 2: Isi dengan Rata-rata Kolom (Imputasi)
Kita bisa lebih pintar. Kita isi NULL dengan rata-rata dari seluruh kolom num_sales menggunakan Subquery di dalam COALESCE.
SQL
SELECT
all_dates.dt,
COALESCE(
sales.num_sales,
(SELECT AVG(num_sales) FROM sales) -- Akan mengisi NULL dgn 77.8
) AS sales_estimate_1,
-- Kita juga bisa membulatkannya
ROUND(
COALESCE(
sales.num_sales,
(SELECT AVG(num_sales) FROM sales)
), 1 -- Bulatkan 1 desimal
) AS sales_estimate_2
FROM
all_dates
LEFT JOIN sales
ON all_dates.dt = sales.dt;
Insight (Sudut Pandang yang Jarang Dilihat):
NULL tidak sama dengan 0. NULL berarti "tidak diketahui". COALESCE adalah penawarnya. Ini adalah cara Anda sebagai analis untuk secara eksplisit menyatakan "Bagaimana saya ingin memperlakukan data yang tidak diketahui ini?" Mengisinya dengan 0, rata-rata, atau nilai lain adalah keputusan bisnis, dan COALESCE adalah alat teknis untuk mengeksekusinya.
👉 Apa yang bisa kamu lakukan sekarang:
Jalankan kueri LEFT JOIN Anda. Gunakan COALESCE pada kolom yang NULL untuk menggantinya dengan teks 'Missing' atau angka 0.
6. Window Functions (ROW_NUMBER, LAG, LEAD)
Mengisi NULL dengan rata-rata global (77.8) itu "oke", tapi tidak "pintar". Estimasi untuk tanggal 3 seharusnya lebih dekat dengan tanggal 2 & 4, bukan rata-rata global.
Untuk melakukan ini, kita perlu "melihat" nilai di baris sebelum dan sesudah baris saat ini. Inilah kekuatan Window Functions.
- Window Function melakukan perhitungan di seluruh "jendela" data tanpa mengkolaps baris (tidak seperti GROUP BY).
- OVER(): Keyword wajib untuk mendefinisikan "jendela".
Mari kita coba tiga fungsi:
- ROW_NUMBER() OVER(): Memberi nomor 1, 2, 3... pada setiap baris.
- LAG(num_sales) OVER(): Mengambil nilai num_sales dari baris sebelumnya (Lag = Ketinggalan).
- LEAD(num_sales) OVER(): Mengambil nilai num_sales dari baris berikutnya (Lead = Memimpin).
SQL
-- Kueri ini hanya untuk demo LAG dan LEAD SELECT dt, num_sales, LAG(num_sales) OVER (ORDER BY dt) AS prior_day_sales, LEAD(num_sales) OVER (ORDER BY dt) AS next_day_sales FROM sales;
Hasilnya akan seperti ini (perhatikan NULL di awal dan akhir):
| dt | num_sales | prior_day_sales | next_day_sales |
| 1 | 61 | NULL | 72 |
| 2 | 72 | 61 | 84 |
| 4 | 84 | 72 | 90 |
| 5 | 90 | 84 | 65 |
| 7 | 65 | 90 | NULL |
Insight (Sudut Pandang yang Jarang Dilihat):
Ini adalah kekuatan super SQL. Sebelum ada window functions, untuk mendapatkan nilai baris sebelumnya, Anda harus melakukan self-join yang rumit, lambat, dan membingungkan. Sekarang, Anda bisa "melihat" ke atas dan ke bawah window data Anda dengan bebas hanya dengan LAG dan LEAD. Ini membuka semua jenis analisis: perhitungan year-over-year, moving averages, dan, seperti kasus kita, mengambil nilai tetangga.
👉 Apa yang bisa kamu lakukan sekarang:
Ambil data penjualan Anda. Tambahkan kolom baru menggunakan LAG(num_sales) OVER (ORDER BY date). Sekarang Anda memiliki penjualan hari ini dan kemarin dalam satu baris. Anda bisa dengan mudah menghitung selisih harian!
7. Kueri Final: Menggabungkan COALESCE & Window Functions
Sekarang kita siap untuk final. Kita akan gabungkan COALESCE dengan LAG dan LEAD untuk membuat estimasi yang cerdas.
Logikanya:
COALESCE( num_sales, (LAG(...) + LEAD(...)) / 2 )
Artinya: "Coba ambil num_sales. Jika NULL, maka ambil nilai LAG (hari sebelumnya), tambahkan dengan nilai LEAD (hari sesudahnya), dan bagi 2 (rata-rata)."
Kita perlu sedikit merapikan kuerinya. Kita butuh CTE dua langkah:
- CTE_Joined: Gabungkan tanggal lengkap (all_dates) dengan sales.
- CTE_Window: Terapkan LAG dan LEAD pada hasil CTE_Joined.
- SELECT Final: Terapkan logika COALESCE kita.
SQL
-- 1. CTE untuk membuat seri tanggal lengkap
WITH RECURSIVE all_dates (dt) AS (
SELECT CAST('2025-01-01' AS DATE)
UNION ALL
SELECT DATE_ADD(dt, INTERVAL 1 DAY)
FROM all_dates
WHERE dt < '2025-01-07'
),
-- 2. CTE untuk join dan dapatkan LAG/LEAD
CTE_Window AS (
SELECT
all_dates.dt,
sales.num_sales,
-- Kita butuh LAG/LEAD di sini, PENTING: Terapkan pada 'num_sales'
-- (Perhatikan: ini perlu penyesuaian logika,
-- kita harusnya menerapkan LAG/LEAD *setelah* data di-join
-- dan di-isi. Mari kita sederhanakan untuk materi ini
-- seperti di video, kita terapkan di langkah akhir)
-- Versi yang disederhanakan (seperti di video)
LAG(sales.num_sales) OVER (ORDER BY all_dates.dt) AS prior_sales,
LEAD(sales.num_sales) OVER (ORDER BY all_dates.dt) AS next_sales
FROM
all_dates
LEFT JOIN sales
ON all_dates.dt = sales.dt
)
-- 3. SELECT Final untuk menghitung estimasi
SELECT
dt,
num_sales AS original_sales,
-- Logika emas kita ada di sini
ROUND(
COALESCE(
num_sales,
(prior_sales + next_sales) / 2
), 1
) AS estimated_sales
FROM
CTE_Window;
(Catatan: Logika LAG/LEAD di atas akan mengambil nilai NULL tetangga. Kueri di video menerapkan LAG/LEAD pada tabel yang sudah digabungkan, yang merupakan trik cerdas. Untuk tanggal 3, LAG-nya adalah 72 (tgl 2) dan LEAD-nya adalah 84 (tgl 4). Sempurna!)
Hasil Akhir:
Kueri ini akan memberi Anda dengan tepat apa yang kita inginkan: daftar tanggal lengkap dengan estimasi cerdas untuk data yang hilang.
Insight (Sudut Pandang yang Jarang Dilihat):
Ini adalah puncak dari declarative programming. Kita tidak memberi tahu SQL bagaimana cara menghitungnya (jangan loop, jangan simpan variabel). Kita mendeklarasikan apa yang kita inginkan: "Untuk setiap baris, beri saya penjualannya. Oh, jika tidak ada, beri saya rata-rata dari tetangga sebelum dan sesudahnya." Database engine yang canggih kemudian akan mencari cara paling efisien untuk mengeksekusi keinginan tersebut. Inilah keindahan SQL.
✅ Checklist & Rencana Aksi Anda
Anda berhasil! Kueri yang tadinya terlihat mustahil sekarang sudah Anda pahami. Tapi pengetahuan tanpa praktik akan menguap.
Berikut adalah checklist Anda untuk benar-benar menguasai materi ini:
- [ ] Pahami Penumpukan Data: Ambil dua kueri sederhana dan gabungkan menggunakan UNION. Ganti ke UNION ALL dan catat perbedaan performa/hasilnya.
- [ ] Pahami Logika Himpunan: Ambil dua tabel (misal, customers & orders). Lakukan INNER JOIN vs. LEFT JOIN. Jelaskan dengan kata-kata Anda sendiri siapa yang muncul dan siapa yang hilang di setiap skenario.
- [ ] Latih Kebersihan Kode: Ambil kueri JOIN Anda dari langkah #2. Tulis ulang bagian tabel customers sebagai Subquery (misal, (SELECT * FROM customers WHERE region = 'West')).
- [ ] Ubah Subquery menjadi CTE: Tulis ulang kueri dari langkah #3 menggunakan WITH ... AS .... Rasakan bedanya dalam keterbacaan.
- [ ] Hasilkan Data Sendiri: Gunakan Recursive CTE untuk membuat daftar 30 hari ke depan dari hari ini.
- [ ] Bersihkan Data NULL: Jalankan kueri yang Anda tahu menghasilkan NULL (dari LEFT JOIN Anda). Gunakan COALESCE untuk mengganti NULL dengan nilai statis (angka 0 atau teks 'N/A').
- [ ] Lakukan Imputasi Sederhana: Ganti COALESCE Anda dari langkah #6. Alih-alih nilai statis, isi NULL dengan AVG dari seluruh kolom (menggunakan Subquery). Jangan lupa gunakan ROUND.
- [ ] Lihat "Tetangga" Anda: Ambil tabel data apa pun yang memiliki urutan (misal, penjualan berdasarkan tanggal). Tambahkan tiga kolom: ROW_NUMBER(), LAG(value), dan LEAD(value). Pastikan Anda menyertakan OVER (ORDER BY ...) yang benar.
- [ ] [Tantangan] Gabungkan Semuanya: Ambil data Anda dari langkah #8. Buat kueri yang menghitung (value - LAG(value)) / LAG(value) untuk menemukan persentase pertumbuhan day-over-day.
Selamat mencoba, dan sampai jumpa di materi berikutnya!
📜 Glosarium SQL Canggih: Dari Awam Jadi Ahli
Berikut adalah istilah-istilah kunci, dijelaskan dengan analogi agar mudah menempel di ingatan.
1. UNION vs. UNION ALL
- Apa Ini? Perintah untuk "menumpuk" hasil dari dua (atau lebih) kueri SELECT di atas satu sama lain.
- Analogi: Dua Daftar Tamu Pesta
- Bayangkan Anda punya dua daftar tamu (Daftar A dan Daftar B).
- UNION: Anda adalah penjaga pintu yang teliti. Anda panggil semua orang dari Daftar A. Lalu, saat memanggil orang dari Daftar B, Anda mengecek: "Apakah orang ini sudah ada di dalam?" Jika "Budi" ada di kedua daftar, hanya satu Budi yang boleh masuk. Hasilnya pasti unik (tidak ada duplikat).
- UNION ALL: Anda adalah penjaga pintu yang santai. Anda panggil semua orang dari Daftar A. Lalu Anda panggil semua orang dari Daftar B. Anda tidak peduli. Jika "Budi" ada di kedua daftar, maka akan ada dua Budi di pesta.
- Insight Ahli: UNION perlu kerja ekstra (menyortir dan membandingkan data) untuk menghapus duplikat, sehingga lebih lambat. UNION ALL jauh lebih cepat karena hanya menumpuk data. Selalu gunakan UNION ALL kecuali Anda benar-benar butuh menghapus duplikat.
2. Subquery (Subkueri)
- Apa Ini? Sebuah kueri SELECT yang "bersarang" di dalam kueri SELECT utama.
- Analogi: Resep di Dalam Resep
- Anda sedang membuat kue (kueri utama). Di langkah ke-3, resepnya bilang: "Masukkan 1 cangkir krim mentega vanila."
- Di bagian bawah resep kue, ada resep terpisah untuk membuat krim mentega vanila itu (campur mentega, gula, vanila).
- Subquery adalah resep terpisah ((SELECT ... FROM ...) di dalam tanda kurung) yang harus Anda selesaikan terlebih dahulu untuk mendapatkan satu "bahan" yang dibutuhkan oleh resep utama Anda.
- Insight Ahli: Subquery sangat berguna, tetapi jika terlalu banyak bersarang (resep di dalam resep di dalam resep), kueri Anda akan jadi "spaghetti code" — berantakan dan sulit dibaca.
3. CTE (Common Table Expression)
- Apa Ini? Cara modern dan rapi untuk menggantikan Subquery. Anda mendefinisikan "tabel sementara" di awal kueri Anda.
- Analogi: Mise en Place (Persiapan Koki)
- Daripada membuat krim mentega vanila (Subquery) di tengah-tengah proses membuat kue, seorang koki profesional akan menyiapkannya terlebih dahulu.
- CTE adalah saat Anda berkata, WITH krim_mentega AS ( ... ) di bagian paling atas. Anda menyiapkan semua "bahan" Anda di mangkuk-mangkuk terpisah (CTE 1, CTE 2, dst.).
- Kueri utama Anda di bagian bawah menjadi sangat bersih, tinggal mengambil dari mangkuk-mangkuk yang sudah Anda siapkan.
- Insight Ahli: CTE membuat kueri Anda bisa "bercerita" dari atas ke bawah. Ini adalah game-changer untuk keterbacaan (readability) dan jauh lebih mudah di-debug daripada Subquery yang bersarang.
4. Recursive CTE (CTE Rekursif)
- Apa Ini? CTE canggih yang bisa "memanggil dirinya sendiri" untuk membuat data berurutan.
- Analogi: Efek Domino
- Bagaimana Anda membuat 100 domino jatuh? Anda tidak perlu mendorong 100 domino satu per satu.
- Anda hanya perlu melakukan 3 hal:
- Anchor (Jangkar): Mendorong domino pertama (SELECT '2025-01-01').
- Recursive Step (Langkah Berulang): Menetapkan aturan: "Setiap domino yang jatuh akan menjatuhkan domino tepat di depannya" (SELECT tanggal + 1 HARI).
- Termination (Kondisi Berhenti): Aturan ini berhenti berlaku setelah domino ke-100 (WHERE tanggal < '2025-01-07').
- Insight Ahli: Ini bukan looping seperti di bahasa pemrograman. Ini adalah cara paling efisien di SQL untuk menghasilkan data dari ketiadaan, seperti membuat daftar kalender, hierarki organisasi (BOS -> Manajer -> Staf), atau struktur pohon.
5. LEFT JOIN
- Apa Ini? Menggabungkan dua tabel, tetapi "memprioritaskan" semua data dari tabel kiri.
- Studi Kasus: Absensi Kelas
- Tabel Kiri: Daftar_Siswa (berisi semua siswa di kelas).
- Tabel Kanan: Pengumpul_PR (hanya berisi siswa yang mengumpulkan PR).
- Daftar_Siswa LEFT JOIN Pengumpul_PR: Hasilnya adalah semua siswa dari Daftar_Siswa. Jika seorang siswa ada di Pengumpul_PR, kolom nilainya akan terisi. Jika siswa itu tidak ada di Pengumpul_PR (dia tidak mengumpulkan), dia akan tetap ada di daftar, tetapi kolom nilainya akan berisi NULL.
- Insight Ahli: LEFT JOIN adalah alat utama Anda untuk menemukan apa yang hilang. Dalam video, kita menggunakannya untuk menemukan tanggal mana yang ada di kalender lengkap kita (kiri) tetapi hilang dari tabel sales (kanan).
6. COALESCE
- Apa Ini? Sebuah fungsi yang mengembalikan nilai pertama yang tidak NULL dari daftar yang Anda berikan.
- Analogi: Rencana Cadangan
- Bayangkan Anda ingin mencari nomor HP seseorang: COALESCE(nomor_HP_utama, nomor_HP_kantor, '00000').
- SQL akan:
- "Cek nomor_HP_utama. Ada isinya? Bagus, pakai itu."
- "Oh, nomor_HP_utama ternyata NULL (kosong)? Oke, lanjut. Cek nomor_HP_kantor."
- "Oh, nomor_HP_kantor juga NULL? Oke, rencana terakhir, pakai nilai default '00000'."
- Insight Ahli: NULL adalah "racun" dalam matematika (misal: 5 + NULL hasilnya NULL). COALESCE adalah penawarnya. Ini adalah cara Anda secara eksplisit menangani data yang hilang dan menggantinya dengan nilai default (seperti 0, teks 'N/A', atau bahkan rata-rata kolom).
7. Window Functions (LAG & LEAD)
- Apa Ini? Fungsi yang bisa melakukan perhitungan di seluruh "jendela" data (seperti semua baris), tetapi tanpa "meringkas" datanya (tidak seperti GROUP BY).
- Analogi: Berada di Antrian Bioskop
- Anda adalah baris data saat ini.
- OVER (ORDER BY ...): Ini adalah perintah untuk "semua orang harap mengantri dengan rapi berdasarkan nomor urut!" Tanpa ini, tidak ada yang tahu siapa di depan atau di belakang.
- LAG(): (Lag = Tertinggal). Anda menengok ke belakang dan melihat data penjualan orang yang tepat di belakang Anda (data kemarin).
- LEAD(): (Lead = Memimpin). Anda menengok ke depan dan melihat data penjualan orang yang tepat di depan Anda (data besok).
- Insight Ahli: Ini adalah kekuatan super SQL. Sebelum ada ini, Anda harus melakukan self-join yang rumit dan lambat. Sekarang, Anda bisa dengan mudah "melihat" data di baris lain. Ini adalah fondasi untuk analisis time-series, moving averages, dan (seperti di kasus kita) mengisi data yang hilang dengan nilai tetangganya.
👨🏫 Materi Ajar: "Cara Mengisi Data Bolong Seperti Analis Pro"
Gunakan teks ini untuk mengajarkan orang lain (atau "diri Anda di masa lalu"). Ini akan memaksa Anda menyederhanakan dan memperkuat pemahaman Anda.
(Mulai di sini)
Halo! Hari ini kita akan menyelesaikan salah satu masalah paling umum di dunia data: data yang bolong.
Bayangkan kamu punya data penjualan harian, tapi ternyata tanggal 3 dan 6 Januari datanya hilang (NULL). Apa yang kamu lakukan? Dihapus? Diisi angka 0?
Itu solusi amatir. Seorang analis pro akan mengestimasi data itu. Dan hari ini, kita akan mengisi data yang hilang itu dengan rata-rata dari hari sebelum dan sesudahnya. Keren, kan?
Kita akan membangun kuerinya langkah demi langkah.
Langkah 1: Siapkan "Bahan" (Pakai CTE)
Pertama, lupakan Subquery yang berantakan. Kita akan pakai CTE. Anggap ini seperti koki menyiapkan bahan di mangkuk terpisah sebelum memasak.
Kita butuh dua "mangkuk":
- Mangkuk Kalender: Daftar tanggal lengkap dari 1 s.d. 7 Januari.
- Mangkuk Data Asli: Data penjualan kita yang bolong.
Langkah 2: Buat Kalender Ajaib (Pakai Recursive CTE)
Bagaimana cara membuat daftar tanggal 1-7 Januari tanpa mengetik satu per satu? Pakai "Efek Domino" atau Recursive CTE.
Kita bilang ke SQL:
- Domino Pertama: Mulai dari '2025-01-01'.
- Aturan Jatuh: Tambahkan 1 hari (INTERVAL 1 DAY) ke tanggal sebelumnya.
- Berhenti: Lakukan ini sampai kamu mencapai '2025-01-07'.
SQL
WITH RECURSIVE all_dates AS (
SELECT CAST('2025-01-01' AS DATE) AS dt
UNION ALL
SELECT DATE_ADD(dt, INTERVAL 1 DAY)
FROM all_dates
WHERE dt < '2025-01-07'
)
SELECT * FROM all_dates;
(Catatan: CAST di sana penting untuk memberitahu SQL, "ini adalah tanggal, bukan teks!")
Coba jalankan itu. Kamu sekarang punya kalender lengkap. Ajaib!
Langkah 3: Temukan yang Hilang (Pakai LEFT JOIN)
Sekarang kita gabungkan "Mangkuk Kalender" kita (all_dates) dengan "Mangkuk Data Asli" (sales).
Kita pakai LEFT JOIN. Ingat analogi absensi kelas?
- Tabel Kiri: all_dates (semua tanggal yang seharusnya ada)
- Tabel Kanan: sales (data yang kita punya)
SQL
-- (Kode Recursive CTE dari atas...) SELECT T1.dt, T2.num_sales FROM all_dates AS T1 LEFT JOIN sales AS T2 ON T1.dt = T2.dt;
Hasilnya? Kita akan dapat 7 baris. Tanggal 1, 2, 4, 5, 7 akan ada num_sales-nya. Tapi tanggal 3 dan 6, kolom num_sales-nya akan berisi NULL.
Nah! Kita sudah menemukan data yang bolong!
Langkah 4: Lihat Tetangga (Pakai LAG & LEAD)
Sekarang bagian serunya. Untuk mengisi NULL di tanggal 3, kita butuh data tanggal 2 (kemarin) dan tanggal 4 (besok).
Kita perlu "melihat tetangga" kita. Kita tambahkan Window Function.
SQL
-- (Kode CTE dan JOIN dari atas...)
-- Kita perlu CTE lagi untuk merapikannya
WITH CTE_Joined AS (
SELECT
T1.dt,
T2.num_sales
FROM
all_dates AS T1
LEFT JOIN
sales AS T2 ON T1.dt = T2.dt
),
CTE_Window AS (
SELECT
dt,
num_sales,
LAG(num_sales) OVER (ORDER BY dt) AS prior_sales, -- Lihat ke belakang
LEAD(num_sales) OVER (ORDER BY dt) AS next_sales -- Lihat ke depan
FROM
CTE_Joined
)
SELECT * FROM CTE_Window;
Lihat hasilnya! Di baris tanggal 3, num_sales-nya NULL, tapi prior_sales-nya 72 (dari tgl 2) dan next_sales-nya 84 (dari tgl 4). Kita sudah dapat angkanya!
Langkah 5: Isi yang Bolong (Pakai COALESCE)
Ini langkah final. Kita gunakan "Rencana Cadangan" atau COALESCE.
Logikanya: "Coba ambil num_sales. Kalau ternyata NULL, jangan panik. Ambil (prior_sales + next_sales) / 2 sebagai gantinya."
SQL
-- (Semua kode CTE dari atas...)
SELECT
dt,
COALESCE(
num_sales, -- Rencana A
(prior_sales + next_sales) / 2 -- Rencana B (jika A adalah NULL)
) AS estimated_sales
FROM
CTE_Window;
Selesai! Kamu baru saja menjalankan kueri yang sangat canggih. Kamu membuat data kalender dari nol, menggabungkannya untuk menemukan NULL, menggunakan window function untuk melihat data tetangga, dan menggunakan COALESCE untuk mengisi NULL itu dengan perhitungan yang cerdas.
Itulah bedanya analis biasa dengan analis pro. Kamu tidak hanya menghapus data, kamu memperbaikinya.
(Akhiri di sini)