Snowflake中基于关联键获取最近日期租金的SQL实现需求

需求概述 #

在Snowflake中通过左连接关联t1和t2两张表，保留t1的全部数据，为t1的每条记录匹配t2中日期小于等于当前记录reporting_week的最近日期对应的租金，若t2中无匹配数据则租金显示0.00。

示例表结构 #

with t1 as (
    select '02/26/2024' as reporting_week, '1361164' as propertyid
    union 
    select '02/19/2024' as reporting_week, '1361164' as propertyid
    union 
    select '02/11/2024' as reporting_week, '1361164' as propertyid
    union 
    select '02/09/2024' as reporting_week, '1361164' as propertyid
    union 
    select '02/09/2024' as reporting_week, '1369999' as propertyid
),
t2 as (
select '02/25/2024' as date, '1361164' as id, 999 as rent
union 
select '02/19/2024' as date, '1361164' as id, 888 as rent
union 
select '02/10/2024' as date, '1361164' as id, 777 as rent
)

期望输出 #

问题分析 #

你提供的尝试SQL存在以下问题：

• 字段名不匹配：t1中实际字段是propertyid和reporting_week，但代码中误用了pid和date
• 多余的t2b关联：不需要额外关联t2取最大日期，逻辑冗余
• 最终使用join而非left join，会过滤掉t1中无匹配的记录（如propertyid=1369999的行）

正确实现方案 #

方案一：使用窗口函数ROW_NUMBER()（推荐） #

通过窗口函数为每个t1记录匹配的t2数据按日期倒序排序，取第一条（最近日期）的数据：

with t1 as (
    select '02/26/2024' as reporting_week, '1361164' as propertyid
    union 
    select '02/19/2024' as reporting_week, '1361164' as propertyid
    union 
    select '02/11/2024' as reporting_week, '1361164' as propertyid
    union 
    select '02/09/2024' as reporting_week, '1361164' as propertyid
    union 
    select '02/09/2024' as reporting_week, '1369999' as propertyid
),
t2 as (
select '02/25/2024' as date, '1361164' as id, 999 as rent
union 
select '02/19/2024' as date, '1361164' as id, 888 as rent
union 
select '02/10/2024' as date, '1361164' as id, 777 as rent
),
ranked_rents as (
    select 
        t1.propertyid,
        t1.reporting_week,
        t2.rent,
        -- 按propertyid和reporting_week分组，对匹配的t2日期倒序排名
        row_number() over (
            partition by t1.propertyid, t1.reporting_week 
            order by t2.date desc
        ) as rn
    from t1
    left join t2 
        on t2.id = t1.propertyid 
        and t2.date <= t1.reporting_week
)
select 
    propertyid,
    reporting_week,
    -- 无匹配时显示0.00，否则取排名第一的租金
    coalesce(rent, 0.00) as rent
from ranked_rents
where rn = 1 -- 只保留最近日期的租金记录
order by reporting_week desc, propertyid;

方案二：使用MAX()关联匹配最近日期 #

先为每个t1记录找到对应的最近t2日期，再关联t2获取租金：

with t1 as (
    select '02/26/2024' as reporting_week, '1361164' as propertyid
    union 
    select '02/19/2024' as reporting_week, '1361164' as propertyid
    union 
    select '02/11/2024' as reporting_week, '1361164' as propertyid
    union 
    select '02/09/2024' as reporting_week, '1361164' as propertyid
    union 
    select '02/09/2024' as reporting_week, '1369999' as propertyid
),
t2 as (
select '02/25/2024' as date, '1361164' as id, 999 as rent
union 
select '02/19/2024' as date, '1361164' as id, 888 as rent
union 
select '02/10/2024' as date, '1361164' as id, 777 as rent
),
latest_dates as (
    select 
        t1.propertyid,
        t1.reporting_week,
        max(t2.date) as latest_rent_date
    from t1
    left join t2 
        on t2.id = t1.propertyid 
        and t2.date <= t1.reporting_week
    group by t1.propertyid, t1.reporting_week
)
select 
    ld.propertyid,
    ld.reporting_week,
    coalesce(t2.rent, 0.00) as rent
from latest_dates ld
left join t2 
    on t2.id = ld.propertyid 
    and t2.date = ld.latest_rent_date
order by ld.reporting_week desc, ld.propertyid;

说明 #

两种方案都能实现需求：

• 方案一更直观，适合处理复杂的匹配逻辑，数据量较大时性能更稳定
• 方案二逻辑简单，适合理解基础关联逻辑的场景

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