石家庄
乾学院 513 号会员
2 回帖  •  652 浏览  • 
     ...

Python 和 SPL 对比 10——一对 N 关联

 

数据分析中，经常会遇到两表或多表关联，表间关联关系可以分为以下几种：一对一、一对多、多对一和多对多。一对一关联，是指一张表的一条记录只与另外一张表的一条记录对应；一对多关联，是指一张表的一条记录与另外一张表的多条记录对应；本文先对比 Python 和 SPL 一对N关联的运算能力。

同维关联

一对一关联也叫同维关联，关联的两表互为同维表。如：

现有学生期末成绩表，平时成绩表，请统计学生的学期成绩（期末考试成绩、平时成绩所占比例分别是 0.7,0.3）。

期末考试成绩表

studentid

fscore

…

s1087

68.36422

…

s1049

78.17515

…

s1018

82.61762

…

…

…

…

平时成绩表

studentid

uscore

…

s1050

75.11157

…

s1039

88.02989

…

s1091

87.95997

…

…

…

…

Python

import pandas as   pd

fexam_file="D:/data/FExamRes.csv"

uperf_file="D:/data/UPerformance.csv"

fexam=pd.read_csv(fexam_file)

uperf=pd.read_csv(uperf_file)

scores=pd.merge(fexam,uperf,on="studentid",how="outer").fillna(0)

scores["total"]=0.7*scores["fscore"]+0.3*scores["uscore"]

print(scores)

 

 

 

期末考试成绩

平时成绩

关联后补 0

计算成绩

 

Python的 merge 函数可以关联两个 Dataframe，因为有可能有的同学缺少某项成绩，所以使用外连接方式，没有成绩时，成绩记为 0，最后再加权求和就得到最终成绩了。Python关联后会得到一个新的Dataframe，数据会被复制，耗时且耗内存。

SPL

A

B

1

D:/data/FExamRes.csv

2

D:/data/UPerformance.csv

3

=file(A1).import@tc()

4

=file(A2).import@tc()

5

=join@f(A3:fexam,studentid;A4:uperf,studentid)

同维关联

6

=A5.new([fexam.studentid,uperf.studentid].conj().id().~:studentid,fexam.fscore:fscore,uperf.uscore:uscore,0.7*fexam.fscore+0.3*uperf.uscore:total)

SPL的join函数完成关联，@f选项是外连接，A6完成成绩的计算。SPL关联只是利用主键（本例中的学号studentid）将两表建立关联，并没有复制数据，内存占用小且运算很快。

多同维表

关联运算经常会遇到三张甚至更多的同维表关联，如：

学生成绩除期末考试成绩和平时成绩外，还有选修课成绩，三者的占比分别是 0.6,0.3,0.1，请统计学生的最终成绩。

期末考试成绩和平时成绩续用上例数据，选修课成绩表如下：

选修课成绩表

studentid

escore

…

s1051

74.66936

…

s1035

57.33134

…

s1047

61.29318

…

…

…

…

Python

#续用 fexam 和 uperf

elec_file="D:/data/ElectiveScore.csv"

fexam=fexam.set_index(keys="studentid")

uperf=uperf.set_index(keys="studentid")

elec =   pd.read_csv(elec_file,index_col='studentid')

student_score =   pd.concat([fexam,uperf,elec],axis=1,join='outer',sort=True).fillna(0).reset_index()

student_score['total']   = 0.6*student_score.fscore+0.3*student_score.uscore+0.1*student_score.escore

print(student_score)

 

 

设置索引

 

读数据时设置索引

多同维表关联

 

 

汇总成绩

 

Python在多个同维表关联时就不能使用 merge 函数了，因为 merge 函数只支持两表关联，本例中的三表关联则要 merge 两次，有点麻烦，不过 Python 提供了 concat 函数，可以根据索引关联，这使得多同维表关联没有那么麻烦了，但需要多掌握一个函数的用法。

SPL

A

B

…

/A3是期末成绩表，A4 是平时成绩表

8

D:/data/ElectiveScore.csv

9

=file(A8).import@tc()

10

=A3.keys(studentid)

/设置主键

11

=A4.keys(studentid)

12

=A9.keys(studentid)

13

=join@f(A3:fexam;A4:uperf;A9:elec)

/多同维表关联

14

=A13.new([fexam.studentid,uperf.studentid,elec.studentid].conj().id().~:studentid,

fexam.fscore:fscore,uperf.uscore:uscore,elec.escore:escore,

0.6*fexam.fscore+0.3*uperf.uscore+0.1*escore:total)

/计算总成绩

SPL 的 join 函数既可以关联两表也可以三表或更多表，设置主键后，关联时会自动按主键关联，后续的运算三表和两表也没有什么区别，还是 SPL 容易举一反三，不需要记忆那么多函数的用法。

主子表

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

现有订单表和订单明细表，请查询 s150 门店各省客户的消费情况。

订单表（订单明细表的主表）：

storeid

orderid

client

location

…

s101

o10001

c104231

Beijing

…

s101

o10002

c107032

Beijing

…

s101

o10003

c108875

Beijing

…

…

…

…

…

…

订单明细表（订单表的子表）

storeid

orderid

productid

price

…

s101

o10001

p1078

1273

…

s101

o10002

p1091

131

…

s101

o10002

p1008

780

…

…

…

…

…

…

 

Python

detail_file="D:/data/Detail.csv"

order_file="D:/data/Order.csv"

detail=pd.read_csv(detail_file)

order=pd.read_csv(order_file)

detail150=detail.query("storeid=='s150'")

order150=order.query("storeid=='s150'")

order_detail=pd.merge(order150,detail150,on="orderid",how="inner")

loc_consume=order_detail.groupby("location").price.sum()

print(loc_consume)

 

 

 

 

s150门店订单明细

s150门店订单

两表关联

分组聚合

 

本例中只有两表关联，关联关系明确，只要用订单号 orderid 将两表关联即可，然后按照地区 location 分组汇总价格就可以了。这里的关联是一对多的关系，但我们也可以将其转换为一对一的关系，

1. 汇总订单明细中每个订单的消费金额；

2. 关联订单表和第 1 步的汇总结果，此时就是一对一的关联关系了；

3. 按地区 location 分组聚合。代码写出来是这样的:

1.      order_sum=detail150.groupby(“orderid”).price.sum()

2.      order_detail=pd.merge(order150, order_sum,on="orderid",how="inner")

3.      loc_consume=order_detail.groupby("location").price.sum()

SPL

A

B

…

…

16

D:/data/Detail.csv

17

D:/data/Order.csv

18

=file(A16).import@tc()

19

=file(A17).import@tc()

20

=A18.select(storeid=="s150")

/选出

21

=A19.select(storeid=="s150")

22

=A20.groups(orderid;sum(price):sprice)

/分组聚合

23

=join(A21:order,orderid;A22:consume,orderid)

/关联

24

=A23.groups(order.location;sum(consume.sprice):consume)

/分组聚合

SPL 是精心设计过的，对于这种主子表关联，鼓励分组聚合——同维关联的方式完成关联运算，因为这样关联关系更清晰，当关联表很多很复杂时这样关联不容易出错。当然 SPL 同样可以完成 Python 先关联在汇总的方法，代码这样写：

A25=join(A21:order,orderid;A20:detail,orderid)

A26=A25.groups(order.location;sum(detail.price):consume)

多层主子表

有时会遇到多层主子表的情况，如：

续用上例的订单表和订单明细表，现在新增门店信息表。

门店信息表（主表）：

storeid

province

…

s101

Heilongjiang

…

s102

Beijing

…

s103

Tianjin

…

…

…

…

请根据门店信息表、订单表和订单明细表，汇总各省门店的销售情况。

Python

#续用 detail 和 order 两表

store_file="D:/data/Store.csv"

store =   pd.read_csv(store_file)

detail_sum =   detail.groupby(['storeid','orderid']).price.sum()

order_detai = pd.merge(order,detail_sum,on=['storeid','orderid'])

store_amount =   order_detai.groupby('storeid',as_index=False).price.sum()

store_inf =   pd.merge(store,store_amount,on=["storeid"])

province_sale =   store_inf.groupby('province',as_index=False).price.sum()

print(province_sale)

 

 

 

汇总每个订单的金额

 

关联订单和汇总后的订单金额

汇总每个门店的销售额

 

关联门店信息和门店销售额

按门店所在省份分组汇总

 

 

订单明细表是订单表的子表，订单表又是门店表的子表，两层主子表。都遵循子表汇总——关联主表这一关联方式，会使关联关系更清晰，而且不会使关联后的表过大或者出现多对多的错误关联。本例中出现了多字段关联的方式，Python 也可以轻松完成。

SPL

A

B

…

…

28

D:/data/Store.csv

29

=file(A28).import@tc()

30

=A18.groups(storeid,orderid;sum(price):sprice)

/汇总每个订单的金额

31

=join(A19:order,[storeid,orderid];A30:detail_sum)

/关联订单和汇总后的订单金额

32

=A31.groups(order.storeid;sum(detail_sum.sprice):sales)

/汇总每个门店的销售额

33

=join(A29:store,storeid;A32:store_amount)

/关联门店信息和门店销售额

34

=A33.groups(store.province;sum(store_amount.sales):sales)

/按门店所在省份分组汇总

SPL 按相同的思路完成运算也不在话下。

一主表多子表

一张主表多张子表的情况也比较常见，例如订单表、订单明细表和订单回款表。订单明细表和订单回款表都是订单表的子表。如：

订单表，订单明细表，回款表部分内容如下：

订单表（主表）

orderid

clientid

date

…

10012

100658

2019/2/13

…

10023

103478

2019/1/12

…

10040

108013

2019/1/4

…

…

…

…

…

订单明细表（子表）

orderid

productid

price

…

10012

3018

428.5

…

10012

3019

349.2

…

10023

3019

349.2

…

…

…

…

…

订单回款表（子表）

orderid

term

payment

…

10012

1

259.2

…

10012

2

259.2

…

10012

3

259.3

…

…

…

…

…

请根据订单表、订单明细表和订单回款表，找出未完全回款的订单。

Python

detail_file="D:/data/Detail_2.csv"

order_file="D:/data/Order_2.csv"

pay_file="D:/data/Payment_2.csv"

detail_2 =   pd.read_csv(detail_file)

order_2 =   pd.read_csv(order_file,index_col='orderid')

pay =   pd.read_csv(pay_file)

detail_order =   detail_2.groupby('orderid').price.sum()

pay_order = pay.groupby('orderid').payment.sum()

order_d_p =   order_2.join([pay_order,detail_order])

nopay_order =   order_d_p.query('price>payment')

print(nopay_order)

 

 

 

 

 

 

汇总订单金额

汇总回款金额

关联主表和两张子表

选出

订单明细表是订单表的子表，回款表也是订单表的子表，一张主表两张子表。还是按照子表汇总——关联主表这一关联方式。Python 在实现上和上例不同了，因为 merge 不可以同时关联三张表，换用另一个函数 join，它可以利用索引将三表关联，建立一张新的宽表，最后拿这张宽表选出结果，在关联方式上和之前的思路略有不同。

SPL

A

B

…

…

36

D:/data/Detail_2.csv

37

D:/data/Order_2.csv

38

D:/data/Payment_2.csv

39

=file(A36).import@tc()

40

=file(A37).import@tc()

41

=file(A38).import@tc()

42

=A39.groups(orderid;sum(price):amount)

/汇总订单金额

43

=A41.groups(orderid;sum(payment):payment)

/汇总回款金额

44

=join(A40:order,orderid;A42:amount;A43:payment)

关联主表和两张子表

45

=A44.select(amount.amount>payment.payment).(order)

/选出

SPL 仍然是按照之前的思路，一步一步完成，关联多张表时也还是 join 函数，只在选出时将订单表展示出来就行了。

小结

Python 进行关联时，是将两张表复制，产生一张新的宽表，然后利用宽表来完成后续的运算，这会增加内存消耗且效率不高；在遇到多表关联时，Python 需要使用其他的函数，并且有一些特殊的条件才可以完成，否则只能使用 merge 函数关联多次，略显麻烦。

SPL 的关联只是将两表建立关联关系，不会复制数据，运算时利用建立好的关联关系迅速完成运算，内存占用小，运行效率高；在遇到多表关联时，SPL 同样从容，两张表和 N 张表的关联对于 SPL 来说是一样的，同样的问题用同样的方式解决，不需要烧脑绕路。

主子关联（一对多）时，将子表汇总后再与主表关联就成了同维关联，因此将两者放在一起介绍。