几种常用分析函数的使用方法几种常用的分析函数如上所示。下面将以下表为例对使 用方法进行演示。表名：tmp_userarea_codeserv_numbertotal_fee 37013903700002 100 37113607660002 80 37013903700001 200 37113607660003 80 37013903700003 50 37113607660001 280Rank/Dense_rank/Row_number求排名first/last求最前最后一名NTILE/WIDTH_DUCKET求结果分组CUME_DIST/PERCENT_RANK求排名占比WITHIN GROUP求某值的排名Select a.*, rank() over(partition by area_code order by total_fee) id1 , dense_rank() over(partition by area_code order by total_fee) id2 , row_number() over(partition by area_code order by total_fee) id3 from tmp_user aRank/Dense_rank/Row_numberAREA_CODESERV_NUMBERTOTAL_FEEID1ID2ID33701390370000350111 37013903700002100222 37013903700001200333 3711360766000280111 3711360766000380112 37113607660001280323上面语句分别用三个函数展示了 每个用户按消费额在该地市的排名。 Rank 和 dense_rank对于相同的值都采用相同的排名，但是rank会跳过接下来的若 干名次，而dense_rank不会。Row_number则不会出现相同的名次，对于相同的值 的排序是任意的。 Partiton by 的功能是将结果集分组，该项也可不用。以上示例是按照地市分组。分组排序应用示例上面语句取各地市消费最高的用户Select area_code,serv_number,total_fee from (Select a.*,rank() over(partition by area_code order by total_fee desc) id from tmp_user a) where id=13701390370000 12003711360766000 1280first/lastSelect area_code,min(SERV_NUMBER) keep(dense_rank last order by total_fee) id from tmp_user a group by area_code3701390370000137113607660001该语句功能为获取各地市消费最高的用户。 Min(serv_number) 是指在排名相同时，取较小的号码。 该函数使用时必须使用dense_rank。 该语句也可写成dense_rank first order by total_fee descNTILESelect AREA_CODE,SERV_NUMBER, ntile(3) over(order by sum(total_fee) id from tmp_user a group by AREA_CODE,SERV_NUMBER370139037000031371136076600021371136076600032370139037000022370139037000013371136076600013NTILE函数将结果集分为几个分区，上面例子显示了消费前三分之一的用户及 后三分之一的用户。WIDTH_DUCKETSelect AREA_CODE,SERV_NUMBER, WIDTH_BUCKET(TOTAL_FEE,100,300,3) id,TOTAL_FEE from tmp_user aWIDTH_DUCKET函数按值的区间来分组。以上按照100300区间均分为三组 。分组值从1开始，遇到不在设定的范围内的值，会自动产生两个组，0和N+1370139037000030503701390370000211003701390370000122003711360766000208037113607660003080371136076600013280CUME_DIST/PERCENT_RANKSelect a.*,DENSE_RANK() over(order by total_fee) ID, CUME_DIST() over(order by total_fee) CUME_DIST, PERCENT_RANK() over(order by total_fee) PERCENT_RANK,1/6, from tmp_user aCUME_DIST：计算小于或等于当前排名的行数在总行数的百分比。 PERCENT_RANK: (当前排名1)/(总行数1)AREA_CODESERV_NUMBERTOTAL_FEEIDCUME_DISTPERCENT_RANK370139037000035010.1666670371136076600028020.50.2371136076600038020.50.23701390370000210030.6666670.63701390370000120040.8333330.837113607660001280511WITHIN GROUPSelect AREA_CODE, RANK(100) WITHIN GROUP(ORDER BY TOTAL_FEE DESC) from tmp_user a GROUP BY AREA_CODE37023712WITHIN GROUP ：求一个常值在实际结果集中的排名。以上例子求100元 的消费能在各地市的排多少名