sql高级oracle分析函数使用总结

1、Oracle 分析函数使用总结使函 R():与 Oracle 分析函数使用总结使函 R():与 NTILE():返回nRSQLdesc名是否为空? SQLselect*fromall_saleswhereNOTNULLNUMBER(38) NOTNULLNUMBER(38) NOTNULLNUMBER(38) NOTNULLNUMBER(38)MONTH123456789111111111110行。SQL举例来说明上述函数的使用：首先是2345R()OVER(ORDERBYSUM(amount) DESC)AS,from ()OVER(ORDERBYSUM(amount)DESC)AS678w

2、here groupbyorder by; R5345注意：PRD_TYPE_ID列为5的SUM(AMOUNT)的值为空返回值为1。因为默认状态下在递增排序中则把它指定为最（）和DENSE-678where groupbyorder by; R5345注意：PRD_TYPE_ID列为5的SUM(AMOUNT)的值为空返回值为1。因为默认状态下在递增排序中则把它指定为最（）和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的空值的排序位于第一，现在则位于第五接下来来看分析函数与PARTITION BY SQL234567R from all_sales whereyear=2003andamount ISNOTSQL234567R from all_sales whereyear=2003andamount ISNOTGROUPBY8*ORDER BYMONTH

4、R1111222233312341234123MONTHR2412341234124444555566MONTHR323124667777341234134212341MONTHR43223412341234134212341MONTHR43223412341234MONTHR3123448行上次学的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前结果集分成 N 部分的意思，这个函数的参数为 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（）函数就相当于 于或等于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