Window Functions Lanjutan

Kita sudah menguasai fungsi jendela inti — ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, hingga agregat berjendela dan frame. Rangkaian itu menutupi sebagian besar kebutuhan, tetapi beberapa pertanyaan masih membutuhkan perangkat lain: bagaimana membagi pelanggan menjadi empat kuartil pembelanjaan, menampilkan nilai pertama dan terakhir tiap kelompok, atau menghitung persentase kumulatif dari suatu ambang. MySQL menyediakan NTILE, FIRST_VALUE, LAST_VALUE, NTH_VALUE, CUME_DIST, dan PERCENT_RANK untuk semua itu. Chapter ini membedah masing-masing fungsi pada data yang sama, membongkar satu jebakan frame default yang membuat LAST_VALUE tampak salah, lalu menutup dengan dua teknik struktur: window function di atas hasil agregasi dan dua jendela berbeda dalam satu query. Terakhir, kita uji sendiri bahwa MySQL tidak memiliki klausa FILTER dan menyiapkan alternatifnya.

Menyiapkan Tabel Latihan #

Kita pakai sepuluh transaksi dari enam pelanggan dengan tiga kategori. Nilai kolom jumlah sengaja memuat angka kembar supaya perilaku fungsi terhadap nilai seri terlihat:

DROP DATABASE IF EXISTS belajar_adv;
CREATE DATABASE belajar_adv;
USE belajar_adv;

Output yang Dihasilkan:

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed

CREATE TABLE penjualan (
  id INT PRIMARY KEY,
  pelanggan VARCHAR(30) NOT NULL,
  kategori VARCHAR(20) NOT NULL,
  produk VARCHAR(30) NOT NULL,
  jumlah INT NOT NULL
);

INSERT INTO penjualan (id, pelanggan, kategori, produk, jumlah) VALUES
  (1,  'Budi', 'Kopi', 'Kopi Gayo',   2),
  (2,  'Budi', 'Teh',  'Teh Melati',  3),
  (3,  'Siti', 'Kopi', 'Kopi Toraja', 4),
  (4,  'Siti', 'Kopi', 'Kopi Gayo',   1),
  (5,  'Siti', 'Teh',  'Teh Melati',  5),
  (6,  'Andi', 'Gula', 'Gula Aren',   6),
  (7,  'Rina', 'Teh',  'Teh Melati',  2),
  (8,  'Dewi', 'Kopi', 'Kopi Toraja', 5),
  (9,  'Dewi', 'Gula', 'Gula Aren',   3),
  (10, 'Tono', 'Kopi', 'Kopi Gayo',   3);

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

Kolom jumlah berisi deret 2, 3, 4, 1, 5, 6, 2, 5, 3, 3. Bila diurutkan menaik menjadi 1, 2, 2, 3, 3, 3, 4, 5, 5, 6 — satu angka tunggal, dua pasang seri, dan tiga nilai 3 — komposisi yang akan membuat perilaku CUME_DIST dan PERCENT_RANK pada nilai seri terlihat jelas.

NTILE: Membagi Baris ke dalam Bucket #

NTILE(n) membagi seluruh baris jendela menjadi n bucket sebanyak mungkin sama besar dan melaporkan nomor bucket tiap baris. Ini cara cepat membuat kuartil (NTILE(4)) atau desil (NTILE(10)) dari suatu peringkat. Kita bagi sepuluh transaksi menjadi tiga bucket berdasarkan jumlah terbesar:

SELECT id, pelanggan, jumlah,
       NTILE(3) OVER (ORDER BY jumlah DESC, id) AS bucket
FROM penjualan
ORDER BY jumlah DESC, id;

Output yang Dihasilkan:

+----+-----------+--------+--------+
| id | pelanggan | jumlah | bucket |
+----+-----------+--------+--------+
|  6 | Andi      |      6 |      1 |
|  5 | Siti      |      5 |      1 |
|  8 | Dewi      |      5 |      1 |
|  3 | Siti      |      4 |      1 |
|  2 | Budi      |      3 |      2 |
|  9 | Dewi      |      3 |      2 |
| 10 | Tono      |      3 |      2 |
|  1 | Budi      |      2 |      3 |
|  7 | Rina      |      2 |      3 |
|  4 | Siti      |      1 |      3 |
+----+-----------+--------+--------+
10 rows in set (0.00 sec)

Mari kita baca pembagiannya. Sepuluh baris dibagi ke tiga bucket: bucket 1 berisi empat baris teratas (jumlah 6, 5, 5, 4), bucket 2 berisi tiga baris berikutnya (3, 3, 3), dan bucket 3 menampung sisanya (2, 2, 1). Ketika jumlah baris tidak habis dibagi n, kelebihannya dibagikan ke bucket paling awal — 10 baris dibagi 3 menghasilkan ukuran 4, 3, 3, bukan 3, 3, 4. Kunci kedua id pada ORDER BY memastikan baris yang bernilai sama mendapat urutan tetap sehingga bucket setiap baris deterministik. Berbeda dengan RANK() yang menghormati nilai seri, NTILE memotong baris secara fisik: dua transaksi bernilai 3 bisa jatuh ke bucket berbeda bila batas potongan jatuh di tengah kelompok seri.

FIRST_VALUE dan LAST_VALUE: Jebakan Frame Default #

FIRST_VALUE dan LAST_VALUE mengambil nilai kolom dari baris pertama dan terakhir dalam jendela. Kedengarannya sederhana, tetapi LAST_VALUE menyimpan jebakan yang paling sering membingungkan pemula. Kita jalankan dulu versi yang tampak aneh:

SELECT id, pelanggan, jumlah,
       FIRST_VALUE(jumlah) OVER w AS pertama,
       LAST_VALUE(jumlah)  OVER w AS terakhir
FROM penjualan
WINDOW w AS (ORDER BY id)
ORDER BY id;

Output yang Dihasilkan:

+----+-----------+--------+---------+----------+
| id | pelanggan | jumlah | pertama | terakhir |
+----+-----------+--------+---------+----------+
|  1 | Budi      |      2 |       2 |        2 |
|  2 | Budi      |      3 |       2 |        3 |
|  3 | Siti      |      4 |       2 |        4 |
|  4 | Siti      |      1 |       2 |        1 |
|  5 | Siti      |      5 |       2 |        5 |
|  6 | Andi      |      6 |       2 |        6 |
|  7 | Rina      |      2 |       2 |        2 |
|  8 | Dewi      |      5 |       2 |        5 |
|  9 | Dewi      |      3 |       2 |        3 |
| 10 | Tono      |      3 |       2 |        3 |
+----+-----------+--------+---------+----------+
10 rows in set (0.00 sec)

Kolom pertama berperilaku seperti yang kita harapkan: angka 2 — nilai baris id 1 — tercetak di semua baris. Kolom terakhir justru berubah-ubah mengikuti barisnya sendiri: baris id 1 menampilkan 2, baris id 3 menampilkan 4, baris id 6 menampilkan 6. Padahal baris terakhir jendela adalah id 10 dengan jumlah 3, jadi kolom terakhir seharusnya menampilkan 3 di mana-mana. Apa yang sebenarnya terjadi?

Jawabannya ada pada frame default. Untuk jendela yang memiliki ORDER BY, MySQL memasang frame RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW secara diam-diam — window function hanya boleh melihat dari awal jendela sampai baris sekarang. FIRST_VALUE mengambil baris pertama frame; karena frame selalu dimulai dari UNBOUNDED PRECEDING, hasilnya stabil. LAST_VALUE mengambil baris terakhir frame; karena frame berhenti di CURRENT ROW, baris “terakhir” yang dilihatnya adalah baris itu sendiri — itulah mengapa kolomnya tampak seperti salinan kolom jumlah. Fungsi bekerja persis sesuai spesifikasi; yang salah adalah dugaan kita tentang batas frame.

Perbaikannya: perlebar frame secara eksplisit sampai baris terakhir jendela.

SELECT id, pelanggan, jumlah,
       FIRST_VALUE(jumlah) OVER w AS pertama,
       LAST_VALUE(jumlah) OVER (
         w ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS terakhir
FROM penjualan
WINDOW w AS (ORDER BY id)
ORDER BY id;

Output yang Dihasilkan:

+----+-----------+--------+---------+----------+
| id | pelanggan | jumlah | pertama | terakhir |
+----+-----------+--------+---------+----------+
|  1 | Budi      |      2 |       2 |        3 |
|  2 | Budi      |      3 |       2 |        3 |
|  3 | Siti      |      4 |       2 |        3 |
|  4 | Siti      |      1 |       2 |        3 |
|  5 | Siti      |      5 |       2 |        3 |
|  6 | Andi      |      6 |       2 |        3 |
|  7 | Rina      |      2 |       2 |        3 |
|  8 | Dewi      |      5 |       2 |        3 |
|  9 | Dewi      |      3 |       2 |        3 |
| 10 | Tono      |      3 |       2 |        3 |
+----+-----------+--------+---------+----------+
10 rows in set (0.00 sec)

Sekarang kolom terakhir menampilkan 3 di semua baris — nilai baris id 10, ujung sebenarnya dari jendela. Penulisan OVER (w ROWS BETWEEN ...) memanfaatkan jendela bernama w lalu menambahkan frame yang belum dimilikinya; jendela bernama boleh diperluas frame-nya, tetapi tidak boleh diberi ORDER BY kedua. Aturan praktisnya: FIRST_VALUE aman dengan frame default, sedangkan LAST_VALUE hampir selalu membutuhkan frame ... AND CURRENT ROW hingga UNBOUNDED FOLLOWING yang eksplisit.

Polanya bekerja sama pada jendela berpartisi. Kita ambil transaksi pertama dan terakhir tiap kategori berdasarkan jumlah terkecil, dengan frame yang sudah diperbaiki:

SELECT kategori, pelanggan, jumlah,
       FIRST_VALUE(pelanggan) OVER w AS pembeli_terkecil,
       LAST_VALUE(pelanggan)  OVER (
         w ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS pembeli_terbesar
FROM penjualan
WINDOW w AS (PARTITION BY kategori ORDER BY jumlah, id)
ORDER BY kategori, jumlah, id;

Output yang Dihasilkan:

+----------+-----------+--------+------------------+------------------+
| kategori | pelanggan | jumlah | pembeli_terkecil | pembeli_terbesar |
+----------+-----------+--------+------------------+------------------+
| Gula     | Dewi      |      3 | Dewi             | Andi             |
| Gula     | Andi      |      6 | Dewi             | Andi             |
| Kopi     | Siti      |      1 | Siti             | Dewi             |
| Kopi     | Budi      |      2 | Siti             | Dewi             |
| Kopi     | Tono      |      3 | Siti             | Dewi             |
| Kopi     | Siti      |      4 | Siti             | Dewi             |
| Kopi     | Dewi      |      5 | Siti             | Dewi             |
| Teh      | Rina      |      2 | Rina             | Siti             |
| Teh      | Budi      |      3 | Rina             | Siti             |
| Teh      | Siti      |      5 | Rina             | Siti             |
+----------+-----------+--------+------------------+------------------+
10 rows in set (0.00 sec)

Jendela w dipecah menjadi tiga partisi kategori, lalu tiap partisi diurutkan dari jumlah terkecil. Di kategori Kopi, pembeli terkecil adalah Siti (transaksi 1 unit) dan pembeli terbesar adalah Dewi (5 unit); di Teh, Rina membuka dengan 2 unit dan Siti menutup dengan 5 unit; di Gula, pembeli terkecil adalah Dewi (3 unit) dan pembeli terbesar adalah Andi (6 unit). Tanpa PARTITION BY, pembandingan akan terjadi lintas kategori dan makna bisnisnya hilang — nilai terkecil seluruh tabel akan menempel di semua partisi.

NTH_VALUE: Nilai pada Posisi ke-n #

NTH_VALUE(kolom, n) mengambil nilai pada posisi ke-n dalam frame — generalisasi dari FIRST_VALUE (n=1) dan LAST_VALUE. Sama seperti LAST_VALUE, fungsi ini terpengaruh frame default sehingga kita tulis frame penuh sejak awal:

SELECT id, pelanggan, jumlah,
       NTH_VALUE(jumlah, 3) OVER (
         ORDER BY jumlah DESC, id
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS nilai_ketiga
FROM penjualan
ORDER BY jumlah DESC, id;

Output yang Dihasilkan:

+----+-----------+--------+--------------+
| id | pelanggan | jumlah | nilai_ketiga |
+----+-----------+--------+--------------+
|  6 | Andi      |      6 |            5 |
|  5 | Siti      |      5 |            5 |
|  8 | Dewi      |      5 |            5 |
|  3 | Siti      |      4 |            5 |
|  2 | Budi      |      3 |            5 |
|  9 | Dewi      |      3 |            5 |
| 10 | Tono      |      3 |            5 |
|  1 | Budi      |      2 |            5 |
|  7 | Rina      |      2 |            5 |
|  4 | Siti      |      1 |            5 |
+----+-----------+--------+--------------+
10 rows in set (0.00 sec)

Jendela diurutkan dari transaksi terbesar — 6, 5, 5, 4, 3, 3, 3, 2, 2, 1 — sehingga nilai pada posisi ketiga adalah 5, dan kolom nilai_ketiga menampilkan 5 pada semua baris. Frame penuh membuat posisi dihitung dari seluruh jendela, bukan dari awal sampai baris sekarang. NTH_VALUE berguna untuk pertanyaan seperti “transaksi terbesar ketiga” atau “harga tawaran kedua tertinggi” tanpa perlu memfilter ROW_NUMBER() = 3 secara terpisah.

CUME_DIST dan PERCENT_RANK: Distribusi Kumulatif #

Dua fungsi ini mengukur posisi sebuah nilai dalam distribusi. CUME_DIST() menghitung proporsi baris bernilai lebih kecil atau sama dengan baris sekarang; PERCENT_RANK() menghitung peringkat relatif dengan rumus (peringkat − 1) / (jumlah baris − 1). Keduanya bekerja pada jendela yang hanya membutuhkan ORDER BY:

SELECT id, pelanggan, jumlah,
       CUME_DIST()     OVER (ORDER BY jumlah) AS cume,
       PERCENT_RANK()  OVER (ORDER BY jumlah) AS p_rank
FROM penjualan
ORDER BY jumlah, id;

Output yang Dihasilkan:

+----+-----------+--------+------+--------------------+
| id | pelanggan | jumlah | cume | p_rank             |
+----+-----------+--------+------+--------------------+
|  4 | Siti      |      1 |  0.1 |                  0 |
|  1 | Budi      |      2 |  0.3 | 0.1111111111111111 |
|  7 | Rina      |      2 |  0.3 | 0.1111111111111111 |
|  2 | Budi      |      3 |  0.6 | 0.3333333333333333 |
|  9 | Dewi      |      3 |  0.6 | 0.3333333333333333 |
| 10 | Tono      |      3 |  0.6 | 0.3333333333333333 |
|  3 | Siti      |      4 |  0.7 | 0.6666666666666666 |
|  5 | Siti      |      5 |  0.9 | 0.7777777777777778 |
|  8 | Dewi      |      5 |  0.9 | 0.7777777777777778 |
|  6 | Andi      |      6 |    1 |                  1 |
+----+-----------+--------+------+--------------------+
10 rows in set (0.00 sec)

Mari kita baca dua titik. Baris jumlah 1 (Siti, Kopi Gayo) menerima cume 0.1 — satu dari sepuluh baris bernilai ≤ 1 — dan p_rank 0 karena peringkatnya pertama. Dua baris bernilai 2 menerima cume 0.3 secara bersamaan: tiga baris bernilai ≤ 2 (satu baris bernilai 1 ditambah kelompok seri beranggota dua), dan nilai seri dihitung satu kelompok. Tiga baris bernilai 3 melompat ke cume 0.6: enam baris bernilai ≤ 3 sehingga proporsinya 6/10 = 0.6, sementara p_rank-nya sama-sama 0.33 karena RANK() ketiganya 4, lalu (4−1)/9 = 0,33. Perhatikan bahwa kedua fungsi memperlakukan kelompok seri sebagai satu unit: cume tidak pernah melompat di tengah kelompok seri, dan p_rank mengikuti pola celah RANK(). Kombinasi keduanya adalah bahan standar laporan “top 20% pelanggan menyumbang berapa persen penjualan”.

Window Function di Atas Hasil Agregasi #

Teknik bertingkat berikutnya menempelkan window function langsung pada hasil GROUP BY: agregat per kelompok dihitung dulu, lalu window function memproses baris-baris hasil agregasi itu. Contoh klasiknya adalah persentase kontribusi tiap kategori terhadap total keseluruhan:

SELECT kategori,
       SUM(jumlah) AS total_kategori,
       ROUND(100 * SUM(jumlah) / SUM(SUM(jumlah)) OVER (), 1) AS porsi_persen
FROM penjualan
GROUP BY kategori
ORDER BY total_kategori DESC;

Output yang Dihasilkan:

+----------+----------------+--------------+
| kategori | total_kategori | porsi_persen |
+----------+----------------+--------------+
| Kopi     |             15 |         44.1 |
| Teh      |             10 |         29.4 |
| Gula     |              9 |         26.5 |
+----------+----------------+--------------+
3 rows in set (0.00 sec)

Ekspresi SUM(SUM(jumlah)) OVER () memang terlihat aneh pada pertama kali. Bagian dalam SUM(jumlah) adalah agregat kelompok — dijalankan oleh GROUP BY kategori — menghasilkan 15 untuk Kopi, 10 untuk Teh, dan 9 untuk Gula. Bagian luar SUM(...) OVER () lalu menjumlahkan ketiga angka hasil agregasi menjadi 34 tanpa menggabungkan barisnya. Porsi kategori pun terhitung: Kopi 44,1%, Teh 29,4%, Gula 26,5%. Evaluasi window function selalu terjadi setelah pengelompokan, bukan sebelumnya — urutan logisnya GROUP BY dulu, jendela kemudian.

Penempatan kolom non-agregat pada pola ini punya syarat. SUM(SUM(jumlah)) OVER () menjadikan query ini query beragregasi, sehingga kolom di daftar SELECT harus lolos aturan only_full_group_by. Kita buktikan apa yang terjadi bila syarat itu dilanggar:

-- @error
SELECT kategori, SUM(SUM(jumlah)) OVER () FROM penjualan;

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'belajar_adv.penjualan.kategori'; this is incompatible with sql_mode=only_full_group_by
Bye

Server menolak dengan ERROR 1140: query beragregasi tanpa GROUP BY tidak boleh memuat kolom non-agregat kategori. Solusinya bukan mematikan mode ONLY_FULL_GROUP_BY, melainkan menulis dua tingkat secara eksplisit — agregasi di CTE atau derived table, window function di query luar — seperti pola CTE dan window function pada chapter sebelumnya.

Dua Jendela Berbeda dalam Satu Query #

Satu query boleh memasang beberapa spesifikasi jendela yang berbeda-beda, dan masing-masing dievaluasi sendiri-sendiri. Kita hitung dua total sekaligus untuk tiap transaksi: total unit pelanggan tersebut dan total unit kategorinya:

SELECT id, pelanggan, kategori, jumlah,
       SUM(jumlah) OVER (PARTITION BY pelanggan) AS total_pelanggan,
       SUM(jumlah) OVER (PARTITION BY kategori) AS total_kategori
FROM penjualan
ORDER BY id;

Output yang Dihasilkan:

+----+-----------+----------+--------+-----------------+----------------+
| id | pelanggan | kategori | jumlah | total_pelanggan | total_kategori |
+----+-----------+----------+--------+-----------------+----------------+
|  1 | Budi      | Kopi     |      2 |               5 |             15 |
|  2 | Budi      | Teh      |      3 |               5 |             10 |
|  3 | Siti      | Kopi     |      4 |              10 |             15 |
|  4 | Siti      | Kopi     |      1 |              10 |             15 |
|  5 | Siti      | Teh      |      5 |              10 |             10 |
|  6 | Andi      | Gula     |      6 |               6 |              9 |
|  7 | Rina      | Teh      |      2 |               2 |             10 |
|  8 | Dewi      | Kopi     |      5 |               8 |             15 |
|  9 | Dewi      | Gula     |      3 |               8 |              9 |
| 10 | Tono      | Kopi     |      3 |               3 |             15 |
+----+-----------+----------+--------+-----------------+----------------+
10 rows in set (0.00 sec)

Kolom total_pelanggan memakai jendela per pelanggan sedangkan total_kategori memakai jendela per kategori — dua partisi yang berpotongan namun bekerja independen. Baris id 1 (Budi, Kopi, 2) menerima total_pelanggan 5 (Budi membeli 2+3) dan total_kategori 15 (seluruh Kopi). Tidak ada batasan jumlah jendela berbeda dalam satu query; yang perlu dijaga hanyalah keterbacaan. Bila beberapa kolom memakai spesifikasi jendela yang sama persis, klausa WINDOW w AS (...) menghindari pengetikan ulang; bila setiap kolom berjendela sendiri, biarkan masing-masing ditulis penuh agar pembaca tidak perlu melompat ke definisi.

Klausa FILTER: Tidak Ada di MySQL #

Standar SQL:2003 mendefinisikan klausa FILTER (WHERE ...) untuk agregat bersyarat — SUM(jumlah) FILTER (WHERE kategori = 'Kopi') — yang banyak diimplementasikan database lain. MySQL 8.4 tidak memilikinya. Kita uji sendiri:

-- @error
SELECT SUM(jumlah) FILTER (WHERE kategori = 'Kopi') FROM penjualan;

Output yang Dihasilkan:

Query OK, 0 rows affected (0.00 sec)

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(WHERE kategori = 'Kopi') FROM penjualan' at line 1
Bye

Server menolak dengan ERROR 1064 — klausa FILTER bukan sintaks yang dikenal, jadi parser langsung gagal. Alternatif standar di MySQL adalah agregasi bersyarat dengan CASE WHEN: nilai yang tidak lolos syarat diubah menjadi nol (atau NULL) sehingga tidak memengaruhi hasil agregat. Pola ini bekerja untuk SUM, COUNT, AVG, termasuk versi berjendelanya:

SELECT pelanggan,
       SUM(jumlah) AS total_semua,
       SUM(CASE WHEN kategori = 'Kopi' THEN jumlah ELSE 0 END) AS total_kopi
FROM penjualan
GROUP BY pelanggan
ORDER BY total_semua DESC, pelanggan;

Output yang Dihasilkan:

+-----------+-------------+------------+
| pelanggan | total_semua | total_kopi |
+-----------+-------------+------------+
| Siti      |          10 |          5 |
| Dewi      |           8 |          5 |
| Andi      |           6 |          0 |
| Budi      |           5 |          2 |
| Tono      |           3 |          3 |
| Rina      |           2 |          0 |
+-----------+-------------+------------+
6 rows in set (0.00 sec)

SELECT id, pelanggan, jumlah,
       SUM(CASE WHEN kategori = 'Kopi' THEN jumlah ELSE 0 END)
         OVER (PARTITION BY pelanggan) AS kopi_per_pelanggan
FROM penjualan
ORDER BY id;

Output yang Dihasilkan:

+----+-----------+--------+--------------------+
| id | pelanggan | jumlah | kopi_per_pelanggan |
+----+-----------+--------+--------------------+
|  1 | Budi      |      2 |                  2 |
|  2 | Budi      |      3 |                  2 |
|  3 | Siti      |      4 |                  5 |
|  4 | Siti      |      1 |                  5 |
|  5 | Siti      |      5 |                  5 |
|  6 | Andi      |      6 |                  0 |
|  7 | Rina      |      2 |                  0 |
|  8 | Dewi      |      5 |                  5 |
|  9 | Dewi      |      3 |                  5 |
| 10 | Tono      |      3 |                  3 |
+----+-----------+--------+--------------------+
10 rows in set (0.00 sec)

Query pertama memakai agregasi bersyarat klasik bersama GROUP BY: kolom total_kopi berisi SUM yang hanya menghitung baris Kopi, berdampingan dengan total_semua yang menghitung semua baris — Siti 5, Dewi 5, Tono 3, Budi 2, sedangkan Andi dan Rina 0. Query kedua memasang ekspresi CASE yang sama ke dalam window function berpartisi pelanggan sehingga tiap baris mentah membawa total Kopi pelanggannya. Untuk COUNT, gunakan COUNT(CASE WHEN ... THEN 1 END) tanpa cabang ELSE karena COUNT mengabaikan NULL; untuk AVG, ELSE NULL mencegah baris non-kopi menarik rata-rata ke bawah. Ketiga varian itu menggantikan hampir semua kebutuhan klausa FILTER.

Membersihkan Database Latihan #

Rangkaian latihan selesai dan database latihan kita jatuhkan kembali:

DROP DATABASE belajar_adv;

Output yang Dihasilkan:

Query OK, 1 row affected (0.00 sec)

Referensi #

• Window Function Descriptions — MySQL 8.4 Reference Manual
• Window Function Concepts and Syntax — MySQL 8.4 Reference Manual
• Window Frames — MySQL 8.4 Reference Manual

Perangkat jendela kita kini lengkap dari bucket (NTILE), nilai tepi (FIRST_VALUE, LAST_VALUE, NTH_VALUE), distribusi (CUME_DIST, PERCENT_RANK), hingga agregasi bertingkat dan multi-jendela. Satu kekuatan rekayasa query masih belum tersentuh: kemampuan query yang memanggil dirinya sendiri. Chapter recursive-queries membongkar WITH RECURSIVE lebih dalam — penelusuran hierarki dua arah, pembangkitan kalender, deret Fibonacci, dan perbandingannya dengan rekursi di sisi aplikasi.