oracle分组高级,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.

搜索

复制