KAL
Menu

Esensial Query

SELECT, WHERE, dan GROUP BY pada basis data yang lebih besar dari memori.

Daftar isi

Data yang lebih besar dari memori tidak dapat ditarik utuh ke pandas, dan di titik itu pekerjaan berpindah ke SQL, yaitu meminta server menghitung dan mengirim hasilnya yang sudah ringkas. Topik ini membangun database latihan analitik dengan satu tabel fakta dan satu dimensi, lalu berlatih SELECT, WHERE, GROUP BY, dan HAVING di atasnya. Setelah membacanya, pertanyaan agregasi yang biasa dijawab groupby dapat dijawab langsung di sisi basis data.

Menyiapkan database latihan#

Database latihan dibuat ulang setiap kali dijalankan agar hasilnya selalu sama, dengan pola drop lalu create yang sama seperti modul akses SQL dari Python. Tabel dim_stasiun menyimpan metadata, sedangkan fakta_ukur menyimpan pengukuran per jam dengan kunci asing ke dimensinya.

bash 
# siapkan database latihan analitik (dapat diulang dengan aman)
psql -h /home/kal/my-project/pgsock -U postgres -c "DROP DATABASE IF EXISTS analitik"
psql -h /home/kal/my-project/pgsock -U postgres -c "CREATE DATABASE analitik"
psql -h /home/kal/my-project/pgsock -U postgres -d analitik -c "CREATE TABLE dim_stasiun (id_stasiun serial PRIMARY KEY, nama text NOT NULL, wilayah text NOT NULL)"
psql -h /home/kal/my-project/pgsock -U postgres -d analitik -c "INSERT INTO dim_stasiun (nama, wilayah) VALUES ('Stasiun A','Kawasan Sekolah'), ('Stasiun B','Jalan Utama'), ('Stasiun C','Taman Kota')"
psql -h /home/kal/my-project/pgsock -U postgres -d analitik -c "CREATE TABLE fakta_ukur (id serial PRIMARY KEY, waktu timestamp NOT NULL, id_stasiun integer REFERENCES dim_stasiun, pm25 numeric NOT NULL, suhu_c numeric, kelembapan numeric)"
psql -h /home/kal/my-project/pgsock -U postgres -d analitik -c "INSERT INTO fakta_ukur (waktu, id_stasiun, pm25, suhu_c, kelembapan) VALUES ('2026-03-01 07:00',1,38.2,25.6,71.0), ('2026-03-01 08:00',1,44.9,26.8,69.4), ('2026-03-01 09:00',1,41.3,27.9,68.7), ('2026-03-01 07:00',2,58.7,24.9,72.3), ('2026-03-01 08:00',2,66.1,25.8,70.9), ('2026-03-01 09:00',2,61.4,26.7,71.5)"
text Keluaran 
DROP DATABASE
CREATE DATABASE
CREATE TABLE
INSERT 0 3
CREATE TABLE
INSERT 0 6

Enam baris pengukuran memang kecil, tetapi pola query di bawah ini tidak berubah pada jutaan baris, dan itulah tujuan latihannya. Opsi -h pada psql menunjuk soket lingkungan latihan, dan lingkungan lain cukup menggantinya dengan host miliknya.

SELECT dan WHERE: memilih kolom dan baris#

SELECT menyatakan kolom yang diinginkan, WHERE menyaring baris sebelum apapun dihitung, ORDER BY mengurutkan, dan LIMIT membatasi jumlah baris yang dikirim. Empat klausa itu sudah cukup untuk sebagian besar pertanyaan pemeriksaan cepat, misalnya tiga pengukuran tertinggi yang melewati 50.

bash 
# tiga pengukuran tertinggi di atas 50
psql -h /home/kal/my-project/pgsock -U postgres -d analitik -c "SELECT waktu, id_stasiun, pm25 FROM fakta_ukur WHERE pm25 > 50 ORDER BY pm25 DESC LIMIT 3"
text Keluaran 
        waktu        | id_stasiun | pm25 
---------------------+------------+------
 2026-03-01 08:00:00 |          2 | 66.1
 2026-03-01 09:00:00 |          2 | 61.4
 2026-03-01 07:00:00 |          2 | 58.7
(3 rows)

Sintaks * yang memilih seluruh kolom nyaman untuk pemeriksaan, tetapi query yang dipakai ulang menyebut kolomnya secara eksplisit, sebab kolom baru yang ditambahkan tabel di kemudian hari tidak mengubah hasilnya. Saringan WHERE dijalankan server sebelum data dikirim, dan pada tabel berjuta baris perbedaannya adalah jutaan baris yang tidak pernah meninggalkan server.

GROUP BY dan HAVING: agregasi dengan syarat#

GROUP BY meringkas baris menjadi kelompok, dan fungsi agregat seperti count, avg, serta max menghitung satu nilai per kelompok, persis dengan pola split-apply-combine pada groupby pandas. HAVING menyaring hasil agregasinya, sementara WHERE menyaring baris sebelum agregasi, dan urutan itulah yang membedakan keduanya.

bash 
# ringkasan per stasiun
psql -h /home/kal/my-project/pgsock -U postgres -d analitik -c "SELECT id_stasiun, count(*) AS jumlah, round(avg(pm25),1) AS pm25_rata, max(pm25) AS pm25_maks FROM fakta_ukur GROUP BY id_stasiun ORDER BY id_stasiun"
psql -h /home/kal/my-project/pgsock -U postgres -d analitik -c "SELECT id_stasiun, round(avg(pm25),1) AS pm25_rata FROM fakta_ukur GROUP BY id_stasiun HAVING avg(pm25) > 50"
text Keluaran 
 id_stasiun | jumlah | pm25_rata | pm25_maks 
------------+--------+-----------+-----------
          1 |      3 |      41.5 |      44.9
          2 |      3 |      62.1 |      66.1
(2 rows)

 id_stasiun | pm25_rata 
------------+-----------
          2 |      62.1
(1 row)

Angkanya terbaca sama seperti groupby pandas pada modul perkakas, yaitu tiga pengukuran per stasiun dengan rata-rata 41.5 berbanding 62.1, dan HAVING menyaring kelompok yang rata-ratanya di atas 50 sehingga hanya Stasiun B lolos. Alias yang didefinisikan pada SELECT dapat dipakai ORDER BY, tetapi HAVING pada PostgreSQL membaca ekspresi aslinya, sehingga HAVING avg(pm25) > 50 ditulis utuh.

Kapan komputasi pindah ke SQL#

Aturan pembagian kerjanya sama dengan modul akses SQL, yaitu saringan dan agregasi ditekan ke server, sedangkan pekerjaan yang membutuhkan seluruh baris ditarik ke pandas. Latihan topik ini melengkapinya dengan kebiasaan membaca hasil sebagai tabel: count yang menyertai setiap baris ringkasan adalah bobot kepercayaannya, dan agregat tanpa jumlah data meminta dikecualikan dari kesimpulan.

Query yang sama diulang pada tabel yang tumbuh tanpa mengubah pola, dan pada praktiknya satu query yang baik menggantikan skrip pemuatan berjuta baris yang lambat. Modul berikutnya melengkapi kemampuan ini dengan join antar tabel beserta pembacaan model datanya.

Kesalahan umum#

Kesalahan pertama adalah menaruh syarat agregat pada WHERE. PostgreSQL menolaknya dengan pesan yang menyebut kolom tidak ada, karena agregasi belum terjadi pada tahap itu, dan syarat tingkat kelompok ditaruh pada HAVING.

Kesalahan kedua adalah menyebut kolom non-agregat di luar GROUP BY. Aturan ini menegakkan kejelasan granularitas, karena satu baris per kelompok tidak dapat memuat nilai yang berbeda antar anggotanya, dan pesan errornya menunjuk kolom yang bermasalah.

Kesalahan ketiga adalah memakai SELECT * pada query yang dipakai ulang. Tabel yang bertambah kolomnya mengubah hasil tanpa mengubah query, dan penyebutan kolom eksplisit menjaga hasilnya tetap stabil.

Ringkasan#

  • Database latihan dibuat ulang dengan pola drop lalu create sehingga setiap jalannya menghasilkan angka yang sama.
  • SELECT, WHERE, ORDER BY, dan LIMIT menyaring serta mengirim sedikit data, dan penyebutan kolom eksplisit menjaga hasil tetap stabil.
  • GROUP BY dengan fungsi agregat meringkas per kelompok, WHERE menyaring baris sebelum agregasi, dan HAVING menyaring setelahnya.
  • Saringan dan agregasi ditekan ke server agar data yang melewati jaringan sekecil mungkin.

Referensi#