Snowflake QUALIFY untuk Pemula: Apa Itu dan Cara Menggunakannya

Sebagai profesional data yang bekerja dengan Snowflake, Anda mungkin perlu memfilter hasil kueri berdasarkan fungsi jendela. Secara tradisional, ini memerlukan subkueri atau logika pemfilteran yang kompleks. Namun, Snowflake menyediakan alternatif yang kuat: klausa QUALIFY. 

Dalam panduan ini, saya akan menguraikan cara menggunakan QUALIFY untuk menyederhanakan pemfilteran, dengan contoh langkah demi langkah dan praktik terbaik.

Apa itu Klausa QUALIFY di Snowflake?

Klausa QUALIFY di Snowflake digunakan untuk memfilter hasil dengan fungsi jendela. Anda dapat mendefinisikan fungsi jendela sebagai bagian dari daftar SELECT atau mereferensikannya langsung di dalam klausa QUALIFY.

Biasanya, WHERE memfilter baris mentah sebelum agregasi, sedangkan HAVING memfilter setelah agregasi GROUP BY.

Sebaliknya, QUALIFY memungkinkan pemfilteran pada hasil pernyataan SQL seperti ROW_NUMBER(), RANK(), dan DENSE_RANK(). Artinya, kita dapat memanfaatkannya dalam fungsi jendela untuk menghasilkan keluaran dan langsung memfilternya.

Anda dapat menganggap QUALIFY melakukan pada fungsi jendela apa yang dilakukan HAVING pada agregat—memfilter hasil setelah dihitung.

Ada beberapa kasus penggunaan di mana klausa QUALIFY terbukti sangat berguna: 

• Anda perlu memfilter hasil kueri berdasarkan keluaran fungsi jendela.
• Anda ingin menyederhanakan kueri SQL dengan mengurangi kebutuhan akan subkueri.
• Anda perlu memberi peringkat, mendeduplikasi, atau membagi data secara efisien dalam partisi.
• Anda sedang menyiapkan laporan atau dasbor yang memerlukan catatan berkinerja terbaik per kategori.

> Jika Anda baru mengenal Snowflake, kursus Introduction to Snowflake kami menyediakan titik awal praktis sebelum mendalami klausa seperti QUALIFY. Anda juga dapat mengeksplorasi kemampuan Snowflake yang lebih luas dengan tutorial ramah pemula ini.

Sintaks QUALIFY di Snowflake

Mari tinjau sintaks dasar QUALIFY agar Anda memahami bagiannya. Berikut contoh kueri sederhana yang menggunakan QUALIFY:

SELECT
 column1,
 column2, 
 window_function() OVER (PARTITION BY column3 ORDER BY column4) AS rank
FROM table_name
QUALIFY window_function_condition;Didukung oleh

Anda memiliki klausa SELECT seperti biasa diikuti oleh beberapa kolom. Lalu ada window_function() yang ditulis dalam format umum untuk menghasilkan kolom bernama rank.

Ada pernyataan FROM seperti biasa, yang memberi tahu kita tabelnya, dan akhirnya QUALIFY. QUALIFY ini diikuti oleh “window_function_condition”, yang sering berupa kesetaraan dan dapat terlihat seperti QUALIFY rank = 1

Catatan: Anda dapat mereferensikan alias dari fungsi jendela atau menulis ulang seluruh ekspresi fungsi jendela di dalam klausa QUALIFY. Keduanya berfungsi.

> Jika Anda perlu meninjau fungsi jendela, lihat cheat sheet fungsi jendela ini. 

Contoh Penggunaan QUALIFY di Snowflake

Sekarang setelah Anda mengetahui struktur keseluruhan QUALIFY, saya akan menampilkan beberapa contoh spesifik yang menggunakan klausa ini.

Contoh 1: Memfilter N baris teratas per grup

Misalkan kita ingin menemukan 3 karyawan dengan bayaran tertinggi di setiap departemen. Pertama, mari lihat contoh yang tidak menggunakan QUALIFY, diikuti versi yang lebih ringkas yang menggunakannya.

/* First we have to rank all the employees */
WITH ranked_employees AS (
SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees)

/* Then we query to subquery and filter using WHERE */
SELECT *
FROM ranked_employees
WHERE rank <= 3;Didukung oleh

Seperti yang Anda lihat, contoh di atas memerlukan CTE, tetapi dengan QUALIFY, kuerinya lebih lugas:

SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
QUALIFY rank <= 3;Didukung oleh

Kueri di atas memberikan peringkat kepada setiap karyawan dalam departemennya dan mengembalikan 3 teratas di setiap departemen. Kueri ini menggunakan pernyataan QUALIFY untuk langsung memfilter tanpa perlu subkueri atau CTE. Ini sangat berguna pada dasbor atau API di mana meminimalkan panjang kueri dan waktu respons itu penting.

Contoh 2: Memfilter catatan duplikat

Jika sebuah tabel memiliki catatan duplikat dan kita ingin mempertahankan hanya kemunculan pertama berdasarkan fungsi pemeringkatan, kita dapat menggunakan QUALIFY dengan ROW_NUMBER():

SELECT 
order_id, 
customer_id, 
order_date,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS row_num
FROM orders
QUALIFY row_num = 1;Didukung oleh

Kolom row_num dihasilkan dengan mempartisi setiap pelanggan dan mengurutkannya berdasarkan tanggal pesanan. Ini memberikan nomor berurutan untuk setiap pesanan, dimulai dari yang paling awal. Dengan memfilter menggunakan QUALIFY row_num = 1, kita memastikan hanya pesanan paling awal untuk setiap pelanggan yang dipertahankan.

Teknik ini umum digunakan dalam tugas deduplikasi saat pembersihan data.

Contoh 3: Memfilter berdasarkan fungsi jendela dalam agregat

Mari sedikit lebih canggih. Menggunakan QUALIFY dengan fungsi jendela agregat memungkinkan pemfilteran yang lebih fleksibel dan dinamis.

Misalnya, jika kita ingin menemukan karyawan dengan gaji di atas rata-rata departemen, kita dapat menggunakan QUALIFY dengan AVG() pada fungsi jendela:

SELECT 
employee_id, 
department, 
salary,
AVG(salary) OVER (PARTITION BY department) AS avg_salary
FROM employees
QUALIFY salary > avg_salary;Didukung oleh

Logika seperti ini sangat bagus saat mengidentifikasi outlier atau kinerja terbaik dalam kelompok sebaya.

> Jika Anda ingin memperkuat keterampilan SQL untuk kueri yang lebih kompleks seperti ini, lihat kursus Intermediate SQL kami.

Praktik Terbaik Menggunakan QUALIFY di Snowflake

Berikut beberapa praktik terbaik dan penggunaan untuk klausa QUALIFY. 

Sederhanakan kueri yang kompleks

Kekuatan QUALIFY terletak pada kemampuannya menyederhanakan kueri dengan menghilangkan kebutuhan akan subkueri dan CTE tambahan. Manfaatkan fungsionalitas ini. Gunakan setiap kali Anda menulis fungsi jendela dan perlu memfilter berdasarkan keluarannya, tanpa membuat subkueri.

Lebih sedikit CTE berarti debug lebih mudah, logika lebih jelas, dan pemeliharaan lebih baik.

Gabungkan QUALIFY dengan fungsi jendela lainnya

Anda dapat menggabungkan beberapa fungsi jendela untuk pemfilteran yang lebih halus. Misalnya, menggunakan ROW_NUMBER() bersama RANK() untuk menyelesaikan seri peringkat sambil membatasi hasil:

SELECT 
employee_id, 
department, 
salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC, employee_id) AS row_num
FROM employees
QUALIFY rank <= 3 AND row_num <= 3;Didukung oleh

Kueri ini mengembalikan karyawan yang berada dalam 3 peringkat gaji teratas di departemennya. Namun, jika lebih dari tiga karyawan seri dalam tiga besar tersebut, kueri hanya akan menampilkan tiga pertama yang diurutkan berdasarkan ID karyawan.

Pertimbangan kinerja

Meskipun QUALIFY menyederhanakan kueri dari sisi kode yang ditulis, beberapa aspek kinerja perlu dipertimbangkan.

Fungsi jendela dapat memakan banyak komputasi, dan kueri yang tersusun buruk bisa menjadi intensif sumber daya. Pastikan untuk mengingat konsep-konsep berikut:

• Fungsi jendela bisa mahal secara komputasi pada dataset besar, jadi perhatikan pada tahap mana dalam proses Anda menggunakannya.
• Pastikan pengindeksan dan pemartisian yang tepat untuk mengoptimalkan kinerja.
• Gunakan LIMIT bersama QUALIFY untuk efisiensi yang lebih baik saat pengujian.
• Gunakan alat Query Profile Snowflake untuk mengidentifikasi hambatan kinerja pada kueri berjendela.

Pemecahan Masalah Kueri QUALIFY

Mudah terjebak pada jebakan dengan pernyataan QUALIFY, mengingat kompleksitas fungsi jendela. Berikut beberapa kiat untuk membantu mengatasi masalah umum yang mungkin Anda temui.

1. Menggunakan fungsi jendela di klausa WHERE

Mencoba menggunakan fungsi jendela langsung di klausa WHERE akan menghasilkan error karena WHERE dievaluasi sebelum fungsi jendela diproses.

-- This will cause an error
SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
WHERE rank = 1;Didukung oleh

Solusi: Gunakan klausa QUALIFY sebagai gantinya, yang dievaluasi setelah fungsi jendela.

SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
QUALIFY rank = 1;Didukung oleh

2. Menghilangkan predikat di QUALIFY

Klausa QUALIFY memerlukan predikat untuk memfilter hasil fungsi jendela. Menghilangkan predikat akan menyebabkan error.

-- This will cause an error
SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
QUALIFY RANK() OVER (PARTITION BY department ORDER BY salary DESC);Didukung oleh

Solusi: Pastikan klausa QUALIFY menyertakan predikat, seperti = 1 untuk memfilter baris berperingkat teratas.

SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
QUALIFY rank = 1;Didukung oleh

3. Alias yang ambigu

Menggunakan alias yang sama untuk kolom dan fungsi jendela dapat menimbulkan kebingungan dan hasil yang tidak terduga.

-- Potentially ambiguous
SELECT salary AS rank,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
QUALIFY rank = 1;Didukung oleh

Solusi: Gunakan alias yang berbeda untuk menghindari ambiguitas.

SELECT salary AS salary_amount,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees
QUALIFY salary_rank = 1;Didukung oleh

4. Partisi atau pengurutan yang salah

Kesalahan dalam menentukan klausa PARTITION BY atau ORDER BY pada fungsi jendela dapat menyebabkan hasil yang tidak terduga.

Solusi: Definisikan partisi dan pengurutan dengan cermat agar sesuai dengan logika yang diinginkan. Misalnya, untuk mendapatkan gaji teratas per departemen:

SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
QUALIFY rank = 1;Didukung oleh

Kesimpulan

Klausa QUALIFY di Snowflake adalah alat yang kuat untuk memfilter hasil berdasarkan fungsi jendela, mengurangi kebutuhan akan subkueri dan membuat kueri lebih mudah dibaca. Dengan memahami cara menggunakannya secara efektif, data scientist dapat menyederhanakan kueri dan meningkatkan efisiensi saat bekerja dengan data yang diperingkat, dikelompokkan, atau diagregasi.

Siap meningkatkan keterampilan Snowflake dan SQL Anda? Mulailah dengan kursus Introduction to Snowflake kami, asah kueri analitis Anda di kursus fungsi jendela PostgreSQL, atau majukan karier Anda dengan jalur Associate Data Engineer in SQL!