【SQL开发实战技巧】系列(二十八):数仓报表场景☞人员分布问题以及不同组(分区)同时聚集如何实现

本文涉及的产品
云原生数据仓库AnalyticDB MySQL版,8核32GB 100GB 1个月
简介: 【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作,毕竟SQL开发在数据分析场景非常重要且基础,面试也会经常问SQL开发和调优经验,相信当我写完这一系列文章,也能再有所收获,未来面对SQL面试也能游刃有余~。

# 前言



本篇文章讲解的主要内容是:***通过行转列实现人员空间分布问题(工作显示为一列,每位员工显示一行)、连续行转列应该注意的问题、通过执行计划看对不同组、分区同时实现聚集需求:要求在员工表的明细数据里列出员工所在部门及职位的人数!!***

【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 |

----------------------------------------------------------------------

```


从执行计划看,就扫描了一次表。

但是我前面不是有两篇文章不是一直在说用分析函数要慎重嘛?为什么我这里又推荐大家用了?

遇到这种多次访问同一个表的情况时,可以尝试看一下能否用分析函数改写,以及改写后的效率如何,如果像我现在这样通过分析执行计划得到"性能提升很明显"这个结论,

那你这个场景当然就可以用啦,当然还有最重要的一点是:改写完了别忘了核对数据!这可是非常重要的一点。


---


# 总结


本篇文章讲解的主要内容是:***通过行转列实现人员空间分布问题(工作显示为一列,每位员工显示一行)、连续行转列应该注意的问题、通过执行计划看对不同组、分区同时实现聚集需求:要求在员工表的明细数据里列出员工所在部门及职位的人数!!***

相关实践学习
AnalyticDB MySQL海量数据秒级分析体验
快速上手AnalyticDB MySQL,玩转SQL开发等功能!本教程介绍如何在AnalyticDB MySQL中,一键加载内置数据集,并基于自动生成的查询脚本,运行复杂查询语句,秒级生成查询结果。
阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
云原生数据仓库AnalyticDB MySQL版是一种支持高并发低延时查询的新一代云原生数据仓库,高度兼容MySQL协议以及SQL:92、SQL:99、SQL:2003标准,可以对海量数据进行即时的多维分析透视和业务探索,快速构建企业云上数据仓库。 了解产品 https://www.aliyun.com/product/ApsaraDB/ads
相关文章
|
1月前
|
存储 数据采集 JavaScript
深入理解数仓开发(一)数据技术篇之日志采集
深入理解数仓开发(一)数据技术篇之日志采集
|
1月前
|
消息中间件 关系型数据库 Kafka
深入理解数仓开发(二)数据技术篇之数据同步
深入理解数仓开发(二)数据技术篇之数据同步
|
6天前
|
存储 数据挖掘 OLAP
阿里云 EMR Serverless StarRocks OLAP 数据分析场景解析
阿里云 E-MapReduce Serverless StarRocks 版是阿里云提供的 Serverless StarRocks 全托管服务,提供高性能、全场景、极速统一的数据分析体验,具备开箱即用、弹性扩展、监控管理、慢 SQL 诊断分析等全生命周期能力。内核 100% 兼容 StarRocks,性能比传统 OLAP 引擎提升 3-5 倍,助力企业高效构建大数据应用。本篇文章对阿里云EMR Serverless StarRocks OLAP 数据分析场景进行解析、存算分离架构升级以及 Trino 兼容,无缝替换介绍。
45 1
|
10天前
|
SQL 自然语言处理 网络协议
【Linux开发实战指南】基于TCP、进程数据结构与SQL数据库:构建在线云词典系统(含注册、登录、查询、历史记录管理功能及源码分享)
TCP(Transmission Control Protocol)连接是互联网上最常用的一种面向连接、可靠的、基于字节流的传输层通信协议。建立TCP连接需要经过著名的“三次握手”过程: 1. SYN(同步序列编号):客户端发送一个SYN包给服务器,并进入SYN_SEND状态,等待服务器确认。 2. SYN-ACK:服务器收到SYN包后,回应一个SYN-ACK(SYN+ACKnowledgment)包,告诉客户端其接收到了请求,并同意建立连接,此时服务器进入SYN_RECV状态。 3. ACK(确认字符):客户端收到服务器的SYN-ACK包后,发送一个ACK包给服务器,确认收到了服务器的确
|
18天前
|
SQL 存储 OLAP
实时数仓Hologres OLAP场景核心能力介绍
Hologres提供统一、实时、弹性、易用的一站式实时数仓引擎,解决复杂OLAP难题。
|
14天前
|
SQL 分布式计算 DataWorks
DataWorks产品使用合集之在使用ODPSSQL节点进行SQL任务开发时,如何执行大量的SQL语句
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
26 4
|
21天前
|
SQL XML 数据库
后端数据库开发高级之通过在xml文件中映射实现动态SQL
后端数据库开发高级之通过在xml文件中映射实现动态SQL
20 3
|
21天前
|
SQL XML Java
后端数据库开发JDBC编程Mybatis之用基于XML文件的方式映射SQL语句实操
后端数据库开发JDBC编程Mybatis之用基于XML文件的方式映射SQL语句实操
31 3
|
21天前
|
SQL Java 数据库连接
2万字实操案例之在Springboot框架下基于注解用Mybatis开发实现基础操作MySQL之预编译SQL主键返回增删改查
2万字实操案例之在Springboot框架下基于注解用Mybatis开发实现基础操作MySQL之预编译SQL主键返回增删改查
26 2
|
25天前
|
SQL 监控 安全
代码审计-PHP原生开发篇&SQL注入&数据库监控&正则搜索&文件定位&静态分析
代码审计-PHP原生开发篇&SQL注入&数据库监控&正则搜索&文件定位&静态分析

热门文章

最新文章