SQL 和 SPL 的多对多连接对比

【摘要】
    连接(JOIN)用于把来自两个或多个表的记录结合起来。SQL 和 SPL 是大家比较熟悉的程序语言,本文将探讨对于连接问题,这两种语言的解决方案和基本原理。如何简便快捷的处理连接运算,这里为你全程解析,并提供 SQL 和 SPL 示例代码。SQL 和 SPL 的多对多连接对比

 

前文介绍了一对一、一对多、多对一这三种表间关联关系,在本文中我们将继续介绍多对多关联关系。

多对多关联:表 A 的一条记录能够对应表 B 中的任意条记录;同时表 B 中的一条记录也能够对应表 A 中的任意条记录。

处理多对多关联关系,可以使用交叉连接。表 A 的每条记录都会与表 B 的所有记录组成一个新的记录,所以返回结果集的行数等于两个表行数的乘积。在 SQL 中 CROSS JOIN 语句用于交叉连接,在 SPL 中提供了函数 xjoin() 用于交叉连接。多对多关联关系在实际应用中很少遇到。我们通过矩阵乘法的例子简单介绍一下:

 

【例 1】 求两个矩阵的乘积。两个矩阵表数据分别存储在 MATRIXA 和 MATRIXB 中:

MATRIXA:

ROW

COL

VALUE

1

1

1

1

2

2

1

3

3

2

1

4

2

2

5

2

3

6

MATRIXB:

ROW

COL

VALUE

1

1

1

1

2

4

2

1

2

2

2

5

3

1

3

3

2

6

本例的数学公式如下:

..

 

SQL的解决方案:

    矩阵乘法是多对多的关系,在 SQL 中可以使用交叉连接。第一步,将两个矩阵表叉乘,选出 A 的列号等于 B 的行号的记录。第二步,对行列号相同的值的值求和。具体语句如下:

 

   SELECT

      "ROW",COL,SUM(VALUE1*VALUE2) VALUE

   FROM (

      SELECT

         A."ROW",A.VALUE VALUE1,B.COL,B.VALUE VALUE2

      FROM MATRIXA A

      CROSS JOIN

      MATRIXB B

      WHERE A.COL=B."ROW"

   )

   GROUP BY "ROW",COL

   ORDER BY "ROW",COL

 

SPL的解决方案:

    SPL提供了函数 xjoin() 用于交叉连接。


A

1

=T("MatrixA.csv")

2

=T("MatrixB.csv")

3

=xjoin(A1:A; A2:B, A.COL==A2.ROW)

4

=A3.groups(A.ROW, B.COL; sum(A.VALUE * B.VALUE):VALUE)

A1:导入矩阵表 A。

A2:导入矩阵表 B。

A3:使用函数 xjoin() 进行交叉连接,在连接的同时按照条件过滤。

A4:分组汇总相同行列号的值的乘积之和。

 

    连接条件不是等值比较时,称为非等值连接。比如年龄是否在某个年龄区间内,收入是否在某个收入区间内等等。

 

【例 2】 根据社区居民表和年龄区间表,查询社区居民所处的年龄段。部分数据如下:

COMMUNITY:

ID

NAME

AGE

1

David

28

2

Daniel

15

3

Andrew

65

4

Rudy


AGE_GROUP:

GROUP_NAME

START

END

Children

0

15

Youth

16

40

Middle

41

60

Old

61

100

 

 

SQL的解决方案:

    在社区居民表中,有的居民没有登记年龄,所以本例可以用左连接(LEFT JOIN)来实现:

 

    SELECT

      ID,NAME,AGE,GROUP_NAME

   FROM COMMUNITY C

   LEFT JOIN

      AGE_GROUP A

   ON A."START"<=C.AGE AND A."END">=C.AGE

   ORDER BY ID

 

SPL的解决方案:

    函数 xjoin() 用于交叉连接,选项 @1 时使用左连接。


A

1

=T("Community.txt")

2

=T("AgeGroup.txt")

3

=xjoin@1(A1:C; A2:A, A2.START<=C.AGE &&   A2.END>=C.AGE)

4

=A3.new(C.ID, C.NAME, C.AGE,A.GROUP_NAME)

A1:导入社区居民表。

A2:导入年龄区间表。

A3:使用函数 xjoin@1() 按照第一个表左连接,在连接的同时选出年龄在相应区间内的记录。当年龄区间无法匹配时保留社区居民记录,年龄段置为空。

A4:返回社区居民所处的年龄段。

 

总结

从上面的讨论可以看出,SQL 对连接的定义非常简单,就是对两个集合(表)做笛卡尔积后再按某种条件过滤。除了常见的等值连接,可以把多对多等值连接甚至非等值连接等都包括进来。但是,有经验的朋友就会知道,现实中绝大部分的连接都是等值连接。SQL 连接过于简单的内涵无法充分体现出最常见等值连接的运算特征。这会导致编写代码和实现运算时就不能利用这些特征,当查询较为复杂时(涉及关联表较多以及有嵌套的情况),无论是书写还是优化都非常困难。

而 SPL 充分利用了这些特征,将等值连接与非等值连接明确的区分开来,针对等值连接的三种关联关系提供了专门的解决方案。SPL 中提供了函数 join()用于一对一(同维表)和一对多(主子表)关联关系,提供了 A.switch() 等函数用于多对一(外键表)关联关系。

    另外 SQL 语句经常要用到临时表、嵌套查询等等,使得 SQL 语句的编写和维护难度提升。而 SPL 只要按自然思维去组织计算逻辑,逐行书写出简洁的代码。

esProc 是专业的数据计算引擎,基于有序集合设计,同时提供了完善的集合运算,相当于 Java 和 SQL 优势的结合。在 SPL 的支持下,连接运算会非常容易。


SQL 与 SPL 对比系列:
SQL 和 SPL 的集合运算对比
SQL 和 SPL 的选出运算对比
SQL 和 SPL 的有序运算对比
SQL 和 SPL 的等值分组对比
SQL 和 SPL 的非等值分组对比
SQL 和 SPL 的有序分组对比
SQL 和 SPL 的一对一和一对多连接对比
SQL 和 SPL 的多对一连接对比
SQL 和 SPL 的多对多连接对比
SQL 和 SPL 的基本静态转置对比
SQL 和 SPL 的复杂静态转置对比
SQL 和 SPL 的动态转置对比
SQL 和 SPL 的递归对比

以下是广告时间

对润乾产品感兴趣的小伙伴,一定要知道软件还能这样卖哟性价比还不过瘾? 欢迎加入好多乾计划。
这里可以低价购买软件产品,让已经亲民的价格更加便宜!
这里可以销售产品获取佣金,赚满钱包成为土豪不再是梦!
这里还可以推荐分享抢红包,每次都是好几块钱的巨款哟!
来吧,现在就加入,拿起手机扫码,开始乾包之旅



嗯,还不太了解好多乾?
猛戳这里
玩转好多乾