Bahasa Query Terapan
Query Agregasi dan Analisis Data
Menggunakan fungsi agregat seperti SUM, AVG, COUNT, MAX, dan MIN untuk menghasilkan laporan atau analisis data yang lebih terperinci.
SELECT Jurusan, AVG(TahunMasuk) AS RataTahunMasuk
FROM Mahasiswa
GROUP BY Jurusan;
Query ini menghitung rata-rata tahun masuk berdasarkan jurusan mahasiswa. Pembuatan Laporan Mingguan/Bulanan: Menggunakan query untuk membuat laporan berkala, seperti penjualan per bulan atau statistik pengguna.
SELECT EXTRACT(MONTH FROM TanggalTransaksi) AS Bulan, SUM(Total) AS TotalPenjualan
FROM Penjualan
GROUP BY EXTRACT(MONTH FROM TanggalTransaksi);
Query ini menghitung total penjualan bulanan dari tabel Penjualan. Optimalisasi Query: Membahas teknik untuk meningkatkan efisiensi query, seperti penggunaan indeks, penghindaran subquery berlebihan, atau cara menulis query yang lebih optimal.CREATE INDEX idx_mahasiswa_tahun ON Mahasiswa(TahunMasuk);
Perintah ini membuat indeks pada kolom TahunMasuk tabel Mahasiswa, yang akan meningkatkan kecepatan pencarian data berdasarkan tahun masuk. Penerapan Query dalam Data Warehousing: Membahas peran SQL dalam data warehousing, seperti pengelompokan data untuk kebutuhan ETL (Extract, Transform, Load).
SELECT ProductID, SUM(Quantity) AS TotalQuantity
FROM Sales
WHERE Date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY ProductID;
Query ini menampilkan total jumlah produk yang terjual dalam satu tahun, sesuai kebutuhan pelaporan bisnis. Case Statement dan Conditional Query: Menggunakan kondisi dalam query untuk menghasilkan data dinamis.
SELECT Nama,
CASE
WHEN TahunMasuk < 2020 THEN 'Senior'
ELSE 'Junior'
END AS Kategori
FROM Mahasiswa;
Query ini mengelompokkan mahasiswa menjadi Senior atau Junior berdasarkan tahun masuk.
Analisis Data dengan Fungsi Agregasi Lanjutan
Selain fungsi dasar seperti `SUM`, `AVG`, `COUNT`, `MAX`, dan `MIN`, fungsi agregasi lanjutan dapat menghasilkan analisis yang lebih mendalam.
Contoh Analisis Periode Tertentu: Menghitung rata-rata penjualan bulanan atau mingguan.
SELECT EXTRACT(YEAR FROM Tanggal) AS Tahun, EXTRACT(MONTH FROM Tanggal) AS Bulan, AVG(Total) AS RataRataPenjualan
FROM Penjualan
GROUP BY EXTRACT(YEAR FROM Tanggal), EXTRACT(MONTH FROM Tanggal);
Query ini menghitung rata-rata penjualan setiap bulan di setiap tahun.
Rolling Sum atau Moving Average: Berguna untuk analisis tren dalam periode tertentu.
SELECT Tanggal, TotalPenjualan,
SUM(TotalPenjualan) OVER (ORDER BY Tanggal ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS RollingTotal7Hari
FROM PenjualanHarian;
Query ini menghitung total penjualan untuk periode 7 hari (termasuk hari ini dan enam hari sebelumnya), memberikan tren penjualan jangka pendek.
Analisis Data Multi-Level dengan GROUPING SETS
`GROUPING SETS` memungkinkan analisis data pada beberapa level agregasi dalam satu query. Misalnya, kita bisa menghitung total penjualan per wilayah, per produk, atau kombinasi keduanya.
SELECT Wilayah, Produk, SUM(Penjualan) AS TotalPenjualan
FROM Penjualan
GROUP BY GROUPING SETS ((Wilayah), (Produk), (Wilayah, Produk));
Query ini memberikan total penjualan berdasarkan `Wilayah`, `Produk`, dan kombinasi keduanya.
CUBE dan ROLLUP untuk Analisis Dimensi Berlapis
`CUBE` dan `ROLLUP` adalah teknik agregasi yang membantu menganalisis data dengan lebih dari satu dimensi, seperti kategori, wilayah, atau waktu.
- ROLLUP: Membuat subtotal pada setiap level agregasi.
SELECT Wilayah, Kategori, SUM(Penjualan) AS TotalPenjualan
FROM Penjualan
GROUP BY ROLLUP(Wilayah, Kategori);
Query ini menghasilkan total penjualan dengan subtotal per wilayah dan kategori.
- CUBE: Membuat semua kombinasi agregasi dari dimensi yang diberikan.
SELECT Wilayah, Kategori, SUM(Penjualan) AS TotalPenjualan
FROM Penjualan
GROUP BY CUBE(Wilayah, Kategori);
Query ini memberikan total penjualan per kombinasi wilayah dan kategori, termasuk total keseluruhan.
Menggunakan Case Statements untuk Kategori Data Dinamis
`CASE` digunakan untuk membuat kolom terhitung (calculated columns) atau pengelompokan data dinamis dalam query.
Contoh Pemberian Kategori Berdasarkan Nilai:
SELECT NamaProduk, Penjualan,
CASE
WHEN Penjualan >= 10000 THEN 'Sangat Tinggi'
WHEN Penjualan >= 5000 THEN 'Tinggi'
WHEN Penjualan >= 1000 THEN 'Sedang'
ELSE 'Rendah'
END AS KategoriPenjualan
FROM Produk;
Query ini mengelompokkan produk berdasarkan total penjualan dengan kategori `Sangat Tinggi`, `Tinggi`, `Sedang`, dan `Rendah`.
Analisis Data Hirarki dengan Recursive CTE
Recursive CTE sering digunakan untuk struktur data hirarki seperti struktur organisasi atau pohon kategori produk.
Contoh Struktur Hirarki Karyawan:
WITH RECURSIVE HierarkiKaryawan AS (
SELECT ID, Nama, AtasanID
FROM Karyawan
WHERE Nama = 'Direktur Utama'
UNION ALL
SELECT k.ID, k.Nama, k.AtasanID
FROM Karyawan k
INNER JOIN HierarkiKaryawan h ON k.AtasanID = h.ID
)
SELECT FROM HierarkiKaryawan;
Query ini menampilkan seluruh struktur organisasi di bawah `Direktur Utama`.
Pencarian Data Berdasarkan Pola dengan Wildcards dan Regex
Pencarian pola sangat berguna saat bekerja dengan data teks yang bervariasi, seperti alamat email, nomor telepon, atau pola nama.
- LIKE dengan Wildcards: Pencarian yang lebih fleksibel.
Contoh:
SELECT FROM Karyawan
WHERE Email LIKE '%@example.com';
Query ini menemukan semua karyawan dengan alamat email yang berakhir dengan `@example.com`.
- Regex untuk Pencarian Pola Kompleks: SQL pada beberapa sistem basis data mendukung regex untuk pencarian pola yang lebih rumit.
- Contoh:
SELECT FROM Pelanggan
WHERE Nama REGEXP '^[A-Z][a-z]$';
Query ini menemukan pelanggan dengan nama yang dimulai dengan huruf kapital diikuti huruf kecil.
Optimasi Query dengan Indexing dan Query Execution Plan
Untuk dataset besar, optimasi query sangat penting agar eksekusi berjalan lebih efisien. Dengan indexing dan analisis query execution plan, kita bisa mempercepat query yang kompleks.
- Membuat Indeks pada kolom yang sering diakses.
Contoh:
CREATE INDEX idx_penjualan_tanggal ON Penjualan(Tanggal);
Indeks ini mempercepat pencarian data berdasarkan `Tanggal` pada tabel `Penjualan`.
- Melihat Execution Plan untuk Optimasi: Sebagian besar sistem basis data mendukung perintah seperti `EXPLAIN` untuk melihat rencana eksekusi query.
Contoh:
EXPLAIN SELECT FROM Penjualan WHERE Tanggal = '2024-10-10';
Menampilkan informasi tentang bagaimana query ini dieksekusi, yang dapat membantu dalam optimasi.
Partisi Data untuk Query Lebih Efisien
Partisi data memisahkan data besar ke dalam segmen-segmen berdasarkan kriteria tertentu, memungkinkan query menjadi lebih cepat.
- Membuat Partisi Berdasarkan Kolom Tanggal:
CREATE TABLE Penjualan (
ID INT,
Tanggal DATE,
Total INT
) PARTITION BY RANGE (YEAR(Tanggal)) (
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025)
);
Partisi ini membagi data penjualan berdasarkan tahun, sehingga query data pada tahun tertentu akan lebih cepat.
Analisis Data dengan JSON dan Fungsi JSON di SQL
JSON sering digunakan dalam aplikasi modern, dan beberapa basis data mendukung query langsung pada data JSON.
- Mengambil Nilai dari JSON:
SELECT data->>'$.alamat.kota' AS Kota
FROM Pelanggan
WHERE data->>'$.alamat.kota' = 'Jakarta';
Query ini menampilkan kota pelanggan dari kolom JSON `data`, lalu memfilter yang berasal dari kota 'Jakarta'.
Pivot dan Unpivot Data dengan SQL
Pivot data memungkinkan kita mengonversi baris menjadi kolom dan sebaliknya, berguna untuk laporan data dinamis.
Contoh Pivot:
SELECT Tahun,
SUM(CASE WHEN Kategori = 'Elektronik' THEN Penjualan ELSE 0 END) AS Elektronik,
SUM(CASE WHEN Kategori = 'Fashion' THEN Penjualan ELSE 0 END) AS Fashion
FROM Penjualan
GROUP BY Tahun;
Query ini mem-pivot data penjualan agar menghasilkan kolom untuk setiap kategori per tahun.
Analisis Geospasial dengan Fungsi Spasial SQL
Pada sistem yang mendukung data spasial (misalnya PostgreSQL), SQL dapat digunakan untuk analisis berbasis lokasi.
Contoh Menemukan Lokasi Terdekat:
SELECT NamaTempat, Lokasi
FROM Tempat
WHERE ST_Distance(Lokasi, ST_MakePoint(106.8456, -6.2088)) < 5;
Query ini mencari tempat yang berjarak kurang dari 5 km dari titik koordinat Jakarta.
Query Time-Series untuk Data Berkala
Time-series adalah jenis analisis yang sering digunakan dalam data keuangan, cuaca, atau metrik kinerja.
Contoh Tren Penjualan Harian:
SELECT Tanggal, SUM(Penjualan) OVER (ORDER BY Tanggal) AS TrenPenjualan
FROM PenjualanHarian;
Query ini menampilkan penjualan kumulatif harian, memberikan gambaran tren penjualan.