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

编程问答

Oracle分析函数——函数列表

發(fā)布時間：2024/9/5 编程问答 31 豆豆

生活随笔收集整理的這篇文章主要介紹了 Oracle分析函数——函数列表小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.

--------------聚合函數(shù)
SUM ：該函數(shù)計算組中表達式的累積和

MIN ：在一個組中的數(shù)據(jù)窗口中查找表達式的最小值

MAX ：在一個組中的數(shù)據(jù)窗口中查找表達式的最大值

AVG ：用于計算一個組和數(shù)據(jù)窗口內(nèi)表達式的平均值。

COUNT ：對一組內(nèi)發(fā)生的事情進行累積計數(shù)

----------------開窗函數(shù)

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

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

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

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

FIRST_VALUE ：返回組中數(shù)據(jù)窗口的第一個值

LAST_VALUE ：返回組中數(shù)據(jù)窗口的最后一個值。

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

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

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

----------------數(shù)據(jù)分析函數(shù)

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

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

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

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

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

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

COVAR_POP ：返回一對表達式的總體協(xié)方差

COVAR_SAMP：返回一對表達式的樣本協(xié)方差

CORR ：返回一對表達式的相關系數(shù)

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

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

PERCENT_RANK：和CUME_DIST（累積分配）函數(shù)類似

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

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

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

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

---------------統(tǒng)計求和函數(shù)

CUBE :按照OLAP的CUBE方式進行數(shù)據(jù)統(tǒng)計,即各個維度均需統(tǒng)計

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分析函數(shù)實際上操作對象是查詢出的數(shù)據(jù)集，也就是說不需二次查詢數(shù)據(jù)庫，實際上就是oracle實現(xiàn)了一些我們
自身需要編碼實現(xiàn)的統(tǒng)計功能，對于簡化開發(fā)工作量有很大的幫助，特別在開發(fā)第三方報表軟件時是非常有幫助的。
Oracle從8.1.6開始提供分析函數(shù)。
oracle分析函數(shù)的語法：

function_name(arg1,arg2,...) over ( )
說明：

partition-clause 數(shù)據(jù)記錄集分組

order-by-clause 數(shù)據(jù)記錄集排序

windowing clause 功能非常強大、比較復雜，定義分析函數(shù)在操作行的集合。有三種開窗方式: range、row、specifying。
--Partition by，按相應的值(manager_id)進行分組統(tǒng)計

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)進行排序并累計統(tǒng)計

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)進行累計統(tǒng)計

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)進行累計統(tǒng)計
--該平均值由當前員工和與之具有相同經(jīng)理的前一個和后兩個三者的平均數(shù)得來

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)進行累計統(tǒng)計
--該平均值由當前員工和與之具有相同經(jīng)理,并且雇用時間在該員工時間之前的50天以內(nèi)和在該員工之后的150天之內(nèi)員工的薪水的平均值
--range為取值范圍，估計只有數(shù)字和日期能夠進行取值了

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)進行累計統(tǒng)計
--該平均值由當前員工和與之具有相同經(jīng)理的平均值
--每行對應的數(shù)據(jù)窗口是從第一行到最后一行

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分析函數(shù)——SUM,AVG,MIN,MAX,COUNT

SUM
功能描述：該函數(shù)計算組中表達式的累積和。
SAMPLE：下例計算同一經(jīng)理下員工的薪水累積值

MIN
功能描述：在一個組中的數(shù)據(jù)窗口中查找表達式的最小值。
SAMPLE：下面例子中dept_min返回當前行所在部門的最小薪水值

MAX
功能描述：在一個組中的數(shù)據(jù)窗口中查找表達式的最大值。
SAMPLE：下面例子中dept_max返回當前行所在部門的最大薪水值

AVG
功能描述：用于計算一個組和數(shù)據(jù)窗口內(nèi)表達式的平均值。
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
功能描述：對一組內(nèi)發(fā)生的事情進行累積計數(shù)，如果指定*或一些非空常數(shù)，count將對所有行計數(shù)，如果指定一個表達式，
count返回表達式非空賦值的計數(shù)，當有相同值出現(xiàn)時，這些相等的值都會被納入被計算的值；可以使用DISTINCT來記錄
去掉一組中完全相同的數(shù)據(jù)后出現(xiàn)的行數(shù)。
SAMPLE：下面例子中計算每個員工在按薪水排序中當前行附近薪水在[n-50,n+150]之間的行數(shù)，n表示當前行的薪水
例如，Philtanker的薪水2200，排在他之前的行中薪水大于等于2200-50的有1行，排在他之后的行中薪水小于等于2200＋150的行沒有，
所以count計數(shù)值cnt3為2（包括自己當前行）；cnt2值相當于小于等于當前行的SALARY值的所有行數(shù)

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分析函數(shù)——函數(shù)RANK,DENSE_RANK,FIRST,LAST…

RANK
功能描述：根據(jù)ORDER BY子句中表達式的值，從查詢返回的每一行，計算它們與其它行的相對位置。組內(nèi)的數(shù)據(jù)按ORDER BY子句排序，
然后給每一行賦一個號，從而形成一個序列，該序列從1開始，往后累加。每次ORDER BY表達式的值發(fā)生變化時，該序列也隨之增加。
有同樣值的行得到同樣的數(shù)字序號（認為null時相等的）。然而，如果兩行的確得到同樣的排序，則序數(shù)將隨后跳躍。若兩行序數(shù)為1，
則沒有序數(shù)2，序列將給組中的下一行分配值3，DENSE_RANK則沒有任何跳躍。
SAMPLE：下例中計算每個員工按部門分區(qū)再按薪水排序，依次出現(xiàn)的序列號（注意與DENSE_RANK函數(shù)的區(qū)別）

DENSE_RANK

功能描述：根據(jù)ORDER BY子句中表達式的值，從查詢返回的每一行，計算它們與其它行的相對位置。組內(nèi)的數(shù)據(jù)按ORDER BY子句排序，然后給每一行賦一個號，從而形成一個序列，該序列從1開始，往后累加。每次ORDER BY表達式的值發(fā)生變化時，該序列也隨之增加。有同樣值的行得到同樣的數(shù)字序號（認為null時相等的）。密集的序列返回的時沒有間隔的數(shù)
SAMPLE：下例中計算每個員工按部門分區(qū)再按薪水排序，依次出現(xiàn)的序列號（注意與RANK函數(shù)的區(qū)別）

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分析函數(shù)——統(tǒng)計分析函數(shù)

方差和標準差：
樣本中各數(shù)據(jù)與樣本平均數(shù)的差的平方和的平均數(shù)叫做樣本方差；樣本方差的算術平方根叫做樣本標準差。
樣本方差和樣本標準差都是衡量一個樣本波動大小的量，樣本方差或樣本標準差越大，樣本數(shù)據(jù)的波動就越大。
數(shù)學上一般用E{[X-E(X)]^2}來度量隨機變量X與其均值E(X)即期望的偏離程度，稱為X的方差。

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

StdDev返回expr的樣本標準偏差。它可用作聚集和分析函數(shù)。它與stddev_samp的不同之處在于，當計算的輸入數(shù)據(jù)只有一行時，stddev返回0，而stddev_samp返回null。
Oracle數(shù)據(jù)庫中，標準偏差計算結果與variance用作集聚函數(shù)計算結果的平方根相等。該函數(shù)參數(shù)可取任何數(shù)字類型或是任何能隱式轉換成數(shù)字類型的非數(shù)字類型。

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

STDDEV_SAMP
功能描述：該函數(shù)計算累積樣本標準偏離，并返回總體變量的平方根，其返回值與VAR_POP函數(shù)的平方根相同。（Standard Deviation－Sample）
SAMPLE：
它與stddev_samp的不同之處在于，當計算的輸入數(shù)據(jù)只有一行時，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的區(qū)別

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）該函數(shù)返回非空集合的總體變量（忽略null），VAR_POP進行如下計算：
(SUM(expr2) - SUM(expr)2 / COUNT(expr)) / COUNT(expr)

VAR_SAMP
功能描述：（Variance Sample）該函數(shù)返回非空集合的樣本變量（忽略null），VAR_POP進行如下計算：
(SUM(exprexpr)-SUM(expr)SUM(expr)/COUNT(expr))/(COUNT(expr)-1)
SAMPLE：

VARIANCE
功能描述：該函數(shù)返回表達式的變量，Oracle計算該變量如下：
如果表達式中行數(shù)為1，則返回0
如果表達式中行數(shù)大于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

協(xié)方差分析是建立在方差分析和回歸分析基礎之上的一種統(tǒng)計分析方法。
方差分析是從質量因子的角度探討因素不同水平對實驗指標影響的差異。一般說來，質量因子是可以人為控制的。
回歸分析是從數(shù)量因子的角度出發(fā)，通過建立回歸方程來研究實驗指標與一個(或幾個)因子之間的數(shù)量關系。
但大多數(shù)情況下，數(shù)量因子是不可以人為加以控制的。

兩個不同參數(shù)之間的方差就是協(xié)方差
若兩個隨機變量X和Y相互獨立，則E[(X-E(X))(Y-E(Y))]=0，因而若上述數(shù)學期望不為零，則X和Y必不是相互獨立的，亦即它們之間存在著一定的關系
定義
E[(X-E(X))(Y-E(Y))]稱為隨機變量X和Y的協(xié)方差，記作COV(X，Y)，即COV(X，Y)=E[(X-E(X))(Y-E(Y))]。

COVAR_POP
功能描述：返回一對表達式的總體協(xié)方差。
SAMPLE：

COVAR_SAMP
功能描述：返回一對表達式的樣本協(xié)方差
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
功能描述：返回一對表達式的相關系數(shù)，它是如下的縮寫：
COVAR_POP(expr1,expr2)/STDDEV_POP(expr1)*STDDEV_POP(expr2))
從統(tǒng)計上講，相關性是變量之間關聯(lián)的強度，變量之間的關聯(lián)意味著在某種程度
上一個變量的值可由其它的值進行預測。通過返回一個-1~1之間的一個數(shù),相關
系數(shù)給出了關聯(lián)的強度，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分析函數(shù)——統(tǒng)計分析函數(shù)

方差和標準差：

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

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

方差是標準差的平方

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

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

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

STDDEV

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

SAMPLE：

STDDEV_SAMP

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

SAMPLE：

它與stddev_samp的不同之處在于，當計算的輸入數(shù)據(jù)只有一行時，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的區(qū)別

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）該函數(shù)返回非空集合的總體變量（忽略null），VAR_POP進行如下計算：

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

VAR_SAMP

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

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

SAMPLE：

VARIANCE

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

如果表達式中行數(shù)為1，則返回0

如果表達式中行數(shù)大于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

協(xié)方差分析是建立在方差分析和回歸分析基礎之上的一種統(tǒng)計分析方法。

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

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

兩個不同參數(shù)之間的方差就是協(xié)方差

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

定義

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

COVAR_POP

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

SAMPLE：

COVAR_SAMP

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

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

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

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

從統(tǒng)計上講，相關性是變量之間關聯(lián)的強度，變量之間的關聯(lián)意味著在某種程度

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

系數(shù)給出了關聯(lián)的強度，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分析函數(shù)——統(tǒng)計分析函數(shù)

方差和標準差：

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

方差是標準差的平方

STDDEV

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

SAMPLE：

STDDEV_SAMP

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

SAMPLE：

它與stddev_samp的不同之處在于，當計算的輸入數(shù)據(jù)只有一行時，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的區(qū)別

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）該函數(shù)返回非空集合的總體變量（忽略null），VAR_POP進行如下計算：

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

VAR_SAMP

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

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

SAMPLE：

VARIANCE

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

如果表達式中行數(shù)為1，則返回0

如果表達式中行數(shù)大于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

協(xié)方差分析是建立在方差分析和回歸分析基礎之上的一種統(tǒng)計分析方法。

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

兩個不同參數(shù)之間的方差就是協(xié)方差

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

定義

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

COVAR_POP

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

SAMPLE：

COVAR_SAMP

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

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

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

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

從統(tǒng)計上講，相關性是變量之間關聯(lián)的強度，變量之間的關聯(lián)意味著在某種程度

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

系數(shù)給出了關聯(lián)的強度，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分析函數(shù)——統(tǒng)計分析函數(shù)

方差和標準差：

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

方差是標準差的平方

STDDEV

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

SAMPLE：

STDDEV_SAMP

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

SAMPLE：

它與stddev_samp的不同之處在于，當計算的輸入數(shù)據(jù)只有一行時，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的區(qū)別

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）該函數(shù)返回非空集合的總體變量（忽略null），VAR_POP進行如下計算：

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

VAR_SAMP

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

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

SAMPLE：

VARIANCE

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

如果表達式中行數(shù)為1，則返回0

如果表達式中行數(shù)大于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

協(xié)方差分析是建立在方差分析和回歸分析基礎之上的一種統(tǒng)計分析方法。

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

兩個不同參數(shù)之間的方差就是協(xié)方差

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

定義

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

COVAR_POP

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

SAMPLE：

COVAR_SAMP

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

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

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

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

從統(tǒng)計上講，相關性是變量之間關聯(lián)的強度，變量之間的關聯(lián)意味著在某種程度

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

系數(shù)給出了關聯(lián)的強度，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分析函數(shù)——數(shù)據(jù)分布函數(shù)及報表函數(shù)

CUME_DIST

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

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

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，則給組中的每一行分配一個數(shù)（從1到4），如果組中有20行，則給前5行分配1，給下5行分配2等等。如果組的基數(shù)不能由表達式值平均分開，則對這些行進行分配時，組中就沒有任何percentile的行數(shù)比其它percentile的行數(shù)超過一行，最低的percentile是那些擁有額外行的percentile。例如，若表達式=4，行數(shù)=21，則percentile=1的有5行，percentile=2的有5行等等。

SAMPLE：下例中把6行數(shù)據(jù)分為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（累積分配）函數(shù)類似，對于一個組中給定的行來說，在計算那行的序號時，先減1，然后除以n-1（n為組中所有的行數(shù)）。該函數(shù)總是返回0～1（包括1）之間的數(shù)。

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

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

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

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

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

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

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

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)

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

算法太復雜，看不懂了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, --數(shù)據(jù)分布百分比

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

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

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

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

FROM employees

RATIO_TO_REPORT

功能描述：該函數(shù)計算expression/(sum(expression))的值，它給出相對于總數(shù)的百分比，即當前行對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

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

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

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

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

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

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

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年最后三個星期中兩種產(chǎn)品（260和270）在周末的銷售量中已開發(fā)票數(shù)量和總數(shù)量的累積斜率和回歸線的截距

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月每天的累積交易數(shù)量

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年每月銷售量中已開發(fā)票數(shù)量和總數(shù)量的累積回歸線決定系數(shù)

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月最后兩周產(chǎn)品260的銷售量中已開發(fā)票數(shù)量和總數(shù)量的累積平均值

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：下例計算產(chǎn)品260和270在1998年2月周末銷售量中已開發(fā)票數(shù)量和總數(shù)量的累積REGR_SXY, REGR_SXX, and REGR_SYY統(tǒng)計值

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分析函數(shù)——分析函數(shù)案例

環(huán)比

　　環(huán)比就是現(xiàn)在的統(tǒng)計周期和上一個統(tǒng)計周期比較。例如2008年7月份與2008年6月份相比較稱其為環(huán)比。

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

　　學過統(tǒng)計或者經(jīng)濟知識的人都知道，統(tǒng)計指標按其具體內(nèi)容、實際作用和表現(xiàn)形式可以分為總量指標、相對指標和平均指標。由于采用基期的不同，發(fā)展速度可分為同比發(fā)展速度、環(huán)比發(fā)展速度和定基發(fā)展速度。簡單地說，就是同比、環(huán)比與定基比，都可以用百分數(shù)或倍數(shù)表示。

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

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

同比

　　英文：year-on-year

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

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

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

定基比

　　另可參見同比、環(huán)比：

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, --環(huán)比分析，與上個月份進行比較

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

)

--用分析函數(shù)替代

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分析函數(shù)——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官方網(wǎng)站的相關文檔，并加了一些實用例子

使用正規(guī)表達式編寫更好的SQL

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

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

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

什么是正規(guī)表達式？

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

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

用Oracle Database 10g使用正規(guī)表達式

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

正規(guī)表達式的基本例子

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

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

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

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

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

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

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

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

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

REGEXP_LIKE操作符

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

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

SELECT zip

FROM zipcode

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

ZIP

ab123

123xy

007ab

abcxy

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

REGEXP_INSTR函數(shù)

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

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

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

AS rx_instr

FROM dual

RX_INSTR

編寫更復雜的模式

讓我們在前一個例子的郵政編碼模式上展開，以便包含一個可選的四位數(shù)字模式。您的模式現(xiàn)在可能看起來像這樣：[[: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})將按?重復操作符的指示重復零次或一次。此外，企圖用傳統(tǒng)的SQL函數(shù)來實現(xiàn)相同的結果甚至對SQL專家也是一個挑戰(zhàn)。為了更好地說明這個正規(guī)表達式示例的不同組成部分，表7包含了一個對單個文字和元字符的描述。

REGEXP_SUBSTR函數(shù)

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

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

', [^,]*,')

FROM dual

REGEXP_SUBSTR('FIR

, second field ,

REGEXP_REPLACE函數(shù)

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

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

AS replace

FROM dual

REPLACE

Joe Smith

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

SELECT REGEXP_REPLACE('Joe Smith',

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

AS RX_REPLACE

FROM dual

RX_REPLACE

Joe Smith

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

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

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

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

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

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

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

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

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

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

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

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:]]') --任何含有非數(shù)字的字符列表

union

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

union

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

union

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

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' -返回全是大寫和數(shù)字的字符串

-- 'p', 'P' -返回鍵盤上出現(xiàn)字符的隨機組合

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

)

轉載于:https://www.cnblogs.com/theonewu/p/9446882.html

總結

以上是生活随笔為你收集整理的Oracle分析函数——函数列表的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。

上一篇： jQuery中的text()、html(
下一篇：解决U盘无法拷贝大文件问题