Mệnh đề PARTITION BY được sử dụng bên trong mệnh đề OVER() của một hàm phân tích (như SUM, AVG, ROW_NUMBER, RANK).
Nhiệm vụ chính của nó là chia tập kết quả (result set) thành các phân vùng (partitions) nhỏ hơn dựa trên một hoặc nhiều cột. Sau đó, hàm phân tích sẽ được tính toán độc lập trên từng phân vùng này và khởi động lại từ đầu khi chuyển sang phân vùng mới.
Sự khác biệt giữ: GROUP BY vs. PARTITION BY
Đây là điểm dễ gây nhầm lẫn nhất khi mới làm quen với SQL:
GROUP BY: Gom nhóm các dòng có cùng giá trị thành một dòng duy nhất. Bạn sẽ mất đi thông tin chi tiết của từng dòng ban đầu (ví dụ: chỉ xem được tổng doanh thu của một khoa, không thấy được từng bác sĩ trong khoa đó).
PARTITION BY: Tính toán giá trị tổng hợp nhưng giữ nguyên toàn bộ các dòng chi tiết. Giá trị tổng hợp được gắn thêm vào như một cột mới trên mỗi dòng.
Cú pháp
SELECT
cột_1,
cột_2,
TÊN_HÀM_PHÂN_TÍCH() OVER (
PARTITION BY cột_phân_vùng
ORDER BY cột_sắp_xếp
) AS ten_cot_moi
FROM ten_bang;
TÊN_HÀM_PHÂN_TÍCH: Các hàm như SUM(), COUNT(), ROW_NUMBER(), LEAD(), LAG().
PARTITION BY: Chỉ định tiêu chí chia nhóm (tùy chọn).
ORDER BY: Chỉ định thứ tự xử lý dữ liệu bên trong từng phân vùng (rất quan trọng đối với các phép tính lũy kế hoặc xếp hạng).
Các ví dụ
Để hiểu rõ cách vận hành, hãy xem xét các truy vấn được áp dụng trong hai ngữ cảnh hệ thống quen thuộc: Quản lý phòng khám (Clinic Management) và Sổ cái kế toán (General Ledger).
Ví dụ 1: Tính tỷ trọng doanh thu (Kết hợp chi tiết và tổng hợp)
Bài toán: Trong hệ thống quản lý phòng khám, bạn cần hiển thị danh sách tất cả các ca khám bệnh của từng bác sĩ, kèm theo cột hiển thị "Tổng doanh thu của cả khoa" để tính tỷ trọng đóng góp của bác sĩ đó mà không dùng đến JOIN phức tạp.
SELECT
ma_ca_kham,
ten_bac_si,
khoa_phong,
doanh_thu_ca_kham,
SUM(doanh_thu_ca_kham) OVER (PARTITION BY khoa_phong) AS tong_doanh_thu_khoa,
ROUND(doanh_thu_ca_kham / SUM(doanh_thu_ca_kham) OVER (PARTITION BY khoa_phong) * 100, 2) AS ty_trong_phan_tram
FROM
ho_so_kham_benh
WHERE
ngay_kham = TRUNC(SYSDATE);
Giải thích: Truy vấn vẫn trả về hàng trăm ca khám bệnh chi tiết. Tuy nhiên, cột tong_doanh_thu_khoa sẽ tính tổng tiền cho từng khoa_phong và lặp lại giá trị đó trên mọi dòng thuộc khoa tương ứng.
Ví dụ 2: Xếp hạng dữ liệu với ROW_NUMBER() và RANK()
Bài toán: Lấy ra danh sách 3 bác sĩ có doanh thu cao nhất trong mỗi khoa.
WITH XepHangBacSi AS (
SELECT
ten_bac_si,
khoa_phong,
SUM(doanh_thu) as tong_doanh_thu_ca_nhan,
RANK() OVER (PARTITION BY khoa_phong ORDER BY SUM(doanh_thu) DESC) as thu_hang
FROM
ho_so_kham_benh
GROUP BY
ten_bac_si, khoa_phong
)
SELECT *
FROM XepHangBacSi
WHERE thu_hang <= 3;
Giải thích: PARTITION BY khoa_phong chia dữ liệu thành từng khoa. ORDER BY SUM(doanh_thu) DESC sắp xếp doanh thu giảm dần trong nội bộ khoa đó. Hàm RANK() sẽ đánh số 1, 2, 3... và khởi động lại lại từ 1 khi chuyển sang khoa khác.
Ví dụ 3: Tính số dư lũy kế (Running Total / Cân bằng nợ có)
Bài toán: Trong module kế toán, bạn cần tính số dư lũy kế (cộng dồn các phát sinh nợ/có) qua từng giao dịch của một tài khoản theo thứ tự thời gian.
SELECT
ma_tai_khoan,
ngay_giao_dich,
ma_chung_tu,
so_tien_phat_sinh, -- Số dương là Nợ, số âm là Có
SUM(so_tien_phat_sinh) OVER (
PARTITION BY ma_tai_khoan
ORDER BY ngay_giao_dich, ma_chung_tu
) AS so_du_luy_ke
FROM
chi_tiet_so_cai
WHERE
ky_ke_toan = '2026-09';
Giải thích: Bằng cách kết hợp PARTITION BY ma_tai_khoan và ORDER BY ngay_giao_dich, Oracle hiểu rằng nó cần tính tổng cộng dồn (running sum) dòng hiện tại với các dòng trước đó trong cùng một tài khoản. Sự kết hợp này giải quyết bài toán sổ cái cực kỳ hiệu quả mà không cần dùng đến con trỏ (cursor) hay vòng lặp.
Tối ưu trong Oracle
Khi sử dụng PARTITION BY với các tập dữ liệu lớn, hãy lưu ý các nguyên tắc tối ưu sau:
Đánh Index phù hợp: Để truy vấn chạy nhanh, hãy tạo một Composite Index (chỉ mục phức hợp) bao gồm cột trong PARTITION BY và cột trong ORDER BY. Ví dụ: CREATE INDEX idx_sdt_taikhoan_ngay ON chi_tiet_so_cai(ma_tai_khoan, ngay_giao_dich);. Oracle có thể sử dụng index này để tránh việc sắp xếp lại dữ liệu (SORT ORDER BY) tốn bộ nhớ.
Tránh lạm dụng ORDER BY khi không cần thiết: Nếu bạn chỉ cần tính tổng toàn cục của một phân vùng (như Ví dụ 1), đừng đưa ORDER BY vào trong mệnh đề OVER(). Việc thêm ORDER BY sẽ kích hoạt tính năng tính lũy kế (Windowing clause: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), làm truy vấn chậm đi đáng kể.
Sử dụng Clause Windowing Cụ Thể: Khi cần tính toán trên một cửa sổ dữ liệu trượt (ví dụ: trung bình doanh thu của 3 ngày gần nhất), hãy định nghĩa rõ ràng ROWS BETWEEN 2 PRECEDING AND CURRENT ROW để giới hạn phạm vi quét dữ liệu của Oracle.
Việc nắm vững PARTITION BY không chỉ giúp mã SQL gọn gàng hơn mà còn chuyển gánh nặng xử lý logic (như tính lũy kế, phân trang, gom nhóm) từ tầng Application (C#, Java) xuống tầng Database, nơi phần mềm Oracle Engine đã được tối ưu hóa ở mức C/C++ để thực thi các phép toán này với tốc độ cao nhất.
Mệnh đề PARTITION BY là gì?
Mệnh đề PARTITION BY được sử dụng bên trong mệnh đề OVER() của một hàm phân tích (như SUM, AVG, ROW_NUMBER, RANK).
Nhiệm vụ chính của nó là chia tập kết quả (result set) thành các phân vùng (partitions) nhỏ hơn dựa trên một hoặc nhiều cột. Sau đó, hàm phân tích sẽ được tính toán độc lập trên từng phân vùng này và khởi động lại từ đầu khi chuyển sang phân vùng mới.
Sự khác biệt giữ: GROUP BY vs. PARTITION BY
Đây là điểm dễ gây nhầm lẫn nhất khi mới làm quen với SQL:
GROUP BY: Gom nhóm các dòng có cùng giá trị thành một dòng duy nhất. Bạn sẽ mất đi thông tin chi tiết của từng dòng ban đầu (ví dụ: chỉ xem được tổng doanh thu của một khoa, không thấy được từng bác sĩ trong khoa đó).
PARTITION BY: Tính toán giá trị tổng hợp nhưng giữ nguyên toàn bộ các dòng chi tiết. Giá trị tổng hợp được gắn thêm vào như một cột mới trên mỗi dòng.
Cú pháp
TÊN_HÀM_PHÂN_TÍCH: Các hàm như SUM(), COUNT(), ROW_NUMBER(), LEAD(), LAG().
PARTITION BY: Chỉ định tiêu chí chia nhóm (tùy chọn).
ORDER BY: Chỉ định thứ tự xử lý dữ liệu bên trong từng phân vùng (rất quan trọng đối với các phép tính lũy kế hoặc xếp hạng).
Các ví dụ
Để hiểu rõ cách vận hành, hãy xem xét các truy vấn được áp dụng trong hai ngữ cảnh hệ thống quen thuộc: Quản lý phòng khám (Clinic Management) và Sổ cái kế toán (General Ledger).
Ví dụ 1: Tính tỷ trọng doanh thu (Kết hợp chi tiết và tổng hợp)
Bài toán: Trong hệ thống quản lý phòng khám, bạn cần hiển thị danh sách tất cả các ca khám bệnh của từng bác sĩ, kèm theo cột hiển thị "Tổng doanh thu của cả khoa" để tính tỷ trọng đóng góp của bác sĩ đó mà không dùng đến JOIN phức tạp.
Giải thích: Truy vấn vẫn trả về hàng trăm ca khám bệnh chi tiết. Tuy nhiên, cột tong_doanh_thu_khoa sẽ tính tổng tiền cho từng khoa_phong và lặp lại giá trị đó trên mọi dòng thuộc khoa tương ứng.
Ví dụ 2: Xếp hạng dữ liệu với ROW_NUMBER() và RANK()
Bài toán: Lấy ra danh sách 3 bác sĩ có doanh thu cao nhất trong mỗi khoa.
Giải thích: PARTITION BY khoa_phong chia dữ liệu thành từng khoa. ORDER BY SUM(doanh_thu) DESC sắp xếp doanh thu giảm dần trong nội bộ khoa đó. Hàm RANK() sẽ đánh số 1, 2, 3... và khởi động lại lại từ 1 khi chuyển sang khoa khác.
Ví dụ 3: Tính số dư lũy kế (Running Total / Cân bằng nợ có)
Bài toán: Trong module kế toán, bạn cần tính số dư lũy kế (cộng dồn các phát sinh nợ/có) qua từng giao dịch của một tài khoản theo thứ tự thời gian.
Giải thích: Bằng cách kết hợp PARTITION BY ma_tai_khoan và ORDER BY ngay_giao_dich, Oracle hiểu rằng nó cần tính tổng cộng dồn (running sum) dòng hiện tại với các dòng trước đó trong cùng một tài khoản. Sự kết hợp này giải quyết bài toán sổ cái cực kỳ hiệu quả mà không cần dùng đến con trỏ (cursor) hay vòng lặp.
Tối ưu trong Oracle
Khi sử dụng PARTITION BY với các tập dữ liệu lớn, hãy lưu ý các nguyên tắc tối ưu sau:
Đánh Index phù hợp: Để truy vấn chạy nhanh, hãy tạo một Composite Index (chỉ mục phức hợp) bao gồm cột trong PARTITION BY và cột trong ORDER BY. Ví dụ: CREATE INDEX idx_sdt_taikhoan_ngay ON chi_tiet_so_cai(ma_tai_khoan, ngay_giao_dich);. Oracle có thể sử dụng index này để tránh việc sắp xếp lại dữ liệu (SORT ORDER BY) tốn bộ nhớ.
Tránh lạm dụng ORDER BY khi không cần thiết: Nếu bạn chỉ cần tính tổng toàn cục của một phân vùng (như Ví dụ 1), đừng đưa ORDER BY vào trong mệnh đề OVER(). Việc thêm ORDER BY sẽ kích hoạt tính năng tính lũy kế (Windowing clause: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), làm truy vấn chậm đi đáng kể.
Sử dụng Clause Windowing Cụ Thể: Khi cần tính toán trên một cửa sổ dữ liệu trượt (ví dụ: trung bình doanh thu của 3 ngày gần nhất), hãy định nghĩa rõ ràng ROWS BETWEEN 2 PRECEDING AND CURRENT ROW để giới hạn phạm vi quét dữ liệu của Oracle.
Việc nắm vững PARTITION BY không chỉ giúp mã SQL gọn gàng hơn mà còn chuyển gánh nặng xử lý logic (như tính lũy kế, phân trang, gom nhóm) từ tầng Application (C#, Java) xuống tầng Database, nơi phần mềm Oracle Engine đã được tối ưu hóa ở mức C/C++ để thực thi các phép toán này với tốc độ cao nhất.