Posted inMySQL

MySQL 8 Window Functions: Giải pháp ‘cứu cánh’ cho những câu Query báo cáo phức tạp

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

Chuyện đêm muộn: Khi SQL ‘hành’ bạn đến 2 giờ sáng

Tôi vẫn nhớ như in một đêm trực hệ thống cách đây vài năm. Kim đồng hồ chỉ 2 giờ sáng, sếp nhắn tin cần gấp báo cáo doanh thu cho buổi họp đầu ngày. Đề bài nghe khá đơn giản: Liệt kê đơn hàng kèm theo cột “Tổng doanh thu lũy kế” và thứ hạng doanh số của từng đơn trong ngày hôm đó.

Lúc ấy, dự án vẫn chạy MySQL 5.7. Tôi phải vật lộn với mớ SELF JOIN chồng chéo và dùng biến @variable để cộng dồn dữ liệu. Kết quả? Câu query chạy mất 25 giây cho tập dữ liệu 5 triệu dòng, CPU server nhảy vọt lên 95%. Nếu ngày đó tôi biết đến Window Functions của MySQL 8, có lẽ tôi đã được ngủ sớm thêm 2 tiếng.

Window Functions (Hàm cửa sổ) không đơn thuần là một tính năng mới. Nó là bước ngoặt giúp MySQL xử lý các bài toán phân tích dữ liệu (Data Analytics) chuyên nghiệp mà không làm suy giảm hiệu năng hệ thống.

Làm quen với Window Functions trong 5 phút

Khác với GROUP BY vốn gộp nhiều dòng thành một, Window Functions cho phép bạn tính toán trên một tập hợp các dòng nhưng vẫn giữ nguyên chi tiết từng bản ghi. Chìa khóa ở đây chính là từ khóa OVER.

Hãy nhìn vào bảng sales. Thay vì viết subquery phức tạp, bạn chỉ cần một dòng lệnh để xếp hạng doanh số:

SELECT 
    sale_date, 
    employee_id, 
    amount,
    RANK() OVER (ORDER BY amount DESC) as sales_rank
FROM sales;

Kết quả trả về ngay lập tức cột sales_rank. Không cần JOIN, không cần tạo bảng tạm, code sạch và cực kỳ dễ đọc.

Giải mã cấu trúc của một Window Function

Để làm chủ công cụ này, bạn cần hiểu rõ ba thành phần chính trong mệnh đề OVER:

1. PARTITION BY: Chia để trị

PARTITION BY giúp bạn chia dữ liệu thành các nhóm độc lập để tính toán. Ví dụ, bạn muốn xếp hạng nhân viên nhưng tính riêng cho từng phòng ban thay vì tính chung toàn công ty:

SELECT 
    department, 
    employee_name, 
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank_in_dept
FROM employees;

2. ORDER BY: Xác định thứ tự xử lý

Thành phần này quyết định thứ tự các dòng được đưa vào tính toán. Với các hàm như SUM(), khi có ORDER BY, MySQL sẽ hiểu bạn đang muốn tính lũy kế (running total) thay vì tính tổng toàn bộ.

3. Frame Clause (ROWS/RANGE): Giới hạn khung hình

Đây là kỹ thuật nâng cao để xác định phạm vi tính toán quanh dòng hiện tại. Ví dụ: “Tính trung bình doanh thu của 3 ngày gần nhất” (dòng hiện tại và 2 dòng trước đó). Điều này cực kỳ hữu ích để vẽ biểu đồ đường trung bình động (Moving Average).

3 bộ hàm Window Function “phải biết” trên Production

Trong thực tế, 90% công việc của bạn sẽ xoay quanh các nhóm hàm sau:

Nhóm xếp hạng: ROW_NUMBER, RANK, DENSE_RANK

  • ROW_NUMBER(): Đánh số thứ tự thuần túy (1, 2, 3, 4).
  • RANK(): Các giá trị bằng nhau sẽ cùng hạng, nhưng hạng tiếp theo sẽ bị nhảy cóc (1, 2, 2, 4).
  • DENSE_RANK(): Giống RANK nhưng không nhảy số (1, 2, 2, 3).

Nhóm so sánh: LAG và LEAD

Đây là công cụ tuyệt vời để so sánh dữ liệu giữa các dòng. Bạn muốn biết doanh thu hôm nay tăng hay giảm bao nhiêu % so với hôm qua? LAG sẽ lấy giá trị của dòng phía trước để bạn tính toán ngay trên cùng một hàng.

SELECT 
    sale_date, 
    amount,
    LAG(amount, 1) OVER (ORDER BY sale_date) as prev_day_amount
FROM daily_sales;

Nhóm tính tổng lũy kế (Running Total)

Bạn chỉ cần dùng hàm SUM kết hợp với OVER và ORDER BY. Cách này nhanh hơn gấp nhiều lần so với việc dùng subquery lồng nhau trong các phiên bản MySQL cũ.

Ứng dụng thực tế: Tìm Top 3 sản phẩm mỗi danh mục

Trước đây, bài toán này thường yêu cầu dùng UNION cho từng danh mục hoặc viết query rất nặng nề. Với Window Function, mọi thứ trở nên gọn gàng hơn nhiều:

WITH RankedProducts AS (
    SELECT 
        category_id, 
        product_name, 
        total_sold,
        DENSE_RANK() OVER (PARTITION BY category_id ORDER BY total_sold DESC) as rnk
    FROM product_stats
)
SELECT * FROM RankedProducts WHERE rnk <= 3;

Kết hợp Common Table Expression (CTE) và Window Function là bộ đôi hoàn hảo. Code của bạn sẽ vừa chuyên nghiệp, vừa dễ bảo trì cho người sau.

Kinh nghiệm xương máu về hiệu năng

Sau nhiều lần tối ưu hệ thống, tôi rút ra 3 lưu ý quan trọng khi dùng Window Functions:

  • Index là bắt buộc: Hãy đánh index cho các cột trong PARTITION BY và ORDER BY. Nếu thiếu index, MySQL sẽ phải dùng filesort trên ổ cứng. Với bảng 10 triệu dòng, tốc độ query sẽ chậm đi thấy rõ.
  • Kiểm tra bộ nhớ: Window Functions tính toán trực tiếp trên RAM. Nếu tập dữ liệu quá lớn, bạn cần điều chỉnh thông số window_buffer_size trong file cấu hình my.cnf.
  • Dùng đúng lúc: Nếu chỉ cần tính tổng đơn giản (Grand Total), hãy dùng GROUP BY truyền thống. Chỉ nên dùng Window Function khi bạn cần so sánh hoặc tính toán phân đoạn giữa các dòng.

Việc làm chủ Window Functions không chỉ giúp bạn viết code nhanh hơn. Nó còn giúp hệ thống vận hành ổn định, tránh những pha “treo” server bất ngờ vì query quá nặng. Nếu bạn đang quản lý các hệ thống báo cáo hoặc tài chính, hãy thử refactor lại các câu lệnh cũ ngay hôm nay.

Share:
Tags: