GROUPING SET trong SQL Server

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

Tạo bảng sales.sales_summary

Chúng tôi sẽ sử dụng cơ sở dữ liệu mẫu BikeStores để minh họa. Đầu tiên chúng ta hãy tạo một bảng mới có tên sales.sales_summary.

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;

Trong truy vấn này, chúng tôi truy xuất dữ liệu số lượng bán hàng theo thương hiệu và danh mục và đưa vào bảng sales.sales_summary.

Truy vấn sau đây trả về dữ liệu từ bảng sales.sales_summary:

SELECT
    *
FROM
    sales.sales_summary
ORDER BY
    brand,
    category,
    model_year;
GROUPING SET trong SQL Server

Bắt đầu với GROUPING SETS trong SQL Server

Theo định nghĩa GROUPING SETS là một nhóm các cột mà bạn nhóm. Thông thường, một truy vấn với một tập hợp xác định một tập hợp nhóm duy nhất.

Ví dụ: truy vấn sau xác định một tập hợp nhóm bao gồm thương hiệu và danh mục được biểu thị là (brand, category). Truy vấn trả về số tiền bán hàng được nhóm theo thương hiệu và danh mục:

SELECT
    brand,
    category,
    SUM (sales) sales
FROM
    sales.sales_summary
GROUP BY
    brand,
    category
ORDER BY
    brand,
    category;
GROUPING SET trong SQL Server

Truy vấn sau đây trả về số lượng bán hàng theo thương hiệu. Nó xác định một tập hợp nhóm (brand):

SELECT
    brand,
    SUM (sales) sales
FROM
    sales.sales_summary
GROUP BY
    brand
ORDER BY
    brand;
GROUPING SET trong SQL Server

Truy vấn sau đây trả về số lượng bán hàng theo danh mục. Nó định nghĩa một tập hợp nhóm (category):

SELECT
    category,
    SUM (sales) sales
FROM
    sales.sales_summary
GROUP BY
    category
ORDER BY
    category;
GROUPING SET trong SQL Server

Truy vấn sau xác định một tập hợp nhóm trống (). Nó trả về số tiền bán hàng cho tất cả các thương hiệu và danh mục.

SELECT
    SUM (sales) sales
FROM
    sales.sales_summary;
GROUPING SET trong SQL Server

Bốn truy vấn trên trả về bốn tập kết quả với bốn nhóm:

(brand, category)
(brand)
(category)
()

Để có được tập hợp kết quả thống nhất với dữ liệu tổng hợp cho tất cả các tập hợp nhóm, bạn có thể sử dụng toán tử UNION ALL.

Vì toán tử UNION ALL yêu cầu tất cả tập hợp kết quả có cùng số cột, bạn cần thêm cột NULL vào danh sách các truy vấn như sau:

SELECT
    brand,
    category,
    SUM (sales) sales
FROM
    sales.sales_summary
GROUP BY
    brand,
    category
UNION ALL
SELECT
    brand,
    NULL,
    SUM (sales) sales
FROM
    sales.sales_summary
GROUP BY
    brand
UNION ALL
SELECT
    NULL,
    category,
    SUM (sales) sales
FROM
    sales.sales_summary
GROUP BY
    category
UNION ALL
SELECT
    NULL,
    NULL,
    SUM (sales)
FROM
    sales.sales_summary
ORDER BY brand, category;
GROUPING SET trong SQL Server

Truy vấn tạo ra một kết quả duy nhất với các tổng hợp cho tất cả các nhóm nhóm như chúng tôi mong đợi.

Tuy nhiên, nó có hai vấn đề lớn:

  1. Truy vấn khá dài.
  2. Truy vấn chậm vì SQL Server cần thực hiện bốn truy vấn con và kết hợp các tập hợp kết quả thành một.

Để khắc phục những sự cố này, SQL Server cung cấp một mệnh đề con của mệnh đề GROUP BY được gọi là GROUPING SETS.

GROUPING SETS định nghĩa nhiều nhóm trong cùng một truy vấn. Sau đây là cú pháp chung của GROUPING SETS:

SELECT
    column1,
    column2,
    aggregate_function (column3)
FROM
    table_name
GROUP BY
    GROUPING SETS (
        (column1, column2),
        (column1),
        (column2),
        ()
);

Truy vấn này tạo ra bốn nhóm như sau:

(column1, column2)
(column1)
(column2)
()

Bạn có thể sử dụng GROUPING SETS để viết lại truy vấn lấy dữ liệu bán hàng như sau:

SELECT
	brand,
	category,
	SUM (sales) sales
FROM
	sales.sales_summary
GROUP BY
	GROUPING SETS (
		(brand, category),
		(brand),
		(category),
		()
	)
ORDER BY
	brand,
	category;

Như bạn có thể thấy, truy vấn tạo ra kết quả giống như truy vấn sử dụng toán tử UNION ALL. Tuy nhiên, truy vấn này dễ đọc hơn nhiều và tất nhiên là hiệu quả hơn.

Function GROUPING

Function GROUPING cho biết một cột được chỉ định trong mệnh đề GROUP BY có được tập hợp hay không. Nó trả về 1 nếu được tập hợp hoặc 0 nếu không tập hợp trong tập kết quả.

Xem ví dụ truy vấn sau:

SELECT
    GROUPING(brand) grouping_brand,
    GROUPING(category) grouping_category,
    brand,
    category,
    SUM (sales) sales
FROM
    sales.sales_summary
GROUP BY
    GROUPING SETS (
        (brand, category),
        (brand),
        (category),
        ()
    )
ORDER BY
    brand,
    category;
GROUPING SET trong SQL Server

Giá trị trong cột grouping_brand cho biết sản phẩm đó có được tập hợp hay không, 1 có nghĩa là số tiền bán hàng được tổng hợp theo thương hiệu, 0 có nghĩa là số tiền bán hàng không được tổng hợp theo thương hiệu. Khái niệm tương tự được áp dụng cho cột grouping_category.

Trong hướng dẫn này, bạn đã học cách tạo nhiều nhóm trong một truy vấn bằng cách sử dụng GROUPING SETS trong SQL Server.



Bài viết liên quan:

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

Cách sử dụng mệnh đề HAVING trong SQL Server để lọc các nhóm dựa trên các điều kiện được chỉ định.

Tìm hiểu mọi thứ cần biết về trigger trong SQL Server giúp bạn sử dụng trigger hiệu quả nhất.