如何在Snowflake中用SQL实现多日期范围的优先级连接值查找？

示例数据 #

费率表（Rate table） #

销售表（Sales table） #

预期结果表（Result table） #

匹配规则 #

1. 优先级1：若销售日期落在多个费率的日期范围内，选择Start日期最晚的费率
2. 优先级2：若销售日期不在任何费率范围内，选择Start日期早于销售日期且最接近的费率
3. 优先级3：若以上两种情况都不满足，仅按Country和Code匹配任意一个费率（此处取分组内Start最早的费率保证确定性）

现有实现SQL #

select 
    country, code, date, 
    coalesce(b.rate, b1.rate, b2.rate, b3.rate) rate 
from 
    sales_table a
left join 
    rate_table b on a.country = b.country 
                 and a.code = b.code
                 and a.date between b.start and b.end 
                 and b.start = (select max(r.start) from rate_table r 
                                where a.country = r.country 
                                  and a.code = r.code 
                                  and a.date between r.start and r.end)
left join 
    rate_table b1 on a.country = b1.country 
                  and a.code = b1.code
                  and a.date >= b1.start 
                  and b1.start = (select max(start) from rate_table r1 
                                  where a.country = r1.country 
                                    and a.code = r1.code 
                                    and a.date >= r1.start)
                  and b.start is null
left join 
    rate_table b2 on a.country = b2.country 
                  and a.code = b2.code
                  and a.date <= b2.start 
                  and b2.start = (select min(start) from rate_table r2 
                                  where a.country = r2.country 
                                    and a.code = r2.code 
                                    and a.date <= r2.start)
                  and b.start is null 
                  and b1.start is null
left join 
    rate_table b3 on a.country = b3.country 
                  and a.code = b3.code
                  and b.start is null 
                  and b1.start is null 
                  and b2.start is null;

可以利用窗口函数ROW_NUMBER()一次性关联所有可能的费率规则，通过排序逻辑映射优先级，直接取排名第一的结果。这种方式避免了多次子查询和左连接，性能更优且代码更简洁。

WITH ranked_rates AS (
    SELECT
        s.country,
        s.code,
        s.date,
        r.rate,
        ROW_NUMBER() OVER (
            PARTITION BY s.country, s.code, s.date
            ORDER BY
                -- 优先级1：在日期范围内的记录排最前，且start越晚越优先
                CASE WHEN s.date BETWEEN r.start AND r.end THEN 1 ELSE 2 END,
                -- 优先级2：不在范围内时，先取start早于销售日期且最近的，再取start晚于销售日期且最早的
                CASE 
                    WHEN s.date BETWEEN r.start AND r.end THEN r.start DESC
                    WHEN s.date > r.start THEN r.start DESC
                    ELSE r.start ASC
                END
        ) AS rn
    FROM sales_table s
    LEFT JOIN rate_table r
        ON s.country = r.country AND s.code = r.code
)
SELECT
    country,
    code,
    date,
    rate
FROM ranked_rates
WHERE rn = 1;

优化说明 #

1. 逻辑紧凑：通过CASE表达式直接将规则转化为排序权重，一次关联覆盖所有匹配场景
2. 性能提升：减少了多次表扫描和子查询计算，数据量越大，性能优势越明显
3. 结果确定：优先级3场景下默认取Country+Code分组中Start最早的费率，如需调整可修改ORDER BY的最后排序条件

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