Oracle analytic function-percentile_disc

语法:
PERCENTILE_DISC(expr) WITHIN GROUP (ORDER BY expr [ DESC | ASC ]) [ OVER (query_partition_clause) ]

函数返回一个与输入的分布百分比值相对应的数据值,分布百分比的计算方法同函数cume_dist(),如果没有正好对应的数据值,就取大于该分布百分比值的下一个值对应的值。

percentile_disc函数在功能上类似于percentile_cont函数,只是percentile_cont函数使用了连续分布模型,而percentile_disc函数使用了离期分布模型。

SQL> select department_id,salary,CUME_DIST() OVER (PARTITION BY department_id ORDER BY salary) Cume_Dist,
     percentile_disc(0.5) within group (order by salary) over(partition by department_id) percentile_dist_sal
from employees where department_id not in (50,80);  

DEPARTMENT_ID     SALARY  CUME_DIST PERCENTILE_DIST_SAL
------------- ---------- ---------- -------------------
           10       4400          1                4400
           20       6000         .5                6000    ----取cume_dist为0.5的数据值
           20      13000          1                6000
           30       2500 .166666667                2800
           30       2600 .333333333                2800
           30       2800         .5                2800
           30       2900 .666666667                2800
           30       3100 .833333333                2800
           30      11000          1                2800
           40       6500          1                6500
           60       4200         .2                4800
           60       4800         .6                4800
           60       4800         .6                4800
           60       6000         .8                4800
           60       9000          1                4800
           70      10000          1               10000
           90      17000 .666666667               17000
           90      17000 .666666667               17000
           90      24000          1               17000
          100       6900 .166666667                7800
          100       7700 .333333333                7800
          100       7800         .5                7800
          100       8200 .666666667                7800
          100       9000 .833333333                7800
          100      12008          1                7800
          110       8300         .5                8300
          110      12008          1                8300

27 rows selected.

SQL> select department_id,salary,CUME_DIST() OVER (PARTITION BY department_id
         ORDER BY salary) Cume_Dist,
         percentile_disc(0.6) within group (order by salary) over(partition by department_id) percentile_dist_sal
from employees 
where department_id not in (50,80);  
DEPARTMENT_ID     SALARY  CUME_DIST PERCENTILE_DIST_SAL
------------- ---------- ---------- -------------------
           10       4400          1                4400
           20       6000         .5               13000 --百分比为0.6的时候,没有对应的cume_dist的值,取大于该百分比的下一个cust_dist对应的数值
           20      13000          1               13000
           30       2500 .166666667                2900
           30       2600 .333333333                2900
           30       2800         .5                2900
           30       2900 .666666667                2900    --取2900
           30       3100 .833333333                2900
           30      11000          1                2900
           40       6500          1                6500
           60       4200         .2                4800
           60       4800         .6                4800
           60       4800         .6                4800
           60       6000         .8                4800
           60       9000          1                4800
           70      10000          1               10000
           90      17000 .666666667               17000
           90      17000 .666666667               17000
           90      24000          1               17000
          100       6900 .166666667                8200
          100       7700 .333333333                8200
          100       7800         .5                8200
          100       8200 .666666667                8200   --取8200
          100       9000 .833333333                8200
          100      12008          1                8200
          110       8300         .5               12008
          110      12008          1               12008

27 rows selected.
此条目发表在analytic function, SQL分类目录。将固定链接加入收藏夹。

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s