sql高級oracle分析函數(shù)使用總結(jié)

1、Oracle 分析函數(shù)使用總結(jié)使函 R():與 Oracle 分析函數(shù)使用總結(jié)使函 R():與 NTILE():返回nRSQLdesc名是否為空? SQLselect*fromall_saleswhereNOTNULLNUMBER(38) NOTNULLNUMBER(38) NOTNULLNUMBER(38) NOTNULLNUMBER(38)MONTH123456789111111111110行。SQL舉例來說明上述函數(shù)的使用：首先是2345R()OVER(ORDERBYSUM(amount) DESC)AS,from ()OVER(ORDERBYSUM(amount)DESC)AS678w

2、here groupbyorder by; R5345注意：PRD_TYPE_ID列為5的SUM(AMOUNT)的值為空返回值為1。因為默認(rèn)狀態(tài)下在遞增排序中則把它指定為最（）和DENSE-678where groupbyorder by; R5345注意：PRD_TYPE_ID列為5的SUM(AMOUNT)的值為空返回值為1。因為默認(rèn)狀態(tài)下在遞增排序中則把它指定為最（）和DENSE-。這里還有一個問題的例子中沒有 SUM(AMOUNT)相等將PRD_TYPE_ID4 R5334SQL和NULLS234567R() OVER(ORDERBYSUM(amount)DESC NULLSLAST)

3、AS,from all_sales whereyear=2003()OVER(ORDERBYSUM(amount)DESCNULLSLAST)ASgroupby8*orderby R1234512345不使用NULLSLAST時PRD_TYPE_ID為5的空值的排序位于第一，現(xiàn)在則位于第五接下來來看分析函數(shù)與PARTITION BY SQL234567R from all_sales whereyear=2003andamount ISNOTSQL234567R from all_sales whereyear=2003andamount ISNOTGROUPBY8*ORDER BYMONTH

4、R1111222233312341234123MONTHR2412341234124444555566MONTHR323124667777341234134212341MONTHR43223412341234134212341MONTHR43223412341234MONTHR3123448行上次學(xué)的ROLLUP、CUBE、R()OVER(ORDERBYSUM(amount)DESCNULLSLAST)ASFROMBY rPRD_TYPE_ID R905081.84 478270.91 402751.16 186381.22 61 13425R()OVER(ORDERBYDESCrFROMBY

5、 prd_type_id,emp_id;EMP_IDR11111112R()OVER(ORDERBYDESCrFROMBY prd_type_id,emp_id;EMP_IDR11111112222EMP_IDR22233333334EMP_IDR444444555655EMP_IDR5545879142行SQL234567R55EMP_IDR5545879142行SQL234567R()OVER(ORDERBYSUM(amount) DESCNULLSLAST)ASFROMWHEREyear=2003 GROUPBYORDERBYEMP_IDR12345769811行比：SQL23CUME_

6、DIST()OVER(ORDERBYSUM(amount)DESC)AS45678FROM all_sales ()OVER(ORDERBYSUM(amount)DESC)ASGROUP BYORDERBY 1234545678FROM all_sales ()OVER(ORDERBYSUM(amount)DESC)ASGROUP BYORDERBY 12345110前結(jié)果集分成 N 部分的意思，這個函數(shù)的參數(shù)為 SQL2345678NTILE(2)OVER(ORDERBYSUM(amount)DESC)ASFROM all_sales ANDamountISNOTGROUPBYprd_typ

7、e_id ORDERBY 12341212注意這里的N21，2SQL2345678NTILE(3)OVER(ORDERBYSUM(amount)DESC)ASFROM all_sales ANDamountISNOTGROUPBYprd_type_id ORDERBY 123413121片N=4時就與 12341421片N=4時就與 1234142SQL234567ROW_NUMBER()OVER(ORDERBYSUM(amount)DESC)ASrow_number FROM all_salesWHEREGROUPBYprd_type_id ORDERBY 1234525341這里ROW-N

8、UMBER（）函數(shù)就相當(dāng)于 于或等于X 的值找到大于或等于X的值 SQL23456PERCENTILE_CONT(0.6)WITHINGROUP(ORDERBYSUM(amount)DESC)ASpercentile_cont, PERCENTILE_DISC(0.6) WITHIN GROUP (ORDER BY SUM(amount) DESC) AS percentile_disc FROM all_salesWHEREGROUPBY 下面這個例子是計算出下面這個例子是計算出2003112月的累積銷量SQL234month 月份,SUM(amountAS 月總銷量 (ORDER BYmo

9、nth ROWS N UNBOUNDEDPRECEDING AND CURRENTROW) AS 累積銷量 -5678FROM all_sales WHEREGROUPBYORDERBY月總銷量積銷123456789 160221.98月總銷量積銷 12行SQL SELECT23456789month 月份,SUM(amountAS 月總銷量 (ORDERBYmonthROWSUNBOUNDEDPRECEDINGAS 月累積銷FROM all_sales ANDmonthN6ANDGROUPBYmonth ORDERBY月總銷量積銷6789160221.98 7行6789160221.98 7

10、行SQL234量5678month 月份,SUM(amountAS 月總銷量 (ORDER BYmonth ROWS N3PRECEDINGAND CURRENT ROW) AS 三月平均累積FROM all_sales WHEREGROUPBYORDERBY12345678912行SQL2345678month 月份,SUM(amountAS 月總銷量(ORDERBYmonthROWSFROM all_sales WHEREGROUPBYORDERBYN1PRECEDING2345678month 月份,SUM(amountAS 月總銷量(ORDERBYmonthROWSFROM all_sales WHEREGROUPBYORDERBYN1PRECEDINGAND1FOLLOWINGAS 平均累積銷123456789SQL23456789month 月份,SUM(amountAS 月總銷量 (ORDERBYmonthROWSN1PRECEDINGAND1FOLLOWINGAS 前月銷量(ORDERBYmonthROWSN1PRECEDINGAND1FOLLOWINGAS 后月銷FROM all_sales WHEREGROUPBYORDERBY前月銷123456789前月銷160221.98 1256789前月銷160221.98 12行SQL23456