python sql 三大工具（Python中8种流行的SQL窗口函数了解一下）

有关如何在业务分析中利用Pandas来有效复制最常用的SQL窗口函数的教程。

任何致力于构建公司KPI或创建分析随着时间推移的业务绩效的数据分析人员都知道SQL窗口功能是一个强大的工具。使用它们的真正优势在于，可以在不同的时间范围内计算多个指标，而不会影响原始数据集的粒度。反过来，这意味着无需使用多个自我联接或CTE即可实现更多目标，从而节省了大量代码行。

如果到目前为止，您主要是使用SQL来建立指标并提取见解，并且正在学习Python，那么您可能想知道如何在Pandas中复制您心爱的Window函数。在接下来的教程中，我将展示如何利用Python中的窗口函数来使您的代码更加紧凑和高效。

数据集1

在本教程的第一部分中，我们将使用可通过GitHub下载的模拟客户订单数据集。数据集最初包括七列（订单日期，订单ID，客户ID，商品ID，商品价格，数量和已付款金额（£）），但我们还将添加订单月份，如下所示：

import pandas as pd import yfinance as yf orders = pd.read_csv('/Users/anbento/Documents/Data_Sets/Medium/customer_orders.csv', parse_dates = ['order_date'], index_col = ['order_date'] ) orders.insert(0, 'order_month', pd.DatetimeIndex(orders.index).to_period('M') ) orders.reset_index(inplace = True) orders.columns = ['Order Date', 'Order Month' ,'Order ID', 'Customer ID', 'Item ID', 'Item Price', 'Quantity', 'Amount Paid (£)'] print(orders.shape) orders.head(10) #orders.groupby('Customer ID')['Order ID'].count()) #orders.groupby('Customer ID')['Amount Paid (£)'].sum()

我们发现数据集包括50个不同的订单ID和8列，但是如果我们想进一步探索它，我们可以通过运行以下命令来分析客户的订单分布和支付的金额：

print(orders.groupby('Customer ID')['Order ID'].count()) orders.groupby('Customer ID')['Amount Paid (£)'].sum()

正如我们所看到的，总共有四个客户，其中customer_1是完成最多订单（18）并花费最高金额（1291.75英镑）的一个。请注意，如何在Python中使用DataFrame.groupby（）方法来获得与在查询结束时通过该列进行SQL分组所获得的结果相同的结果。但是还有更多，因为此方法还用于复制典型的窗口函数的over（partition by…）子句，我们将在下面学习。

Python中的窗口函数

1. 行号（）→等级（方法=“第一”）

SQL Row Number（）函数为数据集分区内的每一行分配一个顺序整数。在实践中通常使用它来创建一个辅助列，该列根据指定的分区和顺序对字段进行排名。然后，可以使用该列更有效地过滤或合并数据集。

假设我们要根据订单日期（从较新的日期开始）按客户对订单进行排名。为了在SQL中获得此结果，我们可以编写：

row number() over(partition by customer_id order by order_date)

对于大熊猫，通过将.rank（method ='first'） 函数应用于按Order Date过滤的GroupBy对象以创建Row Num列，可以实现相同的结果：

#1. ROW NUMBER() --> .RANK(method='first') # Ranks orders over time by Custoner ID orders['Row Num'] = orders.groupby(['Customer ID'])['Order Date'].rank(method='first') # Sorts values by Customer ID and Order Date in ascending order orders.sort_values(by= ['Customer ID','Order Date'], inplace = True) orders = orders.reset_index(drop = True) orders.head(20)

我们可以验证customer_1是否确实完成了18个订单，第一个订单于2019-02-13（行num = 1），最后一个订单于2020-02-25（行num = 18）。由于我们按客户ID进行了划分和排序，因此，在对所有18个订单进行排名之后，该函数将开始对customer_2的订单进行排名：

问题1：如果要按降序分配行号（从最新到最旧的顺序）怎么办？

这将很简单，因为尽管未在代码中显示，但pandas rank（method =…）函数具有一个升序参数，默认情况下设置为true。通过将其设置为False，我们将颠倒等级的分配方式：

orders.groupby（['Customer ID']）['Order Date']。rank（method ='first'，ascending = False）

问题2：如果我们想按多列进行划分（例如客户ID和订单月份）怎么办？

在这种情况下，我们只需要将首选字段添加到GroupBy对象：

#SQL语法 row number() over(partition by customer_id, order_month order by order_date) #Python Syntax orders.groupby([‘Customer ID’, 'Order Month'])[‘Order Date’].rank(method=’first')

＃2。等级（）→等级（方法='min'）

SQL RANK（）函数为结果集分区中的每一行分配一个等级。与ROW NUMBER（）不同，等级不是连续的，这意味着分区中共享相同值的行将获得相同的等级。使用与上述相同的示例，SQL语法为：

#2. RANK() --> RANK(method='min') orders['Rank'] = orders.groupby(['Customer ID'])['Order Date'].rank(method='min') orders.head(10)

而与之相对应的Python为：

#2. RANK() --> RANK(method='min') orders['Rank'] = orders.groupby(['Customer ID'])['Order Date'].rank(method='min') orders.head(10)

由于customer_1在2019-02-21年执行了两个订单，因此两个订单都被分配了等级= 3，而下一个订单被分配了等级= 5，则完全跳过了等级=4。这正是我们得到的排名类型。我们必须使用SQL rank（）窗口函数。

＃3。Dense_rank（）→等级（method ='dense'）

如果我们想避免排名值之间的差距，我们应该改用SQL density_rank（）函数。实际上，与rank（）函数不同，使用density_rank（）返回连续的等级值。在我们的例子中，SQL语法为：

density_rank（）over（按customer_id划分的订单，按order_date划分的）

而在Python中，我们只需要在rank（）函数中将method ='min'替换为method ='dense' ：

正如预期的那样，尽管在分析期间是客户的第5个订单，但customer_1在2019_02_23上执行的订单现在已被分配为等级= 4 。

总结到目前为止，我们已经学到了什么：尽管在SQL中有3个不同的函数来计算数字数据等级，但在熊猫中，我们只需要将rank（）函数与方法（“ first”，“ min”或“ dense”）一起使用）和升序（True 或False）参数获得所需的结果。

＃4.Sum（...）over（按…划分。。。按..行无限制在前）→cumsum（）

现在，我们希望计算每个客户每个月支付的累计金额 ，并按订购日期排序。此计算也称为运行总计，它可能是业务分析中最常用的指标之一。在SQL中实现此目标的一种方法是：

sum(amount_paid) over(partition by customer_id, oravg(amount_paid) over(partition by customer_id, order_month) der_month order by order_date rows unbounded preceding)

上面的语法并不完全是最有效，最直观的语法：有很多代码要编写，如果我们希望按order_date排序，我们还必须包括rows…子句以计算累积总和而不是总和。选择的窗口。

这是Python简洁的原因之一，为了实现与上述相同的结果，我们只需要编写以下代码：

正如预期的那样，“ 运行总金额”（£）列显示每个客户在一个月内的已付金额（£）的累加总和，该行对应于最后一个当月订单，最终显示该客户在那个月。

＃5。平均（…）超过（被...划分）→变换（np.mean）

以类似的方式，我们也可能希望计算每个客户每月的平均花费。这次，SQL语法非常直观：

avg(amount_paid) over(partition by customer_id, order_month)

而值得对Python解决方案进行详细说明：

之所以使用pandas DataFrame.transform（） 函数，是因为它允许我们在计算GroupBy对象的均值之后使Series的长度等于原始数据集的长度。这种方法使我们达到了预期的结果：

数据集2

在本教程的第二部分中，我们将介绍AAPL和AMZN股票调整。使用yfinance包关闭Yahoo Finance的价格。我们应该在一开始就已经导入了它，但是如果不是这种情况，我们现在可以通过运行以下命令来导入它：

!pip install yfinance # <-- to install the package import yfinance as yf aapl = yf.download(“AAPL”, start=”2020–04–20", end=”2020–04–30").resample(‘D’).ffill() amzn = yf.download(“AMZN”, start=”2020–04–20", end=”2020–04–30").resample(‘D’).ffill()

通过上面的代码，我们还使用yf.download（）方法创建了2020–04–20至2020–04–30期间的两个新数据集（aapl和amzn）。为了包括周末和节假日，对数据进行了重新采样，并将缺失的股票价格替换为之前的最新调整。关闭价格观察。最终将这些额外的操作应用于数据集（GitHub中提供完整代码），这些数据最终已合并到股票 DataFrame中：

＃6超前/滞后（return_value，偏移量）→Shift（n）

使用股票数据集，我们现在希望计算DoD和WoW Adj。平仓价格变化百分比，并通过保持DataFrame长度不变来实现，我们需要一个函数来访问位于当前行之前的特定物理偏移量的行。在SQL中，此窗口函数名为lag（），在我们的示例中，其语法为：

#Retrieves the stock price located 1 row back from the current row lag(adj_close, 1) over(partition by symbol order by date) #Retrieves the stock price located 7 rows back from the current row lag(adj_close, 7) over(partition by symbol order by date)

重要提示： 由于我们已经对其进行了重新采样，因此我们的数据集现在具有每日粒度，这意味着要获取昨天的股价，我们只需要移回一行，而要获取上周同一天记录的股价，我们只需要移动后7行。如果我们排除了周末和节假日，这些计算就不会那么简单了。

在Python我们可以实现通过施加相同的结果移位（）一个的GroupBy对象（通过分组上功能的符号，并通过过滤ADJ。关闭）来创建LAG1和Lag7列。请注意，因为我们希望分别向后移动1和7行（并且我们的数据集具有降序），所以shift（）函数将负整数 作为参数：

我们可以看到shift（n）函数已正确应用到每只股票上，因此在第一个观察日期（2020–04–20），没有可用的先前价格，将显示NaN值。以同样的方式，在Lag7列中显示的第一个有效值会在第一个观察日期（2020–04–27）后7天出现。

到目前为止，我们只是设法获取滞后的股票价格，而不是计算变化百分比。为此，我们可以应用以下公式：

[(Adj Close / Lag1) - 1] x 100 <-- DoD % Change [(Adj Close / Lag7) -1] x 100 <-- WoW % Change

或使用pct_change（n）函数计算内部变动后的股票价格并返回其百分比变化：

为了简洁起见，在本教程中，我们不会显示使用lead（）函数的等效示例，但是很容易猜到，唯一真正的变化是分配给shift（）和pct_change（ ）功能。请记住，对数据集进行排序的方式将影响应传递给函数以获取预期结果的符号。

＃7。first / last value（）→rank（method ='first'，ascending = True / False）== 1

窗口函数的first value（） 和last value（） 用于在数据集的有序分区中检索第一行或最后一行的值。假设我们想找到第一个和最后一个可用的调整。每月关闭 AAPL股票的价格。我们将使用的修改后的aapl数据集的前10行显示如下：

访问我的GitHub帐户以获取完整代码

而我们处理SQL查询的方法是：

#The aapl dataset includes prices for one stock only, therefore the #partition is just applied on order_month first value(adj_close) over(partition by order_month order by date) last value(adj_close) over(partition by order_month order by date)

在Python中，不存在完全类似于first / last value（）的函数，但是可以通过使用Pandas rank（method ='first'）函数对分区中的值进行排序，然后返回来获得相似的结果只是rank = 1的那个。根据我们如何在rank（）函数中设置升序参数，rank = 1的值将与分区中的第一个或最后一个值匹配。

尽管不是特别有挑战性，但此过程还是有点困难，因为我们最终将需要在整个分区上回填或正向填充第一个或最后一个值，以获得所需的结果：

有效期为2020-03-16至2020-4-14，这是第一个（或更近期的）可用调整。三月份APL的收盘价为242.2美元（Rank Asc = 1），最后一个（或最近）为254.3美元（Rank Desc = 1）。同样在4月，第一个可用的调整。AAPL的收盘价为240.9美元，最后一个为287美元。

＃8。Avg（…）over（按…顺序按n个前一行和当前行之间的..行划分）→滚动（window =…）.mean（）

我们将在本教程中复制的最后一个窗口函数是移动平均值。移动平均值是更一般功能的特例：

avg（return_value）over（）

为了在SQL中进行计算，我们需要声明一个row…子句（就像我们对正在运行的total所做的一样）。例如，如果我们要计算AAPL调整的7天移动平均线。用SQL的Close Price（假设数据集按升序排列），我们将这样写：

avg(adj_close) over(order by date rows between 6 preceding and current row)

在…行中，我们仅表示希望在计算平均值时包括最新观察值，因此我们只需要前面的6条记录即可获得7天的窗口。对于经验丰富的分析师来说，这个概念似乎微不足道，但可能会引起新手的困惑。

幸运的是，使用Pandas计算移动平均线是一种真正的乐趣，因为我们只需要创建一个滚动对象（指定所需的window），然后在其上应用mean（）函数即可。另外，我们也可能希望计算和扩展均值， 这本身就是移动平均的一种特殊情况：

实际上，这两个指标之间的差异在于，当我们在滚动对象上应用函数时，窗口大小保持不变，而对于扩展对象，窗口则保持增长。使用扩展窗口的逻辑是，随着时间的流逝，我们观察到可以添加到平均计算中的新价格。这是我们可能希望将其包含在计算得出的指标中的新信息。

结论

在本文中，我们学习了如何使用Pandas在Python中复制8种流行的SQL窗口函数。如果您从事分析工作，那么现在应该具备构建KPI的能力，以使用SQL或Python互换监视性能。在这两种语言中，当然都有多种方法可以达到相同的结果。

,