Tips dan Trik SQL

(github.com/ben-n93)

7 poin oleh GN⁺ 2024-09-26 | 2 komentar | Bagikan ke WhatsApp

Daftar isi

Pemformatan/Keterbacaan

Gunakan koma di awal saat memisahkan field
Gunakan nilai dummy di klausa WHERE
Indentasi kode yang tepat
Pertimbangkan CTE saat menulis kueri yang kompleks

Fitur yang berguna

Ubah tipe data dengan operator ::
Manfaatkan anti join
Gunakan QUALIFY untuk memfilter window function
Bisa menggunakan posisi kolom untuk GROUP BY

Jebakan yang perlu dihindari

Hati-hati saat menggunakan NOT IN dengan nilai NULL
Ubah nama field hasil perhitungan untuk menghindari ambiguitas
Jelaskan setiap kolom berasal dari tabel mana
Pahami urutan eksekusi
Tambahkan komentar pada kode
Baca dokumentasi secara menyeluruh

Pemformatan/Keterbacaan

Gunakan koma di awal saat memisahkan field

Saat memisahkan field di klausa SELECT, penggunaan koma di awal dapat membantu membedakan kolom baru dengan jelas
Koma di awal memberi petunjuk visual yang memudahkan untuk melihat apakah ada koma yang terlewat

SELECT
  employee_id,
  employee_name,
  job,
  salary
FROM employees;

Gunakan nilai dummy di klausa WHERE

Gunakan nilai dummy di klausa WHERE agar kondisi bisa ditambah dan dihapus secara dinamis

SELECT *
FROM employees
WHERE 1=1 -- nilai dummy
  AND job IN ('Clerk', 'Manager')
  AND dept_no != 5;

Indentasi kode yang tepat

Indentasi membuat kode lebih mudah dibaca dan memudahkan rekan kerja maupun diri Anda di masa depan untuk memahaminya

-- contoh buruk:
SELECT
  timeslot_date,
  timeslot_channel,
  overnight_fta_share,
  IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) > 7,
    LAG(overnight_fta_share, 1) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),
    NULL) AS C7_fta_share,
  IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) >= 29,
    LAG(overnight_fta_share, 2) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),
    NULL) AS C28_fta_share
FROM timeslot_data;

-- contoh baik:
SELECT
  timeslot_date,
  timeslot_channel,
  overnight_fta_share,
  IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) > 7,
    LAG(overnight_fta_share, 1) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),
    NULL) AS C7_fta_share,
  IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) >= 29,
    LAG(overnight_fta_share, 2) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),
    NULL) AS C28_fta_share
FROM timeslot_data;

Pertimbangkan CTE saat menulis kueri yang kompleks

Daripada menumpuk inline view, gunakan common table expression (CTE) untuk meningkatkan keterbacaan dan kerapian kode

-- menggunakan inline view:
SELECT
  vhs.movie,
  vhs.vhs_revenue,
  cs.cinema_revenue
FROM
  (SELECT
    movie_id,
    SUM(ticket_sales) AS cinema_revenue
  FROM tickets
  GROUP BY movie_id) AS cs
INNER JOIN
  (SELECT
    movie,
    movie_id,
    SUM(revenue) AS vhs_revenue
  FROM blockbuster
  GROUP BY movie, movie_id) AS vhs
ON cs.movie_id = vhs.movie_id;

-- menggunakan CTE:
WITH cinema_sales AS (
  SELECT
    movie_id,
    SUM(ticket_sales) AS cinema_revenue
  FROM tickets
  GROUP BY movie_id
),
vhs_sales AS (
  SELECT
    movie,
    movie_id,
    SUM(revenue) AS vhs_revenue
  FROM blockbuster
  GROUP BY movie, movie_id
)
SELECT
  vhs.movie,
  vhs.vhs_revenue,
  cs.cinema_revenue
FROM cinema_sales AS cs
INNER JOIN vhs_sales AS vhs
ON cs.movie_id = vhs.movie_id;

Fitur yang berguna

Ubah tipe data dengan operator `::`

Di beberapa RDBMS, operator :: dapat digunakan untuk mengonversi nilai ke tipe data lain

SELECT CAST('5' AS INTEGER); -- menggunakan fungsi CAST
SELECT '5'::INTEGER; -- menggunakan sintaks ::

Manfaatkan anti join

Anti join sangat berguna saat ingin mengembalikan baris yang hanya ada di satu tabel
Subkueri juga bisa digunakan, tetapi umumnya anti join lebih cepat

-- anti join:
SELECT
  video_content.*
FROM video_content
LEFT JOIN archive
ON video_content.series_id = archive.series_id
WHERE archive.series_id IS NULL;

-- subkueri:
SELECT
  *
FROM video_content
WHERE series_id NOT IN (SELECT DISTINCT series_id FROM archive);

-- subkueri berkorelasi:
SELECT
  *
FROM video_content
WHERE NOT EXISTS (
  SELECT 1
  FROM archive a
  WHERE a.series_id = vc.series_id
);

-- EXCEPT:
SELECT series_id
FROM video_content
EXCEPT
SELECT series_id
FROM archive;

Gunakan `QUALIFY` untuk memfilter window function

QUALIFY dapat digunakan untuk memfilter hasil window function
Berguna untuk mengurangi jumlah baris kode

-- menggunakan QUALIFY:
SELECT
  product,
  market,
  SUM(revenue) AS market_revenue
FROM sales
GROUP BY product, market
QUALIFY DENSE_RANK() OVER (PARTITION BY product ORDER BY SUM(revenue) DESC) <= 10
ORDER BY product, market_revenue;

-- tanpa QUALIFY:
SELECT
  product,
  market,
  market_revenue
FROM (
  SELECT
    product,
    market,
    SUM(revenue) AS market_revenue,
    DENSE_RANK() OVER (PARTITION BY product ORDER BY SUM(revenue) DESC) AS market_rank
  FROM sales
  GROUP BY product, market
)
WHERE market_rank <= 10
ORDER BY product, market_revenue;

Bisa menggunakan posisi kolom untuk `GROUP BY`

Anda dapat menggunakan posisi kolom, bukan nama kolom, untuk GROUP BY atau ORDER BY
Berguna untuk kueri sementara, tetapi dalam kode produksi sebaiknya selalu merujuk ke nama kolom

SELECT
  dept_no,
  SUM(salary) AS dept_salary
FROM employees
GROUP BY 1 -- dept_no adalah kolom pertama di klausa SELECT
ORDER BY 2 DESC;

Jebakan yang perlu dihindari

Hati-hati saat menggunakan `NOT IN` dengan nilai `NULL`

NOT IN tidak akan bekerja jika ada nilai NULL
Sebagai gantinya, gunakan NOT EXISTS

INSERT INTO departments (id)
VALUES (1), (2), (NULL);

-- tidak bekerja karena nilai NULL
SELECT *
FROM employees
WHERE department_id NOT IN (SELECT DISTINCT id FROM departments);

-- solusi
SELECT *
FROM employees e
WHERE NOT EXISTS (
  SELECT 1
  FROM departments d
  WHERE d.id = e.department_id
);

Ubah nama field hasil perhitungan untuk menghindari ambiguitas

Mengganti nama field hasil perhitungan dengan nama kolom yang sudah ada dapat menyebabkan perilaku yang tidak terduga

INSERT INTO products (product, revenue)
VALUES ('Shark', 100), ('Robot', 150), ('Alien', 90);

-- window function memberi peringkat produk 'Robot' di posisi 1
SELECT
  product,
  CASE product WHEN 'Robot' THEN 0 ELSE revenue END AS revenue,
  RANK() OVER (ORDER BY revenue DESC)
FROM products;

Jelaskan setiap kolom berasal dari tabel mana

Dalam kueri yang kompleks, menjelaskan setiap kolom berasal dari tabel mana akan memudahkan pelacakan masalah

SELECT
  vc.video_id,
  vc.series_name,
  metadata.season,
  metadata.episode_number
FROM video_content AS vc
INNER JOIN video_metadata AS metadata
ON vc.video_id = metadata.video_id;

Pahami urutan eksekusi

Saran terpenting bagi orang yang sedang belajar SQL adalah memahami urutan eksekusi
Jika memahami urutan eksekusi, cara menulis kueri akan berubah sepenuhnya

Tambahkan komentar pada kode

Saat menulis kode, tambahkan komentar yang menjelaskan alasannya
Rekan kerja dan diri Anda di masa depan akan berterima kasih

SELECT
  video_content.*
FROM video_content
LEFT JOIN archive -- CMS baru tidak dapat menangani format video arsip
ON video_content.series_id = archive.series_id
WHERE archive.series_id IS NULL;

Baca dokumentasi secara menyeluruh

Membaca dokumentasi secara menyeluruh dapat membantu menghindari masalah yang tidak terduga
Hanya butuh beberapa menit untuk membaca dokumentasi, dan itu bisa membantu menyelesaikan masalah yang tak terduga

-- Jika membaca dokumentasi lebih lanjut, masalah NULL ini bisa diatasi
SELECT COALESCE(GREATEST(signup_date, consumption_date), signup_date, consumption_date);

-- fungsi GREATEST_IGNORE_NULLS juga bisa digunakan
SELECT GREATEST_IGNORE_NULLS(signup_date, consumption_date);

Ringkasan GN⁺

Artikel ini memberikan berbagai tips dan trik untuk menulis SQL dengan lebih efisien dan mudah dibaca
Ada banyak informasi yang bermanfaat baik bagi pemula SQL maupun analis data yang sudah berpengalaman
Khususnya, penggunaan CTE saat menulis kueri kompleks, pemanfaatan anti join, dan penggunaan QUALIFY akan sangat membantu dalam praktik kerja
Penting untuk membiasakan diri memahami urutan eksekusi SQL, menambahkan komentar pada kode, dan membaca dokumentasi dengan teliti
Alat lain dengan fungsi serupa antara lain PostgreSQL, MySQL, dan Oracle

2 komentar

hiyama 2024-09-26

Semua koma di awal pada postingan ini tampaknya ditulis sebagai koma di akhir. Pada teks asli, itu ditulis dengan koma di awal.

-- Good:  
SELECT   
timeslot_date  
, timeslot_channel   
, overnight_fta_share  
, IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) > 7, -- First argument of IFF.  
	LAG(overnight_fta_share, 1) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity), -- Second argument of IFF.  
		NULL) AS C7_fta_share -- Third argument of IFF.  
, IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) >= 29,   
		LAG(overnight_fta_share, 2) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),   
			NULL) AS C28_fta_share  
FROM timeslot_data  
;

GN⁺ 2024-09-26

Komentar Hacker News

Perlunya memahami server DB dengan baik dan sering memeriksa rencana kueri
- EXISTS sering kali lebih cepat daripada IN
- NOT EXISTS dan EXCEPT berperilaku berbeda dalam penanganan nilai NULL
- Disarankan menggunakan kolom subkueri alih-alih join tabel
- Perlu menghindari table scan dan menambahkan indeks
- Saat memfilter ekspresi, dapat menggunakan kolom terhitung dan indeks
- UNION ALL bisa lebih cepat daripada OR
- Urutan pemfilteran dapat dipaksa melalui JOIN subkueri
Tips untuk pekerjaan stored procedure yang kompleks
- Salin tabel permanen ke tabel sementara dan filter hanya baris yang diperlukan
- Manipulasi tabel sementara
- Perbarui tabel permanen di dalam transaksi, lalu rollback jika terjadi kesalahan
- Perlu berhati-hati saat bekerja dengan tabel jarak jauh; disarankan menyalinnya ke tabel sementara terlebih dahulu
- Rencana kueri bisa membingungkan, jadi pecah pekerjaan menjadi langkah-langkah kecil
- Selalu periksa rencana kueri
Pendapat terkait keterbacaan kode
- Dua contoh pertama mengorbankan keterbacaan demi kemudahan penulisan
- Contoh terakhir tidak banyak terbantu oleh indentasi
Saran penggunaan sintaks FROM-first dan piping pada SQL
- Pengalaman menggunakan Kusto query language merupakan lompatan besar
Tips terkait Anti Join
- Disarankan menggunakan EXISTS, karena menguntungkan untuk memeriksa keberadaan baris pada subkueri berbasis kondisi
Keuntungan penggunaan koma di awal pada pernyataan SELECT
- Memungkinkan komentar pada setiap baris
- Keterbacaan meningkat melalui indentasi kode
Di MSSQL, saat menggunakan komentar, disarankan memakai /* */ alih-alih --
- Karena query store menyimpan kueri tanpa line break
Disarankan menggunakan window function
Perdebatan tentang penggunaan 1=1 di klausa WHERE
Pengenalan AI2sql
- Dapat membuat kueri SQL dari prompt bahasa Inggris biasa
- Berguna saat menulis kueri yang kompleks

Tips dan Trik SQL

Daftar isi

Pemformatan/Keterbacaan

Fitur yang berguna

Jebakan yang perlu dihindari

Pemformatan/Keterbacaan

Gunakan koma di awal saat memisahkan field

Gunakan nilai dummy di klausa WHERE

Indentasi kode yang tepat

Pertimbangkan CTE saat menulis kueri yang kompleks

Fitur yang berguna

Ubah tipe data dengan operator ::

Manfaatkan anti join

Gunakan QUALIFY untuk memfilter window function

Bisa menggunakan posisi kolom untuk GROUP BY

Jebakan yang perlu dihindari

Hati-hati saat menggunakan NOT IN dengan nilai NULL

Ubah nama field hasil perhitungan untuk menghindari ambiguitas

Jelaskan setiap kolom berasal dari tabel mana

Pahami urutan eksekusi

Tambahkan komentar pada kode

Baca dokumentasi secara menyeluruh

Ringkasan GN⁺

Bacaan terkait

2 komentar

Komentar Hacker News

Ubah tipe data dengan operator `::`

Gunakan `QUALIFY` untuk memfilter window function

Bisa menggunakan posisi kolom untuk `GROUP BY`

Hati-hati saat menggunakan `NOT IN` dengan nilai `NULL`