在Snowflake SQL中获取交易日期前最近的客户状态值

需求概述 #

有两张表：

• 状态表（STATUS_TABLE）：存储客户指定日期的状态信息
• 交易表（TRAN_TABLE）：存储客户交易历史

需要为每条交易记录，获取该交易日期之前状态表中最近INSERT_DATE对应的客户STATUS。

测试数据 #

状态表（STATUS_TABLE） #

交易表（TRAN_TABLE） #

期望结果 #

解决方案 #

方法1：使用LATERAL JOIN（推荐） #

针对每条交易记录单独查询最近状态，性能更高效：

SELECT
    t.ACCT,
    t.TRAN_DATE,
    s.STATUS
FROM
    TRAN_TABLE t
LEFT JOIN LATERAL (
    SELECT STATUS
    FROM STATUS_TABLE s
    WHERE s.ACCT = t.ACCT
      AND s.INSERT_DATE <= t.TRAN_DATE
    ORDER BY s.INSERT_DATE DESC
    LIMIT 1
) s ON TRUE
ORDER BY t.TRAN_DATE DESC;

方法2：使用窗口函数ROW_NUMBER() #

通过关联后排序过滤实现：

WITH joined_data AS (
    SELECT
        t.ACCT,
        t.TRAN_DATE,
        s.STATUS,
        ROW_NUMBER() OVER (
            PARTITION BY t.ACCT, t.TRAN_DATE
            ORDER BY s.INSERT_DATE DESC
        ) AS rn
    FROM TRAN_TABLE t
    LEFT JOIN STATUS_TABLE s
        ON s.ACCT = t.ACCT
        AND s.INSERT_DATE <= t.TRAN_DATE
)
SELECT
    ACCT,
    TRAN_DATE,
    STATUS
FROM joined_data
WHERE rn = 1
ORDER BY TRAN_DATE DESC;

说明 #

两种方法均可得到期望结果：

• LATERAL JOIN更适合大数据量场景，避免不必要的关联计算
• 窗口函数逻辑更直观，便于理解关联排序的过程

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