使用LEFT JOIN与ROW_NUMBER OVER PARTITION实现日志关联最新动作的SQL问题

原查询的核心错误 #

• 关联条件完全错误：原脚本中CAST(lg.logdate AS INTEGER) = act.orderID把日志日期转成整数和订单ID关联，逻辑完全不符合需求，正确的关联应该是两张表的orderID相等。
• 最终关联缺失日志日期匹配：子查询关联时只匹配orderID，导致每个订单仅返回一个全局最新动作，而非对应日志行日期的最近动作。

修正后的SQL脚本 #

WITH latest_actions AS (
    SELECT 
        lg.orderID,
        lg.logdate,
        act.actiondate AS last_action_date,
        act.actionname AS action_performed,
        -- 按「订单ID+日志日期」分区，取该日期及之前的最新动作
        ROW_NUMBER() OVER (
            PARTITION BY lg.orderID, lg.logdate 
            ORDER BY act.actiondate DESC, act.actionname DESC -- 同日期下取排序靠后的动作（如action3优先于action2.5）
        ) AS ranked
    FROM 
        Logs lg
    LEFT JOIN 
        Actions act 
        ON lg.orderID = act.orderID 
        AND act.actiondate <= lg.logdate -- 仅关联日志日期及之前的动作
)
SELECT 
    lg.orderID,
    lg.logdate,
    lg.orderdate,
    lg.perimeter,
    lg.ordertype,
    la.last_action_date,
    la.action_performed
FROM 
    Logs lg
LEFT JOIN (
    SELECT 
        orderID, 
        logdate, 
        last_action_date, 
        action_performed
    FROM latest_actions
    WHERE ranked = 1
) la 
ON lg.orderID = la.orderID AND lg.logdate = la.logdate -- 同时匹配订单ID和日志日期，确保对应关系正确
ORDER BY 
    lg.orderID, lg.logdate

修正说明 #

1. 修正表关联逻辑：通过lg.orderID = act.orderID关联两张表的订单维度，同时将act.actiondate <= lg.logdate作为关联条件，过滤出当前日志日期及之前的动作。
2. 窗口函数分区精准：保持PARTITION BY lg.orderID, lg.logdate，确保每个日志行独立计算其对应的最近动作。
3. 最终关联补全日志日期：关联子查询时同时匹配orderID和logdate，避免不同日期的动作混配。
4. 同日期多动作处理：增加act.actionname DESC排序，确保同一日期下取预期的动作（如示例中的action3、action23）。

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