Snowflake SQL关联无匹配时取历史费用计算报错求助

问题分析 #

原查询报错是因为尝试在CASE中使用Snowflake不支持的子查询类型。需求核心逻辑：

• 当tableb记录匹配到tablea中同key且asofdate处于start_date/end_date区间的记录时，直接取fees/3作为fees_calc
• 无匹配时，取tablea中同key、end_date早于当前asofdate的最新一条记录的fees/3

另外注意：tablea的fees字段是varchar类型，计算前需转为数值类型，否则会出现类型错误。

解决方案 #

通过预筛选tablea的历史记录，结合LEFT JOIN和窗口函数实现需求，避免使用不支持的子查询：

方案1：用CTE预处理历史记录 #

先为每个key的记录按end_date倒序标记，筛选出最新的历史记录，再进行双条件关联：

WITH tablea_processed AS (
    SELECT 
        key,
        fees::FLOAT AS fees_num, -- 转换为数值类型用于计算
        start_date,
        end_date,
        -- 为每个key按end_date倒序编号，最新记录排第1
        ROW_NUMBER() OVER (PARTITION BY key ORDER BY end_date DESC) AS rn
    FROM tablea
)
SELECT 
    b.key,
    b.asofdate,
    -- 优先取区间匹配的fees，无匹配则取最新历史记录的fees
    COALESCE(a_match.fees_num, a_latest.fees_num) AS fees,
    a_match.start_date,
    a_match.end_date,
    -- 计算最终的fees_calc
    COALESCE(a_match.fees_num, a_latest.fees_num)/3 AS fees_calc
FROM tableb b
-- 左连接区间匹配的记录
LEFT JOIN tablea_processed a_match 
    ON b.key = a_match.key 
    AND b.asofdate BETWEEN a_match.start_date AND a_match.end_date
-- 左连接符合条件的最新历史记录
LEFT JOIN tablea_processed a_latest 
    ON b.key = a_latest.key 
    AND a_latest.end_date < b.asofdate
    AND a_latest.rn = 1 -- 只取每个key的最新历史记录

方案2：用QUALIFY简化筛选逻辑 #

利用Snowflake的QUALIFY子句直接筛选出每个key的最新历史记录，代码更简洁：

SELECT 
    b.key,
    b.asofdate,
    COALESCE(a_match.fees::FLOAT, a_latest.fees::FLOAT) AS fees,
    a_match.start_date,
    a_match.end_date,
    COALESCE(a_match.fees::FLOAT, a_latest.fees::FLOAT)/3 AS fees_calc
FROM tableb b
-- 优先匹配区间内的记录
LEFT JOIN tablea a_match
    ON b.key = a_match.key 
    AND b.asofdate BETWEEN a_match.start_date AND a_match.end_date
-- 匹配无区间记录时的最新历史数据
LEFT JOIN (
    SELECT *
    FROM tablea
    QUALIFY ROW_NUMBER() OVER (PARTITION BY key ORDER BY end_date DESC) = 1
) a_latest
    ON b.key = a_latest.key 
    AND a_latest.end_date < b.asofdate

测试结果验证 #

用你提供的测试数据执行后，输出结果如下：

完全符合需求：2022-10-01的123记录无区间匹配，自动取了最新end_date（2022-09-30）的fees值计算结果。

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