Snowflake SQL中处理汇率缺失的货币转换实现方案问询

我有两张简化后的表：

Revenue表：存储以USD计价的收入 #

Xrate表：存储USD转EUR的汇率 #

将两张表关联、过滤并分组后得到如下结果集：

但部分时间戳可能不存在对应的汇率，关联后该行的xrate会为NULL。
我需要计算转换为EUR的收入，逻辑如下：

• 仅当所有xrate均不为NULL时，使用revenue*xrate计算；
• 若存在任何xrate为NULL/NA，则所有行的adjusted_xrate设为1，再通过revenue * adjusted_xrate计算收入。

请问能否在Snowflake SQL中实现该逻辑？我尝试过MAX、COUNT等聚合函数，但未找到合适的实现方式。

可以通过窗口函数判断全局是否存在NULL的xrate，再结合条件逻辑实现需求，示例SQL如下：

WITH joined_data AS (
    -- 完成两张表的左关联，保留所有Revenue数据
    SELECT 
        r.Timestamp,
        r.id,
        r.revenue,
        x.xrate
    FROM Revenue r
    LEFT JOIN Xrate x ON r.Timestamp = x.Timestamp
    -- 此处可添加你的过滤条件
),
global_check AS (
    -- 通过窗口函数判断全局是否存在汇率缺失
    SELECT 
        *,
        CASE 
            WHEN COUNT_IF(xrate IS NULL) OVER () > 0 THEN 1 
            ELSE xrate 
        END AS adjusted_xrate
    FROM joined_data
)
-- 计算最终的欧元计价收入
SELECT 
    Timestamp,
    id,
    revenue,
    xrate,
    adjusted_xrate,
    revenue * adjusted_xrate AS eur_revenue
FROM global_check;

逻辑说明： #

1. joined_data CTE：执行左关联操作，确保所有Revenue记录都被保留，匹配不到对应汇率的行xrate字段为NULL；
2. global_check CTE：使用COUNT_IF(xrate IS NULL) OVER ()统计整个结果集中xrate为NULL的行数。如果统计结果大于0，说明存在汇率缺失，将adjusted_xrate设为1；否则直接使用原xrate值；
3. 最终计算转换后的欧元收入eur_revenue。

如果需要按特定维度（比如按日期分组）判断汇率是否缺失，只需在窗口函数的OVER()子句中添加分区条件即可，例如OVER (PARTITION BY DATE(Timestamp))，这样会按日期分组检查每组内的汇率完整性。

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