Oracle analytic function-covar_pop,covar_samp

covar_samp

COVAR_SAMP计算一对表达式的样本协方差,可以用于聚合或分析函数。
Oracle在这个函数上的实现方式为:首先淘汰掉expr1或expr2是NULL的数据,然后采用下面的计算公式
(SUM(expr1 * expr2) – SUM(expr1) * SUM(expr2) / n) / (n-1) –原公式做因式分解获得
其中n是(‪expr1‬, ‪expr2‬)对的个数,此处expr1或expr2都不为null。‬‬‬‬‬‬‬‬

covar_pop

Covar_pop返回一对表达式对总体协方差。可以当作聚合函数和分析函数使用。
Oracle在这个函数上的实现方式为:首先淘汰掉expr1或expr2是NULL的数据,然后采用下面的计算公式
(SUM(expr1 * expr2) – SUM(expr1) * SUM(expr2) / n) / n –原公式做因式分解获得
其中n是(‪expr1‬, ‪expr2‬)对的个数,此处expr1或expr2都不为null。‬‬‬‬‬‬‬‬

SQL> select department_id,
  2         covar_pop(sysdate - hire_date, salary) "covar_pop",
  3         covar_samp(sysdate - hire_date, salary) "covar_samp"
  4    from employees
  5   where department_id <= 40
  6   group by department_id;

DEPARTMENT_ID  covar_pop covar_samp
------------- ---------- ----------
           10          0
           20     957250    1914500
           30 1258261.11 1509913.33
           40          0

分析功能

SQL> SELECT product_id,
  2         supplier_id,
  3         COVAR_POP(list_price, min_price) OVER(ORDER BY product_id, supplier_id) AS COVAR_POP,
  4         COVAR_SAMP(list_price, min_price) OVER(ORDER BY product_id, supplier_id) AS COVAR_SAMP
  5    FROM product_information p
  6   WHERE category_id = 29
  7   ORDER BY product_id, supplier_id;

PRODUCT_ID SUPPLIER_ID  COVAR_POP COVAR_SAMP
---------- ----------- ---------- ----------
      1774      103088          0
      1775      103087    1473.25     2946.5
      1794      103096 1702.77778 2554.16667
      1825      103093    1926.25 2568.33333
      2004      103086     1591.4    1989.25
      2005      103086     1512.5       1815
      2416      103088 1475.97959 1721.97619
      2417      103088 1478.70313 1689.94643
      2449      103088  1326.8642 1492.72222
      3101      103086     1195.2       1328
      3170      103089 1590.07438 1749.08182
      3171      103089    1718.25 1874.45455
      3172      103094 1710.42012 1852.95513
      3173      103094 1588.56122 1710.75824
      3175      103089     1593.4 1707.21429
发表在 analytic function, SQL | 留下评论

Oracle analytic function-stddev,stddev_samp,stddev_pop

stddev

STDDEV返回expr的样本标准差(sample standard deviation),这个函数可以被用于聚合函数也可以被用于分析函数。当expr只有一行输入的时候STDDEV函数返回0,STDDEV_SAMP返回为null。
Oracle计算标准差的公式为VARIANCE聚合函数的方差的算术平方根。
当函数语法中指定了DISTINCT,那么在analytic_clause部分值可以使用query_partition_clause语句,ORDER BY 和开窗语句是不允许使用的。

stddev_pop

STDDEV_POP计算总体标准差(population standard deviation)并返回总体方差的算术平方根。这个函数可以被用于聚合和分析函数。
这个函数与VAR_POP函数的算术平方根相同,当VAR_POP返回为null的时候STDEDEV_POP也返回null。

stddev_samp

STDDEV_SAMP计算累计样本标准差(cumulative sample standard deviation),返回样本方差的算术平方根。这个函数可以是聚合功能也可以是分析功能。
STDDEV_SAMP函数与VAR_SAMP函数的算术平方根相同。当VAR_SAMP返回null的时候stddev_samp也返回null。

三个函数对比

聚合函数
计算employees表salary列的样本标准差、总体标准差和样本累积标准差。
SQL> select stddev(salary) "stddev",
  2         stddev_pop(salary) "stddev_pop",
  3         stddev_samp(salary) "stddev_samp"
  4    from employees;

    stddev stddev_pop stddev_samp
---------- ---------- -----------
3915.74258 3897.40176  3915.74258

分析函数
SQL> select department_id,
  2         last_name,
  3         salary,
  4         stddev(salary) over (partition by department_id order by hire_date) "stddev", --样本标准差
  5         stddev_pop(salary) over (partition by department_id order by hire_date) "stddev_pop",--总体标准差
  6         stddev_samp(salary) over (partition by department_id order by hire_date) "stddev_samp" --累积样本标准差
  7  from employees where department_id <= 40;

DEPARTMENT_ID LAST_NAME                                              SALARY     stddev stddev_pop stddev_samp
------------- -------------------------------------------------- ---------- ---------- ---------- -----------
           10 Whalen                                                   4400          0          0
           20 Hartstein                                               13000          0          0
           20 Fay                                                      6000 4949.74747       3500  4949.74747
           30 Raphaely                                                11000          0          0
           30 Khoo                                                     2600 5939.69696       4200  5939.69696
           30 Tobias                                                   2600 4849.74226 3959.79797  4849.74226
           30 Baida                                                    2900 4152.40894 3596.09163  4152.40894
           30 Himuro                                                   2600 3725.31878 3332.02641  3725.31878
           30 Colmenares                                               2500 3415.65026 3118.04782  3415.65026
           40 Mavris                                                   6500          0          0
发表在 analytic function, SQL | 留下评论

Oracle analytic function-variance,var_samp,var_pop

variance

Variance计算expr的方差。
函数中当expr的个数为1的时候(即求方差的元素只有1个时候),variance返回为0.
当expr > 1的时候,variance等同于var_samp.

SQL> select count(*) from employees where department_id = 10;

  COUNT(*)
----------
         1

SQL> SELECT VARIANCE(salary) "Variance" from employees where department_id = 10;   --当元素个数为1的时候

  Variance
----------
         0

var_samp

Var_samp返回在数据集中丢弃了nulls之后的数据集合的样本方差。如果这个函数被用于一个空集合,那么函数将返回null。
函数的计算方式为:
(SUM(expr – (SUM(expr) / COUNT(expr)))2) / (COUNT(expr) – 1)

当输入的数据集的个数为1个元素的时候,variance函数返回为0,var_samp返回为null。当数据集个数大于1个元素的时候,var_samp和variance相同。

var_pop

Var_pop返回数据集中丢弃了nulls之后的总体方差,对于空集合,var_pop函数返回null。
函数的计算公式为
SUM((expr – (SUM(expr) / COUNT(expr)))2) / COUNT(expr)

函数对比

SQL> select department_id,
  2         last_name,
  3         salary,
  4         variance(salary) over(partition by department_id order by hire_date) "Variance",
  5         var_samp(salary) over(partition by department_id order by hire_date) "Var_samp",
  6         var_pop(salary) over(partition by department_id order by hire_date) "Var_pop"
  7    from employees
  8   where department_id <= 40;

DEPARTMENT_ID LAST_NAME                                              SALARY   Variance   Var_samp    Var_pop
------------- -------------------------------------------------- ---------- ---------- ---------- ----------
           10 Whalen                                                   4400          0                     0
           20 Hartstein                                               13000          0                     0
           20 Fay                                                      6000   24500000   24500000   12250000
           30 Raphaely                                                11000          0                     0
           30 Khoo                                                     2600   35280000   35280000   17640000
           30 Tobias                                                   2600   23520000   23520000   15680000
           30 Baida                                                    2900   17242500   17242500   12931875
           30 Himuro                                                   2600   13878000   13878000   11102400
           30 Colmenares                                               2500 11666666.7 11666666.7 9722222.22
           40 Mavris                                                   6500          0                     0
发表在 analytic function, SQL | 留下评论

select unpivot

前面的文章对pivot的使用进行了测试,本文对unpivot进行测试。
语法:
SELECT …
FROM …
UNPIVOT [INCLUDE NULLS|EXCLUDE NULLS]
( unpivot_clause
unpivot_for_clause
unpivot_in_clause )
WHERE …

使用unpivot来做列转行,默认情况unpivot是exclude nulls。

SQL> with t as (select department_id,
  2         sum(decode(job_id, 'AD_ASST', salary, '')) as AD_ASST,
  3         sum(decode(job_id, 'MK_MAN', salary, '')) as MK_MAN,
  4         sum(decode(job_id, 'MK_REP', salary, '')) as MK_REP,
  5         sum(decode(job_id, 'PU_CLERK', salary, '')) as PU_CLERK,
  6         sum(decode(job_id, 'PU_MAN', salary, '')) as PU_MAN,
  7         sum(decode(job_id, 'HR_REP', salary, '')) as HR_REP
  8    from employees
  9   where department_id < = 40
 10   group by department_id
 11   )
 12   select * From t
 13   unpivot(
 14   total_salary
 15   for job_id in (
 16      AD_ASST as 'AD_ASST',
 17      MK_MAN as 'MK_MAN',
 18      MK_REP as 'MK_REP',
 19      PU_CLERK as 'PU_CLERK',
 20      PU_MAN as 'PU_MAN',
 21      HR_REP as 'HR_REP'
 22       )
 23  ) order by 1;

DEPARTMENT_ID JOB_ID           TOTAL_SALARY
------------- ---------------- ------------
           10 AD_ASST                  4400
           20 MK_MAN                  13000
           20 MK_REP                   6000
           30 PU_CLERK                13900
           30 PU_MAN                  11000
           40 HR_REP                   6500

已选择6行。

执行计划

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                        | E-Rows |E-Bytes| Cost (%CPU)|  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                             |        |       |    15 (100)|       |       |          |
|   1 |  TEMP TABLE TRANSFORMATION     |                             |        |       |            |       |       |          |
|   2 |   LOAD AS SELECT               |                             |        |       |            |   270K|   270K|  270K (0)|
|   3 |    SORT GROUP BY NOSORT        |                             |      1 |    16 |     2   (0)|       |       |          |
|   4 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEES                   |     10 |   160 |     2   (0)|       |       |          |
|*  5 |      INDEX RANGE SCAN          | EMP_DEPARTMENT_IX           |     10 |       |     1   (0)|       |       |          |
|   6 |   SORT ORDER BY                |                             |      6 |   192 |    13   (8)|  2048 |  2048 | 2048  (0)|
|*  7 |    VIEW                        |                             |      6 |   192 |    12   (0)|       |       |          |
|   8 |     UNPIVOT                    |                             |        |       |            |       |       |          |
|   9 |      VIEW                      |                             |      1 |    91 |     2   (0)|       |       |          |
|  10 |       TABLE ACCESS FULL        | SYS_TEMP_0FD9D66E0_260513AC |      1 |    16 |     2   (0)|       |       |          |
-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("DEPARTMENT_ID"<=40)
   7 - filter("unpivot_view_015"."TOTAL_SALARY" IS NOT NULL)   -----exclude nulls

增加include null,结果集是count(department_id)  * COUNT(distinct JOB_ID) = 4 * 6 = 24

SQL> with t as (select department_id,
  2         sum(decode(job_id, 'AD_ASST', salary, '')) as AD_ASST,
  3         sum(decode(job_id, 'MK_MAN', salary, '')) as MK_MAN,
  4         sum(decode(job_id, 'MK_REP', salary, '')) as MK_REP,
  5         sum(decode(job_id, 'PU_CLERK', salary, '')) as PU_CLERK,
  6         sum(decode(job_id, 'PU_MAN', salary, '')) as PU_MAN,
  7         sum(decode(job_id, 'HR_REP', salary, '')) as HR_REP
  8    from employees
  9   where department_id < = 40
 10   group by department_id
 11   order by 1)
 12   select * From t
 13   unpivot include nulls(
 14   total_salary
 15   for job_id in (
 16      AD_ASST as 'AD_ASST',
 17      MK_MAN as 'MK_MAN',
 18      MK_REP as 'MK_REP',
 19      PU_CLERK as 'PU_CLERK',
 20      PU_MAN as 'PU_MAN',
 21      HR_REP as 'HR_REP'
 22       )
 23  ) order by 1;

DEPARTMENT_ID JOB_ID           TOTAL_SALARY
------------- ---------------- ------------
           10 PU_MAN
           10 PU_CLERK
           10 MK_REP
           10 MK_MAN
           10 AD_ASST                  4400
           10 HR_REP
           20 PU_MAN
           20 PU_CLERK
           20 MK_REP                   6000
           20 MK_MAN                  13000
           20 AD_ASST
           20 HR_REP
           30 AD_ASST
           30 MK_MAN
           30 MK_REP
           30 PU_CLERK                13900
           30 PU_MAN                  11000
           30 HR_REP
           40 AD_ASST
           40 MK_MAN
           40 MK_REP
           40 PU_CLERK
           40 PU_MAN
           40 HR_REP                   6500

已选择24行。

执行计划

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                        | E-Rows |E-Bytes| Cost (%CPU)|  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                             |        |       |    15 (100)|       |       |          |
|   1 |  TEMP TABLE TRANSFORMATION     |                             |        |       |            |       |       |          |
|   2 |   LOAD AS SELECT               |                             |        |       |            |   270K|   270K|  270K (0)|
|   3 |    SORT GROUP BY NOSORT        |                             |      1 |    16 |     2   (0)|       |       |          |
|   4 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEES                   |     10 |   160 |     2   (0)|       |       |          |
|*  5 |      INDEX RANGE SCAN          | EMP_DEPARTMENT_IX           |     10 |       |     1   (0)|       |       |          |
|   6 |   SORT ORDER BY                |                             |      6 |   192 |    13   (8)|  2048 |  2048 | 2048  (0)|
|   7 |    VIEW                        |                             |      6 |   192 |    12   (0)|       |       |          |
|   8 |     UNPIVOT                    |                             |        |       |            |       |       |          |
|   9 |      VIEW                      |                             |      1 |    91 |     2   (0)|       |       |          |
|  10 |       TABLE ACCESS FULL        | SYS_TEMP_0FD9D66E2_260513AC |      1 |    16 |     2   (0)|       |       |          |
-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("DEPARTMENT_ID"<=40)
发表在 SQL | 留下评论

Banker’s rounding-plsql function

本文在Oracle数据库中用pl/sql实现银行家算法的四舍六入五取偶。即:若需要舍入到的位的后面”小于5″或”大于5″的话,按通常意义的四舍五入处理.若”若需要舍入到的位的后面”等于5″,则要看舍入后末位为偶数还是奇数,偶数时直接舍弃,基数时进位。

create or replace function fn_bankers_round(val number, rnd number := 0)
  return number is
  v_rnd number;
  v_tmp number;
begin
  v_rnd := trunc(rnd);
  v_tmp := (trunc(val,v_rnd + 1) - trunc(val, v_rnd)) *
                 power(10, v_rnd + 1);
  if Abs(v_tmp) < 5 or
     (Abs(v_tmp) = 5 and
      mod(trunc(val * power(10, v_rnd)), 2) = 0) then
    return trunc(val, v_rnd);
  else
    return round(val, v_rnd);
  end if;
end;
/

输出结果

SQL> select fn_bankers_round(0.378453113,4) from dual;

FN_BANKERS_ROUND(0.378453113,4)
-------------------------------
                          .3784

SQL> select fn_bankers_round(0.378553113,4) from dual;

FN_BANKERS_ROUND(0.378553113,4)
-------------------------------
                          .3786

SQL> select fn_bankers_round(0.378443113,4) from dual;

FN_BANKERS_ROUND(0.378443113,4)
-------------------------------
                          .3784

SQL> select fn_bankers_round(0.378463113,4) from dual;

FN_BANKERS_ROUND(0.378463113,4)
-------------------------------
                          .3785

SQL> select fn_bankers_round(0.378433113,4) from dual;

FN_BANKERS_ROUND(0.378433113,4)
-------------------------------
                          .3784
发表在 algorithm, PLSQL | 留下评论

select pivot

从Oracle 11.1开始,oracle在SELECT中引入了pivot和unpivot两个语法。
按照官方文档的说法,PIVOT是将ROWS转换成COLUMNS,UNPIVOT是将COLUMNS转换成ROWS。
本文针对pivot语法进行测试,测试环境为11.2.0.4,使用Oracle Demo用户HR的employees表。
函数的语法如下
SELECT …
FROM …
PIVOT [XML]
( pivot_clause
pivot_for_clause
pivot_in_clause
)
WHERE …

语法说明
Pivot_clause用于声明参与聚合的列;
Pivot_for_clause定义被分组或PIVOT的列;
Pivot_in_clause定义在pivot_for_clause中列的过滤条件,在pivot_in_clause中对每个值的聚合都将被变换为分别的列。

原始数据

SQL> select department_id,manager_id,job_id,salary from  employees where department_id <= 40;

DEPARTMENT_ID MANAGER_ID JOB_ID                   SALARY
------------- ---------- -------------------- ----------
           10        101 AD_ASST                    4400
           20        100 MK_MAN                    13000
           20        201 MK_REP                     6000
           30        100 PU_MAN                    11000
           30        114 PU_CLERK                   2600
           30        114 PU_CLERK                   2900
           30        114 PU_CLERK                   3100
           30        114 PU_CLERK                   2800
           30        114 PU_CLERK                   2500
           40        101 HR_REP                     6500
已选择10行。

现在查询各部门各JOB类型的总薪水。

SQL> select department_id, job_id, sum(salary) as sum_salary
  2    from employees
  3   where department_id < = 40
  4   group by department_id, job_id
  5   order by 1,2
  6  ;

DEPARTMENT_ID JOB_ID               SUM_SALARY
------------- -------------------- ----------
           10 AD_ASST                    4400
           20 MK_MAN                    13000
           20 MK_REP                     6000
           30 PU_CLERK                  13900
           30 PU_MAN                    11000
           40 HR_REP                     6500

现在如果我们想要展示每个JOB类型都作为一列显示的话会更加直观一些,在不适用pivot的前提下需要适用DECODE函数来实现。

SQL> select department_id,
  2         sum(decode(job_id, 'AD_ASST', salary, '')) as AD_ASST,
  3         sum(decode(job_id, 'MK_MAN', salary, '')) as MK_MAN,
  4         sum(decode(job_id, 'MK_REP', salary, '')) as MK_REP,
  5         sum(decode(job_id, 'PU_CLERK', salary, '')) as PU_CLERK,
  6         sum(decode(job_id, 'PU_MAN', salary, '')) as PU_MAN,
  7         sum(decode(job_id, 'HR_REP', salary, '')) as HR_REP
  8    from employees
  9   where department_id < = 40
 10   group by department_id
 11   order by 1;

DEPARTMENT_ID    AD_ASST     MK_MAN     MK_REP   PU_CLERK     PU_MAN     HR_REP
------------- ---------- ---------- ---------- ---------- ---------- ----------
           10       4400
           20                 13000       6000
           30                                       13900      11000
           40                                                              6500

使用pivot来实现

SQL> select * from
  2    (select department_id,job_id,salary from employees where department_id <= 40)
  3  pivot(
  4       sum(salary)     --pivot_clause
  5       for job_id      --pivot_for_clause
  6       in (            --filter
  7      'AD_ASST' as AD_ASST,
  8      'MK_MAN' as MK_MAN,
  9      'MK_REP' as MK_REP,
 10      'PU_CLERK' as PU_CLERK,
 11      'PU_MAN' as PU_MAN,
 12      'HR_REP' as HR_REP
 13       )
 14  )
 15  order by 1;

DEPARTMENT_ID    AD_ASST     MK_MAN     MK_REP   PU_CLERK     PU_MAN     HR_REP
------------- ---------- ---------- ---------- ---------- ---------- ----------
           10       4400
           20                 13000       6000
           30                                       13900      11000
           40                                                              6500

执行计划
Plan hash value: 2508337833

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | E-Rows |E-Bytes| Cost (%CPU)|
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |        |       |     2 (100)|
|   1 |  SORT GROUP BY NOSORT PIVOT  |                   |      1 |    16 |     2   (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |     10 |   160 |     2   (0)|
|*  3 |    INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |     10 |       |     1   (0)|
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("DEPARTMENT_ID" with pivot_employees as (select department_id,job_id,salary from employees where department_id <= 40)
  2  select * from  pivot_employees
  3  pivot(
  4       sum(salary) as salary     --别名追加到列名后面
  5       for job_id
  6       in (
  7      'AD_ASST' as AD_ASST,
  8      'MK_MAN' as MK_MAN,
  9      'MK_REP' as MK_REP,
 10      'PU_CLERK' as PU_CLERK,
 11      'PU_MAN' as PU_MAN,
 12      'HR_REP' as HR_REP
 13       )
 14  )
 15  order by 1;

DEPARTMENT_ID AD_ASST_SALARY MK_MAN_SALARY MK_REP_SALARY PU_CLERK_SALARY PU_MAN_SALARY HR_REP_SALARY
------------- -------------- ------------- ------------- --------------- ------------- -------------
           10           4400
           20                        13000          6000
           30                                                      13900         11000
           40                                                                                   6500

下面对pivot输入sum和count两列

SQL> with pivot_employees as (select department_id,job_id,salary from employees where department_id <= 40)
  2  select * from  pivot_employees
  3  pivot(
  4       sum(salary) as s,
  5       count(salary) as c
  6       for job_id
  7       in (
  8      'AD_ASST' as AD_ASST,
  9      'MK_MAN' as MK_MAN,
 10      'MK_REP' as MK_REP,
 11      'PU_CLERK' as PU_CLERK,
 12      'PU_MAN' as PU_MAN,
 13      'HR_REP' as HR_REP
 14       )
 15  )
 16  order by 1;

DEPARTMENT_ID  AD_ASST_S  AD_ASST_C   MK_MAN_S   MK_MAN_C   MK_REP_S   MK_REP_C PU_CLERK_S PU_CLERK_C   PU_MAN_S   PU_MAN_C  HR_REP_S HR_REP_C
------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------- ---------
           10       4400          1                     0                     0                     0                     0                0
           20                     0      13000          1       6000          1                     0                     0                0
           30                     0                     0                     0      13900          5      11000          1                0
           40                     0                     0                     0                     0                     0      6500      1

上述实验中对JOB_ID的列举使用了IN,当枚举值较多的时候,子查询获取子集的方式在pivot的使用中不受支持,但是oracle支持返回为xml格式的数据。

SQL> col JOB_ID_XML for a100
SQL> select * from
  2    (select department_id,job_id,salary from employees where department_id <= 40)
  3  pivot xml(
  4       sum(salary)     
  5       for job_id      
  6       in (any)
  7       )
  8  order by 1;
发表在 SQL | 一条评论

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 | 留下评论