ORACLE分析函数实战(Analytical Functions)_PART7

接PART6：http://blog.chinaunix.net/uid-7655508-id-5834920.html

1.3.2.5 NTILE

  NTILE实现等高均匀分布，NTILE这个分析函数是根据查询的结果集按照分析子句order by(partition可选)的顺序，通过指定的expr的值，expr就是所分配的最大桶数，按照结果集行数目/expr获得一个值，这个值就是每个桶落入多少行,先按照平均分配的原则依次递增分配，直到分析函数的值最大为桶数(如果桶数超过行集数目，那么最大为行集数目),如果桶数有余数，那么对余数从分析函数的值从小到大平均分配，每个桶值最多增加1个，这样逼近平均分配原则。

  其中NTILE一般是正整数，如果不是整数，必须大于1，否则出错，一般是使用trunc自动截断，比如NTILE(2.6)实际上是NTILE(2)。

  比如查询出的行有30行，对NTILE(4)计算，那么最终分析函数的最大值是4，相当于首先按照order by和partition的顺序对30行平均分为4组，每组7条数据，因为余2组，根据最多每组增加1的原则，第1组和第2组，比第3组和第4组多1个桶。

  从上面我们有所觉察此函数到底什么作用了，你猜的没错，这个函数可以用来查找前n%的数据。比如NTILE(4)我们可以用来查找前25%的数据，25%到50%的数据等等。实际上此函数是对rank,row_number,dense_rank等排名函数的一个扩展。

      
  比如有这样的问题：
  查找orders表中2001年每个区域销售总额占所有区域2001年销售总额25%的地区以及对应的销售总额。

  分析：当然这个需求，我们使用row_number等函数也可以实现，不过有点麻烦，比如我们可以将所有记录数算出来，然后将排名<记录数*25%的记录取出，这样也就是取出前8名就可以了，因为有余数。

  但是没有使用NTILE简单，下面比较一下这两种方法。

1.使用传统排名函数求前25%的销售总额数据。
也就是将rownum先算出来，然后计算应该取的行号总数*百分比，对余数的处理具体情况具体分析。

with tmp

as(

SELECT region_id, cust_nbr, SUM(tot_sales) cust_sales,

 ROW_NUMBER() OVER (ORDER BY SUM(tot_sales) DESC) rn

FROM orders

WHERE year = 2001

GROUP BY region_id, cust_nbr

)

select * from tmp where rn<=(select round(count(*)*0.25) from tmp);

 

 

REGION_ID   CUST_NBR CUST_SALES         RN

--------- ---------- ---------- -------------------------------------

        9         25    2232703          1

        8         17    1944281          2

        7         14    1929774          3

        5          4    1878275          4

       10         26    1808949          5

        6          6    1788836          6

        8         20    1413722          7

       10         27    1322747          8

 

2. 使用NTILE实现。
求前25%只要NTILE(4)分析函数的结果为1即可。

SELECT * FROM (

SELECT region_id, cust_nbr, SUM(tot_sales) cust_sales,

        NTILE(4) OVER (ORDER BY SUM(tot_sales) DESC) sales_quartile

FROM orders

WHERE year = 2001

GROUP BY region_id, cust_nbr)

WHERE sales_quartile=1;

结果同上。

1.3.2.6 WIDTH_BUCKET

  WIDTH_BUCKET实现频率均匀分布，这个函数和NTILE类似，也是将查询结果集放入桶中，然后根据均等分布的原则给予行号。但是和NTILE不同的是，WIDTH_BUCKET函数试图创建等宽的桶，也就是按照值的范围均等分布，比如你的数据集合是一个钟形曲线(正态曲线，也就是处于两端的数据很少，处于中间的比较多),那么你可能希望中间桶的值分布多点，两端少点。用WIDTH_BUCKET就可以实现这个需求。

WIDTH_BUCKET函数能够对数值类型和日期类型进行操作，四个参数的解释如下：

   expr:生成桶的原始数据，日期或数值类型。

   min_value:1号桶的开始范围值。

max_value:最后N号桶的结束范围值。

num_buckets:需要建立的桶数N。
这个函数也就是根据min_value,max_value和num_buckets与原始值expr比较，看expr落入哪个桶中。如果expr在）之间，那么按max_value/num_buckets的范围均等分布桶数，如果不在这个之间，比如那么桶数为0，>=max_value,则桶数为N+1。

注意，这个函数没有over之类的东西。另外每个值对应的桶数是确定的，可能发生桶数断号的情况，比如:

drop table test;

create table test(num number);

insert into test values(0.1);

insert into test values(10);

insert into test values(20);

insert into test values(100);

insert into test values(200);

insert into test values(300);

insert into test values(400);

insert into test values(500);

insert into test values(10000);

insert into test values(20000);
commit;

测试1：

 

DINGJUN123>select num,width_bucket(num,1,500,5) from test order by num;

 

       NUM WIDTH_BUCKET(NUM,1,500,5)

---------- -------------------------

        .1                         0

        10                         1

        20                         1

       100                         1

       200                         2

       300                         3

       400                         4

       500                         6

     10000                         6

     20000                         6

  上面的num是对应的表达式，min_value=1,max_value=500,num_buckets=5，也就是num的范围如果在[1,500)之间，则最小为1号桶，最大为5号桶。超出范围，如果小于1，则为0，大于500则为6，按照max_value/num_buckets=100为段分为5个桶，对应范围是:

 

range	<1	[1,100]	(100,200]	(200,300]	(300,400]	(400,500)	>=500
#buckets	0	1	2	3	4	5	6

 
  上面SQL运行的结果，红色的为0桶，黄色及以下为6桶。这个函数可以放在任何允许有函数的地方，比如可以放在order by中(分析函数都可以放在order by中)。

DINGJUN123>select num,width_bucket(num,1,500,5) from test order by width_bucket(num,1,500,5);

 

       NUM WIDTH_BUCKET(NUM,1,500,5)

---------- -------------------------

        .1                         0

        10                         1

        20                         1

       100                         1

       200                         2

       300                         3

       400                         4

       500                         6

     10000                         6

     20000                         6

 

 

1.3.2.7 CUME_DIST and PERCENT_RANK

      

  CUME_DIST是计算一个值在结果集中的累积分布(这个分布是相对于位置的),总是返回(0,1]的值。必须要排序，对于排序有相同的，也就是rank相同，那么相同的值结果一致，以最大的排名算，计算公式是:cume_dist=（相同排序值的最大row_number）/记录总数。      PERCENT_RANK是按照(rank的排名-1)/(总数-1)计算百分比的排名。

 

DINGJUN123>select * from test

  2  ;

 

        ID        SAL

---------- ----------

         1         20

         2         20

         3         10

         4         10

         5         30

         6         10

 

已选择6行。

 

DINGJUN123>select id,sal,cume_dist() over(order by sal) cume,percent_rank() over(order by sal) p_rank,

  2  rank() over(order by sal) rank,row_number() over(order by sal) rn from test;

 

        ID        SAL       CUME     P_RANK       RANK         RN

---------- ---------- ---------- ---------- ---------- -----------------

         3         10         .5          0          1          1

         4         10         .5          0          1          2

         6         10                    1          3

         2         20 .833333333         .6          4          4

         1         20 .833333333         .6          4          5

         5         30          1          1          6          6

---