Redshift中匹配订单至对应患者最近问诊的SQL查询问题

你的报错原因很明确：派生表子查询无法访问外层orders表的别名o，因为子查询的作用域是独立的，看不到外层的表引用。下面提供两种适合Redshift的可行解法：

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

LATERAL JOIN允许子查询引用外层表的字段，正好匹配你的需求——为每个订单找到对应患者的最近一次问诊：

SELECT
    o.patient_id,
    o.order_id,
    o.order_datetime,
    c.consultation_id
FROM
    orders o
LEFT JOIN LATERAL (
    SELECT consultation_id
    FROM consultations c
    WHERE c.patient_id = o.patient_id
      AND c.consultation_date <= o.order_datetime
    ORDER BY c.consultation_date DESC
    LIMIT 1
) c ON TRUE
ORDER BY o.patient_id, o.order_datetime;

这个查询会为每个订单，从对应患者的问诊记录中筛选出日期早于等于订单日期的记录，再取最新的那一条。

方法二：使用窗口函数+QUALIFY子句 #

利用Redshift支持的QUALIFY子句，结合窗口函数筛选每个订单的最近问诊：

WITH ranked_consultations AS (
    SELECT
        patient_id,
        consultation_id,
        consultation_date
    FROM consultations
)
SELECT
    o.patient_id,
    o.order_id,
    o.order_datetime,
    rc.consultation_id
FROM orders o
JOIN ranked_consultations rc
    ON rc.patient_id = o.patient_id
    AND rc.consultation_date <= o.order_datetime
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY o.patient_id, o.order_id 
    ORDER BY rc.consultation_date DESC
) = 1
ORDER BY o.patient_id, o.order_datetime;

这里先关联订单和符合日期条件的问诊，再通过QUALIFY为每个订单保留问诊日期最晚的那一条记录。

两种方法都能得到你期望的输出结果，可根据实际数据量选择更高效的方案。

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