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

项目：练习海量数据合并及统计汇总

数据来源：https://tianchi.aliyun.com/dataset/dataDetail?dataId=1074

需求：请使用A股上市公司季度营收预测数据集《Income Statement.xls》中的sheet-General Business和《Company Operating.xlsx》中的sheet-EN。
找出在数据集所有年份中，按季度统计，白云机场旅客吞吐量最高的那一季度对应的净利润是多少？（注意，是单季度对应的净利润，非累计净利润。）

导入数据源

import pandas as pd
path_1=r'../data/阿里天池/Income Statement.xls'
path_2=r'../data/阿里天池/Company Operating.xlsx'
df_income=pd.read_excel(path_1,sheet_name=0)
df_company=pd.read_excel(path_2,sheet_name=1)
df_company.info() #了解数据类型及列名

此处根据题目要求，要先时间数据转换成按季度数据。用df.to_preiod()方法

df_company=df_company.set_index('END_DATE').to_period('Q') #把原来按天的日期转换成季度

白云机场季度旅客吞吐量=df_company[df_company['INDIC_NAME_EN']=='Baiyun Airport:Passenger throughput'].groupby(['END_DATE','TICKER_SYMBOL'])[['VALUE']].sum()

然后计算出company表中机场吞吐量

接着在另一张表income中筛选统计分季度的利润数据

df_income.info() #了解数据类型及列名
df_income['END_DATE']=pd.to_datetime(df_income['END_DATE'])
分季度利润=df_income.set_index('END_DATE').to_period('Q').groupby(['END_DATE','TICKER_SYMBOL'])[['N_INCOME']].sum()

最终合并两组数据。这里用merge方法，不用concat。主要目的是把相同字段的数据合并掉

res=pd.merge(白云机场季度旅客吞吐量,分季度利润,on=['END_DATE','TICKER_SYMBOL']).sort_values(by='VALUE',ascending=False)[:1]
print(f'白云机场吞吐量最大的季度是{res.index[0]},吞吐量是:{res.values[0,0]},利润是{res.values[0,1]}') #汇总结果

此题比较容易，相对来说就是要了解时间处理数据中如何把时间序列转换成按季度的时间索引。

以下SQL代码

SELECT t1.利润 FROM
(SELECT TICKER_SYMBOL,
		YEAR(End_date)AS year,
		QUARTER(end_date) AS Q,
		SUM(N_INCOME) AS '利润'
FROM `income statement`
GROUP BY TICKER_SYMBOL,YEAR,Q) AS t1
JOIN
(SELECT TICKER_SYMBOL,
		YEAR(End_date)AS year,
		QUARTER(end_date) AS Q,
		SUM(VALUE) AS '吞吐量'
FROM `Company Operating`
WHERE INDIC_NAME_EN='Baiyun Airport:Passenger throughput'
GROUP BY TICKER_SYMBOL,YEAR,Q
ORDER BY 吞吐量 DESC
LIMIT 1) AS t2
ON t1.TICKER_SYMBOL=t2.TICKER_SYMBOL AND t1.year=t2.year AND t1.Q=t2.Q

总结：初级的SQL问题，主要是围绕多表数据合并，拼接和处理汇总统计等一些常见问题。相比较python，SQL处理数据的效率和方便程度还是很快的