[20171219]Cube, Grouping and Rollup.txt
--//每到年底.总有一些报表统计之类的事情,这些事情非常繁琐,报表往往是一次性,写sql语句非常耗费时间.
--//而我发现许多开发根本不熟悉oracle分析函数,甚至不知道,我经常建议开发花一点点事件学习这方面知识,
--//可惜.....几乎很少人去了解掌握这些知识.今天讲讲分组GROUPING SETS,实际上只要会不到1个小时就基本
--//能掌握这些知识.
1.环境:
SCOTT@book> @ ver1
PORT_STRING VERSION BANNER
------------------- -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> create table t1 as select trunc(rownum/10)+1 a,trunc(rownum/12)+1 b ,trunc(rownum/20)+1 c,rownum d from dual connect by level<=50;
Table created.
2.GROUPING SETS:
--//grouping sets子句允许你指定多个group by 选项。增强了group by 的功能。可以通过一条select 语句实现复杂繁琐的多条select
--//语句的查询。并且更加的高效，解析存储一条SQL于语句。
SCOTT@book> select a,b,c,sum(d),count(*) from t1 group by a,b,c order by 1,2,3;
A B C SUM(D) COUNT(*)
---------- ---------- ---------- ---------- ----------
1 1 1 45 9
2 1 1 21 2
2 2 1 124 8
3 2 2 86 4
3 3 2 159 6
4 3 2 195 6
4 4 2 150 4
5 4 3 348 8
5 5 3 97 2
6 5 3 50 1
10 rows selected.
--//如果使用GROUPING SETS,相当于:
select a,b,c,sum(d),count(*) from t1 group by grouping sets((a,b,c)) order by 1,2,3;
--//当时使用GROUPING SETS能实现更加复杂的组合:
SCOTT@book> select a,b,c,sum(d),count(*) from t1 group by grouping sets((a,b,c),(a),(b),(c),()) ;
A B C SUM(D) COUNT(*)
---------- ---------- ---------- ---------- ----------
1 1 1 45 9
2 1 1 21 2
2 2 1 124 8
3 2 2 86 4
3 3 2 159 6
4 3 2 195 6
4 4 2 150 4
5 4 3 348 8
5 5 3 97 2
6 5 3 50 1
1 45 9
2 145 10
3 245 10
4 345 10
5 445 10
6 50 1
1 66 11
2 210 12
4 498 12
5 147 3
3 354 12
1 190 19
2 590 20
3 495 11
1275 50
25 rows selected.
--//相当于:
select a,b,c,sum(d),count(*) from t1 group by a,b,c
union all
select a,null,null,sum(d),count(*) from t1 group by a
union all
select null,b,null,sum(d),count(*) from t1 group by b
union all
select null,null,c,sum(d),count(*) from t1 group by c
union all
select null,null,null,sum(d),count(*) from t1;
--//你还可以建立查询(a,b),(b,c),(a,c)的集合.
--//唯独注意一点,以上的例子必须包含(a,b,c)集合,不然报错:
SCOTT@book> select a, b, c, sum(d ) from t1 group by grouping sets ( (a,b), b);
select a, b, c, sum(d ) from t1 group by grouping sets ( (a,b), b)
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
3.GROUPING Function and GROUPING_ID Function
--//由此引出GROUPING Function and GROUPING_ID Function,例子:
SELECT deptno
,job
,SUM (sal)
,GROUPING (deptno) gdno
,GROUPING (job) gjno
,GROUPING_ID (deptno, job) gid_dj
,GROUPING_ID (job, deptno) gid_jd
FROM emp
GROUP BY GROUPING SETS
(
(deptno, job)
,deptno
,job
,( )
);
DEPTNO JOB SUM(SAL) GDNO GJNO GID_DJ GID_JD
---------- --------- ---------- ---------- ---------- ---------- ----------
10 CLERK 1300 0 0 0 0
20 CLERK 1900 0 0 0 0
30 CLERK 950 0 0 0 0
20 ANALYST 6000 0 0 0 0
10 MANAGER 2450 0 0 0 0
20 MANAGER 2975 0 0 0 0
30 MANAGER 2850 0 0 0 0
30 SALESMAN 5600 0 0 0 0
10 PRESIDENT 5000 0 0 0 0
CLERK 4150 1 0 2 1
ANALYST 6000 1 0 2 1
MANAGER 8275 1 0 2 1
SALESMAN 5600 1 0 2 1
PRESIDENT 5000 1 0 2 1
10 8750 0 1 1 2
20 10875 0 1 1 2
30 9400 0 1 1 2
29025 1 1 3 3
18 rows selected.
GROUPING function has the general syntax of GROUPING ( ). It is used only in SELECT clause. It takes only a single
column expression as argument.
GROUPING_ID takes a set of columns. It applies the GROUPING function on each column in its argument and composes a bit
vector with the "0" and "1" values. It returns the decimal equivalent of the bit vector. The columns GID_DJ and GID_JD show
the use of GROUPING_ID function and also show how interchanging the order of the columns inside the GROUPING_ID function
might impact the result.
--//我不翻译,自己体会.
4.CUBE:
GROUP BY CUBE( a, b, c) is equivalent to
GROUP BY GROUPING SETS ( (a, b, c), (a, b), (b, c), (a, c), (a), (b), (c), ( )).
--//相当于GROUPING SETS的特例.实际上就是a,b,c的各种组合.
SCOTT@book> select a,b,c,sum(d),count(*) from t1 group by cube(a,b,c);
A B C SUM(D) COUNT(*)
---------- ---------- ---------- ---------- ----------
1275 50
1 190 19
2 590 20
3 495 11
1 66 11
1 1 66 11
2 210 12
2 1 124 8
2 2 86 4
3 354 12
3 2 354 12
4 498 12
4 2 150 4
4 3 348 8
5 147 3
5 3 147 3
1 45 9
1 1 45 9
1 1 45 9
1 1 1 45 9
2 145 10
2 1 145 10
2 1 21 2
2 1 1 21 2
2 2 124 8
2 2 1 124 8
3 245 10
3 2 245 10
3 2 86 4
3 2 2 86 4
3 3 159 6
3 3 2 159 6
4 345 10
4 2 345 10
4 3 195 6
4 3 2 195 6
4 4 150 4
4 4 2 150 4
5 445 10
5 3 445 10
5 4 348 8
5 4 3 348 8
5 5 97 2
5 5 3 97 2
6 50 1
6 3 50 1
6 5 50 1
6 5 3 50 1
48 rows selected.
5.ROLLUP
ROLLUP (a, b, c) is equivalent to GROUPING SETS ( (a, b, c), (a, b), (a), ( )).
The general syntax of ROLLUP is ROLLUP( )
--//相当于每次从集合尾部拿去1个元素来组成各种集合.可能专业术语不对,意思就是这个意思.
SCOTT@book> select a,b,c,sum(d),count(*) from t1 group by rollup(a,b,c);
A B C SUM(D) COUNT(*)
---------- ---------- ---------- ---------- ----------
1 1 1 45 9
1 1 45 9
1 45 9
2 1 1 21 2
2 1 21 2
2 2 1 124 8
2 2 124 8
2 145 10
3 2 2 86 4
3 2 86 4
3 3 2 159 6
3 3 159 6
3 245 10
4 3 2 195 6
4 3 195 6
4 4 2 150 4
4 4 150 4
4 345 10
5 4 3 348 8
5 4 348 8
5 5 3 97 2
5 5 97 2
5 445 10
6 5 3 50 1
6 5 50 1
6 50 1
1275 50
27 rows selected.
--//可以发现cube,rollup相当于GROUPING SETS的特例.
6.还可以组合生成更加复杂的查询:
Composite Columns
A composite column is a collection of columns that can be used in CUBE or ROLLUP. They are treated as unit before computing
the aggregate.Composite columns usage in CUBE and ROLLUP and the equivalent GROUPING SETS -
. CUBE( (a, b), c) is equivalent to GROUPING SETS ( (a, b, c), (a, b) , c, ( ))
. ROLLUP ( a, (b, c) ) is equivalent to GROUPING SETS ( (a, b, c), ( a ), ( ) )
Partial GROUPING SETS, CUBE or ROLLUP
If any column appears in GROUP BY but outside the aggregation clauses discussed above. It can be thought of as being first
column of the resulting GROUPING SET equivalent. The following examples make this clear.
GROUP BY a, CUBE( b, c) is equivalent to
GROUP BY GROUPING SETS ( (a, b, c), (a, b), (a, c), (a) )
GROUP BY a, ROLLUP( b, c) is equivalent to
GROUP BY GROUPING SETS ( (a, b, c), (a, b), (a) )
--//我个人的看法仅仅掌握了解GROUPING SETS,cube就足够了,我感觉rollup会少用一些.
--//认真看下来,不要1个小时就基本掌握GROUPING SETS的用法.包括cube,ROLLUP.