Oracle analysis function（Oracle分析函数）ORACLE advanced function application
- grouping function
1, ROLLUP
Total, subtotal -- statistical standards and the corresponding dimension of the packet
- decrease from right to left: group by rollup (a, B, c): A, B, C; a, B (C; a (b total), C subtotal total);
--1)
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) partial rollup grouping
For after grouping, sum peid
SELECT, A.DWDH, A.YEAR, A.TYPEID, COUNT (*)
FROM XTYWBILL A
GROUP, BY, A.DWDH, A.YEAR, ROLLUP (A.TYPEID);
2, CUBE
--rollup can only "right to left", such as the need for a full range of dimensions for statistics, you need to use the cube function
--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: can summaries and subtotals do not need to remove some.
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
- Description: focus only on some dimensions of the single packet, subtotal
--group, by, grouping, sets (a, B, c) are equivalent to group,
by, a, group, by, B, group, by, C
- these three groups of union all results
--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) partial grouping sets grouping
- sum based on group by, only pay attention to the subtotal 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 as the parameters of GROUPING SETS
--grouping sets operations are grouped only on single columns without providing aggregate functionality, and if grouping sets is required, aggregate functionality is provided,
-
then you can use rollup or cube as the parameters of the grouping sets, such as the following statement to provide aggregate functionality:
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'));
This statement yields two total rows, because rollup or cube is the parameter of grouping sets, which is equivalent to each
Union all for --rollup and cube operations. So the above statement is equivalent to:
SELECT, A.VPD_COMPID, NULL, AS, TTIME, COUNT (*)
FROM XTBILL2011 A
c++trunc函数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, combination column grouping brief introduction:
- grouping methods: rollup (a, B, c), <=>group, by, a, B, C, group, by, a, B, group, by, null
- grouping methods: rollup (a, (B, c)), <=>group, by, a, B, C, group, by, a, group, by, null
- grouping mode: rollup (a, B),
汇总（C）< = >组，B、C组，B；；组，C组由；组；C组为空；
——分组方式：汇总（A，B），（C）分组集< = >组，B、C组，C；C 组的；
——分组方式：汇总（一），汇总（B），汇总（C）<= >组由；组B；C组；组的，B组，C；；组B、C；A，B，C组；组由空
6、分组函数
——为了区别哪些是小计，分组函数派上用场了！
选择a.vpd_compid，to_char（a.ttime，'mm '），计数（*），分组（a.vpd_compid），分组（to_char（a.ttime，'mm '）），

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。