在 SQLite 中，LAG() 是一个窗口函数，它允许你从当前行的前面某一行中获取指定列的值。这个函数在进行数据对比、计算差值或者分析数据趋势时非常有用。下面详细解释其使用方法并给出示例。

基本语法

LAG() 函数有三种形式：

1. LAG(expr)
2. LAG(expr, offset)
3. LAG(expr, offset, default)

参数解释如下：

• expr：需要计算的表达式，通常是列名，用于从指定行中获取值。
• offset（可选）：表示当前行往前偏移的行数，必须是一个非负整数。默认值为 1，即获取前一行的值。
• default（可选）：当偏移后的行不存在时（比如当前行是第一行，往前偏移就没有行了），返回的默认值。如果不指定，默认返回 NULL。

使用示例

示例表结构和数据

假设我们有一个名为 sales 的表，记录了不同月份的销售金额，表结构和示例数据如下：

-- 创建表
CREATE TABLE sales (
    month TEXT,
    amount REAL
);

-- 插入示例数据
INSERT INTO sales (month, amount) VALUES ('Jan', 1000);
INSERT INTO sales (month, amount) VALUES ('Feb', 1200);
INSERT INTO sales (month, amount) VALUES ('Mar', 1500);
INSERT INTO sales (month, amount) VALUES ('Apr', 1300);

1. 使用LAG(expr)

这种形式是最基本的，offset 默认值为 1，default 默认值为 NULL。以下查询将获取每个月的销售金额以及前一个月的销售金额：

SELECT 
    month,
    amount,
    LAG(amount) OVER (ORDER BY month) AS prev_month_amount
FROM 
    sales;

结果解释：

2. 使用LAG(expr, offset)

指定 offset 可以获取当前行往前指定偏移量的行的值。以下查询获取每个月的销售金额以及前两个月的销售金额（offset 为 2）：

SELECT 
    month,
    amount,
    LAG(amount, 2) OVER (ORDER BY month) AS two_months_ago_amount
FROM 
    sales;

结果解释：

3. 使用LAG(expr, offset, default)

当偏移后的行不存在时，指定 default 可以返回一个自定义的值，而不是 NULL。以下查询将在没有前两个月销售金额时返回 0：

SELECT 
    month,
    amount,
    LAG(amount, 2, 0) OVER (ORDER BY month) AS two_months_ago_amount
FROM 
    sales;

结果解释：

结合PARTITION BY使用

如果数据有分组的需求，可以使用 PARTITION BY 子句。例如，假设 sales 表新增了一个 region 列，我们要计算每个地区内每个月相对于前一个月的销售金额：

-- 修改表结构并插入新数据
ALTER TABLE sales ADD COLUMN region TEXT;
UPDATE sales SET region = 'East' WHERE month IN ('Jan', 'Feb');
UPDATE sales SET region = 'West' WHERE month IN ('Mar', 'Apr');

-- 查询每个地区内前一个月的销售金额
SELECT 
    region,
    month,
    amount,
    LAG(amount) OVER (PARTITION BY region ORDER BY month) AS prev_month_amount
FROM 
    sales;

这样，LAG() 函数会在每个地区的分区内独立计算前一个月的销售金额。

注意事项

• offset 必须是非负整数，否则会导致错误。
• PARTITION BY 子句可以将数据划分成不同的组，LAG() 函数会在每个组内独立进行偏移操作。
• ORDER BY 子句对于 LAG() 函数很重要，它决定了行的顺序，从而影响偏移的结果。