Snowflake中基于日期条件关联SQL表：无匹配取最早有效地址

完全可以用纯SQL实现你要的逻辑，核心是通过窗口函数给地址按优先级排序，优先取匹配查询日期的地址，无匹配则取用户最早的地址。

原始表结构 #

Table A（Name字段唯一） #

Table B（End为NULL表示当前有效） #

实现SQL #

用ROW_NUMBER()窗口函数给每个用户的地址按优先级排序，筛选出排序第一的记录即可：

WITH ranked_addresses AS (
    SELECT 
        b.*,
        ROW_NUMBER() OVER (
            PARTITION BY b.Name 
            ORDER BY 
                -- 优先级1：匹配查询日期的地址排前面
                CASE WHEN b.Start <= '2023-01-31' AND (b.End IS NULL OR b.End >= '2023-01-31') THEN 0 ELSE 1 END,
                -- 优先级2：无匹配时取最早的地址（按Start升序）
                b.Start ASC
        ) AS rn
    FROM TableB b
)
SELECT 
    a.Name,
    a.Color,
    ra.Address,
    ra.Start,
    ra.End
FROM TableA a
LEFT JOIN ranked_addresses ra 
    ON a.Name = ra.Name 
    AND ra.rn = 1;

逻辑说明 #

1. CTE排序阶段：给每个用户的地址计算排序号rn：
   • 先判断地址是否匹配目标日期，匹配的标记为0，不匹配的为1，确保匹配地址排在最前
   • 无匹配的地址按Start升序排列，取最早的那条
2. 主查询阶段：关联TableA和排序后的地址表，只保留每个用户排序为1的地址，就是最终需要的结果

日期替换 #

要查询其他日期时，直接修改CTE里的'2023-01-31'为目标日期即可，比如换成'2023-09-30'就能得到对应的示例结果。

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