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

统计汇总及排序

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

请使用A股上市公司季度营收预测中的数据集《Market Data.xlsx》中的sheet-DATA，

计算截止到2018年底

1、市值最大的三个行业是哪些？

2、这三个行业里市值最大的三个公司是哪些？（每个行业找出前三大的公司，即一共要找出9个）

概要：06题非常容易，等同于02题。此题07题用python比较容易，但SQL的方式我不太理解。所以自己只用了python方法。后面会贴出其他大佬的SQL代码，同时我也发现跑出来的结果都有差异。

老样子，先导入数据，这次数据量比较少，就不做筛选。这次导入时做了索引设置，这次从本地导入，没用数据库

import pandas as pd
df=pd.read_excel('../data/阿里天池/Market Data.xlsx',index_col='END_DATE')

然后这边用了一条链式代码写法，直接得到第一个问题市值最大的三个行业的答案

df.index.max() 取巧的做法，先前导入数据时候，特意做了时间索引

题目要求截止2018年底，那么就是时间轴里最后那天，不单独处理时间数据

市值最大的三个行业=df.loc[df.index.max()].groupby('TYPE_NAME_CN')[['MARKET_VALUE']].sum().sort_values(by='MARKET_VALUE',ascending=False).head(3)

第二个问题，这里先做了数据复制，避免修改原始数据，造成再次重新导入数据的麻烦

这边用也是链式写法，先晒出时间，再重新建索引，把行业名称作为所以，然后用之前获取的市值最大的三个行业作为筛选条件，一次性把符合时间要求的目标行业数据筛选出来

筛完数据后，用groupby做分类，这里把行业和企业编码做索引，获取市值最大的list

df1.loc[df.index.max()].set_index('TYPE_NAME_CN').loc[市值最大的三个行业.index]

groupby(['TYPE_NAME_CN','TICKER_SYMBOL'])[['MARKET_VALUE']].max()

df1=df.copy()
df2=df1.loc[df.index.max()].set_index('TYPE_NAME_CN').loc[市值最大的三个行业.index].groupby(['TYPE_NAME_CN','TICKER_SYMBOL'])[['MARKET_VALUE']].max()

最后把获取的结果df2，做3次筛选，分别选出三个行业中，市值排名前三的公司ID

至此完成问题2.

银行业前三公司=df2.loc['银行'].sort_values(by='MARKET_VALUE',ascending=False).head(3)
医药生物业前三公司=df2.loc['医药生物'].sort_values(by='MARKET_VALUE',ascending=False).head(3)
非银金融业前三公司=df2.loc['非银金融'].sort_values(by='MARKET_VALUE',ascending=False).head(3)

下面看下SQL代码

代码1：

SELECT BaseData.TYPE_NAME_CN,
       BaseData.TICKER_SYMBOL,
       BaseData.MARKET_VALUE
FROM(SELECT TYPE_NAME_CN,
            TICKER_SYMBOL,
            MARKET_VALUE,
	    ROW_NUMBER() OVER(PARTITION BY TYPE_NAME_CN 
	    ORDER BY MARKET_VALUE) AS CompanyRanking
      FROM `market data`) AS BaseData
LEFT JOIN(SELECT TYPE_NAME_CN,
          SUM(MARKET_VALUE)
      FROM `market data`
      WHERE YEAR(END_DATE) = '2018-12-31'
      GROUP BY TYPE_NAME_CN
      ORDER BY SUM(MARKET_VALUE) DESC
      LIMIT 3) AS Top3Type
ON BaseData.TYPE_NAME_CN = Top3Type.TYPE_NAME_CN
WHERE CompanyRanking <= 3 AND Top3Type.TYPE_NAME_CN IS NOT NULL

 代码2：

select TYPE_NAME_CN,TICKER_SYMBOL
from 	-- 两表连接，按行业分组，根据公司市值排序
		(select M.TYPE_NAME_CN,M.TICKER_SYMBOL,row_number() over(partition by M.TYPE_NAME_CN order by M.	MARKET_VALUE desc) rn
		from `market data` M
		inner join 
				-- 查询截止2018-05-31市值最大的三个行业
				(select TYPE_NAME_CN ,sum(MARKET_VALUE)
				from `market data`
				where END_DATE='2018-05-31'
				group by TYPE_NAME_CN
				order by sum(MARKET_VALUE) desc
				limit 3) TypeTop3
		on M.TYPE_NAME_CN=TypeTop3.TYPE_NAME_CN
		where M.TYPE_NAME_CN is not null
		and END_DATE='2018-05-31') t
where rn<=3

 我出来的结果