Oracle rollup and cube usage

During the project, we often encounter issues that are summarized by departments and personnel. It is quite convenient to handle such problems in Oracle! The following describes the instances encountered in the project:

 

 

The query statement is as follows:

Select f_sys_getsectnamebysectid (A. sectionid) as sectname,
-- A. sectionid,
F_sys_employinfo_getnamebyno (A. buyerid) as name,
Sum (C. ratio) as llratio,
Sum (B. ratio) as lratio,
Sum (A. ratio) as ratio,
Sum (D. ratio) as nratio
From (select sectionid, buyerid, Ratio
From t_pprm_asppurratio
Where year = '000000'),
(Select buyerid, ratio from t_pprm_asppurratio where year = '20170101') B,
(Select buyerid, ratio from t_pprm_asppurratio where year = '000000') c,
(Select buyerid, ratio from t_pprm_asppurratio where year = '20170101') d
Where a. buyerid = B. buyerid (+)
And B. buyerid = C. buyerid (+)
And C. buyerid = D. buyerid (+)
Group by rollup (A. sectionid, A. buyerid)

The following post is a more detailed introduction, which requires careful consideration.

Oracle rollup and cube usage
In addition to the most basic syntax, Oracle group by statements also support rollup and cube statements. If rollup (A, B, C) is used, group by is performed on (A, B, C) and group by is performed on (a, B, then perform group by for (a) And then perform group by for the entire table. For group by cube (A, B, C), group by (A, B, C) is first performed, followed by (a, B ), (A, C), (A), (B, C), (B), (c), and finally perform the group by operation on the entire table. Grouping_id () can beautify the effect:

In addition to the most basic syntax, Oracle group by statements also support rollup and cube statements.

 

 

 

In addition to the content of this article, you can also refer:
Analytic Function Reference Manual: http://xsb.itpub.net/post/419/33028
Analysis Function example: http://xsb.itpub.net/post/419/44634

SQL> Create Table T as select * From dba_indexes;

 

The table has been created.

 

SQL> select index_type, status, count (*) from T group by index_type, status;

 

Index_type status count (*)
---------------------------------------------
Lob valid 51
Normal N/A 25
Normal valid 479
Cluster valid 11

 

The following describes the execution results of rollup and cube statements.

 

SQL> select index_type, status, count (*) from T group by rollup (index_type, status );

 

Index_type status count (*)
---------------------------------------------
Lob valid 51
Lob 51
Normal N/A 25
Normal valid 479
Normal 504
Cluster valid 11
Cluster 11
566

 

Eight rows have been selected.

 

SQL> select index_type, status, count (*) from T group by cube (index_type, status );

 

Index_type status count (*)
---------------------------------------------
566
N/A 25
Valid 541
Lob 51
Lob valid 51
Normal 504
Normal N/A 25
Normal valid 479
Cluster 11
Cluster valid 11

 

10 rows have been selected.

 

The query results are not very clear at a glance. The following describes the query results through the grouping function provided by Oracle.

 

SQL> select grouping (index_type) g_ind, grouping (Status) g_st, index_type, status, count (*)
2 from T group by rollup (index_type, status) order by 1, 2;

 

G_ind g_st index_type status count (*)
-----------------------------------------------------------------
0 0 lob valid 51
0 0 normal N/A 25
0 0 normal valid 479
0 0 cluster valid 11
0 1 lob 51
1 0 normal 504
0 1 cluster 11
1 566

 

Eight rows have been selected.

 

The query results are much more intuitive. Compared with group by statements without rollup, rollup adds group by statistics for index_type and group by statistics for all records.

 

That is to say, if rollup (A, B, C) is used, group by is first performed on (A, B, C), and then group by is performed on (a, B, then perform group by for (a) And then perform group by for the entire table.

 

Let's look at the Cube statement.

 

SQL> select grouping (index_type) g_ind, grouping (Status) g_st, index_type, status, count (*)
2 from T group by cube (index_type, status) order by 1, 2;

 

G_ind g_st index_type status count (*)
-----------------------------------------------------------------
0 0 lob valid 51
0 0 normal N/A 25
0 0 normal valid 479
0 0 cluster valid 11
0 1 lob 51
1 0 normal 504
0 1 cluster 11
1 0 N/A 25
1 0 valid 541
1 566

 

10 rows have been selected.

 

Compared with rollup, the cube adds group by statistics for the Status column.

 

For group by cube (A, B, C), group by (A, B, C) is first performed, followed by (a, B ), (A, C), (A), (B, C), (B), (c), and finally perform the group by operation on the entire table.

 

In addition to the grouping function, you can also use grouping_id to identify the group by result.

 

SQL> select grouping_id (index_type, status) g_ind, index_type, status, count (*)
2 from T group by rollup (index_type, status) order by 1;

 

G_ind index_type status count (*)
-------------------------------------------------------
0 lob valid 51
0 normal N/A 25
0 normally valid 479
0 cluster valid 11
1 lob 51
1 normal 504
1 cluster 11
3 566

 

Eight rows have been selected.

 

SQL> select grouping_id (index_type, status) g_ind, index_type, status, count (*)
2 from T group by cube (index_type, status) order by 1;

 

G_ind index_type status count (*)
-------------------------------------------------------
0 lob valid 51
0 normal N/A 25
0 normally valid 479
0 cluster valid 11
1 lob 51
1 normal 504
1 cluster 11
2 N/A 25
2 valid 541
3 566

 

10 rows have been selected.

Grouping_id () can beautify the effect:

Select decode (grouping_id (C1), 1, 'Total', C1) d1,
Decode (grouping_id (C1, C2), 1, 'subtotal ', C2) D2,
Decode (grouping_id (C1, C2, C1 + C2), 1, 'subtotal ', C1 + C2) D3,
Count (*),
Grouping_id (C1, C2, C1 + C2, C1 + 1, C2 + 1 ),
Grouping_id (C1)
From T2
Group by rollup (C1, C2, C1 + C2, C1 + 1, C2 + 1 );

========================================================== ==============================

1. Dedicated rollup functions for total reports

Sales Report

January 20 RMB in Guangzhou

February 25 RMB in Guangzhou

Guangzhou 4500 Yuan

Shenzhen, September 00, January 10

Shenzhen, September 00, February 20

Shenzhen RMB 3000

$7500 for all regions

 

 

Previous query SQL:

Select area, month, sum (money) from saleorder group by area, month

Then, the sum of Guangzhou and Shenzhen and the sum of all regions must be accumulated in the program.

 

1. You can actually use the following SQL:

Select area, month, sum (total_sale) from saleorder group by rollup (area, month)

The same record as the report can be generated.

 

2. If you do not want to accumulate year, you can write it

Select year, month, area, sum (total_sale) from saleorder group by year, rollup (month, Area)

In addition, Oracle 9i also supports the following syntax:

Select year, month, area, sum (total_sale) from saleorder group by rollup (year, month), area)

 

3. If cube (area, month) is used instead of rollup (area, month), the total of each month is obtained in addition to the total of each region, which is displayed at the end of the report.

 

4. Grouping makes the total column better read

When rollup displays Guangzhou total, the month column is null, but it is better to display it as "all months"

Grouping is used to determine whether the current column is a total column, 1 is yes, and then convert it to "all months" with decode"

Select decode (grouping (area), 1, 'all region', Area) area, decode (grouping (month), 1, 'all month', month), sum (money) from saleorder group by rollup (area, month );

 

2. Start with... connect

Oracle provides a classic method for personnel organization and product category.

Select level, name, emp_id, manager_emp_id from employee start with manager_emp_id is null connect by prior emp_id = manager_emp_id;

The preceding statement demonstrates all applications. Start with indicates where to traverse the tree. If it starts from the root, its manager should be null. If it starts from a staff member, emp_id = '11'

Connect by indicates the parent-child relationship. Pay attention to the prior location.

There is also a level column that displays the node hierarchy

 

3. More report/analysis and decision-making functions

3.1 basic structure of the Analysis Function

Analysis Function () over (partion clause, order by clause, window clause)

It is difficult to clarify the concept, but it is better to use examples.

 

3.2 row_number and rank, dense_rank

Used to select reports such as top 3 sales

When two salesmen may have the same performance, rank and dense_rank must be used.

For example

Amount rownum rank dense_rank

Zhang San 4000 yuan 1 1 1

Li Si 3000 yuan 2 2 2

5 RMB 2000 RMB 3 3 3

Sun sat 2000 yuan 4 3 3

Ding Qi 1000 yuan 5 5 4

At this time, we should select both Qian Wu and Sun 6th, tied for the third, so the ranking function is safer than rownumber. As for desnse or ranking, we can see the specific situation.

Select salesperson_id, sum (tot_sales) sp_sales, rank () over (order by sum (tot_sales) DESC) sales_rank from orders group by salesperson_id

3.3 ntile splits the record equally into Class A, Class B, and Class 4.

For example, if I want to get the first 25% of the records, or treat 25% of the records as equal to the same level, and treat the other 25% as equal to the other level.

Select cust_nbr, sum (tot_sales) cust_sales, ntile (4) over (order by sum (tot_sales) DESC) sales_quartile from orders group by cust_nbr order by 3, 2 DESC;

Ntitle (4) divides the records in the order of sum (tot_sales) into four parts.

 

3.4 secondary analysis column and WINDOWS function

In addition to the basic fact data, the report always hopes to have more total annual sales volume next to it. For the cumulative sales volume so far, see the average sales volume of the last three months for reference.

The average sales volume of the last three months and the total sales volume so far are called Windows function. See the following example.

Select month, sum (tot_sales) monthly_sales, sum (tot_sales) over (order by month rows between unbounded preceding and current row) max_preceeding from orders group by month order by month;

Select month, sum (tot_sales) monthly_sales, AVG (sum (tot_sales) over (order by month rows between 1 preceding and 1 following) rolling_avg from orders group by month order by month;

The key to Windows function is the Windows clause's values.

1. A record before preceding

1. One record after following

All records before unbounded preceding

Current row current record

 

4. subquery Summary

Subquery is used every day. In theory, subquery is divided into three types.

1. noncorrelated subquery is the most common style.

2. correlated subqueries pulled the column of the parent Query into the subquery. The first cyt taught me how to understand it for a long time.

3. The Inline view is also used as the most common style.

 

There are three more noncorrelated subqueries.

1. Return the where price in a row <(select max (price) from goods)

2. Return where price> = All (Select Price from goods where type = 2) for multiple rows)

Or where not price <Any (Select Price from goods where type = 2)

The most commonly used in is actually = any ()

3. If multiple rows and columns are returned at a time, the query time is saved.

Update monthly_orders set (tot_orders, max_order_amt) = (select count (*), max (sale_price) from cust_order) delete from line_item where (order_nbr, part_nbr) in (select order_nbr, part_nbr from cust_order C)

==========================================================
/* -------- Understand grouping sets
Select a, B, c, sum (d) from t
Group by grouping sets (A, B, C)

Equivalent

Select * from (
Select a, null, null, sum (d) from T group by
Union all
Select null, B, null, sum (d) from T group by B
Union all
Select null, null, C, sum (d) from T group by C
)
*/