如何筛选表指定日期数据，关联另一表并匹配当日最新时间戳记录

问题核心缺陷 #

• 原查询对modem_auth做LEFT JOIN后，将m.partition_region、m.partition_country过滤条件放在WHERE子句中，无匹配modem记录的车辆会被直接过滤，不符合保留所有vehicles表记录的需求
• 关联条件中出现无来源表的save.vin_2属于笔误，实际应为筛选vehicles表的vin
• 同一vin匹配到多条modem_auth有效区间记录时，没有做去重取最新start_time的逻辑，导致同一vin重复返回多条结果

修正后查询语句 #

WITH matched_modem AS (
    SELECT 
        v.vin,
        date_format(v.manufacture_date, 'yyyy-MM-dd') AS event_date,
        "Vehicle Manufactured" AS event_desc,
        m.lifecycle_mode,
        m.auth_status,
        m.start_time,
        m.end_time,
        -- 同VIN的匹配记录按start_time倒序排列，最新的排第一位
        ROW_NUMBER() OVER(PARTITION BY v.vin ORDER BY m.start_time DESC) AS rn
    FROM vehicles v
    INNER JOIN (SELECT vin, MAX(created_on) AS max_created_on FROM vehicles GROUP BY vin) v2 
        ON v.vin = v2.vin AND v.created_on = v2.max_created_on
    LEFT JOIN modem_auth m 
        ON v.vin = m.vin 
        AND date_format(v.manufacture_date, 'yyyy-MM-dd') BETWEEN TO_DATE(m.start_time) AND TO_DATE(m.end_time)
        AND m.partition_region = 'NA' 
        AND m.partition_country = 'USA'
    WHERE v.vin IN ('LMN12345', 'XYZ12345', 'ABC12345')
)
SELECT 
    vin,
    event_date,
    event_desc,
    lifecycle_mode,
    auth_status,
    start_time,
    end_time
FROM matched_modem
WHERE rn = 1

上面的语句将modem_auth的过滤条件移到LEFT JOIN关联逻辑中，无匹配记录的车辆会正常返回、对应modem字段为NULL；通过窗口函数筛选每个VIN匹配到的最新start_time的modem记录，保证每个VIN仅返回一行结果，完全符合预期输出要求。

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