Python与数据库交互——窗口函数

本文作者：张孟晗，中南财经政法大学统计与数学学院

本文编辑：赵一帆

技术总编：陈   鼎

Stata&Python云端课程来啦！

       为了感谢大家长久以来的支持和信任，爬虫俱乐部为大家送福利啦！！！Stata&Python特惠课程双双上线腾讯课堂～爬虫俱乐部推出了Python编程培训课程、Stata基础课程、Stata进阶课程、Stata文本分析、正则表达式、网络爬虫、基本字符串课程。报名课程即可加入答疑群，对报名有任何疑问欢迎在公众号后台和腾讯课堂留言哦。我们在这篇推文的最后提供了每门课程的课程二维码，大家有需要的话可以直接扫描二维码查看课程详情并进行购买哦~

引言

在之前的推文Python与数据库交互—浅述pymysql和【案例展示】Python与数据库交互中，我们向大家介绍了如何利用Python终端操作MySQL数据库的模块，并处理数据库中的数据。那么这次小编将向大家介绍如何在python使用强大的窗口函数来对数据库中的数据完成一些有趣的小任务。

一、数据导入

首先，我们将excel中的数据导入mysql数据库。大致可以分为两步，先将excel中的数据转换成DataFrame格式，然后再将DataFrame格式的数据导入到数据库中。

import pandas as pd
from sqlalchemy import create_engine
# 读入数据
df = pd.read_excel('F:\python\shuju.xlsx')
# 建立驱动引擎
zzz = create_engine('mysql+pymysql://root:121215zmh@localhost:3306/zzz?charset=utf8')
# 将数据导入数据库
df.to_sql('datas',con = zzz, index= False,if_exists= 'replace')

通过登录Navicat查看，我们可以看到数据已成功导入到了数据库中。

二、窗口函数介绍

如果我们想对导入的数据根据销量对各品牌进行排序编号，想对销量进行一阶滞后，想取出各品牌最高销量的那条记录，想计算一阶移动平均数，想……别想了，强大的窗口函数，一切问题轻松解决！

MySQL从8.0版本开始支持窗口函数。窗口函数的作用类似于在查询中对数据进行分组，不同的是，分组操作会把分组的结果聚合成一条记录，而窗口函数是将结果置于每一条数据记录中。

窗口函数可以分为静态窗口函数和动态窗口函数 。静态窗口函数的窗口大小是固定的，不会因为记录的不同而不同；动态窗口函数的窗口大小会随着记录的不同而变化。（Tips:计算移动平均值特别有用）

窗口函数的语法为：函数 OVER（[PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC ROWS BETWEEN … AND …]）

●  PARTITION BY子句：指定窗口函数按照哪些字段进行分组。分组后，窗口函数可以在每个分组中分别执行。

●  ORDER BY子句：指定窗口函数按照哪些字段进行排序。执行排序操作使窗口函数按照排序后的数据记录的顺序进行编号。

● ROWS BETWEEN … AND …子句：通常用来作为滑动窗口使用，进行逐期递移。

窗口函数总体上可以分为序号函数、分布函数、前后函数、首尾函数和其他函数，具体函数及其说明如下表：

三、函数展示

开始展示之前，我们先建立好与数据库的连接。

import pymysql
conn=pymysql.connect(host='localhost',user='root',password='121215zmh',database='zzz',charset='utf8')

下面我们挑几个重要的窗口函数向大家演示其用法，帮助大家更好的理解窗口函数是如何对数据进行处理的。

1.序号函数

（1）ROW_NUMBER()

result = pd.read_sql("select *,row_number() over(partition by name order by number desc) as rownumber from datas;",con=conn)
result

结果可见最后增加了名为rownumber的一列，这一列是按照name类别进行排序，并且序号无间断且不重复，我们可以看出apple第2季度销量最高为1200，huawei第4季度销量最高为1100。

（2）RANK()与DENSE_RANK()

a = pd.read_sql("select *,rank() over(partition by name order by number desc) as ranks,dense_rank() over(partition by name order by number desc) as denserank from datas;",con=conn)
a

从上表的定义我们可以得出，根据RANK()排序后，huawei对应四行得出的结果顺序应该为1，2，2，4，而根据DENSE_RANK()排序后，huawei对应四行得出的结果顺序应该为1，2，2，3,与结果显示一致。

（3）python基础语法实现

作为比较，我们演示一下用pandas里的rank方法对销量进行排序

"""
加一列排序序号的两种方法
"""
#方法一
df = pd.concat([df,df.groupby(by='name',as_index=False)['number'].rank(ascending=False,method='dense').rename(columns={'number': 'rank'})],axis=1) 
#方法二
df = pd.concat([df,df.groupby(by='name',as_index=False)[['number']].apply(lambda x :x.rank(ascending=False,method='dense')).rename(columns={'number': 'rank'})],axis=1) 
#在原数据表后面加一列rank排序
def sort(df,column='rank'):
    return df.sort_values(by=column)
#定义一个排序函数
df.groupby(by='name',group_keys=False).apply(sort)
#利用排序函数对各组排序结果按升序排列

可以看到，使用pandas来进行分组排序比较复杂，首先得调用groupby函数进行分组，之后调用rank函数进行排序，由于是聚合之后的结果，只有rank一列，所以还要借助concat函数将结果加在原列表的最后面，并定义一个函数对分组后的rank进行排序，才能达到我们所要的效果。两者一对比，不难发现利用sql的窗口函数语句非常的方便快捷。

2.分布函数

result = pd.read_sql("select *,cume_dist() over(partition by name order by number) as cumedist from datas;",con=conn)
result

CUME_DIST（）函数主要用于查询小于等于某个值的比例，我们用name分类后根据number升序排列，新加的cumedist一列展示了各类别中小于等于各number值的比例。

3.前后函数

此函数可以用于生成滞后或者提前n数据，从而可以鲜明的对比出不同时期的数量差异。

result = pd.read_sql("select *,lag(number，1) over(partition by name) as lags from datas;",con=conn)
result

从结果上我们可以看到，新生成的lags列是number列滞后一期的结果，从中我们可以清楚地对比本季度与上一季度的销量差异。

4.累计函数

其实累计函数就是以聚合函数作为窗口函数并配上order by子句，例如我们想计算各品牌手机本年内各季度的累计销售额，用累计函数实现十分方便。

reresult = pd.read_sql("select *,sum(number) over(partition by name order by quarter) cunsum from datas;",con=conn)
result

可以看到，新生成的a列为各各品牌从第一季度到第四季度的累计销售额，apple的前半年销售额为1700，全年的销售额为2900。

5.动态窗口

动态窗口是窗口函数比较高级的用法，具体来说就是我们可以控制每次要聚合的列，这对于求移动平均值来说十分的方便。

result = pd.read_sql("select *,avg(number) over(partition by name rows between 1 preceding and current row) as moveavg from datas;",con=conn)
result

我们以apple类为例进行讲解，我们生成的moveavg列是本行所对应的number及其上一行结合起来的平均值，如850=（500+1200）/2。需要注意的是，由于第一行没有上一行，所以第一行输出的是500/1=500。

大家有没有觉得用窗口函数完成一些小任务十分的方便呢，不妨赶快动手去试一试吧！

END

最后，我们为大家揭秘雪球网(https://xueqiu.com/)最新所展示的沪深证券和港股关注人数增长Top10。

腾讯课堂课程二维码

 对我们的推文累计打赏超过1000元，我们即可给您开具发票，发票类别为“咨询费”。用心做事，不负您的支持！

往期推文推荐       Stata之post命令——数据邮递        Seminar丨2002年萨班斯·奥克斯利法案的经济后果        Python基础——三大数字类型，你都了解吗？
        如何用Stata绘制带指向性箭头标注的图像       
        Seminar丨荐仆贷款——19世纪中国的信任辅助贷款              【技能篇】多进程队列间通信
        爬完独立董事的年薪，我的眼镜跌破了！        识别旅游“照骗”——看风景名胜是否名副其实        主成分分析的Python实现
Seminar丨谁更重要：高管股权薪酬与财务报告欺诈DOS能量，超乎你想象！Seminar丨附近的公司：利用卫星图像研究本地信息优势Stata&Python云端课程来啦！Seminar|总统的朋友：政治关联与企业价值
爬虫实战 | 爬取中国天气网Seminar|媒体关联董事对融资和外部治理的影响神奇的组内交叉合并 PDF分章节转TXT并实现可视化——以胡景北知青日记1971至1978年为例
order命令——快速改变变量顺序的利器 

关于我们 

   微信公众号“Stata and Python数据分析”分享实用的Stata、Python等软件的数据处理知识，欢迎转载、打赏。我们是由李春涛教授领导下的研究生及本科生组成的大数据处理和分析团队。

   武汉字符串数据科技有限公司一直为广大用户提供数据采集和分析的服务工作，如果您有这方面的需求，请发邮件到[e-mail]，或者直接联系我们的数据中台总工程司海涛先生，电话：[phone]，wechat: super4ht。海涛先生曾长期在香港大学从事研究工作，现为知名985大学的博士生，爬虫俱乐部网络爬虫技术和正则表达式的课程负责人。

此外，欢迎大家踊跃投稿，介绍一些关于Stata和Python的数据处理和分析技巧。

投稿邮箱：[e-mail]投稿要求：
1）必须原创，禁止抄袭；
2）必须准确，详细，有例子，有截图；
注意事项：
1）所有投稿都会经过本公众号运营团队成员的审核，审核通过才可录用，一经录用，会在该推文里为作者署名，并有赏金分成。

2）邮件请注明投稿，邮件名称为“投稿+推文名称”。
3）应广大读者要求，现开通有偿问答服务，如果大家遇到有关数据处理、分析等问题，可以在公众号中提出，只需支付少量赏金，我们会在后期的推文里给予解答。