Oracle analytic function-percentile_cont

percentile_cont是连续分布模型的逆向函数。这个函数接收一个百分位数和一个排序说明,返回一个与输入的分布百分比值相对应的数据值,分布百分比的计算方法与percent_rank相同。Nulls在计算中被忽略。
如果输入的百分位数没有正好对应的数据值,就使用如下的算法来计算得到:
RN = 1+ (P*(N-1)) 其中P是输入的分布百分比值,N是组内的行数
CRN = CEIL(RN) FRN = FLOOR(RN)
If (CRN = FRN = RN) then the result is
(value of expression from row at RN)
Else the result is
(CRN – RN) * (value of expression for row at FRN) + (RN – FRN) * (value of expression for row at CRN)

当没有值与指定的percent_rank精确匹配的时候,percentile_cont(百分位数)会计算两个离得最近的值的平均值。

SQL> select first_name || ' ' || last_name as Name, salary
from employees
where department_id = 30 order by salary;

NAME                                               SALARY
---------------------------------------------- ----------
Karen Colmenares                                     2500
Guy Himuro                                           2600
Sigal Tobias                                         2800
Shelli Baida                                         2900
Alexander Khoo                                       3100
Den Raphaely                                        11000

SQL> select department_id,percentile_cont(0.5) within group (order by salary) percent_count_salary
from employees where department_id = 30
group by department_id;
DEPARTMENT_ID PERCENT_COUNT_SALARY
------------- --------------------
           30                 2850

计算步骤:
RN = 1 + (0.5 * (6-1)) = 3.5
CRN=4 FRN=3
计算结果为(4-3.5) * 2800 +(3.5-3) * 2900 = 1400 + 1450 = 2850

SQL> select first_name,last_name,salary,
percent_rank() over(partition by department_id order by salary)  percent_rank,
percentile_cont(0.5) within group (order by salary) over(partition bydepartment_id) percentile_cont
from employees
where department_id = 30;

FIRST_NAME           LAST_NAME                     SALARY PERCENT_RANK    PERCENTILE_CONT
-------------------- ------------------------- ---------- ---------------- -------------------
Karen                Colmenares                      2500                0           2850
Guy                  Himuro                          2600               .2           2850
Sigal                Tobias                          2800               .4           2850 --与计算的相同,为0.4和0.6分别对应的值的平均值
Shelli               Baida                           2900               .6           2850
Alexander            Khoo                            3100               .8           2850
Den                  Raphaely                       11000                1           2850

SQL> select first_name,last_name,department_id,salary,percent_rank() over(partition by department_id order by salary) percent_rank,
percentile_cont(0.7) within group (order by salary) over(partition bydepartment_id) percentile_cont
from employees
where department_id = 30;  

FIRST_NAME           LAST_NAME                 DEPARTMENT_ID     SALARY PERCENT_RANK    PERCENTILE_CONT
-------------------- ------------------------- ------------- ---------- ---------------- -------------------
Karen                Colmenares                           30       2500                0                3000
Guy                  Himuro                               30       2600               .2                3000
Sigal                Tobias                               30       2800               .4                3000
Shelli               Baida                                30       2900               .6                3000    ----(2900 + 3100)/2
Alexander            Khoo                                 30       3100               .8                3000
Den                  Raphaely                             30      11000                1                3000

SQL> select first_name,last_name,department_id,salary,percent_rank() over(partition by department_id order by salary) percent_rank,
percentile_cont(0.5) within group (order by salary) over(partition bydepartment_id) percentile_cont
from employees;

FIRST_NAME           LAST_NAME                 DEPARTMENT_ID     SALARY PERCENT_RANK    PERCENTILE_CONT
-------------------- ------------------------- ------------- ---------- ---------------- -------------------
Jennifer             Whalen                               10       4400                0                4400
Pat                  Fay                                  20       6000                0                9500
Michael              Hartstein                            20      13000                1                9500
Karen                Colmenares                           30       2500                0                2850
Guy                  Himuro                               30       2600               .2                2850
Sigal                Tobias                               30       2800               .4                2850
Shelli               Baida                                30       2900               .6                2850
Alexander            Khoo                                 30       3100               .8                2850
Den                  Raphaely                             30      11000                1                2850
Susan                Mavris                               40       6500                0                6500
TJ                   Olson                                50       2100                0                3100
Hazel                Philtanker                           50       2200       .022727273                3100
Steven               Markle                               50       2200       .022727273                3100
Ki                   Gee                                  50       2400       .068181818                3100
James                Landry                               50       2400       .068181818                3100
Joshua               Patel                                50       2500       .113636364                3100
Peter                Vargas                               50       2500       .113636364                3100
James                Marlow                               50       2500       .113636364                3100
Randall              Perkins                              50       2500       .113636364                3100
Martha               Sullivan                             50       2500       .113636364                3100
Donald               OConnell                             50       2600       .227272727                3100
Douglas              Grant                                50       2600       .227272727                3100
Randall              Matos                                50       2600       .227272727                3100
John                 Seo                                  50       2700       .295454545                3100
Irene                Mikkilineni                          50       2700       .295454545                3100
Girard               Geoni                                50       2800       .340909091                3100
Vance                Jones                                50       2800       .340909091                3100
Mozhe                Atkinson                             50       2800       .340909091                3100
Timothy              Gates                                50       2900       .409090909                3100
Michael              Rogers                               50       2900       .409090909                3100
Anthony              Cabrio                               50       3000       .454545455                3100
Kevin                Feeney                               50       3000       .454545455                3100
Curtis               Davies                               50       3100               .5                3100    --对应0.5存在
Alana                Walsh                                50       3100               .5                3100
Jean                 Fleaur                               50       3100               .5                3100
Winston              Taylor                               50       3200       .568181818                3100
Stephen              Stiles                               50       3200       .568181818                3100
Samuel               McCain                               50       3200       .568181818                3100
Julia                Nayer                                50       3200       .568181818                3100
Jason                Mallin                               50       3300       .659090909                3100
Laura                Bissot                               50       3300       .659090909                3100
Julia                Dellinger                            50       3400       .704545455                3100
Trenna               Rajs                                 50       3500       .727272727                3100
Jennifer             Dilly                                50       3600              .75                3100
Renske               Ladwig                               50       3600              .75                3100
Kelly                Chung                                50       3800       .795454545                3100
Britney              Everett                              50       3900       .818181818                3100
Sarah                Bell                                 50       4000       .840909091                3100
Alexis               Bull                                 50       4100       .863636364                3100
Nandita              Sarchand                             50       4200       .886363636                3100
Kevin                Mourgos                              50       5800       .909090909                3100
Shanta               Vollman                              50       6500       .931818182                3100
Payam                Kaufling                             50       7900       .954545455                3100
Matthew              Weiss                                50       8000       .977272727                3100
Adam                 Fripp                                50       8200                1                3100
Diana                Lorentz                              60       4200                0                4800
David                Austin                               60       4800              .25                4800
Valli                Pataballa                            60       4800              .25                4800
Bruce                Ernst                                60       6000              .75                4800
Alexander            Hunold                               60       9000                1                4800
Hermann              Baer                                 70      10000                0               10000
Sundita              Kumar                                80       6100                0                8900
Charles              Johnson                              80       6200        .03030303                8900
Amit                 Banda                                80       6200        .03030303                8900
Sundar               Ande                                 80       6400       .090909091                8900
David                Lee                                  80       6800       .121212121                8900
Sarath               Sewall                               80       7000       .151515152                8900
Oliver               Tuvault                              80       7000       .151515152                8900
Mattea               Marvins                              80       7200       .212121212                8900
Elizabeth            Bates                                80       7300       .242424242                8900
William              Smith                                80       7400       .272727273                8900
Nanette              Cambrault                            80       7500       .303030303                8900
Louise               Doran                                80       7500       .303030303                8900
Lindsey              Smith                                80       8000       .363636364                8900
Christopher          Olsen                                80       8000       .363636364                8900
Jack                 Livingston                           80       8400       .424242424                8900
Jonathon             Taylor                               80       8600       .454545455                8900
Alyssa               Hutton                               80       8800       .484848485                8900
Allan                McEwen                               80       9000       .515151515                8900
Peter                Hall                                 80       9000       .515151515                8900
Danielle             Greene                               80       9500       .575757576                8900
David                Bernstein                            80       9500       .575757576                8900
Patrick              Sully                                80       9500       .575757576                8900
Tayler               Fox                                  80       9600       .666666667                8900
Harrison             Bloom                                80      10000       .696969697                8900
Peter                Tucker                               80      10000       .696969697                8900
Janette              King                                 80      10000       .696969697                8900
Eleni                Zlotkey                              80      10500       .787878788                8900
Clara                Vishney                              80      10500       .787878788                8900
Gerald               Cambrault                            80      11000       .848484848                8900
Ellen                Abel                                 80      11000       .848484848                8900
Lisa                 Ozer                                 80      11500       .909090909                8900
Alberto              Errazuriz                            80      12000       .939393939                8900
Karen                Partners                             80      13500        .96969697                8900
John                 Russell                              80      14000                1                8900
Lex                  De Haan                              90      17000                0               17000
Neena                Kochhar                              90      17000                0               17000
Steven               King                                 90      24000                1               17000
Luis                 Popp                                100       6900                0                8000
Ismael               Sciarra                             100       7700               .2                8000
Jose Manuel          Urman                               100       7800               .4                8000   ---(7800 + 8200)/2
John                 Chen                                100       8200               .6                8000
Daniel               Faviet                              100       9000               .8                8000
Nancy                Greenberg                           100      12008                1                8000
William              Gietz                               110       8300                0               10154
Shelley              Higgins                             110      12008                1               10154
Kimberely            Grant                                         7000                0                7000
此条目发表在analytic function, SQL, Uncategorized分类目录。将固定链接加入收藏夹。

发表评论

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