Oracle analytic function-corr

皮尔逊相关系数的数学公式请参考WIKI上关于Pearson correlation coefficient的内容。

Pearson相关系数是用协方差除以两个变量的标准差得到的,虽然协方差能反映两个随机变量的相关程度(协方差大于0的时候表示两者正相关,小于0的时候表示两者负相关),但是协方差值的大小并不能很好地度量两个随机变量的关联程度,例如,现在二维空间中分布着一些数据,我们想知道数据点坐标X轴和Y轴的相关程度,如果X与Y的相关程度较小但是数据分布的比较离散,这样会导致求出的协方差值较大,用这个值来度量相关程度是不合理的。为了更好的度量两个随机变量的相关程度,引入了Pearson相关系数,其在协方差的基础上除以了两个随机变量的标准差,容易得出,pearson是一个介于-1和1之间的值,当两个变量的线性关系增强时,相关系数趋于1或-1;当一个变量增大,另一个变量也增大时,表明它们之间是正相关的,相关系数大于0;如果一个变量增大,另一个变量却减小,表明它们之间是负相关的,相关系数小于0;如果相关系数等于0,表明它们之间不存在线性相关关系。

上述的描述是比较难懂的,简单一点的描述是
cos[a,b] = a * b / |a|*|b| 皮尔逊系数就是cos计算之前两个向量都先搞个标准化,计算a,b两个向量的夹角的cos值。

当两个变量的标准差都不为零时,相关系数才有定义,皮尔逊相关系数适用于:
(1)、两个变量之间是线性关系,都是连续数据。
(2)、两个变量的总体是正态分布,或接近正态的单峰分布。
(3)、两个变量的观测值是成对的,每对观测值之间相互独立。

CORR函数一对表达式的相关系数,计算的理论基于皮尔逊相关系数,排除掉expr1或expr2为null的数值,基于下面的计算公式计算
COVAR_POP(expr1, expr2) / (STDDEV_POP(expr1) * STDDEV_POP(expr2))
函数返回值为NUMBER类型。如果函数被用于空集则函数返回NULL。

聚合功能
SQL> SELECT weight_class, CORR(list_price, min_price) "Correlation"
  2    FROM product_information
  3    GROUP BY weight_class
  4    ORDER BY weight_class, "Correlation";

WEIGHT_CLASS Correlation
------------ -----------
           1  .999149795
           2  .999022941
           3  .998484472
           4  .999359909
           5  .999536087

随着对参与运算的两个数据样本点之间的数值相差越来越大,相关系数不断变小,最终为负数,不相关。

分析功能
计算员工在公司时间和员工职位薪水之间的相关性。
SQL> SELECT employee_id,
  2         job_id,
  3         TO_CHAR((SYSDATE - hire_date) YEAR TO MONTH) "Yrs-Mns",
  4         salary,
  5         CORR(SYSDATE - hire_date, salary) OVER(PARTITION BY job_id) AS "Correlation"
  6    FROM employees
  7   WHERE department_id in (50,80)
  8   ORDER BY job_id, employee_id;

EMPLOYEE_ID JOB_ID               Yrs-Mns            SALARY Correlation
----------- -------------------- -------------- ---------- -----------
        145 SA_MAN               +12-08              14000  .912385598
        146 SA_MAN               +12-05              13500  .912385598
        147 SA_MAN               +12-03              12000  .912385598
        148 SA_MAN               +09-08              11000  .912385598
        149 SA_MAN               +09-04              10500  .912385598
        150 SA_REP               +12-04              10000   .80436755
        151 SA_REP               +12-02               9500   .80436755
        152 SA_REP               +11-10               9000   .80436755
        153 SA_REP               +11-02               8000   .80436755
        154 SA_REP               +10-06               7500   .80436755
        155 SA_REP               +09-06               7000   .80436755
发表在 analytic function, SQL | 留下评论

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 | 一条评论