oracle分析函数（Oracle analysis function）.doc

oracle分析函数（Oracle analysis function） 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 of 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 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