CALL VPD_PKG.SET_CONTEXT_COMPID('-1');
SELECT A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'),COUNT(*)
FROM XTBILL2011 A
GROUP BY ROLLUP(A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'));
--2)部分rollup分组
--对标准分组后，对a.typeid进行小计
SELECT A.DWDH,A.YEAR,A.TYPEID,COUNT(*)
FROM XTYWBILL A
GROUP BY A.DWDH,A.YEAR,ROLLUP(A.TYPEID);
2、CUBE
--rollup只能对“从右到左递减”，如需要全方位的维度进行统计，需要用到cube函数
--1)
SELECT A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'),COUNT(*)
FROM XTBILL2011 A
GROUP BY CUBE(A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'));
--2) 部分cube:可以去掉合计与某些不需要的小计。
SELECT A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'),COUNT(*)
FROM XTBILL2011 A
GROUP BY A.VPD_COMPID,CUBE(TO_CHAR(A.TTIME,'MM'));
3、GROUPING SETS
--说明：仅关注单列分组，某些维度的小计
--group by grouping sets(a,b,c)相当于group by a,group by b,group by c
--这三组的union all结果
--1)
SELECT A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'),COUNT(*)
FROM XTBILL2011 A
GROUP BY GROUPING SETS(A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'));
--2)部分grouping sets分组
--在group by的基础上进行小计，仅关注小计的情况
SELECT A.DWDH,A.YEAR,A.TYPEID,COUNT(*)
FROM XTYWBILL A
GROUP BY A.DWDH,GROUPING SETS(A.YEAR,A.TYPEID);
SELECT A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'),COUNT(*)
FROM XTBILL2011 A
GROUP BY A.VPD_COMPID,GROUPING SETS(TO_CHAR(A.TTIME,'MM'));
4、CUBE,ROLLUP作为GROUPING SETS的参数
--grouping sets操作只对单列进行分组，而不提供合计的功能，如果需要grouping sets提供合计的功能，
--那么可以使用rollup或cube作为grouping sets的参数，比如下面的语句提供合计功能：
SELECT A.VPD_COMPID,TO_CHAR(A.TTIME,'MM') AS TTIME,COUNT(*)
FROM XTBILL2011 A
GROUP BY GROUPING SETS(ROLLUP(A.VPD_COMPID),ROLLUP(TO_CHAR(A.TTIME,'MM')));
--这条语句产生了两个合计行，因为rollup或cube作为grouping sets的参数，相当于对每个
--rollup与cube操作的union all。所以上面的语句等价于：
SELECT A.VPD_COMPID,NULL AS TTIME,COUNT(*)
FROM XTBILL2011 A
GROUP BY ROLLUP(A.VPD_COMPID)
UNION ALL
SELECT NULL,TO_CHAR(A.TTIME,'MM'),COUNT(*)
FROM XTBILL2011 A
GROUP BY ROLLUP(TO_CHAR(A.TTIME,'MM'));
5、组合列分组简介：
--分组方式： rollup(a,b,c)<=>group by a,b,c; group by a,b; group by null
--分组方式： rollup(a,(b,c))<=>group by a,b,c; group by a; group by null
--分组方式： rollup(a,b),rollup(c)<=>group by a,b,c; group by a,b; group by a,c; group by a; group by c; group by null
--分组方式： rollup(a,b),grouping sets(c)<=>group by a,b,c; group by a,c; group by c
--分组方式： rollup(a),rollup(b),rollup(c)<=>group by a; group by b; group by c; group by a,b; group by a,c; group by b,c; group by a,b,c; group by null
6、GROUPING函数
--为了区别哪些是小计，grouping函数派上用场了！
SELECT A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'),COUNT(*),GROUPING(A.VPD_COMPID),GROUPING(TO_CHAR(A.TTIME,'MM')),
DECODE(GROUPING(A.VPD_COMPID),1,'所有单位',A.VPD_COMPID) VPD_COMPID,
DECODE(TO_CHAR(A.TTIME,'MM'),1,'所有月份',TO_CHAR(A.TTIME,'MM')) TTIME
FROM XTBILL2011 A
GROUP BY ROLLUP(A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'));
--过滤某些分组结果
SELECT A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'),COUNT(*),GROUPING(A.VPD_COMPID),GROUPING(TO_CHAR(A.TTIME,'MM')),
DECODE(GROUPING(A.VPD_COMPID),1,'所有单位',A.VPD_COMPID) VPD_COMPID,
DECODE(TO_CHAR(A.TTIME,'MM'),1,'所有月份',TO_CHAR(A.TTIME,'MM')) TTIME
FROM XTBILL2011 A
GROUP BY ROLLUP(A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'))
HAVING GROUPING(A.VPD_COMPID)=1 OR GROUPING(TO_CHAR(A.TTIME,'MM'))=0;
7、GROUPING_ID函数
--可用rollup或cube与grouping_id组合运用，过滤出想要的分组统计信息
SELECT A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'),GROUPING_ID(A.VPD_COMPID,TO_CHAR(A.TTIME,'MM')),COUNT(*)
FROM XTBILL2011 A
GROUP BY CUBE(A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'))
HAVING GROUPING_ID(A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'))=2; --1,2,3,0
--GROUPING_ID(a,b,c)过滤分组结果
分组级别 位向量 GROUPING_ID结果
a,b,C 0 0 0 0
a,B 0 0 1 1
A 0 1 1 3
汇总 1 1 1 7
8、GROUP_ID函数
--判断重复的分组
SELECT A.VPD_COMPID,TO_CHAR(A.TTIME,'MM') AS TTIME,GROUP_ID() ID,COUNT(*)
FROM XTBILL2011 A
GROUP BY GROUPING SETS(ROLLUP(A.VPD_COMPID),ROLLUP(TO_CHAR(A.TTIME,'MM')))
-- HAVING GROUP_ID()=0;
9、实例应用说明：
DROP TABLE T;
CREATE TABLE t(
ORDER_DATE DATE, --订购日期
ORDER_NO NUMBER, --订购号
ORDER_BOOK VARCHAR2(10), --订购书籍
ORDER_FEE NUMBER, --订单总金额
ORDER_NUM NUMBER
);
INSERT INTO T
SELECT TO_DATE('2010-05-01','YYYY-MM-DD')+LEVEL,
TRUNC(DBMS_RANDOM.value*1000),
'book1',100+LEVEL,LEVEL
FROM DUAL
CONNECT BY LEVEL<5;
INSERT INTO T
SELECT TO_DATE('2010-06-01','YYYY-MM-DD')+LEVEL,
TRUNC(DBMS_RANDOM.value*1000),
'book2',200+LEVEL,LEVEL
FROM DUAL
CONNECT BY LEVEL<5;
--要求：每组order_book内，按日期升序排列（order_no排序不管），常规分组在前，小计在后，合计最后。
SELECT DECODE(GROUPING_ID(ORDER_DATE,ORDER_NO,ORDER_BOOK),6,ORDER_BOOK||'小计',
7,'合计',
TO_CHAR(ORDER_DATE,'YYYY-MM-DD')
) ORDER_DATE1,
ORDER_NO,
DECODE(GROUPING_ID(ORDER_DATE,ORDER_NO,ORDER_BOOK),6,NULL,ORDER_BOOK) ORDER_BOOK1,
SUM(ORDER_FEE) ORDER_FEE,
SUM(ORDER_NUM) ORDER_NUM
FROM T
GROUP BY ROLLUP(ORDER_BOOK,(ORDER_DATE,ORDER_NO))
ORDER BY ORDER_BOOK,ORDER_DATE; 作者: 张兴康 时间: 2020-6-30 14:08 作者: 张兴康 时间: 2020-6-30 14:21
