Bài 18: Tối ưu Query với Window Functions

Window functions (hàm cửa sổ) là một công cụ mạnh mẽ trong SQL, cho phép thực hiện các phép tính trên một tập hợp các hàng liên quan đến hàng hiện tại mà không cần nhóm dữ liệu. Chúng thường được sử dụng để tính toán các giá trị tích lũy, xếp hạng, và các phép toán phức tạp khác. Tuy nhiên, nếu không được sử dụng đúng cách, window functions có thể gây ra các vấn đề về hiệu suất. Bài viết này sẽ đi sâu vào khái niệm window functions, cách sử dụng chúng để tối ưu hóa truy vấn, và so sánh với các kỹ thuật khác như subquery.

18.1. Window Functions là gì?

Window functions là các hàm SQL cho phép thực hiện các phép tính trên một tập hợp các hàng liên quan đến hàng hiện tại, được gọi là "cửa sổ" (window). Khác với các hàm tổng hợp (aggregate functions), window functions không nhóm dữ liệu mà thay vào đó, chúng giữ nguyên số lượng hàng trong kết quả.

18.1.1. Cách sử dụng window functions

Cú pháp cơ bản:

  SELECT column1, column2, 
         window_function(column3) OVER (PARTITION BY column4 ORDER BY column5)
  FROM table_name;

Ví dụ: Sử dụng hàm ROW_NUMBER() để đánh số thứ tự các hàng trong mỗi nhóm:

  SELECT employee_id, first_name, last_name, department_id,
         ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num
  FROM employees;

18.1.2. Các hàm phổ biến (ROW_NUMBER, RANK, DENSE_RANK)

ROW_NUMBER(): Đánh số thứ tự các hàng trong mỗi nhóm, bắt đầu từ 1.

Ví dụ:

  SELECT employee_id, first_name, last_name, department_id,
         ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num
  FROM employees;

RANK(): Đánh số thứ tự các hàng trong mỗi nhóm, với các hàng có cùng giá trị sẽ có cùng thứ hạng, và thứ hạng tiếp theo sẽ bị bỏ qua.

Ví dụ:

  SELECT employee_id, first_name, last_name, department_id,
         RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
  FROM employees;

DENSE_RANK(): Tương tự như RANK(), nhưng thứ hạng tiếp theo không bị bỏ qua.

Ví dụ:

  SELECT employee_id, first_name, last_name, department_id,
         DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rank
  FROM employees;

18.2. Tối ưu query với window functions

Window functions có thể được sử dụng để thay thế các subquery phức tạp, giúp cải thiện hiệu suất và làm cho truy vấn dễ đọc hơn.

18.2.1. Sử dụng window functions thay thế subquery

Vấn đề: Các subquery phức tạp có thể làm chậm hiệu suất và khó bảo trì.

Giải pháp: Sử dụng window functions để thay thế các subquery phức tạp.

Ví dụ không tối ưu (sử dụng subquery):

  SELECT e.employee_id, e.first_name, e.last_name, e.salary,
         (SELECT COUNT(*) FROM employees e2 WHERE e2.department_id = e.department_id AND e2.salary > e.salary) + 1 AS rank
  FROM employees e;

Ví dụ tối ưu (sử dụng window function):

  SELECT employee_id, first_name, last_name, salary,
         RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
  FROM employees;

18.2.2. Tránh sử dụng window functions không cần thiết

Vấn đề: Sử dụng window functions không cần thiết có thể làm tăng độ phức tạp của truy vấn và giảm hiệu suất.

Giải pháp: Chỉ sử dụng window functions khi thực sự cần thiết và đảm bảo rằng chúng được sử dụng một cách hiệu quả.

Ví dụ không tối ưu:

  SELECT employee_id, first_name, last_name, salary,
         ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
         RANK() OVER (ORDER BY salary DESC) AS rank,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
  FROM employees;

Ví dụ tối ưu:

  SELECT employee_id, first_name, last_name, salary,
         RANK() OVER (ORDER BY salary DESC) AS rank
  FROM employees;

18.3. Khi nào nên sử dụng window functions

18.3.1. Trường hợp phù hợp cho window functions

Tính toán tích lũy: Window functions phù hợp cho các phép tính tích lũy, chẳng hạn như tổng tích lũy (running total) hoặc trung bình tích lũy (running average).
- Ví dụ:
```
  SELECT employee_id, first_name, last_name, salary,
         SUM(salary) OVER (ORDER BY employee_id) AS running_total
  FROM employees;
```

Xếp hạng và phân trang: Window functions phù hợp cho các bài toán xếp hạng và phân trang dữ liệu.

Ví dụ:

  SELECT employee_id, first_name, last_name, salary,
         RANK() OVER (ORDER BY salary DESC) AS rank
  FROM employees;

So sánh giữa các hàng: Window functions phù hợp cho các bài toán so sánh giữa các hàng, chẳng hạn như tính chênh lệch giữa các hàng liên tiếp.
- Ví dụ:
```
  SELECT employee_id, first_name, last_name, salary,
         salary - LAG(salary, 1) OVER (ORDER BY employee_id) AS salary_diff
  FROM employees;
```

18.3.2. Đánh đổi giữa window functions và subquery

Window Functions:
- Ưu điểm: Giữ nguyên số lượng hàng trong kết quả, dễ đọc và bảo trì.
- Nhược điểm: Có thể làm chậm hiệu suất nếu sử dụng không đúng cách.
Subquery:
- Ưu điểm: Linh hoạt và có thể được sử dụng trong nhiều tình huống khác nhau.
- Nhược điểm: Có thể làm tăng độ phức tạp của truy vấn và giảm hiệu suất.

Kết luận

Window functions là một công cụ mạnh mẽ giúp tối ưu hóa các truy vấn phức tạp bằng cách thực hiện các phép tính trên một tập hợp các hàng liên quan. Bằng cách sử dụng window functions thay thế các subquery phức tạp, bạn có thể cải thiện hiệu suất và làm cho truy vấn dễ đọc hơn. Tuy nhiên, việc sử dụng window functions cần được cân nhắc kỹ lưỡng để tránh các vấn đề về hiệu suất. Trong các bài tiếp theo, chúng ta sẽ đi sâu vào các kỹ thuật tối ưu hóa khác, chẳng hạn như sử dụng indexed views và query rewrite.

Từ vựng chuyên môn (Glossary)

Window Functions: Hàm cửa sổ.
ROW_NUMBER: Đánh số thứ tự các hàng.
RANK: Xếp hạng các hàng.
DENSE_RANK: Xếp hạng các hàng không bỏ qua thứ hạng.
Subquery: Truy vấn con.
Running Total: Tổng tích lũy.
Running Average: Trung bình tích lũy.
Lag Function: Hàm trễ, lấy giá trị từ hàng trước đó.
Query Optimization: Tối ưu hóa truy vấn.
Data Ranking: Xếp hạng dữ liệu.
Data Pagination: Phân trang dữ liệu.