数据库存在缺失年份时如何正确使用lag函数生成滞后变量

该方案写法简洁、性能开销低，仅适用于单期滞后的计算场景：核心逻辑是先通过LAG取上一行的年份，判断当前行与上一行的年份差是否等于1，差值符合要求时才返回上一行的数值，否则返回NULL。
示例代码如下：

SELECT 
    id, -- 分组维度，比如不同主体的唯一标识，无分组可删除PARTITION BY部分
    year,
    value,
    CASE 
        WHEN year - LAG(year, 1) OVER (PARTITION BY id ORDER BY year) = 1 
        THEN LAG(value, 1) OVER (PARTITION BY id ORDER BY year) 
        ELSE NULL 
    END AS lag_1y_value
FROM your_table

该方案适合多期滞后计算、或数据中年份缺失较多的场景：先生成覆盖所有时间范围的连续年份序列，补全缺失年份的空值行后，直接用原生LAG函数即可得到符合要求的滞后值。
示例代码如下：

WITH full_years AS (
    -- 生成连续年份序列，不同数据库可替换为更简化的序列生成语法
    SELECT 2011 + t4.num * 1000 + t3.num * 100 + t2.num * 10 + t1.num AS year
    FROM (SELECT 0 num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t1,
         (SELECT 0 num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t2,
         (SELECT 0 num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t3,
         (SELECT 0 num UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t4
),
id_year_grid AS (
    -- 生成所有分组维度与年份的笛卡尔积，补全可能缺失的组合
    SELECT DISTINCT t.id, f.year
    FROM your_table t
    CROSS JOIN full_years f
    WHERE f.year BETWEEN (SELECT MIN(year) FROM your_table) AND (SELECT MAX(year) FROM your_table)
),
full_data AS (
    -- 左连接原始数据，缺失年份的value自动补为NULL
    SELECT g.id, g.year, t.value
    FROM id_year_grid g
    LEFT JOIN your_table t ON g.id = t.id AND g.year = t.year
)
-- 直接调用原生LAG即可得到符合要求的滞后变量
SELECT 
    id,
    year,
    value,
    LAG(value, 1) OVER (PARTITION BY id ORDER BY year) AS lag_1y_value
FROM full_data
WHERE value IS NOT NULL -- 不需要展示缺失年份行可加该过滤条件

• 不同数据库的连续序列生成可使用更简化的语法：PostgreSQL可直接调用generate_series函数，MySQL 8.0+可使用递归CTE生成序列，无需写多层UNION ALL的笛卡尔积。
• 若需要按季度、月度等更低频率计算滞后，将年份差判断替换为对应时间维度的差值判断即可。

内容的提问来源于stack exchange，提问作者son vu van