oracle最全函數(shù)大全(分析函數(shù)-聚合函數(shù)-轉(zhuǎn)換函數(shù)-日期型函數(shù)-字符型函數(shù)-數(shù)值型函數(shù)-其他函數(shù))

1、acleacle函數(shù)大全函數(shù)大全(分析函數(shù)分析函數(shù)聚合函數(shù)聚合函數(shù)轉(zhuǎn)換函數(shù)轉(zhuǎn)換函數(shù)日期型函數(shù)日期型函數(shù)字符型函數(shù)字符型函數(shù)數(shù)值型函數(shù)數(shù)值型函數(shù)其他函數(shù)其他函數(shù))acle函數(shù)大全函數(shù)大全.................................................................................................................1acle分析函數(shù)分析函數(shù)SQ

2、LPLUS環(huán)境環(huán)境.......................................................................................1acle10g函數(shù)大全聚合函數(shù)................................................................................19acle10g函數(shù)大全轉(zhuǎn)換函數(shù)...........

3、.....................................................................23acle10g函數(shù)大全函數(shù)大全日期型函數(shù)日期型函數(shù).............................................................................40acle10g函數(shù)大全函數(shù)大全字符型函數(shù)字符型函數(shù)....................

4、.........................................................45acle10g函數(shù)大全函數(shù)大全數(shù)值型函數(shù)數(shù)值型函數(shù).............................................................................55acle10g函數(shù)大全函數(shù)大全其他函數(shù)其他函數(shù)..................................

5、..............................................58acleacle分析函數(shù)分析函數(shù)SQLPLUSSQLPLUS環(huán)境環(huán)境一、總體介紹1.1.1.1.分析函數(shù)如何工作分析函數(shù)如何工作語法FUNCTION_NAME(…)OVER()PARTITION子句DERBY子句WINDOWING子句缺省時(shí)相當(dāng)于RANGEUNBOUNDEDPRECEDING1.值域窗值域窗(RANGEWINDOW)RANGE

6、NPRECEDING僅對(duì)數(shù)值或日期類型有效選定窗為排序后當(dāng)前行之前某列(即排序列)值大于小于(當(dāng)前行該列值–N)的所有行因此與DERBY子句有關(guān)系。2.行窗行窗(ROWWINDOW)ROWSNPRECEDING選定窗為當(dāng)前行及之前N行。還可以加上BETWEEN形式例如RANGEBETWEENmPRECEDINGnFOLLOWING函數(shù)AVG(eXPr)一組或選定窗中表達(dá)式的平均值CR(exprexpr)即COVAR_POP(exp1ex

7、p2)(STDDEV_POP(expr1)STDDEV_POP(expr2))兩個(gè)表達(dá)式的互相關(guān)1(反相關(guān))~1(正相關(guān))0表示不相關(guān)COUNT()計(jì)數(shù)COVAR_POP(exprexpr)總體協(xié)方差COVAR_SAMP(exprexpr)樣本協(xié)方差createtablestudents(idnumber(150)areavar2(10)stu_typevar2(2)scenumber(202))intostudentsvalues(1

8、111g80)intostudentsvalues(1111j80)intostudentsvalues(1222g89)intostudentsvalues(1222g68)intostudentsvalues(2111g80)intostudentsvalues(2111j70)intostudentsvalues(2222g60)intostudentsvalues(2222j65)intostudentsvalues(3111g

9、75)intostudentsvalues(3111j58)intostudentsvalues(3222g58)intostudentsvalues(3222j90)intostudentsvalues(4111g89)intostudentsvalues(4111j90)intostudentsvalues(4222g90)intostudentsvalues(4222j89)commit二、具體應(yīng)用：1、分組求和：1.2.1.2.