好的，我们开始今天的讲座，主题是利用LAG()与LEAD()函数在SQL中进行行间数据比较。这两个函数是SQL Server、PostgreSQL、Oracle等数据库系统中提供的窗口函数，它们允许我们在查询结果集中访问当前行之前或之后的行的数据，而无需自连接。这使得进行诸如计算差异、同比、环比等操作变得非常方便。
一、窗口函数的基本概念
在深入LAG()和LEAD()之前，我们需要简单了解一下窗口函数的基本概念。 窗口函数，顾名思义，是在一个“窗口”上执行的函数。这个“窗口”是与当前行相关的一组行。 与聚合函数（如SUM(), AVG(), COUNT()）不同，窗口函数不会将多个行合并为单个输出行。 相反，它会为结果集中的每一行都返回一个值，这个值是基于与当前行相关的窗口计算得出的。
窗口函数的基本语法结构如下：
function_name(arguments) OVER (
[PARTITION BY column_list]
[ORDER BY column_list [ASC | DESC]]
[ROWS | RANGE frame_definition]
)function_name(arguments): 要执行的窗口函数，例如LAG(),LEAD(),ROW_NUMBER(),RANK()等。OVER(...): 定义窗口的子句。PARTITION BY column_list: 将结果集划分为多个分区，窗口函数将分别应用于每个分区。类似于GROUP BY，但不会将行折叠成一行。ORDER BY column_list [ASC | DESC]: 在每个分区内对行进行排序，定义窗口函数计算的顺序。ROWS | RANGE frame_definition: 定义窗口的大小。例如，可以指定当前行之前/之后的多少行作为窗口。
二、LAG()函数
LAG()函数允许访问结果集中当前行之前的行的数据。它的语法如下：
LAG(expression [, offset [, default_value]]) OVER (
[PARTITION BY column_list]
ORDER BY column_list [ASC | DESC]
)expression: 要访问的列或表达式。offset: 可选参数，指定要访问的行相对于当前行的偏移量。 默认值为1，表示访问前一行。default_value: 可选参数，如果偏移量超出了结果集的范围，则返回的默认值。 如果未指定，则返回NULL。PARTITION BY column_list: 可选参数，将结果集划分为多个分区。ORDER BY column_list [ASC | DESC]: 必须参数，在每个分区内对行进行排序。LAG()函数会根据此排序来确定前一行。
示例1：计算销售额的环比增长
假设我们有一个名为Sales的表，包含以下数据：
| Date | SalesAmount |
|---|---|
| 2023-01-01 | 100 |
| 2023-01-02 | 120 |
| 2023-01-03 | 110 |
| 2023-01-04 | 130 |
以下查询使用LAG()函数计算每日销售额的环比增长：
SELECT
Date,
SalesAmount,
LAG(SalesAmount, 1, 0) OVER (ORDER BY Date) AS PreviousDaySales,
(SalesAmount - LAG(SalesAmount, 1, 0) OVER (ORDER BY Date)) * 100.0 / LAG(SalesAmount, 1, 0) OVER (ORDER BY Date) AS GrowthPercentage
FROM
Sales;查询结果如下：
| Date | SalesAmount | PreviousDaySales | GrowthPercentage |
|---|---|---|---|
| 2023-01-01 | 100 | 0 | NULL |
| 2023-01-02 | 120 | 100 | 20.00 |
| 2023-01-03 | 110 | 120 | -8.33 |
| 2023-01-04 | 130 | 110 | 18.18 |
解释：
LAG(SalesAmount, 1, 0) OVER (ORDER BY Date): 获取前一天的销售额。1表示偏移量为1，0表示如果前一天没有销售额（例如第一天），则返回0。(SalesAmount - LAG(SalesAmount, 1, 0) OVER (ORDER BY Date)) * 100.0 / LAG(SalesAmount, 1, 0) OVER (ORDER BY Date): 计算增长百分比。
示例2：按类别计算销售额的环比增长
假设我们有一个名为CategorySales的表，包含以下数据：
| Category | Date | SalesAmount |
|---|---|---|
| A | 2023-01-01 | 50 |
| A | 2023-01-02 | 60 |
| A | 2023-01-03 | 55 |
| B | 2023-01-01 | 30 |
| B | 2023-01-02 | 35 |
| B | 2023-01-03 | 40 |
以下查询使用LAG()函数按类别计算每日销售额的环比增长：
SELECT
Category,
Date,
SalesAmount,
LAG(SalesAmount, 1, 0) OVER (PARTITION BY Category ORDER BY Date) AS PreviousDaySales,
(SalesAmount - LAG(SalesAmount, 1, 0) OVER (PARTITION BY Category ORDER BY Date)) * 100.0 / LAG(SalesAmount, 1, 0) OVER (PARTITION BY Category ORDER BY Date) AS GrowthPercentage
FROM
CategorySales;查询结果如下：
| Category | Date | SalesAmount | PreviousDaySales | GrowthPercentage |
|---|---|---|---|---|
| A | 2023-01-01 | 50 | 0 | NULL |
| A | 2023-01-02 | 60 | 50 | 20.00 |
| A | 2023-01-03 | 55 | 60 | -8.33 |
| B | 2023-01-01 | 30 | 0 | NULL |
| B | 2023-01-02 | 35 | 30 | 16.67 |
| B | 2023-01-03 | 40 | 35 | 14.29 |
解释：
PARTITION BY Category: 将结果集按类别划分，分别计算每个类别的环比增长。
三、LEAD()函数
LEAD()函数允许访问结果集中当前行之后的行的数据。 它的语法如下：
LEAD(expression [, offset [, default_value]]) OVER (
[PARTITION BY column_list]
ORDER BY column_list [ASC | DESC]
)expression: 要访问的列或表达式。offset: 可选参数，指定要访问的行相对于当前行的偏移量。 默认值为1，表示访问后一行。default_value: 可选参数，如果偏移量超出了结果集的范围，则返回的默认值。 如果未指定，则返回NULL。PARTITION BY column_list: 可选参数，将结果集划分为多个分区。ORDER BY column_list [ASC | DESC]: 必须参数，在每个分区内对行进行排序。LEAD()函数会根据此排序来确定后一行。
示例1：计算下一个订单的交货时间
假设我们有一个名为Orders的表，包含以下数据：
| OrderID | OrderDate | ShipDate |
|---|---|---|
| 1 | 2023-01-01 | 2023-01-03 |
| 2 | 2023-01-02 | 2023-01-05 |
| 3 | 2023-01-03 | 2023-01-06 |
以下查询使用LEAD()函数计算每个订单到下一个订单的交货时间间隔：
SELECT
OrderID,
OrderDate,
ShipDate,
LEAD(OrderDate, 1, NULL) OVER (ORDER BY OrderDate) AS NextOrderDate,
LEAD(ShipDate, 1, NULL) OVER (ORDER BY OrderDate) AS NextShipDate,
DATEDIFF(day, ShipDate, LEAD(ShipDate, 1, NULL) OVER (ORDER BY OrderDate)) AS DaysToNextShip
FROM
Orders;查询结果如下：
| OrderID | OrderDate | ShipDate | NextOrderDate | NextShipDate | DaysToNextShip |
|---|---|---|---|---|---|
| 1 | 2023-01-01 | 2023-01-03 | 2023-01-02 | 2023-01-05 | 2 |
| 2 | 2023-01-02 | 2023-01-05 | 2023-01-03 | 2023-01-06 | 1 |
| 3 | 2023-01-03 | 2023-01-06 | NULL | NULL | NULL |
解释：
LEAD(OrderDate, 1, NULL) OVER (ORDER BY OrderDate): 获取下一个订单的下单日期。LEAD(ShipDate, 1, NULL) OVER (ORDER BY OrderDate): 获取下一个订单的发货日期。DATEDIFF(day, ShipDate, LEAD(ShipDate, 1, NULL) OVER (ORDER BY OrderDate)): 计算当前订单发货日期到下一个订单发货日期的天数差。
示例2：查找连续事件的开始和结束时间
假设我们有一个名为EventLog的表，记录了事件的开始和结束时间：
| EventID | StartTime | EndTime |
|---|---|---|
| 1 | 09:00 | 10:00 |
| 2 | 10:00 | 11:00 |
| 3 | 12:00 | 13:00 |
| 4 | 13:00 | 14:00 |
以下查询使用LEAD()函数查找连续事件的开始和结束时间：
SELECT
EventID,
StartTime,
EndTime,
CASE
WHEN EndTime = LEAD(StartTime, 1, NULL) OVER (ORDER BY StartTime) THEN 'Continuous'
ELSE 'Non-Continuous'
END AS EventStatus
FROM
EventLog;查询结果如下：
| EventID | StartTime | EndTime | EventStatus |
|---|---|---|---|
| 1 | 09:00 | 10:00 | Continuous |
| 2 | 10:00 | 11:00 | Non-Continuous |
| 3 | 12:00 | 13:00 | Continuous |
| 4 | 13:00 | 14:00 | Non-Continuous |
解释：
CASE WHEN EndTime = LEAD(StartTime, 1, NULL) OVER (ORDER BY StartTime) THEN 'Continuous' ELSE 'Non-Continuous' END: 如果当前事件的结束时间等于下一个事件的开始时间，则表示事件是连续的。
四、LAG()和LEAD()的结合使用
LAG()和LEAD()函数可以结合使用，以访问当前行之前和之后的行的数据。 这在需要比较相邻两行或多行数据时非常有用。
示例：计算一段时间内的最大波动幅度
假设我们有一个名为StockPrices的表，记录了股票的每日价格：
| Date | Price |
|---|---|
| 2023-01-01 | 100 |
| 2023-01-02 | 105 |
| 2023-01-03 | 95 |
| 2023-01-04 | 110 |
| 2023-01-05 | 100 |
以下查询使用LAG()和LEAD()函数计算一段时间内的最大波动幅度：
SELECT
Date,
Price,
ABS(Price - LAG(Price, 1, Price) OVER (ORDER BY Date)) AS PriceChangeFromPrevious,
ABS(Price - LEAD(Price, 1, Price) OVER (ORDER BY Date)) AS PriceChangeToNext
FROM
StockPrices;查询结果如下：
| Date | Price | PriceChangeFromPrevious | PriceChangeToNext |
|---|---|---|---|
| 2023-01-01 | 100 | 0 | 5 |
| 2023-01-02 | 105 | 5 | 10 |
| 2023-01-03 | 95 | 10 | 15 |
| 2023-01-04 | 110 | 15 | 10 |
| 2023-01-05 | 100 | 10 | 0 |
解释：
ABS(Price - LAG(Price, 1, Price) OVER (ORDER BY Date)): 计算当前价格与前一天价格的差的绝对值。ABS(Price - LEAD(Price, 1, Price) OVER (ORDER BY Date)): 计算当前价格与后一天价格的差的绝对值。
在实际应用中，你可以根据具体需求选择合适的窗口函数和参数，灵活地进行行间数据比较。
五、注意事项
ORDER BY子句在OVER()子句中是强制性的，除非你使用的是聚合窗口函数（如SUM() OVER ()，此时窗口是整个结果集）。 这是因为LAG()和LEAD()需要知道行的顺序才能确定前一行和后一行。PARTITION BY子句是可选的，用于将结果集划分为多个分区。 如果省略，则整个结果集被视为一个分区。offset参数指定要访问的行相对于当前行的偏移量。 默认值为1。default_value参数指定如果偏移量超出了结果集的范围，则返回的默认值。 如果未指定，则返回NULL。LAG()和LEAD()函数在不同的数据库系统中的实现可能略有不同。 请参考你所使用的数据库系统的文档。- 窗口函数不能在
WHERE子句中使用。 你可以使用子查询或公共表表达式 (CTE) 来解决这个问题。
六、实际应用场景
- 数据分析： 计算同比、环比增长率，移动平均值等。
- 时间序列分析： 查找事件序列中的模式，例如趋势、季节性等。
- 财务分析： 计算股票收益率、风险指标等。
- 业务分析： 分析用户行为，例如用户流失、用户转化等。
- 日志分析： 查找错误日志中的异常模式。
- 数据清洗： 识别和纠正数据中的错误，例如重复数据、缺失数据等。
七、使用限制与替代方案
虽然 LAG() 和 LEAD() 函数非常强大，但也存在一些限制：
- 性能： 对于大型数据集，窗口函数的性能可能不如其他方法，例如自连接。
- 复杂性： 复杂的窗口函数表达式可能会难以理解和维护。
在某些情况下，可以使用以下替代方案：
- 自连接： 可以使用自连接来访问相邻行的数据。 但是，自连接通常比窗口函数更复杂且性能更差。
- 游标： 可以使用游标来逐行处理结果集。 但是，游标的性能通常很差，应尽量避免使用。
- 编程语言： 可以使用编程语言（如 Python）来处理数据并计算所需的值。
窗口函数提供了一种简洁而强大的方式来在SQL中执行行间比较，在大多数情况下，是比自连接更清晰和高效的解决方案。
总结：在行间数据对比中LAG()和LEAD()函数是利器
LAG()和LEAD()函数是SQL中用于访问结果集中前一行和后一行数据的窗口函数。 它们在计算差异、同比、环比增长率、查找连续事件等方面非常有用，能够简化复杂的查询逻辑。