在写报表类SQL时，经常遇到这样的需求：拿当前行的数据和上一行或下一行做比较，比如计算环比增长率、找出数据中的跳变点。传统做法是用自连接，写起来繁琐，性能也不理想。SQLite从3.25版本开始支持窗口函数，其中的LEAD和LAG专门用来解决这个问题，让查询语句直接访问结果集中当前行前后相邻的数据。这篇文章把这两个函数的用法、参数细节和实际应用场景讲清楚。
LEAD与LAG的基本语法和参数含义
LAG函数用来访问当前行之前的行，也就是往“上”看；LEAD函数用来访问当前行之后的行，也就是往“下”看。两个函数的语法结构完全一致，都接受最多三个参数。第一个参数是要取值的列或表达式，第二个参数是偏移量，表示往前或往后看几行，第三个参数是当目标行不存在时返回的默认值。
LAG(表达式, 偏移量, 默认值) OVER (PARTITION BY 分组列 ORDER BY 排序列) LEAD(表达式, 偏移量, 默认值) OVER (PARTITION BY 分组列 ORDER BY 排序列)
偏移量和默认值都可以省略。省略偏移量时默认为1，也就是取紧邻的上一行或下一行；省略默认值时，如果目标行超出了分区边界，函数返回NULL。OVER子句是窗口函数的必备部分，其中的ORDER BY决定了行的排列顺序，直接影响到“前后行”的定义。需要特别注意的是，窗口函数里的ORDER BY和查询最外层的ORDER BY是两回事，前者只决定窗口内计算的顺序，不会改变最终结果集的输出顺序。
下面用一个简单的销售数据表演示基本用法。假设有一张记录各月销售额的表monthly_sales：
CREATE TABLE monthly_sales (
id INTEGER PRIMARY KEY,
month TEXT,
amount INTEGER
);
INSERT INTO monthly_sales (month, amount) VALUES
('2024-01', 1200),
('2024-02', 1500),
('2024-03', 1350),
('2024-04', 1800),
('2024-05', 2100);
SELECT
month,
amount,
LAG(amount) OVER (ORDER BY month) AS prev_amount,
LEAD(amount) OVER (ORDER BY month) AS next_amount
FROM monthly_sales;查询结果中，第一行的prev_amount是NULL，因为2024-01前面没有数据；最后一行的next_amount是NULL，因为2024-05后面没有数据。中间每一行都同时拿到了前一个月和后一个月的销售额。如果希望NULL显示成别的值，比如0，可以直接写LAG(amount, 1, 0)，这样边界行就会返回0而不是NULL。
实际应用场景：环比计算与数据跳变检测
LEAD和LAG最典型的用途就是计算环比。所谓环比，就是当前周期和上一个周期的对比。利用LAG拿到上一期的数值，再用四则运算就能算出增长率，整个过程只需要一次表扫描。
SELECT
month,
amount,
LAG(amount, 1, 0) OVER (ORDER BY month) AS prev_amount,
ROUND(
(amount - LAG(amount, 1, 0) OVER (ORDER BY month)) * 100.0
/ NULLIF(LAG(amount, 1, 0) OVER (ORDER BY month), 0),
2
) AS growth_rate
FROM monthly_sales;这段SQL里有一个细节值得注意：除法之前用NULLIF把分母为0的情况转成NULL，避免除零错误。另外增长率计算中用了100.0而不是100，这是因为在SQLite中整数除法会截断小数部分，写成浮点数才能得到正确的小数结果。
除了环比，LEAD和LAG还能用来检测数据中的异常跳变。比如监控系统中记录的传感器读数，如果相邻两条记录之间的差值超过某个阈值，就说明可能出现了异常。这时可以在HAVING或外层WHERE里对窗口函数的结果做过滤。由于窗口函数不能直接出现在WHERE子句中，标准写法是把窗口查询包在子查询里，然后在外层过滤：
SELECT *
FROM (
SELECT
month,
amount,
LAG(amount) OVER (ORDER BY month) AS prev_amount
FROM monthly_sales
)
WHERE ABS(amount - prev_amount) > 200;这个模式在实际开发中出现频率非常高，任何“比较相邻行再筛选”的需求都可以套用。类似地，用LEAD可以判断当前行是不是某个区间的最后一行，比如当前行的next值与当前值不同时，说明分组边界就在这里，这在处理连续区间合并问题时特别好用。
PARTITION BY分组与自连接的性能对比
前面的例子都是对全表数据做前后行访问，但实际业务中往往需要在分组内部进行比较。比如一张订单表记录了多个用户的消费记录，要计算每个用户本次消费与上次消费的间隔天数。如果不用PARTITION BY，LAG会跨用户取数据，结果就全错了。加上PARTITION BY user_id之后，窗口函数会在每个用户分组内独立计算，上一个用户的最后一行不会泄漏到下一个用户的第一行。
SELECT
user_id,
order_date,
LAG(order_date) OVER (
PARTITION BY user_id
ORDER BY order_date
) AS prev_order_date,
julianday(order_date) - julianday(
LAG(order_date) OVER (
PARTITION BY user_id
ORDER BY order_date
)
) AS days_since_last_order
FROM orders;这里用到了SQLite的julianday函数把日期转成天数差，从而计算两次消费之间隔了几天。需要强调的是，PARTITION BY和ORDER BY必须配合使用，分组列在前，排序列在后，顺序反了结果就不对。
在没有窗口函数的旧版本SQLite中，同样的需求只能靠自连接实现，比如用相关子查询找每条记录在同组内排它之前的最近一条。这种写法的时间复杂度接近行的平方，数据量到几万行就会明显变慢，而窗口函数内部基于排序实现，复杂度是行数乘以对数，性能高出几个数量级。在10万行的测试数据上，自连接方案可能需要数秒，窗口函数方案通常在几十毫秒内完成。
当然窗口函数也有一些使用上的注意点。它只在查询结果上计算，不能在WHERE和GROUP BY的HAVING里直接引用，必须借助子查询或CTE包一层。另外偏移量参数必须是常量或表达式，不能引用外部列。理解了这两点限制，LEAD和LAG基本可以覆盖绝大多数前后行比较的需求，让SQL从多层嵌套的自连接简化成一行窗口表达式，代码可读性和执行效率都会明显提升。