Analis jarang bekerja dengan satu berkas CSV, karena data dalam jumlah besar umumnya tersimpan di dalam basis data yang diakses dengan SQL. Topik ini membahas cara menulis query SELECT dengan WHERE, ORDER BY, dan LIMIT, dilanjutkan JOIN untuk menggabungkan tabel, lalu GROUP BY dengan COUNT dan AVG untuk merangkum, semuanya dijalankan melalui Python dan SQLite. Setelah topik ini selesai, pertanyaan analisis dapat diterjemahkan menjadi query yang menghasilkan tabel jawaban atas data perpustakaan sekolah.
Gudang data perpustakaan#
Basis data contoh berperan sebagai gudang data kecil perpustakaan sekolah yang terdiri atas tiga tabel. Tabel anggota memuat enam anggota beserta kelasnya, tabel judul_buku memuat enam judul beserta kategorinya, dan tabel peminjaman memuat dua belas catatan peminjaman yang merujuk kedua tabel lain melalui kolom id_anggota dan id_buku. Rancangan semacam ini lazim dijumpai pada basis data, yaitu setiap jenis informasi disimpan satu kali pada tabelnya sendiri, lalu dirujuk melalui kolom penghubung. Skrip berikut membuat ketiga tabel tersebut, mengisinya dengan data literal, dan menyimpan semuanya ke dalam satu berkas bernama perpustakaan.db di folder topik.
import sqlite3
import pandas as pd
koneksi = sqlite3.connect("perpustakaan.db")
kursor = koneksi.cursor()
kursor.executescript("""
DROP TABLE IF EXISTS peminjaman;
DROP TABLE IF EXISTS anggota;
DROP TABLE IF EXISTS judul_buku;
CREATE TABLE anggota (
id_anggota INTEGER PRIMARY KEY,
nama TEXT,
kelas TEXT
);
CREATE TABLE judul_buku (
id_buku INTEGER PRIMARY KEY,
judul TEXT,
kategori TEXT
);
CREATE TABLE peminjaman (
id_pinjam INTEGER PRIMARY KEY,
id_anggota INTEGER,
id_buku INTEGER,
tanggal_pinjam TEXT,
lama_hari INTEGER
);
""")
anggota = [
(1, "Ali", "7A"),
(2, "Budi", "7A"),
(3, "Citra", "7B"),
(4, "Dewi", "7B"),
(5, "Eka", "8A"),
(6, "Fajar", "8A"),
]
judul_buku = [
(1, "Cerita dari Desa Sendu", "fiksi"),
(2, "Kisah Sepuluh Penjelajah", "fiksi"),
(3, "Pengantar Astronomi", "sains"),
(4, "Belajar Menghitung Cepat", "pelajaran"),
(5, "Riwayat Kereta Api Nusantara", "sejarah"),
(6, "Panduan Menulis Surat", "bahasa"),
]
peminjaman = [
(1, 1, 1, "2024-07-01", 7),
(2, 1, 3, "2024-07-03", 5),
(3, 2, 2, "2024-07-05", 4),
(4, 3, 3, "2024-07-08", 9),
(5, 3, 5, "2024-07-10", 6),
(6, 4, 2, "2024-07-12", 3),
(7, 4, 4, "2024-07-15", 8),
(8, 5, 1, "2024-07-18", 2),
(9, 5, 6, "2024-07-20", 10),
(10, 6, 5, "2024-07-22", 6),
(11, 2, 6, "2024-07-25", 7),
(12, 1, 5, "2024-07-28", 5),
]
kursor.executemany("INSERT INTO anggota VALUES (?, ?, ?)", anggota)
kursor.executemany("INSERT INTO judul_buku VALUES (?, ?, ?)", judul_buku)
kursor.executemany("INSERT INTO peminjaman VALUES (?, ?, ?, ?, ?)", peminjaman)
koneksi.commit()
print(pd.read_sql("SELECT * FROM peminjaman", koneksi))
koneksi.close() id_pinjam id_anggota id_buku tanggal_pinjam lama_hari
0 1 1 1 2024-07-01 7
1 2 1 3 2024-07-03 5
2 3 2 2 2024-07-05 4
3 4 3 3 2024-07-08 9
4 5 3 5 2024-07-10 6
5 6 4 2 2024-07-12 3
6 7 4 4 2024-07-15 8
7 8 5 1 2024-07-18 2
8 9 5 6 2024-07-20 10
9 10 6 5 2024-07-22 6
10 11 2 6 2024-07-25 7
11 12 1 5 2024-07-28 5Perintah executescript menjalankan beberapa pernyataan SQL sekaligus, dimulai dari DROP TABLE IF EXISTS agar skrip dapat dijalankan ulang tanpa error tabel yang sudah ada. Pernyataan INSERT dengan tanda tanya mengisi baris dari daftar Python, dan commit menyimpan perubahannya ke berkas. Karena tersimpan sebagai berkas, isi basis data tetap hidup setelah program berakhir, sehingga setiap blok berikutnya cukup membuka berkas yang sama tanpa membuat ulang datanya. Perilaku inilah yang membedakannya dari basis data ":memory:" yang hanya hidup di memori.
Query sebagai pertanyaan#
Query dibaca paling mudah apabila dipandang sebagai pertanyaan, dan hasilnya sebagai tabel jawaban. Pertanyaan contohnya berbunyi: “Peminjaman mana yang lamanya sekurang-kurangnya tujuh hari, ditampilkan lima teratas sesuai urutan tanggal?” Jawaban atas pertanyaan itu diterjemahkan menjadi query berikut. Klausa SELECT memilih kolom yang ditampilkan, WHERE menyaring baris, ORDER BY mengurutkan, dan LIMIT membatasi jumlah barisnya.
import sqlite3
import pandas as pd
koneksi = sqlite3.connect("perpustakaan.db")
query = """
SELECT tanggal_pinjam, lama_hari
FROM peminjaman
WHERE lama_hari >= 7
ORDER BY tanggal_pinjam
LIMIT 5
"""
print(pd.read_sql(query, koneksi))
koneksi.close() tanggal_pinjam lama_hari
0 2024-07-01 7
1 2024-07-08 9
2 2024-07-15 8
3 2024-07-20 10
4 2024-07-25 7Keluaran berupa lima baris dengan dua kolom, sesuai pertanyaannya. Baris yang lamanya di bawah tujuh hari tidak lolos penyaringan WHERE, dan LIMIT 5 kebetulan sama dengan banyaknya baris yang lolos, sehingga keduanya menegaskan hasil yang sama. Apabila pertanyaannya berubah, misalnya meminta kolom kelas, hanya klausa SELECT yang diganti, sedangkan struktur pertanyaannya tetap. Pola membaca query sebagai pertanyaan memudahkan pemeriksaan kebenarannya sebelum hasilnya digunakan.
Menggabungkan tabel dengan JOIN#
Kolom id_anggota pada tabel peminjaman hanya berisi angka, sehingga pertanyaan tentang nama peminjam tidak dapat dijawab dari satu tabel. JOIN menambahkan kolom dari tabel lain dengan mencocokkan nilai kolom penghubungnya, yaitu id_anggota pada peminjaman dengan id_anggota pada anggota. Setiap baris peminjaman diperkaya dengan baris anggota yang cocok, sehingga nama dan kelas ikut tampil pada hasil. Tulisan AS p dan AS a pada query berikut merupakan alias, yaitu nama singkat yang menggantikan nama tabel yang panjang.
import sqlite3
import pandas as pd
koneksi = sqlite3.connect("perpustakaan.db")
query = """
SELECT p.id_pinjam, a.nama, a.kelas, p.tanggal_pinjam
FROM peminjaman AS p
JOIN anggota AS a ON p.id_anggota = a.id_anggota
ORDER BY p.id_pinjam
"""
print(pd.read_sql(query, koneksi))
koneksi.close() id_pinjam nama kelas tanggal_pinjam
0 1 Ali 7A 2024-07-01
1 2 Ali 7A 2024-07-03
2 3 Budi 7A 2024-07-05
3 4 Citra 7B 2024-07-08
4 5 Citra 7B 2024-07-10
5 6 Dewi 7B 2024-07-12
6 7 Dewi 7B 2024-07-15
7 8 Eka 8A 2024-07-18
8 9 Eka 8A 2024-07-20
9 10 Fajar 8A 2024-07-22
10 11 Budi 7A 2024-07-25
11 12 Ali 7A 2024-07-28Setiap kolom pada SELECT dan JOIN ditulis dengan awalan aliasnya, misalnya p.id_anggota. Awalan ini wajib apabila kedua tabel memuat nama kolom yang sama, sebab id_anggota ada di kedua tabel sekaligus. Tanpa awalan, SQLite menolak query dengan pesan error ambiguous column name, dan pesan itu menjadi temuan yang paling sering dijumpai saat JOIN pertama kali ditulis. Dua belas baris keluaran juga memperlihatkan perilaku penting lainnya, yaitu satu anggota muncul lebih dari satu kali karena yang digabungkan adalah baris peminjamannya, bukan anggotanya.
Merangkum dengan GROUP BY#
Pertanyaan analisis sering memuat kata “per”, misalnya berapa peminjaman per kategori buku atau per kelas. GROUP BY mengumpulkan baris yang nilainya sama menjadi satu kelompok, lalu fungsi agregasi menghitung setiap kelompok menjadi satu angka. COUNT menghitung banyaknya baris dalam kelompok, sedangkan AVG menghitung rata-rata sebuah kolom. Nama kolom hasil ditentukan melalui AS, sehingga tabel jawabannya langsung terbaca.
import sqlite3
import pandas as pd
koneksi = sqlite3.connect("perpustakaan.db")
query = """
SELECT j.kategori,
COUNT(*) AS jumlah_pinjam,
AVG(p.lama_hari) AS rata_lama_hari
FROM peminjaman AS p
JOIN judul_buku AS j ON p.id_buku = j.id_buku
GROUP BY j.kategori
ORDER BY jumlah_pinjam DESC, j.kategori
"""
print(pd.read_sql(query, koneksi))
koneksi.close() kategori jumlah_pinjam rata_lama_hari
0 fiksi 4 4.000000
1 sejarah 3 5.666667
2 bahasa 2 8.500000
3 sains 2 7.000000
4 pelajaran 1 8.000000Kelompok fiksi memimpin dengan empat peminjaman, tetapi rata-rata lamanya justru paling pendek, yaitu empat hari. Menggabungkan dua ukuran dalam satu query seperti ini berguna karena jumlah dan rata-rata menjawab pertanyaan yang berbeda. Urutan ORDER BY jumlah_pinjam DESC menempatkan kategori dengan peminjaman terbanyak di atas, dan kategori dengan jumlah sama diurutkan menurut abjad karena klausa kedua pada ORDER BY, yaitu j.kategori.
Query yang sama dapat ditujukan pada kelompok lain hanya dengan mengganti kolom pengelompokannya. Kelompok kelas menjawab pertanyaan sebaran minat baca antarkelas, yaitu 7A dengan lima peminjaman, 7B dengan empat, dan 8A dengan tiga.
import sqlite3
import pandas as pd
koneksi = sqlite3.connect("perpustakaan.db")
query = """
SELECT a.kelas, COUNT(*) AS jumlah_pinjam
FROM peminjaman AS p
JOIN anggota AS a ON p.id_anggota = a.id_anggota
GROUP BY a.kelas
ORDER BY a.kelas
"""
print(pd.read_sql(query, koneksi))
koneksi.close() kelas jumlah_pinjam
0 7A 5
1 7B 4
2 8A 3Kombinasi JOIN dan GROUP BY seperti pada dua query terakhir adalah pola yang paling sering digunakan analis ketika pertanyaannya menyentuh lebih dari satu tabel. Tabel ringkasannya kecil, tetapi setiap angkanya lahir dari pertanyaan yang dirumuskan dengan jelas. Angka 7A pada keluaran terakhir, misalnya, dapat ditelusuri kembali sampai ke baris-baris peminjaman Ali dan Budi pada query JOIN sebelumnya.
Kesalahan umum#
Kesalahan pertama adalah menaruh kondisi agregasi pada WHERE. Klausa WHERE dievaluasi sebelum kelompok dibentuk, sehingga kondisi seperti WHERE rata_lama_hari > 5 merujuk kolom yang belum ada dan berakhir dengan error. Kondisi yang menyeleksi hasil pengelompokan ditulis pada klausa HAVING, misalnya HAVING COUNT(*) >= 3 untuk memilih kelompok dengan minimal tiga baris.
Kesalahan kedua adalah membaca pesan error sintaks tanpa memeriksa posisinya. SQLite melaporkan kesalahan dengan pesan OperationalError yang mengutip kata terdekat, misalnya near "FROM", dan kata yang dikutip itu biasanya berada tepat sesudah bagian yang salah. Koma yang hilang antara dua nama kolom dan tanda kutip yang tidak tertutup adalah dua penyebab yang paling sering muncul. Pesan error SQL terasa samar pada awalnya, tetapi posisinya hampir selalu akurat.
Kesalahan ketiga adalah melupakan bahwa WHERE bekerja sebelum GROUP BY. Apabila filter lama_hari >= 7 ditambahkan pada query agregasi, COUNT(*) hanya menghitung baris yang lolos filter, sehingga jumlah per kategori berubah dan bukan lagi jumlah seluruh peminjaman. Hasilnya benar menurut definisinya, tetapi menyesatkan apabila pertanyaannya sebenarnya ingin seluruh data. Pisahkan pertanyaan yang difilter dari yang tidak, atau jalankan keduanya sebagai dua query terpisah.
Ringkasan#
- Basis data SQLite tersimpan sebagai berkas dan tetap hidup antarprogram, sehingga satu skrip pembuat data cukup untuk seluruh analisis.
- Query dibaca sebagai pertanyaan, dengan
SELECTmemilih kolom,WHEREmenyaring baris,ORDER BYmengurutkan, danLIMITmembatasi. - JOIN memperkaya tabel dengan kolom dari tabel lain melalui pencocokan kolom penghubung, dan kolom yang namanya sama wajib ditulis dengan awalan alias.
- GROUP BY mengumpulkan baris per kelompok, lalu
COUNTdanAVGmenghasilkan tabel ringkasan per kategori atau per kelas.
Referensi#
- sqlite3 — Python Software Foundation — Modul bawaan Python untuk membuat koneksi SQLite dan mengeksekusi pernyataan SQL.
- SELECT — SQLite — Sintaks pernyataan SELECT SQLite, termasuk WHERE, ORDER BY, LIMIT, dan klausa agregasinya.
- pandas.read_sql — Pandas
— Cara
read_sqlmenjalankan query melalui koneksi basis data dan mengembalikan DataFrame.