在Snowflake SQL中如何从同表获取上一个YEARWEEK？已尝试Rank和Lag未成功

核心解决方案 #

方法1：直接用LAG函数（按日期排序） #

如果日期维度表中每个日期对应唯一记录，只需按DATE字段排序后使用LAG函数，避免直接按YWEEK排序（跨年度时字符串排序会出错）：

SELECT
    DATE_DIM,
    DATE,
    YWEEK,
    LAG(YWEEK) OVER (ORDER BY DATE) AS PREV_YEARWEEK
FROM
    你的日期维度表名;

方法2：通过日期计算生成上一个YEARWEEK（更可靠） #

针对跨年度场景（比如2023年最后一周之后是2024年第一周），用DATEADD计算上一周的日期，再提取ISO标准的YWEEK：

SELECT
    DATE_DIM,
    DATE,
    YWEEK,
    TO_CHAR(DATEADD(WEEK, -1, DATE), 'IYYYIW') AS PREV_YEARWEEK
FROM
    你的日期维度表名;

• IYYY：ISO标准年份
• IW：ISO标准周数

方法3：处理同一YWEEK多条记录的场景 #

如果同一个YWEEK对应多个日期记录，希望这些记录返回同一个上一个YWEEK，可以先对YWEEK去重排序，再关联回原表：

WITH 去重排序的YWEEK AS (
    SELECT DISTINCT
        YWEEK,
        DATE,
        LAG(YWEEK) OVER (ORDER BY DATE) AS PREV_YEARWEEK
    FROM
        你的日期维度表名
)
SELECT
    d.DATE_DIM,
    d.DATE,
    d.YWEEK,
    ys.PREV_YEARWEEK
FROM
    你的日期维度表名 d
JOIN
    去重排序的YWEEK ys ON d.YWEEK = ys.YWEEK
ORDER BY
    d.DATE;

常见错误原因 #

• 直接按YWEEK字符串排序：跨年度时（如202352和202401），字符串排序会导致顺序错乱，LAG返回错误结果。
• LAG函数未指定正确的ORDER BY：窗口排序逻辑错误，无法取到时间上的前一个YWEEK。
• 不必要的PARTITION BY：误加分隔窗口的分区字段，导致无法获取全局的上一个YWEEK。

示例效果 #

假设原表数据：

使用方法2的输出：

内容的提问来源于stack exchange，提问作者K.Tom