【学习记录】阿里天池SQL练习题1-python与SQL方式比较

如何做海量数据合并 需求：请使用A股上市公司季度营收预测数据集《Income Statement.xls》和《Company Operating.xlsx》和《Market Data.xlsx》，以Market Data为主表，将三张表中的TICKER_SYMBOL为600383和600048的信息合并在一起。只需要显示以下字段。

表名	字段名
Income Statement	TICKER_SYMBOL
Income Statement	END_DATE
Income Statement	T_REVENUE
Income Statement	T_COGS
Income Statement	N_INCOME
Market Data	TICKER_SYMBOL
Market Data	END_DATE_
Market Data	CLOSE_PRICE
Company Operating	TICKER_SYMBOL
Company Operating	INDIC_NAME_EN
Company Operating	END_DATE
Company Operating	VALUE

思考：海量数据集合并，首先想到运算效率。由于是海量数据合并，如条件不做筛选，数据处理量较大的情况下，读入数据的时长会非常长，导致运算时长较久。

#导入pandas库，数据读入DataFrame容器

import pandas as pd
Income_Statement=pd.read_excel('./data/阿里天池/Income Statement.xls',usecols=['TICKER_SYMBOL','END_DATE','T_REVENUE','T_COGS','N_INCOME'])
Market_Data=pd.read_excel('./data/阿里天池/Market Data.xlsx',usecols=['TICKER_SYMBOL','END_DATE_','CLOSE_PRICE'])
Company_Operating=pd.read_excel('./data/阿里天池/Company Operating.xlsx',sheet_name='EN',usecols=['TICKER_SYMBOL','INDIC_NAME_EN','END_DATE','VALUE'])

相比用Heidi直接导入SQL文件，python的数据写入用时很短。这边先在读入数据时做了字段（列名）筛选，便于减少读入的数据量

---

接下来关于需求的一个核心理解，先看一段SQL代码如下

select i.TICKER_SYMBOL i_TICKER_SYMBOL
		  ,i.END_DATE i_END_DATE
			,i.T_REVENUE
			,i.T_COGS
			,i.N_INCOME
			,m.TICKER_SYMBOL m_TICKER_SYMBOL
			,m.END_DATE m_END_DATE
			,m.CLOSE_PRICE
			,c.TICKER_SYMBOL c_TICKER_SYMBOL
			,c.INDIC_NAME_EN
			,c.END_DATE c_END_DATE
			,c.VALUE
from `market data` m left join `income statement` i on m.TICKER_SYMBOL=i.TICKER_SYMBOL
left join `company operating`c on m.TICKER_SYMBOL=c.TICKER_SYMBOL
where m.TICKER_SYMBOL in (600383,600048);

这边我参考了网上很多写法（大部分都很类似）如上方式，用这类方式合并后的数据量会非常大这么写的结果是：7,871,472行数据量

而网上还有一种很复杂查询方式，如下多个子查询嵌套。最终结果：606行记录

SELECT MD.* ,OD.INDIC_NAME_EN,OD.VALUE,IT.N_INCOME,IT.T_COGS,IT.T_REVENUE
FROM ( SELECT TICKER_SYMBOL,END_DATE,CLOSE_PRICE FROM `market data`
WHERE TICKER_SYMBOL IN ('600383','600048'))AS MD LEFT JOIN 
( SELECT TICKER_SYMBOL,INDIC_NAME_EN,END_DATE,VALUE FROM `company operating`
WHERE TICKER_SYMBOL IN ('600383','600048'))AS OD 
ON MD.TICKER_SYMBOL=OD.TICKER_SYMBOL AND MD.END_DATE=OD.END_DATE
LEFT JOIN ( SELECT DISTINCT TICKER_SYMBOL,END_DATE,T_REVENUE,T_COGS,N_INCOME
FROM `income statement`
WHERE TICKER_SYMBOL IN ('600383','600048'))AS IT
ON MD.TICKER_SYMBOL=IT.TICKER_SYMBOL AND MD.END_DATE=IT.END_DATE;

仔细对比了2个方式。发现第一种方式是先用主键TICKER_SYMBOL进行inner合并，再做where筛选指定股票代码。

而第二种方式是先把3个列表里指定的股票代码筛选出来后，再合并筛选后的3张表。

重新再看了遍题目的要求：以Market Data为主表，将三张表中的TICKER_SYMBOL为600383和600048的信息合并在一起。也就是只合并3个表里TICKER_SYMBOL为600383和600048的信息。

所以如果先用TICKER_SYMBOL作为主键，进行inner合并，再筛选。其实就会出现了很多重复数据，导致数据冗余。也就是导致数据量如此庞大的原因

---

Md=Market_Data[(Market_Data['TICKER_SYMBOL']==600383) | (Market_Data['TICKER_SYMBOL']==600048)]
Is=Income_Statement[(Income_Statement['TICKER_SYMBOL']==600383) | (Income_Statement['TICKER_SYMBOL']==600048)]
Co=Company_Operating[(Company_Operating['TICKER_SYMBOL']==600383) | (Company_Operating['TICKER_SYMBOL']==600048)]

理解这点后，就先把数据做筛选处理，把3个容器中的TICKER_SYMBOL为600383和600048的数据先筛出来（可以在导入数据时先建立索引。会比现在代码更简洁）

finally_data=pd.concat([Md,Is,Co],keys={'Md','Is','Co'})

最后再用pd.concat合并，并用keys参数区分开3张原表的数据。这里为什么用concat方法，其实刚开始用了merge方法。merge方法的问题是一次只能合并2个表。但这个不是关键，merge方法中文理解应该是【连接】，而concat方法理解是【拼接】。merge方法合并数据即便用outer(外连接），也会自动把重复的数据合并，而拼接是不会合并重复数据。concat方法只会合并重列名和行索引。

最终出来的结果：678条记录 

如下完整代码

import pandas as pd

#读入数据
Income_Statement=pd.read_excel('./data/阿里天池/Income Statement.xls',usecols=['TICKER_SYMBOL','END_DATE','T_REVENUE','T_COGS','N_INCOME'])
Market_Data=pd.read_excel('./data/阿里天池/Market Data.xlsx',usecols=['TICKER_SYMBOL','END_DATE_','CLOSE_PRICE'])
Company_Operating=pd.read_excel('./data/阿里天池/Company Operating.xlsx',sheet_name='EN',usecols=['TICKER_SYMBOL','INDIC_NAME_EN','END_DATE','VALUE'])
Md=Market_Data[(Market_Data['TICKER_SYMBOL']==600383) | (Market_Data['TICKER_SYMBOL']==600048)]

#筛选处理数据
Is=Income_Statement[(Income_Statement['TICKER_SYMBOL']==600383) | (Income_Statement['TICKER_SYMBOL']==600048)]
Co=Company_Operating[(Company_Operating['TICKER_SYMBOL']==600383) | (Company_Operating['TICKER_SYMBOL']==600048)]

#合并数据
finally_data=pd.concat([Md,Is,Co],keys={'Md','Is','Co'})

最后对比用SQL和Python分别处理此类情景

SQL文档读入海量数据时间会比CSV读入DataFrame的时间来的久，但一旦读入数据库后，查询处理数据会比较方便。不用像python每次要花时间重新把数据读入容器。

python处理数据比较快，包括后面做数据清洗：如查缺补漏，处理NaN，字符串处理等。最主要python的解题思路是自由决定先后顺序。而不用像SQL代码的执行顺序是固定的，如where必须是select之后，from是最先执行等。