如何在Netezza中基于最近日期关联两张表？

核心SQL实现 #

WITH ranked_matches AS (
    SELECT
        t2.*,
        t1.color AS closest_color,
        t1.d1 AS closest_d1,
        t1.var1 AS closest_var1,
        ROW_NUMBER() OVER (
            PARTITION BY t2.id, t2.color, t2.d2, t2.var2
            ORDER BY ABS(t1.d1 - t2.d2) ASC
        ) AS rn
    FROM my_table2 t2
    LEFT JOIN my_table1 t1
        ON t2.color = t1.color
)
SELECT
    id,
    color,
    d2,
    var2,
    CASE WHEN rn = 1 THEN closest_color END AS closest_color,
    CASE WHEN rn = 1 THEN closest_d1 END AS closest_d1,
    CASE WHEN rn = 1 THEN closest_var1 END AS closest_var1
FROM ranked_matches
WHERE rn = 1 OR rn IS NULL
ORDER BY id;

关键细节说明 #

• 日期差绝对值计算：Netezza中日期类型直接相减会得到天数差，用ABS()函数即可取绝对值，比如ABS(t1.d1 - t2.d2)就能得到两个日期的间隔天数绝对值。
• 窗口函数分组排序：通过PARTITION BY t2.id, t2.color, t2.d2, t2.var2确保每个my_table2的行单独分组，ORDER BY ABS(t1.d1 - t2.d2) ASC让日期差最小的记录排在最前面，行号rn=1就是我们要找的最接近记录。
• 左连接保证行数一致：使用LEFT JOIN关联my_table1，my_table2中不存在匹配color的行（比如purple）会保留，对应的my_table1字段值为NULL。
• 结果过滤：WHERE rn = 1 OR rn IS NULL确保只保留每个my_table2行对应的最接近记录，或者无匹配的原行，最终结果行数和my_table2完全一致。

补充说明 #

如果my_table2没有唯一标识列（比如示例中的id），PARTITION BY可以替换为my_table2的所有列，确保每个原始行单独分组。如果存在多个日期差相同的最接近记录，ROW_NUMBER()会随机取其中一条，若要保留所有并列记录，可替换为RANK()并调整过滤条件。

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