Oracle analytic function-cume_dist

函数语法如下:
聚合函数的语法
CUME_DIST (expression1, … expression_n) WITHIN GROUP (ORDER BY expression_order1, … expression_order_n)

分析函数的语法
CUME_DIST() OVER ([ query_partition_clause ] order_by_clause)

数据的累积分布,也即小于等于当前数据值的所有数据的概率分布,对于表示数据点在某个区间内出现的概率有很大的帮助。

累积分布函数为Cumulative Distribution Function, 简称CDF。

cume_dist()计算某个值在一组值中的累计分布,为相对位置/总行数,对于重复行,计算的时候取重复行中的最后一行的位置,返回值为(0,1]之间。这个函数可以使用数值数据类型也可以使用非数值类型作为参数,原因是函数会对非数值类型的参数进行隐式转换。

cume_dist()函数在聚合函数的用途中,用参数中的指定的数据构造一条假定的数据并插入到现存行中,然后计算这条假定数据在所有行中的相对位置。

聚合函数的例1:
下面的实验中,原始数据如下

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

NAME                                                                                             SALARY
-------------------------------------------------------------------------------------------- ----------
Karen Colmenares                                                                                   2500
Guy Himuro                                                                                         2600
Alexander Khoo                                                                                     2600
Sigal Tobias                                                                                       2600
Shelli Baida                                                                                       2900
Den Raphaely                                                                                      11000
已选择6行。

当计算薪水值2500.00在部门中的累计分布时,当2500.00加入到记录中后所谓的位置为第2行。Cume_dist计算的结果为2/7= 0.2857142857

SQL> select cume_dist(2500.00) within group(order by salary) as cume_dist_sal_2500
  2  from employees
  3  where department_id = 30;

CUME_DIST_SAL_2500
------------------
        .285714286
计算薪水值为2500在组内的累计分布情况。

聚合函数例2

SQL> select first_name ||' '|| last_name as Name, salary,commission_pct
  2    from employees
  3   where department_id = 80
  4   order by 2 desc;

NAME                                                                                             SALARY COMMISSION_PCT
-------------------------------------------------------------------------------------------- ---------- --------------
John Russell                                                                                      14000             .4
Karen Partners                                                                                    13500             .3
Alberto Errazuriz                                                                                 12000             .3
Lisa Ozer                                                                                         11500            .25
Ellen Abel                                                                                        11000             .3
Gerald Cambrault                                                                                  11000             .3
Eleni Zlotkey                                                                                     10500             .2
Clara Vishney                                                                                     10500            .25
Harrison Bloom                                                                                    10000             .2
Peter Tucker                                                                                      10000             .3
Janette King                                                                                      10000            .35
Tayler Fox                                                                                         9600             .2
David Bernstein                                                                                    9500            .25
Patrick Sully                                                                                      9500            .35
Danielle Greene                                                                                    9500            .15
Peter Hall                                                                                         9000            .25
Allan McEwen                                                                                       9000            .35
Alyssa Hutton                                                                                      8800            .25
Jonathon Taylor                                                                                    8600             .2
Jack Livingston                                                                                    8400             .2
Christopher Olsen                                                                                  8000             .2
Lindsey Smith                                                                                      8000             .3
Nanette Cambrault                                                                                  7500             .2
Louise Doran                                                                                       7500             .3
William Smith                                                                                      7400            .15
Elizabeth Bates                                                                                    7300            .15
Mattea Marvins                                                                                     7200             .1
Oliver Tuvault                                                                                     7000            .15
Sarath Sewall                                                                                      7000            .25
David Lee                                                                                          6800             .1
Sundar Ande                                                                                        6400             .1
Charles Johnson                                                                                    6200             .1
Amit Banda                                                                                         6200             .1
Sundita Kumar                                                                                      6100             .1

已选择34行。

SQL> select cume_dist(7500.00, 0.20) within group(order by salary, commission_pct) as cume_dist_sal_7500
  2    from employees
  3   where department_id = 80;

CUME_DIST_SAL_7500
------------------
        .342857143
上述的SQL反应的结果:薪水值为7500,commission_pct为0.20的员工在部门ID为80的组内的累计分布。

分析函数例1:

SQL> col first_name for a20
SQL> col last_name for a20
SQL>
SQL> select first_name,last_name,salary,cume_dist() over(order by salary desc) as cume_dist_sal_analytic
  2  from employees
  3  where department_id = 80;

FIRST_NAME           LAST_NAME                SALARY CUME_DIST_SAL_ANALYTIC
-------------------- -------------------- ---------- ----------------------
John                 Russell                   14000             .029411765
Karen                Partners                  13500             .058823529
Alberto              Errazuriz                 12000             .088235294
Lisa                 Ozer                      11500             .117647059
Ellen                Abel                      11000             .176470588
Gerald               Cambrault                 11000             .176470588
Eleni                Zlotkey                   10500             .235294118
Clara                Vishney                   10500             .235294118
Harrison             Bloom                     10000             .323529412
Peter                Tucker                    10000             .323529412
Janette              King                      10000             .323529412
Tayler               Fox                        9600             .352941176
David                Bernstein                  9500             .441176471
Patrick              Sully                      9500             .441176471
Danielle             Greene                     9500             .441176471
Peter                Hall                       9000                     .5
Allan                McEwen                     9000                     .5
Alyssa               Hutton                     8800             .529411765
Jonathon             Taylor                     8600             .558823529
Jack                 Livingston                 8400             .588235294
Christopher          Olsen                      8000             .647058824
Lindsey              Smith                      8000             .647058824
Nanette              Cambrault                  7500             .705882353
Louise               Doran                      7500             .705882353
William              Smith                      7400             .735294118
Elizabeth            Bates                      7300             .764705882
Mattea               Marvins                    7200             .794117647
Oliver               Tuvault                    7000             .852941176
Sarath               Sewall                     7000             .852941176
David                Lee                        6800             .882352941
Sundar               Ande                       6400             .911764706
Charles              Johnson                    6200             .970588235
Amit                 Banda                      6200             .970588235
Sundita              Kumar                      6100                      1

已选择34行。
上述语句计算每行的salary的累计分布。

分析函数例2:

SQL> select job_id,first_name,last_name,salary,cume_dist() over(partition by job_id order by salary) as cume_dist_sal_analytic
  2  from employees
  3  where department_id = 80
  4  order by job_id, last_name, salary,cume_dist_sal_analytic;
JOB_ID               FIRST_NAME                               LAST_NAME                       SALARY CUME_DIST_SAL_ANALYTIC
-------------------- ---------------------------------------- --------------------------- ---------- ----------------------
SA_MAN               Gerald                                   Cambrault                        11000                     .4
SA_MAN               Alberto                                  Errazuriz                        12000                     .6
SA_MAN               Karen                                    Partners                         13500                     .8
SA_MAN               John                                     Russell                          14000                      1
SA_MAN               Eleni                                    Zlotkey                          10500                     .2
SA_REP               Ellen                                    Abel                             11000             .965517241
SA_REP               Sundar                                   Ande                              6400             .137931034
SA_REP               Amit                                     Banda                             6200             .103448276
SA_REP               Elizabeth                                Bates                             7300             .310344828
SA_REP               David                                    Bernstein                         9500              .75862069
SA_REP               Harrison                                 Bloom                            10000             .896551724
SA_REP               Nanette                                  Cambrault                         7500             .413793103
SA_REP               Louise                                   Doran                             7500             .413793103
SA_REP               Tayler                                   Fox                               9600             .793103448
SA_REP               Danielle                                 Greene                            9500              .75862069 
SA_REP               Peter                                    Hall                              9000             .655172414
SA_REP               Alyssa                                   Hutton                            8800             .586206897
SA_REP               Charles                                  Johnson                           6200             .103448276
SA_REP               Janette                                  King                             10000             .896551724
SA_REP               Sundita                                  Kumar                             6100             .034482759
SA_REP               David                                    Lee                               6800             .172413793
SA_REP               Jack                                     Livingston                        8400             .517241379
SA_REP               Mattea                                   Marvins                           7200             .275862069
SA_REP               Allan                                    McEwen                            9000             .655172414
SA_REP               Christopher                              Olsen                             8000             .482758621
SA_REP               Lisa                                     Ozer                             11500                      1
SA_REP               Sarath                                   Sewall                            7000              .24137931
SA_REP               William                                  Smith                             7400             .344827586
SA_REP               Lindsey                                  Smith                             8000             .482758621
SA_REP               Patrick                                  Sully                             9500              .75862069
SA_REP               Jonathon                                 Taylor                            8600             .551724138
SA_REP               Peter                                    Tucker                           10000             .896551724
SA_REP               Oliver                                   Tuvault                           7000              .24137931
SA_REP               Clara                                    Vishney                          10500             .931034483

根据上述的查询结果,SA_REP工作类型的员工中,有75.86%的人薪水低于或等于Greene的薪水(9500)。

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

One Response to Oracle analytic function-cume_dist

  1. Pingback引用通告: Oracle analytic function-percent_rank | LEO Notes

发表评论

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