Phân Tích Dữ Liệu Chuyên Nghiệp Với Window Functions

Chào mừng bạn tiếp tục hành trình trong chuỗi series tự học SQL cùng MCNA Technology School!

Ở Buổi 3, bạn đã học cách gom nhóm bằng GROUP BY. Tuy nhiên, GROUP BY có một hạn chế lớn: nó sẽ gộp các dòng lại thành 1 dòng duy nhất cho mỗi nhóm, khiến bạn mất đi thông tin chi tiết của từng cá thể.

Làm thế nào để vừa giữ nguyên chi tiết từng dòng, vừa tính toán xếp hạng hoặc chạy số liệu tổng hợp theo từng nhóm? Window Functions trong SQL chính là công cụ mạnh mẽ giúp bạn giải quyết bài toán này.

1. Window Functions Trong SQL Là Gì?

Window Functions (Hàm cửa sổ) thực hiện các tính toán trên một tập hợp các dòng dữ liệu có liên quan đến dòng hiện tại (gọi là “cửa sổ” – window).

Khác với GROUP BY, Window Functions không gộp các dòng lại. Mỗi dòng trong bảng kết quả vẫn giữ nguyên bản sắc riêng của nó.

Cú pháp cơ bản:

TÊN_HÀM() OVER (
    PARTITION BY cột_chia_nhóm
    ORDER BY cột_sắp_xếp
)

• OVER(): Từ khóa bắt buộc để đánh dấu đây là một Window Function.

• PARTITION BY: Chia tập dữ liệu thành các nhóm nhỏ (tương tự GROUP BY nhưng không làm gộp dòng).

• ORDER BY: Sắp xếp thứ tự các dòng trong từng nhóm nhỏ đó.

2. Top 3 Hàm Xếp Hạng Phổ Biến Nhất (ROW_NUMBER, RANK, DENSE_RANK)

Khi cần xếp hạng dữ liệu (ví dụ: tìm top 3 học viên xuất sắc nhất từng lớp), bạn sẽ dùng 3 hàm này. Hãy cùng xem sự khác biệt giữa chúng khi có giá trị trùng lặp (đồng hạng):

So sánh nhanh qua ví dụ:

Giả sử có 4 học viên với điểm số lần lượt là: 10, 9, 9, 8.

Ví dụ thực tế tại MCNA:

Thực hiện xếp hạng học viên có điểm thi cao nhất trong từng khóa học:

SELECT 
    HoTen,
    KhoaHoc,
    DiemThi,
    DENSE_RANK() OVER (
        PARTITION BY KhoaHoc 
        ORDER BY DiemThi DESC
    ) AS XepHang
FROM HocVien;

3. Lọc Kết Quả Window Functions Bằng CTE

Do thứ tự thực thi trong SQL, bạn không thể dùng Window Functions trực tiếp trong mệnh đề WHERE. Để lọc lấy danh sách Top 1 học viên mỗi lớp, bạn cần kết hợp với kiến thức CTE ở Buổi 5:

WITH BangXepHang AS (
    SELECT 
        HoTen,
        KhoaHoc,
        DiemThi,
        DENSE_RANK() OVER (
            PARTITION BY KhoaHoc 
            ORDER BY DiemThi DESC
        ) AS XepHang
    FROM HocVien
)
SELECT * 
FROM BangXepHang 
WHERE XepHang = 1;

4. Bài Tập Thực Hành Học SQL

Hãy vận dụng Window Functions trong SQL để giải bài tập xếp hạng nhân sự dưới đây:

Đề bài: Cho bảng NhanVien (MaNV, HoTen, PhongBan, Luong).

1. Viết câu lệnh đánh số thứ tự đơn thuần (ROW_NUMBER) cho toàn bộ nhân viên theo mức lương giảm dần.

2. Viết câu lệnh sử dụng DENSE_RANK() để xếp hạng lương của nhân viên trong từng phòng ban riêng biệt.

3. Lấy ra danh sách các nhân viên thuộc Top 2 lương cao nhất của mỗi phòng ban (kết hợp CTE).

-- Câu 1: Đánh số thứ tự toàn bộ nhân viên theo lương giảm dần
SELECT 
    HoTen, Luong,
    ROW_NUMBER() OVER (ORDER BY Luong DESC) AS STT
FROM NhanVien;

-- Câu 2: Xếp hạng lương theo từng phòng ban
SELECT 
    HoTen, PhongBan, Luong,
    DENSE_RANK() OVER (
        PARTITION BY PhongBan 
        ORDER BY Luong DESC
    ) AS HangLuong
FROM NhanVien;

-- Câu 3: Lọc Top 2 lương cao nhất mỗi phòng ban
WITH RankingTable AS (
    SELECT 
        HoTen, PhongBan, Luong,
        DENSE_RANK() OVER (
            PARTITION BY PhongBan 
            ORDER BY Luong DESC
        ) AS HangLuong
    FROM NhanVien
)
SELECT * 
FROM RankingTable 
WHERE HangLuong <= 2;