當前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

Oracle所有分析函数转

發布時間：2025/3/15 编程问答 28 豆豆

生活随笔收集整理的這篇文章主要介紹了 Oracle所有分析函数转小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

Oracle所有分析函數<轉>

Oracle分析函數——函數列表

SUM????????：該函數計算組中表達式的累積和

MIN????????：在一個組中的數據窗口中查找表達式的最小值

MAX????????：在一個組中的數據窗口中查找表達式的最大值

AVG?????????：用于計算一個組和數據窗口內表達式的平均值。

COUNT??????：對一組內發生的事情進行累積計數

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

RANK???????：根據ORDER BY子句中表達式的值，從查詢返回的每一行，計算它們與其它行的相對位置

DENSE_RANK?：根據ORDER BY子句中表達式的值，從查詢返回的每一行，計算它們與其它行的相對位置

FIRST???????：從DENSE_RANK返回的集合中取出排在最前面的一個值的行

LAST????????：從DENSE_RANK返回的集合中取出排在最后面的一個值的行

FIRST_VALUE?：返回組中數據窗口的第一個值

LAST_VALUE?：返回組中數據窗口的最后一個值。

LAG????????：可以訪問結果集中的其它行而不用進行自連接

LEAD???????：LEAD與LAG相反，LEAD可以訪問組中當前行之后的行

ROW_NUMBER：返回有序組中一行的偏移量，從而可用于按特定標準排序的行號

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

STDDEV?????：計算當前行關于組的標準偏離

STDDEV_POP：該函數計算總體標準偏離，并返回總體變量的平方根

STDDEV_SAMP：該函數計算累積樣本標準偏離，并返回總體變量的平方根

VAR_POP????：該函數返回非空集合的總體變量（忽略null）

VAR_SAMP???：該函數返回非空集合的樣本變量（忽略null）

VARIANCE???：如果表達式中行數為1，則返回0，如果表達式中行數大于1，則返回VAR_SAMP

COVAR_POP??：返回一對表達式的總體協方差

COVAR_SAMP：返回一對表達式的樣本協方差

CORR???????：返回一對表達式的相關系數

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

CUME_DIST??：計算一行在組中的相對位置

NTILE???????：將一個組分為"表達式"的散列表示

PERCENT_RANK：和CUME_DIST（累積分配）函數類似

PERCENTILE_DISC：返回一個與輸入的分布百分比值相對應的數據值

PERCENTILE_CONT：返回一個與輸入的分布百分比值相對應的數據值

RATIO_TO_REPORT：該函數計算expression/(sum(expression))的值，它給出相對于總數的百分比

REGR_ (Linear Regression) Functions：這些線性回歸函數適合最小二乘法回歸線，有9個不同的回歸函數可使用

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

CUBE????????:按照OLAP的CUBE方式進行數據統計,即各個維度均需統計

ROLLUP??????:

SELECT

?department_id,

?manager_id,

?employee_id,

?first_name||' '||last_name employee_name,

?hire_date,

?salary,

?job_id

?FROM employees

?ORDER BY department_id,hire_date

Oracle分析函數實際上操作對象是查詢出的數據集，也就是說不需二次查詢數據庫，實際上就是oracle實現了一些我們自身需要編碼實現的統計功能，對于簡化開發工作量有很大的幫助，特別在開發第三方報表軟件時是非常有幫助的。Oracle從8.1.6開始提供分析函數。

oracle分析函數的語法：

function_name(arg1,arg2,...)

over

( )

?????說明：

1. partition-clause?數據記錄集分組

2. order-by-clause??數據記錄集排序

3. windowing clause?功能非常強大、比較復雜，定義分析函數在操作行的集合。有三種開窗方式: range、row、specifying。

--Partition by，按相應的值(manager_id)進行分組統計

SELECT

?manager_id,

?first_name||' '||last_name employee_name,

??hire_date,

?salary,

?AVG(salary) OVER (PARTITION BY manager_id) avg_salary

FROM employees;

--等同于上面

SELECT

?a.manager_id,

?a.employee_name,

?a.hire_date,

?a.salary,

?b.avg_salary

FROM

(

?SELECT

???manager_id,

???first_name||' '||last_name employee_name,

???hire_date,

???salary

???FROM employees

) a,

(

?SELECT

???manager_id,

???AVG(salary) avg_salary

???FROM employees

??GROUP BY manager_id

) b

WHERE a.manager_id=b.manager_id

ORDER BY a.manager_id

--Order by按相應的值(hire_date)進行排序并累計統計

SELECT

?manager_id,

?first_name||' '||last_name employee_name,

?hire_date,

?salary,

?AVG(salary) OVER (ORDER BY hire_date)

FROM employees;

--Partition by Order by首先按相應的值(manager_id,hire_date)排序，并按order by的值(hire_date)進行累計統計

SELECT

?manager_id,

?first_name||' '||last_name employee_name,

?hire_date,

?salary,

?AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date)

FROM employees;

--Partition by Order by首先按相應的值(manager_id,hire_date)排序，并按order by的值(hire_date)進行累計統計

--該平均值由當前員工和與之具有相同經理的前一個和后兩個三者的平均數得來

SELECT

?manager_id,

?first_name||' '||last_name employee_name,

?hire_date,

?salary,

?AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING)

FROM employees;

--Partition by Order by首先按相應的值(manager_id,hire_date)排序，并按order by的值(hire_date)進行累計統計

--該平均值由當前員工和與之具有相同經理,并且雇用時間在該員工時間之前的50天以內和在該員工之后的150天之內員工的薪水的平均值

--range為取值范圍，估計只有數字和日期能夠進行取值了

SELECT

?manager_id,

?first_name||' '||last_name employee_name,

?hire_date,

?salary,

?AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING)

FROM employees;

--Partition by Order by首先按相應的值(manager_id,hire_date)排序，并按order by的值(hire_date)進行累計統計

--該平均值由當前員工和與之具有相同經理的平均值

--每行對應的數據窗口是從第一行到最后一行

SELECT

?manager_id,

?first_name||' '||last_name employee_name,

?hire_date,

?salary,

?AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date) avg_salary_part_order,

?AVG(salary) OVER (PARTITION BY manager_id ) avg_salary_order,?

?AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) avg_salary_unbound1, --等同于僅partition時候的值

?AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) avg_salary_unbound2--等同于上面

FROM employees;

Oracle分析函數——SUM,AVG,MIN,MAX,COUNT

SUM

功能描述：該函數計算組中表達式的累積和。

SAMPLE：下例計算同一經理下員工的薪水累積值

MIN

功能描述：在一個組中的數據窗口中查找表達式的最小值。

SAMPLE：下面例子中dept_min返回當前行所在部門的最小薪水值

MAX

功能描述：在一個組中的數據窗口中查找表達式的最大值。

SAMPLE：下面例子中dept_max返回當前行所在部門的最大薪水值

AVG

功能描述：用于計算一個組和數據窗口內表達式的平均值。

SAMPLE：下面的例子中列c_mavg計算員工表中每個員工的平均薪水報告

SELECT

?department_id,

?first_name||' '||last_name employee_name,

?hire_date,?

?salary,

?MIN(salary) OVER (PARTITION BY department_id order by hire_date) AS dept_min,

?MAX(salary) OVER (PARTITION BY department_id order by hire_date) AS dept_max,?

?AVG(salary) OVER (PARTITION BY department_id order by hire_date) AS dept_avg,???

?SUM(salary) OVER (PARTITION BY department_id order by hire_date) AS dept_sum/*,???

?COUNT(*) OVER (ORDER BY salary) AS count_by_salary,?

?COUNT(*) OVER (ORDER BY salary RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) AS count_by_salary_range*/

FROM employees

COUNT

功能描述：對一組內發生的事情進行累積計數，如果指定*或一些非空常數，count將對所有行計數，如果指定一個表達式，count返回表達式非空賦值的計數，當有相同值出現時，這些相等的值都會被納入被計算的值；可以使用DISTINCT來記錄去掉一組中完全相同的數據后出現的行數。

SAMPLE：下面例子中計算每個員工在按薪水排序中當前行附近薪水在[n-50,n+150]之間的行數，n表示當前行的薪水

例如，Philtanker的薪水2200，排在他之前的行中薪水大于等于2200-50的有1行，排在他之后的行中薪水小于等于2200＋150的行沒有，所以count計數值cnt3為2（包括自己當前行）；cnt2值相當于小于等于當前行的SALARY值的所有行數

SELECT

?department_id,

?first_name||' '||last_name employee_name,

?salary,

?COUNT(*) OVER (ORDER BY salary) AS count_by_salary,?

?COUNT(*) OVER (ORDER BY salary RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) AS count_by_salary_range

FROM employees

WHERE department_id in (10,20,30);

Oracle分析函數——函數RANK,DENSE_RANK,FIRST,LAST…

RANK

功能描述：根據ORDER BY子句中表達式的值，從查詢返回的每一行，計算它們與其它行的相對位置。組內的數據按ORDER BY子句排序，然后給每一行賦一個號，從而形成一個序列，該序列從1開始，往后累加。每次ORDER BY表達式的值發生變化時，該序列也隨之增加。有同樣值的行得到同樣的數字序號（認為null時相等的）。然而，如果兩行的確得到同樣的排序，則序數將隨后跳躍。若兩行序數為1，則沒有序數2，序列將給組中的下一行分配值3，DENSE_RANK則沒有任何跳躍。

SAMPLE：下例中計算每個員工按部門分區再按薪水排序，依次出現的序列號（注意與DENSE_RANK函數的區別）

DENSE_RANK

SAMPLE：下例中計算每個員工按部門分區再按薪水排序，依次出現的序列號（注意與RANK函數的區別）

SELECT

?department_id,

?first_name||' '||last_name employee_name,

?salary,

?RANK() OVER (ORDER BY salary) AS RANK_ORDER,

?DENSE_RANK() OVER (ORDER BY salary) AS DENSE_RANK_ORDER??

FROM employees

SELECT

?department_id,

?first_name||' '||last_name employee_name,

?salary,

?RANK() OVER (PARTITION BY department_id ORDER BY salary) AS RANK_PART_ORDER,?

?DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary) AS DENSE_RANK_PART_ORDER???

FROM employees

Oracle分析函數——統計分析函數

方差和標準差：

樣本中各數據與樣本平均數的差的平方和的平均數叫做樣本方差；樣本方差的算術平方根叫做樣本標準差。樣本方差和樣本標準差都是衡量一個樣本波動大小的量，樣本方差或樣本標準差越大，樣本數據的波動就越大。

數學上一般用E{[X-E(X)]^2}來度量隨機變量X與其均值E(X)即期望的偏離程度，稱為X的方差。

方差是標準差的平方

方差和標準差。方差和標準差是測算離散趨勢最重要、最常用的指標。方差是各變量值與其均值離差平方的平均數，它是測算數值型數據離散程度的最重要的方法。標準差為方差的平方根，用S表示。

StdDev返回expr的樣本標準偏差。它可用作聚集和分析函數。它與stddev_samp的不同之處在于，當計算的輸入數據只有一行時，stddev返回0，而stddev_samp返回null。

Oracle數據庫中，標準偏差計算結果與variance用作集聚函數計算結果的平方根相等。該函數參數可取任何數字類型或是任何能隱式轉換成數字類型的非數字類型。

STDDEV

功能描述：計算當前行關于組的標準偏離。（Standard Deviation）

SAMPLE：

STDDEV_SAMP

功能描述：該函數計算累積樣本標準偏離，并返回總體變量的平方根，其返回值與VAR_POP函數的平方根相同。（Standard Deviation－Sample）

SAMPLE：

它與stddev_samp的不同之處在于，當計算的輸入數據只有一行時，stddev返回0，而stddev_samp返回null。

SELECT

?department_id,

?first_name||' '||last_name employee_name,

?hire_date,

?salary,

?STDDEV_SAMP(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS cum_sdev

?FROM employees

?WHERE department_id in (20,30,60);

STDDEV和STDDEV_SAMP的區別

SELECT

?first_name||' '||last_name employee_name,

?hire_date,

?salary,

?STDDEV(salary) OVER (ORDER BY hire_date) "StdDev",

?STDDEV_SAMP(salary) OVER (ORDER BY hire_date) AS cum_sdev

?FROM employees

VAR_POP

功能描述：（Variance Population）該函數返回非空集合的總體變量（忽略null），VAR_POP進行如下計算：

(SUM(expr2) - SUM(expr)2 / COUNT(expr)) / COUNT(expr)

VAR_SAMP

功能描述：（Variance Sample）該函數返回非空集合的樣本變量（忽略null），VAR_POP進行如下計算：

(SUM(expr*expr)-SUM(expr)*SUM(expr)/COUNT(expr))/(COUNT(expr)-1)

SAMPLE：

VARIANCE

功能描述：該函數返回表達式的變量，Oracle計算該變量如下：

如果表達式中行數為1，則返回0

如果表達式中行數大于1，則返回VAR_SAMP

SAMPLE：

SELECT

?department_id,

?first_name||' '||last_name employee_name,

?hire_date,

?salary,

?STDDEV(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS "STDDEV",

?STDDEV_SAMP(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS "STDDEV_SAMP",

?VAR_POP(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS "VAR_POP",

?VAR_SAMP(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS "VAR_SAMP",

?VARIANCE(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS "VARIANCE"?

?FROM employees

協方差分析是建立在方差分析和回歸分析基礎之上的一種統計分析方法。

方差分析是從質量因子的角度探討因素不同水平對實驗指標影響的差異。一般說來，質量因子是可以人為控制的。

回歸分析是從數量因子的角度出發，通過建立回歸方程來研究實驗指標與一個(或幾個)因子之間的數量關系。但大多數情況下，數量因子是不可以人為加以控制的。

兩個不同參數之間的方差就是協方差

若兩個隨機變量X和Y相互獨立，則E[(X-E(X))(Y-E(Y))]=0，因而若上述數學期望不為零，則X和Y必不是相互獨立的，亦即它們之間存在著一定的關系。

定義

E[(X-E(X))(Y-E(Y))]稱為隨機變量X和Y的協方差，記作COV(X，Y)，即COV(X，Y)=E[(X-E(X))(Y-E(Y))]。

COVAR_POP

功能描述：返回一對表達式的總體協方差。

SAMPLE：

COVAR_SAMP

功能描述：返回一對表達式的樣本協方差

SAMPLE：

SELECT

?a.department_id,

?a.employee_id,

?b.employee_id manager_id,

?a.first_name||' '||a.last_name employee_name,

?b.first_name||' '||b.last_name manager_name,??

?a.hire_date,

?a.salary employee_salary,

?b.salary manager_salary,

?COVAR_POP(a.salary,b.salary) OVER (ORDER BY a.department_id,a.hire_date ) AS CUM_COVP,

?COVAR_SAMP(a.salary,b.salary) OVER (ORDER BY a.department_id,a.hire_date ) AS CUM_SAMP

?FROM employees a,employees b

?WHERE a.manager_id=b.employee_id(+)

CORR

功能描述：返回一對表達式的相關系數，它是如下的縮寫：

COVAR_POP(expr1,expr2)/STDDEV_POP(expr1)*STDDEV_POP(expr2))

從統計上講，相關性是變量之間關聯的強度，變量之間的關聯意味著在某種程度

上一個變量的值可由其它的值進行預測。通過返回一個-1~1之間的一個數,相關

系數給出了關聯的強度，0表示不相關。

SELECT

?a.department_id,

?a.first_name||' '||a.last_name employee_name,

?b.first_name||' '||b.last_name manager_name,??

?a.hire_date,

?a.salary employee_salary,

?b.salary manager_salary,

?CORR(a.salary,b.salary) OVER (ORDER BY a.department_id,a.hire_date ) AS CORR

?FROM employees a,employees b

?WHERE a.manager_id=b.employee_id(+)

Oracle分析函數——統計分析函數

方差和標準差：

數學上一般用E{[X-E(X)]^2}來度量隨機變量X與其均值E(X)即期望的偏離程度，稱為X的方差。

方差是標準差的平方

STDDEV

功能描述：計算當前行關于組的標準偏離。（Standard Deviation）

SAMPLE：

STDDEV_SAMP

功能描述：該函數計算累積樣本標準偏離，并返回總體變量的平方根，其返回值與VAR_POP函數的平方根相同。（Standard Deviation－Sample）

SAMPLE：

它與stddev_samp的不同之處在于，當計算的輸入數據只有一行時，stddev返回0，而stddev_samp返回null。

SELECT

?department_id,

?first_name||' '||last_name employee_name,

?hire_date,

?salary,

?STDDEV_SAMP(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS cum_sdev

?FROM employees

?WHERE department_id in (20,30,60);

STDDEV和STDDEV_SAMP的區別

SELECT

?first_name||' '||last_name employee_name,

?hire_date,

?salary,

?STDDEV(salary) OVER (ORDER BY hire_date) "StdDev",

?STDDEV_SAMP(salary) OVER (ORDER BY hire_date) AS cum_sdev

?FROM employees

VAR_POP

功能描述：（Variance Population）該函數返回非空集合的總體變量（忽略null），VAR_POP進行如下計算：

(SUM(expr2) - SUM(expr)2 / COUNT(expr)) / COUNT(expr)

VAR_SAMP

功能描述：（Variance Sample）該函數返回非空集合的樣本變量（忽略null），VAR_POP進行如下計算：

(SUM(expr*expr)-SUM(expr)*SUM(expr)/COUNT(expr))/(COUNT(expr)-1)

SAMPLE：

VARIANCE

功能描述：該函數返回表達式的變量，Oracle計算該變量如下：

如果表達式中行數為1，則返回0

如果表達式中行數大于1，則返回VAR_SAMP

SAMPLE：

SELECT

?department_id,

?first_name||' '||last_name employee_name,

?hire_date,

?salary,

?STDDEV(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS "STDDEV",

?STDDEV_SAMP(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS "STDDEV_SAMP",

?VAR_POP(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS "VAR_POP",

?VAR_SAMP(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS "VAR_SAMP",

?VARIANCE(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS "VARIANCE"?

?FROM employees

協方差分析是建立在方差分析和回歸分析基礎之上的一種統計分析方法。

方差分析是從質量因子的角度探討因素不同水平對實驗指標影響的差異。一般說來，質量因子是可以人為控制的。

兩個不同參數之間的方差就是協方差

若兩個隨機變量X和Y相互獨立，則E[(X-E(X))(Y-E(Y))]=0，因而若上述數學期望不為零，則X和Y必不是相互獨立的，亦即它們之間存在著一定的關系。

定義

E[(X-E(X))(Y-E(Y))]稱為隨機變量X和Y的協方差，記作COV(X，Y)，即COV(X，Y)=E[(X-E(X))(Y-E(Y))]。

COVAR_POP

功能描述：返回一對表達式的總體協方差。

SAMPLE：

COVAR_SAMP

功能描述：返回一對表達式的樣本協方差

SAMPLE：

SELECT

?a.department_id,

?a.employee_id,

?b.employee_id manager_id,

?a.first_name||' '||a.last_name employee_name,

?b.first_name||' '||b.last_name manager_name,??

?a.hire_date,

?a.salary employee_salary,

?b.salary manager_salary,

?COVAR_POP(a.salary,b.salary) OVER (ORDER BY a.department_id,a.hire_date ) AS CUM_COVP,

?COVAR_SAMP(a.salary,b.salary) OVER (ORDER BY a.department_id,a.hire_date ) AS CUM_SAMP

?FROM employees a,employees b

?WHERE a.manager_id=b.employee_id(+)

CORR

功能描述：返回一對表達式的相關系數，它是如下的縮寫：

COVAR_POP(expr1,expr2)/STDDEV_POP(expr1)*STDDEV_POP(expr2))

從統計上講，相關性是變量之間關聯的強度，變量之間的關聯意味著在某種程度

上一個變量的值可由其它的值進行預測。通過返回一個-1~1之間的一個數,相關

系數給出了關聯的強度，0表示不相關。

SELECT

?a.department_id,

?a.first_name||' '||a.last_name employee_name,

?b.first_name||' '||b.last_name manager_name,??

?a.hire_date,

?a.salary employee_salary,

?b.salary manager_salary,

?CORR(a.salary,b.salary) OVER (ORDER BY a.department_id,a.hire_date ) AS CORR

?FROM employees a,employees b

?WHERE a.manager_id=b.employee_id(+)

Oracle分析函數——統計分析函數

方差和標準差：

數學上一般用E{[X-E(X)]^2}來度量隨機變量X與其均值E(X)即期望的偏離程度，稱為X的方差。

方差是標準差的平方

STDDEV

功能描述：計算當前行關于組的標準偏離。（Standard Deviation）

SAMPLE：

STDDEV_SAMP

功能描述：該函數計算累積樣本標準偏離，并返回總體變量的平方根，其返回值與VAR_POP函數的平方根相同。（Standard Deviation－Sample）

SAMPLE：

它與stddev_samp的不同之處在于，當計算的輸入數據只有一行時，stddev返回0，而stddev_samp返回null。

SELECT

?department_id,

?first_name||' '||last_name employee_name,

?hire_date,

?salary,

?STDDEV_SAMP(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS cum_sdev

?FROM employees

?WHERE department_id in (20,30,60);

STDDEV和STDDEV_SAMP的區別

SELECT

?first_name||' '||last_name employee_name,

?hire_date,

?salary,

?STDDEV(salary) OVER (ORDER BY hire_date) "StdDev",

?STDDEV_SAMP(salary) OVER (ORDER BY hire_date) AS cum_sdev

?FROM employees

VAR_POP

功能描述：（Variance Population）該函數返回非空集合的總體變量（忽略null），VAR_POP進行如下計算：

(SUM(expr2) - SUM(expr)2 / COUNT(expr)) / COUNT(expr)

VAR_SAMP

功能描述：（Variance Sample）該函數返回非空集合的樣本變量（忽略null），VAR_POP進行如下計算：

(SUM(expr*expr)-SUM(expr)*SUM(expr)/COUNT(expr))/(COUNT(expr)-1)

SAMPLE：

VARIANCE

功能描述：該函數返回表達式的變量，Oracle計算該變量如下：

如果表達式中行數為1，則返回0

如果表達式中行數大于1，則返回VAR_SAMP

SAMPLE：

SELECT

?department_id,

?first_name||' '||last_name employee_name,

?hire_date,

?salary,

?STDDEV(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS "STDDEV",

?STDDEV_SAMP(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS "STDDEV_SAMP",

?VAR_POP(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS "VAR_POP",

?VAR_SAMP(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS "VAR_SAMP",

?VARIANCE(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS "VARIANCE"?

?FROM employees

協方差分析是建立在方差分析和回歸分析基礎之上的一種統計分析方法。

方差分析是從質量因子的角度探討因素不同水平對實驗指標影響的差異。一般說來，質量因子是可以人為控制的。

兩個不同參數之間的方差就是協方差

若兩個隨機變量X和Y相互獨立，則E[(X-E(X))(Y-E(Y))]=0，因而若上述數學期望不為零，則X和Y必不是相互獨立的，亦即它們之間存在著一定的關系。

定義

E[(X-E(X))(Y-E(Y))]稱為隨機變量X和Y的協方差，記作COV(X，Y)，即COV(X，Y)=E[(X-E(X))(Y-E(Y))]。

COVAR_POP

功能描述：返回一對表達式的總體協方差。

SAMPLE：

COVAR_SAMP

功能描述：返回一對表達式的樣本協方差

SAMPLE：

SELECT

?a.department_id,

?a.employee_id,

?b.employee_id manager_id,

?a.first_name||' '||a.last_name employee_name,

?b.first_name||' '||b.last_name manager_name,??

?a.hire_date,

?a.salary employee_salary,

?b.salary manager_salary,

?COVAR_POP(a.salary,b.salary) OVER (ORDER BY a.department_id,a.hire_date ) AS CUM_COVP,

?COVAR_SAMP(a.salary,b.salary) OVER (ORDER BY a.department_id,a.hire_date ) AS CUM_SAMP

?FROM employees a,employees b

?WHERE a.manager_id=b.employee_id(+)

CORR

功能描述：返回一對表達式的相關系數，它是如下的縮寫：

COVAR_POP(expr1,expr2)/STDDEV_POP(expr1)*STDDEV_POP(expr2))

從統計上講，相關性是變量之間關聯的強度，變量之間的關聯意味著在某種程度

上一個變量的值可由其它的值進行預測。通過返回一個-1~1之間的一個數,相關

系數給出了關聯的強度，0表示不相關。

SELECT

?a.department_id,

?a.first_name||' '||a.last_name employee_name,

?b.first_name||' '||b.last_name manager_name,??

?a.hire_date,

?a.salary employee_salary,

?b.salary manager_salary,

?CORR(a.salary,b.salary) OVER (ORDER BY a.department_id,a.hire_date ) AS CORR

?FROM employees a,employees b

?WHERE a.manager_id=b.employee_id(+)

Oracle分析函數——統計分析函數

方差和標準差：

數學上一般用E{[X-E(X)]^2}來度量隨機變量X與其均值E(X)即期望的偏離程度，稱為X的方差。

方差是標準差的平方

STDDEV

功能描述：計算當前行關于組的標準偏離。（Standard Deviation）

SAMPLE：

STDDEV_SAMP

功能描述：該函數計算累積樣本標準偏離，并返回總體變量的平方根，其返回值與VAR_POP函數的平方根相同。（Standard Deviation－Sample）

SAMPLE：

它與stddev_samp的不同之處在于，當計算的輸入數據只有一行時，stddev返回0，而stddev_samp返回null。

SELECT

?department_id,

?first_name||' '||last_name employee_name,

?hire_date,

?salary,

?STDDEV_SAMP(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS cum_sdev

?FROM employees

?WHERE department_id in (20,30,60);

STDDEV和STDDEV_SAMP的區別

SELECT

?first_name||' '||last_name employee_name,

?hire_date,

?salary,

?STDDEV(salary) OVER (ORDER BY hire_date) "StdDev",

?STDDEV_SAMP(salary) OVER (ORDER BY hire_date) AS cum_sdev

?FROM employees

VAR_POP

功能描述：（Variance Population）該函數返回非空集合的總體變量（忽略null），VAR_POP進行如下計算：

(SUM(expr2) - SUM(expr)2 / COUNT(expr)) / COUNT(expr)

VAR_SAMP

功能描述：（Variance Sample）該函數返回非空集合的樣本變量（忽略null），VAR_POP進行如下計算：

(SUM(expr*expr)-SUM(expr)*SUM(expr)/COUNT(expr))/(COUNT(expr)-1)

SAMPLE：

VARIANCE

功能描述：該函數返回表達式的變量，Oracle計算該變量如下：

如果表達式中行數為1，則返回0

如果表達式中行數大于1，則返回VAR_SAMP

SAMPLE：

SELECT

?department_id,

?first_name||' '||last_name employee_name,

?hire_date,

?salary,

?STDDEV(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS "STDDEV",

?STDDEV_SAMP(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS "STDDEV_SAMP",

?VAR_POP(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS "VAR_POP",

?VAR_SAMP(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS "VAR_SAMP",

?VARIANCE(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS "VARIANCE"?

?FROM employees

協方差分析是建立在方差分析和回歸分析基礎之上的一種統計分析方法。

方差分析是從質量因子的角度探討因素不同水平對實驗指標影響的差異。一般說來，質量因子是可以人為控制的。

兩個不同參數之間的方差就是協方差

若兩個隨機變量X和Y相互獨立，則E[(X-E(X))(Y-E(Y))]=0，因而若上述數學期望不為零，則X和Y必不是相互獨立的，亦即它們之間存在著一定的關系。

定義

E[(X-E(X))(Y-E(Y))]稱為隨機變量X和Y的協方差，記作COV(X，Y)，即COV(X，Y)=E[(X-E(X))(Y-E(Y))]。

COVAR_POP

功能描述：返回一對表達式的總體協方差。

SAMPLE：

COVAR_SAMP

功能描述：返回一對表達式的樣本協方差

SAMPLE：

SELECT

?a.department_id,

?a.employee_id,

?b.employee_id manager_id,

?a.first_name||' '||a.last_name employee_name,

?b.first_name||' '||b.last_name manager_name,??

?a.hire_date,

?a.salary employee_salary,

?b.salary manager_salary,

?COVAR_POP(a.salary,b.salary) OVER (ORDER BY a.department_id,a.hire_date ) AS CUM_COVP,

?COVAR_SAMP(a.salary,b.salary) OVER (ORDER BY a.department_id,a.hire_date ) AS CUM_SAMP

?FROM employees a,employees b

?WHERE a.manager_id=b.employee_id(+)

CORR

功能描述：返回一對表達式的相關系數，它是如下的縮寫：

COVAR_POP(expr1,expr2)/STDDEV_POP(expr1)*STDDEV_POP(expr2))

從統計上講，相關性是變量之間關聯的強度，變量之間的關聯意味著在某種程度

上一個變量的值可由其它的值進行預測。通過返回一個-1~1之間的一個數,相關

系數給出了關聯的強度，0表示不相關。

SELECT

?a.department_id,

?a.first_name||' '||a.last_name employee_name,

?b.first_name||' '||b.last_name manager_name,??

?a.hire_date,

?a.salary employee_salary,

?b.salary manager_salary,

?CORR(a.salary,b.salary) OVER (ORDER BY a.department_id,a.hire_date ) AS CORR

?FROM employees a,employees b

?WHERE a.manager_id=b.employee_id(+)

Oracle分析函數——數據分布函數及報表函數

CUME_DIST

功能描述：計算一行在組中的相對位置，CUME_DIST總是返回大于0、小于或等于1的數，該數表示該行在N行中的位置。例如，在一個3行的組中，返回的累計分布值為1/3、2/3、3/3

SAMPLE：下例中計算每個部門的員工按薪水排序依次累積出現的分布百分比

SELECT

?department_id,

?first_name||' '||last_name employee_name,

?salary,

?CUME_DIST() OVER (PARTITION BY department_id ORDER BY salary) AS cume_dist

FROM employees

NTILE

功能描述：將一個組分為"表達式"的散列表示，例如，如果表達式=4，則給組中的每一行分配一個數（從1到4），如果組中有20行，則給前5行分配1，給下5行分配2等等。如果組的基數不能由表達式值平均分開，則對這些行進行分配時，組中就沒有任何percentile的行數比其它percentile的行數超過一行，最低的percentile是那些擁有額外行的percentile。例如，若表達式=4，行數=21，則percentile=1的有5行，percentile=2的有5行等等。

SAMPLE：下例中把6行數據分為4份

SELECT

?department_id,

?first_name||' '||last_name employee_name,

?salary,

?NTILE(4) OVER (PARTITION BY department_id ORDER BY salary DESC) AS quartile

?FROM employees

PERCENT_RANK

功能描述：和CUME_DIST（累積分配）函數類似，對于一個組中給定的行來說，在計算那行的序號時，先減1，然后除以n-1（n為組中所有的行數）。該函數總是返回0～1（包括1）之間的數。

SAMPLE：下例中如果Khoo的salary為2900，則pr值為0.6，因為RANK函數對于等值的返回序列值是一樣的

SELECT??

?department_id,

?first_name||' '||last_name employee_name,

?salary,

?PERCENT_RANK() OVER (PARTITION BY department_id ORDER BY salary) AS pr

FROM employees

ORDER BY department_id,salary;

PERCENTILE_DISC

功能描述：返回一個與輸入的分布百分比值相對應的數據值，分布百分比的計算方法見函數CUME_DIST，如果沒有正好對應的數據值，就取大于該分布值的下一個值。

注意：本函數與PERCENTILE_CONT的區別在找不到對應的分布值時返回的替代值的計算方法不同

SAMPLE：下例中0.7的分布值在部門30中沒有對應的Cume_Dist值，所以就取下一個分布值0.83333333所對應的SALARY來替代

SELECT

?department_id,

?first_name||' '||last_name employee_name,

?salary,

?PERCENTILE_DISC(0.7) WITHIN GROUP (ORDER BY salary ) OVER (PARTITION BY department_id) "Percentile_Disc",

?CUME_DIST() OVER (PARTITION BY department_id ORDER BY salary) "Cume_Dist"

FROM employees

PERCENTILE_CONT

功能描述：返回一個與輸入的分布百分比值相對應的數據值，分布百分比的計算方法見函數PERCENT_RANK，如果沒有正好對應的數據值，就通過下面算法來得到值：

RN = 1+ (P*(N-1))其中P是輸入的分布百分比值，N是組內的行數

CRN = CEIL(RN) FRN = FLOOR(RN)

if (CRN = FRN = RN) then

(value of expression from row at RN)

else

(CRN - RN) * (value of expression for row at FRN) +

(RN - FRN) * (value of expression for row at CRN)

注意：本函數與PERCENTILE_DISC的區別在找不到對應的分布值時返回的替代值的計算方法不同

算法太復雜，看不懂了L

SAMPLE：在下例中，對于部門60的Percentile_Cont值計算如下：

P=0.7 N=5 RN =1+ (P*(N-1)=1+(0.7*(5-1))=3.8 CRN = CEIL(3.8)=4

FRN = FLOOR(3.8)=3

（4 - 3.8）* 4800 + (3.8 - 3) * 6000 = 5760

SELECT

?department_id,

?first_name||' '||last_name employee_name,

?salary,??

?PERCENTILE_DISC(0.7) WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY department_id) "Percentile_Disc",

?PERCENTILE_CONT(0.7) WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY department_id) "Percentile_Cont",

?PERCENT_RANK() OVER (PARTITION BY department_id ORDER BY salary) "Percent_Rank"

?FROM employees

總案例

SELECT

?department_id,

?first_name||' '||last_name employee_name,

?salary,

?CUME_DIST() OVER (PARTITION BY department_id ORDER BY salary) AS cume_dist, --數據分布百分比

?NTILE(4) OVER (PARTITION BY department_id ORDER BY salary) AS quartile,????--數據分布，以NTILE中的exp來計算

?PERCENT_RANK() OVER (PARTITION BY department_id ORDER BY salary) AS pr,????--數據分布百分比，從0開始計

?PERCENTILE_DISC(0.7) WITHIN GROUP (ORDER BY salary ) OVER (PARTITION BY department_id) "Percentile_Disc",?--輸入的分布百分比值相對應的數據值

?PERCENTILE_CONT(0.7) WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY department_id) "Percentile_Cont"???--表達式太復雜了，...

FROM employees

RATIO_TO_REPORT

功能描述：該函數計算expression/(sum(expression))的值，它給出相對于總數的百分比，即當前行對sum(expression)的貢獻。

SAMPLE：下例計算每個員工的工資占該類員工總工資的百分比

SELECT

?department_id,

?first_name||' '||last_name employee_name,

?salary,

?RATIO_TO_REPORT(salary) OVER () AS rr

FROM employees

WHERE job_id = 'PU_CLERK';

REGR_ (Linear Regression) Functions

功能描述：這些線性回歸函數適合最小二乘法回歸線，有9個不同的回歸函數可使用。

REGR_SLOPE：返回斜率，等于COVAR_POP(expr1, expr2) / VAR_POP(expr2)

REGR_INTERCEPT：返回回歸線的y截距，等于

AVG(expr1) - REGR_SLOPE(expr1, expr2) * AVG(expr2)

REGR_COUNT：返回用于填充回歸線的非空數字對的數目

REGR_R2：返回回歸線的決定系數，計算式為：

If VAR_POP(expr2) = 0 then return NULL

If VAR_POP(expr1) = 0 and VAR_POP(expr2) != 0 then return 1

If VAR_POP(expr1) > 0 and VAR_POP(expr2 != 0 then

return POWER(CORR(expr1,expr),2)

REGR_AVGX：計算回歸線的自變量(expr2)的平均值，去掉了空對(expr1, expr2)后，等于AVG(expr2)

REGR_AVGY：計算回歸線的應變量(expr1)的平均值，去掉了空對(expr1, expr2)后，等于AVG(expr1)

REGR_SXX：返回值等于REGR_COUNT(expr1, expr2) * VAR_POP(expr2)

REGR_SYY：返回值等于REGR_COUNT(expr1, expr2) * VAR_POP(expr1)

REGR_SXY:返回值等于REGR_COUNT(expr1, expr2) * COVAR_POP(expr1, expr2)

（下面的例子都是在SH用戶下完成的）

SAMPLE 1：下例計算1998年最后三個星期中兩種產品（260和270）在周末的銷售量中已開發票數量和總數量的累積斜率和回歸線的截距

SELECT t.fiscal_month_number "Month", t.day_number_in_month "Day",

REGR_SLOPE(s.amount_sold, s.quantity_sold)

OVER (ORDER BY t.fiscal_month_desc, t.day_number_in_month) AS CUM_SLOPE,

REGR_INTERCEPT(s.amount_sold, s.quantity_sold)

OVER (ORDER BY t.fiscal_month_desc, t.day_number_in_month) AS CUM_ICPT

FROM sales s, times t

WHERE s.time_id = t.time_id

AND s.prod_id IN (270, 260)

AND t.fiscal_year=1998

AND t.fiscal_week_number IN (50, 51, 52)

AND t.day_number_in_week IN (6,7)

ORDER BY t.fiscal_month_desc, t.day_number_in_month;

SAMPLE 2：下例計算1998年4月每天的累積交易數量

SELECT UNIQUE t.day_number_in_month,

REGR_COUNT(s.amount_sold, s.quantity_sold)

OVER (PARTITION BY t.fiscal_month_number ORDER BY t.day_number_in_month)

"Regr_Count"

FROM sales s, times t

WHERE s.time_id = t.time_id

AND t.fiscal_year = 1998 AND t.fiscal_month_number = 4;

SAMPLE 3：下例計算1998年每月銷售量中已開發票數量和總數量的累積回歸線決定系數

SELECT t.fiscal_month_number,

REGR_R2(SUM(s.amount_sold), SUM(s.quantity_sold))

OVER (ORDER BY t.fiscal_month_number) "Regr_R2"

FROM sales s, times t

WHERE s.time_id = t.time_id

AND t.fiscal_year = 1998

GROUP BY t.fiscal_month_number

ORDER BY t.fiscal_month_number;

SAMPLE 4：下例計算1998年12月最后兩周產品260的銷售量中已開發票數量和總數量的累積平均值

SELECT t.day_number_in_month,

REGR_AVGY(s.amount_sold, s.quantity_sold)

OVER (ORDER BY t.fiscal_month_desc, t.day_number_in_month)

"Regr_AvgY",

REGR_AVGX(s.amount_sold, s.quantity_sold)

OVER (ORDER BY t.fiscal_month_desc, t.day_number_in_month)

"Regr_AvgX"

FROM sales s, times t

WHERE s.time_id = t.time_id

AND s.prod_id = 260

AND t.fiscal_month_desc = '1998-12'

AND t.fiscal_week_number IN (51, 52)

ORDER BY t.day_number_in_month;

SAMPLE 5：下例計算產品260和270在1998年2月周末銷售量中已開發票數量和總數量的累積REGR_SXY, REGR_SXX, and REGR_SYY統計值

SELECT t.day_number_in_month,

REGR_SXY(s.amount_sold, s.quantity_sold)

OVER (ORDER BY t.fiscal_year, t.fiscal_month_desc) "Regr_sxy",

REGR_SYY(s.amount_sold, s.quantity_sold)

OVER (ORDER BY t.fiscal_year, t.fiscal_month_desc) "Regr_syy",

REGR_SXX(s.amount_sold, s.quantity_sold)

OVER (ORDER BY t.fiscal_year, t.fiscal_month_desc) "Regr_sxx"

FROM sales s, times t

WHERE s.time_id = t.time_id

AND prod_id IN (270, 260)

AND t.fiscal_month_desc = '1998-02'

AND t.day_number_in_week IN (6,7)

ORDER BY t.day_number_in_month;

Oracle分析函數——分析函數案例

環比

　　環比就是現在的統計周期和上一個統計周期比較。例如2008年7月份與2008年6月份相比較稱其為環比。

　　環比發展速度是報告期水平與前一時期水平之比，表明現象逐期的發展速度。如計算一年內各月與前一個月對比，即2月比1月，3月比2月，4月比3月……12月比11月，說明逐月的發展程度。如分析抗擊"非典"期間某些經濟現象的發展趨勢，環比比同比更說明問題。

　　學過統計或者經濟知識的人都知道，統計指標按其具體內容、實際作用和表現形式可以分為總量指標、相對指標和平均指標。由于采用基期的不同，發展速度可分為同比發展速度、環比發展速度和定基發展速度。簡單地說，就是同比、環比與定基比，都可以用百分數或倍數表示。

　　定基比發展速度，也簡稱總速度，一般是指報告期水平與某一固定時期水平之比，表明這種現象在較長時期內總的發展速度。同比發展速度，一般指是指本期發展水平與上年同期發展水平對比，而達到的相對發展速度。環比發展速度，一般指是指報告期水平與前一時期水平之比，表明現象逐期的發展速度。

　　同比和環比，這兩者所反映的雖然都是變化速度，但由于采用基期的不同，其反映的內涵是完全不同的；同比與環比相比較，而不能拿同比與環比相比較；而對于同一個地方，考慮時間縱向上發展趨勢的反映，則往往要把同比與環比放在一起進行對照

同比

　　英文：year-on-year

　　同比就是今年第n月與去年第n月比；（環比就是今年第n月與第n-1月或第n+1月比）學過統計或者經濟知識的人都知道，統計指標按其具體內容、實際作用和表現形式可以分為總量指標、相對指標和平均指標。由于采用基期的不同，發展速度可分為同比發展速度、環比發展速度和定基發展速度。簡單地說，就是同比、環比與定基比，都可以用百分數或倍數表示。

　　同比發展速度主要是為了消除季節變動的影響，用以說明本期發展水平與去年同期發展水平對比而達到的相對發展速度。如，本期2月比去年2月，本期6月比去年6月等。其計算公式為：同比發展速度=本期發展水平/去年同期發展水平×100%。在實際工作中，經常使用這個指標，如某年、某季、某月與上年同期對比計算的發展速度，就是同比發展速度。

　　定基比發展速度也叫總速度。是報告期水平與某一固定時期水平之比，表明這種現象在較長時期內總的發展速度。如,"九五"期間各年水平都以1995年水平為基期進行對比，一年內各月水平均以上年12月水平為基期進行對比，就是定基發展速度。

定基比

　　另可參見同比、環比：

　　環比發展速度是報告期水平與前一時期水平之比，表明現象逐期的發展速度。如計算一年內各月與前一個月對比，即2月比1月，3月比2月，4月比3月……12月比11月，說明逐月的發展程度。如分析抗擊"非典"期間某些經濟現象的發展趨勢，環比比同比更說明問題

CREATE TABLE salaryByMonth

(

?employeeNo?varchar2(20),

?yearMonth varchar2(6),

?salary number

)

SELECT

?employeeno,

?yearmonth,

?salary,???

?MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY yearmonth) OVER (PARTITION BY employeeno) first_salary, --基比分析salary/first_salary??

?LAG(salary,1,0) OVER (PARTITION BY employeeno ORDER BY yearmonth) AS prev_sal,???--環比分析，與上個月份進行比較

?LAG(salary,12,0) OVER (PARTITION BY employeeno ORDER BY yearmonth) AS prev_12_sal --同比分析，與上個年度相同月份進行比較??

?FROM salaryByMonth

ORDER BY employeeno,yearmonth

--SQL常用的算法

SELECT *

?FROM salaryByMonth a

?WHERE (a.employeeno,a.salary) IN

?SELECT b.employeeno,max(salary)

???FROM salaryByMonth b

??GROUP BY b.employeeno

--用分析函數替代

SELECT distinct

?employeeno,

?MAX(salary) OVER (PARTITION BY employeeno) AS max_salary,

?FIRST_VALUE(yearmonth) OVER (PARTITION BY employeeno ORDER BY salary DESC) AS high_yearmonth????

?FROM salaryByMonth

Oracle分析函數——CUBE，ROLLUP

CUBE

功能描述：

注意：

ROLLUP

功能描述：

注意：

如果是ROLLUP(A, B, C)的話，GROUP BY順序

(A、B、C)

(A、B)

(A)

最后對全表進行GROUP BY操作。

如果是GROUP BY CUBE(A, B, C)，GROUP BY順序

(A、B、C)

(A、B)

(A、C)

(A)，

(B、C)

(B)

(C)，

最后對全表進行GROUP BY操作。

CREATE TABLE studentscore

(

?student_name varchar2(20),

?subjects varchar2(20),

?score number

)

INSERT INTO studentscore VALUES('WBQ','ENGLISH',90);

INSERT INTO studentscore VALUES('WBQ','MATHS',95);

INSERT INTO studentscore VALUES('WBQ','CHINESE',88);

INSERT INTO studentscore VALUES('CZH','ENGLISH',80);

INSERT INTO studentscore VALUES('CZH','MATHS',90);

INSERT INTO studentscore VALUES('CZH','HISTORY',92);

INSERT INTO studentscore VALUES('CB','POLITICS',70);

INSERT INTO studentscore VALUES('CB','HISTORY',75);

INSERT INTO studentscore VALUES('LDH','POLITICS',80);

INSERT INTO studentscore VALUES('LDH','CHINESE',90);

INSERT INTO studentscore VALUES('LDH','HISTORY',95);

SELECT

?student_name,

?subjects,

?sum(score)

?FROM studentscore

?GROUP BY CUBE(student_name,subjects);

等同于以下標準SQL

SELECT NULL,subjects,SUM(score)

?FROM studentscore

?GROUP BY subjects

?UNION

SELECT student_name,NULL,SUM(score)

?FROM studentscore

?GROUP BY student_name

?UNION

SELECT NULL,NULL,SUM(score)

?FROM studentscore

?UNION

SELECT student_name,subjects,SUM(score)

?FROM studentscore

?GROUP BY student_name,subjects

SELECT

?student_name,

?subjects,

?sum(score)

?FROM studentscore

?GROUP BY ROLLUP(student_name,subjects);

SELECT student_name,NULL,SUM(score)

?FROM studentscore

?GROUP BY student_name

?UNION

SELECT NULL,NULL,SUM(score)

?FROM studentscore

?UNION

SELECT student_name,subjects,SUM(score)

?FROM studentscore

?GROUP BY student_name,subjects

SELECT

?grouping(student_name),

?grouping(subjects),?

?student_name,

?subjects,

?sum(score)

?FROM studentscore

?GROUP BY CUBE(student_name,subjects)

?ORDER BY 1,2;

SELECT

?grouping(student_name),

?grouping(subjects),?

?student_name,

?subjects,

?sum(score)

?FROM studentscore

?GROUP BY ROLLUP(student_name,subjects)

?ORDER BY 1,2;

SELECT

?grouping_id(student_name,subjects),??

?student_name,

?subjects,

?sum(score)

?FROM studentscore

?GROUP BY CUBE(student_name,subjects)

?ORDER BY 1;

SELECT

?grouping_id(student_name,subjects),??

?student_name,

?subjects,

?sum(score)

?FROM studentscore

?GROUP BY ROLLUP(student_name,subjects)

?ORDER BY 1;

SELECT

?grouping(student_name),

?grouping(subjects),

?CASE WHEN grouping(student_name)=0 AND grouping(subjects)=1 THEN '學生成績合計'

??????WHEN grouping(student_name)=1 AND grouping(subjects)=0 THEN '課目成績合計'

??????WHEN grouping(student_name)=1 AND grouping(subjects)=1 THEN '總???????????????計'

??????ELSE ''

?END SUMMARY,

?student_name,

?subjects,

?sum(score)

?FROM studentscore

?GROUP BY CUBE(student_name,subjects)

?ORDER BY 1,2;

本文參考Oracle官方網站的相關文檔，并加了一些實用例子

使用正規表達式編寫更好的SQL

http://www.oracle.com/technology/global/cn/oramag/webcolumns/2003/techarticles/rischert_regexp_pt1.html

使用正則表達式編寫更好的SQL（續）

http://www.oracle.com/technology/global/cn/oramag/webcolumns/2003/techarticles/rischert_regexp_pt2.html

什么是正規表達式？

正規表達式由一個或多個字符型文字和/或元字符組成。在最簡單的格式下，正規表達式僅由字符文字組成，如正規表達式cat。它被讀作字母c，接著是字母a和t，這種模式匹配cat、location和catalog之類的字符串。元字符提供算法來確定Oracle如何處理組成一個正規表達式的字符。當您了解了各種元字符的含義時，您將體會到正規表達式用于查找和替換特定的文本數據是非常強大的。

驗證數據、識別重復關鍵字的出現、檢測不必要的空格，或分析字符串只是正規表達式的許多應用中的一部分。您可以用它們來驗證電話號碼、郵政編碼、電子郵件地址、社會安全號碼、IP地址、文件名和路徑名等的格式。此外，您可以查找如HTML標記、數字、日期之類的模式，或任意文本數據中符合任意模式的任何事物，并用其它的模式來替換它們。

用Oracle Database?10g使用正規表達式

您可以使用最新引進的Oracle SQL REGEXP_LIKE操作符和REGEXP_INSTR、REGEXP_SUBSTR以及REGEXP_REPLACE函數來發揮正規表達式的作用。您將體會到這個新的功能如何對LIKE操作符和INSTR、SUBSTR和REPLACE函數進行了補充。實際上，它們類似于已有的操作符，但現在增加了強大的模式匹配功能。被搜索的數據可以是簡單的字符串或是存儲在數據庫字符列中的大量文本。正規表達式讓您能夠以一種您以前從未想過的方式來搜索、替換和驗證數據，并提供高度的靈活性。

正規表達式的基本例子

在使用這個新功能之前，您需要了解一些元字符的含義。句號(.)匹配一個正規表達式中的任意字符（除了換行符）。例如，正規表達式a.b匹配的字符串中首先包含字母a，接著是其它任意單個字符（除了換行符），再接著是字母b。字符串axb、xaybx和abba都與之匹配，因為在字符串中隱藏了這種模式。如果您想要精確地匹配以a開頭和以b結尾的一條三個字母的字符串，則您必須對正規表達式進行定位。脫字符號(^)元字符指示一行的開始，而美元符號($)指示一行的結尾（參見表1）。因此，正規表達式^a.b$匹配字符串aab、abb或axb。將這種方式與LIKE2ù×÷·?提供的類似的模式匹配a_b相比較，其中(_)是單字符通配符。

默認情況下，一個正規表達式中的一個單獨的字符或字符列表只匹配一次。為了指示在一個正規表達式中多次出現的一個字符，您可以使用一個量詞，它也被稱為重復操作符。.如果您想要得到從字母a開始并以字母b結束的匹配模式，則您的正規表達式看起來像這樣：^a.*b$。*元字符重復前面的元字符(.)指示的匹配零次、一次或更多次。LIKE操作符的等價的模式是a%b，其中用百分號(%)來指示任意字符出現零次、一次或多次。

表2給出了重復操作符的完整列表。注意它包含了特殊的重復選項，它們實現了比現有的LIKE通配符更大的靈活性。如果您用圓括號括住一個表達式，這將有效地創建一個可以重復一定次數的子表達式。例如，正規表達式b(an)*a匹配ba、bana、banana、yourbananasplit等。

Oracle的正規表達式實施支持POSIX（可移植操作系統接口）字符類，參見表3中列出的內容。這意味著您要查找的字符類型可以非常特別。假設您要編寫一條僅查找非字母字符的LIKE條件—作為結果的WHERE子句可能不經意就會變得非常復雜。

POSIX字符類必須包含在一個由方括號([])指示的字符列表中。例如，正規表達式[[:lower:]]匹配一個小寫字母字符，而[[:lower:]]{5}匹配五個連續的小寫字母字符。

除POSIX字符類之外，您可以將單獨的字符放在一個字符列表中。例如，正規表達式^ab[cd]ef$匹配字符串abcef和abdef。必須選擇c或d。

除脫字符(^)和連字符(-)之外，字符列表中的大多數元字符被認為是文字。正規表達式看起來很復雜，這是因為一些元字符具有隨上下文環境而定的多重含義。^就是這樣一種元字符。如果您用它作為一個字符列表的第一個字符，它代表一個字符列表的非。因此，[^[:digit:]]查找包含了任意非數字字符的模式，而^[[:digit:]]查找以數字開始的匹配模式。連字符(-)指示一個范圍，正規表達式[a-m]匹配字母a到字母m之間的任意字母。但如果它是一個字符行中的第一個字符（如在[-afg]中），則它就代表連字符。

之前的一個例子介紹了使用圓括號來創建一個子表達式；它們允許您通過輸入更替元字符來輸入可更替的選項，這些元字符由豎線(|)分開。

例如，正規表達式t(a|e|i)n允許字母t和n之間的三種可能的字符更替。匹配模式包括如tan、ten、tin和Pakistan之類的字，但不包括teen、mountain或tune。作為另一種選擇，正規表達式t(a|e|i)n也可以表示為一個字符列表t[aei]n。表4匯總了這些元字符。雖然存在更多的元字符，但這個簡明的概述足夠用來理解這篇文章使用的正規表達式。

REGEXP_LIKE操作符

REGEXP_LIKE操作符向您介紹在Oracle數據庫中使用時的正規表達式功能。表5列出了REGEXP_LIKE的語法。

下面的SQL查詢的WHERE子句顯示了REGEXP_LIKE操作符，它在ZIP列中搜索滿足正規表達式[^[:digit:]]的模式。它將檢索ZIPCODE表中的那些ZIP列值包含了任意非數字字符的行。

SELECT zip

FROM zipcode

WHERE REGEXP_LIKE(zip, '[^[:digit:]]')

ZIP

-----

ab123

123xy

007ab

abcxy

這個正規表達式的例子僅由元字符組成，更具體來講是被冒號和方括號分隔的POSIX字符類digit。第二組方括號（如[^[:digit:]]中所示）包括了一個字符類列表。如前文所述，需要這樣做是因為您只可以將POSIX字符類用于構建一個字符列表。

REGEXP_INSTR函數

這個函數返回一個模式的起始位置，因此它的功能非常類似于INSTR函數。新的REGEXP_INSTR函數的語法在表6中給出。這兩個函數之間的主要區別是，REGEXP_INSTR讓您指定一種模式，而不是一個特定的搜索字符串；因而它提供了更多的功能。接下來的示例使用REGEXP_INSTR來返回字符串Joe Smith, 10045 Berry Lane, San Joseph, CA 91234中的五位郵政編碼模式的起始位置。如果正規表達式被寫為[[:digit:]]{5}，則您將得到門牌號的起始位置而不是郵政編碼的，因為10045是第一次出現五個連續數字。因此，您必須將表達式定位到該行的末尾，正如$元字符所示，該函數將顯示郵政編碼的起始位置，而不管門牌號的數字個數。

SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234',

'[[:digit:]]{5}$')

AS rx_instr

FROM dual

RX_INSTR

----------

編寫更復雜的模式

讓我們在前一個例子的郵政編碼模式上展開，以便包含一個可選的四位數字模式。您的模式現在可能看起來像這樣：[[:digit:]]{5}(-[[:digit:]]{4})?$。如果您的源字符串以5位郵政編碼或5位+ 4位郵政編碼的格式結束，則您將能夠顯示該模式的起始位置。

SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234-1234',

' [[:digit:]]{5}(-[[:digit:]]{4})?$')

AS starts_at

FROM dual

STARTS_AT

----------

在這個示例中，括弧里的子表達式(-[[:digit:]]{4})將按?重復操作符的指示重復零次或一次。此外，企圖用傳統的SQL函數來實現相同的結果甚至對SQL專家也是一個挑戰。為了更好地說明這個正規表達式示例的不同組成部分，表7包含了一個對單個文字和元字符的描述。

REGEXP_SUBSTR函數

SUBSTR函數的REGEXP_SUBSTR函數用來提取一個字符串的一部分。表8顯示了這個新函數的語法。在下面的示例中，匹配模式[^,]*的字符串將被返回。該正規表達式搜索其后緊跟著空格的一個逗號；然后按[^,]*的指示搜索零個或更多個不是逗號的字符，最后查找另一個逗號。這種模式看起來有點像一個用逗號分隔的值字符串。

SELECT REGEXP_SUBSTR('first field, second field , third field',

', [^,]*,')

FROM dual

REGEXP_SUBSTR('FIR

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

, second field ,

REGEXP_REPLACE函數

讓我們首先看一下傳統的REPLACE SQL函數，它把一個字符串用另一個字符串來替換。假設您的數據在正文中有不必要的空格，您希望用單個空格來替換它們。利用REPLACE函數，您需要準確地列出您要替換多少個空格。然而，多余空格的數目在正文的各處可能不是相同的。下面的示例在Joe和Smith之間有三個空格。REPLACE函數的參數指定要用一個空格來替換兩個空格。在這種情況下，結果在原來的字符串的Joe和Smith之間留下了一個額外的空格。

SELECT REPLACE('Joe Smith',' ', ' ')

AS replace

FROM dual

REPLACE

---------

Joe Smith

REGEXP_REPLACE函數把替換功能向前推進了一步，其語法在表9中列出。以下查詢用單個空格替換了任意兩個或更多的空格。( )子表達式包含了單個空格，它可以按{2,}的指示重復兩次或更多次。

SELECT REGEXP_REPLACE('Joe Smith',

'( ){2,}', ' ')

AS RX_REPLACE

FROM dual

RX_REPLACE

----------

Joe Smith

'^'匹配輸入字符串的開始位置，在方括號表達式中使用，此時它表示不接受該字符集合。

'$'匹配輸入字符串的結尾位置。如果設置了RegExp對象的Multiline屬性，則$也匹配'n'或'r'。

'.'匹配除換行符n之外的任何單字符。

'?'匹配前面的子表達式零次或一次。

'*'匹配前面的子表達式零次或多次。

'+'匹配前面的子表達式一次或多次。

'( )'標記一個子表達式的開始和結束位置。

'[]'標記一個中括號表達式。

'{m,n}'一個精確地出現次數范圍，m=<出現次數<=n，'{m}'表示出現m次，'{m,}'表示至少出現m次。

'|'指明兩項之間的一個選擇。例子'^([a-z]+|[0-9]+)$'表示所有小寫字母或數字組合成的字符串。

num匹配num，其中num是一個正整數。對所獲取的匹配的引用。

create table TEST

(

?MC VARCHAR2(60)

);

insert into TEST (MC) values ('b0');

insert into TEST (MC) values ('0b');

insert into TEST (MC) values ('1234-233-3223-2323');

insert into TEST (MC) values ('123-45-5678');

insert into TEST (MC) values ('123-56-1234567890');

insert into TEST (MC) values ('123456789');

insert into TEST (MC) values ('idadfa');

insert into TEST (MC) values ('[a');

insert into TEST (MC) values ('[i');

insert into TEST (MC) values ('[a-c]');

insert into TEST (MC) values ('[a-c]a');

insert into TEST (MC) values ('a[a-c]');

insert into TEST (MC) values ('[bdd-a]');

insert into TEST (MC) values ('[adddddd');

insert into TEST (MC) values ('[eeeea]');

insert into TEST (MC) values ('[eeeee]');

insert into TEST (MC) values ('[b]');

insert into TEST (MC) values ('112233445566778899');

insert into TEST (MC) values ('22113344 5566778899');

insert into TEST (MC) values ('991122334455667788');

insert into TEST (MC) values ('aabbccddee');

insert into TEST (MC) values ('bbaaaccddee');

insert into TEST (MC) values ('eeaabbccdd');

insert into TEST (MC) values ('ab123');

insert into TEST (MC) values ('123xy');

insert into TEST (MC) values ('007ab');

insert into TEST (MC) values ('abcxy');

insert into TEST (MC) values ('The final test is is is how to find duplicate words.');

commit;

select 1,'^[:digit:]',mc from test where regexp_like(mc,'^[:digit:]')?--以':digit'中的任何一個字符開頭的字符串

union

select 2,'[^[:digit:]]',mc from test where regexp_like(mc,'[^[:digit:]]') --任何含有非數字的字符列表

union

select 3,'^[[:digit:]]',mc from test where regexp_like(mc,'^[[:digit:]]') --數字開頭

union

select 4,'^[^[:digit:]]',mc from test where regexp_like(mc,'^[^[:digit:]]') --包含任何非數字開頭的

union

select 5,'[[:digit:]]',mc from test where regexp_like(mc,'[[:digit:]]') --任何含有數字的字符列表

create table email

( email varchar2(100)

)

insert into email values('windboy@vip.sina.com');

insert into email values('windboy@sina.com');

insert into email values('window2007@vip.sina.com');

insert into email values('21com@sina.com');

insert into email values('windboy@163.com');

insert into email values('test@mail.vip.sina.com');

insert into email values('test.mail.vip.sina.com');

insert into email values('test');

commit;

select * from email where REGEXP_LIKE(email,'^[[:alnum:]]+@([[:alnum:]]).+');

關于正則表達式的后向引用，暫時還是沒法理解

select 0,REGEXP_REPLACE('Ellen Hildi Smith','(.*) (.*) (.*)', '\11\22\33') from dual

union

select 1,REGEXP_REPLACE('Ellen Hildi Smith','(.*)(.*)(.*)', '\11\22\33') from dual?--2，3

union

select 2,REGEXP_REPLACE('Ellen Hildi Smith','(.*)(.*) (.*)', '\11\22\33') from dual?--2

union

select 3,REGEXP_REPLACE('Ellen Hildi Smith','(.*) (.*)(.*)', '\11\22\33') from dual?--3

union

select 4,REGEXP_REPLACE('EllenHildi Smith','(.*) (.*) (.*)', '\11\22\33') from dual

union

select 5,REGEXP_REPLACE('EllenHildi Smith','(.*)(.*)(.*)', '\11\22\33') from dual?--2，3

union

select 6,REGEXP_REPLACE('EllenHildi Smith','(.*)(.*) (.*)', '\11\22\33') from dual?--2

union

select 7,REGEXP_REPLACE('EllenHildi Smith','(.*) (.*)(.*)', '\11\22\33') from dual?--3

union

select 8,REGEXP_REPLACE('Ellen HildiSmith','(.*) (.*) (.*)', '\11\22\33') from dual

union

select 9,REGEXP_REPLACE('Ellen HildiSmith','(.*)(.*)(.*)', '\11\22\33') from dual?--2，3

union

select 10,REGEXP_REPLACE('Ellen HildiSmith','(.*)(.*) (.*)', '\11\22\33') from dual?--2

union

select 11,REGEXP_REPLACE('Ellen HildiSmith','(.*) (.*)(.*)', '\11\22\33') from dual?--3

-- 'u', 'U' -返回全是大寫的字符串

-- 'l', 'L' -返回全是小寫的字符串

-- 'a', 'A' -返回大小寫結合的字符串

-- 'x', 'X' -返回全是大寫和數字的字符串

-- 'p', 'P' -返回鍵盤上出現字符的隨機組合

SELECT

?trunc(dbms_random.value(1,101)),

?DBMS_RANDOM.string('~',5),

?DBMS_RANDOM.string('l',5),

?DBMS_RANDOM.string('L',5),?

?DBMS_RANDOM.string('a',5),

?DBMS_RANDOM.string('A',5),

?DBMS_RANDOM.string('u',5),

?DBMS_RANDOM.string('U',5),?

?DBMS_RANDOM.string('x',5),

?DBMS_RANDOM.string('X',5),

?DBMS_RANDOM.string('p',5),

?DBMS_RANDOM.string('P',5)???

from

(

SELECT level,ROWNUM rn

?FROM DUAL

CONNECT BY ROWNUM<=1001

)

轉載網站：http://blog.csdn.net/fujiakai/article/details/51066389

posted on 2017-10-13 09:13 瀚城老爺子閱讀(...) 評論(...) 編輯收藏

轉載于:https://www.cnblogs.com/hclyz/p/7659419.html

總結

以上是生活随笔為你收集整理的Oracle所有分析函数转的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。