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

这篇具有很好参考价值的文章主要介绍了【SQL开发实战技巧】系列(二十八):数仓报表场景☞人员分布问题以及不同组(分区)同时聚集如何实现。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

系列文章目录

【SQL开发实战技巧】系列(一):关于SQL不得不说的那些事
【SQL开发实战技巧】系列(二):简单单表查询
【SQL开发实战技巧】系列(三):SQL排序的那些事
【SQL开发实战技巧】系列(四):从执行计划讨论UNION ALL与空字符串&UNION与OR的使用注意事项
【SQL开发实战技巧】系列(五):从执行计划看IN、EXISTS 和 INNER JOIN效率,我们要分场景不要死记网上结论
【SQL开发实战技巧】系列(六):从执行计划看NOT IN、NOT EXISTS 和 LEFT JOIN效率,记住内外关联条件不要乱放
【SQL开发实战技巧】系列(七):从有重复数据前提下如何比较出两个表中的差异数据及对应条数聊起
【SQL开发实战技巧】系列(八):聊聊如何插入数据时比约束更灵活的限制数据插入以及怎么一个insert语句同时插入多张表
【SQL开发实战技巧】系列(九):一个update误把其他列数据更新成空了?Merge改写update!给你五种删除重复数据的写法!
【SQL开发实战技巧】系列(十):从拆分字符串、替换字符串以及统计字符串出现次数说起
【SQL开发实战技巧】系列(十一):拿几个案例讲讲translate|regexp_replace|listagg|wmsys.wm_concat|substr|regexp_substr常用函数
【SQL开发实战技巧】系列(十二):三问(如何对字符串字母去重后按字母顺序排列字符串?如何识别哪些字符串中包含数字?如何将分隔数据转换为多值IN列表?)
【SQL开发实战技巧】系列(十三):讨论一下常用聚集函数&通过执行计划看sum()over()对员工工资进行累加
【SQL开发实战技巧】系列(十四):计算消费后的余额&计算银行流水累计和&计算各部门工资排名前三位的员工
【SQL开发实战技巧】系列(十五):查找最值所在行数据信息及快速计算总和百之max/min() keep() over()、fisrt_value、last_value、ratio_to_report
【SQL开发实战技巧】系列(十六):数据仓库中时间类型操作(初级)日、月、年、时、分、秒之差及时间间隔计算
【SQL开发实战技巧】系列(十七):数据仓库中时间类型操作(初级)确定两个日期之间的工作天数、计算—年中周内各日期出现次数、确定当前记录和下一条记录之间相差的天数
【SQL开发实战技巧】系列(十八):数据仓库中时间类型操作(进阶)INTERVAL、EXTRACT以及如何确定一年是否为闰年及周的计算
【SQL开发实战技巧】系列(十九):数据仓库中时间类型操作(进阶)如何一个SQL打印当月或一年的日历?如何确定某月内第一个和最后—个周内某天的日期?
【SQL开发实战技巧】系列(二十):数据仓库中时间类型操作(进阶)获取季度开始结束时间以及如何统计非连续性时间的数据
【SQL开发实战技巧】系列(二十一):数据仓库中时间类型操作(进阶)识别重叠的日期范围,按指定10分钟时间间隔汇总数据
【SQL开发实战技巧】系列(二十二):数仓报表场景☞ 从分析函数效率一定快吗聊一聊结果集分页和隔行抽样实现方式
【SQL开发实战技巧】系列(二十三):数仓报表场景☞ 如何对数据排列组合去重以及通过如何找到包含最大值和最小值的记录这个问题再次用执行计划给你证明分析函数性能不一定高
【SQL开发实战技巧】系列(二十四):数仓报表场景☞通过案例执行计划详解”行转列”,”列转行”是如何实现的
【SQL开发实战技巧】系列(二十五):数仓报表场景☞结果集中的重复数据只显示一次以及计算部门薪资差异高效的写法以及如何对数据进行快速分组
【SQL开发实战技巧】系列(二十六):数仓报表场景☞聊聊ROLLUP、UNION ALL是如何分别做分组合计的以及如何识别哪些行是做汇总的结果行
【SQL开发实战技巧】系列(二十七):数仓报表场景☞通过对移动范围进行聚集来详解分析函数开窗原理以及如何一个SQL打印九九乘法表
【SQL开发实战技巧】系列(二十八):数仓报表场景☞人员分布问题以及不同组(分区)同时聚集如何实现



前言

本篇文章讲解的主要内容是:通过行转列实现人员空间分布问题(工作显示为一列,每位员工显示一行)、连续行转列应该注意的问题、通过执行计划看对不同组、分区同时实现聚集需求:要求在员工表的明细数据里列出员工所在部门及职位的人数!!
【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作,毕竟SQL开发在数据分析场景非常重要且基础,面试也会经常问SQL开发和调优经验,相信当我写完这一系列文章,也能再有所收获,未来面对SQL面试也能游刃有余~。


一、人员在工作空间的分布问题

现在有一个需求:要求每种工作显示为一列,每位员工显示一行,员工与工作对应时显示为是,不对应则显示为空!
那这个需求怎么做呢?
其实我们可以活用PIVOT函数,按工作、员工分组,设对应位置为是:

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

看一下执行计划:

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

看执行计划:

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

从执行计划看,就扫描了一次表。
但是我前面不是有两篇文章不是一直在说用分析函数要慎重嘛?为什么我这里又推荐大家用了?
遇到这种多次访问同一个表的情况时,可以尝试看一下能否用分析函数改写,以及改写后的效率如何,如果像我现在这样通过分析执行计划得到"性能提升很明显"这个结论,
那你这个场景当然就可以用啦,当然还有最重要的一点是:改写完了别忘了核对数据!这可是非常重要的一点。


总结

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

到了这里,关于【SQL开发实战技巧】系列(二十八):数仓报表场景☞人员分布问题以及不同组(分区)同时聚集如何实现的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包