如何在SQL中一次性生成某列的多个Lag（滞后值）

当需要批量生成1到N阶的滞后值时，手动重复编写LAG语句效率极低，以下是几种通用且高效的实现方案：

一、动态SQL生成（跨数据库通用） #

通过动态拼接SQL语句，自动生成指定阶数的所有LAG字段，只需修改最大阶数参数即可适配不同需求。

SQL Server 示例 #

DECLARE @max_lag INT = 20; -- 设置需要生成的最大滞后阶数
DECLARE @sql NVARCHAR(MAX) = 'SELECT A, B';

WHILE @max_lag >= 1
BEGIN
    SET @sql = @sql + ', LAG(A, ' + CAST(@max_lag AS VARCHAR) + ', 0) OVER (PARTITION BY B) AS lag' + CAST(@max_lag AS VARCHAR);
    SET @max_lag = @max_lag - 1;
END

SET @sql = @sql + ' FROM myTable;';
EXEC sp_executesql @sql;

PostgreSQL 示例 #

DO $$
DECLARE
    max_lag INT := 20;
    sql TEXT := 'SELECT A, B';
BEGIN
    FOR i IN 1..max_lag LOOP
        sql := sql || ', LAG(A, ' || i || ', 0) OVER (PARTITION BY B) AS lag' || i;
    END LOOP;
    sql := sql || ' FROM myTable;';
    EXECUTE sql;
END $$;

MySQL 8.0+ 示例 #

SET @max_lag = 20;
SET @sql = 'SELECT A, B';

WHILE @max_lag > 0 DO
    SET @sql = CONCAT(@sql, ', LAG(A, ', @max_lag, ', 0) OVER (PARTITION BY B) AS lag', @max_lag);
    SET @max_lag = @max_lag - 1;
END WHILE;

SET @sql = CONCAT(@sql, ' FROM myTable;');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

二、数据库特定简化方案（以PostgreSQL为例） #

如果允许以数组形式返回所有滞后值，可以利用GENERATE_SERIES和数组函数快速生成，写法更简洁：

SELECT 
    A,
    B,
    ARRAY(
        SELECT LAG(A, i, 0) OVER (PARTITION BY B ORDER BY A) 
        FROM GENERATE_SERIES(1,20) AS s(i)
    ) AS lag_values
FROM myTable;

注：这种方式返回的是包含所有滞后值的数组，若需要拆分为单独字段，仍需结合动态SQL处理。

内容的提问来源于stack exchange，提问作者user59419