HAVING

Topik ini membahas klausa HAVING untuk menyaring hasil pengelompokan setelah fungsi agregat dihitung. Pembahasan mencakup posisi WHERE yang bekerja sebelum agregasi, peran HAVING yang bekerja sesudahnya, pola pemakaian keduanya dalam satu query, dan batasan masing-masing. Basis data latihan berupa catatan tiket loket dibangun ulang dari berkas skrip, sehingga seluruh keluaran pada contoh dapat diulang dari data yang sama.

Menyiapkan basis data latihan#

Basis data latihan tiket_loket dibangun ulang dari berkas skrip agar seluruh contoh pada topik ini bermula dari data yang sama. Berkas data-tiket-loket.sql memuat pembuatan ulang basis data, tabel tiket, dan tiga belas baris data. Setiap baris mewakili satu tiket dengan kolom loket berisi L1 sampai L4, kolom jenis berisi dewasa atau anak, serta kolom harga berisi 15000 untuk tiket dewasa dan 8000 untuk tiket anak. Sebarannya sengaja dibuat tidak merata, yaitu lima tiket pada L1, empat pada L2, tiga pada L3, dan satu pada L4, agar perbedaan hasil penyaringan langsung terlihat.

-- tidak-dijalankan
-- Berkas latihan topik HAVING: membangun ulang basis data tiket_loket dari nol.
-- Jalankan dengan: mysql -u root < data-tiket-loket.sql

DROP DATABASE IF EXISTS tiket_loket;
CREATE DATABASE tiket_loket;
USE tiket_loket;

CREATE TABLE tiket (
  id INT PRIMARY KEY,
  loket VARCHAR(2) NOT NULL,
  jenis VARCHAR(10) NOT NULL,
  harga INT UNSIGNED NOT NULL
);

INSERT INTO tiket (id, loket, jenis, harga) VALUES
  (1,  'L1', 'dewasa', 15000),
  (2,  'L1', 'dewasa', 15000),
  (3,  'L1', 'anak',   8000),
  (4,  'L1', 'dewasa', 15000),
  (5,  'L1', 'anak',   8000),
  (6,  'L2', 'dewasa', 15000),
  (7,  'L2', 'dewasa', 15000),
  (8,  'L2', 'dewasa', 15000),
  (9,  'L2', 'dewasa', 15000),
  (10, 'L3', 'anak',   8000),
  (11, 'L3', 'anak',   8000),
  (12, 'L3', 'dewasa', 15000),
  (13, 'L4', 'anak',   8000);

# membangun ulang basis data latihan dari berkas skrip
mysql -u root < data-tiket-loket.sql

Perintah di atas tidak mencetak apa pun karena skripnya hanya berisi pernyataan pembuatan struktur dan pengisian data. Tabel tiket kini siap dipakai untuk mengamati urutan kerja WHERE dan HAVING pada query pengelompokan. Seluruh pernyataan pada berkas bersifat pembangunan ulang, sehingga latihan dapat diulang kapan pun dengan keadaan data yang identik. Pembahasan tentang tipe data dan pembuatan tabel memiliki topik tersendiri di jalur ini, sehingga bagian ini langsung memanfaatkan data yang tersedia.

WHERE menyaring baris sebelum agregasi#

Klausa WHERE memilih baris yang ikut dihitung sebelum pengelompokan terjadi. Baris yang gagal memenuhi syaratnya tidak pernah mencapai tahap pengelompokan, sehingga seluruh fungsi agregat hanya bekerja atas baris yang lolos. Query berikut membatasi sumber data pada dua loket, lalu merangkum banyaknya tiket dan total harga per jenis.

# merangkum tiket dua loket dengan saringan WHERE
mysql -t -u root tiket_loket -e "SELECT jenis, COUNT(*) AS jumlah, SUM(harga) AS total FROM tiket WHERE loket IN ('L1','L2') GROUP BY jenis ORDER BY jenis;"

+--------+--------+--------+
| jenis  | jumlah | total  |
+--------+--------+--------+
| anak   |      2 |  16000 |
| dewasa |      7 | 105000 |
+--------+--------+--------+

Keluaran memuat dua jenis tiket dari dua loket saja. Tiket anak dihitung 2 karena hanya L1 yang menjualnya di antara kedua loket tersebut, sedangkan tiket dewasa dihitung 7 dari gabungan L1 dan L2. Urutan kerja querynya berjalan bertahap, yaitu baris dikumpulkan dari FROM, disaring oleh WHERE, dikelompokkan oleh GROUP BY, lalu dirangkum pada daftar SELECT. Apabila saringan dua loket itu dilepas, jumlah tiket dewasa bertambah dari baris L3, dan itulah perubahan yang dihasilkan satu klausa WHERE. Karena penyaringan terjadi sebelum pengelompokan, syarat pada WHERE tidak dapat merujuk hasil fungsi agregat apa pun, dan angka kelompok selalu dibaca setelah saringan barisnya ditetapkan.

HAVING menyaring kelompok setelah agregasi#

Klausa HAVING menyaring kelompok hasil GROUP BY berdasarkan nilai fungsi agregatnya. Baris keluaran yang gagal memenuhi syaratnya dibuang utuh beserta angka agregatnya. Query berikut menghitung rekap per loket terlebih dahulu, lalu menyisakan loket dengan total harga minimal 30000.

# menyisakan loket dengan total harga minimal 30000
mysql -t -u root tiket_loket -e "SELECT loket, COUNT(*) AS jumlah, SUM(harga) AS total FROM tiket GROUP BY loket HAVING SUM(harga) >= 30000 ORDER BY loket;"

+-------+--------+-------+
| loket | jumlah | total |
+-------+--------+-------+
| L1    |      5 | 61000 |
| L2    |      4 | 60000 |
| L3    |      3 | 31000 |
+-------+--------+-------+

Loket L4 hilang dari keluaran karena total harganya hanya 8000, padahal kelompoknya tetap terbentuk sebelum disaring. Syarat SUM(harga) >= 30000 merujuk fungsi agregat yang tidak mungkin ditulis pada WHERE, dan itulah fungsi utama HAVING. Kedua klausa juga dapat bekerja bersama dalam satu query, sebagaimana contoh berikut yang hanya melihat tiket dewasa, lalu menyisakan loket dengan minimal dua tiket jenis itu.

# menggabungkan saringan baris dan saringan kelompok
mysql -t -u root tiket_loket -e "SELECT loket, COUNT(*) AS dewasa, SUM(harga) AS total FROM tiket WHERE jenis = 'dewasa' GROUP BY loket HAVING COUNT(*) >= 2 ORDER BY loket;"

+-------+--------+-------+
| loket | dewasa | total |
+-------+--------+-------+
| L1    |      3 | 45000 |
| L2    |      4 | 60000 |
+-------+--------+-------+

Pada query ini WHERE jenis = 'dewasa' mengecilkan bahan sebelum pengelompokan, sehingga kelompok L3 hanya memuat satu tiket dewasa dan kelompok L4 kosong. HAVING COUNT(*) >= 2 kemudian membuang kelompok yang kecil, dan keluaran hanya menyisakan L1 dengan 45000 serta L2 dengan 60000. Dua saringan yang berbeda tahap inilah yang membedakan WHERE dan HAVING.

Perbedaan WHERE dan HAVING#

Perbedaan kedua klausa tersebut terletak pada tahap evaluasi dan objek saringannya. Tabel berikut merangkum perbedaannya dalam satu tempat.

Aturan fungsi agregat pada tabel itu ditegakkan server secara langsung. Query berikut mencoba menyaring baris dengan COUNT(*) pada WHERE, dan server menolaknya karena agregat belum ada ketika saringan baris dievaluasi.

# sengaja menempatkan fungsi agregat pada WHERE
mysql -t -u root tiket_loket -e "SELECT loket, COUNT(*) FROM tiket WHERE COUNT(*) > 2 GROUP BY loket;"

ERROR 1111 (HY000) at line 1: Invalid use of group function

Pesan ERROR 1111 muncul karena COUNT(*) hanya bermakna setelah kelompok terbentuk, sedangkan WHERE dievaluasi lebih awal. Perbaikannya adalah memindahkan syarat itu ke HAVING, yaitu HAVING COUNT(*) > 2, sehingga query berjalan pada tahap yang tepat.

Alias agregat pada HAVING#

MySQL memperbolehkan HAVING merujuk alias yang dideklarasikan pada daftar SELECT. Penulisan ini lebih ringkas untuk syarat agregat yang panjang, dan maknanya sama persis dengan menuliskan fungsi agregatnya kembali. Query berikut menyaring loket berdasarkan alias jumlah yang didefinisikan sebagai COUNT(*).

# menyaring kelompok melalui alias pada daftar SELECT
mysql -t -u root tiket_loket -e "SELECT loket, COUNT(*) AS jumlah FROM tiket GROUP BY loket HAVING jumlah >= 3 ORDER BY loket;"

+-------+--------+
| loket | jumlah |
+-------+--------+
| L1    |      5 |
| L2    |      4 |
| L3    |      3 |
+-------+--------+

Keluaran menyisakan L1, L2, dan L3 karena ketiganya memuat minimal tiga tiket, sedangkan L4 hanya memuat satu. Query di atas identik dengan HAVING COUNT(*) >= 3 karena alias jumlah didefinisikan sebagai COUNT(*). Server menyelesaikan rujukan alias itu pada tahap yang sama, yaitu sesudah kelompok terbentuk dan angka agregatnya dihitung, sehingga tidak ada perbedaan hasil maupun urutan evaluasi yang perlu dikhawatirkan. Pada query yang panjang, alias juga mengurangi duplikasi ekspresi agregat yang sama.

Meskipun sah pada MySQL, pola ini perlu dipakai dengan sadar karena sebagian mesin basis data menolaknya. Apabila konsistensi lintas mesin lebih penting daripada keringkasan, tulis ulang syarat agregat dalam bentuk HAVING COUNT(*) >= 3. Perlu diingat pula bahwa alias tidak dapat dirujuk oleh WHERE, karena saringan baris dievaluasi lebih dahulu.

HAVING tanpa GROUP BY#

Query agregat tanpa GROUP BY memperlakukan seluruh tabel sebagai satu kelompok besar. Pada bentuk itu, HAVING tetap berlaku dan menyaring kelompok tunggal tersebut berdasarkan angka agregat globalnya. Query berikut menghitung rekap seluruh tabel, lalu menampilkan hasilnya hanya apabila kelompok itu tidak kosong.

# menerapkan HAVING pada query agregat tanpa GROUP BY
mysql -t -u root tiket_loket -e "SELECT COUNT(*) AS jumlah, SUM(harga) AS total FROM tiket HAVING COUNT(*) > 0;"

+--------+--------+
| jumlah | total  |
+--------+--------+
|     13 | 160000 |
+--------+--------+

Keluaran tetap berupa satu baris berisi 13 tiket dengan total harga 160000. Tanpa HAVING, query semacam ini selalu mengembalikan satu baris meskipun tabelnya kosong, karena agregat atas himpunan kosong masih menghasilkan nilai seperti nol. Dengan HAVING COUNT(*) > 0, baris keluaran itu ditekan apabila tidak ada baris yang terhitung, dan pola ini berguna untuk syarat ambang global sebelum hasil ditampilkan. Pola yang sama bekerja untuk fungsi agregat lain, misalnya HAVING SUM(harga) > 0, dengan perilaku yang tetap pada satu kelompok tunggal itu. Bentuk kelompok tunggal ini memperlihatkan kembali definisi HAVING, yaitu syarat atas kelompok, dan tabel penuh hanyalah kasus kelompoknya satu. Perbedaannya dengan HAVING per kelompok hanyalah banyaknya kelompok yang dievaluasi, bukan bentuk syaratnya.

Kesalahan umum#

Kesalahan pertama adalah menempatkan fungsi agregat pada WHERE. Server menolaknya dengan ERROR 1111 karena agregat belum terbentuk pada tahap saringan baris, dan pesan errornya konsisten sehingga mudah dikenali setelah beberapa kali ditemui. Pindahkan syarat agregat ke HAVING, sedangkan syarat yang hanya melibatkan kolom biasa tetap ditaruh pada WHERE.

Kesalahan kedua adalah memakai HAVING untuk syarat yang tidak melibatkan agregat, misalnya HAVING jenis = 'dewasa'. Server tetap harus membentuk seluruh kelompok terlebih dahulu, baru menyaringnya, sehingga pekerjaan menjadi lebih berat daripada menyaring baris di awal. Penghematan itu terlihat jelas pada tabel besar, karena jumlah kelompok jauh lebih kecil daripada jumlah barisnya. Satu kelompok yang dibuang setelah agregasi berarti banyak baris yang telah dihitung tanpa hasilnya terpakai. Syarat semacam itu berpindah ke WHERE tanpa mengubah hasilnya.

Kesalahan ketiga adalah mengira HAVING menyaring baris mentah. Hasilnya selalu berupa kelompok, sehingga satu baris keluaran mewakili banyak baris data. Pemahaman yang tepat adalah melihat HAVING sebagai penyaring laporan rekap, sedangkan WHERE tetap penyaring laporan detail. Apabila detail per baris yang diinginkan, gunakan WHERE tanpa pengelompokan, dan simpan HAVING untuk kebutuhan rekap.

Ringkasan#

• WHERE menyaring baris sebelum pengelompokan dan tidak dapat merujuk fungsi agregat.
• HAVING menyaring kelompok sesudah agregasi dan boleh merujuk fungsi agregat atau alias pada daftar SELECT.
• Kedua klausa dapat dipakai bersama, yaitu WHERE mengecilkan bahan dan HAVING menyeleksi kelompok.
• Query agregat tanpa GROUP BY memperlakukan seluruh tabel sebagai satu kelompok, dan HAVING tetap berlaku atas kelompok tunggal itu.

Referensi#

• SELECT Statement — Oracle — Dokumentasi pernyataan SELECT, termasuk urutan klausa WHERE, GROUP BY, dan HAVING.
• MySQL Handling of GROUP BY — Oracle — Aturan pengelompokan MySQL beserta perbedaan penanganan WHERE dan HAVING.
• Aggregate (GROUP BY) Function Descriptions — Oracle — Daftar fungsi agregat yang sah dirujuk pada klausa HAVING.