Oracle 技能强化 Part 11 报表和数据仓库运算

1.行转列

做报表或语句改写时经常用“行转列”语句（CASE WHEN END 语句 / PIVOT函数）。

(1) CASE WHEN END 的用法

SELECT job as position,
       case deptno when 10 then sal end as department_10_salary,
       case deptno when 20 then sal end as department_20_salary,
       case deptno when 30 then sal end as department_30_salary,
       sal as sum_salary
  FROM emp
 ORDER BY 1;

 看到上图发现，其实我们可以按照job进行分组汇总，"行转列"语句里会有聚集函数，目的是将同类书籍转为一行显示。这使得报表显得更有序一些。另外最后一列对salary也进行了聚合汇总显示。但PIVOT函数是无法实现的，PIVOT函数只会对数据按照同类规则进行分类。各列的数据是相互独立的，不会出现交叉重复。

SELECT job as position,
       sum(case deptno when 10 then sal end) as department_10_salary,
       sum(case deptno when 20 then sal end) as department_20_salary,
       sum(case deptno when 30 then sal end) as department_30_salary,
       sum(sal) as sum_salary
  FROM emp
 GROUP BY job
 ORDER BY 1;

 （2）PIVOT函数的用法

SELECT *
  FROM (SELECT job, sal, deptno FROM emp)
pivot(SUM(sal) AS salary
   FOR deptno IN(10 AS department10, 20 AS department20, 30 AS department30))
 ORDER BY 1;
 

 比如我们还需要增加bonus的返回，使用PIVOT函数仅需要增加一个字段即可。然而使用CASE WHEN END 语句的话则需要增加三条语句，如下所示：

SELECT *
  FROM (SELECT job, sal,comm, deptno FROM emp)
pivot(SUM(sal) AS salary,SUM(comm) AS bonus
   FOR deptno IN(10 AS d10, 20 AS d20, 30 AS d30))
 ORDER BY 1;

 SELECT job as position,
       sum(case deptno when 10 then sal end) as d_10_salary,
       sum(case deptno when 20 then sal end) as d_20_salary,
       sum(case deptno when 30 then sal end) as d_30_salary,
       sum(case deptno when 10 then comm end) as d_10_bonus,
       sum(case deptno when 20 then comm end) as d_20_bonus,
       sum(case deptno when 30 then comm end) as d_30_bonus,
       sum(sal) as sum_salary
  FROM emp

 然而PIVOT函数进行行转列时仅能按一个条件进行转换，如果同时把job和department都转为列并汇总为一行时，当前只能用CASE WHEN END 语句进行转换列操作。

SELECT sum(case when deptno = 10 then sal end) AS d10_s,
sum(case when deptno = 20 then sal end) AS d20_s,
sum(case when deptno = 30 then sal end) AS d30_s,
sum(case when job = 'ANALYST' then sal end) AS ANALYST,
sum(case when job = 'CLERK' then sal end) AS CLERK,
sum(case when job = 'MANAGER' then sal end) AS MANAGER,
sum(case when job = 'PRESIDENT' then sal end) AS PRESIDENT,
sum(case when job = 'SALESMAN' then sal end) AS SALESMAN
FROM emp;

2. 列转行

test data as below:

CREATE OR REPLACE VIEW v AS
SELECT *
  FROM (SELECT deptno, sal FROM emp)
pivot(COUNT(*) AS ct, SUM(sal) AS s
   FOR deptno IN(10 AS deptno_10, 20 AS deptno_20, 30 AS deptno_30));

 需求是在一列上显示三个部门的人次。以前面对这样的需求会用UNION ALL，但如果出现多列的话coding和maintenance就会不便，然后现在可以直接使用UNPIVOT函数。

SELECT '10' AS 部门编码,DEPTNO_10_CT AS 人次 FROM v
UNION ALL
SELECT '20' AS 部门编码,DEPTNO_20_CT AS 人次 FROM v
UNION ALL
SELECT '30' AS 部门编码,DEPTNO_30_CT AS 人次 FROM v;

SELECT deptno AS 列名, substr(deptno, -5, 2) AS 部门编码, 人次
  FROM v unpivot(人次 FOR deptno IN(deptno_10_CT, deptno_20_CT, deptno_30_CT));
 

 可以在IN列表里维护要转换的列，然而当同时面临人次与工资合计要进行转换时，UNPIVOT函数就会受到限制，需要分别转换再进行JOIN才能实现。（此次使用参数INCLUDE NULLS 是为了数据结果的一致性，即便数据为空也会显示该行）如下所示：

SELECT a.列名, a.部门编码, a.人次, b.工资
  FROM (SELECT substr(deptno, 1, 9) AS 列名,
               substr(deptno, -5, 2) AS 部门编码,
               人次
          FROM v unpivot include NULLS(人次 FOR deptno IN(deptno_10_ct,
                                                        deptno_20_ct,
                                                        deptno_30_ct))) a
 INNER JOIN (SELECT substr(deptno, 1, 9) AS 列名, 工资
               FROM v unpivot include NULLS(工资 FOR deptno IN(deptno_10_s,
                                                             deptno_20_s,
                                                             deptno_30_s))) b
    ON (b.列名 = a.列名);
 

 

 3.将结果集反向转置为一列

有时需求要求数据竖向显示，各行之间用空格进行分隔。使用UNPIVOT函数进行实现，如下所示：

SELECT EMPS
  FROM (SELECT ENAME, JOB, TO_CHAR(SAL) AS SAL, NULL AS T_COL /*增加这一列来显示空行*/
          FROM EMP) UNPIVOT INCLUDE NULLS /*增加这个参数把空值也转为一行*/(EMPS FOR COL IN(ENAME,
                                                                             JOB,
                                                                             SAL,
                                                                             T_COL));

 4.抑制结果集中的重复值。

查询返回的数据经常出现重值。然而这些数据被要求合并显示。通常是被前台处理的，特殊情况，查询结果只需返回第一行数据即可，那么如何处理呢？ 然而用LAG进行判断就好。

SELECT CASE
       /*当部门分类按姓名排序当与上一条内容相同时不显示*/
         WHEN lag(deptno) over(ORDER BY deptno, ename) = deptno THEN
          NULL
         ELSE
          deptno
       END AS deptno,
       ename
  FROM emp
 ORDER BY emp.deptno, ename;

5.利用“行转列”进行计算

“行转列”除了用于报表显示外，还可以利用转换后各值在同一行上的特点进行计算。如下面计算部门 20 与 10 及部门 20 与 30 之间的总工资 差额。

SELECT d10_sal,
       d20_sal,
       d30_sal,
       d20_sal - d10_sal AS d20_10_diff,
       d20_sal - d30_sal AS d20_30_diff
  FROM (SELECT SUM(CASE
                     WHEN deptno = 10 THEN
                      sal
                   END) AS d10_sal,
               SUM(CASE
                     WHEN deptno = 20 THEN
                      sal
                   END) AS d20_sal,
               SUM(CASE
                     WHEN deptno = 30 THEN
                      sal
                   END) AS d30_sal
          FROM emp) totals_by_dept;

6.给数据分组

需求为某公司需开展活动，需将员工进行分组，每5个人一组，用查询语句如何实现这一需求？

/*然后编号除5，并用ceil返回不小于当前值的下一个整数就可以了*/
SELECT ceil(rn / 5) AS 组, empno AS 编码, ename AS 姓名
  FROM (
        /*首先我们要给每位员工生成一个编号*/
        SELECT row_number() over(ORDER BY empno) AS rn ，empno, ename
          FROM emp);

7，创建横向直方图

SELECT deptno AS 部门编号, lpad('*', COUNT(*), '*') AS 员工分布 FROM emp GROUP BY deptno;

 8.创建纵向直方图

SELECT *
  FROM (SELECT '*' AS ename,
               deptno,
               /*分部门按姓名排序取序号，以为作为分组依据*/
               row_number() over(PARTITION BY deptno ORDER BY ename) AS sn
          FROM emp)
pivot(MAX(ename)
   FOR deptno IN(10 AS deptno_10, 20 AS deptno_20, 30 AS deptno_30))
 ORDER BY sn DESC;

 9.标识最大最小值

报表查询时有时会要求标识最大最小值，以前我们常用子查询关联的方式，这样就要对表多扫描一次。那有了分析函数就容易多了，我们可以用 分析函数提出相应的值，然后在返回的数据内做比较即可。

SELECT deptno,
       ename,
       job,
       sal,
       CASE
         WHEN sal = max_by_dept THEN
          '部门内最高工资'
         WHEN sal = min_by_dept THEN
          '部门内最低工资'
       END dept_status,
       CASE
         WHEN sal = max_by_job THEN
          '职内最高工资'
         WHEN sal = min_by_job THEN
          '职内最低工资'
       END job_status
  FROM (SELECT deptno,
               ename,
               job,
               sal,
               MAX(sal) over(PARTITION BY deptno) max_by_dept,
               MAX(sal) over(PARTITION BY job) max_by_job,
               MIN(sal) over(PARTITION BY deptno) min_by_dept,
               MIN(sal) over(PARTITION BY job) min_by_job
          FROM emp) emp_sals
 WHERE sal IN (max_by_dept, max_by_job, min_by_dept, min_by_job);

 10.计算简单的小计

常常生成报表数据时还要加一个总合计，必须要用 UNION ALL 来做？No,我们用 rollup 就可以达到这个目。

SELECT CASE GROUPING(job)
         WHEN 0 THEN
          job
         ELSE
          '总合计'
       END AS 职位,
       SUM(sal) AS 工资小计
  FROM emp
 GROUP BY ROLLUP(job);

 ROLLUP，是 GROUP BY 子句的一种扩展，可以为每个分组返回小计记录以及为所有分组返回总计记录。为了便于理解我们作个与 UNION ALL 对照的实例

SELECT deptno AS 部门编码,
       job AS 职位,
       to_char(hiredate, 'yyyy') AS 年份,
       SUM(sal)
  FROM emp
 GROUP BY ROLLUP(deptno, job, to_char(hiredate, 'yyyy'));

 等价

SELECT deptno AS 部门编码, job AS 职位, to_char(hiredate, 'yyyy') AS 年份, SUM(sal)
FROM emp
GROUP BY deptno , job, to_char(hiredate, 'yyyy')
UNION ALL
SELECT deptno AS 部门编码, job AS 职位, NULL/*职位小讲*/ AS 年份, SUM(sal)
FROM emp
GROUP BY deptno, job
UNION ALL
SELECT deptno AS 部门编码, NULL/*部门小计*/ AS 职位, NULL AS 年份, SUM(sal)
FROM emp
GROUP BY deptno
UNION ALL
SELECT NULL /*总合计*/, '' AS 职位, '' AS y, SUM(sal)
FROM emp; 

 可能这种方式有很多人已用过，那么如果按部门与职位两列汇总加上总合计有没有办法处理呢？我们把部门与职位这两列当作一个整体放在括号 中即可。

SELECT CASE GROUPING(deptno)
         WHEN 0 THEN
          job
         ELSE
          '总合计'
       END AS 部门编码,
       job 职位,
       SUM(sal) AS 工资小计
  FROM emp
 GROUP BY ROLLUP((deptno, job));

 11.常用分析函数开窗讲解

取工资排序后取第一行到当前行范围内最小值。

SELECT ENAME,
       SAL,
       /*因是按工资排序，所以这个语句取出一就是所有行的最小值*/
       MIN(sal) over(ORDER BY sal) AS min_11,
       /*上述语句默认参窗口如下，plan中可以看到*/
       MIN(sal) over(ORDER BY sal RANGE BETWEEN unbounded preceding AND CURRENT ROW) AS min_12,
       /*这种情况下rows与RANGE返回数据一样*/
       MIN(SAL) OVER(ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS min_13,
       /*取所所有行内最小值作对比*/
       MIN(SAL) OVER() AS min_14,
       /*如果明确写出上min_14的范围就是*/
       MIN(SAL) OVER(ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS min_15,
       /*这种情况下rows与RANGE返回数据一样*/
       MIN(SAL) OVER(ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS min_16
  FROM emp
 WHERE deptno = 30;