The basic usage of rollup of Oracle grouping function

Time:2019-11-28

Rollup function

This blog gives a brief introduction to the usage of rollup in Oracle group function. Rollup function is commonly used in group statistics and is also a kind of Oracle analysis function

Environmental preparation


create table dept as select * from scott.dept;
create table emp as select * from scott.emp;

Business scenario: calculate the total salary of each department and the total salary of all departments

You can use union to do this. First, you can count the sum of wages by department, and then you can count the sum of wages of all departments


select a.dname, sum(b.sal)
 from scott.dept a, scott.emp b
 where a.deptno = b.deptno
 group by a.dname
union all
select null, sum(b.sal)
 from scott.dept a, scott.emp b
 where a.deptno = b.deptno;

The above is done with union, and then with rollup. The syntax is simpler and the performance is better


select a.dname, sum(b.sal)
 from scott.dept a, scott.emp b
 where a.deptno = b.deptno
 group by rollup(a.dname);

Business scenario: Based on the above statistics and demand, now we need to look at the sum of salaries corresponding to each department position

select a.dname, b.job, sum(b.sal)
 from scott.dept a, scott.emp b
 where a.deptno = b.deptno
 group by a.dname, b.job
Union all // sum of salaries of all departments
select a.dname, null, sum(b.sal)
 from scott.dept a, scott.emp b
 where a.deptno = b.deptno
 group by a.dname
Union all // sum of salaries of all departments
select null, null, sum(b.sal)
 from scott.dept a, scott.emp b
 where a.deptno = b.deptno;

Implemented with rollup, Simpler Syntax


select a.dname, b.job, sum(b.sal)
 from scott.dept a, scott.emp b
 where a.deptno = b.deptno
 group by rollup(a.dname, b.job);

If you add time statistics, you can use the following SQL:


select to_char(b.hiredate, 'yyyy') hiredate, a.dname, b.job, sum(b.sal)
 from scott.dept a, scott.emp b
 where a.deptno = b.deptno
 group by rollup(to_char(b.hiredate, 'yyyy'), a.dname, b.job);

Cube function


select a.dname, b.job, sum(b.sal)
 from scott.dept a, scott.emp b
 where a.deptno = b.deptno
 group by cube(a.dname, b.job);

cube

Functions are more detailed statistics of dimensions, and the syntax is similar to that of rollup

Suppose there are n dimensions, then there will be n aggregations for roll up and 2n aggregations for cube.

Rollup statistics column

The roll up (a, b) statistical column includes: (a, b), (a), ()

The roll up (a, B, c) statistical column includes: (a, B, c), (a, b), (a), ()

….

Cube statistics column

The statistical columns of cube (a, b) include: (a, b), (a), (b), ()

The statistical columns of cube (a, B, c) include: (a, B, c), (a, b), (a, c), (B, c), (a), (b), (c), ()

….

summary

The above is the whole content of this article. I hope that the content of this article has some reference learning value for your study or work. Thank you for your support for developepaer.

Recommended Today

Rust and python: why rust can replace Python

In this guide, we compare the rust and python programming languages. We will discuss the applicable use cases in each case, review the advantages and disadvantages of using rust and python, and explain why rust might replace python. I will introduce the following: What is rust? What is Python? When to use rust When to […]