Các hàm cửa sổ cơ bản cho phân tích

Trước khi bắt đầu, tưởng tượng bạn đang làm việc với một bảng có hàng ngàn dòng bán hàng. Nhiệm vụ của bạn: xác định ai là người bán số một trong từng danh mục, ai là số hai, v.v. Hoặc, ví dụ, bạn cần đánh số tất cả các dòng trong kết quả truy vấn để theo dõi thứ tự. Tất cả điều này dễ dàng làm được với hàm cửa sổ.

Hàm cửa sổ — là các hàm SQL hoạt động với một tập con các dòng (gọi là "cửa sổ") từ tập dữ liệu. Khác với hàm tổng hợp, vốn gộp các dòng thành một (ví dụ SUM() hoặc AVG()), hàm cửa sổ giữ nguyên các dòng, chỉ thêm giá trị tính toán vào từng dòng.

Khác biệt với hàm tổng hợp

Hàm tổng hợp "nén" dữ liệu bằng cách nhóm các dòng lại:

SELECT department, COUNT(*)
FROM employees
GROUP BY department;

Kết quả: chỉ còn vài dòng, mỗi dòng cho một phòng ban.

So sánh với hàm cửa sổ — ở đây các dòng vẫn giữ nguyên, nhưng thêm một trường mới, ví dụ ROW_NUMBER():

SELECT employee_name, department,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_within_department
FROM employees;

Bạn sẽ nhận được tất cả các dòng như cũ, nhưng có thêm cột rank_within_department, trong đó mỗi nhân viên được gán số thứ tự trong phòng ban của mình.

Các hàm cửa sổ cơ bản

Cú pháp OVER()

Phần quan trọng nhất của mỗi hàm cửa sổ — là từ khóa thần thánh OVER(). Nó xác định "cửa sổ" dữ liệu mà hàm sẽ làm việc. Bên trong OVER() bạn có thể chỉ định phân nhóm (PARTITION BY) và/hoặc thứ tự sắp xếp (ORDER BY).

Cú pháp tổng quát:

<hàm_cửa_sổ>() OVER (
   [PARTITION BY <nhóm>] 
   [ORDER BY <thứ_tự>]
)

Các thành phần:

• PARTITION BY: Chia các dòng thành nhóm. Ví dụ, "chia dữ liệu theo phòng ban".
• ORDER BY: Chỉ định thứ tự xử lý các dòng. Ví dụ, "sắp xếp nhân viên theo lương giảm dần".

Hàm ROW_NUMBER()

Hàm ROW_NUMBER() đánh số các dòng, bắt đầu từ 1, trong "cửa sổ" đã chỉ định. Đôi khi nó hữu ích để tạo số thứ tự dòng trong bảng tạm hoặc xác định vị trí của bản ghi.

Ví dụ. Bảng sales (bán hàng):

Truy vấn:

SELECT seller_name, product_category, revenue,
       ROW_NUMBER() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS row_number
FROM sales;

Kết quả:

Cách nó hoạt động:

1. Dữ liệu được chia thành nhóm theo product_category.
2. Mỗi nhóm được sắp xếp theo revenue (giảm dần).
3. Các dòng trong mỗi nhóm được gán số thứ tự.

Hàm RANK()

Hàm RANK() dùng để xếp hạng các dòng. Khác với ROW_NUMBER(), nó xử lý các giá trị trùng nhau và sẽ bỏ qua số thứ tự (hạng) nếu có giá trị trùng.

Ví dụ:

SELECT seller_name, product_category, revenue,
       RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS rank
FROM sales;

Kết quả:

Hàm DENSE_RANK()

DENSE_RANK() giống RANK(), chỉ khác một điểm: nó không bỏ qua số hạng nếu có giá trị trùng.

Ví dụ. Thêm một giao dịch có cùng doanh thu:

Truy vấn:

SELECT seller_name, product_category, revenue,
       DENSE_RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS dense_rank
FROM sales;

Kết quả:

Ví dụ sử dụng: đánh số dòng

Bài toán: đánh số tất cả các đơn hàng trong bảng orders, sắp xếp theo ngày.

SELECT order_id, customer_name, order_date,
       ROW_NUMBER() OVER (ORDER BY order_date) AS order_number
FROM orders;

Kết quả: bạn sẽ có danh sách đơn hàng được đánh số theo thứ tự thực hiện.

Ví dụ sử dụng: top-3 người bán trong mỗi danh mục

Bài toán: xác định ba người bán tốt nhất trong mỗi danh mục sản phẩm.

WITH ranked_sales AS (
    SELECT seller_name, product_category, revenue,
           RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS rank
    FROM sales
)
SELECT seller_name, product_category, revenue
FROM ranked_sales
WHERE rank <= 3;

Ví dụ sử dụng: phát hiện các giá trị trùng nhau

Bài toán: xác định xem có người bán nào có doanh thu giống nhau trong từng danh mục không.

SELECT seller_name, product_category, revenue,
       DENSE_RANK() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS dense_rank
FROM sales;

Bây giờ bạn có thể thấy các hạng, nơi các giá trị "dính" lại với nhau.