SQL LAG函数

原创

mb65b505fb9403f 2024-01-27 21:36:19 博主文章分类：SQL ©著作权

文章标签 数据默认值结果集 SQL 文章分类 MySQL 数据库

©著作权归作者所有：来自51CTO博客作者mb65b505fb9403f的原创作品，请联系作者获取转载授权，否则将追究法律责任

简介

在SQL中，LAG函数是一种用于访问前一行数据的窗口函数。它允许我们在查询结果集中获取前一行数据的值，以便进行比较、计算或显示。LAG函数在处理时间序列数据、分析趋势和计算增长率等方面非常有用。

SQL LAG函数_数据

语法

下面是LAG函数的基本语法：

其中：

column：需要访问前一行数据的列名。
offset：表示我们希望访问前多少行的数据。默认为1，即前一行。
default：表示当无法访问前一行数据时，返回的默认值。默认为NULL。
PARTITION BY：可以将结果集分区为多个子集，然后为每个子集应用LAG函数。
ORDER BY：用于指定结果集的排序方式。

示例

下面通过一些示例来详细说明LAG函数的使用。

假设我们有一张名为orders的表，记录了顾客购买商品的订单信息，包括顾客姓名、订单日期和销售额。我们希望计算每个顾客的上一个订单的销售额。

CREATE TABLE orders (
    customer_id int,
    order_date date,
    amount decimal(10, 2)
);

INSERT INTO orders (customer_id, order_date, amount) VALUES
(1, '2022-01-01', 100.00),
(1, '2022-02-01', 150.00),
(2, '2022-01-01', 200.00),
(2, '2022-03-01', 250.00),
(3, '2022-02-01', 300.00);

我们可以使用LAG函数来实现这个需求：

SELECT
    customer_id,
    order_date,
    amount,
    LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS previous_amount
FROM
    orders
ORDER BY
    customer_id,
    order_date;

运行结果如下：

customer_id	order_date	amount	previous_amount
1	2022-01-01	100.00
1	2022-02-01	150.00	100.00
2	2022-01-01	200.00
2	2022-03-01	250.00	200.00
3	2022-02-01	300.00

可以看到，对于每个顾客，上一个订单的销售额被正确地填写在了previous_amount列中。

除了访问单个前一行的数据，我们还可以通过调整offset参数来访问更多的前一行数据。例如，如果我们想同时获取前两个订单的销售额，可以将offset参数设置为2。

SELECT
    customer_id,
    order_date,
    amount,
    LAG(amount, 1) OVER (PARTITION BY customer_id ORDER BY order_date) AS previous_amount1,
    LAG(amount, 2) OVER (PARTITION BY customer_id ORDER BY order_date) AS previous_amount2
FROM
    orders
ORDER BY
    customer_id,
    order_date;

运行结果如下：

customer_id	order_date	amount	previous_amount1	previous_amount2
1	2022-01-01	100.00
1	2022-02-01	150.00	100.00
2	2022-01-01	200.00
2	2022-03-01	250.00	200.00
3	2022-02-01	300.00

可以看到，对于每个顾客，我们都能正确地获取前两个订单的销售额。

在某些情况下，前一行数据可能不可用，例如对于每个分区的第一行。我们可以通过指定默认值来处理这种情况。例如，我们将默认值设置为0。

SELECT
    customer_id,
    order_date,
    amount,
    LAG(amount, 1, 0) OVER (PARTITION BY customer_id ORDER BY order_date) AS previous_amount
FROM
    orders
ORDER BY
    customer_id,
    order_date;

运行结果如下：

customer_id	order_date	amount	previous_amount
1	2022-01-01	100.00	0.00
1	2022-02-01	150.00	100.00
2	2022-01-01	200.00	0.00
2	2022-03-01	250.00	200.00
3	2022-02-01	300.00	0.00