# 前言
本篇文章讲解的主要内容是：***通过行转列实现人员空间分布问题(工作显示为一列,每位员工显示一行)、连续行转列应该注意的问题、通过执行计划看对不同组、分区同时实现聚集需求：要求在员工表的明细数据里列出员工所在部门及职位的人数！！***
【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。
---
## 一、人员在工作空间的分布问题
现在有一个需求：要求每种工作显示为一列,每位员工显示一行,员工与工作对应时显示为是,不对应则显示为空！
那这个需求怎么做呢？
其实我们可以活用PIVOT函数,按工作、员工分组,设对应位置为是:
```sql
SQL> select * from (select ename,job from emp)
2 pivot(
3 max('是')
4 for job in(
5 'ANALYST' as ANALYST,
6 'CLERK' as CLERK,
7 'MANAGER' as MANAGER,
8 'PRESIDENT' as PRESIDENT,
9 'SALESMAN' as SALESMAN
10 )
11 );
ENAME ANALYST CLERK MANAGER PRESIDENT SALESMAN
---------- ------- ----- ------- --------- --------
ADAMS 是
ALLEN 是
BLAKE 是
CLARK 是
FORD 是
JAMES 是
JONES 是
KING 是
MARTIN 是
MILLER 是
SCOTT 是
SMITH 是
TURNER 是
WARD 是
14 rows selected
```
这个语句相当于`group by ename,job`。
## 二、创建稀疏矩阵
给上面问题的需求增加一下难度,现在的需求是：对应位置直接显示为员工姓名,且增加在部门间的分布,因未对数据进行汇总,所以仍可以用PIVOT来处理,查询语句如下：
```sql
SQL>
SQL> select *
2 from (select empno, ename, ename as ename2, job, deptno from emp)
3 pivot(max(ename)
4 for deptno in(10 as d10, 20 as d20, 30 as d30))
5 pivot(max(ename2)
6 for job in('ANALYST' as ANALYST,
7 'CLERK' as CLERK,
8 'MANAGER' as MANAGER,
9 'PRESIDENT' as PRESIDENT,
10 'SALESMAN' as SALESMAN
11 ));
EMPNO D10 D20 D30 ANALYST CLERK MANAGER PRESIDENT SALESMAN
----- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
7900 JAMES JAMES
7369 SMITH SMITH
7499 ALLEN ALLEN
7521 WARD WARD
7566 JONES JONES
7654 MARTIN MARTIN
7698 BLAKE BLAKE
7782 CLARK CLARK
7788 SCOTT SCOTT
7839 KING KING
7844 TURNER TURNER
7876 ADAMS ADAMS
7902 FORD FORD
7934 MILLER MILLER
14 rows selected
```
注意：如果对数据有汇总,就不要用这种有两个PIOVT的方式。因为这种查询实际上相当于两个PIVOT的子句嵌套。
在之前的文章中有一个count的case when语句,具体如下：
```sql
SQL>
SQL> select count(case
2 when deptno = 10 then
3 ename
4 end) as deptno_10,
5 count(case
6 when deptno = 20 then
7 ename
8 end) as deptno_20,
9 count(case
10 when deptno = 30 then
11 ename
12 end) as deptno_30,
13 count(case
14 when job = 'ANALYST' then
15 job
16 end) as ANALYST,
17 count(case
18 when job = 'CLERK' then
19 job
20 end) as CLERK,
21 count(case
22 when job = 'MANAGER' then
23 job
24 end) as MANAGER,
25 count(case
26 when job = 'PRESIDENT' then
27 job
28 end) as PRESIDENT,
29 count(case
30 when job = 'SALESMAN' then
31 job
32 end) as SALESMAN
33 from emp;
DEPTNO_10 DEPTNO_20 DEPTNO_30 ANALYST CLERK MANAGER PRESIDENT SALESMAN
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
3 5 6 2 4 3 1 4
```
我们尝试用PIOVT来改写,看会出现什么问题。原始PIOVT语句如下：
```sql
SQL>
SQL> select *
2 from (select ename, ename as ename2, job, deptno from emp)
3 pivot(count(ename)
4 for deptno in(10 as d10, 20 as d20, 30 as d30))
5 pivot(count(ename2)
6 for job in('ANALYST' as ANALYST,
7 'CLERK' as CLERK,
8 'MANAGER' as MANAGER,
9 'PRESIDENT' as PRESIDENT,
10 'SALESMAN' as SALESMAN
11 ));
D10 D20 D30 ANALYST CLERK MANAGER PRESIDENT SALESMAN
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
0 0 1 0 1 1 0 4
0 1 0 2 2 1 0 0
1 0 0 0 1 1 1 0
SQL>
```
可以看到数据,与case when的结果不一致。下面改为嵌套的方式来分析。
嵌套第一步：
```sql
SQL> with t as (
2 select *
3 from (select ename, ename as ename2, job, deptno from emp)
4 pivot(count(ename)
5 for deptno in(10 as d10, 20 as d20, 30 as d30))
6 )
7 select * from t;
ENAME2 JOB D10 D20 D30
---------- --------- ---------- ---------- ----------
FORD ANALYST 0 1 0
KING PRESIDENT 1 0 0
WARD SALESMAN 0 0 1
ADAMS CLERK 0 1 0
ALLEN SALESMAN 0 0 1
BLAKE MANAGER 0 0 1
CLARK MANAGER 1 0 0
JAMES CLERK 0 0 1
JONES MANAGER 0 1 0
SCOTT ANALYST 0 1 0
SMITH CLERK 0 1 0
MARTIN SALESMAN 0 0 1
MILLER CLERK 1 0 0
TURNER SALESMAN 0 0 1
14 rows selected
```
第一步相当于`group by empno,job`。
嵌套示例第二步：
```sql
SQL> with t as
2 (select *
3 from (select ename, ename as ename2, job, deptno from emp)
4 pivot(count(ename)
5 for deptno in(10 as d10, 20 as d20, 30 as d30)))
6 select *
7 from t
8 pivot (count(ename2) for job in('ANALYST' as ANALYST,
9 'CLERK' as CLERK,
10 'MANAGER' as MANAGER,
11 'PRESIDENT' as PRESIDENT,
12 'SALESMAN' as SALESMAN));
D10 D20 D30 ANALYST CLERK MANAGER PRESIDENT SALESMAN
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
0 0 1 0 1 1 0 4
0 1 0 2 2 1 0 0
1 0 0 0 1 1 1 0
SQL>
```
因第一步返回的列为(ENAME2,JOB,D10,D20,D30)去掉(ENAME2,JOB)后,剩余的是(D10,D20,D30)。所以第二
步相当于`group by D10,D20,D30`。
但是我们想要的是对emp表根据job分组求count和根据部门分组求count，它们是对emp表两个组合的统计合并，而不是在条件1的基础上，再进行分组count。
## 三、对不同组、分区同时实现聚集
现在有个需求：要求在员工表的明细数据里列出员工所在部门及职位的人数。
没用分析函数前,这种需求要用自关联：
```sql
SQL> with t as
2 (select count(*) as cnt from emp),
3 t1 as
4 (select deptno, count(*) as dcnt from emp group by deptno),
5 t2 as
6 (select job, count(*) as jcnt from emp group by job)
7 select emp.ename,
8 emp.deptno,
9 t1.dcnt,
10 emp.job,
11 t2.jcnt,
12 (select * from t) as cnt
13 from emp
14 inner join t1
15 on (emp.deptno = t1.deptno)
16 inner join t2
17 on (emp.job = t2.job);
ENAME DEPTNO DCNT JOB JCNT CNT
---------- ------ ---------- --------- ---------- ----------
FORD 20 5 ANALYST 2 14
SCOTT 20 5 ANALYST 2 14
MILLER 10 3 CLERK 4 14
JAMES 30 6 CLERK 4 14
ADAMS 20 5 CLERK 4 14
SMITH 20 5 CLERK 4 14
CLARK 10 3 MANAGER 3 14
BLAKE 30 6 MANAGER 3 14
JONES 20 5 MANAGER 3 14
KING 10 3 PRESIDENT 1 14
TURNER 30 6 SALESMAN 4 14
MARTIN 30 6 SALESMAN 4 14
WARD 30 6 SALESMAN 4 14
ALLEN 30 6 SALESMAN 4 14
14 rows selected
SQL>
```
看一下执行计划：
```sql
Plan Hash Value :
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 868 | 12 | 00:00:01 |
| 1 | VIEW | | 1 | 13 | 1 | 00:00:01 |
| 2 | SORT AGGREGATE | | 1 | | | |
| 3 | INDEX FULL SCAN | IDX_EMPNO | 15 | | 1 | 00:00:01 |
| * 4 | HASH JOIN | | 14 | 868 | 11 | 00:00:01 |
| * 5 | HASH JOIN | | 13 | 559 | 7 | 00:00:01 |
| 6 | VIEW | | 3 | 78 | 4 | 00:00:01 |
| 7 | SORT GROUP BY | | 3 | 9 | 4 | 00:00:01 |
| 8 | TABLE ACCESS FULL | EMP | 15 | 45 | 3 | 00:00:01 |
| * 9 | TABLE ACCESS FULL | EMP | 13 | 221 | 3 | 00:00:01 |
| 10 | VIEW | | 5 | 95 | 4 | 00:00:01 |
| 11 | SORT GROUP BY | | 5 | 40 | 4 | 00:00:01 |
| 12 | TABLE ACCESS FULL | EMP | 15 | 120 | 3 | 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 4 - access("EMP"."JOB"="T2"."JOB")
* 5 - access("EMP"."DEPTNO"="T1"."DEPTNO")
* 9 - filter("EMP"."JOB" IS NOT NULL AND "EMP"."DEPTNO" IS NOT NULL)
```
这种写法比较复杂,而且要对表emp访问四次(因为我建了索引。所以有一个走了索引)。
如果改用分析函数,语句就较简单：
```sql
SQL> select emp.ename,
2 emp.deptno,
3 count(*) over(partition by deptno) dcnt,
4 emp.job,
5 count(*) over(partition by job) jcnt,
6 count(*) over() as cnt
7 from emp
8 ;
ENAME DEPTNO DCNT JOB JCNT CNT
---------- ------ ---------- --------- ---------- ----------
MILLER 10 3 CLERK 4 14
KING 10 3 PRESIDENT 1 14
CLARK 10 3 MANAGER 3 14
SMITH 20 5 CLERK 4 14
SCOTT 20 5 ANALYST 2 14
ADAMS 20 5 CLERK 4 14
FORD 20 5 ANALYST 2 14
JONES 20 5 MANAGER 3 14
WARD 30 6 SALESMAN 4 14
MARTIN 30 6 SALESMAN 4 14
TURNER 30 6 SALESMAN 4 14
ALLEN 30 6 SALESMAN 4 14
JAMES 30 6 CLERK 4 14
BLAKE 30 6 MANAGER 3 14
14 rows selected
```
看执行计划：
```sql
Plan Hash Value : 4086863039
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 255 | 5 | 00:00:01 |
| 1 | WINDOW SORT | | 15 | 255 | 5 | 00:00:01 |
| 2 | WINDOW SORT | | 15 | 255 | 5 | 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 15 | 255 | 3 | 00:00:01 |
----------------------------------------------------------------------
```
从执行计划看，就扫描了一次表。
但是我前面不是有两篇文章不是一直在说用分析函数要慎重嘛？为什么我这里又推荐大家用了？
遇到这种多次访问同一个表的情况时,可以尝试看一下能否用分析函数改写,以及改写后的效率如何，如果像我现在这样通过分析执行计划得到"性能提升很明显"这个结论，
那你这个场景当然就可以用啦，当然还有最重要的一点是：改写完了别忘了核对数据！这可是非常重要的一点。
---
# 总结
本篇文章讲解的主要内容是：***通过行转列实现人员空间分布问题(工作显示为一列,每位员工显示一行)、连续行转列应该注意的问题、通过执行计划看对不同组、分区同时实现聚集需求：要求在员工表的明细数据里列出员工所在部门及职位的人数！！***
