Rollup and cube options in groups in Oracle

When you are working with multi-column grouping statistics, you can only generate statistical results based on all grouped columns if you specify grouping columns directly using the GROUP BY clause. If you use the ROLLUP statement or the CUBE statement in a GROUP BY clause, you can also generate statistical results based on a different subset of the specified columns, in addition to grouping statistics based on all the specified columns.
The rollup option, in addition to generating grouping statistics based on all specified columns, also counts each subset of the specified grouping column from the left. For example, a GROUP by ROLLUP (A,B,C)-formed statistic includes the following content.
? GROUP by (): statistics are not based on the entire query result for any column.
? GROUP by a: Group statistics based on column A.

? GROUP by a B: Group statistics based on columns A and B.
? GROUP BY A,b,c: grouping statistics based on columns A, B, and C three.
With the cube option, all subsets of the specified grouping column are counted, in addition to generating grouping statistics based on all specified columns. For example, the statistics formed by the GROUP by CUBE (A,B,C) include the following.
? GROUP by (): statistics are not based on the entire query result for any column.
? GROUP by a: Group statistics based on column A.
? GROUP BY B: Group statistics based on column B.
? GROUP BY C: Group statistics based on column C.
? GROUP by a B: Group statistics based on columns A and B.
? GROUP BY A,c: grouping statistics based on columns A and C two.
? GROUP BY B,C: Group statistics based on columns B and C two.

? GROUP BY A,b,c: grouping statistics based on columns A, B, and C three.
Example 29: Query 10, 20, 30 the average salary of each position in each department, the average salary of each department and the average salary of all employees. The form of the query results is shown in table 30-3.

Example 30: Query 10, 20, 30 the average salary of each position in each department, the average salary of each department, the average salary of each position and the average salary of all employees. The form of the query results is shown in table 30-4.

To understand what column statistics each statistic is based on, you can use the grouping function, which returns a value of 1 o'clock, indicating that the column is not participating in statistics, and that the function return value is 0 o'clock, which indicates that the column participates in the statistics.

Example 31: Query 10, 20, 30 the average salary for each position in each department, the average salary per department, the average salary for each position and the average salary of all employees, showing which columns each statistic results are based on.

7. Merging group queries

In Oracle 11g, several separate grouped queries can be combined into one group query. Merging group queries requires the use of the grouping sets option in the GROUP BY clause.
Example 32: Query the average salary of each department and the average salary of each position.

The function of the GROUPING sets statement is to use a single statement to get the result set of multiple grouping statistics. Note the difference between a nested column and a non-nested column. A nested column is the same as a single group by statement, and a non-nested column is the equivalent of combining multiple separate group by statements with a UNION all method.

? GROUP BY GROUPING Sets ((A,b,c)): equivalent to GROUP by A,b,c.
? GROUP BY GROUPING Sets (A,b,c): equivalent to GROUP by A UNION ALL GROUPBY B UNION ALL GROUP by C.
? GROUP BY GROUPING sets (A, (b,c)): equivalent to GROUP by A UNION all GROUPBY b,c.
30.2.4 Cumulative Statistical query
In the execution of statistical queries, aggregation functions can be combined with the over function, the overall cumulative statistical query or group cumulative statistical query.
1. Overall cumulative statistics
Each statistic for the overall cumulative statistic is for all previous records, using the order BY statement in the over function to specify the ordering of the statistics, without accumulating statistics if the order BY statement is not specified.

Example 33: The employee's wages, the number of the overall cumulative statistics.

As can be seen from the statistical results, the first statistical results of the cumulative statistics are for the first record; the second statistic is for the top two records, and the third statistic is for the first three records, and so on.

2. Group Cumulative statistics
If you need to do this in groups when you are accumulating statistics, you need to use the partition clause to specify the grouping column for the cumulative statistic.
Example 34: The payroll and the number of employees in each department are grouped in aggregate statistics.

Rollup and cube options in groups in Oracle