SPL:一对多连接

   连接(JOIN)用于把来自两个或多个表的记录结合起来。本文将探讨对于连接问题,SPL 的解决方案和基本原理。

   表之间存在的数据相互依赖关系,就叫做表间关联关系。表间关联关系可以分为以下几种:一对一、多对一、一对多和多对多。我们可以通过表间关联关系,把两个或多个表连接起来,从而实现多表关联查询的目的。

   一对多关联,是指一张表的一条记录能与另外一张表的任意记录进行对应。在一对多的关系中,我们把“一”的这端表叫做主表,“多”的这端表叫子表(从表)。例如有订单表和订单明细表,每笔订单对应唯一的订单 ID,但是每个订单 ID 可能对应多条订单明细信息。我们称订单表是主表,订单明细表是子表。

【例 1】 根据订单表和订单明细表,统计每个订单的总金额。部分数据如下:

ORDERS:

ID

CUSTOMERID

EMPLOYEEID

ORDER_DATE

ARRIVAL_DATE

10248

VINET

5

2012/07/04

2012/08/01

10249

TOMSP

6

2012/07/05

2012/08/16

10250

HANAR

4

2012/07/08

2012/08/05

10251

VICTE

3

2012/07/08

2012/08/05

10252

SUPRD

4

2012/07/09

2012/08/06

ORDER_DETAIL:

ID

ORDER_NUMBER

PRODUCTID

PRICE

COUNT

DISCOUNT

10814

1

48

102.0

8

0.15

10814

2

48

102.0

8

0.15

10814

3

48

306.0

24

0.15

10814

4

48

102.0

8

0.15

10814

5

48

204.0

16

0.15

   单个主子表时,可以使用函数 join(), 通过子表的部分主键与主表主键进行连接。

   SPL脚本如下:


A

1

=T("Orders.txt")

2

=T("OrderDetail.txt")

3

=join(A1:Orders,ID; A2:Detail,ID)

4

=A3.groups(Orders.ID; sum(Detail.PRICE*Detail.COUNT):AMOUNT)

A1:导入订单表。

A2:导入订单明细表。

A3:订单表和订单明细表通过订单 ID 进行连接。

A4:分组汇总每个订单的总金额。

【例 2】 订单表还有一个子表用于记录回款情况。我们现在想知道哪些订单还没有收到全部回款,也就是累计回款金额小于订单总金额的订单。部分数据如下:

ORDERS:

ID

CUSTOMERID

EMPLOYEEID

ORDER_DATE

ARRIVAL_DATE

10248

VINET

5

2012/07/04

2012/08/01

10249

TOMSP

6

2012/07/05

2012/08/16

10250

HANAR

4

2012/07/08

2012/08/05

10251

VICTE

3

2012/07/08

2012/08/05

10252

SUPRD

4

2012/07/09

2012/08/06

ORDER_DETAIL:

ID

ORDER_NUMBER

PRODUCTID

PRICE

COUNT

DISCOUNT

10814

1

48

102.0

8

0.15

10814

2

48

102.0

8

0.15

10814

3

48

306.0

24

0.15

10814

4

48

102.0

8

0.15

10814

5

48

204.0

16

0.15

ORDER_PAYMENT:

ORDER

PAY_DATE

AMOUNT

CHANNEL

INSTALMENTS

10814

2014/01/05

816.0

3

0

10848

2014/01/23

800.25

2

1

10848

2014/01/23

800.25

0

0

10848

2014/01/23

800.25

3

1

10966

2014/03/20

572.0

2

1

   简单地把这三个表 JOIN 起来是不对的,订单明细和订单回款表会发生多对多的关系。

   子表对着订单 ID 分组后,订单 ID 就变成了具有唯一性的事实上的主键。然后我们就可以把这些表通过订单 ID 进行连接,当作一对一(同维表)关系来处理了。

SPL脚本如下:


A

1

=T("Orders.txt")

2

=T("OrderDetail.txt")

3

=T("OrderPayment.txt")

4

=A2.groups(ID; sum(PRICE*COUNT):AMOUNT)

5

=A3.groups(ORDER; sum(AMOUNT):PAY_AMOUNT)

6

=join(A1:Orders,ID; A4:Detail,ID; A5:Payment,ORDER)

7

=A6.new(Orders.ID, Detail.AMOUNT, Payment.PAY_AMOUNT)

8

=A7.select(PAY_AMOUNT<AMOUNT)

A1:导入订单表。

A2:导入订单明细表。

A3:导入订单回款表。

A4:分组汇总每个订单的总金额。

A5:分组汇总每个订单的回款总金额。

A6:使用函数 join() 将订单表和汇总后的明细表、回款表按照订单 ID 连接。

A7:创建由字段订单 ID、订单金额和回款金额组成的序表。

A8:从中选出回款金额小于订单金额的记录,即还没有完全付清的订单。

   SPL在处理一对多关联(主子表)时大体上分为两步:第一步, 所有子表按照部分主键(主表的主键)进行分组,此时所有子表的事实主键与主表相同了。第二步, 将多个表按照其主键(或事实主键)进行连接。


OrderDetail.txt

OrderPayment.txt

Orders.txt