Sử dụng PARTITION BY để chia dữ liệu thành nhóm

Hãy tưởng tượng bạn đang làm phục vụ (hoặc barista nếu bạn thích cà phê) ở một nhà hàng lớn. Mỗi ngày bạn tổng kết tiền tip kiếm được. Nhưng có một điểm: nhà hàng được chia thành các khu vực, và bạn muốn biết mỗi khu vực kiếm được bao nhiêu tip riêng biệt. PARTITION BY — chính là thứ mà SQL dùng để "chia nhà hàng thành các khu vực".

Nói một cách formal hơn, PARTITION BY được dùng trong các hàm cửa sổ để chia tất cả các dòng trong bảng thành các nhóm riêng biệt (hoặc "partition"). Bên trong mỗi nhóm, hàm cửa sổ sẽ chạy lại từ đầu. Kiểu như bạn áp dụng hàm riêng biệt cho từng "partition" vậy.

Ví dụ: nó hoạt động như thế nào

Giả sử mình có bảng sales chứa dữ liệu bán hàng:

Nếu muốn tính xem mỗi người bán hàng kiếm được bao nhiêu tiền, nhưng tách riêng cho từng khu vực, PARTITION BY — chính là thứ bạn cần.

Cú pháp PARTITION BY

Cú pháp khá đơn giản:

window_function() OVER (PARTITION BY column_or_columns)

• window_function() — ví dụ như SUM(), AVG(), ROW_NUMBER() v.v.
• PARTITION BY column — chỉ định cột nào để chia nhóm các dòng.
• OVER() — là toán tử bảo SQL: "Hãy làm gì đó trong phạm vi cửa sổ này".

Ví dụ: tính tổng theo nhóm

Hãy tính tổng doanh số cho từng khu vực:

SELECT
    region,
    salesperson,
    amount,
    SUM(amount) OVER (PARTITION BY region) AS total_sales_by_region
FROM sales;

Kết quả sẽ như này:

Chuyện gì xảy ra ở đây? SQL chia các dòng thành nhóm theo giá trị cột region (North và South), rồi áp dụng hàm SUM() riêng cho từng nhóm. Kết quả là các dòng trong nhóm "North" đều nhận cùng một giá trị tổng, còn nhóm "South" cũng vậy.

Ví dụ sử dụng PARTITION BY

Cùng xem PARTITION BY hữu ích thế nào trong các bài toán thực tế nhé.

Ví dụ 1: Xếp hạng trong nhóm

Giả sử bạn muốn xếp hạng các nhân viên bán hàng trong từng khu vực theo số lượng bán được. Có thể dùng kết hợp PARTITION BY và hàm RANK():

SELECT
    region,
    salesperson,
    amount,
    RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rank_in_region
FROM sales;

Kết quả:

Hàm RANK() sẽ gán thứ hạng trong từng nhóm region, bắt đầu từ 1. Lưu ý là mỗi nhóm thứ hạng đều bắt đầu từ số một nhé.

Ví dụ 2: So sánh từng giá trị với trung bình nhóm

Giả sử bạn muốn xem mỗi nhân viên bán hàng kiếm được bao nhiêu so với trung bình của khu vực mình. Dùng AVG() nhé:

SELECT
    region,
    salesperson,
    amount,
    AVG(amount) OVER (PARTITION BY region) AS avg_sales_by_region,
    amount - AVG(amount) OVER (PARTITION BY region) AS diff_from_avg
FROM sales;

Kết quả:

Đầu tiên SQL chia các dòng thành nhóm theo region. Sau đó tính giá trị trung bình AVG(amount) cho từng nhóm. Cuối cùng, với mỗi dòng, nó tính hiệu giữa giá trị của dòng đó và trung bình nhóm.

Ví dụ 3: Đánh số dòng trong nhóm

Giả sử bạn muốn đánh số tất cả giao dịch trong từng nhóm khu vực. Dùng ROW_NUMBER() nhé:

SELECT
    region,
    salesperson,
    amount,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS row_number
FROM sales;

Kết quả:

So sánh với GROUP BY

Nhiều bạn hay nhầm giữa PARTITION BY và GROUP BY. So sánh thử nhé:

GROUP BY

GROUP BY sẽ thay đổi cấu trúc kết quả — nó biến các dòng thành các aggregate. Ví dụ:

SELECT
    region,
    SUM(amount) AS total_sales
FROM sales
GROUP BY region;

Kết quả:

Ở đây mình mất thông tin về từng nhân viên bán hàng, vì dữ liệu đã được tổng hợp lại rồi.

PARTITION BY

PARTITION BY thì ngược lại, không thay đổi cấu trúc. Bạn vẫn thấy từng dòng, nhưng có thêm các giá trị tính toán theo nhóm. Tức là, PARTITION BY cho phép aggregate mà không mất chi tiết.

Lỗi thường gặp khi dùng PARTITION BY

Lỗi 1: Quên PARTITION BY

Đôi khi bạn muốn nhóm dữ liệu nhưng lại quên dùng PARTITION BY. Ví dụ:

SELECT
    region,
    salesperson,
    amount,
    SUM(amount) OVER () AS total_sales
FROM sales;

Kết quả:

Ở đây SUM(amount) được tính cho toàn bộ bảng, chứ không phải từng khu vực. Nếu muốn tính theo khu vực, nhớ thêm PARTITION BY region nhé.

Lỗi 2: Sai thứ tự trong ORDER BY

Thứ tự dòng trong cửa sổ rất quan trọng với các hàm như RANK() hoặc ROW_NUMBER(). Hãy cẩn thận khi dùng ORDER BY bên trong OVER() nhé.