Spark SQL实现同比上年同期值：新增valuePriorYear列需求

方法一：自关联查询（通用所有SQL方言） #

通过将表与自身关联，匹配年份减1、月份相同的记录来获取上年同期值，适配所有支持JOIN的SQL环境：

SELECT 
    t1.YearMonth,
    t1.value,
    t2.value AS valuePriorYear
FROM 
    your_table t1
LEFT JOIN 
    your_table t2 ON 
        -- 处理字符串类型的YearMonth：截取年份和月份
        SUBSTRING(t1.YearMonth, 1, 4) = CAST(SUBSTRING(t2.YearMonth, 1, 4) AS INT) + 1
        AND SUBSTRING(t1.YearMonth, 5, 2) = SUBSTRING(t2.YearMonth, 5, 2);

如果YearMonth是数值类型（比如202101为整数），可以用整除和取余替代字符串截取：

SELECT 
    t1.YearMonth,
    t1.value,
    t2.value AS valuePriorYear
FROM 
    your_table t1
LEFT JOIN 
    your_table t2 ON 
        (t1.YearMonth / 100) = (t2.YearMonth / 100) + 1
        AND (t1.YearMonth % 100) = (t2.YearMonth % 100);

使用LEFT JOIN可保留原表所有行，无上年同期数据的行中valuePriorYear会显示NULL。

方法二：窗口函数法（适用于支持窗口函数的SQL） #

之前用LAG只拿到上月值，是因为分区和排序逻辑错误。正确逻辑是按月份分区、按年份排序，每个月份组内LAG(1)即可获取上一年同期值：

SELECT 
    YearMonth,
    value,
    LAG(value) OVER (
        PARTITION BY SUBSTRING(YearMonth, 5, 2)  -- 按月份分区
        ORDER BY SUBSTRING(YearMonth, 1, 4)      -- 按年份升序排序
    ) AS valuePriorYear
FROM 
    your_table;

若YearMonth为数值类型，替换为：

SELECT 
    YearMonth,
    value,
    LAG(value) OVER (
        PARTITION BY YearMonth % 100
        ORDER BY YearMonth / 100
    ) AS valuePriorYear
FROM 
    your_table;

这种方法代码更简洁，执行效率更高，适用于MySQL 8.0+、PostgreSQL、SQL Server等支持窗口函数的数据库。

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