Cú pháp OVER() và những điểm nổi bật của nó

OVER() — là chỉ dẫn cho SQL biết nên áp dụng window function lên tập hợp dòng nào. Có thể nói, đây là cách xác định "cửa sổ" dữ liệu để áp dụng window function. Hãy tưởng tượng bạn có một căn phòng đầy người, và bạn muốn đếm xem có bao nhiêu người đứng trên mỗi mét vuông sàn. OVER() sẽ chỉ ra chính xác khu vực nào trong phòng bạn sẽ tập trung. Nói cách khác, nó xác định tập hợp dòng mà function sẽ xử lý.

Toán tử OVER() chỉ dùng với window function để thực hiện thao tác trên các dòng của một hoặc nhiều bảng, mà không cần group dữ liệu.

Cú pháp:

window_function() OVER (
    [PARTITION BY ...]
    [ORDER BY ...]
    [ROWS/RANGE ...]
)

Trong đó:

• PARTITION BY — chia tập dữ liệu thành các nhóm logic
• ORDER BY — xác định thứ tự các dòng trong mỗi nhóm
• ROWS/RANGE — làm rõ kích thước "cửa sổ" (ví dụ: dòng hiện tại + 1 dòng tiếp theo)

Ví dụ: OVER() không có tham số

Khi OVER() được dùng mà không có tham số bổ sung, nghĩa là function phía trước nó sẽ chạy trên toàn bộ tập dữ liệu.

SELECT
    employee_id,
    salary,
    ROW_NUMBER() OVER () AS row_num -- ROW_NUMBER() sẽ áp dụng cho tất cả các dòng kết quả
FROM employees;

Chuyện gì xảy ra?

1. ROW_NUMBER() gán số thứ tự duy nhất cho từng dòng.
2. Vì OVER() không có tham số, tất cả các dòng trong bảng employees được xử lý như một khối duy nhất.

Kết quả:

Dùng PARTITION BY để chia nhóm

Ok, giờ tưởng tượng bạn muốn đánh số nhân viên không phải toàn công ty, mà là trong từng phòng ban. Lúc này PARTITION BY xuất hiện.

PARTITION BY trong OVER() chia dữ liệu thành các nhóm (hay "phân vùng"). Với mỗi nhóm, function sẽ tính toán riêng biệt. Tức là, nếu ROW_NUMBER() là một người phục vụ, thì anh ta sẽ bắt đầu đếm lại từ đầu cho mỗi "bàn" (phân vùng).

Ví dụ: dùng PARTITION BY

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

Chuyện gì xảy ra?

1. Dữ liệu từ bảng employees được chia thành các nhóm theo giá trị department_id.
2. Trong mỗi nhóm, các dòng được đánh số thứ tự bằng ROW_NUMBER().

Kết quả:

Dùng ORDER BY để xác định thứ tự

Giờ thêm chút cấu trúc nhé. Giả sử bạn không chỉ muốn đánh số dòng, mà còn muốn làm theo một thứ tự nhất định, ví dụ bắt đầu từ người có lương cao nhất. Việc này giải quyết bằng ORDER BY.

ORDER BY xác định thứ tự các dòng sẽ được window function xử lý.

Ví dụ: dùng ORDER BY trong OVER()

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

Chuyện gì xảy ra?

1. Dữ liệu được chia nhóm (PARTITION BY department_id).
2. Trong mỗi nhóm, các dòng được sắp xếp giảm dần theo lương (ORDER BY salary DESC).
3. Mỗi dòng được gán một thứ hạng dựa trên thứ tự đó.

Kết quả:

Kết hợp nhiều window function

SQL cho phép dùng nhiều window function trong một query, mỗi cái có thể hoạt động theo quy tắc riêng. Kiểu như trong một phòng vừa có nhạc vừa có người đếm số người — mỗi quá trình độc lập!

Ví dụ: nhiều window function

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

Chuyện gì xảy ra?

1. ROW_NUMBER() đánh số dòng trong mỗi nhóm theo lương giảm dần.
2. AVG() tính lương trung bình trong mỗi nhóm.

Kết quả:

Ví dụ thực tế

Window function với OVER() được dùng trong rất nhiều tình huống thực tế. Đây là vài ví dụ:

• Phân tích bán hàng: xếp hạng sản phẩm theo số lượng bán trong từng danh mục.
• Bảng xếp hạng: xác định vị trí của sinh viên trong mỗi nhóm theo điểm trung bình.
• Chuỗi thời gian: tính tổng tích lũy doanh số theo thời gian.

Ví dụ phân tích bán hàng:

SELECT
    category_id,
    product_id,
    product_name,
    SUM(sales) OVER (PARTITION BY category_id ORDER BY sales DESC) AS cumulative_sales
FROM products;

Lỗi thường gặp khi dùng window function

1. Thiếu PARTITION BY

Nếu bạn không dùng PARTITION BY, window function sẽ áp dụng cho toàn bộ bảng. Điều này có thể dẫn đến kết quả bất ngờ, nhất là khi bạn mong muốn chia theo nhóm.

---

2. Kiểu dữ liệu không đúng trong ORDER BY

ORDER BY trong window function rất nhạy với kiểu dữ liệu. Nếu bạn sort theo trường ngày tháng lưu dưới dạng text (VARCHAR), thứ tự có thể là theo alphabet chứ không phải thời gian.

3. Dùng sai ROWS BETWEEN

Mặc định, window function hoạt động với khung xác định bởi ROWS BETWEEN. Nếu bạn không chỉ rõ khung, có thể sẽ áp dụng hành vi RANGE, mà sẽ trả về nhiều dòng hơn bạn nghĩ.

4. Xử lý NULL không đúng

Window function có thể xử lý NULL khác nhau. Ví dụ, RANK() và DENSE_RANK() sẽ coi NULL là một giá trị và gán cho nó một thứ hạng riêng.

5. Dùng window aggregate function thay vì aggregate thường

Đôi khi người ta dùng window aggregate function (SUM() OVER(...)) trong khi chỉ cần aggregate thường với GROUP BY, làm query phức tạp và chậm hơn.