Oracle 高级分组group by cube拓展

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.   

从上面的输出中，我们会发现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.