在AWS Redshift中通过最近日期关联交易事实表与周期快照表

方法一：使用LATERAL JOIN（推荐，逻辑清晰） #

Redshift支持LATERAL JOIN，可以针对交易表的每一行，精准筛选出对应客户最近的前置快照记录，不会丢失任何交易数据。SQL示例如下：

SELECT
    t.cust_ID,
    t.order_date,
    t.product,
    s.favourite_colour
FROM
    transactions t
LEFT JOIN LATERAL (
    SELECT
        favourite_colour
    FROM
        customer_snapshots s
    WHERE
        s.cust_ID = t.cust_ID
        AND s.snapshot_date <= t.order_date
    ORDER BY
        s.snapshot_date DESC
    LIMIT 1
) s ON TRUE;

逻辑说明：对每笔交易，找到该客户所有快照日期早于等于交易日期的记录，按快照日期倒序取第一条（即最近的有效快照），完美匹配每笔交易的属性需求。

方法二：窗口函数修正版（解决你之前的问题） #

你之前用row_number()只保留每个客户一条记录，核心问题是窗口分区粒度不对。正确做法是按每笔交易的唯一标识分区，如果交易表没有单独的交易ID，可通过cust_ID + order_date + product作为分区键（假设这三个字段能唯一标识一笔交易），再对分区内的快照按日期倒序排序，取第一条。

SQL示例：

WITH joined_data AS (
    SELECT
        t.cust_ID,
        t.order_date,
        t.product,
        s.favourite_colour,
        s.snapshot_date,
        ROW_NUMBER() OVER (
            PARTITION BY t.cust_ID, t.order_date, t.product  -- 按每笔交易独立分区
            ORDER BY s.snapshot_date DESC
        ) AS rn
    FROM
        transactions t
    LEFT JOIN customer_snapshots s
        ON s.cust_ID = t.cust_ID
        AND s.snapshot_date <= t.order_date
)
SELECT
    cust_ID,
    order_date,
    product,
    favourite_colour
FROM
    joined_data
WHERE
    rn = 1;

额外说明 #

• 如果需要过滤掉“交易日期前无快照记录”的客户，把LEFT JOIN改成INNER JOIN即可；若要保留这类交易，favourite_colour会返回NULL，符合业务逻辑。
• 确保snapshot_date和order_date是相同的日期类型（如DATE或TIMESTAMP），避免类型不匹配导致的关联错误。

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