ROLLUP trong SQL Server

Trong hướng dẫn này, bạn sẽ học cách sử dụng ROLLUP trong SQL Server để tạo nhiều tập hợp nhóm.

Giới thiệu về ROLLUP trong SQL Server

ROLLUP trong SQL Server là một mệnh đề con của mệnh đề GROUP BY cung cấp cách viết tắt để xác định nhiều GROUPING SETS. Không giống như CUBE – tạo các tập nhóm với tất cả các kết hợp của các cột, ROLLUP không tạo tất cả các tập hợp nhóm có thể dựa trên các cột thứ nguyên.

Khi tạo các tập hợp nhóm, ROLLUP giả định một hệ thống phân cấp giữa các cột thứ nguyên và chỉ tạo các tập hợp nhóm dựa trên hệ thống phân cấp này.

ROLLUP thường được sử dụng để tạo ra tổng số phụ (subtotals) và tổng số (totals) cho mục đích báo cáo.

Hãy xem xét ví dụ sau đây: mệnh đề CUBE (d1,d2,d3) sẽ xác định tám tập hợp nhóm như sau:

(d1, d2, d3)
(d1, d2)
(d2, d3)
(d1, d3)
(d1)
(d2)
(d3)
()

Nhưng mệnh đề ROLLUP(d1,d2,d3) chỉ tạo ra bốn tập hợp nhóm (giả sử phân cấp d1 > d2 > d3) như sau:

(d1, d2, d3)
(d1, d2)
(d1)
()

Mệnh đề ROLLUP thường được sử dụng để tính toán tổng hợp của dữ liệu phân cấp như bán hàng theo năm > quý > tháng.

Cú pháp của ROLLUP trong SQL Server

Cú pháp chung của ROLLUP trong SQL Server như sau:

SELECT
    d1,
    d2,
    d3,
    aggregate_function(c4)
FROM
    table_name
GROUP BY
    ROLLUP (d1, d2, d3);

Trong cú pháp này, d1, d2 và d3 là các cột thứ nguyên. Câu lệnh sẽ tính tổng các giá trị trong cột c4 dựa trên thứ bậc d1> d2> d3.

Bạn cũng có thể ROLLUP một phần để giảm tổng số phụ (subtotals) được tạo bằng cách sử dụng cú pháp sau:

SELECT
    d1,
    d2,
    d3,
    aggregate_function(c4)
FROM
    table_name
GROUP BY
    d1, 
    ROLLUP (d2, d3);

Ví dụ về ROLLUP trong SQL Server

Chúng ta sẽ sử dụng lại bảng sales.sales_summary trong cơ sở dữ liệu mẫu BikeStores đã tạo trong phần hướng dẫn về GROUPING SETS để minh họa. Nếu bạn chưa tạo bảng sales.sales_summary, bạn có thể sử dụng câu lệnh sau để tạo bảng.

SELECT
    b.brand_name AS brand,
    c.category_name AS category,
    p.model_year,
    round(
        SUM (
            quantity * i.list_price * (1 - discount)
        ),
        0
    ) sales INTO sales.sales_summary
FROM
    sales.order_items i
INNER JOIN production.products p ON p.product_id = i.product_id
INNER JOIN production.brands b ON b.brand_id = p.brand_id
INNER JOIN production.categories c ON c.category_id = p.category_id
GROUP BY
    b.brand_name,
    c.category_name,
    p.model_year
ORDER BY
    b.brand_name,
    c.category_name,
    p.model_year;

Truy vấn sau đây sử dụng ROLLUP để tính toán số tiền bán hàng theo thương hiệu (tổng số phụ – subtotals) và cả thương hiệu và danh mục (tổng số – totals).

SELECT
    brand,
    category,
    SUM (sales) sales
FROM
    sales.sales_summary
GROUP BY
    ROLLUP(brand, category);

Đây là đầu ra:

Trong ví dụ này, truy vấn giả định rằng có một hệ thống phân cấp giữa thương hiệu và danh mục, đó là thương hiệu > danh mục.

Lưu ý rằng nếu bạn thay đổi thứ tự của thương hiệu và danh mục, kết quả sẽ khác như được hiển thị trong truy vấn sau:

SELECT
    category,
    brand,
    SUM (sales) sales
FROM
    sales.sales_summary
GROUP BY
    ROLLUP (category, brand);

Trong ví dụ này, phân cấp là thương hiệu > phân khúc:

Câu lệnh sau cho biết cách thực hiện ROLLUP một phần:

SELECT
    brand,
    category,
    SUM (sales) sales
FROM
    sales.sales_summary
GROUP BY
    brand,
    ROLLUP (category);

Và đầu ra là:

Trong hướng dẫn này, bạn đã học cách sử dụng ROLLUP trong SQL Server để tạo nhiều tập hợp nhóm với giả định về hệ thống phân cấp của các cột đầu vào.