【数据蒋堂】第30期:JOIN简化 – 消除关联

我们将等值JOIN分成三种情况来分别讨论,分情况相当于加强了条件,我们可以充分利用每种情况下的特征。

1. 外键属性化

先看个例子,设有如下两个表:

employee表和delpartment表的主键都是其中的id字段,employee表的department字段是指向department表的外键,department表的manager字段又是指向employee表的外键。这是很常规的表结构设计。

现在我们想问一下:哪些美国籍员工有一个中国籍经理?

用SQL写出来是这样的:

SELECT A.* FROM employee A JOIN department B ON A.department=B.id JOIN employee C ON B.manager=C.id
 WHERE A.nationality=‘美国' AND C.nationality=‘中国'

句子较长,由于employee表需要两次参与JOIN,还需要为它起个别名加以区分。

我们换一种写法:

SELECT * FROM employee WHERE nationality='美国' AND department.manager.nationality='中国'

当然,这不是标准的SQL语句了。

第二个句子中用红色部分表示当前员工的“所属部门的经理的国籍”。我们把外键字段理解成一个对象,外键表的字段被理解为外键字段的属性,department.manager即是”所属部门的经理“,而这个字段在department中仍然是个外键,那么它的外键表字段可以继续理解为它的属性,也就会有department.manager.nationality,即“所属部门的经理的国籍”。

这种的对象式理解方式,显然比笛卡尔积过滤的理解方式要自然直观得多。外键表JOIN时并不会涉及到两个表的乘法,外键字段只是用于找到外键表中对应的那条记录,完全不会涉及到笛卡尔积这种有乘法特性的运算。

我们前面约定,外键表JOIN时维表中关联字段必须是主键,所以外键字段对应的维表记录一定是唯一的,这样deparment.manager.nationality对于employee表中每一条记录都是唯一的,这就不会发生歧义。而如果不做这个约定,就可能发生多对多,department.manager.nationality无法明确定义。

事实上,这种对象式写法在结构化高级语言(如C,Java)中很常见,在这类语言中,数据就是按对象方式存储的。employee表中的department字段取值根本就是一个对象,而不是编号。其实许多表的主键取值本身并没有业务意义,仅仅是为了区分记录,而外键字段也仅仅是为了找到外键表中的相应记录,如果外键字段直接是对象,就不需要再通过编号来标识了。不过,SQL缺乏离散性,不能直接使用这种存储机制,还要借助编号。

外键表关系中,事实表和维表是不对等的,只能基于事实表去找维表字段,而不会有倒过来的情况。

2. 同维表等同化

同维表的情况相对简单,还是从例子开始,设有两个表:

两个表的主键都是id,经理也是员工,两表共用同样的员工编号,经理会比普通员多一些属性,另用一个经理表来保存。

现在我们要统计所有员工(包括经理)的总收入(加上津贴)。

用SQL写出来还是会用到JOIN:

SELECT employee.id, employee.name, employy.salary+manager.allowance FROM
 employyee LEFT JOIN manager ON employee.id=manager.id

而对于两个一对一的表,我们其实可以简单地把它们看成一个表:

SELECT id,name,salary+allowance FROM employee

同样地,根据我们的约定,同维表JOIN时两个表都是按主键关联的,相应记录是唯一对应的,salary+allowance对employee表中每条记录都是唯一可计算的,不会出现歧义。

同维表之间的关系是对等的,从任何一个表都可以引用到其它同维表的字段。

3. 主子表一体化

订单及订单明细是典型的主子表:

Orders表的主键是id,OrderDetail表中的主键是(id,no),前者的主键是后者的一部分。

现在我们想计算每张订单的总金额。

直接用SQL写出来会是这样:

SELECT Orders.id, Orders.customer, SUM(OrderDetail.price)
 FROM Orders JOIN OrderDetail ON Orders.id=OrderDetail.id
 GROUP BY Orders.id, Orders.customer

要完成这个运算,不仅要用到JOIN,还需要做一次GROUP BY,否则选出来的记录数太多。

如果我们把子表中与主表相关的记录看成主表的一个字段,那么这个问题也可以不再使用JOIN以及GROUP BY:

SELECT id, customer, OrderDetail.SUM(price) FROM Orders

与普通字段不同,OrderDetail被看成Orders表的字段时,其取值将是一个集合,因为两个表是一对多的关系。所以要在这里使用聚合运算把集合值计算成单值。

这样看待数据关联,不仅理解书写更为简单,而且不容易出错。

假如Orders表还有一个子表用于记录回款情况:

我们现在想知道那些订单还在欠钱,也就是累计回款金额小于订单总金额的订单。

简单地把这三个表JOIN起来是不对的,OrderDetail和OrderPayment会发生多对多的关系,这就错了(回忆上一篇中多对多大概率错误的说法)。这两个子表要分别先做GROUP,再一起与Orders表JOIN起来才能得到正确结果,写出来较为繁琐。

而如果我们把子表看成主表的集合字段,那就很简单了:

SELECT id,customer,OrderDetail.SUM(price) x, OrderPayment.SUM(amount) y FROM Orders WHERE x>y

这种写法就不容易发生多对多的错误。

主子表关系是不对等的,不过两个方向的引用都有意义,上面谈了从主表引用子表的情况,从子表引用主表则和外键表类似。

我们改变对JOIN运算的看法,摒弃笛卡尔积的思路,把多表关联运算看成是稍复杂些的单表运算。这样,我们相当于从最常见的等值JOIN运算中基本消除了关联,甚至在语法中取消了JOIN关键字,书写和理解都要简单很多。

原文发布时间为:2017-11-14

本文作者:蒋步星

本文来自云栖社区合作伙伴“ 数据蒋堂 ”,了解相关信息可以关注“ 数据蒋堂 ”微信公众号

云栖团队博客稿源:云栖团队博客 (源链) | 关于 | 阅读提示

本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » 【数据蒋堂】第30期:JOIN简化 – 消除关联

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录