我們透過這個文章來看一下AVG函式的普通用法和分析函式用法的區別。都是得到平均數，但是作用的範圍是不一樣的。
【Analytic】分析函式之MIN函式：http://space.itpub.net/519536/viewspace-624736
【Analytic】分析函式之MAX函式：http://space.itpub.net/519536/viewspace-624749

1.萬變不離其宗，先看AVG函式的語法描述。

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”描述：

Good luck.

secooler
10.01.10

-- The End --

【Analytic】分析函式之AVG函式

相關文章