带您使用SNB理解序列分析的SQL窗口函数 (Lead、Lag)及示例

欢迎大家来到IT世界,在知识的湖畔探索吧!

欢迎大家来到IT世界,在知识的湖畔探索吧!

在《带您使用SNB理解SQL开窗函数及示例(Rank, Dense Rank, RowNumber)》短文中我们介绍SQL 开窗函数的基本概念，说明Rank, Dense Rank, and Row Number 的区别。今天我们继续使用SNB介绍两个基于时间分析(Time-Based Analysis)的窗口函数：LEAD() and LAG()。Lead 和 Lag 函数是窗口函数，允许我们分别访问后续和前置行。这些函数对于分析序列（Time-Based Analysis）数据中的趋势或模式非常有用。

接下的示例我们以smartnotebook 内置的DataFrameSQL(dfSQL) 为进行示例介绍LEAD() and LAG()，当然这些SQL 也可以在支持开窗函数的数据库或数据平台执行。

• smartnotebook：SmartNoteBook 是一款现代化的 Notebook，是 开箱即用、云原生、协作式 在线数据科学与分析平台。SmartNoteBook是参考Jupyter NoteBook思想实现数据科学与分析的强大环境，深度融合Python(编程能力、AI能力)与SQL(数据处理)、可视化于一体，更高效编辑、执行和共享数据应用模型，进行数据采集、探索、机器学习、深度学习和交互可视化。开箱即用的数据科学、机器学习工具包；高效编辑器辅助和团队协作跟踪功能；通过Notebook结构化逻辑关系配合自然语言可以很好表现数据分析过程、阐述数据故事，形成数据报告，深层次的挖掘数据、释放数据价值。
• dfSQL：使用SQL的方式操作DataFrame变量，并将结果保存在新的DataFrame变量。

下面是dfSQL 操作方式：

接下来的示例都是基于下面的2024-03-13 至 2024-03-22 上海地铁客流日乘车人数的数据：

import pandas as pd data = [ ["2024-03-13", 1192.10],["2024-03-14", 1179.20],["2024-03-15", 1268.90], ["2024-03-16", 845.40],["2024-03-17", 683.80],["2024-03-18", 1167.10], ["2024-03-19", 1205.40],["2024-03-20", 1238.20], ["2024-03-21", 1230.50], ["2024-03-22", 1299.20] ] # 上海地铁客2024-03-13 至 2024-03-22 客流人数 sh_metro_passenger = pd.DataFrame(data, columns=["date", "passenger_num"]) sh_metro_passenger

欢迎大家来到IT世界,在知识的湖畔探索吧!

LEAD 和 LAG窗口函数的说明

LEAD 和 LAG 函数是窗口函数，分别允许我们访问后续和前置行。这些函数对于分析序列数据（特别是时间序列数据，基于时间分析 Time-Based Analysis）中的趋势或模式非常有用。

• LEAD 的基本语法如下：

欢迎大家来到IT世界,在知识的湖畔探索吧!LEAD(column_name, offset, default_value) OVER (ORDER BY ordering_column)

这里，offset 是查找下一个值时向前查看的行数。default_value 是在下一行不可用时返回的值。

• LAG 的基本语法如下：

LAG(column_name, offset, default_value) OVER (ORDER BY ordering_column)

类似于 LEAD 函数，这里的 offset 表示查找前一个值时向后查看的行数。default_value 是在上一行不可用时返回的值。

示例1:Lead、LAG 窗口函数的简单示例

一个简单的示例，假设我们只想获取下一行（天）和上一行（天)的 passenger_num 并显示它，我们可以编写如下代码：

欢迎大家来到IT世界,在知识的湖畔探索吧!SELECT date,passenger_num, LEAD(passenger_num) OVER (ORDER BY date) AS Next_passenger_num, LAG(passenger_num) OVER (ORDER BY date) AS Prev_passenger_num FROM sh_metro_passenger;

示例2：随时间的变化率（LAG的应用）

上面的示例计算了上一天和下一天客流量(passenger_num)之外，我们还需要另一列，其中将显示当前行客流量(passenger_num)与上一行(上一天)客流量(passenger_num)相比的增长或减少百分比。我们可以应用LAG 函数编写如下查询：

SELECT date,passenger_num, LEAD(passenger_num) OVER (ORDER BY date) AS Next_passenger_num, LAG(passenger_num) OVER (ORDER BY date) AS Prev_passenger_num, ((passenger_num - LAG(passenger_num) OVER (ORDER BY date)) / LAG(passenger_num) OVER (ORDER BY date)) * 100 AS passenger_num_Change FROM sh_metro_passenger;

其他开窗函数

上述示例主要就Lead、Lag 相关进行了介绍和示例。窗口函数详细参考下图，不同数据库或数据平台支持不一定一致，具体还参考具体平台的文档。

SQL窗口函数如LEAD和LAG在数据分析中至关重要。它们提供了强大的功能，帮助分析序列数据(特别是时间序列)中的趋势和模式。LEAD获取下一行值，LAG获取前一行值，对比可计算变化率。掌握这些技术对于数据科学家和分析师来说至关重要，为发现数据见解提供了关键工具。