【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开发实战技巧】系列（十二）：三问(如何对字符串字母去重后按字母顺序排列字符串？如何识别哪些字符串中包含数字？如何将分隔数据转换为多值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是如何分别做分组合计的以及如何识别哪些行是做汇总的结果行

文章目录

系列文章目录
前言
一、如何让结果集中的重复数据只显示一次
二、部门之间计算工资差异时也可以用行转列pivot
三、如何对已有数据进行分组打印
四、放假安排团队分组值班，如何快速进行人员分组？

总结

前言

本篇文章讲解的主要内容是：如何使用lag函数让结果集重复数据只显示一次、用行转列pivot写法优化部门之间计算工资差异类似需求、如何通过ceil函数对已有数据进行分组打印、放假安排团队分组值班，如何通过ntile()over(order by )快速进行人员分组

【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。

一、如何让结果集中的重复数据只显示一次

我们返回的数据中经常会有重复值,如EMP.JOB,这些数据经常要求合并显示。这种一般都在前台处理,偶尔也有特殊情况,需要在返回时就只显示第一行数据,该如何处理呢？其实用LAG进行判断即可。

SQL> select job,case when lag(job)over(order by a.job,ename)=job then null else job end as 职位,ename as 姓名
  2  from emp a
  3  order by a.job,a.ename;
JOB       职位      姓名
--------- --------- ----------
ANALYST   ANALYST   FORD
ANALYST             SCOTT
CLERK     CLERK     ADAMS
CLERK               JAMES
CLERK               MILLER
CLERK               SMITH
MANAGER   MANAGER   BLAKE
MANAGER             CLARK
MANAGER             JONES
PRESIDENT PRESIDENT KING
SALESMAN  SALESMAN  ALLEN
SALESMAN            MARTIN
SALESMAN            TURNER
SALESMAN            WARD
                    test
15 rows selected
SQL>

或许有人注意到,order by子句后的job加上了前缀。如果不加前缀,而且列别名仍然是job会出现什么情况？

SQL> select  case when lag(job)over(order by a.job,ename)=job then null else job end job,ename
  2  from emp a
  3  order by job,a.ename;
JOB       ENAME
--------- ----------
ANALYST   FORD
CLERK     ADAMS
MANAGER   BLAKE
PRESIDENT KING
SALESMAN  ALLEN
          CLARK
          JAMES
          JONES
          MARTIN
          MILLER
          SCOTT
          SMITH
          TURNER
          WARD
          test
15 rows selected

可以看到,order by子句后优先使用的是"别名",而不是"列名",从而使排序结果与需求不一样。所以大家要养成加"前缀＂的习惯。

二、部门之间计算工资差异时也可以用行转列pivot

基础数据：

SQL> select deptno,sum(sal) as sm from emp where deptno is not null group by deptno ;
DEPTNO         SM
------ ----------
    10       8750
    20      10875
    30       9400

现在有个需求：要求计算部门20与部门10及部门20与部门30之间的总工资差额。

对于这个需求你会怎么做？是不是像下面这样子？

SQL> select (select sum(sal) from emp where deptno = 20) -
  2         (select sum(sal) from emp where deptno = 10) as d20_10,
  3         (select sum(sal) from emp where deptno = 20) -
  4         (select sum(sal) from emp where deptno = 30) as d20_30
  5    from dual;
    D20_10     D20_30
---------- ----------
      2125       1475
SQL> 
 Plan Hash Value  : 
------------------------------------------------------------------------
| Id  | Operation              | Name | Rows | Bytes | Cost | Time     |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |    1 |       |   14 | 00:00:01 |
|   1 |   SORT AGGREGATE       |      |    1 |     7 |      |          |
| * 2 |    TABLE ACCESS FULL   | EMP  |    5 |    35 |    3 | 00:00:01 |
|   3 |     SORT AGGREGATE     |      |    1 |     7 |      |          |
| * 4 |      TABLE ACCESS FULL | EMP  |    3 |    21 |    3 | 00:00:01 |
|   5 |   SORT AGGREGATE       |      |    1 |     7 |      |          |
| * 6 |    TABLE ACCESS FULL   | EMP  |    5 |    35 |    3 | 00:00:01 |
|   7 |     SORT AGGREGATE     |      |    1 |     7 |      |          |
| * 8 |      TABLE ACCESS FULL | EMP  |    6 |    42 |    3 | 00:00:01 |
|   9 |   FAST DUAL            |      |    1 |       |    2 | 00:00:01 |
------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - filter("DEPTNO"=20)
* 4 - filter("DEPTNO"=10)
* 6 - filter("DEPTNO"=20)
* 8 - filter("DEPTNO"=30)

通过执行计划可以看到，我们这个sql扫描了4次emp表，性能挺差啊！那如果让你优化，你该怎么优化呢？

对于这种需求,其实也可以通过"行转列"把各值提到同一行上后,再进行计算。

SQL> with t as
  2  （
  3  select deptno, sum(sal) as sm
  4    from emp
  5   where deptno in (10, 20, 30)
  6   group by deptno ）
  7             select d20_sm - d10_sm, d20_sm - d30_sm
  8               from t
  9             pivot(max(sm) as sm
 10                for deptno in(10 as d10, 20 as d20, 30 as d30));
D20_SM-D10_SM D20_SM-D30_SM
------------- -------------
         2125          1475
 Plan Hash Value  : 3261863285 
-------------------------------------------------------------------------
| Id  | Operation               | Name | Rows | Bytes | Cost | Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |    1 |    39 |    4 | 00:00:01 |
|   1 |   VIEW                  |      |    1 |    39 |    4 | 00:00:01 |
|   2 |    SORT AGGREGATE       |      |    1 |    26 |      |          |
|   3 |     VIEW                |      |    3 |    78 |    4 | 00:00:01 |
|   4 |      SORT GROUP BY      |      |    3 |    21 |    4 | 00:00:01 |
| * 5 |       TABLE ACCESS FULL | EMP  |   13 |    91 |    3 | 00:00:01 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 5 - filter("DEPTNO"=10 OR "DEPTNO"=20 OR "DEPTNO"=30)

可以看到只扫描了一次emp表

三、如何对已有数据进行分组打印

有时为了方便打印,会要求多行多列打印,如emp.ename类似下面这样显示：

ADAMS      ALLEN      BLAKE      CLARK      FORD	
JAMES      JONES      KING       MARTIN     MILLER
SCOTT      SMITH      TURNER     WARD       test

要达到这个目的,需要以下操作。

生成序号

SQL> with t as
  2   (select rownum as rn, ename from (select ename from emp order by ename))--第一步，根据名称进行字段顺序排序，排序后取rownum值，因为我想按照字段顺序打印
  3   select * from t
  4  ;
        RN ENAME
---------- ----------
         1 ADAMS
         2 ALLEN
         3 BLAKE
         4 CLARK
         5 FORD
         6 JAMES
         7 JONES
         8 KING
         9 MARTIN
        10 MILLER
        11 SCOTT
        12 SMITH
        13 TURNER
        14 WARD
        15 test
15 rows selected

通过ceil函数把数据分为几个组

SQL> 
SQL> with t as
  2   (select rownum as rn, ename from (select ename from emp order by ename)),--第一步，根据名称进行字段顺序排序，排序后取rownum值，因为我想按照字段顺序打印
  3  t1 as
  4   (select ceil(rn / 5) as gp, ename from t)--我想一页展示五列
  5    select * from t
  6  ;
        RN ENAME
---------- ----------
         1 ADAMS
         2 ALLEN
         3 BLAKE
         4 CLARK
         5 FORD
         6 JAMES
         7 JONES
         8 KING
         9 MARTIN
        10 MILLER
        11 SCOTT
        12 SMITH
        13 TURNER
        14 WARD
        15 test
15 rows selected

给各组数据生成序号

SQL> with t as
  2   (select rownum as rn, ename from (select ename from emp order by ename)),--第一步，根据名称进行字段顺序排序，排序后取rownum值，因为我想按照字段顺序打印
  3  t1 as
  4   (select ceil(rn / 5) as gp, ename from t),--我想一页展示五列
  5  t2 as
  6   (select gp, ename, row_number() over(partition by gp order by ename) as rnn--给每一列编一个序号，便于行转列进行识别
  7      from t1)
  8  select *
  9    FROM t2
 10  ;
        GP ENAME             RNN
---------- ---------- ----------
         1 ADAMS               1
         1 ALLEN               2
         1 BLAKE               3
         1 CLARK               4
         1 FORD                5
         2 JAMES               1
         2 JONES               2
         2 KING                3
         2 MARTIN              4
         2 MILLER              5
         3 SCOTT               1
         3 SMITH               2
         3 TURNER              3
         3 WARD                4
         3 test                5
15 rows selected

通过分组生成序号,并进行行转列

SQL> 
SQL> with t as
  2   (select rownum as rn, ename from (select ename from emp order by ename)),--第一步，根据名称进行字段顺序排序，排序后取rownum值，因为我想按照字段顺序打印
  3  t1 as
  4   (select ceil(rn / 5) as gp, ename from t),--我想一页展示五列
  5  t2 as
  6   (select gp, ename, row_number() over(partition by gp order by ename) as rnn--给每一列编一个序号，便于行转列进行识别
  7      from t1)
  8  select *
  9    FROM t2
 10  pivot (max(ename) as e for rnn in(1 as d1,
 11                               2 as d2,
 12                               3 as d3,
 13                               4 as d4,
 14                               5 as d5));
        GP D1_E       D2_E       D3_E       D4_E       D5_E
---------- ---------- ---------- ---------- ---------- ----------
         1 ADAMS      ALLEN      BLAKE      CLARK      FORD
         2 JAMES      JONES      KING       MARTIN     MILLER
         3 SCOTT      SMITH      TURNER     WARD       test

有些前台打印功能较弱,就可以用这种办法直接返回需要的数据进行打印。

四、放假安排团队分组值班，如何快速进行人员分组？

现在有个需求：五一五天假期快到了，公司想安排公司成员分组，每天一批进行值班，怎么去快速将人员分成5组呢？

我们可以用分析函数ntile来处理这个分组需求。

SQL> select ntile(5)over(order by empno) as gp,ename from emp;
        GP ENAME
---------- ----------
         1 test
         1 SMITH
         1 ALLEN
         2 WARD
         2 JONES
         2 MARTIN
         3 BLAKE
         3 CLARK
         3 SCOTT
         4 KING
         4 TURNER
         4 ADAMS
         5 JAMES
         5 FORD
         5 MILLER
15 rows selected

至于怎么分的,这里不进行详细说明了。

总结

本篇文章介绍了4个场景，这四个场景重点介绍了几个函数配合行转列函数的实际使用场景，可以看到，行转列写法有时候可以用来进行SQL优化！~

昆明网站制作建设广告设计与制作怎样制作免费个人网页建设网银登录官网域名中国官网 app制作软件中文版

上一篇：【RabbitMQ】RabbitMQ安装与使用详解以及Spring集成

下一篇：SpringBoot多种打包方法