将两个表或者多个表关联在一起是常见的运算，这时通常使用 SQL join 的方式进行关联并进行后续计算。但有时数据并不存储在数据库，而是以文件的形式存储在文件系统，单纯为了计算而把数据存储到数据库有点得不偿失。
Python 的 Pandas 提供了丰富的关联运算函数，能更方便的完成文本文件间的关联计算，现在我们就一起来讨论下 Python 的关联处理。
基础关联
所谓关联是指两张数据表通过某个字段或者某些字段之间存在的某种关系，将两张表以某种条件关联起来。SQL 中的关联可以是等值 JOIN 也可以是非等值 JOIN，所谓非等值 JOIN 就是 JOIN 时的过滤条件不一定是相等的，比如 select A.x,B.y from A join B on A.a
关联的方式有内连接、左连接、右连接、全连接。Pandas 使用 merge() 函数的 how 参数完成，其中 inner：内连接，left：左连接，right：右连接，outer：全连接（默认 how=‘inner’）。下面以一些例子加以说明。
合同数据文件和老客户数据文件分别存储在两份文件中，合同数据文件是 2019 年的合同信息，其中的 Client 可能有新客户，部分内容如下：
|
ContractNo
|
ActualSale
|
SellDate
|
Product
|
Quantity
|
Amount
|
Client
|
ApplyArea
|
ApplyMethod
|
|
10961
|
8
|
2019/3/19
|
52
|
6
|
1122
|
C67350
|
EastChina
|
SELF_USE
|
|
10962
|
8
|
2019/3/19
|
7
|
45
|
3584
|
C67393
|
SouthChina
|
SELF_USE
|
|
10963
|
9
|
2019/3/19
|
60
|
2
|
68
|
C27452
|
NorthChina
|
RESELL
|
|
10964
|
3
|
2019/3/20
|
18
|
6
|
2052.5
|
C77608
|
SouthChina
|
RESELL
|
|
10965
|
6
|
2019/3/20
|
51
|
16
|
848
|
C57885
|
EastChina
|
SELF_USE
|
|
10966
|
4
|
2019/3/20
|
37
|
8
|
1255.6
|
C13113
|
SouthChina
|
SELF_USE
|
老客户数据文件是历年来经常合作的老客户的信息，部分内容如下：
|
ID
|
Name
|
City
|
State
|
Country
|
SalesGoal
|
|
C908
|
ALFKI
|
Shenyang
|
Liaoning
|
China
|
3
|
|
C2262
|
ANATR
|
Dalian
|
Liaoning
|
China
|
1
|
|
C4352
|
ANTON
|
Yingkou
|
Liaoning
|
China
|
10
|
|
C4842
|
AROUT
|
benxi
|
Liaoning
|
China
|
6
|
|
C5505
|
BERGS
|
Shijiazhuang
|
Hebei
|
China
|
5
|
内连接（inner）
内连接，是两表关联后，保留两表共有的键。
问题一：计算老客户 2019 年的合同总金额
问题分析：由于合同数据中可能有新客户，也可能有老客户没有在 2019 年签订合同，所以需要使用内连接的方式将两表关联。
Python 代码
|
import pandas as pd
contract = pd.read_csv('Contract.csv')
client = pd.read_csv('Client.csv')
join_inner = pd.merge(contract,client,left_on='Client',right_on='ID')
print(join_inner['Amount'].sum())
|
导入 pandas
合同数据
客户数据
内连接
计算合同总金额
|
讨论：本例中关联列在两个 dataframe 中列名不同，不能使用 on 参数，而是使用 left_on，左 dataframe 的关联列名，和 right_on，右 dataframe 的关联列名，此时默认的参数 how=’inner’，对关联结果的 Amount 列求和得到结果。
左连接（left）
左连接，指两表关联后，保留左侧表的全部键，右侧表不具备左侧键的列为 nan。
问题二：计算出各省客户的合同金额，新客户的省份用 unknown 表示。
问题分析：因为不光要求各省老客户的合同金额，还要把新客户作为 unknown 来计算，因此关联的时候需要使用 left 的方式。
Python 代码
|
import pandas as pd
contract = pd.read_csv('Contract.csv')
client = pd.read_csv('Client.csv',usecols=['ID','State'])
join_left = pd.merge(contract,client,left_on='Client',right_on='ID',how='left')
join_left.fillna('unknown',inplace=True)
state_amount = join_left.groupby('State',as_index=False).Amount.sum()
print(state_amount)
|
左连接
‘unknown’填补缺失值
分组计算各省客户合同金额
|
讨论：修改默认参数 how=‘inner’为‘left’，实现左连接，保留新客户的合同信息。后续再用‘unknown’填补缺失值，分组求和得到结果。
右连接和全连接的方式就是修改 how=‘right’或者 how=‘outer’。这里就不再一一举例了。
左排除连接（left_exluding）
左排除连接，指两表关联后，只保留左侧键值未在右侧表的键出现的行，右侧表的列为 nan。
问题三：计算新客户的合同总金额
问题分析，只求新客户，只需要左连接后，排除掉两表共有键的行就可以了。
Python 代码
|
import pandas as pd
contract = pd.read_csv('Contract.csv')
client = pd.read_csv('Client.csv',usecols=['ID','State'])
join_left = pd.merge(contract,client,left_on='Client',right_on='ID',how='left',indicator=True)
join_left_exluding = join_left.query('_merge == "left_only"').drop('_merge', 1)
new_client_amount = join_left_exluding.Amount.sum()
print(new_client_amount)
|
左连接
左排除连接
计算新客户合同金额
|
讨论：左连接时，增加参数 indicator，那些两表共有的键的行为 both，左表独有的键的行为 left_only，右表独有的为 right_only。然后筛选出 left_only 标记的行就得到了新客户的行，直接求和得到结果。
右排除连接和全排除连接和左排除连接类似，这里也不举例说明了。
Pandas 中的关联函数除了 merge，还有 join 和 concat，我们继续以问题一——计算老客户 2019 年的合同总金额作为案例简单介绍一下这两个函数的使用方法。
join 函数
Python 代码
|
import pandas as pd
contract = pd.read_csv('Contract.csv',index_col='Client')
client = pd.read_csv('Client.csv',index_col='ID')
join_inner = contract.join(client,how='inner')
print(join_inner.Amount.sum())
|
导入合同数据，同时设置索引为‘Client’
导入客户数据，同时设置索引为‘ID’
使用 join 关联两表
|
讨论：join() 函数关联时，只能使用两表的索引，因此需要先将关联列设置成索引。join 时默认的方式 how=‘left’，这里需要修改为‘inner’。最后求和即可。
concat 函数
问题分析：concat()函数关联时，要求关联表的索引是唯一的，这里合同数据的 Client 列有重复，不适合使用 concat() 函数关联，不过为了更好的理解 concat 函数，我们对数据中的客户去重，然后再用 concat 连接。
问题四：求老客户 2019 年第一份合同的总金额。
Python 代码
|
import pandas as pd
contract = pd.read_csv('Contract.csv')
client = pd.read_csv('Client.csv',index_col='ID')
contract = contract.drop_duplicates('Client', keep='first')
contract.set_index('Client',inplace=True)
join_concat = pd.concat([contract,client],axis=1, join='inner')
print(join_concat.Amount.sum())
|
导入客户数据，同时设置索引为‘ID’
Client列去重
设置 Client 为索引
concat关联
|
讨论：把两个 dataframe 放入列表，axis=1 指按行索引进行关联，默认的 join 方式是 outer，这里改为 inner，最后进行求和。
多字段关联
多字段关联是指两表关联时的键不是单一字段，需要同时关联两个或者更多字段。
现有学生表，部分内容如下：
|
studentid
|
name
|
majorid
|
classid
|
|
20200503011
|
Jessica
|
5
|
3
|
|
20200503012
|
Daniel
|
5
|
3
|
|
20200504013
|
Alyssa
|
5
|
4
|
班级表，部分内容如下：
|
majorid
|
classid
|
teacher
|
|
5
|
3
|
Timothy
|
|
5
|
4
|
Alexis
|
问题五：查询学生的学号、姓名、专业、班级和班主任
问题分析，要查询学生的学号、姓名、专业、班级和班主任，需将两表按照专业号 majorid 和班级号 classid 进行关联。
Python 代码：
|
import pandas as pd
stt = pd.read_csv('student.csv')
cls = pd.read_csv('class.csv')
mul_col_join = pd.merge(stt,cls,on=['majorid','classid'])
print(mul_col_join)
|
多字段关联
|
讨论：Pandas 支持多字段关联，只要把要关联的字段放入列表里就可以了。
如果两表的关联字段名不同，如学生表的专业号和班级号是 MAJORID 和 CLASSID。
Python 代码
|
import pandas as pd
stt = pd.read_csv('student.csv')
cls = pd.read_csv('class.csv')
mul_col_join = pd.merge(stt,cls,left_on=['MAJORID','CLASSID'],right_on=['majorid','classid'])
print(mul_col_join)
|
多字段关联
|
讨论：merge 函数中使用 left_on 和 right_on 参数即可。
同维关联
表 A 的主键与表 B 的主键关联，他们是一对一的关系，A 和 B 互称为同维表。
（一）多同维表
期末考试成绩表，平时成绩表，选修课成绩表部分内容如下：
期末考试成绩表
|
studentid
|
fscore
|
…
|
|
s1087
|
68.36422
|
…
|
|
s1049
|
78.17515
|
…
|
|
s1018
|
82.61762
|
…
|
|
…
|
…
|
…
|
平时成绩表
|
studentid
|
uscore
|
…
|
|
s1050
|
75.11157
|
…
|
|
s1039
|
88.02989
|
…
|
|
s1091
|
87.95997
|
…
|
|
…
|
…
|
…
|
选修课成绩表
|
studentid
|
escore
|
…
|
|
s1051
|
74.66936
|
…
|
|
s1035
|
57.33134
|
…
|
|
s1047
|
61.29318
|
…
|
|
…
|
…
|
…
|
问题六：统计学生的学期成绩（期末考试成绩、平时成绩、选修课成绩所占比例分别是 0.6,0.3,0.1）。
问题分析：
三张表都是以学生学号为主键，但有的学生可能没有参加某一项的考试，因此可能会有学生缺少某一项成绩，在关联的时候需要使用外关联的方式。
关联关系如下图：
![..]()
Python 代码：
|
import pandas as pd
fexam = pd.read_csv("FExamRes.csv",index_col='studentid')
uper = pd.read_csv("UPerformance.csv",index_col='studentid')
elec = pd.read_csv("ElectiveScore.csv",index_col='studentid')
student_score = pd.concat([fexam,uper,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)
|
读取三类成绩单，并把学号设置为索引列
同时关联三张表
加权计算总成绩
|
讨论：因为三类成绩的主键都是学生学号，因此将学号设为索引，然后使用 concat 函数同时关联三张表，使得关联更加快捷。
（二）多层主子表
表 A 的主键与表 B 的部分主键关联，A 称为主表，B 称为子表。主子表是多对一的关联关系。
门店信息，订单表，订单明细表部分内容如下：
门店信息表（主表）：
|
storied
|
province
|
…
|
|
s101
|
Heilongjiang
|
…
|
|
s102
|
Beijing
|
…
|
|
s103
|
Tianjin
|
…
|
|
…
|
…
|
…
|
订单表（门店信息表的子表，订单明细表的主表）：
|
storeid
|
ordered
|
client
|
location
|
…
|
|
s101
|
o10001
|
c104231
|
Beijing
|
…
|
|
s101
|
o10002
|
c107032
|
Beijing
|
…
|
|
s101
|
o10003
|
c108875
|
Beijing
|
…
|
|
…
|
…
|
…
|
…
|
…
|
订单明细表（订单信息表的子表）
|
storeid
|
ordered
|
productid
|
price
|
…
|
|
s101
|
o10001
|
p1078
|
1273
|
…
|
|
s101
|
o10002
|
p1091
|
131
|
…
|
|
s101
|
o10002
|
p1008
|
780
|
…
|
|
…
|
…
|
…
|
…
|
…
|
问题七：查看各门店各省客户的消费情况和各省门店的销售情况。
问题分析：1. 订单表和订单明细表关联，其中订单表是主表，订单明细表是子表，关联字段是 [storeid,ordered]，关联后分组汇总即得结果。 2. 门店信息和第 1 步的汇总结果关联，门店信息表是主表，第 1 步的汇总结果是子表，关联字段是 storeid，关联后分组汇总即得结果。
关联关系如下图：
![..]()
Python 代码：
|
import pandas as pd
store = pd.read_csv("Store.csv")
order = pd.read_csv("Order.csv")
detail = pd.read_csv("Detail.csv")
order_detail = pd.merge(order,detail,on=['storeid','ordered'])
store_client_sale = order_detail.groupby(['storeid','location'],as_index=False).price.sum()
print(store_client_sale)
store_order_detail = pd.merge(store,order_detail,on=["storeid"])
province_sale = store_order_detail.groupby('province',as_index=False).price.sum()
print(province_sale)
|
订单表和订单明细表关联
分组汇总
门店信息表与汇总结果关联
分组汇总
|
讨论：主子表关联时要一步一步理清关联关系，找准关联字段和需汇总的字段，必要的时候画出关联关系图。本例实际上是两次主子表关联，每一步关联也可以按照分组汇总——同维关联——分组汇总的方式完成。具体代码如下：
|
import pandas as pd
store = pd.read_csv("Store.csv")
order = pd.read_csv("Order.csv")
detail = pd.read_csv("Detail.csv")
detail_sum = detail.groupby(['storeid','ordered']).price.sum()
order_detai = pd.merge(order,detail_sum,on=['storeid','ordered'])
store_client_amount = order_detai.groupby(['storeid','location'],as_index=False).price.sum()
print(store_client_amount)
store_amount = store_client_amount.groupby('storeid',as_index=False).price.sum()
store_order_detail = pd.merge(store,store_client_amount,on=["storeid"])
province_sale = store_order_detail.groupby('province',as_index=False).price.sum()
print(province_sale)
|
按门店和订单分组汇总
订单与汇总结果同维关联
按客户地区分组汇总
按门店分组汇总
门店与汇总结果同维关联
按门店省份分组汇总
|
（三）多子表查询
订单表，订单明细表，回款表部分内容如下：
订单表（主表）
|
ordered
|
clientid
|
date
|
…
|
|
10012
|
100658
|
2019/2/13
|
…
|
|
10023
|
103478
|
2019/1/12
|
…
|
|
10040
|
108013
|
2019/1/4
|
…
|
|
…
|
…
|
…
|
…
|
订单明细表（子表）
|
ordered
|
productid
|
price
|
…
|
|
10012
|
3018
|
428.5
|
…
|
|
10012
|
3019
|
349.2
|
…
|
|
10023
|
3019
|
349.2
|
…
|
|
…
|
…
|
…
|
…
|
回款表（子表）
|
ordered
|
term
|
payment
|
…
|
|
10012
|
1
|
259.2
|
…
|
|
10012
|
2
|
259.2
|
…
|
|
10012
|
3
|
259.3
|
…
|
|
…
|
…
|
…
|
…
|
问题八：找出未完全回款的订单
问题分析：订单表是主表，订单明细表和回款表都是子表，关联的字段是 ordered。注意这里不可以先用主表分别与两个子表关联，再用两个关联结果关联，因为这样做会发生多对多的关联，使得计算错误。正确的做法应该是先对两个子表汇总，然后再分别与主表关联，得到最终的结果。
关联关系如下：
![..]()
Python 代码：
|
import pandas as pd
order = pd.read_csv('Order.csv',index_col='ordered')
detail = pd.read_csv('Detail.csv')
pay = pd.read_csv('Payment.csv')
pay_order = pay.groupby('ordered').payment.sum()
detail_order = detail.groupby('ordered').price.sum()
order_d_p = order.join([pay_order,detail_order])
nopay_order = order_d_p.query('price>payment')
print(nopay_order)
|
读取订单表并设置索引
订单明细表分组汇总
回款表分组汇总
订单表关联汇总后的两张子表
筛选
|
讨论：两子表汇总结果与订单表的索引相同，此时可以使用 join 函数或者 concat 函数同时关联多表，最后筛选即可得到结果。
外键关联
表 A 的某些字段与表 B 的主键关联，表 A 的关联字段可以不唯一，表 B 的关联字段唯一，这就是多对一关联，也称作外键关联，即表 A 是事实表，表 B 是维表，A 表中与 B 表主键关联的字段称为 A 指向 B 的外键，B 也称为 A 的外键表。下面介绍几种常见的外键关联实例：
（一）一个事实表，多个不同维表。
销售记录表、城市信息表、产品信息表部分内容如下：
销售记录表 (事实表)：
|
recordid
|
product
|
sale_city
|
amount
|
…
|
|
sr100001
|
p1003
|
c104
|
380
|
…
|
|
sr100002
|
p1005
|
c103
|
400
|
…
|
|
sr100003
|
p1003
|
c104
|
626
|
…
|
|
…
|
…
|
…
|
…
|
…
|
城市信息表（维表 1）：
|
cityid
|
name
|
province
|
…
|
|
c101
|
Beijing
|
Beijing
|
…
|
|
c102
|
Tianjin
|
Tianjin
|
…
|
|
c103
|
Harbin
|
Heilongjiang
|
…
|
|
…
|
…
|
…
|
…
|
产品信息表（维表 2）：
|
productid
|
pclass
|
…
|
|
p1001
|
A
|
…
|
|
p1002
|
A
|
…
|
|
p1003
|
B
|
…
|
|
…
|
…
|
…
|
问题九：汇总各个省份各类产品的销售额。
问题分析：计算各省的各类产品的销售额，需要用销售记录表的 sale_city 和城市信息表的 cityid 关联获取省份的信息，用 product 和产品信息表的 productid 关联获取产品类别信息。
关联关系如下图：
![..]()
Python 代码
|
import pandas as pd
record = pd.read_csv("SaleRecord.csv")
city = pd.read_csv("City.csv")
product = pd.read_csv("Product.csv")
recity = pd.merge(record,city,left_on="sale_city",right_on="cityid")
recityp = pd.merge(recity,product,left_on="product",right_on="productid")
res = recityp.groupby(['province','pclass'],as_index=False).amount.sum()
print(res)
|
销售记录与城市信息关联
销售记录与城市信息关联结果与产品信息关联
分组汇总
|
讨论：销售记录表作为事实表，分别与两个维表关联，得到一张宽表，最后对宽表分组汇总即可。
（二）一个事实表，多个维表有维表被多次使用。
销售记录表，城市信息表部分内容如下：
销售记录表（事实表）：
|
recordid
|
product
|
product_city
|
sale_city
|
amount
|
…
|
|
sr100001
|
p1006
|
c105
|
c103
|
603
|
…
|
|
sr100002
|
p1005
|
c105
|
c102
|
1230
|
…
|
|
sr100003
|
p1003
|
c102
|
c102
|
885
|
…
|
|
…
|
…
|
…
|
…
|
…
|
…
|
城市信息表（维表）：
|
cityid
|
name
|
province
|
…
|
|
c101
|
Beijing
|
Beijing
|
…
|
|
c102
|
Tianjin
|
Tianjin
|
…
|
|
c103
|
Harbin
|
Heilongjiang
|
…
|
|
…
|
…
|
…
|
…
|
问题十：找出产品产地与销售地在同一省份的销售记录。
问题分析：产品产地与销售地都要关联城市信息，从而找到两者在同一省的销售记录，城市信息表被关联了两次。
关联关系如下：
![..]()
Python 代码：
|
import pandas as pd
record = pd.read_csv("SaleRecord.csv")
city = pd.read_csv("City.csv")
recitys = pd.merge(record,city,left_on="sale_city",right_on="cityid")
recitysp = pd.merge(recitys,city,left_on="product_city",right_on="cityid",suffixes=('_s', '_p'))
res = recitysp[recitysp['province_s']==recitysp['province_p']].recordid
print(res)
|
销售地与城市信息关联
产品产地与城市信息关联
过滤
|
讨论：第二次关联城市信息时，recitys 的表中已经有了城市的信息，因此需要设置相同字段名的后缀，这里把销售地的城市信息加后缀“_s”，产地信息后缀“_p”，最后过滤出两者省份相同的记录即可。
（三）多层维表
销售记录表、城市信息表、产品信息表部分内容如下：
销售记录表 (事实表)：
|
recordid
|
product
|
sale_city
|
amount
|
…
|
|
sr100001
|
p1003
|
c104
|
380
|
…
|
|
sr100002
|
p1005
|
c103
|
400
|
…
|
|
sr100003
|
p1003
|
c104
|
626
|
…
|
|
…
|
…
|
…
|
…
|
…
|
城市信息表（维表 1）：
|
cityid
|
name
|
province
|
…
|
|
c101
|
Beijing
|
Beijing
|
…
|
|
c102
|
Tianjin
|
Tianjin
|
…
|
|
c103
|
Harbin
|
Heilongjiang
|
…
|
|
…
|
…
|
…
|
…
|
产品信息表（维表 2）：
|
productid
|
product_city
|
…
|
|
p1001
|
c104
|
…
|
|
p1002
|
c103
|
…
|
|
p1003
|
c102
|
…
|
|
…
|
…
|
…
|
问题十一：找出产品产地与销售地在同一省份的销售记录。
问题分析：为获取产品信息，需要用销售记录关联产品信息。关联产品信息后就和例 2 类似了，分别用产地和销售地关联城市信息，过滤后就可得到产品产地与销售地在同一省份的销售记录。
关联关系如下：
![..]()
Python 代码
|
import pandas as pd
record = pd.read_csv("SaleRecord.csv")
city = pd.read_csv("City.csv")
product = pd.read_csv("Product.csv")
recityp = pd.merge(record,product,left_on="product",right_on="productid")
recitys = pd.merge(recityp,city,left_on="sale_city",right_on="cityid")
recitysp = pd.merge(recitys,city,left_on="product_city",right_on="cityid",suffixes=('_s', '_p'))
res = recitysp[recitysp['province_s']==recitysp['province_p']].recordid
print(res)
|
销售记录与产品信息关联关联
销售地与城市信息关联
产品产地与城市信息关联
过滤
|
讨论：三次关联，1. 销售记录与产品；2. 销售地与城市；3. 产地与城市。最后对三次关联后的结果过滤即可。
（四）自关联
员工信息表部分内容如下：
|
empid
|
name
|
superior
|
…
|
|
7902
|
FORD
|
7566
|
…
|
|
7788
|
SCOTT
|
7566
|
…
|
|
7900
|
JAMES
|
7698
|
…
|
|
…
|
…
|
…
|
…
|
问题十二：列出所有员工姓名及其直接上级的姓名。
问题分析：要获取上级的名字须用上级的工号 superior 与员工表的工号 empid 关联。
关联关系如下：
![..]()
Python 代码
|
import pandas as pd
emp = pd.read_csv("Employee.csv")
emps = pd.merge(emp,emp,left_on="superior",right_on="empid",suffixes=('', '_m'),how="left")
res = emps[['name','name_m']]
print(res)
|
superior和 empid 关联
取名字和上级名字
|
讨论：自己既是事实表又是维表，关联的时候设置后缀即可。
（五）环状关联。
员工信息表，部门信息表部分内容如下：
员工信息表：
|
empid
|
name
|
dept
|
province
|
…
|
|
1
|
Rebecca
|
6
|
Beijing
|
…
|
|
2
|
Ashley
|
2
|
Tianjin
|
…
|
|
3
|
Rachel
|
7
|
Heilongjiang
|
…
|
|
…
|
…
|
…
|
…
|
…
|
部门信息表：
|
deptid
|
name
|
manager
|
…
|
|
1
|
Administration
|
20
|
…
|
|
2
|
Finance
|
2
|
…
|
|
3
|
HR
|
162
|
…
|
|
…
|
…
|
…
|
…
|
问题十三：找出北京经理的北京员工
问题分析：首先要关联部门信息表，找到经理字段；然后用经理字段关联员工编号；最后筛选出北京经理的北京员工。
关联关系如图：
![..]()
Python 代码：
|
import pandas as pd
emp = pd.read_csv("Employee.csv")
dept = pd.read_csv("Department.csv")
empd = pd.merge(emp,dept,left_on="dept",right_on="deptid")
empdm = pd.merge(empd,emp,left_on="manager",right_on="empid",suffixes=('', '_m'))
res = empdm[(empdm['province']=="Beijing") & (empdm['province_m']=="Beijing")].name_x
print(res)
|
关联部门信息表
经理字段关联员工编号
筛选
|
讨论：第一步关联时，员工信息表是事实表，部门信息表是维表；第二步关联时，关联的结果是事实表，员工信息表是维表，这就形成了环状关联。由于员工信息表和部门表都有 name 字段，所以第一步关联时，默认为员工信息表的 name 字段增加了后缀“_x”。
9. 多表混合关联
现有以下几张表：订单表，订单明细表，产品信息表，员工信息表，出差信息表，客户信息表，城市信息表，他们的表结构与关联关系如下：
![..]()
问题十四：计算出差时间大于 10 天的 90 后销售员在各省份销售黑龙江商品的金额。
问题分析：
多张表关联，要理清下述三个问题：
(1) 关联关系，多对一、一对一、一对多（当发生多对多关联时，多半是错了）。
(2) 关联方式，内连接、左连接、右连接、全连接。
(3) 关联与处理的顺序，先处理（包括过滤和分组）还是先关联。
Python 代码：
|
import pandas as pd
pd.set_option('display.max_rows', None)
emp = pd.read_csv("Employee.csv")
trv = pd.read_csv("Travel.csv")
emp_inf = pd.merge(emp,trv,on=["empid","name"])
years = pd.to_datetime(emp_inf.birthday).dt.year
emp_inf_c = emp_inf[(years>=1990) & (years<2000)&(emp_inf.time>=10)]
clt = pd.read_csv("Client.csv")
city = pd.read_csv("City.csv")
sale_location = pd.merge(clt,city,left_on='city',right_on='cityid')
pdt = pd.read_csv("Product.csv")
pdt_location = pd.merge(pdt,city,left_on='city',right_on='cityid')
detail = pd.read_csv("Detail.csv")
order = pd.read_csv("Order.csv")
detail_pdt = pd.merge(detail,pdt_location,on='productid',how="left")
order_sale_location = pd.merge(order,sale_location,on='clientid',how="left")
order_sale_location_emp = pd.merge(order_sale_location,emp_inf_c,left_on='saleid',right_on='empid',how="left",suffixes=('_c', '_e'))
order_inf = order_sale_location_emp[order_sale_location_emp.empid.notnull()]
order_detail = pd.merge(order_inf,detail_pdt,on='ordered',how="left",suffixes=('_s', '_p'))
order_detail_Hljp = order_detail[order_detail.province_p=="Heilongjiang"]
res = order_detail_Hljp.groupby(['empid','name_e','province_s'],as_index=False).price.sum()
print(res)
|
关联员工信息
筛选符合条件的员工
关联销售地信息
关联产地信息
明细关联产品信息
订单关联销售信息
订单关联销售员信息
筛选符合条件员工的订单信息
订单关联明细信息
筛选黑龙江产品
分组汇总
|
讨论：在日常工作中，经常会遇到这种混合的关联，多种关联关系混在一起，理不清其中的关系，会事倍功半甚至得到错误的结果。
小结
Python 中的关联是将两表根据某个或者某些字段连接在一起，组成一张宽表，这和 SQL 类似。从上述例子中也可以看出，Python 在解决基础关联和同维关联（问题一至问题八）时，还是比较方便的，一步一步计算下来，思路清晰，代码也好理解。
但是，对于外键关联，Python 的处理方法有这样一些问题：
1.Python 的 merge 函数一次只能解析一个关联关系，在关联关系较多时比较麻烦。
2. 每次解析关联后得到的是一个新表，数据会被复制，耗时且耗内存；这个问题同维表关联时也存在。
3. 当发生自关联（循环关联）时，其本质还是两表关联得到新表，不可以重复利用已经建立好的关联关系。
相比之下，esProc SPL 在处理外键关联时要聪明一些。SPL 会建立外键与主键对象的关联，并没有复制数据本身，这样就可以同时建立多个关联关系，计算快捷且节省内存，而且发生自关联（循环关联）时，也可以重复利用建好的关联关系，简洁而高效。
比如问题九至十四，SPL 代码写出来是这样的：
|
问题
|
SPL代码
|
简单说明
|
|
九
|
relate=A.switch(product,B:productid;sale_city,C:cityid)
res=relate.groups(sale_city.province,product.pclass;sum(amount):amount)
|
A是销售记录，B 是产品信息，C 是城市信息，关联后分组汇总
|
|
十
|
relate=A.switch(sale_city,B:cityid;product_city,B:cityid)
res=relate.select(sale_city.province==product_city.province).(recordid)
|
A是销售记录，B 是城市信息，关联后过滤
|
|
十一
|
relate_BC=B.switch(product_city,C:cityid)
relate_ABC =A.switch(product, relate_BC:productid;sale_city,C:cityid)
res=relate_ABC.select(sale_city.province==product_city.province).(recordid)
|
A是销售记录，B 是产品信息，C 是城市信息，关联后过滤
|
|
十二
|
relate=A.join(superior,A:empid,name:superior_name)
res=relate.new(name,superior_name)
|
A是员工信息，join() 函数关联增加字段，最后建新表
|
|
十三
|
relate_BA=B.switch(manager,A:empid)
relate_ABA =A.switch(dept, relate_BA:deptid)
res= relate_ABA.select(dept.manager.province=="Beijing"&&province=="Beijing").(name)
|
A是员工信息，B 是部门信息，关联后过滤
|
|
十四
|
emp_inf=C.join(empid,D:empid,birthday)
emp_inf_c= emp_inf.select((y=year(birthday),y>=1990&&y<2000&&time>=10))
sale_loc=E.join(city,G:cityid,province)
p_loc=F.join(city,G:cityid,province)
detail_p=B.join(productid, p_loc:productid,province:product_province)
detail_g=detail_p.group(ordered)
order_dec=A.switch(ordered, detail_g:ordered;saleid, emp_inf_c:empid;clientid, sale_loc:clientid)
emp_saleloc_p= order_ec.select(saleid).new(saleid.empid:empid,saleid.name:sale_name,clientid.province:sale_location,ordered.select(product_province=="Heilongjiang").sum(price):price)
res=emp_saleloc_p.groups(empid,sale_name,sale_location;sum(price):price).select(price)
|
A是订单表，B 是订单明细，C 是出差信息，D 是员工信息，E 是客户信息，F 是商品信息，G 是城市信息，综合利用外键对象化，外键增加字段，主子表一体化，同维表等同化等方式， 结合过滤分组汇总等计算得到结果。
|
当然，SPL 处理同维关联也一样简单，比如问题六至八：
|
问题
|
SPL代码
|
简单说明
|
|
六
|
relate_ABC=join@f(A:fexam,studentid;B:uper,studentid;C:elec,studentid)
res= relate_ABC.new(if(fexam.studentid,fexam.studentid,uper.studentid,uper.studentid,elec.studentid):studentid,fexam.fscore:fscore,uper.uscore:uscore,elec.escore:escore,0.6*fexam.fscore+0.3*uper.uscore+0.1*elec.escore:score)
|
A是期末考试成绩，B 是平时成绩，C 是选修课成绩，
join()函数同维表等同化
关联后建新表
|
|
七
|
C_g=C.group(storeid,ordered)
relate_BC=join(B:order,[storeid,ordered]; C_g:detail,[storeid,ordered])
res1= relate_BC.groups(order.storeid,order.location;sum(detail.sum(price)):price)
res1_g=res1.group(storeid)
relate_Ares1=join(A:store,storeid; res1_g:order,storeid)
res2= relate_Ares1.groups(store.province;sum(order.sum(price)):price)
|
C是订单明细，C 分组
B是订单，join() 函数完成主子表一体化
分组汇总得到 res1
res1分组
A是门店信息，主子表一体化
分组汇总得到 res2
|
|
八
|
groupB=B.group(ordered)
groupC=C.group(ordered)
joinABC=join(A:Order,ordered;groupB:Detail,ordered;
groupC:Payment,ordered)
res= joinABC.select(Detail.sum(price)>
Payment.sum(payment)).(Order)
|
B是订单明细表，分组
C是回款表，分组
A是订单表，主子表一体化
过滤满足条件的表
|
Python 还有一个比较严重的缺点，那就是当数据量大到无法一次性载入内存时，使用 Python 进行关联运算将是灾难性的，几乎所有的关联方式都需要自己手动来实现，涉及到外存排序，hash 分段等等复杂代码，非高级程序员几乎不可能完成。这些代码实在过于繁琐，这里也就没有再给出来。对于大文件的场景，Python 不再合适，还是得用 SPL，它提供有游标对象，可以轻松解决内存装不下的文件之间的关联及其它运算。
比如问题一计算 2019 年老客户的合同总金额。当合同数据很大，无法一次性载入内存时，而客户数据可以放入内存时，可以为合同表创建游标来计算，SPL 代码只有区区几行：
|
A
|
B
|
|
1
|
=file(contract_path).cursor@tc()
|
/为事实表——合同表创建游标
|
|
2
|
=file(client_path).import@tc()
|
/导入维表——客户表
|
|
3
|
=A2.switch@i(Client,A3:ID)
|
/游标关联
|
|
4
|
=A4.total(sum(Amount))
|
/汇总结果
|
两个表都特别大时，SPL 还提供了 joinx() 函数，对有序游标进行关联，可以非常高效的完成大数据的关联任务。
