Skills for using the union, cube, rollup, and cumpute operators.
Skills for using the union, cube, rollup, and cumpute operators.
/*
-- 1 the UNION operator combines two or more query results into a single result set.
There are two basic rules for query result sets using UNION:
1. The columns and columns in all queries must be in the same order.
2. Data type must be compatible
A. The UNION result set column name is the same as that in the result set in the first select statement. The result set column names of other select statements are ignored.
B. By default, the UNION operator deletes duplicate rows from the result set. If the all keyword is used, the result set contains all rows without deleting duplicate rows.
C. SQL is used to set values for statements containing the UNION operator from left to right. Brackets can be used to change the order of values.
-- Example:
*/
Select * from tablea
Union all
(
Select * from tableb
Union all
Select * from tablec
)
/*
In this way, you can first merge tableb and tablec, and then merge tablea
D. If you want to save the merged result set to a new data table, the into statement must be added to the first select statement.
E. The order by and compute clauses can only be used in the last select statement, which affects the sorting and counting summary of the final merge results.
The f. group by and having clauses can be used in a single select query without affecting the final result.
*/
-- 2 CUBE summary data
/*
The result set generated by the CUBE operator is a multi-dimensional dataset. A multi-dimensional dataset is an extension of fact data. Fact data is the data that records individual events.
The extension is created on the columns that the user intends to analyze. These columns are called dimensions. A multi-dimensional dataset is a result set that contains all possible combinations of cross tables for each dimension.
The CUBE operator is specified in the group by clause of the SELECT statement. The statement selection list should contain dimension columns and aggregate function expressions.
The dimension column and keyword with cube should be specified for group. The result set contains all possible combinations of values in the dimension column, and the aggregated values in the basic rows that match the combination of these dimension values.
*/
-- The following query returns a subtotal of all possible combinations of Item and Color:
--> Title: generate a sequence number.
--> Author: wufeng4552
--> Date: 14:36:20
If not object_id ('tempdb .. # t') is null
Drop table # t
Go
Create table # t ([Item] nvarchar (5), [Color] nvarchar (4), [Quantity] int)
Insert # t
Select N 'table', N 'Blue ', 124 union all
Select N 'table', N 'red', 223 union all
Select N 'chair', N 'Blue ', 101 union all
Select N 'chair', N 'red', 210
Go
Select [Item],
[Color],
Sum ([Quantity]) [Quantity]
From # t group by [Item], [Color] with cube
/*
Item Color Quantity
---------------------
Chair Blue 101
Chair red210
Chair NULL 311
Table Blue 124
Table red223
Table NULL 347
NULL 658
NULL Blue 225
NULL red433
*/
/* The NULL value generated by the CUBE operation brings about a problem: how to distinguish the NULL value generated by the CUBE operation from the NULL value returned from the actual data?
This problem can be solved by the GROUPING function.
If the value in the column comes from the fact data, the GROUPING function returns 0. If the value in the column is NULL generated by the CUBE operation, 1 is returned.
In the CUBE operation, the generated NULL represents the whole value. You can use the 'select' statement to replace the generated NULL with the string 'all.
Because NULL in the fact data indicates that the data value is UNKNOWN, the SELECT statement can also be decoded as the returned string UNKNOWN to replace NULL from the fact data.
For example:
*/
--> Title: generate a sequence number.
--> Author: wufeng4552
--> Date: 14:36:20
If not object_id ('tempdb .. # t') is null
Drop table # t
Go
Create table # t ([Item] nvarchar (5), [Color] nvarchar (4), [Quantity] int)
Insert # t
Select N 'table', N 'Blue ', 124 union all
Select N 'table', N 'red', 223 union all
Select N 'chair', N 'Blue ', 101 union all
Select N 'chair', N 'red', 210
Go
Select [Item] = case when grouping ([Item]) = 1 then 'all' else isnull (Item, 'unknown ') end,
[Color] = case when grouping ([Color]) = 1 then 'all' else isnull ([Color], 'unknown ') end,
Sum ([Quantity]) [Quantity]
From # t group by [Item], [Color] with cube
/*
Item Color Quantity
---------------------
Chair Blue 101
Chair red210
Chair ALL 311
Table Blue 124
Table red223
Table ALL 347
ALL 658
Full Blue 225
ALL red433
(Nine data columns are affected)
*/
/*
SELECT statements containing cubes with many dimensions may generate large result sets because these statements generate rows for all combinations of values in all dimensions.
These large result sets may contain too much data and are not easy to read and understand. One solution to this problem is to place the SELECT statement in the View:
*/
Create view view_cube
As
Select [Item] = case when grouping ([Item]) = 1 then 'all' else isnull (Item, 'unknown ') end,
[Color] = case when grouping ([Color]) = 1 then 'all' else isnull ([Color], 'unknown ') end,
Sum ([Quantity]) [Quantity]
From tb group by [Item], [Color] with cube -- hour tables cannot use hour tables.
-- Then you can use this view to query only the dimension values you are interested in:
SELECT *
FROM InvCube
WHERE Item = 'chair' AND Color = 'all'
/*
Item Color QtySum
-------------------------------------------------
Chair ALL 311.00
*/
-- 3 ROLLUP summary data
/*
The ROLLUP operator is useful when collecting data using ROLLUP to generate reports that contain subtotal and aggregate.
The result set generated by the ROLLUP operator is similar to the result set generated by the CUBE operator.
The difference between CUBE and ROLLUP is that the result set generated by CUBE displays the aggregation of all the combinations of the values in the selected column.
The result set generated by ROLLUP displays the aggregation of a certain hierarchy of values in the selected column. For example, simple table # t
Contains: Item Color Quantity
*/
Select [Item] = case when grouping ([Item]) = 1 then 'all' else isnull (Item, 'unknown ') end,
[Color] = case when grouping ([Color]) = 1 then 'all' else isnull ([Color], 'unknown ') end,
Sum ([Quantity]) [Quantity]
From # t group by [Item], [Color] with rollup
/*
Item Color Quantity
---------------------
Chair Blue 101
Chair red210
Chair ALL 311
Table Blue 124
Table red223
Table ALL 347
ALL 658
(7 data columns are affected)
*/
/*
If the ROLLUP keyword in the query is changed to CUBE, the CUBE result set is the same as the preceding result, but the following two rows are returned at the end of the result set: ALL Blue 225.00
ALL red433.00
The CUBE operation may combine Item and Color values to generate rows.
For example, CUBE not only reports all possible combinations of Color values (Red, Blue, and Red + Blue) combined with Item value Chair ),
It also reports all possible combinations of Item values (Chair, Table, and Chair + Table) combined with the Color value Red ).
For each value in the right column of the group by clause, the ROLLUP operation does not report all possible combinations of values in the left column (or left column. For example,
ROLLUP does not report all possible combinations of Item values for each Color value.
The result set of The ROLLUP operation is similar to the result set returned by compute by. However, ROLLUP has the following advantages: ROLLUP returns a single result set; compute by returns multiple result sets, multiple result sets increase the complexity of application code.
ROLLUP can be used in server cursors; compute by cannot.
Sometimes, the execution plan generated BY the query optimizer for ROLLUP is more efficient than that generated by compute.
*/
