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.

oracle 高階分組group by cube擴充

相關文章