分組統計 GROUP

                個人認為，這個在財務計算中肯定有用：

A),

select tc_orc002, tc_orc017, avg(tc_orc018)
  from ds4.tc_orc_file
 where tc_orc016 > sysdate - 2
   and tc_orc003 is not null
 group by rollup(tc_orc002, tc_orc017)；

 

 TN70BS228B 2MN70-61B 0.01
 TN70BS228B  0.01
 PU90UHS030G 2MU90-04G 1
 PU90UHS030G  1
 VN60V-060SB 2MN60-01B 0.01
 VN60V-060SB  0.01
 PU90UHS022.4G 2MU90-04G 1
 PU90UHS022.4G  1
 RE70J-S018B0100 2ME70-01B 0.09
 RE70J-S018B0100  0.09
 RN70J-G210B0100 2MN70-01B 28.04
 RN70J-G210B0100  28.04

分組小計，說白了，就是把平均值給出後，把平均的結果中平均一遍，最後再把每條記錄求和後除以總數；

B):

select tc_orc002, tc_orc017, SUM(tc_orc018),AVG(tc_orc018)
  from ds4.tc_orc_file
 where tc_orc016 > sysdate -0.5
   and tc_orc003 is not null AND TC_ORC017 LIKE'2MN%'
 group by cube(tc_orc002, tc_orc017)

個人感覺就是在rollup的基礎上，再把每類統計一下：

                 28.22     2.56545454545455
  2MN50-01B 0.25      0.25
  2MN70-01B 25.2     3.15
  2MN90-01B 2.77     1.385
 RN90J-G085B0100    1.94 1.94
 RN90J-G085B0100    2MN90-01B   1.94 1.94
 RN90J-S040B0100                        0.83 0.83
 RN90J-S040B0100     2MN90-01B    0.83 0.83
 RN70AS568-129B0100  0.53 0.53
 RN70AS568-129B0100 2MN70-01B  0.53 0.53
 XN70AS568-312B0100  11.11 11.11
 XN70AS568-312B0100 2MN70-01B 11.11 11.11
 QN70M01002005B0100A  0 0
 QN70M01002005B0100A 2MN70-01B 0 0
 RN50M008000350B0100  0.25 0.25
 RN50M008000350B0100 2MN50-01B 0.25 0.25
 RN70M003200120B0100  0.03 0.03
 RN70M003200120B0100 2MN70-01B 0.03 0.03

C):Grouping:

在統計的時候是否用到了特定列：

select tc_orc002, tc_orc017, SUM(tc_orc018),AVG(tc_orc018),grouping(tc_orc017)
  from ds4.tc_orc_file
 where tc_orc016 > sysdate -0.5
   and tc_orc003 is not null AND TC_ORC017 LIKE'2MN%'
 group by cube(tc_orc002, tc_orc017);

 

D):GROUPING SETS:

SELECT deptno,job,avg(sal), FROM emp

group by grouping sets(deptno,job);

這個是把 部門的平均工資，和崗位工資 合起來而已。

deptno          job             avg(sal)

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

10                                 2916.6

20                                 2175

30                                 1566.7

                analyst           3000

                cleark             1037.5

                manager          2758.33

                president         5000

                salesman         1400