Trik SQL pada PostgreSQL yang Jarang Digunakan

PostgreSQL adalah salah satu sistem manajemen basis data relasional yang paling kuat dan kaya fitur. Meskipun banyak orang hanya memanfaatkan fitur dasar, PostgreSQL menawarkan sejumlah trik dan teknik lanjutan yang jarang digunakan tetapi sangat berguna. Dalam artikel ini, kita akan mengeksplorasi beberapa trik SQL pada PostgreSQL yang mungkin belum Anda ketahui, tetapi bisa meningkatkan efisiensi dan fungsionalitas query Anda.

1. Menggunakan LATERAL untuk Mengakses Kolom dari Subquery

LATERAL adalah operator yang memungkinkan subquery diakses secara langsung dalam query utama. Ini sangat berguna ketika subquery bergantung pada kolom dari baris yang sedang diproses.

Contoh:

SELECT 
    p.product_name,
    reviews.avg_rating
FROM 
    products p
CROSS JOIN LATERAL (
    SELECT AVG(rating) AS avg_rating
    FROM reviews r
    WHERE r.product_id = p.product_id
) reviews;

Dengan menggunakan LATERAL, Anda bisa mengakses data yang dihasilkan oleh subquery secara dinamis berdasarkan kolom dari tabel utama.

2. Menggunakan GENERATE_SERIES untuk Menghasilkan Rentang Data

GENERATE_SERIES adalah fungsi yang berguna untuk menghasilkan rangkaian nilai, yang dapat sangat berguna untuk keperluan pengujian atau menghasilkan data dummy.

Contoh:

SELECT *
FROM GENERATE_SERIES(1, 10) AS num;

Ini akan menghasilkan tabel dengan satu kolom yang berisi angka dari 1 hingga 10.

3. Menggunakan CROSS JOIN untuk Menghasilkan Kombinasi Baris

CROSS JOIN menghasilkan produk Cartesian antara dua tabel, yaitu setiap baris dari tabel pertama akan dipasangkan dengan setiap baris dari tabel kedua. Ini berguna untuk analisis data yang memerlukan kombinasi lengkap dari dua set data.

Contoh:

SELECT 
    a.name AS artist_name,
    g.genre
FROM 
    artists a
CROSS JOIN 
    genres g;

Ini akan menghasilkan semua kombinasi antara artis dan genre yang ada.

4. Menggunakan FILTER dengan Fungsi Agregat untuk Menyaring Data

Fungsi agregat dengan FILTER memungkinkan Anda untuk menerapkan kondisi pada agregat dalam query.

Contoh:

SELECT 
    department,
    SUM(salary) FILTER (WHERE gender = 'F') AS total_female_salaries,
    SUM(salary) FILTER (WHERE gender = 'M') AS total_male_salaries
FROM employees
GROUP BY department;

Dengan FILTER, Anda dapat menghitung total gaji berdasarkan kondisi tertentu dalam satu query.

5. Menggunakan PARTITION BY dalam Fungsi Jendela

Fungsi jendela seperti ROW_NUMBER(), RANK(), dan SUM() dengan PARTITION BY memungkinkan Anda melakukan analisis data dalam kelompok.

Contoh:

SELECT 
    employee_id,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

Ini akan memberikan peringkat gaji dalam setiap departemen secara terpisah.

6. Menggunakan PL/pgSQL untuk Menulis Fungsi Kustom

PostgreSQL mendukung bahasa pemrograman PL/pgSQL untuk menulis fungsi yang lebih kompleks dan terstruktur. Fungsi ini dapat digunakan untuk menyederhanakan query yang sering digunakan atau untuk melakukan operasi yang tidak mungkin dilakukan dengan SQL biasa.

Contoh Fungsi:

CREATE OR REPLACE FUNCTION get_employee_bonus(employee_id INT) RETURNS NUMERIC AS $$
DECLARE
    employee_salary NUMERIC;
    bonus NUMERIC;
BEGIN
    SELECT salary INTO employee_salary FROM employees WHERE id = employee_id;
    bonus := employee_salary * 0.1;
    RETURN bonus;
END;
$$ LANGUAGE plpgsql;

Fungsi ini menghitung bonus berdasarkan gaji karyawan.

7. Menggunakan TABLESAMPLE untuk Mengambil Sampel Data

TABLESAMPLE memungkinkan Anda untuk mengambil sampel acak dari tabel, yang sangat berguna untuk analisis dan pengujian data.

Contoh:

SELECT *
FROM sales
TABLESAMPLE SYSTEM (5);

Ini akan mengambil 5% sampel acak dari tabel sales.

8. Menggunakan WITH RECURSIVE untuk Query Rekursif

WITH RECURSIVE memungkinkan Anda untuk melakukan query rekursif yang dapat berguna untuk bekerja dengan data hierarkis, seperti struktur organisasi.

Contoh:

WITH RECURSIVE org_chart AS (
    SELECT employee_id, manager_id, name
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.manager_id, e.name
    FROM employees e
    INNER JOIN org_chart o ON e.manager_id = o.employee_id
)
SELECT * FROM org_chart;

Ini akan menghasilkan struktur organisasi lengkap mulai dari manajer tingkat atas.

9. Menggunakan JSONB untuk Menyimpan dan Mengelola Data JSON

PostgreSQL mendukung tipe data JSONB untuk menyimpan dan mengelola data dalam format JSON, yang memungkinkan query yang efisien dan fleksibel pada data terstruktur.

Contoh:

SELECT
    data->>'name' AS name,
    data->>'age' AS age
FROM users
WHERE data->>'status' = 'active';

Ini memungkinkan Anda untuk melakukan query langsung pada data JSON yang disimpan dalam kolom.

10. Menggunakan EXCLUDE untuk Menyimpan Data Unik dalam Index

EXCLUDE constraint memungkinkan Anda untuk memastikan bahwa tidak ada dua baris dalam tabel yang memiliki nilai yang sama untuk kolom-kolom yang ditentukan dengan cara yang tidak dapat dilakukan dengan UNIQUE.

Contoh:

CREATE TABLE reservations (
    room_id INT,
    check_in DATE,
    check_out DATE,
    EXCLUDE USING GIST (room_id WITH =, tstzrange(check_in, check_out) WITH &&)
);

Ini memastikan bahwa tidak ada dua reservasi yang tumpang tindih untuk kamar yang sama.