8.1 外键关联

现有门店信息表、订单表、订单明细表、订单回款表、门店线上评分表、门店线下评分表、门店考核成绩表。完成以下计算：

门店信息表：

订单表

订单明细表

订单回款表

门店线上评分表

门店线下评分表

门店考核成绩表

1. 同维关联——计算门店客户评分。（线上评分 *0.4+ 线下评分 *0.6）
2. 多同维表——计算门店总评分。（门店考核成绩 *0.6+ 线上评分 *0.1+ 线下评分 *0.3）
3. 主子表——查询 s150 门店各州客户的总消费额
4. 多层主子表——汇总各州门店的总销售额
5. 一主表多子表——找出未完全回款的订单

SPL

SQL

1. 同维关联

SELECT COALESCE(o.storeid, f.storeid) AS storeid,
     (COALESCE(o.online_score, 0)*0.4 + COALESCE(f.offline_score, 0)*0.6) AS customer_score
FROM online_score o
FULL JOIN offline_score f ON o.storeid = f.storeid;

2. 多同维表

SELECT COALESCE(t.storeid, o.storeid, f.storeid) AS storeid,
     (COALESCE(t.test_score, 0)*0.6 + COALESCE(o.online_score, 0)*0.1 + 
        COALESCE(f.offline_score, 0)*0.3) AS total_score
FROM test_score t
FULL JOIN online_score o ON t.storeid = o.storeid
FULL JOIN offline_score f ON t.storeid = f.storeid;

3. 主子表

SELECT o.location, SUM(d.price) AS total_consumption
FROM Orders o
JOIN Detail d ON o.orderid = d.orderid AND o.storeid = d.storeid
WHERE o.storeid = 's150'
GROUP BY o.location;

4. 多层主子表

SELECT s.state, SUM(d.price) AS total_sales
FROM Store s
JOIN Orders o ON s.storeid = o.storeid
JOIN Detail d ON o.orderid = d.orderid AND o.storeid = d.storeid
GROUP BY s.state;

5. 一主表多子表

SELECT o.storeid,o.orderid, p. total_payment,d. total_price
FROM Orders o
JOIN (
    SELECT p.storeid, p.orderid, SUM(p.payment) AS total_payment
    FROM Payment p
    GROUP BY p.storeid, p.orderid) p
ON o.storeid = p.storeid AND o.orderid = p.orderid
JOIN (
    SELECT d.storeid, d.orderid, SUM(d.price) AS total_price
    FROM Detail d
    GROUP BY d.storeid, d.orderid) d
ON o.storeid = d.storeid AND o.orderid = d.orderid
WHERE d.total_price-p.total_payment>0.000001
ORDER BY o.storeid,o.orderid;

Python

#同维关联
onscore=pd.read_csv("../online_score.csv",index_col='storeid')
ofscore=pd.read_csv("../offline_score.csv",index_col='storeid')
scores=pd.merge(onscore,ofscore,on="storeid",how="outer").fillna(0)
scores['total']=0.6*scores['offline_score']+0.4*scores['online_score']
#多同维表
tscore=pd.read_csv('../test_score.csv',index_col='storeid')
store_score=pd.concat([onscore,ofscore,tscore],axis=1,join='outer',sort=True).fillna(0).reset_index()
store_score['total']=0.6*store_score.test_score+0.3*store_score.offline_score+0.1*store_score.online_score
#主子表
detail=pd.read_csv("../Detail.csv")
order=pd.read_csv("../Orders.csv")
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()
#多层主子表
store=pd.read_csv("../Store.csv")
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"])
state_sale=store_inf.groupby('state',as_index=False).price.sum()
#一主表多子表
pay=pd.read_csv("../Payment.csv")
detail_order=detail.groupby(['storeid','orderid']).price.sum()
pay_order=pay.groupby(['storeid','orderid']).payment.sum()
order=order.set_index(['storeid','orderid'])
order_d_p=order.join([pay_order,detail_order])
nopay_order=order_d_p.query('price-payment>1e-6')

8.3 混合关联
SPL SQL Python 代码示例对比