KAL
Menu

Agregasi string dan tanggal

Menyusun daftar teks dan deret waktu dengan string_agg, array_agg, dan date_trunc.

Daftar isi

Topik ini menutup modul agregasi dengan agregasi yang hasilnya bukan angka, yaitu string_agg yang merangkai teks, array_agg yang menyusun array, dan date_trunc yang memotong waktu menjadi kelompok periode. Ketiganya menjawab kebutuhan laporan yang lazim, mulai dari daftar judul per kategori sampai tren bulanan.

Blok penyiapan dataset#

bash 
# siapkan ulang tabel dan data contoh untuk topik ini
PGOPTIONS='-c client_min_messages=warning' psql -q -U postgres -d perpustakaan <<'SQL'
DROP TABLE IF EXISTS peminjaman, buku, anggota, kategori CASCADE;
CREATE TABLE kategori (id SERIAL PRIMARY KEY, nama TEXT NOT NULL UNIQUE);
CREATE TABLE anggota (id SERIAL PRIMARY KEY, nama TEXT NOT NULL, email TEXT UNIQUE, bergabung DATE NOT NULL, aktif BOOLEAN NOT NULL DEFAULT TRUE);
CREATE TABLE buku (id SERIAL PRIMARY KEY, judul TEXT NOT NULL, penulis TEXT NOT NULL, tahun INTEGER NOT NULL, kategori_id INTEGER REFERENCES kategori(id));
CREATE TABLE peminjaman (id SERIAL PRIMARY KEY, anggota_id INTEGER NOT NULL REFERENCES anggota(id), buku_id INTEGER NOT NULL REFERENCES buku(id), tanggal_pinjam DATE NOT NULL, tanggal_kembali DATE);
INSERT INTO kategori (nama) VALUES ('Pemrograman'), ('Sains'), ('Sejarah'), ('Fiksi'), ('Statistika');
INSERT INTO anggota (nama, email, bergabung, aktif) VALUES ('Ali Rahman', '[email protected]', '2024-01-15', TRUE), ('Budi Santoso', '[email protected]', '2024-03-02', TRUE), ('Citra Kirana', '[email protected]', '2024-05-20', FALSE), ('Dewi Lestari', '[email protected]', '2024-08-11', TRUE), ('Eka Pratama', NULL, '2025-01-30', TRUE);
INSERT INTO buku (judul, penulis, tahun, kategori_id) VALUES ('Dasar Basis Data', 'Sari Wulandari', 2021, 1), ('Jaringan Komputer', 'Ahmad Fauzi', 2019, 1), ('Fisika untuk Pemula', 'Rina Marlina', 2020, 2), ('Kimia Organik', 'Rina Marlina', 2022, 2), ('Sejarah Nusantara', 'Bambang Wijaya', 2018, 3), ('Cerita Kota Lama', 'Nur Aisyah', 2023, 4), ('Statistika Terapan', 'Sari Wulandari', 2022, 5), ('Algoritma dan Struktur Data', 'Ahmad Fauzi', 2021, 1);
INSERT INTO peminjaman (anggota_id, buku_id, tanggal_pinjam, tanggal_kembali) VALUES (1, 1, '2025-03-01', '2025-03-15'), (1, 7, '2025-03-05', '2025-03-20'), (2, 3, '2025-03-10', NULL), (3, 5, '2025-02-20', '2025-03-01'), (2, 1, '2025-04-02', NULL), (4, 6, '2025-04-07', NULL), (5, 8, '2025-04-09', '2025-04-25'), (4, 2, '2025-05-01', NULL);
SQL

string_agg merangkai teks menjadi satu sel#

string_agg menyambungkan nilai teks dari seluruh baris kelompok dengan pemisah yang dinyatakan, dan urutannya ditegakkan melalui ORDER BY di dalam pemanggilannya. Hasilnya cocok untuk daftar ringkas dalam satu sel laporan.

bash 
# daftar judul untuk setiap kategori
psql -U postgres -d perpustakaan -c "SELECT k.nama AS kategori, string_agg(b.judul, ', ' ORDER BY b.judul) AS koleksi FROM kategori k JOIN buku b ON b.kategori_id = k.id GROUP BY k.nama ORDER BY k.nama;"
text Keluaran 
  kategori   |                             koleksi
-------------+------------------------------------------------------------------
 Fiksi       | Cerita Kota Lama
 Pemrograman | Algoritma dan Struktur Data, Dasar Basis Data, Jaringan Komputer
 Sains       | Fisika untuk Pemula, Kimia Organik
 Sejarah     | Sejarah Nusantara
 Statistika  | Statistika Terapan
(5 rows)

Kategori Pemrograman memuat tiga judul yang terurut abjad dalam satu sel, dan urutan di dalam string_agg dinyatakan eksplisit karena urutan baris pada agregasi tidak dijanjikan. Pemisahnya bebas, dan pola umum lainnya memakai baris baru agar daftar panjang tetap terbaca pada berkas teks.

array_agg dan pilihan menyimpan sebagai array#

array_agg mengumpulkan nilai ke dalam array, sehingga hasilnya tetap terstruktur dan dapat diakses menurut posisinya, bukan sekadar teks yang diuraikan ulang. Pilihan antara keduanya mengikuti kebutuhan konsumennya, yaitu teks untuk tampilan dan array untuk pemrosesan lanjutan di dalam query.

bash 
# judul per penulis sebagai array
psql -U postgres -d perpustakaan -c "SELECT penulis, array_agg(judul ORDER BY judul) AS karya FROM buku GROUP BY penulis ORDER BY penulis LIMIT 3;"
text Keluaran 
    penulis     |                        karya
----------------+-----------------------------------------------------
 Ahmad Fauzi    | {"Algoritma dan Struktur Data","Jaringan Komputer"}
 Bambang Wijaya | {"Sejarah Nusantara"}
 Nur Aisyah     | {"Cerita Kota Lama"}
(3 rows)

Setiap baris memuat array judul yang dapat diindeks dengan tanda kurung siku, dihitung dengan cardinality, dan diuji keanggotaannya, kemampuan yang dibahas lebih dalam pada dokumentasi jenis array. Untuk laporan yang dikirim ke aplikasi, array memelihara struktur tanpa kesepakatan pemisah buatan.

Memilih bentuk hasil menurut konsumennya#

Ketiga agregasi ini menempati wilayah yang berdekatan, dan pilihannya mengikuti konsumen hasilnya. Teks yang dihasilkan string_agg cocok ketika seluruh pekerjaan berakhir pada tampilan, array hasil array_agg cocok untuk query lanjutan dan antarmuka aplikasi, sedangkan angka dari COUNT dan temannya tetap menjadi bahan pengelompokan berikutnya.

bash 
# bentuk teks dan array atas data yang sama
psql -U postgres -d perpustakaan -c "SELECT penulis, string_agg(judul, ' | ' ORDER BY judul) AS teks, array_length(array_agg(judul), 1) AS banyak FROM buku GROUP BY penulis ORDER BY penulis LIMIT 3;"
text Keluaran 
    penulis     |                      teks                       | banyak
----------------+-------------------------------------------------+--------
 Ahmad Fauzi    | Algoritma dan Struktur Data | Jaringan Komputer |      2
 Bambang Wijaya | Sejarah Nusantara                               |      1
 Nur Aisyah     | Cerita Kota Lama                                |      1
(3 rows)

Satu query menampilkan kedua bentuk berdampingan, dan array_length mengukur banyaknya karya tanpa menguraikan teksnya. Penggabungan string pada tingkat aplikasi yang menuntut struktur ulang adalah tanda bahwa bentuk array seharusnya dipilih sejak awal.

date_trunc untuk kelompok periode#

date_trunc memotong nilai waktu sampai satuan yang dinyatakan, misalnya bulan, sehingga baris dari periode yang sama berbagi nilai yang identik dan siap dikelompokkan. Pola tren waktu menumpuk date_trunc, GROUP BY, dan agregasi angka.

bash 
# jumlah peminjaman per bulan
psql -U postgres -d perpustakaan -c "SELECT date_trunc('month', tanggal_pinjam)::date AS bulan, COUNT(*) AS pinjaman FROM peminjaman GROUP BY 1 ORDER BY 1;"
text Keluaran 
   bulan    | pinjaman
------------+----------
 2025-02-01 |        1
 2025-03-01 |        3
 2025-04-01 |        3
 2025-05-01 |        1
(4 rows)

Cast ke date menghapus bagian jam pada label bulan, dan referensi posisi GROUP BY 1 merujuk kolom hasil pertama, kebiasaan ringkas yang dijelaskan pada dokumentasi SELECT. Periode yang tidak memiliki data tetap absen dari hasil, dan pengisian rangkanya dilakukan melalui kerangka kalender yang dibangkitkan dengan generate_series, fungsi yang diperkenalkan pada modul subquery dan CTE.

Kesalahan umum#

Kesalahan pertama adalah string_agg tanpa ORDER BY di dalamnya, sehingga urutan teks dalam satu sel berubah-ubah antar eksekusi, dan laporan yang sama tampak berbeda setiap hari. Kesalahan kedua adalah menyalin hasil date_trunc bertipe timestamp ke aplikasi tanpa cast, sehingga label bulan membawa jam 00:00:00 yang mengganggu perbandingan teks di luar basis data.

Kesalahan ketiga adalah memakai string_agg untuk membentuk daftar yang kemudian diuraikan ulang dengan pemisah, padahal konsumen berikutnya berada di dalam query yang sama, dan array_agg menyelamatkan seluruh pekerjaan penguraian itu. Kesalahan keempat adalah mengira date_trunc mengubah data yang tersimpan, padahal ia fungsi pembacaan, dan kolom aslinya tetap utuh.

Ringkasan#

  • string_agg merangkai teks kelompok menjadi satu sel dengan pemisah bebas, dan urutannya ditegakkan di dalam pemanggilan.
  • array_agg menyimpan hasil sebagai array terstruktur yang tetap dapat diakses menurut posisi.
  • date_trunc memotong waktu menjadi periode tetap, sehingga tren bulanan dan mingguan menjadi satu GROUP BY.
  • Hasil date_trunc bertipe timestamp, dan cast ke date membersihkan label periodenya.

Referensi#