總結歸納：110個Oracle常用函數的總結

　　/*===================================================================
 *Author:
 *Date:  //
 *Description:ORACLE Function
 *==================================================================*/
 
SQL中的單記錄函數
ASCII
返回與指定的字符對應的十進制數;
SQL> select ascii(A) Aascii(a) aascii() zeroascii( ) space from dual;

　　A A ZERO SPACE

　　CHR
給出整數返回對應的字符;
SQL> select chr() zhaochr() chr from dual;

　　ZH C

趙 A

　　CONCAT
連接兩個字符串;
SQL> select concat()||轉 高乾競電話 from dual;

　　高乾競電話

轉

　　INITCAP
返回字符串並將字符串的第一個字母變為大寫;
SQL> select initcap(smith) upp from dual;

　　UPP

Smith

　　INSTR(CCIJ)
在一個字符串中搜索指定的字符返回發現指定的字符的位置;
C 被搜索的字符串
C 希望搜索的字符串
I 搜索的開始位置默認為
J 出現的位置默認為
SQL> select instr(oracle traningra) instring from dual;

　　INSTRING

　　LENGTH
返回字符串的長度;
SQL> select namelength(name)addrlength(addr)sallength(to_char(sal)) from gaonchar_tst;

　　NAME LENGTH(NAME) ADDR LENGTH(ADDR) SAL LENGTH(TO_CHAR(SAL))

高乾競 北京市海錠區

　　LOWER
返回字符串並將所有的字符小寫
SQL> select lower(AaBbCcDd)AaBbCcDd from dual;

　　AABBCCDD

aabbccdd

　　UPPER
返回字符串並將所有的字符大寫
SQL> select upper(AaBbCcDd) upper from dual;

　　UPPER

AABBCCDD

　　RPAD和LPAD(粘貼字符)
RPAD 在列的右邊粘貼字符
LPAD 在列的左邊粘貼字符
SQL> select lpad(rpad(gao*)*)from dual;

　　LPAD(RPAD(GAO

*******gao*******
不夠字符則用*來填滿

　　LTRIM和RTRIM
LTRIM 刪除左邊出現的字符串
RTRIM 刪除右邊出現的字符串
SQL> select ltrim(rtrim( gao qian jing ) ) from dual;

　　LTRIM(RTRIM(

gao qian jing

　　SUBSTR(stringstartcount)
取子字符串從start開始取count個
SQL> select substr() from dual;

　　SUBSTR(

　　REPLACE(stringss)
string 希望被替換的字符或變量
s 被替換的字符串
s 要替換的字符串
SQL> select replace(he love youhei) from dual;

　　REPLACE(H

i love you

　　SOUNDEX
返回一個與給定的字符串讀音相同的字符串
SQL> create table table(xm varchar());
SQL> insert into table values(weather);
SQL> insert into table values(wether);
SQL> insert into table values(gao);

　　SQL> select xm from table where soundex(xm)=soundex(weather);

　　XM

weather
wether

　　TRIM(s from string)
LEADING 剪掉前面的字符
TRAILING 剪掉後面的字符
如果不指定默認為空格符

　　ABS
返回指定值的絕對值
SQL> select abs()abs() from dual;

　　ABS() ABS()

　　ACOS
給出反余弦的值
SQL> select acos() from dual;

　　ACOS()

　　ASIN
給出反正弦的值
SQL> select asin() from dual;

　　ASIN()

　　ATAN
返回一個數字的反正切值
SQL> select atan() from dual;

　　ATAN()

　　CEIL
返回大於或等於給出數字的最小整數
SQL> select ceil() from dual;

　　CEIL()

　　COS
返回一個給定數字的余弦
SQL> select cos() from dual;

　　COS()

　　COSH
返回一個數字反余弦值
SQL> select cosh() from dual;

　　COSH()

　　EXP
返回一個數字e的n次方根
SQL> select exp()exp() from dual;

　　EXP() EXP()

　　FLOOR
對給定的數字取整數
SQL> select floor() from dual;

　　FLOOR()

　　LN
返回一個數字的對數值
SQL> select ln()ln()ln() from dual;

　　LN() LN() LN()

　　LOG(nn)
返回一個以n為底n的對數
SQL> select log()log() from dual;

　　LOG() LOG()

　　MOD(nn)
返回一個n除以n的余數
SQL> select mod()mod()mod() from dual;

　　MOD() MOD() MOD()

　　POWER
返回n的n次方根
SQL> select power()power() from dual;

　　POWER() POWER()

　　ROUND和TRUNC
按照指定的精度進行捨入
SQL> select round()round()trunc()trunc() from dual;

　　ROUND() ROUND() TRUNC() TRUNC()

　　SIGN
取數字n的符號大於返回小於返回等於返回
SQL> select sign()sign()sign() from dual;

　　SIGN() SIGN() SIGN()

　　SIN
返回一個數字的正弦值
SQL> select sin() from dual;

　　SIN()

　　SIGH
返回雙曲正弦的值
SQL> select sin()sinh() from dual;

　　SIN() SINH()

　　SQRT
返回數字n的根
SQL> select sqrt()sqrt() from dual;

　　SQRT() SQRT()

　　TAN
返回數字的正切值
SQL> select tan()tan() from dual;

　　TAN() TAN()

　　TANH
返回數字n的雙曲正切值
SQL> select tanh()tan() from dual;

　　TANH() TAN()

　　TRUNC
按照指定的精度截取一個數
SQL> select trunc() trunctrunc() from dual;

　　TRUNC TRUNC()

　　ADD_MONTHS
增加或減去月份
SQL> select to_char(add_months(to_date(yyyymm))yyyymm) from dual;

　　TO_CHA


SQL> select to_char(add_months(to_date(yyyymm))yyyymm) from dual;

　　TO_CHA

　　LAST_DAY
返回日期的最後一天
SQL> select to_char(sysdateyyyymmdd)to_char((sysdate)+yyyymmdd) from dual;

　　TO_CHAR(SY TO_CHAR((S


SQL> select last_day(sysdate) from dual;

　　LAST_DAY(S

月

　　MONTHS_BETWEEN(datedate)
給出datedate的月份
SQL> select months_between(月月) mon_between from dual;

　　MON_BETWEEN


SQL>selectmonths_between(to_date(yyyymmdd)to_date(yyyymmdd)) mon_betw from dual;

　　MON_BETW

　　NEW_TIME(datethisthat)
給出在this時區=other時區的日期和時間
SQL> select to_char(sysdateyyyymmdd hh:mi:ss) bj_timeto_char(new_time
(sysdatePDTGMT)yyyymmdd hh:mi:ss) los_angles from dual;

　　BJ_TIME LOS_ANGLES

:: ::

　　NEXT_DAY(dateday)
給出日期date和星期x之後計算下一個星期的日期
SQL> select next_day(月星期五) next_day from dual;

　　NEXT_DAY

月

　　SYSDATE
用來得到系統的當前日期
SQL> select to_char(sysdateddmmyyyy day) from dual;

　　TO_CHAR(SYSDATE

星期日
trunc(datefmt)按照給出的要求將日期截斷如果fmt=mi表示保留分截斷秒
SQL> select to_char(trunc(sysdatehh)yyyymmdd hh:mi:ss) hh
to_char(trunc(sysdatemi)yyyymmdd hh:mi:ss) hhmm from dual;

　　HH HHMM

:: ::

　　CHARTOROWID
將字符數據類型轉換為ROWID類型
SQL> select rowidrowidtochar(rowid)ename from scottemp;

　　ROWID ROWIDTOCHAR(ROWID) ENAME

AAAAfKAACAAAAEqAAA AAAAfKAACAAAAEqAAA SMITH
AAAAfKAACAAAAEqAAB AAAAfKAACAAAAEqAAB ALLEN
AAAAfKAACAAAAEqAAC AAAAfKAACAAAAEqAAC WARD
AAAAfKAACAAAAEqAAD AAAAfKAACAAAAEqAAD JONES

　　CONVERT(cdsetsset)
將源字符串 sset從一個語言字符集轉換到另一個目的dset字符集
SQL> select convert(strutzwehpfdec) conversion from dual;

　　conver

strutz

　　HEXTORAW
將一個十六進制構成的字符串轉換為二進制

　　RAWTOHEXT
將一個二進制構成的字符串轉換為十六進制

　　ROWIDTOCHAR
將ROWID數據類型轉換為字符類型

　　TO_CHAR(dateformat)
SQL> select to_char(sysdateyyyy/mm/dd hh:mi:ss) from dual;

　　TO_CHAR(SYSDATEYY

// ::

　　TO_DATE(stringformat)
將字符串轉化為ORACLE中的一個日期

　　TO_MULTI_BYTE
將字符串中的單字節字符轉化為多字節字符
SQL> select to_multi_byte(高) from dual;

　　TO

高

　　TO_NUMBER
將給出的字符轉換為數字
SQL> select to_number() year from dual;

　　YEAR

　　BFILENAME(dirfile)
指定一個外部二進制文件
SQL>insert into file_tb values(bfilename(lob_dirimagegif));

　　CONVERT(xdescsource)
將x字段或變量的源source轉換為desc
SQL> select sidserial#usernamedecode(command
none
insert

select
update
delete
drop
other) cmd from v$session where type!=background;

　　SID SERIAL# USERNAME CMD

none
none
none
none
none
none
none
none
GAO select
GAO none

　　DUMP(sfmtstartlength)
DUMP函數以fmt指定的內部數字格式返回一個VARCHAR類型的值
SQL> col global_name for a
SQL> col dump_string for a
SQL> set lin
SQL> select global_namedump(global_name) dump_string from global_name;

　　GLOBAL_NAME DUMP_STRING

ORACLEWORLD Typ= Len= CharacterSet=ZHSGBK: WORLD

　　EMPTY_BLOB()和EMPTY_CLOB()
這兩個函數都是用來對大數據類型字段進行初始化操作的函數

　　GREATEST
返回一組表達式中的最大值即比較字符的編碼大小
SQL> select greatest(AAABAC) from dual;

　　GR

AC
SQL> select greatest(啊安天) from dual;

　　GR

天

　　LEAST
返回一組表達式中的最小值
SQL> select least(啊安天) from dual;

　　LE

啊

　　UID
返回標識當前用戶的唯一整數
SQL> show user
USER 為GAO
SQL> select usernameuser_id from dba_users where user_id=uid;

　　USERNAME USER_ID

GAO

　　USER
返回當前用戶的名字
SQL> select user from dual;

　　USER

GAO

　　USEREVN
返回當前用戶環境的信息opt可以是:
ENTRYIDSESSIONIDTERMINALISDBALABLELANGUAGECLIENT_INFOLANGVSIZE
ISDBA 查看當前用戶是否是DBA如果是則返回true
SQL> select userenv(isdba) from dual;

　　USEREN

FALSE
SQL> select userenv(isdba) from dual;

　　USEREN

TRUE
SESSION
返回會話標志
SQL> select userenv(sessionid) from dual;

　　USERENV(SESSIONID)


ENTRYID
返回會話人口標志
SQL> select userenv(entryid) from dual;

　　USERENV(ENTRYID)


INSTANCE
返回當前INSTANCE的標志
SQL> select userenv(instance) from dual;

　　USERENV(INSTANCE)


LANGUAGE
返回當前環境變量
SQL> select userenv(language) from dual;

　　USERENV(LANGUAGE)

SIMPLIFIED CHINESE_CHINAZHSGBK
LANG
返回當前環境的語言的縮寫
SQL> select userenv(lang) from dual;

　　USERENV(LANG)

ZHS
TERMINAL
返回用戶的終端或機器的標志
SQL> select userenv(terminal) from dual;

　　USERENV(TERMINA

GAO
VSIZE(X)
返回X的大小(字節)數
SQL> select vsize(user)user from dual;

　　VSIZE(USER) USER

SYSTEM

　　AVG(DISTINCT|ALL)
all表示對所有的值求平均值distinct只對不同的值求平均值
SQLWKS> create table table(xm varchar()sal number());
語句已處理
SQLWKS> insert into table values(gao);
SQLWKS> insert into table values(gao);
SQLWKS> insert into table values(zhu);
SQLWKS> commit;

　　SQL> select avg(distinct sal) from gaotable;

　　AVG(DISTINCTSAL)

　　SQL> select avg(all sal) from gaotable;

　　AVG(ALLSAL)

　　MAX(DISTINCT|ALL)
求最大值ALL表示對所有的值求最大值DISTINCT表示對不同的值求最大值相同的只取一次
SQL> select max(distinct sal) from scottemp;

　　MAX(DISTINCTSAL)

　　MIN(DISTINCT|ALL)
求最小值ALL表示對所有的值求最小值DISTINCT表示對不同的值求最小值相同的只取一次
SQL> select min(all sal) from gaotable;

　　MIN(ALLSAL)

　　STDDEV(distinct|all)
求標准差ALL表示對所有的值求標准差DISTINCT表示只對不同的值求標准差
SQL> select stddev(sal) from scottemp;

　　STDDEV(SAL)

　　SQL> select stddev(distinct sal) from scottemp;

　　STDDEV(DISTINCTSAL)

　　VARIANCE(DISTINCT|ALL)
求協方差

　　SQL> select variance(sal) from scottemp;

　　VARIANCE(SAL)

　　GROUP BY
主要用來對一組數進行統計
SQL> select deptnocount(*)sum(sal) from scottemp group by deptno;

　　DEPTNO COUNT(*) SUM(SAL)

　　HAVING
對分組統計再加限制條件
SQL> select deptnocount(*)sum(sal) from scottemp group by deptno having count(*)>=;

　　DEPTNO COUNT(*) SUM(SAL)



SQL> select deptnocount(*)sum(sal) from scottemp having count(*)>= group by deptno ;

　　DEPTNO COUNT(*) SUM(SAL)

　　ORDER BY
用於對查詢到的結果進行排序輸出
SQL> select deptnoenamesal from scottemp order by deptnosal desc;

　　DEPTNO ENAME SAL

KING
CLARK
MILLER
SCOTT
FORD
JONES
ADAMS
SMITH
BLAKE
ALLEN
TURNER
WARD
MARTIN
JAMES

　　 pl/sql中的case語句

　　select   (case   when   DUMMY=X   then      else      end)   as   flag   from   dual;

　　case的第種用法
case col when a then
when b then
else end
這種用法跟decode一樣沒什麼區別

　　case的第種用法
case when score < then d
when score >= and score < then c
when score >= and score < then b
else a end

　　NVL(expr expr)

　　NVL(expr expr)>expr為NULL返回expr不為NULL返回expr注意兩者的類型要一致
NVL (expr expr expr) >expr不為NULL返回expr為NULL返回exprexpr和expr類型不同的話expr會轉換為expr的類型
NULLIF (expr expr) >相等返回NULL不等返回expr 

 
Oracle分析函數參考手冊

　　=============================================
作者: xsb([url])[/url]
發表於: :
分類: DW&BI
出處

     Oracle從開始提供分析函數分析函數用於計算基於組的某種聚合值它和聚合函數的不同之處是對於每個組返回多行
而聚合函數對於每個組只返回一行

　　常用的分析函數如下所列:
row_number() over(partition by order by )
rank() over(partition by order by )
dense_rank() over(partition by order by )
count() over(partition by order by )
max() over(partition by order by )
min() over(partition by order by )
sum() over(partition by order by )
avg() over(partition by order by )
first_value() over(partition by order by )
last_value() over(partition by order by )
lag() over(partition by order by )
lead() over(partition by order by )

　　下面例子中使用的表來自Oracle自帶的HR用戶下的表如果沒有安裝該用戶可以在SYS用戶下運行
$ORACLE_HOME/demo/schema/human_resources/hr_mainsql來創建

　　除本文內容外你還可參考
ROLLUP與CUBE [url][/url]
分析函數使用例子介紹[url][/url]

　　本文如果未指明缺省是在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》第章

　　ohwww :
 
續

　　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子句排序然後給每一行賦一個號從而形成一個序列該序列從開始往後累加每次ORDER BY表達式的值發生變化時該序列也隨之增加有同樣值的行得到同樣的數字序號（認為null時相等的）密集的序列返回的時沒有間隔的數
SAMPLE下例中計算每個員工按部門分區再按薪水排序依次出現的序列號（注意與RANK函數的區別）

　　SELECT ddepartment_id elast_name esalary DENSE_RANK()
OVER (PARTITION BY edepartment_id ORDER BY esalary) as drank
FROM employees e departments d
WHERE edepartment_id = ddepartment_id
AND ddepartment_id IN ( );

　　DEPARTMENT_ID LAST_NAME SALARY DRANK

Lorentz
Austin
Pataballa
Ernst
Hunold
Kochhar
De Haan
King

　　FIRST
功能描述從DENSE_RANK返回的集合中取出排在最前面的一個值的行（可能多行因為值可能相等）因此完整的語法需要在開始處加上一個集合函數以從中取出記錄
SAMPLE下面例子中DENSE_RANK按部門分區再按傭金commission_pct排序FIRST取出傭金最低的對應的所有行然後前面的MAX函數從這個集合中取出薪水最低的值LAST取出傭金最高的對應的所有行然後前面的MIN函數從這個集合中取出薪水最高的值
SELECT last_name department_id salary
MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct)
OVER (PARTITION BY department_id) Worst
MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct)
OVER (PARTITION BY department_id) Best
FROM employees
WHERE department_id in ()
ORDER BY department_id salary;

　　LAST_NAME DEPARTMENT_ID SALARY Worst Best

Fay
Hartstein
Kumar
Banda
Johnson
Ande
Lee
Tuvault
Sewall
Marvins
Bates

　　FIRST_VALUE
功能描述返回組中數據窗口的第一個值
SAMPLE下面例子計算按部門分區按薪水排序的數據窗口的第一個值對應的名字如果薪水的第一個值有多個則從多個對應的名字中取缺省排序的第一個名字

　　SELECT department_id last_name salary FIRST_VALUE(last_name)
OVER (PARTITION BY department_id ORDER BY salary ASC ) AS lowest_sal
FROM employees
WHERE department_id in();

　　DEPARTMENT_ID LAST_NAME SALARY LOWEST_SAL

Fay Fay
Hartstein Fay
Colmenares Colmenares
Himuro Colmenares
Tobias Colmenares
Baida Colmenares
Khoo Colmenares
Raphaely Colmenares

　　LAG
功能描述可以訪問結果集中的其它行而不用進行自連接它允許去處理游標就好像游標是一個數組一樣在給定組中可參考當前行之前的行這樣就可以從組中與當前行一起選擇以前的行Offset是一個正整數其默認值為若索引超出窗口的范圍就返回默認值（默認返回的是組中第一行）其相反的函數是LEAD
SAMPLE下面的例子中列prev_sal返回按hire_date排序的前行的salary值

　　SELECT last_name hire_date salary
LAG(salary ) OVER (ORDER BY hire_date) AS prev_sal
FROM employees
WHERE job_id = PU_CLERK;

　　LAST_NAME HIRE_DATE SALARY PREV_SAL

Khoo 月
Tobias 月
Baida 月
Himuro 月
Colmenares 月

　　LAST
功能描述從DENSE_RANK返回的集合中取出排在最後面的一個值的行（可能多行因為值可能相等）因此完整的語法需要在開始處加上一個集合函數以從中取出記錄
SAMPLE下面例子中DENSE_RANK按部門分區再按傭金commission_pct排序FIRST取出傭金最低的對應的所有行然後前面的MAX函數從這個集合中取出薪水最低的值LAST取出傭金最高的對應的所有行然後前面的MIN函數從這個集合中取出薪水最高的值
SELECT last_name department_id salary
MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct)
OVER (PARTITION BY department_id) Worst
MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct)
OVER (PARTITION BY department_id) Best
FROM employees
WHERE department_id in ()
ORDER BY department_id salary;

　　LAST_NAME DEPARTMENT_ID SALARY Worst Best

Fay
Hartstein
Kumar
Banda
Johnson
Ande
Lee
Tuvault
Sewall
Marvins
Bates

LAST_VALUE
功能描述返回組中數據窗口的最後一個值
SAMPLE下面例子計算按部門分區按薪水排序的數據窗口的最後一個值對應的名字如果薪水的最後一個值有多個則從多個對應的名字中取缺省排序的最後一個名字
SELECT department_id last_name salary LAST_VALUE(last_name)
OVER(PARTITION BY department_id ORDER BY salary) AS highest_sal
FROM employees
WHERE department_id in();

　　DEPARTMENT_ID LAST_NAME SALARY HIGHEST_SAL

Fay Fay
Hartstein Hartstein
Colmenares Colmenares
Himuro Himuro
Tobias Tobias
Baida Baida
Khoo Khoo
Raphaely Raphaely

　　LEAD
功能描述LEAD與LAG相反LEAD可以訪問組中當前行之後的行Offset是一個正整數其默認值為若索引超出窗口的范圍就返回默認值（默認返回的是組中第一行）
SAMPLE下面的例子中每行的NextHired返回按hire_date排序的下一行的hire_date值

　　SELECT last_name hire_date
LEAD(hire_date ) OVER (ORDER BY hire_date) AS NextHired
FROM employees WHERE department_id = ;

　　LAST_NAME HIRE_DATE NextHired

Raphaely DEC MAY
Khoo MAY JUL
Tobias JUL DEC
Baida DEC NOV
Himuro NOV AUG
Colmenares AUG

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

　　SELECT department_id last_name salary
MAX(salary) OVER (PARTITION BY department_id) AS dept_max
FROM employees WHERE department_id in ();

　　DEPARTMENT_ID LAST_NAME SALARY DEPT_MAX

Whalen
Hartstein
Fay
Raphaely
Khoo
Baida
Tobias
Himuro
Colmenares

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

　　SELECT department_id last_name salary
MIN(salary) OVER (PARTITION BY department_id) AS dept_min
FROM employees WHERE department_id in ();

　　DEPARTMENT_ID LAST_NAME SALARY DEPT_MIN

Whalen
Hartstein
Fay
Raphaely
Khoo
Baida
Tobias
Himuro
Colmenares

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

　　SELECT last_name salary
NTILE() OVER (ORDER BY salary DESC) AS quartile FROM employees
WHERE department_id = ;

　　LAST_NAME SALARY QUARTILE

Greenberg
Faviet
Chen
Urman
Sciarra
Popp

　　PERCENT_RANK
功能描述和CUME_DIST（累積分配）函數類似對於一個組中給定的行來說在計算那行的序號時先減然後除以n（n為組中所有的行數）該函數總是返回～（包括）之間的數
SAMPLE下例中如果Khoo的salary為則pr值為因為RANK函數對於等值的返回序列值是一樣的

　　SELECT department_id last_name salary
PERCENT_RANK()
OVER (PARTITION BY department_id ORDER BY salary) AS pr
FROM employees
WHERE department_id <
ORDER BY department_idsalary;

　　DEPARTMENT_ID LAST_NAME SALARY PR

Whalen
Fay
Hartstein
Colmenares
Himuro
Tobias
Baida
Khoo
Raphaely
Mavris

　　PERCENTILE_CONT
功能描述返回一個與輸入的分布百分比值相對應的數據值分布百分比的計算方法見函數PERCENT_RANK如果沒有正好對應的數據值就通過下面算法來得到值
RN = + (P*(N)) 其中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的區別在找不到對應的分布值時返回的替代值的計算方法不同

　　SAMPLE在下例中對於部門的Percentile_Cont值計算如下
P= N= RN =+ (P*(N)=+(*())= CRN = CEIL()=
FRN = FLOOR()=
（ ）* + ( ) * =

　　SELECT last_name salary department_id
PERCENTILE_CONT() 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 WHERE department_id IN ( );

　　LAST_NAME SALARY DEPARTMENT_ID Percentile_Cont Percent_Rank

Colmenares
Himuro
Tobias
Baida
Khoo
Raphaely
Lorentz
Austin
Pataballa
Ernst
Hunold

　　PERCENTILE_DISC
功能描述返回一個與輸入的分布百分比值相對應的數據值分布百分比的計算方法見函數CUME_DIST如果沒有正好對應的數據值就取大於該分布值的下一個值
注意本函數與PERCENTILE_CONT的區別在找不到對應的分布值時返回的替代值的計算方法不同

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

　　SELECT last_name salary department_id
PERCENTILE_DISC() 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
WHERE department_id in ( );

　　LAST_NAME SALARY DEPARTMENT_ID Percentile_Disc Cume_Dist

Colmenares
Himuro
Tobias
Baida
Khoo
Raphaely
Lorentz
Austin
Pataballa
Ernst
Hunold

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

　　SELECT ddepartment_id elast_name esalary RANK()
OVER (PARTITION BY edepartment_id ORDER BY esalary) as drank
FROM employees e departments d
WHERE edepartment_id = ddepartment_id
AND ddepartment_id IN ( );

　　DEPARTMENT_ID LAST_NAME SALARY DRANK

Lorentz
Austin
Pataballa
Ernst
Hunold
Kochhar
De Haan
King

　　RATIO_TO_REPORT
功能描述該函數計算expression/(sum(expression))的值它給出相對於總數的百分比即當前行對sum(expression)的貢獻
SAMPLE下例計算每個員工的工資占該類員工總工資的百分比

　　SELECT last_name salary RATIO_TO_REPORT(salary) OVER () AS rr
FROM employees
WHERE job_id = PU_CLERK;

　　LAST_NAME SALARY RR

Khoo
Baida
Tobias
Himuro
Colmenares

　　REGR_ (Linear Regression) Functions
功能描述這些線性回歸函數適合最小二乘法回歸線有個不同的回歸函數可使用
REGR_SLOPE返回斜率等於COVAR_POP(expr expr) / VAR_POP(expr)
REGR_INTERCEPT返回回歸線的y截距等於
AVG(expr) REGR_SLOPE(expr expr) * AVG(expr)
REGR_COUNT返回用於填充回歸線的非空數字對的數目
REGR_R返回回歸線的決定系數計算式為
If VAR_POP(expr) = then return NULL
If VAR_POP(expr) = and VAR_POP(expr) != then return
If VAR_POP(expr) > and VAR_POP(expr != then
return POWER(CORR(exprexpr))
REGR_AVGX計算回歸線的自變量(expr)的平均值去掉了空對(expr expr)後等於AVG(expr)
REGR_AVGY計算回歸線的應變量(expr)的平均值去掉了空對(expr expr)後等於AVG(expr)
REGR_SXX 返回值等於REGR_COUNT(expr expr) * VAR_POP(expr)
REGR_SYY 返回值等於REGR_COUNT(expr expr) * VAR_POP(expr)
REGR_SXY: 返回值等於REGR_COUNT(expr expr) * COVAR_POP(expr expr)

　　（下面的例子都是在SH用戶下完成的）
SAMPLE 下例計算年最後三個星期中兩種產品（和）在周末的銷售量中已開發票數量和總數量的累積斜率和回歸線的截距

　　SELECT tfiscal_month_number Month tday_number_in_month Day
REGR_SLOPE(samount_sold squantity_sold)
OVER (ORDER BY tfiscal_month_desc tday_number_in_month) AS CUM_SLOPE
REGR_INTERCEPT(samount_sold squantity_sold)
OVER (ORDER BY tfiscal_month_desc tday_number_in_month) AS CUM_ICPT
FROM sales s times t
WHERE stime_id = ttime_id
AND sprod_id IN ( )
AND tfiscal_year=
AND tfiscal_week_number IN ( )
AND tday_number_in_week IN ()
ORDER BY tfiscal_month_desc tday_number_in_month;

　　Month Day CUM_SLOPE CUM_ICPT

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

　　SELECT UNIQUE tday_number_in_month
REGR_COUNT(samount_sold squantity_sold)
OVER (PARTITION BY tfiscal_month_number ORDER BY tday_number_in_month)
Regr_Count
FROM sales s times t
WHERE stime_id = ttime_id
AND tfiscal_year = AND tfiscal_month_number = ;

　　DAY_NUMBER_IN_MONTH Regr_Count

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

　　SELECT tfiscal_month_number
REGR_R(SUM(samount_sold) SUM(squantity_sold))
OVER (ORDER BY tfiscal_month_number) Regr_R
FROM sales s times t
WHERE stime_id = ttime_id
AND tfiscal_year =
GROUP BY tfiscal_month_number
ORDER BY tfiscal_month_number;

　　FISCAL_MONTH_NUMBER Regr_R

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

　　SELECT tday_number_in_month
REGR_AVGY(samount_sold squantity_sold)
OVER (ORDER BY tfiscal_month_desc tday_number_in_month)
Regr_AvgY
REGR_AVGX(samount_sold squantity_sold)
OVER (ORDER BY tfiscal_month_desc tday_number_in_month)
Regr_AvgX
FROM sales s times t
WHERE stime_id = ttime_id
AND sprod_id =
AND tfiscal_month_desc =
AND tfiscal_week_number IN ( )
ORDER BY tday_number_in_month;

　　DAY_NUMBER_IN_MONTH Regr_AvgY Regr_AvgX

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

　　SELECT tday_number_in_month
REGR_SXY(samount_sold squantity_sold)
OVER (ORDER BY tfiscal_year tfiscal_month_desc) Regr_sxy
REGR_SYY(samount_sold squantity_sold)
OVER (ORDER BY tfiscal_year tfiscal_month_desc) Regr_syy
REGR_SXX(samount_sold squantity_sold)
OVER (ORDER BY tfiscal_year tfiscal_month_desc) Regr_sxx
FROM sales s times t
WHERE stime_id = ttime_id
AND prod_id IN ( )
AND tfiscal_month_desc =
AND tday_number_in_week IN ()
ORDER BY tday_number_in_month;

　　DAY_NUMBER_IN_MONTH Regr_sxy Regr_syy Regr_sxx

　　 

　　ROW_NUMBER
功能描述返回有序組中一行的偏移量從而可用於按特定標准排序的行號
SAMPLE下例返回每個員工再在每個部門中按員工號排序後的順序號

　　SELECT department_id last_name employee_id ROW_NUMBER()
OVER (PARTITION BY department_id ORDER BY employee_id) AS emp_id
FROM employees
WHERE department_id < ;

　　DEPARTMENT_ID LAST_NAME EMPLOYEE_ID EMP_ID

Whalen
Hartstein
Fay
Raphaely
Khoo
Baida
Tobias
Himuro
Colmenares
Mavris

　　STDDEV
功能描述計算當前行關於組的標准偏離（Standard Deviation）
SAMPLE下例返回部門按雇傭日期排序的薪水值的累積標准偏離

　　SELECT last_name hire_datesalary
STDDEV(salary) OVER (ORDER BY hire_date) StdDev
FROM employees
WHERE department_id = ;

　　LAST_NAME HIRE_DATE SALARY StdDev

Raphaely 月
Khoo 月
Tobias 月
Baida 月
Himuro 月
Colmenares 月

　　STDDEV_POP
功能描述該函數計算總體標准偏離並返回總體變量的平方根其返回值與VAR_POP函數的平方根相同（Standard Deviation－Population）
SAMPLE下例返回部門的薪水值的總體標准偏差

　　SELECT department_id last_name salary
STDDEV_POP(salary) OVER (PARTITION BY department_id) AS pop_std
FROM employees
WHERE department_id in ();

　　DEPARTMENT_ID LAST_NAME SALARY POP_STD

Hartstein
Fay
Raphaely
Khoo
Baida
Colmenares
Himuro
Tobias
Hunold
Ernst
Austin
Pataballa
Lorentz

　　STDDEV_SAMP
功能描述 該函數計算累積樣本標准偏離並返回總體變量的平方根其返回值與VAR_POP函數的平方根相同（Standard Deviation－Sample）
SAMPLE下例返回部門的薪水值的樣本標准偏差

　　SELECT department_id last_name hire_date salary
STDDEV_SAMP(salary) OVER
(PARTITION BY department_id ORDER BY hire_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_sdev
FROM employees
WHERE department_id in ();

　　DEPARTMENT_ID LAST_NAME HIRE_DATE SALARY CUM_SDEV

Hartstein 月
Fay 月
Raphaely 月
Khoo 月
Tobias 月
Baida 月
Himuro 月
Colmenares 月
Hunold 月
Ernst 月
Austin 月
Pataballa 月
Lorentz 月

　　SUM
功能描述該函數計算組中表達式的累積和
SAMPLE下例計算同一經理下員工的薪水累積值

　　SELECT manager_id last_name salary
SUM (salary) OVER (PARTITION BY manager_id ORDER BY salary
RANGE UNBOUNDED PRECEDING) l_csum
FROM employees
WHERE manager_id in ();

　　MANAGER_ID LAST_NAME SALARY L_CSUM

Whalen
Mavris
Baer
Greenberg
Higgins
Lorentz
Austin
Pataballa
Ernst
Popp
Sciarra
Urman
Chen
Faviet

　　VAR_POP
功能描述（Variance Population）該函數返回非空集合的總體變量（忽略null）VAR_POP進行如下計算
(SUM(expr) SUM(expr) / COUNT(expr)) / COUNT(expr)
SAMPLE下例計算年每月銷售的累積總體和樣本變量（本例在SH用戶下運行）

　　SELECT tcalendar_month_desc
VAR_POP(SUM(samount_sold))
OVER (ORDER BY tcalendar_month_desc) Var_Pop
VAR_SAMP(SUM(samount_sold))
OVER (ORDER BY tcalendar_month_desc) Var_Samp
FROM sales s times t
WHERE stime_id = ttime_id AND tcalendar_year =
GROUP BY tcalendar_month_desc;

　　CALENDAR Var_Pop Var_Samp


E+ E+
E+ E+
E+ E+
E+ E+
E+ E+
E+ E+
E+ E+
E+ E+
E+ E+
E+ E+
E+ E+

　　 

From:http://tw.wingwit.com/Article/program/Oracle/201311/18274.html