QUALIFY: Mệnh đề lọc SQL mà trước đây bạn không biết là mình cần

Khái niệm cơ bản và cú pháp của mệnh đề SQL QUALIFY

QUALIFY là một mệnh đề dùng để lọc kết quả của hàm cửa sổ (window function). Do đó, để sử dụng thành công mệnh đề QUALIFY, phải có ít nhất một hàm WINDOW trong danh sách SELECT hoặc mệnh đề QUALIFY – chỉ các hàng mà biểu thức boolean đánh giá là TRUE mới được trả về.

Mẹo: Hãy xem Cheat Sheet về SQL Window Functions.

Nếu không có mệnh đề QUALIFY, việc lọc trên kết quả của một hàm WINDOW sẽ đòi hỏi lồng truy vấn. Các lập trình viên SQL thường khuyến nghị hạn chế tối đa truy vấn lồng nhau vì chúng làm mã khó đọc hơn và tăng độ phức tạp khi gỡ lỗi, cộng tác.

Nói cách khác, ý tưởng chính đằng sau mệnh đề QUALIFY là đơn giản hóa các truy vấn cần lọc trên kết quả của các hàm cửa sổ; điều này có nghĩa là QUALIFY được đánh giá sau khi các hàm cửa sổ được tính toán.

Dưới đây là thứ tự thực thi điển hình cho một truy vấn có mệnh đề QUALIFY:

• FROM
• WHERE
• GROUP BY và Tổng hợp
• HAVING
• WINDOW
• QUALIFY
• DISTINCT
• ORDER BY
• LIMIT

Cú pháp tổng quát của một câu lệnh QUALIFY như sau:

QUALIFY <predicate>Được hỗ trợ bởi

Ở dạng tổng quát, nó sẽ trông như thế này:

SELECT <column_list>
  FROM <data_source>
  [GROUP BY ...]
  [HAVING ...]
  QUALIFY <predicate>
  [ ... ]Được hỗ trợ bởi

Lưu ý: <predicate> là một biểu thức dùng để lọc kết quả sau khi các tổng hợp và hàm cửa sổ được tính toán.

Khi nào nên dùng SQL QUALIFY

Mệnh đề QUALIFY khá giống HAVING ở chỗ nó tránh cần đến truy vấn con để thực hiện lọc. Ví dụ, bạn có thể dùng QUALIFY để lọc kết quả của một hàm phân tích – hàm dùng để tính giá trị tổng hợp dựa trên một nhóm hàng.

Để rõ hơn khi nào bạn sẽ dùng QUALIFY, đây là một tình huống ngắn:

Giả sử bạn là nhà khoa học dữ liệu và một bên liên quan yêu cầu thông tin lần đăng nhập gần nhất của mỗi khách hàng. Truy vấn ban đầu của bạn có thể như sau:

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 Được hỗ trợ bởi

Đây là khởi đầu tốt, nhưng chưa đáp ứng yêu cầu của bên liên quan vì nó sẽ trả về tất cả các lần đăng nhập chứ không chỉ lần gần nhất. Để trả về lần đăng nhập cuối cùng, chúng ta cần thêm bộ lọc.

Người mới học SQL có thể mắc bẫy khi cố lọc bằng mệnh đề WHERE như sau:

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 = 1Được hỗ trợ bởi

Đáng tiếc, đoạn mã này sẽ không chạy; điều tương tự cũng xảy ra nếu bạn thử GROUP BY và HAVING.

Lý do là do thứ tự thực thi mà chúng ta đã bàn ở phần trước.

WHERE, GROUP BY và HAVING đều được đánh giá trước các hàm WINDOW; điều này có nghĩa là chúng không thể lọc các hàm WINDOW vì chúng chưa biết chúng tồn tại – hàm WINDOW chưa được đánh giá tại thời điểm ba mệnh đề đó chạy.

Một cách để khiến bộ lọc chạy sau hàm WINDOW là dùng Common Table Expression (CTE).

Mã của chúng ta sẽ trông như sau:

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 = 1Được hỗ trợ bởi

Về mặt kỹ thuật, mã này là hợp lệ – Nó sẽ chạy tốt và trả về kết quả mà bên liên quan của bạn cần.

Tuy nhiên, chúng ta phải đưa vào hai truy vấn và có thêm vài dòng mã, điều có thể trở nên dư thừa nếu bạn lặp lại quy trình này thường xuyên.

Cách xử lý hay nhất cho vấn đề này là dùng QUALIFY.

Mã sẽ trông như sau:

-- 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 = 1Được hỗ trợ bởi

Giải pháp này hoạt động vì các mệnh đề QUALIFY được đánh giá sau các hàm WINDOW trong thứ tự thực thi của SQL, nghĩa là chúng nhận biết được sự tồn tại của các hàm này nên có thể lọc chúng ngay trong cùng truy vấn.

Bạn có thể tự hỏi ngoài việc viết ít dòng mã hơn thì QUALIFY có lợi ích gì khác không; câu trả lời là gần như không có lợi ích về hiệu năng.

Giải pháp dùng CTE và giải pháp dùng QUALIFY đều chạy trong khoảng thời gian tương tự, nên khó có thể nói có cải thiện hiệu năng đáng kể từ QUALIFY.

Lợi ích chính là cải thiện trải nghiệm: ít mã hơn và dễ đọc hơn.

So sánh SQL QUALIFY với các phương pháp lọc khác

Đến đây, bạn hiểu rằng mệnh đề QUALIFY là một phương pháp lọc khác có trong SQL. Bạn cũng biết thời điểm tốt nhất để dùng mệnh đề này.

Vậy sự khác biệt giữa QUALIFY và các phương pháp lọc khác là gì?

Hãy điểm lại từng phương pháp lọc.

Mệnh đề WHERE

WHERE dùng để lọc các bản ghi trong một bảng; nghĩa là nó thực hiện các thao tác trên hàng. Ta dùng nó khi muốn trích xuất những bản ghi đáp ứng điều kiện cụ thể. Ví dụ, ta có thể lọc tập dữ liệu theo tuổi của ai đó. Mã sẽ như sau:

SELECT name, gender, height, weight, age 
FROM gym_members_info 
WHERE age < 23; Được hỗ trợ bởi

Những điểm chính về mệnh đề WHERE:

1. Dùng để lọc bản ghi dựa trên điều kiện cụ thể.
2. Thao tác được thực hiện trên các hàng.
3. Có thể dùng WHERE mà không cần GROUP BY.
4. WHERE có thể dùng với các câu lệnh SELECT, UPDATE, DELETE.

Giờ hãy so sánh với mệnh đề HAVING.

Mệnh đề HAVING

HAVING dùng để lọc các bản ghi từ các nhóm do mệnh đề GROUP BY xác định dựa trên một điều kiện cụ thể. Vì vậy, khác biệt rõ ràng nhất giữa WHERE và HAVING là HAVING yêu cầu phải có mệnh đề GROUP BY thì mới được đánh giá thành công.

Lợi ích chính là bạn có thể áp dụng HAVING cho một tập con các nhóm đã tổng hợp, điều mà WHERE không làm được.

Ví dụ:

SELECT gender, AVG(height) 
FROM gym_members_info
GROUP BY gender 
HAVING AVG(height) > 170 Được hỗ trợ bởi

Đoạn mã trên sẽ trả về các nhóm giới tính có chiều cao trung bình lớn hơn 170 cm; nói cách khác, HAVING thực hiện thao tác trên cột.

Một điểm nữa cần lưu ý là HAVING chỉ có thể dùng với câu lệnh SELECT, không như WHERE.

Mệnh đề QUALIFY

QUALIFY tương đương với HAVING nhưng áp dụng lọc trên kết quả của các hàm WINDOW, trong khi HAVING lọc trên các hàm tổng hợp và mệnh đề GROUP BY – tìm hiểu thêm về cách dùng GROUP BY.

Nếu chúng ta muốn biết thông tin về người cao nhất trong mỗi nhóm tuổi, chúng ta có thể dùng một hàm WINDOW để tính toán và mệnh đề QUALIFY để lọc kết quả sao cho chỉ lấy người cao nhất ở mỗi nhóm tuổi.

Mã sẽ như sau:

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 = 1Được hỗ trợ bởi

Về bản chất, mệnh đề QUALIFY xác định một biểu thức điều kiện dùng để lọc đầu ra của một hàm phân tích có thứ tự đã được tính toán theo tiêu chí do người dùng chỉ định.

Kết luận và hướng học tiếp

SQL đóng vai trò lớn trong quy trình làm việc dữ liệu điển hình; biết cách triển khai các thao tác hữu ích trong SQL, như mệnh đề QUALIFY, vô cùng hữu ích vì giúp bạn viết mã gọn gàng hơn, khiến người khác dễ chịu hơn khi cộng tác với bạn.

Nếu bạn muốn nâng cao kỹ năng SQL, chúng tôi khuyến nghị các tài nguyên dưới đây:

• Cheat Sheet SQL cơ bản
• Khóa học Introduction to SQL
• Lộ trình kỹ năng SQL Fundamentals