快捷導(dǎo)航

使用Pandas實(shí)現(xiàn)MySQL窗口函數(shù)的解決方法

更新時(shí)間：2023年02月22日 10:24:57 作者：Xin學(xué)數(shù)據(jù)

本文主要介紹 MySQL 中的窗口函數(shù)row_number()、lead()/lag()、rank()/dense_rank()、first_value()、count()、sum()如何使用pandas實(shí)現(xiàn)，同時(shí)二者又有什么區(qū)別，感興趣的朋友一起看看吧

一、前言

環(huán)境：
windows11 64位
Python3.9
MySQL8
pandas1.4.2

本文主要介紹 MySQL 中的窗口函數(shù)row_number()、lead()/lag()、rank()/dense_rank()、first_value()、count()、sum()如何使用pandas實(shí)現(xiàn)，同時(shí)二者又有什么區(qū)別。

注：Python是很靈活的語言，達(dá)成同一個(gè)目標(biāo)或有多種途徑，我提供的只是其中一種解決方法，大家有其他的方法也歡迎留言討論。

二、語法對比

數(shù)據(jù)表

本次使用的數(shù)據(jù)如下。
使用 Python 構(gòu)建該數(shù)據(jù)集的語法如下：

import pandas as pd
import numpy as np

df1 = pd.DataFrame({ 'col1' : list(range(1,7))
                    ,'col2' : ['AA','AA','AA','BB','BB','BB']#list('AABCA')
                    ,'col3' : ['X',np.nan,'Da','Xi','Xa','xa']
                    ,'col4' : [10,5,3,5,2,None]
                    ,'col5' : [90,60,60,80,50,50]
                    ,'col6' : ['Abc','Abc','bbb','Cac','Abc','bbb']
                   })
df2 = pd.DataFrame({'col2':['AA','BB','CC'],'col7':[1,2,3],'col4':[5,6,7]})
df3 = pd.DataFrame({'col2':['AA','DD','CC'],'col8':[5,7,9],'col9':['abc,bcd,fgh','rst,xyy,ijk','nml,opq,wer']})

注：直接將代碼放 jupyter 的 cell 跑即可。后文都直接使用df1、df2、df3調(diào)用對應(yīng)的數(shù)據(jù)。

使用 MySQL 構(gòu)建該數(shù)據(jù)集的語法如下：

with t1 as(
  select  1 as col1, 'AA' as col2, 'X' as col3, 10.0 as col4, 90 as col5, 'Abc' as col6 union all
  select  2 as col1, 'AA' as col2, null as col3, 5.0 as col4, 60 as col5, 'Abc' as col6 union all
  select  3 as col1, 'AA' as col2, 'Da' as col3, 3.0 as col4, 60 as col5, 'bbb' as col6 union all
  select  4 as col1, 'BB' as col2, 'Xi' as col3, 5.0 as col4, 80 as col5, 'Cac' as col6 union all
  select  5 as col1, 'BB' as col2, 'Xa' as col3, 2.0 as col4, 50 as col5, 'Abc' as col6 union all
  select  6 as col1, 'BB' as col2, 'xa' as col3, null as col4, 50 as col5, 'bbb' as col6 
)
,t2 as(
  select  'AA' as col2, 1 as col7, 5 as col4 union all
  select  'BB' as col2, 2 as col7, 6 as col4 union all
  select  'CC' as col2, 3 as col7, 7 as col4 
)
,t3 as(
  select  'AA' as col2, 5 as col8, 'abc,bcd,fgh' as col9 union all
  select  'DD' as col2, 7 as col8, 'rst,xyy,ijk' as col9 union all
  select  'CC' as col2, 9 as col8, 'nml,opq,wer' as col9 
)
select * from t1;

注：直接將代碼放 MySQL 代碼運(yùn)行框跑即可。后文跑 SQL 代碼時(shí)，默認(rèn)帶上數(shù)據(jù)集（代碼的1~18行），僅展示查詢語句，如第19行。

對應(yīng)關(guān)系如下：

Python 數(shù)據(jù)集	MySQL 數(shù)據(jù)集
df1	t1
df2	t2
df3	t3

row_number()

row_number()是對檢索的數(shù)據(jù)計(jì)算行號，從1開始遞增。一般涉及分組字段和排序字段，每一個(gè)分組里的行號都唯一。
MySQL 的row_number()函數(shù)在 Python 中可以使用groupby()+rank()實(shí)現(xiàn)類似的效果。

groupby()單列聚合時(shí)，直接將列名傳遞進(jìn)去即可，如groupby('col2')；如果是多列，則傳一個(gè)列表，如groupby(['col2','col6'])。
rank()只能對一列進(jìn)行排序，如df.col2.rank()；當(dāng)有多列排序的時(shí)候，可以使用sort_values(['col6','col5']先排好序，再聚合，然后使用累加函數(shù)cumcount()或排序函數(shù)rank()。

另外，需要注意一點(diǎn)，排序字段如果有重復(fù)值，在 MySQL 中會(huì)隨機(jī)返回，而 Python 中會(huì)默認(rèn)使用index列進(jìn)一步排序。
具體例子如下：

1、單列分組，單列排序
當(dāng)分組和排序都只有一列的時(shí)候，在 Python 中使用groupby()單列聚合加上rank()對單列進(jìn)行排序即可。

語言	Python	MySQL
代碼	df1_1 = df1.copy() df1_1[‘label’] = df1_1.groupby(‘col2’)[‘col5’].rank(ascending=False,method=‘first’) df1_1[[‘col2’,‘col5’,‘label’]]	select col2,col5,row_number()over(partition by col2 order by col5 desc) label from t1;
結(jié)果

2、多列分組，單列排序
當(dāng)有多列分組，則傳一個(gè)列表給groupby()函數(shù)。

語言	Python	MySQL
代碼	df1_1 = df1.copy() df1_1[‘label’] = df1_1.groupby([‘col2’,‘col6’])[‘col5’].rank(ascending=True,method=‘first’) df1_1[[‘col2’,‘col6’,‘col5’,‘label’]]	select col2,col6,col5,row_number()over(partition by col2,col5 order by col5) label from t1;
結(jié)果

3、單列分組，多列排序
如果是多列排序，相對復(fù)雜一些，如下【Python1】先用sort_values()排好序，然后再用groupby()聚合，然后使用rank()將排序序號加上；而【Python2】和【Python1】前2步相同，在最后一步使用了cumcount()實(shí)現(xiàn)編號。

語言	Python	MySQL
代碼	【Python1】 df1_1 = df1.copy() df1_1[‘label’] = df1_1.sort_values([‘col6’,‘col5’],ascending=[False,True]).groupby([‘col2’])[‘col2’].rank(ascending=False,method=‘first’) df1_1[[‘col2’,‘col6’,‘col5’,‘label’]] 【Python2】 df1_1 = df1.copy() df1_1[‘label’] = df1_1.sort_values([‘col6’,‘col5’],ascending=[False,True]).groupby([‘col2’]).cumcount()+1 df1_1[[‘col2’,‘col6’,‘col5’,‘label’]]	select col2,col6,col5,row_number()over(partition by col2 order by col6 desc,col5) label from t1;
結(jié)果

3、多列分組，多列排序
多列分組和多列排序，直接在【3、單列分組，多列排序】的基礎(chǔ)上，將多個(gè)分組字段添加到groupby([])中的列表即可。不再贅述。

lead()/lag()

lead()是從當(dāng)前行向后取列值，也可以理解為將指定的列向上移動(dòng)；而lag()則相反，是從當(dāng)前行向前取列值，也可以理解為將指定的列向下移動(dòng)。
配合排序，二者可以進(jìn)行互換，即：

正序的lead()==倒序的lag()
倒序的lead()==正序的lag()

在 Python 中，可以通過shift()函數(shù)實(shí)現(xiàn)列值的上下移動(dòng)，當(dāng)傳入一個(gè)正數(shù)時(shí)，列值向下移動(dòng)，當(dāng)傳入一個(gè)負(fù)數(shù)時(shí)，列值向上移動(dòng)。
注：關(guān)于單列/多列分組和單列/多列排序的情況，參考row_number()，不再贅述。

1、移動(dòng)1行
移動(dòng)1行時(shí)，MySQL 中直接使用lead(col1)/lag(col1)即可，使用lead(col1,1)/lag(col1,1)也沒問題，再結(jié)合升降序?qū)崿F(xiàn)列值的上下移動(dòng)。
在 Python 中，則使用shift(-1)或shift(1)實(shí)現(xiàn)相同的效果。以下例子是將col1下移，所以使用shift(-1)。

語言	Python	MySQL
代碼	df1_1 = df1.copy() df1_1[‘col1_2’] = df1_1.groupby([‘col2’]).col1.shift(-1) df1_1[[‘col2’,‘col1’,‘col1_2’]].sort_values([‘col2’,‘col1’],ascending=[True,True])	【MySQL1】 select col2,col1,lead(col1)over(partition by col2 order by col1) col1_2 from t1; 【MySQL2】 select col2,col1,lag(col1)over(partition by col2 order by col1 desc) col1_2 from t1;
結(jié)果

2、移動(dòng)多行
移動(dòng)多行的時(shí)候，MySQL 中需要指定移動(dòng)行數(shù)，如下例子，移動(dòng)2行，使用lead(col1,2)或lag(col1,2)，再結(jié)合升降序?qū)崿F(xiàn)列值的上下移動(dòng)。
在 Python 中，則修改傳遞給shift()函數(shù)的參數(shù)值即可，如下例子，使用shift(2)向上移動(dòng)2行。

語言	Python	MySQL
代碼	df1_1 = df1.copy() df1_1[‘col1_2’] = df1_1.groupby([‘col2’]).col1.shift(2) # 通過shift控制 df1_1[[‘col2’,‘col1’,‘col1_2’]].sort_values([‘col2’,‘col1’],ascending=[True,True])	【MySQL1】 select col2,col1,lead(col1,2)over(partition by col2 order by col1 desc) col1_2 from t1; 【MySQL2】 select col2,col1,lag(col1,2)over(partition by col2 order by col1) col1_2 from t1;
結(jié)果

rank()/dense_rank()

rank()和dense_rank()用于計(jì)算排名。rank()排名可能不連續(xù)，就是當(dāng)有重復(fù)值的時(shí)候，會(huì)并列使用小的排名，而重復(fù)值之后的排名則按照重復(fù)個(gè)數(shù)疊加往后排，如一組數(shù)（10,20,20,30），按升序排列是（1,2,2,4）；而dense_rank()的排名是連續(xù)的，還是上面的例子，按升序排列是（1,2,2,3）。
而在 Python 中，排序同樣是通過rank()函數(shù)實(shí)現(xiàn)，只是method和row_number()使用的不一樣。實(shí)現(xiàn)rank()的效果，使method='min'，而實(shí)現(xiàn)dense_rank()的效果，使用method='dense'。除了這兩種和在row_number()中使用的method='first'，還有average和max。average的邏輯是所有值進(jìn)行不重復(fù)連續(xù)排序之后，將分組內(nèi)的重復(fù)值的排名進(jìn)行平均，還是上面的例子，按升序排列是（1,2.5,2.5,4），max和min相反，使用的是分組內(nèi)重復(fù)值取大的排名進(jìn)行排序，還是上面的例子，按升序排列是（1,3,3,4）。
同樣地，排序字段如果有重復(fù)值，在 MySQL 中會(huì)隨機(jī)返回，而 Python 中會(huì)默認(rèn)使用index列進(jìn)一步排序。

注：關(guān)于單列/多列分組和單列/多列排序的情況，參考row_number()，不再贅述。
1、rank()
Python 中使用rank(method='min')實(shí)現(xiàn) MySQL 中的rank()窗口函數(shù)。

語言	Python	MySQL
代碼	df1_1 = df1.copy() df1_1[‘label’] = df1_1.groupby([‘col2’])[‘col5’].rank(ascending=True,method=‘min’) df1_1[[‘col2’,‘col5’,‘label’]]	select col2,col5,rank()over(partition by col2 order by col5) col1_2 from t1;
結(jié)果

2、dense_rank()
Python 中使用rank(method='dense')實(shí)現(xiàn) MySQL 中的rank()窗口函數(shù)。

語言	Python	MySQL
代碼	df1_1 = df1.copy() df1_1[‘label’] = df1_1.groupby([‘col2’])[‘col5’].rank(ascending=True,method=‘dense’) df1_1[[‘col2’,‘col5’,‘label’]]	select col2,col5,dense_rank()over(partition by col2 order by col5) col1_2 from t1;
結(jié)果

first_value()

MySQL 中的窗口函數(shù)first_value()是取第一個(gè)值，可用于取數(shù)據(jù)默認(rèn)順序的第一個(gè)值，也可以通過排序，取某一列的最大值或最小值。
在 Pandas 中，也有相同功能的函數(shù)first()。
不過，first_value()是窗口函數(shù)，不會(huì)影響表單內(nèi)的其他字段，但first()時(shí)一個(gè)普通函數(shù)，只返回表單中的第一個(gè)值對應(yīng)的行，所以在 Python 中要實(shí)現(xiàn)first_value()窗口函數(shù)相同的結(jié)果，需要將first()函數(shù)返回的結(jié)果，再通過表聯(lián)結(jié)關(guān)聯(lián)回原表（具體例子如下）。在 Python 中，還有一個(gè)last()函數(shù)，和first()相反，結(jié)合排序，也可以實(shí)現(xiàn)相同效果，和first()可互換，讀者可自行測試，不再贅述。

注：關(guān)于單列/多列分組和單列/多列排序的情況，參考row_number()，不再贅述。
1、取最大值
MySQL 中，對col5降序，便可通過first_value()取得最大值。同樣，在 Python 中，使用sort_values()對col5進(jìn)行降序，便可通過first()取得最大值，然后再merge()回原表。

語言	Python	MySQL
代碼	df1_1 = df1.copy() df1_2 = df1_1.sort_values([‘col5’],ascending=[False]).groupby([‘col2’]).first().reset_index()[[‘col2’,‘col5’]] # 最好加個(gè)排序 df1[[‘col2’,‘col5’]].merge(df1_2,on = ‘col2’,how = ‘left’,suffixes=(‘’,‘_2’))	select col2,col5,first_value(col5)over(partition by col2 order by col5 desc) col5_2 from t1;
結(jié)果

2、取最小值
取最小值，則是在取最大值的基礎(chǔ)上，改變col5的排序即可，由降序改為升序。

語言	Python	MySQL
代碼	df1_1 = df1.copy() df1_2 = df1_1.sort_values([‘col5’],ascending=[True]).groupby([‘col2’]).first().reset_index()[[‘col2’,‘col5’]] df1[[‘col2’,‘col5’]].merge(df1_2,on = ‘col2’,how = ‘left’,suffixes=(‘’,‘_2’))	select col2,col5,first_value(col5)over(partition by col2 order by col5) col5_2 from t1;
結(jié)果

count()/sum()

MySQL 的聚合函數(shù)count()和sum()等，也可以加上over()實(shí)現(xiàn)窗口函數(shù)的效果。

count()可以用于求各個(gè)分組內(nèi)的個(gè)數(shù)，也可以對分組內(nèi)某個(gè)列的值進(jìn)行累計(jì)。
sum()可以用于對各個(gè)分組內(nèi)某個(gè)列的值求和，也可以對分組某個(gè)列的值進(jìn)行累加。

在 Python 中，針對累計(jì)和累加的功能，可以使用groupby()+cumcount()和groupby()+cumsum()實(shí)現(xiàn)（如下例子1和2），而針對分組內(nèi)的計(jì)數(shù)和求和，可以通過groupby()+count()和groupby()+sum()實(shí)現(xiàn)（如下例子3和4）。

注：關(guān)于單列/多列分組和單列/多列排序的情況，參考row_number()，不再贅述。
1、升序累計(jì)
Python 中使用sort_values()+groupby()+cumcount()實(shí)現(xiàn) MySQL count(<col_name>)over(partition by <col_name> order by <col_name>)效果。

語言	Python	MySQL
代碼	df1_1 = df1.copy() df1_1[‘col5_2’] = df1_1.sort_values([‘col5’,‘col1’],ascending=[True,False]).groupby(‘col2’).col5.cumcount()+1 df1_1[[‘col2’,‘col5’,‘col5_2’]]	select col2,col5,count(col5)over(partition by col2 order by col5,col1) col5_2 from t1;
結(jié)果

2、升序累加
Python 中使用sort_values()+groupby()+cumsum()實(shí)現(xiàn) MySQL sum(<col_name>)over(partition by <col_name> order by <col_name>)效果。

語言	Python	MySQL
代碼	df1_1 = df1.copy() df1_1[‘col5_2’] = df1_1.sort_values([‘col5’,‘col1’],ascending=[True,False]).groupby(‘col2’).col5.cumsum() df1_1[[‘col2’,‘col5’,‘col5_2’]]	select col2,col5,sum(col5)over(partition by col2 order by col5,col1) col5_2 from t1;
結(jié)果

3、分組計(jì)數(shù)
Python 中使用sort_values()+groupby()+count()實(shí)現(xiàn) MySQL count(<col_name>)over(partition by <col_name>)效果。

語言	Python	MySQL
代碼	df1_1 = df1.copy() df1_2 = df1_1.sort_values([‘col5’,‘col1’],ascending=[True,False]).groupby(‘col2’).col5.count().reset_index() df1_1[[‘col2’,‘col5’]].merge(df1_2,how=‘left’,on=‘col2’,suffixes=(‘’,‘_2’))	select col2,col5,count(col5)over(partition by col2) col5_2 from t1;
結(jié)果

4、分組求和
Python 中使用sort_values()+groupby()+sum()實(shí)現(xiàn) MySQL sum(<col_name>)over(partition by <col_name>)效果。

語言	Python	MySQL
代碼	df1_1 = df1.copy() df1_2 = df1_1.sort_values([‘col5’,‘col1’],ascending=[True,False]).groupby(‘col2’).col5.sum().reset_index() df1_1[[‘col2’,‘col5’]].merge(df1_2,how=‘left’,on=‘col2’,suffixes=(‘’,‘_2’))	select col2,col5,sum(col5)over(partition by col2) col5_2 from t1;
結(jié)果

三、小結(jié)

MySQL 的窗口函數(shù)效果，在 Python 中，基本都需要經(jīng)過多個(gè)步驟，使用多個(gè)函數(shù)進(jìn)行組合處理。窗口函數(shù)涉及到分組字段和排序字段，在 Python 中對應(yīng)使用groupby()和sort_values()，所以基本上在 Python 中實(shí)現(xiàn)窗口函數(shù)的效果都需要使用到這兩個(gè)函數(shù)輔助處理數(shù)據(jù)。剩下的聚合形式就根據(jù)聚合窗口函數(shù)的特性做修改，對應(yīng)關(guān)系如下：