oracle中分組中的ROLLUP和CUBE選項

標籤：

     在進行多列分組統計時，如果直接使用GROUP BY子句指定分組列，則只能產生基於所有分組列的統計結果。如果在GROUP BY子句中使用ROLLUP語句或CUBE語句，除了產生基於所有指定列的分組統計外，還可以產生基於指定列不同子集的統計結果。
使用ROLLUP選項，除了產生基於所有指定列的分組統計外，還對指定的分組列從左開始的每個子集進行統計。例如，GROUP BY ROLLUP(A,B,C)形成的統計包括以下內容。
?GROUP BY()：不基於任何列的整個查詢結果的統計。
?GROUP BY A：基於A列的分組統計。

?GROUP BY A,B：基於A、B兩列的分組統計。
?GROUP BY A,B,C：基於A、B、C三列的分組統計。
使用CUBE選項，除了產生基於所有指定列的分組統計外，還對指定分組列的所有子集進行統計。例如，GROUP BY CUBE(A,B,C)形成的統計包括以下內容。
?GROUP BY()：不基於任何列的整個查詢結果的統計。
?GROUP BY A：基於A列的分組統計。
?GROUP BY B：基於B列的分組統計。
?GROUP BY C：基於C列的分組統計。
?GROUP BY A,B：基於A、B兩列的分組統計。
?GROUP BY A,C：基於A、C兩列的分組統計。
?GROUP BY B,C：基於B、C兩列的分組統計。

?GROUP BY A,B,C：基於A、B、C三列的分組統計。
例29：查詢10、20、30的各個部門中各個職位的平均工資、每個部門的平均工資和所有員工的平均工資。查詢結果的形式如表30-3所示。

例30：查詢10、20、30的各個部門中各個職位的平均工資、每個部門的平均工資、每個職位的平均工資和所有員工的平均工資。查詢結果的形式如表30-4所示。

為了瞭解每個統計結果是基於哪些列統計而來的，可以使用GROUPING函數，函數傳回值為1時，表示該列沒有參與統計；函數傳回值為0時，表示該列參與了統計。

例31：查詢10、20、30的各個部門中各個職位的平均工資、每個部門的平均工資、每個職位的平均工資和所有員工的平均工資，顯示每個統計結果是基於哪些列進行的。

7.合并分組查詢

在Oracle 11g中，可以將幾個單獨的分組查詢合并成一個分組查詢。合并分組查詢需要在GROUP BY子句中使用GROUPING SETS選項。
例32：查詢各個部門的平均工資和各個職位的平均工資。

GROUPING SETS語句的作用就是使用一個語句得到多個分組統計的結果集。要注意嵌套列與非嵌套列的區別。嵌套列與單個GROUP BY語句作用相同；非嵌套列相當於將多個單獨的GROUP BY語句查詢結果，採用UNION ALL方式的合并起來。

?GROUP BY GROUPING SETS((A,B,C))：等價於GROUP BY A,B,C。
?GROUP BY GROUPING SETS(A,B,C)：等價於GROUP BY A UNION ALL GROUPBY B UNION ALL GROUP BY C。
?GROUP BY GROUPING SETS(A,(B,C))：等價於GROUP BY A UNION ALL GROUPBY B,C。
30.2.4　累計統計查詢
在執行統計查詢時，可以將聚集合函式與OVER函數相結合，進行總體累計統計查詢或分組累計統計查詢。
1.總體累計統計
總體累計統計的每一個統計結果都是針對之前的所有記錄進行的，在OVER函數中使用ORDER BY語句指定統計的順序，如果不指定ORDER BY語句，則不進行累計統計。

例33：對員工的工資、人數進行總體累計統計。

由統計結果可以看出，累計統計的第一個統計結果是針對第一條記錄進行的；第二個統計結果是針對前兩條記錄進行的；第三個統計結果是針對前三條記錄進行的，以此類推。

2.分組累計統計
如果在進行累計統計時，需要按組進行，則需要使用PARTITION子句指定累計統計的分組列。
例34：對各個部門中的員工的工資、人數進行分組累計統計。

 

oracle中分組中的ROLLUP和CUBE選項