Chạy ngay trong 5 phút — Thấy kết quả trước, hiểu sau
Vẫn đang viết subquery lồng 3-4 tầng để xếp hạng nhân viên? Hay dùng self-join để so sánh doanh số tháng này với tháng trước? Window Functions MySQL 8 giải quyết cả hai trong một câu query — không cần subquery, không cần join thêm. Xếp hạng, so sánh dòng kề nhau, tổng lũy kế: viết thẳng trong SELECT.
Tạo bảng test và chạy thử ngay:
CREATE TABLE sales (
id INT AUTO_INCREMENT PRIMARY KEY,
employee_name VARCHAR(50),
department VARCHAR(50),
sale_amount DECIMAL(10,2),
sale_date DATE
);
INSERT INTO sales (employee_name, department, sale_amount, sale_date) VALUES
('Minh', 'Tech', 15000000, '2024-01-15'),
('Lan', 'Tech', 22000000, '2024-01-16'),
('Hùng', 'Sales', 18000000, '2024-01-15'),
('Thảo', 'Sales', 18000000, '2024-01-17'),
('Nam', 'Tech', 22000000, '2024-01-18'),
('Hoa', 'Sales', 25000000, '2024-01-19'),
('Khoa', 'Tech', 12000000, '2024-01-20');-- Xếp hạng nhân viên trong từng phòng ban — 3 hàm cạnh nhau để so sánh
SELECT
employee_name,
department,
sale_amount,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale_amount DESC) AS row_num,
RANK() OVER (PARTITION BY department ORDER BY sale_amount DESC) AS rnk,
DENSE_RANK() OVER (PARTITION BY department ORDER BY sale_amount DESC) AS dense_rnk
FROM sales;Chạy xong, để ý Lan và Nam cùng 22 triệu nhưng ba hàm cho kết quả khác hẳn. Phần dưới giải thích từng trường hợp.
Giải thích chi tiết: Từng hàm làm gì
ROW_NUMBER() — Số thứ tự tuyệt đối, không bao giờ trùng
Dù hai dòng có giá trị bằng nhau, ROW_NUMBER vẫn gán số khác nhau. Thứ tự của hai dòng tie là không xác định — engine tự quyết, không đoán trước được. Thêm cột tie-breaker vào ORDER BY (ví dụ: id sau sale_amount DESC) để kết quả nhất quán giữa các lần chạy.
-- Lấy đúng 1 nhân viên doanh số cao nhất mỗi phòng (không bao giờ trả 2 dòng)
SELECT * FROM (
SELECT
employee_name, department, sale_amount,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale_amount DESC) AS rn
FROM sales
) t
WHERE rn = 1;RANK() — Đồng hạng, nhảy số tiếp theo
Lan và Nam cùng 22 triệu → cùng rank 1. Rank tiếp theo nhảy lên 3, bỏ qua 2. Hợp lý khi làm bảng xếp hạng thực tế: hai người giỏi ngang nhau thì không ai xứng gọi là hạng nhì.
-- Top 2 mỗi phòng — có thể trả về 3 dòng nếu có đồng hạng 1
SELECT * FROM (
SELECT
employee_name, department, sale_amount,
RANK() OVER (PARTITION BY department ORDER BY sale_amount DESC) AS rnk
FROM sales
) t
WHERE rnk <= 2;DENSE_RANK() — Đồng hạng, số liên tục không nhảy
Cơ bản giống RANK nhưng số không bị nhảy. Đồng hạng 1 → tiếp theo là 2, không phải 3. Hay dùng cho báo cáo phân nhóm: nhóm top-1, nhóm top-2, nhóm top-3 — số thứ hạng có nghĩa rõ ràng hơn.
-- So sánh rõ sự khác biệt
SELECT
employee_name,
sale_amount,
RANK() OVER (ORDER BY sale_amount DESC) AS rank_val,
DENSE_RANK() OVER (ORDER BY sale_amount DESC) AS dense_val
FROM sales;
-- Hoa: 25tr → rank 1 / dense 1
-- Lan, Nam: 22tr → rank 2 / dense 2
-- Hùng, Thảo: 18tr → rank 4 / dense 3 ← đây là điểm khác nhauLAG() và LEAD() — So sánh với dòng liền kề
Cá nhân mình dùng hai hàm này nhiều nhất khi làm báo cáo tháng. LAG() kéo giá trị từ dòng trước, LEAD() nhìn sang dòng sau — cùng partition và thứ tự sắp xếp.
-- Tính % thay đổi doanh số so với giao dịch trước của từng nhân viên
SELECT
employee_name,
sale_date,
sale_amount,
LAG(sale_amount) OVER (PARTITION BY employee_name ORDER BY sale_date) AS prev_sale,
LEAD(sale_amount) OVER (PARTITION BY employee_name ORDER BY sale_date) AS next_sale,
ROUND(
(sale_amount - LAG(sale_amount) OVER (PARTITION BY employee_name ORDER BY sale_date))
/ LAG(sale_amount) OVER (PARTITION BY employee_name ORDER BY sale_date) * 100, 2
) AS pct_change
FROM sales;LAG/LEAD nhận tối đa 3 tham số: LAG(column, offset, default). Offset mặc định là 1, default là NULL khi không có dòng trước/sau:
-- Lấy giá trị 2 dòng trước, trả 0 nếu không tồn tại
LAG(sale_amount, 2, 0) OVER (PARTITION BY department ORDER BY sale_date)Nâng cao — Kỹ thuật kết hợp thực tế
Running Total với SUM() OVER và frame clause
Hàm xếp hạng chỉ là phần nổi. SUM, AVG, MAX, MIN đều chạy được với OVER — và frame clause mới là lúc tính năng này thực sự có ích:
SELECT
employee_name,
department,
sale_date,
sale_amount,
-- Tổng lũy kế trong phòng theo ngày
SUM(sale_amount) OVER (
PARTITION BY department
ORDER BY sale_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total,
-- Trung bình toàn phòng (không phụ thuộc ORDER BY)
AVG(sale_amount) OVER (PARTITION BY department) AS dept_avg
FROM sales
ORDER BY department, sale_date;Named Window — Viết một lần, dùng nhiều lần
Nhiều hàm dùng cùng partition và order? Thay vì copy-paste 4 lần, MySQL 8 cho đặt tên window và dùng lại. Sửa logic partition là sửa một chỗ duy nhất:
SELECT
employee_name,
department,
sale_amount,
ROW_NUMBER() OVER w AS row_num,
RANK() OVER w AS rnk,
DENSE_RANK() OVER w AS dense_rnk,
LAG(sale_amount) OVER w AS prev_amount
FROM sales
WINDOW w AS (PARTITION BY department ORDER BY sale_amount DESC);
-- Thay vì lặp lại (PARTITION BY department ORDER BY sale_amount DESC) 4 lầnKết hợp CTE để xử lý bài toán phức tạp
CTE và Window Functions kết hợp rất tự nhiên: CTE tính rank và dept_avg trong một lần scan, query ngoài chỉ cần lọc theo kết quả — không một lớp subquery lồng nào:
-- Lấy nhân viên doanh số trên trung bình phòng, kèm rank trong phòng
WITH dept_ranked AS (
SELECT
employee_name,
department,
sale_amount,
AVG(sale_amount) OVER (PARTITION BY department) AS dept_avg,
DENSE_RANK() OVER (PARTITION BY department ORDER BY sale_amount DESC) AS dept_rank
FROM sales
)
SELECT
employee_name,
department,
sale_amount,
ROUND(dept_avg, 0) AS dept_avg,
dept_rank
FROM dept_ranked
WHERE sale_amount > dept_avg
ORDER BY department, dept_rank;Phát hiện gap trong chuỗi dữ liệu
Thủ thuật nhỏ nhưng hữu dụng: LEAD() còn dùng để phát hiện ID bị thiếu trong sequence, không cần self-join:
-- Tìm ID bị thiếu trong sequence
SELECT
id,
LEAD(id) OVER (ORDER BY id) AS next_id,
LEAD(id) OVER (ORDER BY id) - id - 1 AS gap_size
FROM sales
HAVING gap_size > 0;Tips thực tế từ kinh nghiệm
1. EXPLAIN ANALYZE trước khi deploy
Database production mình chạy MySQL 8.0, khoảng 50GB data. Từng có query window function bị timeout 15 giây trên bảng giao dịch 8 triệu dòng — sau khi thêm composite index còn 0.3 giây. Vì sao chậm? Window Functions phải buffer toàn bộ partition vào RAM trước khi tính toán. Luôn kiểm tra trước khi deploy:
EXPLAIN ANALYZE
SELECT
employee_name,
RANK() OVER (PARTITION BY department ORDER BY sale_amount DESC) AS rnk
FROM sales;
-- Chú ý: "Using temporary" và "Using filesort" trong output
-- Nếu thấy tmp disk usage lớn → cần index hoặc pre-aggregate2. Index đúng chỗ — partition column trước, order column sau
-- Với PARTITION BY department ORDER BY sale_amount
CREATE INDEX idx_dept_sale ON sales (department, sale_amount DESC);
-- Covering index nếu SELECT chỉ cần vài cột
CREATE INDEX idx_dept_sale_cover ON sales (department, sale_amount DESC, employee_name);3. Hiểu rõ ROWS vs RANGE
Điểm hay nhầm nhất khi mới dùng frame clause:
-- ROWS: đếm đúng N dòng vật lý
SUM(sale_amount) OVER (
ORDER BY sale_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)
-- → Cộng 3 dòng gần nhất (vật lý), bất kể giá trị
-- RANGE: đếm theo khoảng giá trị, đồng giá trị tính chung
SUM(sale_amount) OVER (
ORDER BY sale_date
RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW
)
-- → Cộng tất cả dòng trong 7 ngày gần nhấtDùng RANGE khi logic là “trong khoảng giá trị/thời gian”. Dùng ROWS khi cần chính xác N dòng.
4. Tránh dùng ROW_NUMBER thay thế LIMIT với bảng lớn
Dùng ROW_NUMBER để phân trang nghe có vẻ hay, nhưng MySQL scan toàn bộ bảng và tính số thứ tự cho mọi dòng rồi mới lọc — chi phí không khác gì OFFSET thông thường. Bảng dưới 100k dòng: chấp nhận được. Triệu dòng trở lên: chuyển sang keyset pagination.
Nếu bạn đang cân nhắc nâng cấp từ MySQL 5.7 lên 8.x, Window Functions là lý do thực tế nhất để làm. Bài toán từng cần 3-4 tầng subquery lồng nhau giờ gọn trong một CTE + window clause. Code ngắn, dễ maintain — và query planner optimize tốt hơn khi không còn subquery lồng để parse.
