关于avg

------------前文转自secooler 快乐的DBA---------------------------
原文地址http://blog.itpub.net/519536/viewspace-624799/
--------------------------------------------------------------
我们通过这个文章来看一下AVG函数的普通用法和分析函数用法的区别。都是得到平均数，但是作用的范围是不一样的。
【Analytic】分析函数之MIN函数：http://space.itpub.net/519536/viewspace-624736
【Analytic】分析函数之MAX函数：http://space.itpub.net/519536/viewspace-624749

1.万变不离其宗，先看AVG函数的语法描述。
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions011.htm#SQLRF00609
AVG([ DISTINCT | ALL ] expr)
  [ OVER(analytic_clause) ]

2.通过实验看一下两种AVG函数的用法和区别
1）创建测试表T，并初始化几条数据。
sec@ora10g> create table t (group_id number(10), name varchar2(10), salary int);
sec@ora10g> insert into t values (1,'Tom',1200);
sec@ora10g> insert into t values (2,'Kary',2400);
sec@ora10g> insert into t values (2,'Joe',800);
sec@ora10g> insert into t values (3,'Erick',3600);
sec@ora10g> insert into t values (3,'Andy',600);
sec@ora10g> insert into t values (3,'Mary',300);
sec@ora10g> commit;

2）T表全貌
sec@ora10g> select * from t;

  GROUP_ID NAME                               SALARY
---------- ------------------------------ ----------
         1 Tom                                  1200
         2 Kary                                 2400
         2 Joe                                   800
         3 Erick                                3600
         3 Andy                                  600
         3 Mary                                  300

6 rows selected.

共三组数据，group_id分别是1、2和3。第1组有一个人，第2组有两个人，第3组有三个人。最后一列是每个人的薪水值。

3）先看一下普通AVG函数的用法
sec@ora10g> select group_id, name, salary, avg(salary) from t group by group_id, name, salary order by group_id;

  GROUP_ID NAME                               SALARY AVG(SALARY)
---------- ------------------------------ ---------- -----------
         1 Tom                                  1200        1200
         2 Joe                                   800         800
         2 Kary                                 2400        2400
         3 Andy                                  600         600
         3 Erick                                3600        3600
         3 Mary                                  300         300

6 rows selected.

得到的最大值是按照“group_id, name, salary”三列进行分组后的每组最大值。

4）再看一下分析函数AVG得到的平均值
sec@ora10g> select group_id, name, salary, avg(salary) over (partition by group_id) analytic_avg from t order by group_id;

  GROUP_ID NAME                               SALARY ANALYTIC_AVG
---------- ------------------------------ ---------- ------------
         1 Tom                                  1200         1200
         2 Kary                                 2400         1600
         2 Joe                                   800         1600
         3 Erick                                3600         1500
         3 Andy                                  600         1500
         3 Mary                                  300         1500

6 rows selected.

注意这里得到的平均值与基本AVG函数得到的平均值区别，这里的平均值是每行记录所对应组内的平均薪酬值。

5）我们进一步限定一下计算的范围
sec@ora10g> select group_id, name, salary, avg(salary) over (partition by group_id order by name rows between 1 preceding and 1 following) analytic_avg from t order by group_id;

  GROUP_ID NAME                               SALARY ANALYTIC_AVG
---------- ------------------------------ ---------- ------------
         1 Tom                                  1200         1200
         2 Joe                                   800         1600
         2 Kary                                 2400         1600
         3 Andy                                  600         2100
         3 Erick                                3600         1500
         3 Mary                                  300         1950

6 rows selected.

此时得到的薪酬值是每行记录与其之前的记录和其之后的记录三者的平均值。
注意头和尾的平均值计算方法。
头：组内第一条记录与第二条记录的和除以二得到第一条记录的平均值；
尾：组内最后一条记录与倒数第二条记录的和除以二得到最后一条记录的平均值。

3.小结
具体问题具体分析，在统计分析报表系统中AVG分析函数用处颇大。
有关分析函数的扩展可以参考Oracle的官方文档中的“Analytic Functions”描述：http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm#SQLRF06174

Good luck.

secooler
10.01.10

-- The End --

------------------------------------华丽的分割线--------------------------------------

补充：
1.avg(salary) over (partition by group_id order by name rows between 1 preceding and 1 following) 
  红色部分不写代表从第一行到当前行为止~
2.如果向前没有行了，或者向后没有行了，那就不计算了~（1950=（3600+300）x2）
3.Null不会被当做一个value来计算
4.还有CURRENT ROW这种值（ between  CURRENT ROW  and 3 following,当前行+接下来的三行）