lag函数sql

在SQL语言中，lag函数是一种窗口函数，用于获取表中的上一行数据。它可以帮助我们在查询结果中访问前一行数据的值，并在需要时进行比较、计算或分析。

1. 什么是lag函数

lag函数是一种窗口函数，它可以在查询结果中获取上一行数据的值。它的基本语法如下：

lag(column, [offset], [default]) OVER (order by ...)

• column：要获取上一行数据的列。
• offset：要获取的上一行的偏移量，默认为1，即上一行。
• default：当没有上一行数据时返回的默认值，可选参数。

lag函数常用于分析时间序列数据、计算前后行的差异、查询上一次操作的结果等。

2. 使用lag函数的示例

为了更好地理解lag函数的用法，我们将使用一个示例数据库表orders，该表包含以下列：order_id、order_date、customer_id和amount。

CREATE TABLE orders (
    order_id INT,
    order_date DATE,
    customer_id INT,
    amount DECIMAL(10,2)
);

INSERT INTO orders VALUES
    (1, '2022-01-01', 1, 100.00),
    (2, '2022-02-01', 1, 150.00),
    (3, '2022-02-15', 2, 200.00),
    (4, '2022-03-01', 1, 80.00),
    (5, '2022-04-01', 2, 120.00),
    (6, '2022-05-01', 1, 300.00);

2.1 获取上一行数据

我们可以使用lag函数获取每个订单的上一行数据。例如，我们可以获取每个订单的上一个订单日期和金额。

SELECT
    order_id,
    order_date,
    lag(order_date) OVER (ORDER BY order_date) AS previous_order_date,
    amount,
    lag(amount) OVER (ORDER BY order_date) AS previous_amount
FROM
    orders;

运行上述查询，我们将得到以下结果：

可以看到，lag函数帮助我们获取了每个订单的上一行订单日期和金额。在第一行中，由于没有上一行数据，返回值为null。

2.2 计算前后行的差异

除了获取上一行数据，使用lag函数还可以计算当前行与上一行的差异。例如，我们可以计算每个订单的金额与上一个订单金额的差异。

SELECT
    order_id,
    order_date,
    amount,
    amount - lag(amount) OVER (ORDER BY order_date) AS difference
FROM
    orders;

查询结果如下：

通过计算差异，我们可以更清楚地了解每个订单金额的变化情况。

3. lag函数的更多用法

除了上述用法之外，lag函数还有一些其他的用法：

3.1 指定偏移量

我们可以使用lag函数的第二个参数来指定要获取的上一行的偏移量。例如，我们想获取每个订单的前两个订单金额，可以使用以下查询：

SELECT
    order_id,
    order_date,
    amount,
    lag(amount, 2) OVER (ORDER BY order_date) AS previous_amount_2
FROM
    orders;

查询结果如下：

可以看到，我们获取了每个订单的前两个订单金额。在第一、二行中，由于没有前两个订单，返回值为null。

3.2 指定默认值

当lag函数无法获取上一行数据时，我们可以指定一个默认值作为返回结果。例如，在上一例中，我们可以将缺失的数据设置为0。

SELECT
    order_id,
    order_date,
    amount,
    lag(amount, 1, 0.00) OVER (ORDER BY order_date) AS previous_amount
FROM
    orders;

查询结果如下：

在第一行中，由于没有上一行数据，返回的默认值为0.00。