I have been busy buying a house for two days recently, so I missed two days. Today we’re going to talk about grouping sets.

When we make reports, we often need group aggregation, multi-group aggregation and aggregation. Poor performance is inevitable if aggregation is done through another T-SQL statement. If the aggregation function of the reporting tool is better than the use of additional T-SQL statements, but it is not crisp, or you need to synthesize the whole result set and then aggregate, and most importantly, in many cases, the report aggregation function may not achieve the effect we need. GROUPING SETS, CUBE, ROLLUP, GROUPING _id are used to perform many of the GROUPING functions required for financial reporting based on the GROUPING SETS, CUBE, ROLLUP, GROUPING _id.

GROUPING SETS

GROUPING SETS is equivalent to GROUPING multiple GROUP BY statements through a UNION ALL

WITH T AS(
SELECT 'A' A, 'A' B UNION ALL
SELECT 'A' A, 'B' B UNION ALL 
SELECT 'A' A, 'C' B UNION ALL
SELECT 'B' A, 'A' B UNION ALL
SELECT 'B' A, 'B' B UNION ALL
SELECT 'B' A, 'C' B UNION ALL
SELECT 'C' A, 'A' B UNION ALL
SELECT 'C' A, 'B' B UNION ALL
SELECT 'C' A, 'C' B)
SELECT  A, B, 
COUNT(A) AS CNT,
GROUPING_ID(A,B)FROM T
GROUP BY GROUPING SETS(A, B, ());
Copy the code

The results are as follows:

GROUPING SETS is usually used with GROUPING_ID or GROUPING function to output columns, such as GROUPING columns.

GROUPING_ID (a, b, c)  = GROUPING(a) + GROUPING(b) + GROUPING(c)

GROUPING is used to distinguish standard null values from those returned by ROLLUP, CUBE, or GROUPING SETS. NULL returned as the result of a ROLLUP, CUBE, or GROUPING SETS operation is a special application of NULL. It is used as a placeholder for columns in the result set to represent the whole. If the column output is NULL, it is an aggregate column. If it is not NULL, it is not an aggregate column. It was hard to understand at first.

\

In the case of only one column as a GROUP BY CUBE/ROLLUP, the two functions have the same effect.

The difference between CUBE and ROLLUP is that in cases where CUBE outputs more columns than ROLLUP, the only thing CUBE does more than ROLLUP is aggregate row outputs for subsequent columns individually.

WITH T AS ( SELECT 'A' A, 'A' B UNION ALL SELECT 'A' A, 'B' B UNION ALL SELECT 'A' A, 'C' B UNION ALL SELECT 'B' A, 'A' B UNION ALL SELECT 'B' A, 'B' B UNION ALL SELECT 'B' A, 'C' B UNION ALL SELECT 'C' A, 'A' B UNION ALL SELECT 'C' A, 'B' B UNION ALL SELECT 'C' A, 'C' B) SELECT A, B, COUNT(A) AS CNT, GROUPING_ID(A,B) FROM T GROUP BY A,B WITH CUBE ; WITH T AS ( SELECT 'A' A, 'A' B UNION ALL SELECT 'A' A, 'B' B UNION ALL SELECT 'A' A, 'C' B UNION ALL SELECT 'B' A, 'A' B UNION ALL SELECT 'B' A, 'B' B UNION ALL SELECT 'B' A, 'C' B UNION ALL SELECT 'C' A, 'A' B UNION ALL SELECT 'C' A, 'B' B UNION ALL SELECT 'C' A, 'C' B) SELECT A, B, COUNT(A) AS CNT, GROUPING_ID(A,B) FROM T GROUP BY A,B WITH ROLLUPCopy the code

The CUBE query result is as follows:

\

ROLLUP The query result is as follows:

Long click the following two-dimensional code to pay attention to us, more learning experience/teaching materials everything, thank you!

\