KAL
Menu

Join dan Model Data

Membaca model data, kunci, dan star schema sebelum menulis join.

Daftar isi

Join yang benar dimulai sebelum query ditulis, yaitu membaca model data untuk memahami tabel mana yang memuat apa dan kunci mana yang menghubungkannya. Topik ini menambahkan konteks model data pada database latihan analitik, memperlihatkan pola bintang yang melandasinya, dan menjalankan join serta anti-join pada skema tersebut. Setelah membacanya, query lintas tabel ditulis dengan keyakinan atas granularitas hasilnya, bukan dengan percobaan sampai angkanya terlihat masuk akal.

Membaca model data sebelum menulis query#

Model data menjawab tiga pertanyaan, yaitu tabel apa yang ada, apa yang diwakili satu barisnya, dan kunci mana yang menghubungkannya. Pada basis data produksi, pertanyaan itu dijawab dari katalognya, misalnya perintah \d nama_tabel pada psql yang menampilkan kolom, tipe, dan kunci asingnya, serta percakapan singkat dengan pemilik datanya.

Kunci primer (primary key) mengidentifikasi satu baris secara unik, dan kunci asing (foreign key) menunjuk ke kunci primer tabel lain. Pada database latihan ini, dim_stasiun.id_stasiun adalah kunci primer, sedangkan fakta_ukur.id_stasiun adalah kunci asingnya, dan pasangan inilah yang menjadi landasan setiap join.

Granularitas ditanyakan dengan kalimat yang sama seperti pada modul jenis dan bentuk data, yaitu satu baris fakta mewakili satu pengukuran pada satu jam untuk satu stasiun. Kalimat itu yang menentukan bahwa agregasi per stasiun sah, sedangkan penjumlahan antar jam perlu dipikirkan maknanya.

Pola bintang yang melandasinya#

Pola yang mendasari banyak gudang data adalah star schema, yaitu satu tabel fakta yang berada di tengah dengan tabel dimensi yang mengelilinginya, masing-masing terhubung lewat kunci asingnya. Diagram berikut memetakan skema database latihan beserta perluasan wajarnya pada praktik.

fakta_ukurwaktu, pm25, suhu_c,kelembapan, id_stasiundim_stasiunid_stasiun, nama, wilayahdim_waktutanggal, jam, haridim_sensorjenis, tahun_operasianalisisquery agregasi per dimensi
Pola star schema pada database latihan analitik

Keunggulan pola ini adalah pemisahan yang jelas, yaitu tabel fakta yang tumbuh besar hanya menyimpan angka dan kunci, sedangkan nama panjang dan metadata tinggal di dimensi yang kecil. Query agregasinya pun seragam, yaitu angka dari fakta dipotong menurut kolom dari dimensi, dan pemangkas yang sama berlaku dari data pengukuran sampai data transaksi.

Baris Stasiun C pada dimensi yang belum memiliki pengukuran bukan keanehan, melainkan keadaan normal pada model ini, dan bagian anti-join di bawah memanfaatkannya sebagai latihan.

Join dan granularitas hasil#

Inner join memasangkan baris yang kuncinya bertemu di kedua sisi, dan hasil join satu ke banyak tetap bergranularitas fakta, yaitu satu baris pengukuran dengan kolom metadata yang menempel. Aturan yang menyertai adalah jumlah baris hasil dapat diperiksa, dan pada fakta yang granularitasnya jam, penggandaan terjadi bila kunci di sisi dimensi tidak unik.

bash 
# siapkan ulang database latihan agar topik dapat dijalankan sendiri
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
bash 
# inner join: pengukuran dengan nama stasiunnya
psql -h /home/kal/my-project/pgsock -U postgres -d analitik -c "SELECT f.waktu, s.nama, f.pm25 FROM fakta_ukur f JOIN dim_stasiun s ON f.id_stasiun = s.id_stasiun ORDER BY f.waktu, s.nama LIMIT 4"
text Keluaran 
        waktu        |   nama    | pm25 
---------------------+-----------+------
 2026-03-01 07:00:00 | Stasiun A | 38.2
 2026-03-01 07:00:00 | Stasiun B | 58.7
 2026-03-01 08:00:00 | Stasiun A | 44.9
 2026-03-01 08:00:00 | Stasiun B | 66.1
(4 rows)

Alias satu huruf pada kedua tabel menjaga query tetap terbaca, dan urutan kolom hasilnya ditentukan SELECT, bukan urutan tabel pada FROM. Agregasi di atas hasil join pun aman dari penggandaan karena id_stasiun unik pada dimensinya, dan pemeriksaan keunikannya dapat ditegaskan dengan constraint UNIQUE.

Anti-join: menemukan yang tidak berpasangan#

LEFT JOIN mempertahankan seluruh baris sisi kiri dan mengisi kosong dengan NULL untuk baris yang tidak bertemu pasangannya, dan agregasi count atas kolom sisi kanan menghasilkan daftar dimensi yang tidak punya fakta, yang disebut anti-join.

bash 
# anti-join: dimensi tanpa pengukuran
psql -h /home/kal/my-project/pgsock -U postgres -d analitik -c "SELECT s.nama, count(f.id) AS jumlah_fakta FROM dim_stasiun s LEFT JOIN fakta_ukur f ON f.id_stasiun = s.id_stasiun GROUP BY s.nama ORDER BY s.nama"
text Keluaran 
   nama    | jumlah_fakta 
-----------+--------------
 Stasiun A |            3
 Stasiun B |            3
 Stasiun C |            0
(3 rows)

Stasiun C muncul dengan nol fakta, dan pola count(f.id) yang menghitung kolom tabel kanan adalah pembedanya, karena count(*) akan menghitung baris kosong itu sebagai satu. Anti-join semacam ini adalah pemeriksaan rutin integritas antar sumber, sejalan dengan audit baris tanpa pasangan pada merge pandas, dan hasilnya berupa temuan yang diputuskan, misalnya stasiun baru yang sensor sedang dipasang.

Kesalahan umum#

Kesalahan pertama adalah join tanpa membaca granularitas kedua tabel. Tabel dimensi yang kuncinya tidak unik menggandakan fakta, dan agregasi setelahnya dobel diam-diam, sedangkan pemeriksaan UNIQUE pada dimensi menutupnya sejak awal.

Kesalahan kedua adalah menghitung count(*) pada LEFT JOIN yang hendak mendeteksi kekosongan. Baris tanpa pasangan tetap berjumlah satu, dan count(kolom_tabel_kanan) yang bernilai nol adalah sinyal yang benar.

Kesalahan ketiga adalah menulis join berantai tanpa memeriksa jumlah baris antar tahap. Setiap join mengubah granularitas, dan kebiasaan membandingkan count sebelum dan sesudahnya menjaga hasil tetap dapat dipertanggungjawabkan.

Ringkasan#

  • Model data dibaca lebih dahulu, dari kunci primer, kunci asing, sampai granularitas satu barisnya.
  • Star schema menaruh angka pada fakta dan metadata pada dimensi, dan query agregasinya memotong angka menurut kolom dimensi.
  • Inner join menjaga granularitas fakta bila kunci dimensi unik, dan jumlah barisnya diperiksa antar tahap.
  • LEFT JOIN dengan count atas kolom kanan menjadi anti-join yang menemukan dimensi tanpa fakta.

Referensi#