Oracle從8.1.6開始提供分析函式，分析函式用於計算基於組的某種聚合值，它和聚合函式的不同之處是對於每個組返回多行，而聚合函式對於每個組只返回一行。
下面例子中使用的表來自Oracle自帶的HR使用者下的表，如果沒有安裝該使用者，可以在SYS使用者下執行$ORACLE_HOME/demo/schema/human_resources/hr_main.sql來建立。
少數幾個例子需要訪問SH使用者下的表，如果沒有安裝該使用者，可以在SYS使用者下執行$ORACLE_HOME/demo/schema/sales_history/sh_main.sql來建立。
如果未指明預設是在HR使用者下執行例子。
開窗函式的的理解：
開窗函式指定了分析函式工作的資料視窗大小，這個資料視窗大小可能會隨著行的變化而變化，舉例如下：
over（order by salary）按照salary排序進行累計，order by是個預設的開窗函式
over（partition by deptno）按照部門分割槽
over（order by salary range between 50 preceding and 150 following）
每行對應的資料視窗是之前行幅度值不超過50，之後行幅度值不超過150
over（order by salary rows between 50 preceding and 150 following）
每行對應的資料視窗是之前50行，之後150行
over（order by salary rows between unbounded preceding and unbounded following）
每行對應的資料視窗是從第一行到最後一行，等效：
over（order by salary range between unbounded preceding and unbounded following）

主要參考資料：《expert one-on-one》 Tom Kyte 《Oracle9i SQL Reference》第6章

AVG
功能描述：用於計算一個組和資料視窗內表示式的平均值。
SAMPLE：下面的例子中列c_mavg計算員工表中每個員工的平均薪水報告，該平均值由當前員工和與之具有相同經理的前一個和後一個三者的平均數得來；

SELECT manager_id, last_name, hire_date, salary,
AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS c_mavg
FROM employees;

MANAGER_ID LAST_NAME HIRE_DATE SALARY C_MAVG
---------- ------------------------- --------- ---------- ----------
100 Kochhar 21-SEP-89 17000 17000
100 De Haan 13-JAN-93 17000 15000
100 Raphaely 07-DEC-94 11000 11966.6667
100 Kaufling 01-MAY-95 7900 10633.3333
100 Hartstein 17-FEB-96 13000 9633.33333
100 Weiss 18-JUL-96 8000 11666.6667
100 Russell 01-OCT-96 14000 11833.3333
.
.
.

CORR
功能描述：返回一對錶達式的相關係數，它是如下的縮寫：
COVAR_POP(expr1,expr2)/STDDEV_POP(expr1)*STDDEV_POP(expr2))
從統計上講，相關性是變數之間關聯的強度，變數之間的關聯意味著在某種程度
上一個變數的值可由其它的值進行預測。透過返回一個-1~1之間的一個數, 相關
係數給出了關聯的強度，0表示不相關。
SAMPLE：下例返回1998年月銷售收入和月單位銷售的關係的累積係數（本例在SH使用者下執行）

SELECT t.calendar_month_number,
CORR (SUM(s.amount_sold), SUM(s.quantity_sold))
OVER (ORDER BY t.calendar_month_number) as CUM_CORR
FROM sales s, times t
WHERE s.time_id = t.time_id AND calendar_year = 1998
GROUP BY t.calendar_month_number
ORDER BY t.calendar_month_number;

CALENDAR_MONTH_NUMBER CUM_CORR
--------------------- ----------
1
2 1
3 .994309382
4 .852040875
5 .846652204
6 .871250628
7 .910029803
8 .917556399
9 .920154356
10 .86720251
11 .844864765
12 .903542662

COVAR_POP
功能描述：返回一對錶達式的總體協方差。
SAMPLE：下例CUM_COVP返回定價和最小產品價格的累積總體協方差

SELECT product_id, supplier_id,
COVAR_POP(list_price, min_price)
OVER (ORDER BY product_id, supplier_id) AS CUM_COVP,
COVAR_SAMP(list_price, min_price)
OVER (ORDER BY product_id, supplier_id) AS CUM_COVS
FROM product_information p
WHERE category_id = 29
ORDER BY product_id, supplier_id;

PRODUCT_ID SUPPLIER_ID CUM_COVP CUM_COVS
---------- ----------- ---------- ----------
1774 103088 0
1775 103087 1473.25 2946.5
1794 103096 1702.77778 2554.16667
1825 103093 1926.25 2568.33333
2004 103086 1591.4 1989.25
2005 103086 1512.5 1815
2416 103088 1475.97959 1721.97619[@more@]

Oracle 9i 分析函式參考手冊(1)

相關文章