oracle 高级分组group by cube拓展
oracle的cube拓展功能会将cube（）里指定的每一列按照顺序替换成null值，并返回指定列的所有组合。
oracle的cube分组拓展主要用于替换需要通过union all和goup by 组合来实现业务功能的场景。通过该函数可以节省代码量，且使代码更加简洁。
实验过程如下：
首先看一下A表的内容：
HR@ORA11GR2 > select * from a;
A A2 A3
---------- ---------- ----------
1 4 5
2 4 6
3 4 7
4 5 9
5 5 10
6 5 11
7 5 12
8 5 13
8 rows selected.
若要通过union all 来实现数据组合功能：
HR@ORA11GR2 > with temp as (
2 select a, a2 from a
3 union all
4 select a , null a2 from a
5 union all
6 select null a , a2 from a
7 union all
8 select null a , null a2 from a
9 )
10 select * from temp group by a, a2 order by a2 desc;
A A2
---------- ----------
1
2
3
4
5
6
7
8
4 5
5 5
A A2
---------- ----------
6 5
7 5
8 5
5
1 4
2 4
3 4
4
19 rows selected.
使用cube分组拓展结果：
HR@ORA11GR2 > select a, a2 from a group by cube(a, a2) order by a2 desc;
A A2
---------- ----------
1
2
3
4
5
6
7
8
4 5
5 5
A A2
---------- ----------
6 5
7 5
8 5
5
1 4
2 4
3 4
4
19 rows selected.
实验如下：
oracle的cube分组拓展主要用于替换需要通过union all和goup by 组合来实现业务功能的场景。通过该函数可以节省代码量，且使代码更加简洁。
实验过程如下：
首先看一下A表的内容：
HR@ORA11GR2 > select * from a;
A A2 A3
---------- ---------- ----------
1 4 5
2 4 6
3 4 7
4 5 9
5 5 10
6 5 11
7 5 12
8 5 13
8 rows selected.
若要通过union all 来实现数据组合功能：
HR@ORA11GR2 > with temp as (
2 select a, a2 from a
3 union all
4 select a , null a2 from a
5 union all
6 select null a , a2 from a
7 union all
8 select null a , null a2 from a
9 )
10 select * from temp group by a, a2 order by a2 desc;
A A2
---------- ----------
1
2
3
4
5
6
7
8
4 5
5 5
A A2
---------- ----------
6 5
7 5
8 5
5
1 4
2 4
3 4
4
19 rows selected.
使用cube分组拓展结果：
HR@ORA11GR2 > select a, a2 from a group by cube(a, a2) order by a2 desc;
A A2
---------- ----------
1
2
3
4
5
6
7
8
4 5
5 5
A A2
---------- ----------
6 5
7 5
8 5
5
1 4
2 4
3 4
4
19 rows selected.
从上面的输出中，我们会发现cube会将许多的null值，如果cube（）里指定的列本身就具有null值，又需要如何区分呢？
解决方法为通过grouping()函数来排除null值，例如grouping(a)来检测A表中a列是否有一行null值是由cube产生的，如果有，则返回1，其他所有情况，则返回0.
接着再结合decode（）函数或case表达式，来将cube（）产生的null值转化成通俗易懂的字符串。
实验如下：
HR@ORA11GR2 > select decode(grouping(a),1,'cube_value',a) a , decode(grouping(a2), 1, 'cube_value',a2) a2 from a group by cube(a, a2) order by a2 desc;
A A2
---------------------------------------- ----------------------------------------
cube_value cube_value
4 cube_value
8 cube_value
7 cube_value
6 cube_value
5 cube_value
3 cube_value
2 cube_value
1 cube_value
cube_value 5
6 5
A A2
---------------------------------------- ----------------------------------------
8 5
7 5
4 5
5 5
1 4
cube_value 4
3 4
2 4
19 rows selected.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30167136/viewspace-1720037/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
Oracle 间隔分区
请登录后发表评论
登录
全部评论
