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');

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......余下全文>>
 

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 ......余下全文>>