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;
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;
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;
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;
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;
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;
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:
- Truy vấn khá dài.
- 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;
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.