Contoh syntax SQL yang kompleks dan panjang dalam PostgreSQL yang bisa Anda gunakan untuk latihan sehari-hari

EXERCISE POSTGRESQL

Berikut adalah contoh syntax SQL yang kompleks dan panjang dalam PostgreSQL yang bisa Anda gunakan untuk latihan sehari-hari. Syntax ini mencakup beberapa perintah seperti CREATE TABLE, INSERT, UPDATE, JOIN, AGGREGATE FUNCTIONS, dan SUBQUERY. Syntax ini juga berisi skenario di mana kita membuat tabel, memasukkan data, memperbarui data, dan melakukan query untuk menganalisis data.

Skenario

Misalkan kita memiliki sistem manajemen untuk toko yang memiliki tabel customers, products, dan orders.

1. Membuat Tabel

Pertama, kita membuat tabel untuk menyimpan data pelanggan, produk, dan pesanan.

sql
-- Tabel untuk pelanggan
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(15),
    created_at TIMESTAMP DEFAULT NOW()
);

-- Tabel untuk produk
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price NUMERIC(10, 2) NOT NULL CHECK (price > 0),
    stock INT NOT NULL DEFAULT 0
);

-- Tabel untuk pesanan
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date TIMESTAMP DEFAULT NOW(),
    total_amount NUMERIC(10, 2),
    status VARCHAR(20) DEFAULT 'Pending',
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Tabel untuk detail pesanan (relasi many-to-many antara orders dan products)
CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0),
    price NUMERIC(10, 2) NOT NULL CHECK (price > 0),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

2. Memasukkan Data

Selanjutnya, kita akan memasukkan data contoh ke dalam tabel.

sql
-- Memasukkan data pelanggan
INSERT INTO customers (name, email, phone) VALUES
('Alice Johnson', 'alice@example.com', '123-456-7890'),
('Bob Smith', 'bob@example.com', '234-567-8901'),
('Charlie Brown', 'charlie@example.com', '345-678-9012');

-- Memasukkan data produk
INSERT INTO products (product_name, price, stock) VALUES
('Laptop', 1200.00, 10),
('Smartphone', 800.00, 15),
('Tablet', 500.00, 20),
('Headphones', 150.00, 50);

-- Memasukkan data pesanan
INSERT INTO orders (customer_id, total_amount, status) VALUES
(1, 1300.00, 'Completed'),
(2, 800.00, 'Completed'),
(3, 500.00, 'Pending');

-- Memasukkan data detail pesanan
INSERT INTO order_items (order_id, product_id, quantity, price) VALUES
(1, 1, 1, 1200.00),  -- Alice membeli 1 Laptop
(1, 4, 2, 150.00),   -- Alice membeli 2 Headphones
(2, 2, 1, 800.00),   -- Bob membeli 1 Smartphone
(3, 3, 1, 500.00);   -- Charlie membeli 1 Tablet

3. Melakukan Pembaruan Data

Misalkan kita ingin mengurangi stok produk berdasarkan pesanan yang telah diselesaikan.

sql
-- Mengurangi stok produk berdasarkan jumlah yang dipesan dalam pesanan yang selesai
UPDATE products
SET stock = stock - subquery.total_quantity
FROM (
    SELECT product_id, SUM(quantity) AS total_quantity
    FROM order_items
    JOIN orders ON orders.order_id = order_items.order_id
    WHERE orders.status = 'Completed'
    GROUP BY product_id
) AS subquery
WHERE products.product_id = subquery.product_id;

4. Melakukan Query untuk Menganalisis Data

A. Menampilkan Total Pembelian Setiap Pelanggan

sql
SELECT c.customer_id, c.name, SUM(o.total_amount) AS total_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status = 'Completed'
GROUP BY c.customer_id, c.name
ORDER BY total_spent DESC;

B. Menampilkan Produk dengan Penjualan Terbanyak

sql
SELECT p.product_id, p.product_name, SUM(oi.quantity) AS total_sold
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
JOIN orders o ON oi.order_id = o.order_id
WHERE o.status = 'Completed'
GROUP BY p.product_id, p.product_name
ORDER BY total_sold DESC;

C. Menampilkan Pesanan yang Belum Selesai Beserta Detailnya

sql
SELECT o.order_id, c.name AS customer_name, o.order_date, o.total_amount, o.status,
       p.product_name, oi.quantity, oi.price
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.status = 'Pending'
ORDER BY o.order_date;

D. Menampilkan Stok Produk yang Hampir Habis (Kurang dari 5)

sql
SELECT product_id, product_name, stock
FROM products
WHERE stock < 5;

E. Menghitung Rata-rata Total Pembelian Semua Pelanggan

sql
SELECT AVG(total_amount) AS average_purchase
FROM orders
WHERE status = 'Completed';

5. Menghapus Data Tertentu

Misalnya, kita ingin menghapus pesanan yang statusnya masih "Pending" selama lebih dari 30 hari.

sql
DELETE FROM orders
WHERE status = 'Pending'
AND order_date < NOW() - INTERVAL '30 days';

Ringkasan

Dengan syntax SQL di atas, Anda akan berlatih berbagai konsep seperti:

Membuat dan mengatur tabel dengan constraint (seperti PRIMARY KEY, FOREIGN KEY, CHECK, dll.).
Menggunakan INSERT untuk menambahkan data.
Menggunakan UPDATE dengan subquery untuk memperbarui data.
Menulis query kompleks dengan JOIN, GROUP BY, ORDER BY, dan agregasi data.
Menggunakan conditional statement dengan WHERE dan INTERVAL.

Latihan dengan syntax ini akan membantu Anda memahami cara kerja berbagai perintah SQL dalam PostgreSQL dan meningkatkan keterampilan pemrosesan data Anda.

lanjut

Baik, mari kita lanjutkan dengan beberapa query lanjutan dan teknik SQL yang lebih kompleks untuk latihan.

6. Menambahkan Index untuk Optimasi

Indeks dapat mempercepat query pada kolom yang sering digunakan dalam kondisi WHERE, JOIN, atau ORDER BY. Mari kita tambahkan beberapa indeks pada tabel.

sql
-- Menambahkan indeks pada kolom customer_id di tabel orders untuk mempercepat join
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- Menambahkan indeks pada kolom product_id di tabel order_items untuk mempercepat join
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

-- Menambahkan indeks pada kolom stock di tabel products untuk mempercepat pencarian produk dengan stok rendah
CREATE INDEX idx_products_stock ON products(stock);

7. Menggunakan VIEW untuk Query yang Sering Digunakan

View adalah virtual table yang menyimpan hasil dari query kompleks. Ini sangat membantu jika Anda memiliki query panjang yang sering digunakan.

A. Membuat View untuk Total Pembelian Setiap Pelanggan

sql
CREATE VIEW customer_total_spent AS
SELECT c.customer_id, c.name, SUM(o.total_amount) AS total_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status = 'Completed'
GROUP BY c.customer_id, c.name;

Sekarang, Anda dapat menggunakan view customer_total_spent seperti tabel biasa untuk melihat total pembelian setiap pelanggan:

sql
SELECT * FROM customer_total_spent ORDER BY total_spent DESC;

8. Menggunakan Stored Procedure untuk Operasi yang Berulang

Stored procedure memungkinkan Anda membuat fungsi SQL yang dapat dipanggil berulang kali. Ini berguna untuk tugas-tugas rutin, seperti menambah pesanan baru sekaligus memperbarui stok.

A. Membuat Stored Procedure untuk Menambah Pesanan Baru

sql
CREATE OR REPLACE FUNCTION create_order(
    customer_id INT,
    product_quantities JSON
) RETURNS VOID AS $$
DECLARE
    order_id INT;
    product_id INT;
    quantity INT;
    price NUMERIC(10, 2);
BEGIN
    -- Membuat pesanan baru
    INSERT INTO orders (customer_id, total_amount, status)
    VALUES (customer_id, 0, 'Pending')
    RETURNING order_id INTO order_id;
    
    -- Memproses setiap item di product_quantities (contoh: '{"1": 2, "3": 1}')
    FOR product_id, quantity IN SELECT * FROM json_each_text(product_quantities)
    LOOP
        -- Mengambil harga produk
        SELECT price INTO price FROM products WHERE product_id = product_id::INT;
        
        -- Menambahkan item pesanan
        INSERT INTO order_items (order_id, product_id, quantity, price)
        VALUES (order_id, product_id::INT, quantity::INT, price);
        
        -- Mengurangi stok produk
        UPDATE products SET stock = stock - quantity::INT
        WHERE product_id = product_id::INT;
    END LOOP;
    
    -- Menghitung total harga pesanan
    UPDATE orders
    SET total_amount = (SELECT SUM(quantity * price) FROM order_items WHERE order_id = order_id)
    WHERE order_id = order_id;
    
    RAISE NOTICE 'Pesanan baru dibuat dengan ID %', order_id;
END;
$$ LANGUAGE plpgsql;

Cara Menggunakan Stored Procedure

Stored procedure create_order bisa dipanggil untuk membuat pesanan baru dengan format JSON. Misalnya, untuk pelanggan dengan customer_id = 1 yang membeli 2 unit produk dengan product_id = 1 dan 1 unit produk dengan product_id = 3:

sql
SELECT create_order(1, '{"1": 2, "3": 1}');

9. Menggunakan Trigger untuk Memvalidasi atau Mengotomatisasi Tindakan

Trigger adalah cara untuk menjalankan kode secara otomatis ketika ada perubahan di tabel, seperti INSERT, UPDATE, atau DELETE.

A. Membuat Trigger untuk Memvalidasi Stok

Misalnya, kita ingin memastikan stok produk tidak bisa menjadi negatif.

Pertama, kita buat fungsi untuk melakukan validasi stok:

sql
CREATE OR REPLACE FUNCTION check_stock() RETURNS TRIGGER AS $$
BEGIN
    IF NEW.stock < 0 THEN
        RAISE EXCEPTION 'Stok tidak boleh negatif!';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Kemudian, kita tambahkan trigger ke tabel products:

sql
CREATE TRIGGER trigger_check_stock
BEFORE UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION check_stock();

Dengan trigger ini, setiap kali UPDATE dilakukan pada kolom stock di tabel products, fungsi check_stock akan memastikan nilai stock tidak negatif. Jika nilai negatif, akan muncul pesan error.

10. Menggunakan Recursive CTE untuk Hierarki Data

Recursive Common Table Expression (CTE) digunakan untuk bekerja dengan data hierarkis, seperti struktur organisasi atau kategori berlapis.

Contoh: Struktur Kategori Produk

Misalnya, kita ingin membuat tabel kategori produk berjenjang, di mana setiap kategori bisa memiliki subkategori.

Membuat Tabel categories dengan Referensi Diri

sql
CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL,
    parent_id INT REFERENCES categories(category_id)
);

Memasukkan Data Kategori

sql
INSERT INTO categories (category_name, parent_id) VALUES
('Electronics', NULL),
('Computers', 1),
('Laptops', 2),
('Smartphones', 1),
('Accessories', NULL),
('Headphones', 5),
('Chargers', 5);

Menggunakan Recursive CTE untuk Menampilkan Hirarki Kategori

sql
WITH RECURSIVE category_hierarchy AS (
    SELECT category_id, category_name, parent_id, 1 AS level
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.category_id, c.category_name, c.parent_id, ch.level + 1
    FROM categories c
    JOIN category_hierarchy ch ON c.parent_id = ch.category_id
)
SELECT * FROM category_hierarchy ORDER BY level, category_name;

Hasil dari query ini akan menampilkan hirarki kategori berdasarkan levelnya.

11. Menggabungkan Banyak Teknik dalam Query Kompleks

Misalnya, kita ingin menampilkan laporan penjualan dengan informasi lengkap, termasuk total penjualan tiap produk, jumlah unit yang terjual, dan jumlah sisa stok.

sql
SELECT 
    p.product_id,
    p.product_name,
    COALESCE(SUM(oi.quantity), 0) AS total_units_sold,
    COALESCE(SUM(oi.quantity * oi.price), 0) AS total_sales,
    p.stock AS remaining_stock
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
LEFT JOIN orders o ON oi.order_id = o.order_id AND o.status = 'Completed'
GROUP BY p.product_id, p.product_name, p.stock
ORDER BY total_sales DESC, total_units_sold DESC;

Dalam query ini:

Kita menggunakan LEFT JOIN untuk memasukkan semua produk, meskipun tidak ada yang terjual.
Fungsi COALESCE digunakan untuk menggantikan nilai NULL dengan 0.
Kita mengelompokkan data berdasarkan produk dan mengurutkan hasil berdasarkan total penjualan dan jumlah unit terjual.

12. Membuat Backup Data Tabel dengan `COPY`

Untuk backup atau ekspor data, Anda bisa menggunakan perintah COPY. Misalnya, untuk mengekspor data dari tabel orders ke file CSV:

sql
COPY orders TO '/path/to/backup/orders_backup.csv' DELIMITER ',' CSV HEADER;

Untuk mengimpor data kembali ke tabel:

sql
COPY orders FROM '/path/to/backup/orders_backup.csv' DELIMITER ',' CSV HEADER;

Penutup

Dengan latihan di atas, Anda akan mendapatkan pengalaman praktis dalam:

Membuat dan mengelola indeks, view, dan trigger.
Membuat stored procedure dan recursive CTE untuk bekerja dengan data hierarki.
Menggunakan teknik backup dan restore data.

Syntax-syntax ini akan membantu Anda menguasai PostgreSQL dengan lebih dalam, terutama jika Anda ingin bekerja dengan data yang lebih kompleks dan operasi yang lebih efisien.

- - - - -

Tentu, mari kita lanjutkan dengan beberapa teknik tambahan dan lebih lanjut dalam PostgreSQL yang dapat membantu Anda memahami lebih dalam lagi.

13. Partisi Tabel untuk Kinerja yang Lebih Baik

Partisi tabel adalah cara untuk membagi data besar ke dalam beberapa bagian berdasarkan aturan tertentu. Ini bermanfaat untuk mengelola data yang sangat besar, seperti data transaksi tahunan atau data historis.

A. Membuat Tabel Partisi Berdasarkan Tanggal

Misalnya, kita memiliki tabel sales yang sangat besar dan kita ingin mempartisinya berdasarkan tahun.

Buat tabel induk tanpa data:

sql
CREATE TABLE sales (
    sale_id SERIAL PRIMARY KEY,
    sale_date DATE NOT NULL,
    customer_id INT,
    amount NUMERIC(10, 2)
) PARTITION BY RANGE (sale_date);

Membuat partisi untuk tahun tertentu:

sql
CREATE TABLE sales_2023 PARTITION OF sales
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE sales_2024 PARTITION OF sales
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

Memasukkan data ke tabel sales secara otomatis akan menempatkan data ke partisi yang sesuai berdasarkan sale_date.

B. Manfaat dan Penggunaan Partisi

Partisi membuat query lebih cepat, terutama jika Anda sering melakukan pencarian berdasarkan rentang tanggal. PostgreSQL akan memilih partisi yang sesuai tanpa harus memindai seluruh tabel.

14. Menggunakan WINDOW Function untuk Analisis Data

Window function sangat berguna dalam analisis data, misalnya untuk menghitung peringkat, rata-rata bergerak, atau total kumulatif.

Contoh: Menghitung Peringkat Penjualan untuk Setiap Produk

Misalnya, kita ingin menampilkan peringkat penjualan untuk setiap produk berdasarkan total_sales.

sql
SELECT 
    product_id,
    product_name,
    SUM(quantity * price) AS total_sales,
    RANK() OVER (ORDER BY SUM(quantity * price) DESC) AS sales_rank
FROM order_items
JOIN products ON order_items.product_id = products.product_id
GROUP BY product_id, product_name
ORDER BY sales_rank;

Window function RANK() memberikan peringkat pada setiap produk berdasarkan total penjualan.

Contoh: Menghitung Rata-Rata Bergerak (Moving Average)

Moving average sering digunakan untuk analisis tren. Misalnya, kita ingin menghitung rata-rata penjualan dalam 7 hari terakhir untuk setiap hari.

sql
SELECT 
    sale_date,
    amount,
    AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg
FROM sales
ORDER BY sale_date;

15. Menggunakan JSON dan JSONB untuk Menyimpan Data Semi-Terstruktur

PostgreSQL mendukung tipe data JSON dan JSONB untuk menyimpan data semi-terstruktur. Tipe data ini sangat berguna untuk data fleksibel yang tidak selalu mengikuti skema tetap.

A. Menyimpan dan Mengakses Data JSON

Misalnya, kita memiliki tabel customers yang berisi kolom details untuk menyimpan informasi tambahan dalam format JSON.

sql
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    details JSONB
);

Kita bisa memasukkan data JSON seperti ini:

sql
INSERT INTO customers (name, details) 
VALUES ('John Doe', '{"age": 30, "preferences": {"color": "blue", "food": "pizza"}}');

B. Mengakses Data JSON

Untuk mengakses data JSON, kita bisa menggunakan operator -> atau ->>:

sql
-- Mendapatkan preferensi warna dari pelanggan
SELECT name, details->'preferences'->>'color' AS favorite_color
FROM customers;

C. Memperbarui Data JSON

sql
-- Menambahkan nomor telepon ke JSON
UPDATE customers
SET details = jsonb_set(details, '{phone}', '"123-456-7890"')
WHERE name = 'John Doe';

16. Menggunakan FULL TEXT SEARCH untuk Pencarian Teks

Full Text Search di PostgreSQL memungkinkan Anda melakukan pencarian teks pada kolom besar atau isi dokumen.

Tambahkan kolom untuk menyimpan indeks teks:

sql
ALTER TABLE products ADD COLUMN document TSVECTOR;

Isi kolom document dengan data teks yang ingin dicari:

sql
UPDATE products SET document = to_tsvector('english', product_name || ' ' || description);

Buat indeks pada kolom document:

sql
CREATE INDEX idx_products_document ON products USING GIN(document);

Lakukan pencarian dengan menggunakan to_tsquery:

sql
SELECT product_name
FROM products
WHERE document @@ to_tsquery('english', 'laptop & gaming');

Query ini akan mencari produk yang mengandung kata “laptop” dan “gaming” di kolom product_name atau description.

17. Menggunakan Subquery untuk Analisis Lebih Kompleks

Subquery memungkinkan Anda menulis query dalam query lain untuk analisis yang lebih kompleks.

Contoh: Menemukan Produk dengan Penjualan Lebih dari Rata-Rata

sql
SELECT product_id, product_name, total_sales
FROM (
    SELECT product_id, product_name, SUM(quantity * price) AS total_sales
    FROM order_items
    JOIN products ON order_items.product_id = products.product_id
    GROUP BY product_id, product_name
) AS sales_summary
WHERE total_sales > (SELECT AVG(total_sales) FROM (
    SELECT SUM(quantity * price) AS total_sales
    FROM order_items
    GROUP BY product_id
) AS avg_sales);

Query ini mencari produk dengan total penjualan lebih besar dari rata-rata total penjualan semua produk.

18. Menggunakan CASE Statement untuk Membuat Kolom Kondisional

CASE statement dapat digunakan untuk membuat kolom baru berdasarkan kondisi tertentu.

Contoh: Mengkategorikan Produk Berdasarkan Harga

sql
SELECT 
    product_name,
    price,
    CASE 
        WHEN price < 50 THEN 'Low'
        WHEN price BETWEEN 50 AND 100 THEN 'Medium'
        ELSE 'High'
    END AS price_category
FROM products;

19. Menggunakan LATERAL JOIN untuk Query yang Lebih Fleksibel

LATERAL JOIN memungkinkan Anda menggunakan subquery yang mengacu pada tabel sebelumnya dalam query. Ini sangat berguna untuk menghasilkan data dinamis.

Contoh: Menampilkan 3 Produk Terlaris untuk Setiap Kategori

sql
SELECT 
    categories.category_name,
    top_products.product_name,
    top_products.total_sales
FROM categories
JOIN LATERAL (
    SELECT product_name, SUM(quantity * price) AS total_sales
    FROM products
    JOIN order_items ON products.product_id = order_items.product_id
    WHERE products.category_id = categories.category_id
    GROUP BY product_name
    ORDER BY total_sales DESC
    LIMIT 3
) AS top_products ON true;

Dengan LATERAL, kita bisa mendapatkan 3 produk terlaris untuk setiap kategori dengan melakukan subquery di dalam join.

20. Menggunakan UPSERT untuk Menyimpan Data

UPSERT (insert or update) memungkinkan Anda menyimpan data tanpa khawatir duplikasi. Jika data sudah ada, maka data tersebut akan diperbarui.

sql
INSERT INTO products (product_id, product_name, price, stock)
VALUES (1, 'New Product', 99.99, 10)
ON CONFLICT (product_id) 
DO UPDATE SET 
    price = EXCLUDED.price,
    stock = EXCLUDED.stock;

Jika product_id = 1 sudah ada, perintah ini akan memperbarui harga dan stoknya, jika tidak, data baru akan dimasukkan.

Itulah beberapa query dan teknik lanjutan yang dapat Anda gunakan untuk latihan sehari-hari di PostgreSQL. Dengan mempraktikkan syntax ini, Anda akan memiliki pemahaman lebih dalam tentang berbagai fitur canggih di PostgreSQL, mulai dari partisi, window function, hingga JSON, indeks, dan lain-lain.

- - - - - - -

Tentu, berikut beberapa topik dan teknik lanjutan lain yang bisa Anda pelajari untuk memperdalam pemahaman Anda tentang PostgreSQL.

21. Menggunakan Recursive CTE (Common Table Expression) untuk Hierarki Data

Recursive CTE adalah cara yang efektif untuk menangani data yang berbentuk hierarki, seperti data organisasi atau struktur folder.

Contoh: Mencari Struktur Manajemen Karyawan

Misalnya, kita memiliki tabel employees dengan informasi karyawan dan manajernya.

sql
CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    employee_name VARCHAR(100),
    manager_id INT
);

Kita bisa menggunakan CTE rekursif untuk mencari struktur manajemen:

sql
WITH RECURSIVE management_hierarchy AS (
    SELECT employee_id, employee_name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL  -- Memulai dari karyawan tanpa manajer (misal: CEO)

    UNION ALL

    SELECT e.employee_id, e.employee_name, e.manager_id, mh.level + 1
    FROM employees e
    JOIN management_hierarchy mh ON e.manager_id = mh.employee_id
)
SELECT * FROM management_hierarchy
ORDER BY level, employee_id;

Query ini akan menunjukkan hierarki manajemen, dengan setiap karyawan dan tingkatannya dalam struktur.

22. Menggunakan Rollup untuk Menghitung Total Subtotal dalam Grouping

ROLLUP berguna untuk menghitung total subtotal dalam hasil agregasi.

Contoh: Menghitung Total Penjualan Berdasarkan Kategori dan Produk

Misalkan kita ingin menghitung penjualan per kategori, per produk, dan total keseluruhan:

sql
SELECT category_name, product_name, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY ROLLUP (category_name, product_name)
ORDER BY category_name, product_name;

Dengan ROLLUP, kita akan mendapatkan total untuk setiap kategori dan total keseluruhan.

23. Menggunakan ARRAY untuk Menyimpan Daftar Nilai

PostgreSQL mendukung tipe data ARRAY, yang memungkinkan Anda menyimpan daftar nilai dalam satu kolom.

A. Membuat Tabel dengan Kolom ARRAY

sql
CREATE TABLE survey_responses (
    respondent_id SERIAL PRIMARY KEY,
    answers TEXT[]
);

B. Menambahkan Data ke Kolom ARRAY

sql
INSERT INTO survey_responses (answers) 
VALUES (ARRAY['Yes', 'No', 'Maybe']);

C. Mengakses dan Memanipulasi Data ARRAY

Mengambil elemen tertentu dari array:

sql
SELECT answers[1] AS first_answer FROM survey_responses;

Menambahkan elemen ke array:

sql
UPDATE survey_responses
SET answers = array_append(answers, 'Definitely')
WHERE respondent_id = 1;

24. Menggunakan HSTORE untuk Menyimpan Key-Value Pairs

HSTORE adalah tipe data untuk menyimpan pasangan key-value. Cocok untuk data yang fleksibel dengan banyak atribut opsional.

A. Membuat Tabel dengan Kolom HSTORE

sql
CREATE EXTENSION IF NOT EXISTS hstore;

CREATE TABLE product_attributes (
    product_id SERIAL PRIMARY KEY,
    attributes HSTORE
);

B. Menyimpan dan Mengambil Data HSTORE

Menambahkan data ke kolom HSTORE:

sql
INSERT INTO product_attributes (attributes) 
VALUES ('color => "red", size => "medium"');

Mengambil nilai berdasarkan key:

sql
SELECT attributes->'color' AS color FROM product_attributes;

C. Memperbarui atau Menambahkan Key-Value dalam HSTORE

sql
UPDATE product_attributes
SET attributes = attributes || 'material => "cotton"'
WHERE product_id = 1;

25. Menangani Transaksi dengan BEGIN, COMMIT, dan ROLLBACK

PostgreSQL mendukung transaksi yang memungkinkan Anda mengelompokkan beberapa perintah menjadi satu unit atomik.

Contoh: Menggunakan Transaksi

sql
BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

COMMIT;

Jika terjadi kesalahan selama transaksi, Anda dapat melakukan ROLLBACK untuk membatalkan semua perubahan.

sql
BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

ROLLBACK;

26. Menulis Fungsi dan Stored Procedures di PostgreSQL

PostgreSQL mendukung penulisan fungsi (function) dan prosedur tersimpan (stored procedure) untuk memperluas kemampuan database.

A. Membuat Fungsi untuk Menghitung Diskon Otomatis

sql
CREATE OR REPLACE FUNCTION calculate_discount(price NUMERIC, discount_rate NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    RETURN price - (price * discount_rate / 100);
END;
$$ LANGUAGE plpgsql;

Menggunakan fungsi:

sql
SELECT calculate_discount(100, 10); -- Hasilnya 90

B. Membuat Stored Procedure untuk Transaksi Multi-Tabel

Stored procedure memungkinkan operasi kompleks yang melibatkan banyak tabel.

sql
CREATE OR REPLACE PROCEDURE transfer_funds(from_account INT, to_account INT, amount NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE accounts SET balance = balance - amount WHERE account_id = from_account;
    UPDATE accounts SET balance = balance + amount WHERE account_id = to_account;
END;
$$;

Menjalankan prosedur:

sql
CALL transfer_funds(1, 2, 500);

27. Menggunakan EXPLAIN dan ANALYZE untuk Mengoptimalkan Query

EXPLAIN dan ANALYZE adalah perintah yang memungkinkan Anda melihat bagaimana PostgreSQL menjalankan query. Sangat membantu untuk mengidentifikasi bottleneck kinerja.

sql
EXPLAIN ANALYZE
SELECT * FROM sales WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31';

Output dari EXPLAIN ANALYZE akan menunjukkan apakah PostgreSQL menggunakan indeks, jumlah waktu yang dibutuhkan untuk setiap operasi, dan lainnya. Ini membantu Anda mengoptimalkan query.

28. Menggunakan Index untuk Meningkatkan Kinerja Query

Indeks adalah struktur data yang mempercepat pencarian data.

A. Membuat Indeks B-TREE

Indeks B-TREE adalah indeks default di PostgreSQL dan bagus untuk pencarian rentang dan pengurutan.

sql
CREATE INDEX idx_sales_date ON sales(sale_date);

B. Indeks GIN untuk Data JSONB

GIN (Generalized Inverted Index) sering digunakan untuk data JSONB dan full-text search.

sql
CREATE INDEX idx_products_attributes ON products USING GIN(attributes);

29. Membuat View untuk Mempermudah Query Kompleks

View adalah query yang disimpan dan bisa digunakan seperti tabel. View memudahkan untuk menyederhanakan query yang kompleks.

Contoh: Membuat View Penjualan Bulanan

sql
CREATE VIEW monthly_sales AS
SELECT 
    DATE_TRUNC('month', sale_date) AS sale_month,
    SUM(amount) AS total_sales
FROM sales
GROUP BY sale_month;

Anda bisa menggunakannya seperti tabel biasa:

sql
SELECT * FROM monthly_sales WHERE sale_month = '2023-01-01';

30. Menggunakan TRIGGERS untuk Memantau dan Mengontrol Perubahan Data

Trigger memungkinkan Anda menjalankan kode secara otomatis sebelum atau setelah event tertentu terjadi pada tabel, seperti INSERT, UPDATE, atau DELETE.

A. Membuat Trigger untuk Melacak Update

Misalnya, kita ingin melacak setiap perubahan pada tabel employees dengan membuat log perubahan.

Buat tabel log:

sql
CREATE TABLE employee_changes (
    change_id SERIAL PRIMARY KEY,
    employee_id INT,
    old_salary NUMERIC,
    new_salary NUMERIC,
    change_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Buat fungsi trigger:

sql
CREATE OR REPLACE FUNCTION log_salary_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.salary IS DISTINCT FROM NEW.salary THEN
        INSERT INTO employee_changes (employee_id, old_salary, new_salary)
        VALUES (OLD.employee_id, OLD.salary, NEW.salary);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Buat trigger pada tabel employees:

sql
CREATE TRIGGER salary_update_trigger
AFTER UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION log_salary_changes();

Dengan trigger ini, setiap kali ada perubahan salary pada tabel employees, perubahan tersebut akan dicatat di tabel employee_changes.

Dengan mempelajari dan mempraktikkan konsep-konsep di atas, Anda akan semakin menguasai PostgreSQL, termasuk teknik-teknik pengelolaan data yang lebih canggih seperti penggunaan array, JSONB, hstore, fungsi, transaksi, dan trigger. Teknik ini akan sangat membantu Anda dalam mengelola database dengan efisien dan mendukung analisis data yang kompleks.

Story Julfahmi