Snowflake中基于动态Offset实现Quantity列偏移生成Current_quantity

Snowflake的LAG()函数要求偏移量必须是常量，不能直接传入列值，所以要实现动态偏移得换思路。结合你提到的Offset范围是0-8（很小的固定范围），这里提供两种实用方案：

方案一：CASE枚举固定偏移值 #

直接枚举所有可能的Offset值，用CASE语句匹配对应的LAG()结果，简单高效。

假设你的主表包含日期列sale_date、数量列Quantity，关联后得到动态偏移列offset_col，代码示例：

SELECT
    sale_date,
    Quantity,
    offset_col,
    CASE offset_col
        WHEN 0 THEN Quantity
        WHEN 1 THEN LAG(Quantity, 1) OVER (ORDER BY sale_date)
        WHEN 2 THEN LAG(Quantity, 2) OVER (ORDER BY sale_date)
        WHEN 3 THEN LAG(Quantity, 3) OVER (ORDER BY sale_date)
        WHEN 4 THEN LAG(Quantity, 4) OVER (ORDER BY sale_date)
        WHEN 5 THEN LAG(Quantity, 5) OVER (ORDER BY sale_date)
        WHEN 6 THEN LAG(Quantity, 6) OVER (ORDER BY sale_date)
        WHEN 7 THEN LAG(Quantity, 7) OVER (ORDER BY sale_date)
        WHEN 8 THEN LAG(Quantity, 8) OVER (ORDER BY sale_date)
    END AS Current_quantity
FROM your_main_table
-- 这里加入关联获取offset_col的逻辑，比如：
-- JOIN your_offset_table ON your_main_table.id = your_offset_table.id
ORDER BY sale_date;

• 优点：代码直观，执行性能好，适合Offset范围固定且较小的场景。
• 缺点：如果后续Offset范围扩大，需要手动补充CASE分支。

方案二：ARRAY_AGG动态取数 #

通过数组收集窗口内的历史数据，再根据行号和Offset计算索引取值，更灵活，适合Offset范围可能变化的场景。

WITH ranked_data AS (
    SELECT
        sale_date,
        Quantity,
        offset_col,
        -- 按日期生成连续行号
        ROW_NUMBER() OVER (ORDER BY sale_date) AS row_num
    FROM your_main_table
    -- 关联offset表的逻辑放在这里
)
SELECT
    sale_date,
    Quantity,
    offset_col,
    -- 收集从第一行到当前行的所有Quantity，按行号升序排列
    -- 用行号减去Offset得到目标索引（Snowflake数组是1-based）
    ARRAY_AGG(Quantity) OVER (
        ORDER BY row_num
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    )[row_num - offset_col] AS Current_quantity
FROM ranked_data
ORDER BY sale_date;

• 逻辑说明：先给每行分配行号，再把历史Quantity存入数组，通过row_num - offset_col定位到需要的偏移值。比如Offset=2时，当前行row_num=3，取数组第1位（3-2=1），也就是2024011的Quantity，符合你的示例需求。
• 边界处理：当row_num - offset_col < 1时（比如前几行没有足够的前置数据），数组索引越界会返回NULL，和LAG()函数的默认行为一致。

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