利用分析函數解決分组排序

今天寫報表遇到分組排序的問題，經過一番探查搞掂了，嘿嘿，分析函數真強大。

 

SELECT CUST_ID,SUM(DECODE(Y_MONTH,'201001',QTY1,0)) JAN_2010, SUM(DECODE(Y_MONTH,'201001',RANK,0)) RANK1, SUM(DECODE(Y_MONTH,'201002',QTY1,0)) FEB_2010, SUM(DECODE(Y_MONTH,'201002',RANK,0)) RANK2, SUM(DECODE(Y_MONTH,'201003',QTY1,0)) MAR_2010, SUM(DECODE(Y_MONTH,'201003',RANK,0)) RANK3, SUM(DECODE(Y_MONTH,'201004',QTY1,0)) Apr_2010, SUM(DECODE(Y_MONTH,'201004',RANK,0)) RANK4, SUM(DECODE(Y_MONTH,'201005',QTY1,0)) May_2010, SUM(DECODE(Y_MONTH,'201005',RANK,0)) RANK5, SUM(DECODE(Y_MONTH,'201006',QTY1,0)) Jun_2010, SUM(DECODE(Y_MONTH,'201006',RANK,0)) RANK6, SUM(DECODE(Y_MONTH,'201007',QTY1,0)) Jul_2010, SUM(DECODE(Y_MONTH,'201007',RANK,0)) RANK7 From ( select M.CUST_ID, TO_CHAR(M.SALES_DATE,'YYYYMM') Y_MONTH, SUM(D.QTY) QTY1, rank() over(PARTITION BY TO_CHAR(M.SALES_DATE,'YYYYMM') order by SUM(D.QTY) desc NULLS LAST) rank FROM SH_SALES_M M,SH_SALES_D D WHERE M.ORG_ID=100 AND M.ORG_ID=D.ORG_ID AND M.SALES_ID=D.SALES_ID AND TO_CHAR(M.SALES_DATE,'YYYYMM')>='201001' AND TO_CHAR(M.SALES_DATE,'YYYYMM')<='201007' AND SE_ID IN (SELECT SE_ID FROM SO_ORD_M WHERE ORG_ID=100 AND COLUMN1=1) AND M.STOC_NO<>'FN06' AND M.STATUS=99 GROUP BY M.CUST_ID ,TO_CHAR(M.SALES_DATE,'YYYYMM') ) GROUP BY CUST_ID ORDER BY CUST_ID

 

關鍵點:分析函數 RANK()

 

下边贴出这些函数，及简单应用。

1.RANK()



dense_rank()



【语法】RANK ( ) OVER ( [query_partition_clause] order_by_clause )



	dense_RANK ( ) OVER ( [query_partition_clause] order_by_clause )







【功能】聚合函数RANK 和 dense_rank 主要的功能是计算一组数值中的排序值。



【参数】dense_rank与rank()用法相当，



【区别】dence_rank在并列关系是，相关等级不会跳过。rank则跳过



rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内） 



dense_rank()l是连续排序，有两个第二名时仍然跟着第三名。



【说明】Oracle分析函数










2.ROW_NUMBER()



【语法】ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2) 



【功能】表示根据COL1分组，在分组内部根据 COL2排序，而这个值就表示每组内部排序后的顺序编号（组内连续的唯一的） 



row_number() 返回的主要是“行”的信息，并没有排名



【参数】



【说明】Oracle分析函数







主要功能：用于取前几名，或者最后几名等










3.lag（）本身是延后的意思也就是延后出现某列的数，而lead（）有引领、领先的意思也就是提前几行显示某列数据






【语法】



lag(EXPR,<OFFSET>,<DEFAULT>)



LEAD(EXPR,<OFFSET>,<DEFAULT>)



【功能】表示根据COL1分组，在分组内部根据 COL2排序，而这个值就表示每组内部排序后的顺序编号（组内连续的唯一的） 



lead （） 下一个值 lag（） 上一个值







【参数】



EXPR是从其他行返回的表达式 



OFFSET是缺省为1 的正数，表示相对行数。希望检索的当前行分区的偏移量



DEFAULT是在OFFSET表示的数目超出了分组的范围时返回的值。



【说明】Oracle分析函数