QUALIFY: Pernyataan Pemfilteran SQL yang Tak Anda Sangka Anda Butuhkan

Konsep Dasar dan Sintaks Klausa SQL QUALIFY

QUALIFY adalah klausa yang digunakan untuk memfilter hasil dari fungsi jendela (window function). Karena itu, agar dapat menggunakan klausa QUALIFY dengan benar, harus ada setidaknya satu fungsi WINDOW pada daftar SELECT atau klausa QUALIFY – hanya baris yang dievaluasi TRUE pada ekspresi boolean yang akan dikembalikan.

Tip: Lihat Cheat Sheet SQL Window Functions.

Jika tidak ada klausa QUALIFY, pemfilteran pada hasil fungsi WINDOW memerlukan penumpukan (nesting). Pengembang SQL umumnya menyarankan untuk menghindari kueri bertingkat sebisa mungkin karena membuat kode kurang mudah dibaca manusia serta meningkatkan kompleksitas debugging dan kolaborasi.

Dengan kata lain, gagasan utama di balik klausa QUALIFY adalah menyederhanakan kueri yang membutuhkan pemfilteran pada hasil fungsi window; artinya QUALIFY dievaluasi setelah fungsi window dihitung.

Berikut urutan eksekusi yang umum untuk kueri dengan klausa pernyataan QUALIFY:

• FROM
• WHERE
• GROUP BY dan Agregasi
• HAVING
• WINDOW
• QUALIFY
• DISTINCT
• ORDER BY
• LIMIT

Sintaks umum dari pernyataan QUALIFY adalah sebagai berikut:

QUALIFY <predicate>Didukung oleh

Dalam bentuk umumnya, tampilannya akan seperti ini:

SELECT <column_list>
  FROM <data_source>
  [GROUP BY ...]
  [HAVING ...]
  QUALIFY <predicate>
  [ ... ]Didukung oleh

Catatan: <predicate> adalah ekspresi yang digunakan untuk memfilter hasil setelah agregasi dan fungsi window dihitung.

Kapan Menggunakan SQL QUALIFY

Klausa pernyataan QUALIFY mirip dengan klausa HAVING dalam hal menghindari kebutuhan akan subkueri untuk melakukan pemfilteran. Misalnya, Anda dapat menggunakan QUALIFY untuk memfilter hasil fungsi analitik – fungsi yang digunakan untuk menghitung nilai agregat berdasarkan sekelompok baris.

Agar lebih jelas kapan tepatnya Anda akan menggunakan QUALIFY, berikut skenario singkat:

Misalkan Anda bekerja sebagai data scientist, dan seorang pemangku kepentingan meminta informasi login terakhir untuk setiap pelanggan. Kueri awal Anda mungkin terlihat seperti ini:

SELECT 
  user_id, 
  ip, 
  country_code,
  os,
  RANK() over (
    PARTITION BY user_id ORDER BY log_datetime DESC
  ) as previous_logins
FROM login_logs
WHERE TRUE Didukung oleh

Ini awal yang baik, tetapi belum memenuhi permintaan pemangku kepentingan karena akan mengembalikan semua login, bukan yang terbaru saja. Untuk mengembalikan login terakhir, kita perlu menambahkan filter.

Pendatang baru di SQL mungkin terjebak mencoba memfilter ini menggunakan klausa WHERE, seperti berikut:

SELECT 
  user_id, 
  ip, 
  country_code,
  os,
  RANK() over (
    PARTITION BY user_id ORDER BY log_datetime DESC
  ) AS previous_logins
FROM login_logs
WHERE TRUE 
  AND last_login = 1Didukung oleh

Sayangnya, kode ini tidak akan dieksekusi; hal yang sama terjadi jika Anda mencoba GROUP BY dan HAVING.

Alasan hal ini terjadi bergantung pada urutan operasi yang kita bahas di bagian sebelumnya.

WHERE, GROUP BY, dan HAVING semuanya dievaluasi sebelum fungsi WINDOW; artinya, mereka tidak dapat memfilter fungsi WINDOW karena belum mengetahui keberadaannya – fungsi WINDOW belum dievaluasi saat ketiga klausa tersebut dijalankan.

Salah satu cara agar filter dilakukan setelah fungsi WINDOW adalah menggunakan Common Table Expression (CTE).

Berikut tampilan kodenya:

WITH 
logins AS (
  SELECT 
      user_id, 
      ip, 
      country_code,
      os,
      RANK() OVER (
        PARTITION BY user_id ORDER BY log_datetime DESC
      ) AS previous_logins
  FROM login_logs
  WHERE TRUE
)

SELECT user_id, ip, country_code, os 
FROM logins
WHERE previous_logins = 1Didukung oleh

Secara teknis, kode ini valid – Ia akan dieksekusi dengan baik dan mengembalikan hasil yang diminta pemangku kepentingan Anda.

Namun, kita harus memperkenalkan dua kueri, dan ada beberapa baris kode tambahan, yang dapat menjadi berulang jika Anda sering mengulangi proses ini.

Solusi terbaik untuk masalah ini adalah menggunakan QUALIFY.

Berikut tampilan kodenya:

-- Starter code from @Jiho Choi on StackOverflow 
SELECT 
  user_id, 
  ip, 
  country_code,
  os,
  RANK() over (
    PARTITION BY user_id ORDER BY log_datetime DESC
  ) as previous_logins
FROM login_logs
WHERE TRUE 
QUALIFY previous_logins = 1Didukung oleh

Solusi ini berfungsi karena klausa QUALIFY dievaluasi setelah fungsi WINDOW dalam urutan operasi SQL, yang berarti ia mengetahui keberadaan fungsi tersebut sehingga dapat memfilternya dalam kueri yang sama.

Anda mungkin bertanya-tanya apa manfaat QUALIFY selain menulis lebih sedikit baris kode, dan jawabannya adalah tidak ada manfaat kinerja yang berarti.

Solusi CTE yang kita gunakan dan solusi QUALIFY keduanya dieksekusi dalam rentang waktu yang serupa, jadi kita tidak bisa mengatakan ada banyak peningkatan kinerja dari solusi QUALIFY.

Manfaat utamanya adalah peningkatan kenyamanan; kodenya lebih sedikit dan lebih mudah dibaca.

Membandingkan SQL QUALIFY dengan Metode Pemfilteran Lain

Saat ini, Anda memahami bahwa klausa pernyataan QUALIFY adalah metode pemfilteran lain yang tersedia di SQL. Anda juga tahu kapan waktu terbaik untuk menggunakan klausa QUALIFY.

Lalu apa perbedaan antara QUALIFY dan metode pemfilteran lainnya?

Mari kita rekap setiap metode pemfilteran.

Klausa WHERE

WHERE digunakan untuk memfilter rekaman dalam tabel; artinya ia menerapkan operasi pada baris. Kita menggunakannya saat ingin mengekstrak rekaman yang memenuhi kondisi tertentu. Misalnya, kita mungkin ingin memfilter dataset berdasarkan usia seseorang. Berikut tampilan kodenya:

SELECT name, gender, height, weight, age 
FROM gym_members_info 
WHERE age < 23; Didukung oleh

Berikut hal-hal utama yang perlu diperhatikan tentang klausa WHERE.

1. Digunakan untuk memfilter rekaman berdasarkan kondisi tertentu.
2. Operasi diterapkan pada baris.
3. Anda dapat menggunakan klausa WHERE tanpa klausa GROUP BY
4. WHERE dapat digunakan dengan pernyataan SELECT, UPDATE, DELETE.

Mari bandingkan dengan klausa HAVING.

Klausa HAVING

HAVING digunakan untuk memfilter rekaman dari grup yang didefinisikan oleh klausa GROUP BY berdasarkan kondisi tertentu. Jadi, perbedaan paling mencolok antara WHERE dan HAVING adalah HAVING memerlukan keberadaan klausa GROUP BY agar dapat dievaluasi dengan sukses.

Manfaat utamanya adalah Anda dapat menerapkan klausa HAVING pada subset grup teragregasi, yang tidak mungkin dilakukan dalam blok WHERE.

Contohnya:

SELECT gender, AVG(height) 
FROM gym_members_info
GROUP BY gender 
HAVING AVG(height) > 170 Didukung oleh

Kode di atas akan mengembalikan kelompok gender di mana tinggi badan rata-ratanya lebih besar dari 170 sentimeter; dengan kata lain, HAVING menerapkan operasi pada kolom.

Hal lain yang perlu diketahui adalah HAVING hanya dapat digunakan dengan pernyataan SELECT, tidak seperti WHERE.

Klausa QUALIFY

QUALIFY ekuivalen dengan HAVING, kecuali bahwa ia melakukan pemfilteran pada hasil fungsi WINDOW, sedangkan HAVING memfilter pada fungsi agregat dan klausa GROUP BY – pelajari lebih lanjut tentang Cara menggunakan GROUP BY.

Jika kita ingin mengetahui informasi tentang orang tertinggi dalam setiap kelompok usia, kita dapat menggunakan fungsi WINDOW untuk melakukan perhitungan dan klausa QUALIFY untuk memfilter hasilnya sehingga kita hanya menemukan orang tertinggi dari setiap kelompok usia.

Berikut tampilan kodenya:

SELECT name, gender, height, weight, age 
RANK() over (
    PARTITION BY age ORDER BY height DESC
  ) as ranked_ages
FROM gym_members_info
WHERE TRUE
QUALIFY ranked_ages = 1Didukung oleh

Intinya, klausa QUALIFY menentukan ekspresi kondisional yang digunakan untuk memfilter keluaran dari fungsi analitik berurutan yang telah dihitung sesuai dengan kriteria yang ditentukan pengguna.

Kesimpulan dan Studi Lanjutan

SQL berperan besar dalam alur kerja sains data yang umum; mengetahui cara menerapkan operasi yang berguna di SQL, seperti klausa QUALIFY, sangat membantu karena memungkinkan Anda menulis kode yang lebih bersih, sehingga lebih menyenangkan bagi orang lain untuk berkolaborasi dengan Anda.

Jika Anda ingin menguasai keterampilan SQL, kami sarankan Anda melihat sumber daya di bawah ini:

• Cheat Sheet Dasar-dasar SQL
• Kursus Introduction to SQL
• Skill track SQL Fundamentals