Postgres 8.x(Redshift)动态子查询关联两表最近日期数据方案咨询

你提供的原有方案不可行，核心原因如下：

1. 你使用的Redshift基于Postgres 8.x内核，本身不支持lateral join语法，这是查询无法正常运行的直接原因
2. 关联条件a.pid = b.pid存在列名拼写错误，tableA的对应列名为p_id而非pid
3. 就算语法可运行，1700万行的tableA逐行去1.5亿行的tableB做匹配，计算量达到万亿级，必然会超时无法产出结果，且top 1的写法只会返回一条最近日期的行，无法满足你示例中多个相同最近日期行都要返回的需求。

推荐使用Redshift原生优化过的窗口函数实现，性能远高于逐行匹配方案，同时可满足并列最近日期行全部返回的需求：

WITH joined_data AS (
    SELECT
        a.p_id,
        a.category,
        a.l_date,
        b.e_id,
        b.e_date,
        -- 按tableA的唯一行分组，按日期间隔从小到大排序
        RANK() OVER (
            PARTITION BY a.p_id, a.category, a.l_date
            ORDER BY ABS(DATEDIFF(day, a.l_date, b.e_date)) ASC
        ) AS date_rank
    FROM tableA a
    LEFT JOIN tableB b ON a.p_id = b.p_id
)
SELECT p_id, category, l_date, e_id, e_date
FROM joined_data
WHERE date_rank = 1;

• 分布键调整：将tableA和tableB的分布键都设置为p_id，让相同p_id的所有数据都落在同一个计算节点，避免关联时跨节点的数据shuffle，性能可提升数倍。
• 排序键调整：将tableB的排序键设置为(p_id, e_date)，tableA的排序键设置为(p_id, l_date)，可进一步加速关联和窗口排序的计算效率。
• 数据预裁剪：如果业务上有可限定的时间范围，可先过滤掉tableB中e_date不在tableA的l_date合理范围内的无效数据，减少参与计算的总数据量。
• 关联类型调整：如果不需要保留无匹配tableB行的tableA数据，可将LEFT JOIN改为INNER JOIN，进一步降低计算量。

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