Python也能写SQL，组合查询~

上一节我们讲到pandasql库的用法，以及简单举例说明SQL基础查询，这一节我们讲组合查询，其实在数据分析时，使用最多的功能是数据分组或者是数据透视功能，Excel中插入数据透视表，Python中使用pivot_table函数，而SQL中使用group by函数，下面一起学习group by的分组功能。

本文使用工具：Python3.7.0

本文使用函数：pandas、pandasql

适用范围：pandasql库的使用、sql组合查询

数据获取

与上一节相同，首先导入我们的数据文件，这里使用的是pandas库。

• 
  

import pandas asdf=pd.read_excel(r'C:\Users\尚天强\Desktop\数据合并.xlsx')#重命名df.rename(columns={'累计票房(万)':'累计票房'}, inplace = True)df.head()

导入pandasql库，后续的SQL运行都需要借助该库。

• 
  

import pandasql as

对不同的电影类型计数，使用group by进行分组，同时用count计数做聚合运算，得出结果如下。

• 
  

#1.不同电影类型计数sql.sqldf("""select 电影类型,count() as 电影数 from df             group by 电影类型;""")

要得出不同电影类型的平均票房，并降序排列，同样使用group by函数按照电影类型分组，同时聚合函数使用avg，order by函数用于排序，加上参数desc,可降序排列。

• 
  

#2.不同电影类型的平均票房，并降序排列sql.sqldf("""select 电影类型,avg(累计票房) as '平均票房/万' from df             group by 电影类型             order by 累计票房 desc;""")

如果对数据分组后的数据进行筛选，就不能使用where子句，需要使用having实现条件过滤，having和where的区别如下：

• 1.where和having均实现条件过滤；
• 2.where使用在group by之前，having是在group by之后，为了分组之后再次对结果过滤；
• 3.where不能使用聚合函数，having能够使用聚合函数。

这里求得不同的电影类型的平均票房，并且筛选平均票房大于20000，就需要使用having添加过滤条件，而不是使用where子句。

• 
  
• 
  

#3.不同电影类型的平均票房，并筛选平均票房数大于20000万sql.sqldf("""select 电影类型,avg(累计票房) as '平均票房/万' fromgroup byhaving avg(累计票房)>20000order by 累计票房 desc;""")

求累计票房的最大值使用max函数，最小值使用max函数，并且还可以作差来求差值。

• 
  

#4查询电影的累计票房最大和最小值以及差值sql.sqldf("""select max(累计票房) as 最高票房,             min(累计票房) as 最低票房,             max(累计票房)-min(累计票房) as 票房差值 from df;""")

这里使用pandas命令对不同列的缺失值计数，对于SQL不能实现的，pandas可以使用一行代码输出结果，展现python的简洁。

• 
  

df.isnull().sum()

使用SQL语句查找累计票房这一列的缺失值，得出结果有8个缺失值。

• 
  

sql.sqldf("""select count(*) from df where 累计票房 is null;""")

查询累计票房缺失的所有电影信息，这里添加where语句，判断条件是否is null。

#5查询累计票房缺失的所有电影信息sql.sqldf("""select * from df             where 累计票房 is null;""")

对电影的累计票房分为'超低票房'、'低票房'、'中等票房'、'高票房'、'超高票房'，这里使用CASE WHEN进行分组，以end结尾，查询结果如下。

• 
  

#6对电影的累计票房使用CASE WHEN分组sql.sqldf("""selectcasewhen 累计票房 < 100000 then '超低票房'when 累计票房 < 200000 then '低票房'when 累计票房 < 300000 then '中等票房'when 累计票房 < 400000 then '高票房'else '超高票房'end as '电影票房分组' fromwhere 累计票房 is not null;""")

要查询不同电影发行公司电影的票房情况，使用group by函数分组，sum函数做聚合运算，并使用order by函数降序排列。

• 
  

#7不同电影发行公司电影的票房情况，并降序排列sql.sqldf("""select 发行公司,sum(累计票房) as '累计票房/万' fromgroup byorder by sum(累计票房) desc;""")

查询每一个电影主演累计票房最高的那一部电影信息，需要做一个子查询，先子查询每一个电影主演累计票房的最大值，然后作为外部where子句的筛选条件。

• 
  

#8查找每一个电影主演累计票房最高的那一部电影信息sql.sqldf("""select * from df aswhere d.累计票房=(select max(累计票房) fromwhereorder by 累计票房 desc;""")

如果要将查询的结果都输出，可以将每一个查询结果赋值为一个变量，然后统一输出，这里将查询结果赋值为sqltable1。

• 
  

#对电影的累计票房使用CASE WHEN分组，然后赋值为sqltable1sqltable1=sql.sqldf("""selectcasewhen 累计票房 < 100000 then '超低票房'when 累计票房 < 200000 then '低票房'when 累计票房 < 300000 then '中等票房'when 累计票房 < 400000 then '高票房'else '超高票房'end as '电影票房分组' fromwhere 累计票房 is not null;""")

将第二个查询结果赋值为sqltable2。

• 
  

#不同电影发行公司电影的票房情况，并降序排列，然后赋值为sqltable2sqltable2=sql.sqldf("""select 发行公司,sum(累计票房) as '累计票房/万' from dfgroup by 发行公司order by sum(累计票房) desc;""")

将第三个查询结果赋值为sqltable3。

#查找每一个电影主演累计票房最高的那一部电影信息，然后赋值为sqltable3sqltable3=sql.sqldf("""select * from df as dwhere d.累计票房=(select max(累计票房) from dfwhere d.电影主演=电影主演)order by 累计票房 desc;""")

将以上变量全部输出到Excel的不同sheet表格中，使用pd.ExcelWriter命令，统一输出。

• 
  

#数据导出write=pd.ExcelWriter(r'C:\Users\尚天强\Desktop'+'\\SQL组合查询结果'+'.xlsx')

sqltable1.to_excel(write,sheet_name='SQL结果一',index=False)sqltable2.to_excel(write,sheet_name='SQL结果二',index=False)sqltable3.to_excel(write,sheet_name='SQL结果三',index=False)

write.save()write.close()

​