Athena SQL LEFT JOIN逻辑不符合预期，需调整匹配顺序

我需要从包含多时段的表中创建查询，根据charge的创建时间关联数据。每个ID对应多个时段，当前的LEFT JOIN逻辑不符合预期：我希望先遍历所有同ID的记录，优先匹配charge.created_at落在时段period_from和period_to之间的记录，如果没有匹配到，再依次检查其他条件，但当前语句会在第一个条件不满足时直接跳转其他条件，导致结果错误（比如第一行AX列应为2023-01-25、AY列应为2023-02-24，实际结果不符）。

当前使用的SQL语句：

left join measures.subscription_periods_v2 
  on case when ProdPostgres.payment.charge.type = 'RENTAL_FEE'
          then ProdPostgres.payment.charge.id = measures.subscription_periods_v2.charge_id
     else
        ProdPostgres.payment.booking.external_booking_id = measures.subscription_periods_v2.bookingid and ( (cast(ProdPostgres.payment.charge.created_at as date) >= cast(subscription_periods_v2.period_from as date)  and cast(ProdPostgres.payment.charge.created_at as date) <= cast(subscription_periods_v2.period_to as date))
                                                                                                                or    cast(ProdPostgres.payment.charge.created_at as date) >= cast(subscription_periods_v2.period_to as date)

                                                                                                                or cast(ProdPostgres.payment.charge.created_at as date) <= cast(subscription_periods_v2.period_from as date)   )

调整方案 #

要实现优先级匹配，不能直接在JOIN的ON条件里用OR（OR会同时匹配所有符合任一条件的记录，无法保证优先级），可以用以下两种方式解决：

方法1：多轮LEFT JOIN + COALESCE #

分多次LEFT JOIN，先匹配最高优先级的条件，再用后续JOIN补全未匹配的记录，最后用COALESCE优先取高优先级的结果：

SELECT 
    -- 优先取高优先级匹配的时段，无匹配则取低优先级
    COALESCE(sp_high.period_from, sp_low.period_from) AS AX,
    COALESCE(sp_high.period_to, sp_low.period_to) AS AY,
    -- 其他需要查询的字段...
FROM ProdPostgres.payment.charge c
LEFT JOIN ProdPostgres.payment.booking b 
    ON c.booking_id = b.id -- 补充你的booking关联条件
-- 高优先级：匹配charge创建时间落在时段范围内的记录
LEFT JOIN measures.subscription_periods_v2 sp_high
    ON (c.type = 'RENTAL_FEE' AND c.id = sp_high.charge_id)
    OR (c.type != 'RENTAL_FEE'
        AND b.external_booking_id = sp_high.bookingid
        AND CAST(c.created_at AS DATE) BETWEEN CAST(sp_high.period_from AS DATE) AND CAST(sp_high.period_to AS DATE))
-- 低优先级：匹配其他时间条件的记录（仅高优先级无匹配时生效）
LEFT JOIN measures.subscription_periods_v2 sp_low
    ON (c.type = 'RENTAL_FEE' AND c.id = sp_low.charge_id)
    OR (c.type != 'RENTAL_FEE'
        AND b.external_booking_id = sp_low.bookingid
        AND (CAST(c.created_at AS DATE) >= CAST(sp_low.period_to AS DATE)
             OR CAST(c.created_at AS DATE) <= CAST(sp_low.period_from AS DATE)))
    AND sp_high.bookingid IS NULL -- 确保高优先级未匹配才触发

方法2：窗口函数排序优先级 #

先关联所有可能的记录，再用ROW_NUMBER()窗口函数按优先级排序，取每个ID的最高优先级记录：

WITH joined_data AS (
    SELECT 
        c.*,
        b.*,
        sp.period_from AS AX,
        sp.period_to AS AY,
        -- 定义优先级：时间落在时段内为1（最高），其他为2
        ROW_NUMBER() OVER (
            PARTITION BY 
                CASE WHEN c.type = 'RENTAL_FEE' THEN c.id ELSE b.external_booking_id END
            ORDER BY 
                CASE 
                    WHEN CAST(c.created_at AS DATE) BETWEEN CAST(sp.period_from AS DATE) AND CAST(sp.period_to AS DATE) THEN 1
                    ELSE 2 
                END ASC
        ) AS rn
    FROM ProdPostgres.payment.charge c
    LEFT JOIN ProdPostgres.payment.booking b 
        ON c.booking_id = b.id -- 补充你的booking关联条件
    LEFT JOIN measures.subscription_periods_v2 sp
        ON (c.type = 'RENTAL_FEE' AND c.id = sp.charge_id)
        OR (c.type != 'RENTAL_FEE' AND b.external_booking_id = sp.bookingid)
)
SELECT *
FROM joined_data
WHERE rn = 1 -- 仅保留每个ID的最高优先级匹配记录

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