oracle分組匯總統計函數grouping，oraclegrouping

oracle分組匯總統計函數grouping，oraclegrouping

前兩天同事問一個oracle使用grouping完成一個統計報表的功能，這個函數帥呆了。開發分組報表直接一個SQL就搞定。

grouping（columnA）函數的意思：當前行如果是由rollup匯總產生的，那麼columnA這個欄位值為1否則為0

中繼資料：

通過grouping查詢後的資料：

sql:

select decode(grouping(f_line)+grouping(f_workarea),1,'小計',2,'總計',f_workarea) f_workarea,decode(grouping(f_line),1,count(*)||'條',f_line) f_line,sum(f_pagesnumber) sum_pagesnumbers  from t_testcount group by rollup (f_workarea,f_line);

建表資料：

CREATE TABLE t_testcount   (     "F_ID" NUMBER(10,0) ,   "F_WORKAREA" NVARCHAR2(255) ,   "F_LINE" NVARCHAR2(255) ,   "F_REMARK" NVARCHAR2(255), "F_YEAR" VARCHAR2(20 BYTE),  "F_PAGESNUMBER" NVARCHAR2(255)  );insert into T_TESTCOUNT (f_id, f_workarea, f_line, f_remark, f_year, f_pagesnumber)values (1, 'a', 'a1', null, '2014', '1');insert into T_TESTCOUNT (f_id, f_workarea, f_line, f_remark, f_year, f_pagesnumber)values (2, 'a', 'a2', null, '2013', '2');insert into T_TESTCOUNT (f_id, f_workarea, f_line, f_remark, f_year, f_pagesnumber)values (3, 'a', 'a3', null, '2014', '3');insert into T_TESTCOUNT (f_id, f_workarea, f_line, f_remark, f_year, f_pagesnumber)values (4, 'b', 'b1', null, '2014', '1');

常用的oracle 函數， 分組函數

SELECT *

　　FROM dept_costs

　　WHERE dept_total > (SELECT dept_avg

　　FROM avg_cost)

　　ORDER BY department_name;

　　GROUP BY 擴充

　　帶有ROLLUP和CUBE操作的GROUP BY子句

　　- 使用帶有ROLLUP和CUBE操作的GROUP BY子句產生多種分組結果

　　- ROLLUP產生n + 1種分組結果，其是對GROUP BY子句的擴充

　　- CUBE產生2的n次方種分組結果，其是對GROUP BY子句的擴充

　　註：其中的n指的是group_by_expression的數目。

　　ROLLUP操作符 ：ROLLUP產生n + 1種分組結果，順序是從右向左

　　SELECT [column,] group_function(column). . .

　　FROM table

　　[WHERE condition]

　　[GROUP BY [ROLLUP] group_by_expression]

　　[HAVING having_expression]

　　[ORDER BY column];

　　CUBE操作符：CUBE會產生類似於笛卡爾集的分組結果

　　SELECT [column,] group_function(column). . .

　　FROM table

　　[WHERE condition]

　　[GROUP BY [CUBE] group_by_expression]

　　[HAVING having_expression]

　　[ORDER BY column];

　　GROUPING函數：能夠實現更加直觀的分組結果顯示提示

　　SELECT [column,] group_function(column) . , [GROUPING(group_by_expression)]..

　　FROM table

　　[WHERE condition]

　　[GROUP BY [ROLLUP] [CUBE] group_by_expression]

　　[HAVING having_expression]

　　[ORDER BY column];

　　- GROUPING函數可以和CUBE或ROLLUP結合使用

　　- 使用GROUPING函數，可以找到哪些列在該行中參加了分組

　　- 使用GROUPING函數, 可以區分空值產生的原因

　　- GROUPING函數返回0 或1

　　GROUPING SETS：

　　- GROUPING SETS是對GROUP BY子句的進一步擴充

　　- 使用GROUPING SETS可以實現在同一個查詢中定義多個分組集

　　- Oracle 對GROUPING SETS子句指定的分組集進行分組後用UNION ALL操作將各分組結果結合起來

　　- Grouping set 的優點：

　　– 只進行一次分組即可

　　– 不必書寫複雜的UNION語句

　　– GROUPING SETS中包含的分組項越多效能越好

　　以下例子實現了對department_id, job_id分組，對job_id, manager_id分組，最終形成兩個分組：

　　SELECT department_id, job_id, manager_id, avg(salary......餘下全文>>
 
oracle 中grouping 函數 的使用？

GROUPING 用於區分標準空值和由 ROLLUP、CUBE 或 GROUPING SETS 返回的空值。作為 ROLLUP、CUBE 或 GROUPING SETS 操作結果返回的 NULL 是 NULL 的特殊應用。它在結果集內作為列的預留位置，表示全體。

在用SQL時，我們經常會碰到這樣的問題，要求分級求合計數，你是不是經常為怎麼在一張表裡分級統計而煩惱？這裡我們可以用GROUPING()函數來解決該問題。

　　下面用實際的例子來說明，例子是一個行政區、單位、銷售額的資料表。

　　注意：在運行這個樣本時，注意刪掉其中全形的空格，是百度給加上的，會造成查詢分析器報錯。

　　--建立表並插入資料

　　Create Table T_SendMoney(StateCode varchar(6),DepCode varchar(6),SendMoney Money)

　　Insert Into T_SendMoney

　　Select '100001','310001',1000

　　UNION ALL

　　Select '100001','310001',2000

　　UNION ALL

　　Select '100001','310002',1500

　　UNION ALL

　　Select '100002','320001',3000

　　UNION ALL

　　Select '100002','320001',1200

　　UNION ALL

　　Select '100003','330001',1800

　　UNION ALL

　　Select '100003','330002',2100

　　UNION ALL

　　Select '100004','340001',2500

　　--按GROUPING來實現分級摘要資料

　　Select

　　CASE WHEN GROUPING(StateCode)=1 THEN 'Total:' ELSE StateCode END as StateCode

　　,CASE WHEN GROUPING(DepCode)=1 THEN 'State Total:' ELSE DepCode END as DepCode

　　,Sum(SendMoney) AS SendMoney

　　From T_SendMoney

　　GROUP BY ROLLUP(StateCode,DepCode)

　　--查詢結果

　　StateCode DepCode SendMoney

　　-----------------------------------------

　　100001 310001 3000.00

　　100001 310002 1500.00

　　100001 State Total: 4500.00

　　100002 320001 4200.00

　　100002 State Total: 4200.00

　　100003 330001 1800.00

　　100003 ......餘下全文>>