【SQL开发实战技巧】系列(二十五):数仓报表场景☞结果集中的重复数据只显示一次以及计算部门薪资差异高效的写法以及如何对数据进行快速分组

本文涉及的产品
云原生数据仓库AnalyticDB MySQL版,基础版 8ACU 100GB 1个月
简介: 本篇文章讲解的主要内容是:***如何使用lag函数让结果集重复数据只显示一次、用行转列pivot写法优化部门之间计算工资差异类似需求、如何通过ceil函数对已有数据进行分组打印、放假安排团队分组值班,如何通过ntile()over(order by )快速进行人员分组***

前言

本篇文章讲解的主要内容是:如何使用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

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

  1. 生成序号
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
  1. 通过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
  1. 给各组数据生成序号
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
  1. 通过分组生成序号,并进行行转列
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优化!~

相关实践学习
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
相关文章
|
16天前
|
SQL 移动开发 Oracle
SQL语句实现查询连续六天数据的方法与技巧
在数据库查询中,有时需要筛选出符合特定时间连续性条件的数据记录
|
23天前
|
SQL 存储 关系型数据库
添加数据到数据库的SQL语句详解与实践技巧
在数据库管理中,添加数据是一个基本操作,它涉及到向表中插入新的记录
|
24天前
|
SQL 数据挖掘 数据库
SQL查询每秒的数据:技巧、方法与性能优化
id="">SQL查询功能详解 SQL(Structured Query Language,结构化查询语言)是一种专门用于与数据库进行沟通和操作的语言
|
关系型数据库 MySQL Shell
|
28天前
|
人工智能 自然语言处理 关系型数据库
阿里云云原生数据仓库 AnalyticDB PostgreSQL 版已完成和开源LLMOps平台Dify官方集成
近日,阿里云云原生数据仓库 AnalyticDB PostgreSQL 版已完成和开源LLMOps平台Dify官方集成。
|
18天前
|
人工智能 分布式计算 数据管理
阿里云位居 IDC MarketScape 中国实时湖仓评估领导者类别
国际数据公司( IDC )首次发布了《IDC MarketScape: 中国实时湖仓市场 2024 年厂商评估》,阿里云在首次报告发布即位居领导者类别。
|
20天前
|
SQL 分布式计算 数据挖掘
加速数据分析:阿里云Hologres在实时数仓中的应用实践
【10月更文挑战第9天】随着大数据技术的发展,企业对于数据处理和分析的需求日益增长。特别是在面对海量数据时,如何快速、准确地进行数据查询和分析成为了关键问题。阿里云Hologres作为一个高性能的实时交互式分析服务,为解决这些问题提供了强大的支持。本文将深入探讨Hologres的特点及其在实时数仓中的应用,并通过具体的代码示例来展示其实际应用。
110 0
|
4月前
|
数据采集 运维 Cloud Native
Flink+Paimon在阿里云大数据云原生运维数仓的实践
构建实时云原生运维数仓以提升大数据集群的运维能力,采用 Flink+Paimon 方案,解决资源审计、拓扑及趋势分析需求。
18499 54
Flink+Paimon在阿里云大数据云原生运维数仓的实践
|
2月前
|
存储 机器学习/深度学习 监控
阿里云 Hologres OLAP 解决方案评测
随着大数据时代的到来,企业面临着海量数据的挑战,如何高效地进行数据分析和决策变得尤为重要。阿里云推出的 Hologres OLAP(在线分析处理)解决方案,旨在为用户提供快速、高效的数据分析能力。本文将深入探讨 Hologres OLAP 的特点、优势以及应用场景,并针对方案的技术细节、部署指导、代码示例和数据分析需求进行评测。
119 7
|
2月前
|
运维 数据挖掘 OLAP
阿里云Hologres:一站式轻量级OLAP分析平台的全面评测
在数据驱动决策的今天,企业对高效、灵活的数据分析平台的需求日益增长。阿里云的Hologres,作为一站式实时数仓引擎,提供了强大的OLAP(在线分析处理)分析能力。本文将对Hologres进行深入评测,探讨其在多源集成、性能、易用性以及成本效益方面的表现。
98 7

热门文章

最新文章