专栏首页最新最全的大数据技术体系工作总结之因为笛卡尔积问题写SQL搞了半天[害](附笛卡尔积总结)

工作总结之因为笛卡尔积问题写SQL搞了半天[害](附笛卡尔积总结)

文章目录

背景

管控组同事反馈:宿舍总数异常,加起来的间数比深圳市人口都多,无疑数据是异常的

需求

使宿舍数据恢复正常。

解决过程

尝试过左连接,右连接及内连接等等,发现数据始终比实际数据多出很多,查阅资料判断是产生了笛卡尔积,下文有详细的笛卡尔积解释 根据指引选择where 进行多条件限制仍然不行????。

结果

最后发现在大表b中所有的字段都有,直接from 大表即可。为了笛卡尔积问题花了3个多小时[害]

总结教训下次先观察两张表的字段再改SQL! 起初的SQL:

SELECT
	RAWTOHEX(sys_guid()),
	a.SSLDBH,
	CONCAT(b.SSQY, b.LDMC) LDMC,
	COUNT(a.SSFJH),
	NULL SSLX
FROM
	 a
inner JOIN
	 b
ON
	a.XB =b.XB 
  AND a.XQMC =b.XQMC
  AND a.XYMC =b.YXMC 
  AND a.SSLC = b.LCMC   
  AND a.SSLDBH = SUBSTR(b.SSFJH, 1, 6)
GROUP BY
	a.SSLDBH,
	b.SSQY,
	b.LDMC

修改后的SQL:

SELECT
	RAWTOHEX(sys_guid()),
	SUBSTR(b.SSFJH, 1, 6),
	CONCAT(b.SSQY, b.LDMC) LDMC,
	COUNT(b.SSFJH),
	NULL SSLX
FROM
	 b
GROUP BY
	SUBSTR(b.SSFJH, 1, 6),
	b.SSQY,
	b.LDMC

多表连接简介

关系数据库中,一个查询往往会涉及多个表,因为很少有数据库只有一个表,而如果大多查询只涉及到一个表的,那么那个表也往往低于第三范式,存在大量冗余和异常。

因此,连接(Join)就是一种把多个表连接成一个表的重要手段.

比如简单两个表连接学生表(Student)和班级(Class)表,如图:

进行连接后如图:

笛卡尔积

笛卡尔积在SQL中的实现方式既是交叉连接(Cross Join)。所有连接方式都会先生成临时笛卡尔积表,笛卡尔积是关系代数里的一个概念,表示两个表中的每一行数据任意组合,上图中两个表连接即为笛卡尔积(交叉连接)

在实际应用中,笛卡尔积本身大多没有什么实际用处,只有在两个表连接时加上限制条件,才会有实际意义,下面看内连接

内连接

如果分步骤理解的话,内连接可以看做先对两个表进行了交叉连接后,再通过加上限制条件(SQL中通过关键字on)剔除不符合条件的行的子集,得到的结果就是内连接了.上面的图中,如果我加上限制条件

对于开篇中的两个表,假使查询语句如下:

		SELECT *
  FROM [Class] c
       inner join 
       [Student] s
       on c.ClassID=s.StudentClassID

可以将上面查询语句进行分部理解,首先先将Class表和Student表进行交叉连接,生成如下表:

然后通过on后面的限制条件,只选择那些StudentClassID和ClassID相等的列(上图中划了绿色的部分),最终,得到选择后的表的子集

当然,内连接on后面的限制条件不仅仅是等号,还可以使用比较运算符,包括了>(大于)、>=(大于或等于)、<=(小于或等于)、<(小于)、!>(不大于)、!<(不小于)和<>(不等于)。当然,限制条件所涉及的两个列的数据类型必须匹配.

对于上面的查询语句,如果将on后面限制条件由等于改为大于:

		SELECT *
  FROM [Class] c
       inner join 
       [Student] s
       on c.ClassID>s.StudentClassID

则结果从第一步的笛卡尔积中筛选出那些ClassID大于StudentClassID的子集:

虽然上面连接后的表并没有什么实际意义,但这里仅仅作为DEMO使用:-)

关系演算

上面笛卡尔积的概念是关系代数中的概念,而我在前一篇文章中提到还有关系演算的查询方法.上面的关系代数是分布理解的,上面的语句推导过程是这样的:“对表Student和Class进行内连接,匹配所有ClassID和StudentClassID相等行,选择所有的列”

而关系演算法,更多关注的是我想要什么,比如说上面同样查询,用关系演算法思考的方式是“给我找到所有学生的信息,包括他们的班级信息,班级ID,学生ID,学生姓名”

用关系演算法的SQL查询语句如下:

		SELECT *
  FROM [Class] c
       ,
       [Student] s
  where c.ClassID=s.StudentClassID

当然,查询后返回的结果是不会变的:

外连接

假设还是上面两个表,学生和班级.我在学生中添加一个名为Eric的学生,但出于某种原因忘了填写它的班级ID:

当我想执行这样一条查询:给我取得所有学生的姓名和他们所属的班级:

		SELECT s.StudentName,c.ClassName 
        
  FROM [fordemo].[dbo].[Student] s
       inner join 
       [fordemo].[dbo].[Class] c
       on 
       s.StudentClassID=c.ClassID

结果如下图:

可以看到,这个查询“丢失”了Eric…

这时就需要用到外连接,外连接可以使连接表的一方,或者双方不必遵守on后面的连接限制条件.这里把上面的查询语句中的inner join改为left outer join:

		SELECT s.StudentName,c.ClassName 
        
  FROM [fordemo].[dbo].[Student] s
       left outer join 
       [fordemo].[dbo].[Class] c
       on 
       s.StudentClassID=c.ClassID

结果如下:

Eric又重新出现.

右外连接

右外连接和左外连接的概念是相同的,只是顺序不同,对于上面查询语句,也可以改成:

		SELECT s.StudentName,c.ClassName 
        
  FROM [fordemo].[dbo].[Class] c
       right outer join 
       [fordemo].[dbo].[Student] s
       on 
       s.StudentClassID=c.ClassID

效果和上面使用了左外连接的效果是一样的.

全外连接

全外连接是将左边和右边表每行都至少输出一次,用关键字”full outer join”进行连接,可以看作是左外连接和右外连接的结合.

自连接

谈到自连接,让我们首先从一个表和一个问题开始:

上面员工表(Employee),因为经理也是员工的一种,所以将两种人放入一个表中,MangerID字段表示的是当前员工的直系经理的员工id.

现在,我的问题是,如何查找CareySon的经理的姓名?

可以看出,虽然数据存储在单张表中,但除了嵌套查询(这个会在后续文章中讲到),只有自连接可以做到.正确自连接语句如下:

		SELECT m.EmployeeName
  FROM [fordemo].[dbo].[Employee] e
  inner join [fordemo].[dbo].[Employee] m
  on  e.ManagerID=m.id and e.EmployeeName='Careyson'

在详细解释自连接的概念之前,请再看一个更能说明自连接应用之处的例子:

这个表是一个出席会议记录的表,每一行表示出席会议的记录(这里,由于表简单,我就不用EmployeeID和MeetingID来表示了,用名称对于理解表更容易些)

好了,现在我的问题是:找出既参加“谈论项目进度”会议,又参加”讨论职业发展”会议的员工

乍一看上去很让人迷惑是吧,也许你看到这一句脑中第一印象会是:

		SELECT  EmployeeName
  FROM [fordemo].[dbo].[MeettingRecord] m
  where MeetingName='¨???????????¨¨' and meetingName='¨???????¨°???¤?é?1'

(我用的代码高亮插件不支持中文,所以上面where子句后面第一个字符串是’谈论项目进度’,第二个是’讨论职业发展’)

恩,恭喜你,答错了…如果这样写将会什么数据也得不到.正确的写法是使用自连接!

自连接的是一种特殊的连接,是对物理上相同但逻辑上不相同的表进行连接的方式。我看到百度百科上说自连接是一种特殊的内连接,但这是错误的,因为两个相同表之间不光可以内连接,还可以外连接,交叉连接…在进行自连接时,必须为其中至少一个表指定别名以对这两个表进行区分!

回到上面的例子,使用自连接,则正确的写法为:

		SELECT  m.EmployeeName
  FROM [fordemo].[dbo].[MeettingRecord] m,
        [fordemo].[dbo].[MeettingRecord] m2
  where m.MeetingName='¨???????????¨¨' and m2.MeetingName='¨???????¨°???¤?é?1'
        and m.EmployeeName=m2.EmployeeName

(关于乱码问题,请参考上面)

多表连接

多个表连接实际上可以看成是对N个表进行n-1次双表连接.这样理解会让问题简单很多!

比如上面三个表,前两个表是我们已经在文章开始认识的,假设现在又添加了一个教师表,对这三个表进行笛卡尔积如下:

		SELECT *
  FROM [fordemo].[dbo].[Class]
       cross join 
       [fordemo].[dbo].[Teacher]
       cross join 
       [fordemo].[dbo].[Student]

结果可以如图表示:

总结

文中对SQL中各种连接查询方式都做了简单的介绍,并利用一些Demo实际探讨各种连接的用处,掌握好各种连接的原理是写好SQL查询所必不可少的!

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

没有join条件导致笛卡尔乘积 学过线性代数的人都知道,笛卡尔乘积通俗的说,就是两个集合中的每一个成员,都与对方集合中的任意一个成员有关联。可以想象,在SQL查询中,如果对两张表join查询而没有join条件时,就会产生笛卡尔乘积。这就是我们的笛卡尔乘积导致的性能问题中最常见的案例:开发人员在写代码时遗漏了join条件。

发生笛卡尔乘积的sql:

view plaincopy to clipboardprint?select sum(project_fj.danjiaproject_fj.mianji) from project_fj,orderform where project_fj.zhuangtai=‘未售’ and project_fj.project_id=30 select sum(project_fj.danjiaproject_fj.mianji) from project_fj,orderform where project_fj.zhuangtai=‘未售’ and project_fj.project_id=30

这个语句其实只是sql语句的一部分,问题是另一部分用到了表orderform,所以from中有orderform,但是上面的这部分语句完全没有用到orderform,但是不设置条件就导致了笛卡尔乘积。

解决方法:使用LEFT JOIN

view plaincopy to clipboardprint?select sum(project_fj.danjiaproject_fj.mianji) from project_fj LEFT JOIN orderform ON project_fj.id=orderform.project_id where project_fj.zhuangtai=‘未售’ and project_fj.project_id=30 select sum(project_fj.danjiaproject_fj.mianji) from project_fj LEFT JOIN orderform ON project_fj.id=orderform.project_id where project_fj.zhuangtai=‘未售’ and project_fj.project_id=30

本文参与 腾讯云自媒体分享计划 ,欢迎热爱写作的你一起参与!
本文分享自作者个人站点/博客:https://blog.csdn.net/xianyu120复制
如有侵权,请联系 cloudcommunity@tencent.com 删除。
登录 后参与评论
0 条评论

相关文章

  • 盘点数据库中的一些坑(一)

    此文是个人根据日常工作中遇到的坑以及通过各种资料总结的sql 的踩坑点,有些坑如果在编写sql的时候不注意确实是容易翻车,所以这里一并进行总结。

    阿东
  • 《SQL Cookbook》 - 第三章 多表查询

    因此,UNION可能会进行一次排序操作,以便删除重复项。当处理大结果集就需要考虑这个消耗。

    bisal
  • Hive优化器原理与源码解析系列--优化规则HiveJoinAddNotNullRule(十二)

    此篇文章讲解HiveJoinAddNotNullRule优化规则,此优化规则Rule主要功能是将SQL语句中Inner Join关联时,出现在关...

    用户7600169
  • 如何优雅的解决n 1查询!!!

    我们在写代码的时候非常忌讳出现n+1次查询,这就意味的你的循环有多少次,就会查询多少次数据库,这是很恐怖的场景。

    林老师带你学编程
  • flink sql 知其所以然(六)| flink sql 约会 calcite(看这篇就够了)

    全网第一个 flink sql 实战,本文主要介绍 flink sql 与 calcite 之间的关系。flink sql 的解析主要依赖 calcite。

    公众号:大数据羊说
  • 【T-SQL基础】02.联接查询

    悟空聊架构
  • MySQL数据篇之多表操作-----保姆级教程

    向刚才做的这两个操作(插入一个没有部门的员工和删除一个带有员工的部门),这种情况都是不应该发生的。

    大忽悠爱学习
  • 一文快速入门分库分表中间件 Sharding-JDBC (必修课)

    书接上文 《一文快速入门分库分表(必修课)》,这篇拖了好长的时间,本来计划在一周前就该写完的,结果家庭内部突然人事调整,领导层进行权利交接,随之宣布我正式当爹,...

    程序员小富
  • 步步深入:MySQL 架构总览->查询执行流程->SQL 解析顺序

    一直是想知道一条 SQL 语句是怎么被执行的,它执行的顺序是怎样的,然后查看总结各方资料,就有了下面这一篇博文了。

    业余草
  • 分库分表常见概念解读+Sharding-JDBC实战

    之前有不少刚入坑 Java 的粉丝留言,想系统的学习一下分库分表相关技术,可我一直没下定决心搞,眼下赶上公司项目在使用 sharding-jdbc 对现有 M...

    Guide哥
  • 步步深入:MySQL架构总览-&gt;查询执行流程-&gt;SQL解析顺序

    转载自 https://www.cnblogs.com/annsshadow/p/5037667.html

    allsmallpig
  • 定了!MySQL基础这样学

        人和人交流需要语言,人和数据库交流也需要语言,而这个专门特定为程序员和数据库打交道的语言就是 SQL 语言。

    上分如喝水
  • 复杂SQL性能优化的剖析(一)(r11笔记第36天)

    今天本来是处理一个简单的故障,但是发现是一环套一环,花了我快一天的时间。 开始是早上收到一条报警: 报警内容: CPUutilization is too hi...

    jeanron100
  • 学编程数学到底有多重要?线性代数能否视为一门程序语言呢?

    线性代数告诉我们,“行!按我的语法构造一个矩阵,再按矩阵乘法规则去乘你们的图像,我保证结果就是你们想要的”。

    老九君
  • 【干货】蒋步星:关系代数的问题及尝试

    本文共12000字,建议阅读时间25分钟 本讲座选自北京润乾软件技术有限公司董事长蒋步星。于2015年5月22日在清华大学经管学院上所做的题为《关系代数的问题及...

    数据派THU
  • 《MSSQL2008技术内幕:T-SQL语言基础》读书笔记(上)

      数据库在物理上由数据文件和事务日志文件组成,每个数据库必须至少有一个数据文件和一个日志文件。

    Edison Zhou
  • MySQL优化

    编程之心
  • SQL操作四

    爱撒谎的男孩
  • 数据库系统概论考试心得+复习指南

    试后感悟:老师给的大纲完全就是按照试卷的顺序来的,比如触发器是第一题,数据库恢复是第二题。

    wsuo

扫码关注腾讯云开发者

领取腾讯云代金券