如何在Snowflake中基于最新日期筛选匹配Box_no的唯一行

原查询存在的问题 #

1. LEFT JOIN 被转为 INNER JOIN：原查询在LEFT JOIN后添加了WHERE Outbound_Cases.Store LIKE '%15%' AND Outbound_Cases.SYS_State = '50'，这会过滤掉所有Outbound_Cases不满足条件的行，同时也会剔除Packed_Cases中无匹配Outbound记录的行，实际效果等同于INNER JOIN。
2. 分组维度导致重复：GROUP BY中包含了Outbound_Cases.Store和Outbound_Cases.SYS_State，如果同一个Box_no对应多条符合条件的Outbound记录（不同Store或SYS_State组合），会被拆分为多个分组，最终输出重复行。
3. MAX_BY未实现去重：MAX_BY(Outbound_Cases.Date_Modified,Outbound_Cases.Date_Modified)仅能获取当前分组的最大日期，但由于分组维度包含Store和SYS_State，无法实现按Box_no去重的目标。

解决方案：两种高效写法 #

方法1：使用QUALIFY窗口函数（推荐）

直接在Outbound_Cases中筛选出每个Box_no的最新记录，再与Packed_Cases关联，逻辑清晰且性能更优：

SELECT 
    pc.Box_no,
    pc.Item_Style,
    pc.Qty,
    oc.Box_no AS Outbound_Box_no,
    oc.Store,
    oc.Date_Modified,
    oc.SYS_State
FROM 
    Packed_Cases pc
LEFT JOIN (
    SELECT 
        Box_no,
        Store,
        Date_Modified,
        SYS_State
    FROM 
        Outbound_Cases
    WHERE 
        Store LIKE '%15%' 
        AND SYS_State = '50'
    QUALIFY ROW_NUMBER() OVER (PARTITION BY Box_no ORDER BY Date_Modified DESC) = 1
) oc ON pc.Box_no = oc.Box_no

逻辑说明：

• 子查询通过QUALIFY ROW_NUMBER() OVER (PARTITION BY Box_no ORDER BY Date_Modified DESC) = 1，对每个Box_no的记录按Date_Modified降序排序，仅保留最新的第一条记录。
• 主查询用LEFT JOIN确保Packed_Cases中无匹配Outbound记录的行也能保留（若不需要可改为INNER JOIN）。

方法2：先聚合最新日期再回表关联

若习惯用聚合函数思路，可先获取每个Box_no的最新日期，再关联获取对应字段：

SELECT 
    pc.Box_no,
    pc.Item_Style,
    pc.Qty,
    oc.Box_no AS Outbound_Box_no,
    oc.Store,
    oc.Date_Modified,
    oc.SYS_State
FROM 
    Packed_Cases pc
LEFT JOIN (
    SELECT 
        oc1.Box_no,
        oc1.Store,
        oc1.Date_Modified,
        oc1.SYS_State
    FROM 
        Outbound_Cases oc1
    INNER JOIN (
        SELECT 
            Box_no,
            MAX(Date_Modified) AS Latest_Date
        FROM 
            Outbound_Cases
        WHERE 
            Store LIKE '%15%' 
            AND SYS_State = '50'
        GROUP BY Box_no
    ) oc2 ON oc1.Box_no = oc2.Box_no AND oc1.Date_Modified = oc2.Latest_Date
) oc ON pc.Box_no = oc.Box_no

逻辑说明：

• 最内层子查询oc2按Box_no分组，获取每个Box_no的最新Date_Modified。
• 中间层子查询oc1通过Box_no和Date_Modified关联oc2，拿到每个Box_no最新日期对应的Store和SYS_State。
• 最后与Packed_Cases关联得到最终结果。

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