Snowflake QUALIFY cho người mới bắt đầu: Nó là gì và cách sử dụng

Là một chuyên gia dữ liệu làm việc với Snowflake, bạn có thể cần lọc kết quả truy vấn dựa trên các hàm cửa sổ. Truyền thống, điều này đòi hỏi subquery hoặc logic lọc phức tạp. Tuy nhiên, Snowflake cung cấp một lựa chọn mạnh mẽ: mệnh đề QUALIFY. 

Trong hướng dẫn này, tôi sẽ phân tích cách sử dụng QUALIFY để tinh gọn việc lọc, kèm ví dụ từng bước và các thực tiễn tốt nhất.

QUALIFY trong Snowflake là gì?

Mệnh đề QUALIFY trong Snowflake được dùng để lọc kết quả với các hàm cửa sổ. Bạn có thể định nghĩa các hàm cửa sổ như một phần của danh sách SELECT hoặc tham chiếu chúng trực tiếp trong mệnh đề QUALIFY.

Thông thường, WHERE lọc các dòng thô trước khi tổng hợp, trong khi HAVING lọc sau khi GROUP BY tổng hợp.

Thay vào đó, QUALIFY cho phép lọc dựa trên kết quả của các biểu thức SQL như ROW_NUMBER(), RANK() và DENSE_RANK(). Điều này có nghĩa là chúng ta có thể sử dụng nó trong các hàm cửa sổ để tạo ra đầu ra và lập tức lọc trên chúng.

Bạn có thể hình dung QUALIFY làm với hàm cửa sổ điều mà HAVING làm với số tổng hợp — lọc kết quả sau khi đã được tính toán.

Có một số trường hợp sử dụng mà mệnh đề QUALIFY đặc biệt hữu ích: 

• Bạn cần lọc kết quả truy vấn dựa trên đầu ra của hàm cửa sổ.
• Bạn muốn đơn giản hóa truy vấn SQL bằng cách giảm nhu cầu dùng subquery.
• Bạn cần xếp hạng, khử trùng lặp hoặc phân đoạn dữ liệu hiệu quả theo từng partition.
• Bạn đang chuẩn bị báo cáo hoặc dashboard yêu cầu bản ghi top theo từng danh mục.

> Nếu bạn mới bắt đầu với Snowflake, khóa học Introduction to Snowflake sẽ cho bạn điểm khởi đầu thực hành trước khi đi sâu vào các mệnh đề như QUALIFY. Bạn cũng có thể khám phá các khả năng rộng hơn của Snowflake với hướng dẫn thân thiện cho người mới bắt đầu này.

Cú pháp của QUALIFY trong Snowflake

Hãy xem lại cú pháp cơ bản của QUALIFY để bạn hiểu các thành phần của nó. Sau đây là một truy vấn đơn giản sử dụng QUALIFY:

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

Bạn có mệnh đề SELECT quen thuộc theo sau bởi một vài cột. Sau đó là window_function() viết theo định dạng thường dùng để tạo cho chúng ta một cột gọi là rank.

Có câu lệnh FROM như thường lệ để chỉ ra bảng, và cuối cùng là QUALIFY. QUALIFY này được theo sau bởi “window_function_condition”, thường là một điều kiện bằng và có thể trông như QUALIFY rank = 1

Lưu ý: Bạn có thể tham chiếu bí danh của một hàm cửa sổ hoặc viết lại toàn bộ biểu thức hàm cửa sổ bên trong mệnh đề QUALIFY. Cả hai cách đều hoạt động.

> Nếu bạn cần ôn lại hàm cửa sổ, hãy xem cheat sheet về hàm cửa sổ. 

Ví dụ sử dụng QUALIFY trong Snowflake

Giờ bạn đã biết cấu trúc tổng thể của QUALIFY, tôi sẽ trình bày một số ví dụ cụ thể sử dụng mệnh đề này.

Ví dụ 1: Lọc N dòng đầu theo nhóm

Giả sử chúng ta muốn tìm 3 nhân viên được trả lương cao nhất trong mỗi phòng ban. Trước tiên, hãy xem một ví dụ không dùng QUALIFY, sau đó là phiên bản ngắn gọn hơn có dùng.

/* 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;

Như bạn thấy, ví dụ trên cần một CTE, nhưng với QUALIFY, truy vấn sẽ trực quan hơn:

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

Truy vấn trên gán hạng cho từng nhân viên trong phòng ban của họ và trả về top 3 ở mỗi phòng ban. Nó dùng câu lệnh QUALIFY để lọc ngay lập tức mà không cần subquery hay CTE. Điều này đặc biệt hữu ích trong dashboard hoặc API nơi việc rút gọn truy vấn và thời gian phản hồi là quan trọng.

Ví dụ 2: Lọc bản ghi trùng lặp

Nếu một bảng có các bản ghi trùng lặp và chúng ta muốn giữ lại chỉ lần xuất hiện đầu tiên dựa trên một hàm xếp hạng, ta có thể dùng QUALIFY cùng 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;

Cột row_num được tạo bằng cách partition theo từng khách hàng và sắp xếp theo ngày đặt hàng. Điều này gán số thứ tự tuần tự cho mỗi đơn, bắt đầu từ đơn sớm nhất. Bằng cách lọc với QUALIFY row_num = 1, chúng ta đảm bảo chỉ giữ đơn sớm nhất của mỗi khách hàng.

Kỹ thuật này thường được dùng để khử trùng lặp trong quá trình làm sạch dữ liệu.

Ví dụ 3: Lọc dựa trên hàm cửa sổ trong phép tổng hợp

Hãy nâng cao thêm một chút. Sử dụng QUALIFY với các hàm cửa sổ tổng hợp cho phép lọc linh hoạt và động hơn.

Chẳng hạn, nếu chúng ta muốn tìm những nhân viên có lương cao hơn mức trung bình của phòng ban, có thể dùng QUALIFY với AVG() trên một cửa sổ:

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

Loại logic này rất phù hợp khi xác định ngoại lệ hoặc người có hiệu suất hàng đầu trong nhóm ngang hàng.

> Nếu bạn muốn củng cố kỹ năng SQL cho các truy vấn phức tạp như thế này, hãy xem khóa học Intermediate SQL.

Thực tiễn tốt nhất khi dùng QUALIFY trong Snowflake

Dưới đây là một số thực tiễn tốt và cách dùng cho mệnh đề QUALIFY. 

Đơn giản hóa truy vấn phức tạp

Sức mạnh của QUALIFY nằm ở khả năng đơn giản hóa truy vấn bằng cách loại bỏ nhu cầu subquery và CTE thừa. Hãy tận dụng chức năng này. Dùng nó bất cứ khi nào bạn viết hàm cửa sổ và cần lọc dựa trên đầu ra của nó, mà không cần tạo subquery.

Ít CTE hơn đồng nghĩa dễ gỡ lỗi hơn, logic rõ ràng hơn và khả năng bảo trì tốt hơn.

Kết hợp QUALIFY với các hàm cửa sổ khác

Bạn có thể kết hợp nhiều hàm cửa sổ để lọc tinh hơn. Ví dụ, dùng ROW_NUMBER() cùng RANK() để xử lý các trường hợp đồng hạng trong khi vẫn giới hạn kết quả:

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;

Truy vấn này trả về các nhân viên nằm trong top 3 mức lương ở phòng ban của họ. Tuy nhiên, nếu có nhiều hơn ba người đồng hạng trong top 3 đó, nó chỉ hiển thị ba người đầu tiên theo thứ tự mã nhân viên.

Cân nhắc hiệu năng

Mặc dù QUALIFY giúp đơn giản hóa truy vấn về mặt mã viết, vẫn có một số khía cạnh hiệu năng cần cân nhắc.

Các hàm cửa sổ có thể tốn tài nguyên tính toán, và truy vấn cấu trúc kém có thể tiêu tốn nhiều tài nguyên. Hãy ghi nhớ các điểm sau:

• Các hàm cửa sổ có thể tốn kém trên bộ dữ liệu lớn, nên hãy cân nhắc giai đoạn bạn sử dụng chúng trong quy trình.
• Đảm bảo phân vùng và indexing phù hợp để tối ưu hiệu năng.
• Kết hợp LIMIT với QUALIFY để hiệu quả hơn trong giai đoạn thử nghiệm.
• Sử dụng công cụ Query Profile của Snowflake để xác định các nút thắt hiệu năng trong truy vấn có cửa sổ.

Khắc phục sự cố với truy vấn QUALIFY

Với độ phức tạp của các hàm cửa sổ, bạn rất dễ gặp bẫy khi dùng QUALIFY. Dưới đây là một số mẹo giúp xử lý các vấn đề thường gặp.

1. Dùng hàm cửa sổ trong mệnh đề WHERE

Cố gắng dùng hàm cửa sổ trực tiếp trong WHERE sẽ gây lỗi vì WHERE được đánh giá trước khi các hàm cửa sổ được xử lý.

-- 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;

Giải pháp: Hãy dùng mệnh đề QUALIFY, vốn được đánh giá sau các hàm cửa sổ.

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

2. Bỏ qua điều kiện trong QUALIFY

Mệnh đề QUALIFY yêu cầu một điều kiện để lọc kết quả của các hàm cửa sổ. Bỏ qua điều kiện sẽ dẫn đến lỗi.

-- 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);

Giải pháp: Đảm bảo mệnh đề QUALIFY bao gồm một điều kiện, chẳng hạn = 1 để lọc hàng có hạng cao nhất.

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

3. Bí danh mơ hồ

Dùng cùng một bí danh cho cả cột và hàm cửa sổ có thể gây nhầm lẫn và kết quả không mong muốn.

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

Giải pháp: Dùng các bí danh khác nhau để tránh mơ hồ.

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

4. Partition hoặc sắp xếp không chính xác

Chỉ định không chính xác PARTITION BY hoặc ORDER BY trong các hàm cửa sổ có thể dẫn đến kết quả ngoài ý muốn.

Giải pháp: Xác định cẩn thận cách phân vùng và sắp xếp để phù hợp với logic mong muốn. Ví dụ, để lấy mức lương cao nhất theo phòng ban:

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

Kết luận

Mệnh đề QUALIFY trong Snowflake là một công cụ mạnh để lọc kết quả dựa trên các hàm cửa sổ, giảm nhu cầu subquery và giúp truy vấn dễ đọc hơn. Bằng cách hiểu cách sử dụng hiệu quả, các nhà khoa học dữ liệu có thể đơn giản hóa truy vấn và cải thiện hiệu quả khi làm việc với dữ liệu được xếp hạng, nhóm hoặc tổng hợp.

Sẵn sàng nâng cao kỹ năng Snowflake và SQL của bạn? Bắt đầu với khóa học Introduction to Snowflake, mài giũa truy vấn phân tích trong khóa học PostgreSQL về hàm cửa sổ, hoặc thăng tiến sự nghiệp với lộ trình Associate Data Engineer in SQL!