Oracle 9i 分析函數參考手冊

　　Oracle從開始提供分析函數分析函數用於計算基於組的某種聚合值它和聚合函數的不同之處是對於每個組返回多行而聚合函數對於每個組只返回一行
　　
　　下面例子中使用的表來自Oracle自帶的HR用戶下的表如果沒有安裝該用戶可以在SYS用戶下運行$ORACLE_HOME/demo/schema/human_resources/hr_mainsql來創建
　　
　　少數幾個例子需要訪問SH用戶下的表如果沒有安裝該用戶可以在SYS用戶下運行$ORACLE_HOME/demo/schema/sales_history/sh_mainsql來創建
　　
　　如果未指明缺省是在HR用戶下運行例子
　　
　　開窗函數的的理解
　　
　　開窗函數指定了分析函數工作的數據窗口大小這個數據窗口大小可能會隨著行的變化而變化舉例如下
　　
　　over（order by salary） 按照salary排序進行累計order by是個默認的開窗函數
　　over（partition by deptno）按照部門分區
　　over（order by salary range between preceding and following）
　　每行對應的數據窗口是之前行幅度值不超過之後行幅度值不超過
　　over（order by salary rows between preceding and following）
　　每行對應的數據窗口是之前行之後行
　　over（order by salary rows between unbounded preceding and unbounded following）
　　每行對應的數據窗口是從第一行到最後一行等效
　　over（order by salary range between unbounded preceding and unbounded following）
　　
　　主要參考資料《expert oneonone》 Tom Kyte　《Oraclei SQL Reference》第章
　　
　　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 PRECEDING AND FOLLOWING) AS c_mavg
　　FROM employees;
　　
　　MANAGER_ID LAST_NAME　　　　　　　　 HIRE_DATE　　 SALARY　　 C_MAVG
　　
　　 Kochhar　　　　　　　　　 SEP　　　　　　
　　 De Haan　　　　　　　　　 JAN　　　　　　
　　 Raphaely　　　　　　　　　DEC　　　
　　 Kaufling　　　　　　　　　MAY　　　
　　 Hartstein　　　　　　　　 FEB　　　
　　 Weiss　　　　　　　　　　 JUL　　　
　　 Russell　　　　　　　　　 OCT　　　
　　
　　
　　
　　
　　CORR
　　功能描述返回一對表達式的相關系數它是如下的縮寫
　　
　　COVAR_POP(exprexpr)/STDDEV_POP(expr)*STDDEV_POP(expr))從統計上講相關性是變量之間關聯的強度變量之間的關聯意味著在某種程度上一個變量的值可由其它的值進行預測通過返回一個~之間的一個數 相關系數給出了關聯的強度表示不相關
　　
　　SAMPLE下例返回年月銷售收入和月單位銷售的關系的累積系數（本例在SH用戶下運行）
　　
　　SELECT tcalendar_month_number
　　CORR (SUM(samount_sold) SUM(squantity_sold))
　　OVER (ORDER BY tcalendar_month_number) as CUM_CORR
　　FROM sales s times t
　　WHERE stime_id = ttime_id AND calendar_year =
　　GROUP BY tcalendar_month_number
　　ORDER BY tcalendar_month_number;
　　
　　CALENDAR_MONTH_NUMBER　 CUM_CORR
　　
　　
　　　　　　　
　　
　　
　　
　　
　　
　　
　　
　　　
　　
　　
　　
　　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 =
　　ORDER BY product_id supplier_id;
　　
　　PRODUCT_ID SUPPLIER_ID　 CUM_COVP　 CUM_COVS
　　
　　　　　　　　　　
　　　　　　　　　
　　　　　
　　　　　　　
　　　　　　　 　　
　　　　　　　 　　　
　　　　　
　　
　　
　　
　　COVAR_SAMP
　　功能描述返回一對表達式的樣本協方差
　　SAMPLE下例CUM_COVS返回定價和最小產品價格的累積樣本協方差
　　
　　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 =
　　ORDER BY product_id supplier_id;
　　
　　PRODUCT_ID SUPPLIER_ID　 CUM_COVP　 CUM_COVS
　　
　　　　　　　　　　
　　　　　　　　　
　　　　　
　　　　　　　
　　　　　　　 　　
　　　　　　　 　　　
　　　　　
　　
　　
　　
　　COUNT
　　功能描述對一組內發生的事情進行累積計數如果指定*或一些非空常數count將對所有行計數如果指定一個表達式count返回表達式非空賦值的計數當有相同值出現時這些相等的值都會被納入被計算的值可以使用DISTINCT來記錄去掉一組中完全相同的數據後出現的行數
　　
　　SAMPLE下面例子中計算每個員工在按薪水排序中當前行附近薪水在[nn+]之間的行數n表示當前行的薪水
　　
　　例如Philtanker的薪水排在他之前的行中薪水大於等於的有行排在他之後的行中薪水小於等於＋的行沒有所以count計數值cnt為（包括自己當前行）cnt值相當於小於等於當前行的SALARY值的所有行數
　　
　　SELECT last_name salary COUNT(*) OVER () AS cnt
　　COUNT(*) OVER (ORDER BY salary) AS cnt
　　COUNT(*) OVER (ORDER BY salary RANGE BETWEEN PRECEDING
　　AND FOLLOWING) AS cnt FROM employees;
　　
　　LAST_NAME　　　 SALARY　　　 CNT　　　 CNT　　　 CNT
　　
　　Olson　　　　　 　　　　　　　　　　　　　　
　　Markle　　　　　　　　　　　　　　　　　　　
　　Philtanker　　　　　　　　　　　　　　　　　
　　Landry　　　　　　　　　　　　　　　　　　　
　　Gee　　　　　　 　　　　　　　　　　　　　　
　　Colmenares　　　　　　　　　　　 　　　　
　　Patel　　　　　 　　　　　　　　 　　　　
　　
　　
　　
　　CUME_DIST
　　功能描述計算一行在組中的相對位置CUME_DIST總是返回大於小於或等於的數該數表示該行在N行中的位置例如在一個行的組中返回的累計分布值為///
　　SAMPLE下例中計算每個工種的員工按薪水排序依次累積出現的分布百分比
　　
　　SELECT job_id last_name salary CUME_DIST()
　　OVER (PARTITION BY job_id ORDER BY salary) AS cume_dist
　　FROM employees　WHERE job_id LIKE PU%;
　　
　　JOB_ID　　 LAST_NAME　　　　　　　　　　 SALARY　CUME_DIST
　　
　　PU_CLERK　 Colmenares　　　　　　　　　　　　　　　
　　PU_CLERK　 Himuro　　　　　　　　　　　　　　　　　
　　PU_CLERK　 Tobias　　　　　　　　　　　　　　　　　
　　PU_CLERK　 Baida　　　　　　　　　　　　　 　　　　
　　PU_CLERK　 Khoo　　　　　　　　　　　　　　　　　　　
　　PU_MAN　　 Raphaely　　　　　　　　　　　 　　　　　
　　
　　
　　DENSE_RANK
　　功能描述根據ORDER BY子句中表達式的值從查詢返回的每一行計算它們與其它行的相對位置組內的數據按ORDER BY子句排
From:http://tw.wingwit.com/Article/program/Oracle/201311/17275.html