Snowflake窗口函数条件匹配问题：关联表取指定日期前最新值

需求：从关联的第二张表中获取单个字段，匹配两个ID后，筛选出日期不晚于主表对应记录日期的结果，并取最新的一条。

当前编写的SQL：

Select distinct primary_id,
       first_value(desired_column) over(partition by id_1, id_2 order by date desc)
From base_table
Left join second_table 
on second_table.id_1 = base_table.id_1 and 
   second_table.date <= base_table.date

问题：返回行数和主表一致，但desired_column值完全错误，怀疑窗口函数未正确处理date <=条件。

补充示例 #

主表（Base Table）

第二表（Second Table）

预期输出

原SQL的核心问题：

1. partition by id_1, id_2未关联主表主键，导致同一ID组合下的所有主表记录共享窗口结果，无法对应到每条主表的日期条件。
2. distinct的使用逻辑错误，窗口函数计算后去重无法保证每条主表记录匹配正确结果。

方法一：提前筛选关联表最新记录后关联 #

SELECT 
    bt.primary_key,
    bt.id1,
    bt.id2,
    bt.date,
    st.desired_column
FROM base_table bt
LEFT JOIN (
    SELECT 
        id1,
        id2,
        desired_column,
        date,
        -- 按ID组合分组，给记录按日期降序排号
        ROW_NUMBER() OVER (PARTITION BY id1, id2 ORDER BY date DESC) AS rn
    FROM second_table
) st ON st.id1 = bt.id1 
    AND st.id2 = bt.id2 
    AND st.date <= bt.date
    AND st.rn = 1;

方法二：基于主表主键的窗口函数计算 #

SELECT 
    primary_key,
    id1,
    id2,
    date,
    -- 针对每条主表记录，筛选符合日期条件的最新字段值
    FIRST_VALUE(st.desired_column) OVER (
        PARTITION BY bt.primary_key 
        ORDER BY st.date DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS desired_column
FROM base_table bt
LEFT JOIN second_table st 
    ON st.id1 = bt.id1 
    AND st.id2 = bt.id2 
    AND st.date <= bt.date
QUALIFY ROW_NUMBER() OVER (PARTITION BY bt.primary_key ORDER BY (SELECT NULL)) = 1;

说明 #

• 方法一先在子查询中过滤出每个id1+id2组合的最新记录，再与主表关联时加上日期限制，性能更优，因为提前减少了关联数据量。
• 方法二则直接按主表主键分区，确保每条主表记录单独计算，再用QUALIFY保留唯一结果，逻辑更直观。

两种方法均可得到符合预期的输出。

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