Teknik SQL Tingkat Lanjut

Structured Query Language (SQL) adalah fondasi dari sistem manajemen basis data relasional dan harus dimiliki oleh siapa saja yang mengelola data. Namun, meskipun SQL dasar seperti SELECT, INSERT, atau UPDATE dan DELETE dapat melakukan aktivitas kerangka kerja, SQL tingkat lanjut dapat melakukan presentasi informasi yang kompleks, pertanyaan yang sangat tersusun, dan penggambaran visual. Pada artikel ini, kita akan mengeksplorasi teknik SQL yang kuat dan canggih untuk membawa kueri database Anda ke tingkat berikutnya.

1. Fungsi Jendela

Anda juga dapat melakukan penghitungan di sekumpulan baris tabel yang terkait dengan baris saat ini menggunakan fungsi jendela. Sementara fungsi agregat menciutkan baris menjadi satu hasil tunggal, fungsi jendela mempertahankan struktur baris.

Contoh:

SELECT
employee_id,
department_id,
salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank_in_department
FROM employees;

Dengan kueri ini, setiap karyawan diberi peringkat sesuai dengan gaji mereka di departemen yang sama.

Seperti ROW_NUMBER (), RANK (), DENSE_RANK (), NTILE () Fungsi jendela sangat berguna untuk pemeringkatan, penomoran halaman, dan rata-rata bergerak.

sumber: alphaservesp.com

2. CTE (Ekspresi Tabel Umum)

CTE menguraikan kueri yang kompleks menjadi komponen yang lebih sederhana dan mudah dipahami. Ini didefinisikan menggunakan klausa WITH, dan lebih mudah dibaca serta di-debug dibandingkan dengan subkueri bersarang.

Contoh:

WITH SalesByCategory AS (
SELECT
category_id,
SUM(sales_amount) AS total_sales
FROM sales
GROUP BY category_id
)
SELECT
category_id,
total_sales
FROM SalesByCategory
WHERE total_sales > 10000;

Kueri di bawah ini akan memberikan total penjualan per kategori dan memfilter kategori dengan penjualan lebih besar dari 10.000.

CTE juga dapat merujuk pada kueri rekursif seperti menelusuri data hirarkis.

3. Memutar dan Tidak Memutar

Pivot dan Unpivot (dalam SQL) Secara sederhana: Pivot: Mengubah Baris menjadi Kolom. Teknik ini sangat diperlukan untuk mengubah kumpulan data untuk tujuan pelaporan dan analisis.

Contoh (Pivot):

SELECT *
FROM (
SELECT product_id, month, sales
FROM sales_data
) AS source
PIVOT (
SUM(sales) FOR month IN ([Jan], [Feb], [Mar])
) AS pivoted_data;

Kueri ini mem-pivot data penjualan bulanan ke dalam format berbasis kolom.

4. Gabungan Tingkat Lanjut

Menjelajahi beberapa gabungan tingkat lanjut seperti gabungan sendiri, gabungan silang, dan gabungan lateral dapat membantu Anda menavigasi hubungan yang rumit di antara data Anda.

Contoh (Gabungan Lateral):

SELECT
c.customer_id,
o.latest_order_date
FROM customers c
CROSS APPLY (
SELECT TOP 1 order_date AS latest_order_date
FROM orders o
WHERE o.customer_id = c.customer_id
ORDER BY order_date DESC
) o;

Operator CROSS APPLY memungkinkan subkueri yang berkorelasi, yang sangat berguna untuk mengakses data terkait secara efisien.

5. Mengoptimalkan Kueri dan Indeks

Indeks membuat kueri untuk mendapatkan data lebih cepat dengan mengurangi waktu untuk mendapatkan data. Namun, penggunaannya harus direncanakan dengan hati-hati, karena pengindeksan yang tidak perlu dapat memperlambat operasi penulisan.

Teknik

JELASKAN atau JELASKAN RENCANA untuk penjelasan kueri.
Mencegah pemindaian tabel penuh dengan pengindeksan.
Menulis ulang sedemikian rupa sehingga kolom yang diindeks mendapatkan bobot paling besar dalam perencanaan kueri.

Contoh:

CREATE INDEX idx_employee_department ON employees(department_id);

Indeks ini mempercepat pemfilteran kueri (atau penggabungan pada) department_id.

6. Menangani Data Hirarkis

Data hirarkis atau terstruktur pohon - seperti bagan organisasi - pada dasarnya membutuhkan pendekatan yang lebih kompleks seperti CTE rekursif atau model daftar kedekatan.

Contoh (CTE rekursif):

WITH RecursiveOrgChart AS (
SELECT
employee_id,
manager_id,
1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT
e.employee_id,
e.manager_id,
roc.level + 1
FROM employees e
INNER JOIN RecursiveOrgChart roc ON e.manager_id = roc.employee_id
)
SELECT *
FROM RecursiveOrgChart;

Karyawan dengan hubungan manajerial membentuk hierarki, yang dibangun oleh kueri ini.

7. Penanganan Data JSON dan XML

Basis data modern dapat menangani data terstruktur dan semi-terstruktur dalam format JSON atau XML. Mengekstrak dan memanipulasi data JSON menjadi lebih mudah dengan fungsi-fungsi seperti JSON_VALUE dan JSON_QUERY.

Contoh:

SELECT
JSON_VALUE(order_details, '$.product_name') AS product_name,
JSON_VALUE(order_details, '$.quantity') AS quantity
FROM orders;

Kueri ini mengekstrak bidang tertentu dari data JSON yang disimpan dalam sebuah kolom.

Kesimpulan

Menguasai teknik SQL tingkat lanjut memungkinkan Anda menangani skenario data yang kompleks, mengoptimalkan kinerja, dan membuat analisis yang mendalam. Dengan menggabungkan fungsi jendela, CTE, pivoting, dan pengindeksan ke dalam keahlian Anda, Anda dapat membuka potensi penuh SQL dan meningkatkan kemampuan manajemen data Anda.

Kembali ke>>>> Memahami SQL: Bahasa Basis Data