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

老样子先从SQL导入数据，并建立索引。

这里我先查过了数据源的字典，了解了需要分析的用电量字段-第一产业的ID。所以建了索引后很方便筛选出需要的数据，读入新的DataFrame变量“第一产业_当月值”

import sqlalchemy as sac
import pandas as pd

engine=sac.create_engine('mysql+pymysql://user:password@host:3306/ali_sql')
df=pd.read_sql('select * from `macro_industry`',engine)
df=df.set_index('indic_id')
第一产业_当月值=df.loc['2020101522']

数据导入后

1、处理下日期数据，新建一列【年份】并改变格式，仅显示年份

2、然后筛选出2015年用电量最高峰发生在哪个月份

3、再单独筛选出2014年同期的数据

4、rate一下，计算出最终比值，了解用电量是增加还是减少，最终转换成百分比数值

第一产业_当月值['年份']=pd.to_datetime(第一产业_当月值['PERIOD_DATE']).dt.strftime('%Y')

用电最高峰=第一产业_当月值[第一产业_当月值['年份']=='2015'].groupby('PERIOD_DATE').max().sort_values(by='DATA_VALUE',ascending=False)[:1]

去年同期=第一产业_当月值[第一产业_当月值['年份']=='2014'].groupby('PERIOD_DATE').max().sort_values(by='DATA_VALUE',ascending=False)[:1]

rate=(用电最高峰['DATA_VALUE'][0]-去年同期['DATA_VALUE'][0])/用电最高峰['DATA_VALUE'][0]

print('用电量增长了: {:.2%}'.format(rate))

这边有个小问题，就是如何把2组数据进行百分比计算？

在第4步rate时，之前的2个变量【用电最高峰】和【去年同期】都是DataFrame数据类型，做了【DATA_VALUE】切片后变成了Series类型，无法直接计算。

那么要计算就先要把类型变成数值型，如int，float等。这个有个方法df.values可以把数据类型变成np的ndarray的数组类型，再利用下标切片获取单个数据后解决此问题。如下示例，切片后用values获取np.array类型数据，再获取数组中单个元素

 SQL代码

-- 2015年第一产业最大用电量的月份？
SELECt month(mi.PERIOD_DATE) AS '月份1',max(mi.DATA_VALUE) AS '2015最大用电量'
FROM `macro_industry` AS mi
WHERe mi.indic_id=2020101522
AND YEAR(mi.PERIOD_DATE)=2015
GROUP BY mi.PERIOD_DATE
ORDER BY 2015最大用电量 DESC
LIMIT 1

 第二个问题的代码是相对复杂一些，这个是查阅了网上的所谓标准答案，当然也有其他不同写法。但这个写法个人认为是逻辑最清楚，最容易理解的。

-- 相比去年同期增长/减少了多少个百分比？
SELECt t1.*,t2.*,((t1.2015最大用电量-t2.2014最大用电量)/t1.2015最大用电量) AS rate FROM
(SELECt PERIOD_DATE,max(DATA_VALUE) AS '2015最大用电量'
FROM `macro_industry`
WHERe indic_id=2020101522
AND YEAR(PERIOD_DATE)=2015
GROUP BY PERIOD_DATE
ORDER BY 2015最大用电量 DESC
LIMIT 1) AS t1
LEFT JOIN 
(SELECt PERIOD_DATE,max(DATA_VALUE) AS '2014最大用电量'
FROM `macro_industry`
WHERe indic_id=2020101522
AND YEAR(PERIOD_DATE)=2014
GROUP BY PERIOD_DATE
ORDER BY 2014最大用电量 DESC
LIMIT 1) AS t2
ON Year(t1.PERIOD_DATE)=year(t2.PERIOD_DATE)+1
AND MONTH(t1.PERIOD_DATE)=MONTH(t2.PERIOD_DATE)

总结来看：在处理多个数据筛选并合并后的时候，用python的方法会更加灵活。SQL在处理这类问题时候会比较繁琐写。当然高手可以用更加少的代码实现，但代码逻辑理解起来会更加困难。