學習Oracle分析函數(Analytic Functions)

來源:互聯網
上載者:User

標籤:oracle 分析函數 analytic functions

Oracle提供了一些功能很強大的分析函數,使用這些函數可以完成可能需要預存程序來實現的需求。

分析Function Compute基於一組資料行的彙總值,它們不同於彙總函式的是,它們為每一組返回多行結果。分析函數是除ORDER BY子句之外,在查詢語句中最後執行的。所有的join和所有的WHERE ,GROUP BY 和HAVING子句都在分析函數之前執行。所以分析函數只能出現在select或ORDER BY子句中。

為11.2版本官方文檔中給出的文法:

650) this.width=650;" src="https://s5.51cto.com/wyfs02/M01/92/23/wKiom1j8YE-zfXLsAAGOKuDzgbs859.png" title="1.png" alt="wKiom1j8YE-zfXLsAAGOKuDzgbs859.png" />下面簡單介紹一下各個部分:

analytic_function

指定分析函數的名字,後面列出了所有的分析函數

arguments

分析函數可以有0到3個參數。參數可以是任何數實值型別或可以隱式轉換為數實值型別的其他非數實值型別。

analytic_clause

用OVER analytic_clause表明函數操作的是一個查詢結果集。如果想過濾基於分析函數的查詢結果,需要使用嵌套子查詢。

query_partition_clause

用PARTITION BY子句來把查詢結果集基於一個或多個value_expr分組。如果省略,分析函數把所有行當作一組。

order_by_clause

用order_by_claus指定在一組中資料如何排序。

ASC(default)|DESC

NULLS FIRST(default in DESC)|NULLS LAST(default in ASC)

windowing_clause

部分分析函數允許使用windowing_clause子句。

只有當指定了order_by_clause後才能指定這個子句。

ROWS指定使用物理行的window

RANGE指定使用邏輯位移的window

詳細資料請參考:http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions004.htm#i81407

下面為所有的分析函數,帶*號的函數允許使用windowing_clause:

AVG *
CORR *
COUNT *
COVAR_POP *
COVAR_SAMP *
CUME_DIST
DENSE_RANK
FIRST
FIRST_VALUE *
LAG
LAST
LAST_VALUE *
LEAD
LISTAGG
MAX *
MEDIAN
MIN *
NTH_VALUE *
NTILE
PERCENT_RANK
PERCENTILE_CONT
PERCENTILE_DISC
RANK
RATIO_TO_REPORT
REGR_ (Linear Regression) Functions *
ROW_NUMBER
STDDEV *
STDDEV_POP *
STDDEV_SAMP *
SUM *
VAR_POP *
VAR_SAMP *
VARIANCE *

以AVG為例介紹分析函數的使用:

AVG也是一個彙總函式:

[email protected]>select avg(sal) from emp;  AVG(SAL)----------2073.21429

作為分析函數的例子:

eg1:單獨使用

[email protected]>select deptno,ename,hiredate,sal,avg(sal) over() avg from emp;    DEPTNO ENAME  HIREDATE     SALAVG---------- ------------------------------ ------------------- ---------- ----------20 SMITH  1980-12-17 00:00:00     800 2073.2142930 ALLEN  1981-02-20 00:00:00    1600 2073.2142930 WARD   1981-02-22 00:00:00    1250 2073.2142920 JONES  1981-04-02 00:00:00    2975 2073.2142930 MARTIN  1981-09-28 00:00:00    1250 2073.2142930 BLAKE  1981-05-01 00:00:00    2850 2073.2142910 CLARK  1981-06-09 00:00:00    2450 2073.2142920 SCOTT  1987-04-19 00:00:00    3000 2073.2142910 KING   1981-11-17 00:00:00    5000 2073.2142930 TURNER  1981-09-08 00:00:00    1500 2073.2142920 ADAMS  1987-05-23 00:00:00    1100 2073.2142930 JAMES  1981-12-03 00:00:00     950 2073.2142920 FORD   1981-12-03 00:00:00    3000 2073.2142910 MILLER  1982-01-23 00:00:00    1300 2073.21429

從輸出可以看出Function Compute出了整體的平均值,並輸出到每一行

eg2:使用query_partition_clause

[email protected]>select deptno,ename,hiredate,sal,avg(sal) over(partition by deptno) avg from emp;    DEPTNO ENAME  HIREDATE     SALAVG---------- ------------------------------ ------------------- ---------- ----------10 CLARK  1981-06-09 00:00:00    2450 2916.6666710 KING   1981-11-17 00:00:00    5000 2916.6666710 MILLER  1982-01-23 00:00:00    1300 2916.6666720 JONES  1981-04-02 00:00:00    2975       217520 FORD   1981-12-03 00:00:00    3000       217520 ADAMS  1987-05-23 00:00:00    1100       217520 SMITH  1980-12-17 00:00:00     800       217520 SCOTT  1987-04-19 00:00:00    3000       217530 WARD   1981-02-22 00:00:00    1250 1566.6666730 TURNER  1981-09-08 00:00:00    1500 1566.6666730 ALLEN  1981-02-20 00:00:00    1600 1566.6666730 JAMES  1981-12-03 00:00:00     950 1566.6666730 BLAKE  1981-05-01 00:00:00    2850 1566.6666730 MARTIN  1981-09-28 00:00:00    1250 1566.66667[email protected]>select deptno,avg(sal) from emp group by deptno;    DEPTNO   AVG(SAL)---------- ----------30 1566.6666720 217510 2916.66667

從輸出可以看出,AVG計算出了每個部門的平均值,並輸出到對應的行。

eg3:使用order_by_clause

[email protected]>select deptno,ename,hiredate,sal,avg(sal) over(partition by deptno order by sal) avg from emp;    DEPTNO ENAME  HIREDATE     SALAVG---------- ------------------------------ ------------------- ---------- ----------10 MILLER  1982-01-23 00:00:00    1300       130010 CLARK  1981-06-09 00:00:00    2450       187510 KING   1981-11-17 00:00:00    5000 2916.6666720 SMITH  1980-12-17 00:00:00     80080020 ADAMS  1987-05-23 00:00:00    110095020 JONES  1981-04-02 00:00:00    2975       162520 SCOTT  1987-04-19 00:00:00    3000       217520 FORD   1981-12-03 00:00:00    3000       217530 JAMES  1981-12-03 00:00:00     95095030 MARTIN  1981-09-28 00:00:00    1250       115030 WARD   1981-02-22 00:00:00    1250       115030 TURNER  1981-09-08 00:00:00    1500     1237.530 ALLEN  1981-02-20 00:00:00    1600       131030 BLAKE  1981-05-01 00:00:00    2850 1566.66667

從輸出結果可以看出,每個部門的行都按sal做了升序排序。

eg4:使用windowing_clause

[email protected]>select deptno,ename,hiredate,sal,avg(sal) over(partition by deptno order by sal rows BETWEEN 1 PRECEDING AND 1 FOLLOWING) avg from emp;    DEPTNO ENAME  HIREDATE     SALAVG---------- ------------------------------ ------------------- ---------- ----------10 MILLER  1982-01-23 00:00:00    1300       187510 CLARK  1981-06-09 00:00:00    2450 2916.6666710 KING   1981-11-17 00:00:00    5000       372520 SMITH  1980-12-17 00:00:00     80095020 ADAMS  1987-05-23 00:00:00    1100       162520 JONES  1981-04-02 00:00:00    2975 2358.3333320 SCOTT  1987-04-19 00:00:00    3000 2991.6666720 FORD   1981-12-03 00:00:00    3000       300030 JAMES  1981-12-03 00:00:00     950       110030 MARTIN  1981-09-28 00:00:00    1250       115030 WARD   1981-02-22 00:00:00    1250 1333.3333330 TURNER  1981-09-08 00:00:00    1500       145030 ALLEN  1981-02-20 00:00:00    1600 1983.3333330 BLAKE  1981-05-01 00:00:00    2850       2225

從輸出的結果可以看出,分析函數對每一組中的每一行的輸出結果是把它自己與它的上一行和下一行這三行求平均值。

分析函數太多,這裡就不一一介紹功能了,有興趣的同學可以點開上面的串連,去查看對應的功能。

本文出自 “DBA Fighting!” 部落格,請務必保留此出處http://hbxztc.blog.51cto.com/1587495/1918614

學習Oracle分析函數(Analytic Functions)

相關文章

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.