【学习记录】阿里天池SQL练习题10 python和SQL方式

项目：解决数据合并，字符串分列，汇总统计等

数据来源：https://tianchi.aliyun.com/competition/entrance/231593/information
需求：使用Coupon Usage Data for O2O中的数据集《ccf_online_stage1_train.csv》和《ccf_offline_stage1_train.csv》，试找出在2016年6月期间，线上线下累计被使用优惠券满减最多的前3名商家。
比如商家A，消费者A在其中使用了一张200减50的，消费者B使用了一张30减1的，那么商家A累计被使用优惠券满减51元。

先导入数据

import pandas as pd
path_1=r'../data/阿里天池/ccf_online_stage1_train.csv'
path_2=r'../data/阿里天池/ccf_offline_stage1_train.csv'
df_online=pd.read_csv(path_1)
df_offline=pd.read_csv(path_2)

接着分别梳理online和offline数据，字符串切分用str.split()方法

在处理中str.split()中添加参数expand=True。把数据集单独拆成新的2列数据后

再合并到原来数据集里

#处理online数据
df1=df_online[(df_online['Date'].notnull()) & (df_online['Merchant_id'].notnull()) & (df_online['Discount_rate'].notnull())]
df1=df1.set_index('Date').sort_index(ascending=False).loc[20160630.0:20160601.0]
s1=df1['Discount_rate'].str.split(':',expand=True)[1].fillna(0).astype('int')
online=pd.concat([df1,s1],axis=1).groupby('Merchant_id')[[1]].sum()

#处理offline数据
df2=df_offline[(df_offline['Date'].notnull()) & (df_offline['Merchant_id'].notnull()) & (df_offline['Discount_rate'].notnull())]
df2=df2.set_index('Date').sort_index(ascending=False).loc[20160630.0:20160601.0]
s2=df2['Discount_rate'].str.split(':',expand=True)[[1]].fillna(0).astype('int')
offline=pd.concat([df2,s2],axis=1).groupby('Merchant_id')[[1]].sum()

最后合并数据，在做统计汇总排序等处理，得到最终结果

Top3=pd.concat([online,offline],axis=0).groupby('Merchant_id').sum().sort_values(by=1,ascending=False)
Top3.rename(columns={1:'满减金额'})[:3]
print(f'线上和线下累计Top3商家是{Top3.index[:3]},满减金额是{Top3.values[:3]}')

以下SQL代码

SELECT Merchant_id,SUM(优惠券金额) AS cost FROM 
(SELECT Merchant_id,
		(SUBSTRING_INDEX(Discount_rate,':',-1)) AS '优惠券金额'
FROM ccf_offline_stage1_train
WHERE Discount_rate <>'null' AND year(Date)=2016 AND MONTH(Date)=06
UNION ALL
SELECT Merchant_id,
		(SUBSTRING_INDEX(Discount_rate,':',-1)) AS '优惠券金额'
FROM ccf_online_stage1_train
WHERE Discount_rate <>'null' AND year(Date)=2016 AND MONTH(Date)=06)t
GROUP BY Merchant_id
ORDER BY cost desc
LIMIT 3

总结：通过用python的方式把阿里天池的SQL练习题全部做了一遍。总体感觉SQL在处理数据上海是比较python便捷的。只是python可以一条条写，所以相比SQL逻辑可能更加自由一些。SQL基于代码的书写规则和执行规则，没有python这么灵活。当然python后续处理如可视化，把数据批量导出等需求时，优势就更加凸显。