Cách sử dụng ROLLUP trong MySQL

Theo dõi Code Tu Tam trên

Kiến thức MySql Cơ Bản

18/06/2023 -

Post Views: 250

lượt xem

Rate this post

Trong bài viết này, bạn sẽ học cách sử dụng mệnh đề ROLLUP MySQL để tạo tổng phụ và tổng lớn.

Mục lục

Thiết lập một bảng mẫu

Câu lệnh sau đây tạo một bảng mới có tên sales lưu trữ các giá trị đơn hàng được tóm tắt theo dòng sản phẩm và năm. Dữ liệu đến từ các bảng products, orders, và orderDetails trong cơ sở dữ liệu mẫu .

CREATE TABLE sales
SELECT
    productLine,
    YEAR(orderDate) orderYear,
    SUM(quantityOrdered * priceEach) orderValue
FROM
    orderDetails
        INNER JOIN
    orders USING (orderNumber)
        INNER JOIN
    products USING (productCode)
GROUP BY
    productLine ,
    YEAR(orderDate);

Truy vấn sau trả về tất cả các hàng từ bảng sales:

SELECT * FROM sales;

Kết quả:

Tổng kết về MySQL ROLLUP

Tập hợp nhóm là một tập hợp các cột mà bạn muốn nhóm vào. Ví dụ: truy vấn sau đây tạo một nhóm được biểu thị bằng (productline)

SELECT 
    productline, 
    SUM(orderValue) totalOrderValue
FROM
    sales
GROUP BY 
    productline;

Kết quả:

Truy vấn sau đây tạo một tập hợp nhóm trống được biểu thị bằng ():

SELECT 
    SUM(orderValue) totalOrderValue
FROM
    sales;

Kết quả:

Nếu bạn muốn tạo hai hoặc nhiều tập hợp nhóm cùng nhau trong một truy vấn, bạn có thể sử dụng toán tử UNION ALL như sau:

SELECT 
    productline, 
    SUM(orderValue) totalOrderValue
FROM
    sales
GROUP BY 
    productline 
UNION ALL
SELECT 
    NULL, 
    SUM(orderValue) totalOrderValue
FROM
    sales;

kết quả:

Vì UNION ALL yêu cầu tất cả các truy vấn phải có cùng số lượng cột nên chúng tôi đã thêm NULL vào danh sách chọn của truy vấn thứ hai để đáp ứng yêu cầu này.

Trong cột NULL xác định dòng productLine siêu tổng hợp tổng lớn.

Truy vấn này có thể tạo tổng giá trị đơn hàng theo dòng sản phẩm và cả tổng hàng lớn. Tuy nhiên, nó có hai vấn đề:

Truy vấn khá dài.
Hiệu suất của truy vấn có thể không tốt do công cụ cơ sở dữ liệu phải thực thi nội bộ hai truy vấn riêng biệt và kết hợp các tập kết quả thành một.

Để khắc phục những sự cố này, bạn có thể sử dụng mệnh đề ROLLUP.

Mệnh đề ROLLUP là phần mở rộng của mệnh đề GROUP BY với cú pháp như sau:

SELECT 
    select_list
FROM 
    table_name
GROUP BY
    c1, c2, c3 WITH ROLLUP;

Việc ROLLUP tạo nhiều tập hợp nhóm dựa trên các cột hoặc biểu thức được chỉ định trong mệnh đề GROUP BY. Ví dụ:

SELECT 
    productLine, 
    SUM(orderValue) totalOrderValue
FROM
    sales
GROUP BY 
    productline WITH ROLLUP;

kết quả:

Từ kết quả trên mệnh đề ROLLUP không chỉ tạo tổng phụ mà còn tạo tổng lớn của các giá trị đơn hàng.

Nếu bạn có nhiều hơn một cột được chỉ định trong mệnh đề GROUP BY, thì mệnh đề ROLLUP đó sẽ giả định một hệ thống phân cấp giữa các cột đầu vào.

Ví dụ:

GROUP BY c1, c2, c3 WITH ROLLUP

Giả định rằng ROLLUP có hệ thống phân cấp sau:

c1 > c2 > c3

Và nó tạo ra các tập hợp nhóm sau:

(c1, c2, c3)
(c1, c2)
(c1)
()

Và trong trường hợp bạn có hai cột được chỉ định trong mệnh đề GROUP BY:

GROUP BY c1, c2 WITH ROLLUP

sau đó ROLLUP tạo ra các tập hợp nhóm sau:

(c1, c2)
(c1)
()

Xem ví dụ truy vấn sau:

SELECT 
    productLine, 
    orderYear,
    SUM(orderValue) totalOrderValue
FROM
    sales
GROUP BY 
    productline, 
    orderYear 
WITH ROLLUP;

kết quả:

Việc ROLLUP tạo hàng tổng phụ mỗi khi dòng sản phẩm thay đổi và tổng cuối ở cuối kết quả.

Thứ bậc trong trường hợp này là:

productLine > orderYear

Ví dụ: nếu bạn đảo ngược hệ thống phân cấp:

SELECT 
    orderYear,
    productLine, 
    SUM(orderValue) totalOrderValue
FROM
    sales
GROUP BY 
    orderYear,
    productline
WITH ROLLUP;

kết quả:

Hàm ROLLUP tạo tổng phụ mỗi khi năm thay đổi và tổng cuối ở cuối tập kết quả.

Thứ bậc trong ví dụ này là:

orderYear > productLine

Hàm GROUPING() function

Để kiểm tra xem NULL trong tập kết quả có đại diện cho tổng phụ hay tổng lớn hay không, bạn sử dụng hàm GROUPING().

Hàm GROUPING() trả về 1 khi NULL xảy ra trong một hàng siêu tổng hợp, nếu không, nó trả về 0.

Hàm GROUPING() có thể được sử dụng trong danh sách chọn,mệnh đề HAVING và mệnh đề ORDER BY (kể từ MySQL 8.0.12 ).

Hãy xem xét truy vấn sau:

SELECT 
    orderYear,
    productLine, 
    SUM(orderValue) totalOrderValue,
    GROUPING(orderYear),
    GROUPING(productLine)
FROM
    sales
GROUP BY 
    orderYear,
    productline
WITH ROLLUP;

kết quả

GROUPING(orderYear) trả về 1 khi NULL trong cột orderYear xảy ra trong một hàng siêu tổng hợp, 0 nếu không.

Tương tự, GROUPING(productLine) trả về 1 khi NULLtrong cột productLine xảy ra trong một hàng siêu tổng hợp, 0 nếu không.

Chúng tôi thường sử dụng hàm GROUPING() để thay thế các nhãn có ý nghĩa cho các giá trị siêu tổng hợp NULL thay vì hiển thị trực tiếp.

Ví dụ sau đây cho thấy cách kết hợp hàm IF() với hàm GROUPING() để thay thế nhãn cho các giá trị siêu tổng hợp NULL trong cột orderYear và cột productLine:

SELECT 
    IF(GROUPING(orderYear),
        'All Years',
        orderYear) orderYear,
    IF(GROUPING(productLine),
        'All Product Lines',
        productLine) productLine,
    SUM(orderValue) totalOrderValue
FROM
    sales
GROUP BY 
    orderYear , 
    productline 
WITH ROLLUP;

kết quả:

Tổng kết

Trong bài viết này, bạn đã học cách sử dụng MySQL ROLLUP() để tạo nhiều tập hợp nhóm có tính đến hệ thống phân cấp giữa các cột được chỉ định trong mệnh đề GROUP BY.

Các bạn có thể tham khảo thêm tại: https://www.mysqltutorial.org/mysql-rollup/

Các bài viết liên quan: