【SQL开发实战技巧】系列(二十三):数仓报表场景☞ 如何对数据排列组合去重以及通过如何找到包含最大值和最小值的记录这个问题再次用执行计划给你证明分析函数性能不一定高

本文涉及的产品
云原生数据仓库AnalyticDB MySQL版,基础版 8ACU 100GB 1个月
简介: 怎样对数据组合重新排列并去重的问题、通过如何找到包含最大值和最小值的记录这个问题再次用执行计划给你证明分析函数性能不一定高【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作,毕竟SQL开发在数据分析场景非常重要且基础,面试也会经常问SQL开发和调优经验,相信当我写完这一系列文章,也能再有所收获,未来面对SQL面试也能游刃有余~。本篇文章主要介绍的两个方面,第一个方面曾经有好几个网友和同事问我,第二个问题真的是很多同行的通病,认为分析函数是万金油,一股脑用。

前言

本篇文章讲解的主要内容是:怎样对数据组合重新排列并去重的问题、通过如何找到包含最大值和最小值的记录这个问题再次用执行计划给你证明分析函数性能不一定高
【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作,毕竟SQL开发在数据分析场景非常重要且基础,面试也会经常问SQL开发和调优经验,相信当我写完这一系列文章,也能再有所收获,未来面对SQL面试也能游刃有余~。


一、排列组合去重

下面介绍一个数据组合去重的问题。数据环境模拟如下:

drop table test purge;
create table test(id,t1,t2,t3)  as 
select '1','1','3','2' from dual union all
select '2','1','3','2' from dual union all
select '3','3','2','1' from dual union all
select '4','4','2','1' from dual;

上述测试表中前三列tl、t2、t3的数据组合是重复的(都是1、2、3),要求用查询语句找出这些重复的数据,并只保留一行。我们可以用以下步骤达到需求。
1、把tl、t2、t3这三列用列转行合并为一列。

SQL> select * from test
  2  unpivot(b2 for b3 in (t1,t2,t3));

ID B3 B2
-- -- --
1  T1 1
1  T2 3
1  T3 2
2  T1 1
2  T2 3
2  T3 2
3  T1 3
3  T2 2
3  T3 1
4  T1 4
4  T2 2
4  T3 1

12 rows selected

unpivot的具体用法将在后面介绍。
2、通过listagg函数对各组字符排序并合并。

SQL> with t as
  2   (select * from test unpivot(b2 for b3 in(t1, t2, t3)))
  3  select id, listagg(b2, ',') within group(order by b2) as nb2
  4    from t
  5   group by id;

ID NB2
-- --------------------------------------------------------------------------------
1  1,2,3
2  1,2,3
3  1,2,3
4  1,2,4

3、执行常用的去重语句。

SQL> with t as
  2   (select * from test unpivot(b2 for b3 in(t1, t2, t3))),
  3   t1 as (
  4  select id, listagg(b2, ',') within group(order by b2) as nb2
  5    from t
  6   group by id
  7   )
  8   select t1.*,row_number()over(partition by nb2 order by id) as rn
  9   from t1;

ID NB2                                                                                      RN
-- -------------------------------------------------------------------------------- ----------
1  1,2,3                                                                                     1
2  1,2,3                                                                                     2
3  1,2,3                                                                                     3
4  1,2,4                                                                                     1

SQL> 

如上所示,如果要去掉重复的组合数据,只需要保留RN=1的行即可。

二、找到包含最大值和最小值的记录

构建数据如下:

drop table test purge;
create table test as select * from dba_objects;
create index idx_test_object_id on test(object_id);
begin 
  dbms_stats.gather_table_stats(ownname =>'ZYD' ,tabname => 'TEST',estimate_percent => dbms_stats.AUTO_SAMPLE_SIZE,cascade => TRUE,method_opt =>'FOR ALL COLUMNS SIZE REPEAT');
  end;
  

要求返回最大or最小的object_id及对应的object_name,在有分析函数以前,可以用下面的查询:

SQL> select/*zyd*/ object_name,object_id
  2  from test
  3  where object_id in(
  4  select max(object_id) from test
  5  UNION ALL
  6  select min(object_id) from test
  7  );

OBJECT_NAME                                                                       OBJECT_ID
-------------------------------------------------------------------------------- ----------
C_OBJ#                                                                                    2
TEST                                                                                1578856

需要对员工表扫描三次。但用如下分析函数只需要对员工表扫描一次:

SQL> select/*zyd*/ object_name, object_id
  2    from (select object_name,
  3                 object_id,
  4                 min(object_id) over() min_id,
  5                 max(object_id) over() max_id
  6            from test) x
  7   where object_id in (min_id, max_id);

OBJECT_NAME                                                                       OBJECT_ID
-------------------------------------------------------------------------------- ----------
C_OBJ#                                                                                    2
TEST                                                                                1578856

如果大家形成惯性思维,认为分析函数的效率最高,那就错了。我们通过autotrace看下PLAN(可以多执行几次)。

第一个语句的执行计划,如下图所示:

SQL> set timing on;
SQL> set autotrace traceonly;

SQL> alter session set current_schema=zyd;

Session altered.

Elapsed: 00:00:00.01
SQL> select/*zyd*/ object_name,object_id
from test
where object_id in(
select max(object_id) from test
UNION ALL
select min(object_id) from test
);  2    3    4    5    6    7

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2072175425

------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |             |       2 |      86 |       8   (0)| 00:00:01 |
|   1 |  NESTED LOOPS            |             |       2 |      86 |       8   (0)| 00:00:01 |
|   2 |   NESTED LOOPS            |             |       2 |      86 |       8   (0)| 00:00:01 |
|   3 |    VIEW             | VW_NSO_1         |       2 |      26 |       4   (0)| 00:00:01 |
|   4 |     SORT UNIQUE         |             |       2 |      10 |       4   (0)| 00:00:01 |
|   5 |      UNION-ALL            |             |         |         |          |         |
|   6 |       SORT AGGREGATE        |             |       1 |       5 |          |         |
|   7 |        INDEX FULL SCAN (MIN/MAX)| IDX_TEST_OBJECT_ID |       1 |       5 |       2   (0)| 00:00:01 |
|   8 |       SORT AGGREGATE        |             |       1 |       5 |          |         |
|   9 |        INDEX FULL SCAN (MIN/MAX)| IDX_TEST_OBJECT_ID |       1 |       5 |       2   (0)| 00:00:01 |
|* 10 |    INDEX RANGE SCAN        | IDX_TEST_OBJECT_ID |       1 |         |       1   (0)| 00:00:01 |
|  11 |   TABLE ACCESS BY INDEX ROWID    | TEST             |       1 |      30 |       2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  10 - access("OBJECT_ID"="MAX(OBJECT_ID)")


Statistics
----------------------------------------------------------
      1  recursive calls
      0  db block gets
     11  consistent gets
      0  physical reads
      0  redo size
    685  bytes sent via SQL*Net to client
    552  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
      2  rows processed

可以看到,第一个语句虽然访问了三次,但三次都是用的索引,所以效率并不低。第二个语句执行计划如下图所示:

SQL> select/*zyd*/ object_name, object_id
  from (select object_name,
               object_id,
               min(object_id) over() min_id,
               max(object_id) over() max_id
          from test) x
 where object_id in (min_id, max_id);  2    3    4    5    6    7

Elapsed: 00:00:00.12

Execution Plan
----------------------------------------------------------
Plan hash value: 1093040662

----------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time       |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |   115K|    11M|   540   (1)| 00:00:01 |
|*  1 |  VIEW            |       |   115K|    11M|   540   (1)| 00:00:01 |
|   2 |   WINDOW BUFFER     |       |   115K|  3375K|   540   (1)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| TEST |   115K|  3375K|   540   (1)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"="MIN_ID" OR "OBJECT_ID"="MAX_ID")


Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
       1947  consistent gets
      0  physical reads
      0  redo size
    685  bytes sent via SQL*Net to client
    552  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
      2  rows processed

从执行计划可以看出来,走的是全表扫描,因为用的是分析函数,效率反而更低。所以,除语句的改写外,大家还要学会分析PLAN。


总结

本篇文章主要介绍的两个方面,第一个方面曾经有好几个网友和同事问我,第二个问题真的是很多同行的通病,认为分析函数是万金油,一股脑用。实际解决问题中,优化过很多同类问题的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
相关文章
|
18天前
|
SQL 安全 Go
SQL注入不可怕,XSS也不难防!Python Web安全进阶教程,让你安心做开发!
在Web开发中,安全至关重要,尤其要警惕SQL注入和XSS攻击。SQL注入通过在数据库查询中插入恶意代码来窃取或篡改数据,而XSS攻击则通过注入恶意脚本来窃取用户敏感信息。本文将带你深入了解这两种威胁,并提供Python实战技巧,包括使用参数化查询和ORM框架防御SQL注入,以及利用模板引擎自动转义和内容安全策略(CSP)防范XSS攻击。通过掌握这些方法,你将能够更加自信地应对Web安全挑战,确保应用程序的安全性。
53 3
|
2月前
|
SQL 数据处理 数据库
专坑同事的SQL写法:性能杀手揭秘
【8月更文挑战第29天】在日常的数据库开发与维护工作中,编写高效、清晰的SQL语句是每位数据工程师的必修课。然而,不当的SQL编写习惯不仅能降低查询效率,还可能给同事的工作带来不必要的困扰。今天,我们就来揭秘八种常见的“专坑同事”SQL写法,助你避免成为那个无意间拖慢整个团队步伐的人。
32 1
|
2月前
|
SQL NoSQL 数据库
开发效率与灵活性:SQL vs NoSQL
【8月更文第24天】随着大数据和实时应用的兴起,数据库技术也在不断发展以适应新的需求。传统的SQL(结构化查询语言)数据库因其成熟的数据管理机制而被广泛使用,而NoSQL(Not Only SQL)数据库则以其灵活性和扩展性赢得了众多开发者的青睐。本文将从开发者的视角出发,探讨这两种数据库类型的优缺点,并通过具体的代码示例来说明它们在实际开发中的应用。
51 1
|
2月前
|
SQL 关系型数据库 MySQL
【MySQL 慢查询秘籍】慢SQL无处遁形!实战指南:一步步教你揪出数据库性能杀手!
【8月更文挑战第24天】本文以教程形式深入探讨了MySQL慢SQL查询的分析与优化方法。首先介绍了如何配置MySQL以记录执行时间过长的SQL语句。接着,利用内置工具`mysqlslowlog`及第三方工具`pt-query-digest`对慢查询日志进行了详细分析。通过一个具体示例展示了可能导致性能瓶颈的查询,并提出了相应的优化策略,包括添加索引、缩小查询范围、使用`EXPLAIN`分析执行计划等。掌握这些技巧对于提升MySQL数据库性能具有重要意义。
80 1
|
1月前
|
SQL 分布式计算 大数据
大数据开发SQL代码编码原则和规范
这段SQL编码原则强调代码的功能完整性、清晰度、执行效率及可读性,通过统一关键词大小写、缩进量以及禁止使用模糊操作如select *等手段提升代码质量。此外,SQL编码规范还详细规定了代码头部信息、字段与子句排列、运算符前后间隔、CASE语句编写、查询嵌套、表别名定义以及SQL注释的具体要求,确保代码的一致性和维护性。
32 0
|
2月前
|
前端开发 C# 设计模式
“深度剖析WPF开发中的设计模式应用:以MVVM为核心,手把手教你重构代码结构,实现软件工程的最佳实践与高效协作”
【8月更文挑战第31天】设计模式是在软件工程中解决常见问题的成熟方案。在WPF开发中,合理应用如MVC、MVVM及工厂模式等能显著提升代码质量和可维护性。本文通过具体案例,详细解析了这些模式的实际应用,特别是MVVM模式如何通过分离UI逻辑与业务逻辑,实现视图与模型的松耦合,从而优化代码结构并提高开发效率。通过示例代码展示了从模型定义、视图模型管理到视图展示的全过程,帮助读者更好地理解并应用这些模式。
62 0
|
2月前
|
Java XML Maven
跨越时代的飞跃:Struts 2 升级秘籍——从旧版本无缝迁移到最新版,焕发应用新生!
【8月更文挑战第31天】随着软件技术的发展,Struts 2 框架也在不断更新。本文通过具体案例指导开发者如何从旧版平滑升级到 Struts 2.6.x。首先更新 `pom.xml` 中的依赖版本,并执行 `mvn clean install`。接着检查 `struts.xml` 配置,确保符合新版本要求,调整包扫描器等设置。审查 Action 类及其注解,检查配置文件中的弃用项及插件。更新自定义拦截器实现,并验证日志配置。最后,通过一系列测试确保升级后的系统正常运行。通过这些步骤,可以顺利完成 Struts 2 的版本升级,提升应用的安全性和性能。
106 0
|
2月前
|
SQL 关系型数据库 MySQL
SQL性能调优的神奇之处:如何用优化技巧让你的数据库查询飞起来,实现秒级响应?
【8月更文挑战第31天】在现代软件开发中,数据库性能至关重要。本文通过一个实战案例,展示了从慢查询到秒级响应的全过程。通过对查询的详细分析与优化,包括创建索引、改进查询语句及数据类型选择等措施,最终显著提升了性能。文章还提供了示例代码及最佳实践建议,帮助读者掌握SQL性能调优的核心技巧。
65 0
|
2月前
|
SQL 关系型数据库 MySQL
SQL Server、MySQL、PostgreSQL:主流数据库SQL语法异同比较——深入探讨数据类型、分页查询、表创建与数据插入、函数和索引等关键语法差异,为跨数据库开发提供实用指导
【8月更文挑战第31天】SQL Server、MySQL和PostgreSQL是当今最流行的关系型数据库管理系统,均使用SQL作为查询语言,但在语法和功能实现上存在差异。本文将比较它们在数据类型、分页查询、创建和插入数据以及函数和索引等方面的异同,帮助开发者更好地理解和使用这些数据库。尽管它们共用SQL语言,但每个系统都有独特的语法规则,了解这些差异有助于提升开发效率和项目成功率。
164 0
|
2月前
|
SQL NoSQL 关系型数据库
性能与扩展性的考量:SQL vs NoSQL
【8月更文第24天】在选择数据库系统时,开发者和架构师面临着一个关键决策:是选择传统的SQL(结构化查询语言)数据库还是现代的NoSQL(非关系型)数据库。这两种类型各有优劣,尤其是在性能和扩展性方面。本文将深入探讨SQL和NoSQL数据库在这两个方面的差异,并通过具体的代码示例来展示它们各自的优势。
55 0