Window Functions: Sức mạnh của cửa sổ

Nếu GROUP BY gom nhiều dòng thành 1 dòng, làm mất chi tiết, thì Window Functions cho phép tính toán trên một tập hợp dòng (cửa sổ) mà vẫn giữ nguyên các dòng chi tiết.

Concept: "Cửa sổ nhìn ra thế giới"

Với mỗi dòng hiện tại, "cửa sổ" là tập hợp các dòng liên quan đến nó (ví dụ: cùng phòng ban, cùng ngày).

Cú pháp cơ bản:

FUNCTION_NAME() OVER (
    PARTITION BY column1 -- Chia nhỏ cửa sổ theo nhóm (giống Group By)
    ORDER BY column2     -- Sắp xếp thứ tự trong cửa sổ
)

Các hàm Ranking (Xếp hạng)

Giả sử bảng employees (id, name, department, salary). Yêu cầu: Tìm top 3 nhân viên lương cao nhất trong MỖI phòng ban.

SELECT * FROM (
    SELECT 
        name, department, salary,
        DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) as ranking
    FROM employees
) as ranked
WHERE ranking <= 3;

Phân biệt ROW_NUMBER, RANK, DENSE_RANK

Giả sử lương là: [1000, 1000, 900, 800]

Hàm	Kết quả	Giải thích
ROW_NUMBER()	1, 2, 3, 4	Số thứ tự đơn thuần. Bất kể lương bằng nhau, số vẫn tăng.
RANK()	1, 1, 3, 4	Hạng giống nhau nếu giá trị bằng nhau. Hạng tiếp theo sẽ bị nhảy cóc (mất số 2).
DENSE_RANK()	1, 1, 2, 3	Hạng giống nhau, hạng tiếp theo KHÔNG nhảy cóc (Số 2 vẫn xuất hiện). Thường dùng nhất để tìm Top N.

LEAD & LAG (Du hành thời gian)

Truy cập dòng trước (LAG) hoặc dòng sau (LEAD) mà không cần Self-Join.

Ví dụ: So sánh doanh thu ngày hôm nay so với ngày hôm qua.

SELECT 
    date,
    revenue,
    LAG(revenue) OVER (ORDER BY date) as previous_day_revenue,
    (revenue - LAG(revenue) OVER (ORDER BY date)) as diff
FROM daily_sales;

💡 HPN Pro Tip: Pagination bằng Window Function

Thay vì chạy 2 query: 1 query lấy data, 1 query COUNT(*) để tính tổng số trang (Rất chậm), bạn có thể dùng COUNT(*) OVER() để lấy tổng số dòng ngay trong query lấy data.

SELECT 
    id, name,
    COUNT(*) OVER() as total_records -- Tổng tất cả dòng (Không bị ảnh hưởng bởi LIMIT)
FROM users
LIMIT 10 OFFSET 0;

Lưu ý: Cách này tiện nhưng cũng có thể nặng nếu bảng quá lớn.

⚠️ Common Mistake

Quên ORDER BY trong mệnh đề OVER. Các hàm như RANK, LEAD, LAG vô nghĩa nếu không có thứ tự xác định. PARTITION BY là tùy chọn, nhưng ORDER BY thường là bắt buộc để có kết quả đúng ý nghĩa logic.