Redshift条件关联咨询：基于不同键实现表连接的最优方案

核心思路是不修改t2表结构，直接在JOIN的关联条件中根据t1的id动态调整匹配的year_month值，同时通过合理利用索引避免t2大表的全表扫描，保证查询性能。

以下分主流数据库给出具体实现：

1. MySQL 实现 #

利用DATE_SUB和DATE_FORMAT函数将t1的year_month转换为上月格式，再与t2的原始year_month匹配：

SELECT t1.id, t1.year_month, t2.price
FROM t1
JOIN t2
  ON t1.id = t2.id
  AND (
    (t1.id != 'a' AND t1.year_month = t2.year_month)
    OR
    (t1.id = 'a' AND t2.year_month = DATE_FORMAT(DATE_SUB(STR_TO_DATE(t1.year_month, '%Y-%m'), INTERVAL 1 MONTH), '%Y-%m'))
  )

性能优化 #

• 给t2创建联合索引：CREATE INDEX idx_t2_id_yearmonth ON t2(id, year_month);
• 该索引能让数据库快速定位匹配id和year_month的行，避免全表扫描。

2. PostgreSQL 实现 #

通过to_date和to_char函数处理日期转换：

SELECT t1.id, t1.year_month, t2.price
FROM t1
JOIN t2
  ON t1.id = t2.id
  AND (
    (t1.id != 'a' AND t1.year_month = t2.year_month)
    OR
    (t1.id = 'a' AND t2.year_month = to_char(to_date(t1.year_month, 'YYYY-MM') - INTERVAL '1 month', 'YYYY-MM'))
  )

性能优化 #

• 给t2创建联合索引：CREATE INDEX idx_t2_id_yearmonth ON t2(id, year_month);
• PostgreSQL会自动利用该索引加速关联匹配。

3. SQL Server 实现 #

使用DATEADD和FORMAT函数完成日期调整：

SELECT t1.id, t1.year_month, t2.price
FROM t1
JOIN t2
  ON t1.id = t2.id
  AND (
    (t1.id != 'a' AND t1.year_month = t2.year_month)
    OR
    (t1.id = 'a' AND t2.year_month = FORMAT(DATEADD(MONTH, -1, CONVERT(DATE, t1.year_month + '-01')), 'yyyy-MM'))
  )

性能优化 #

• 给t2创建非聚集索引：CREATE NONCLUSTERED INDEX idx_t2_id_yearmonth ON t2(id, year_month);

进阶优化方案（适用于t1中id='a'数据量较大的场景） #

将查询拆分为两个独立子查询再通过UNION ALL合并，能让每个子查询更高效地利用索引：

-- 处理非'a'的常规关联
SELECT t1.id, t1.year_month, t2.price
FROM t1
JOIN t2 ON t1.id = t2.id AND t1.year_month = t2.year_month
WHERE t1.id != 'a'

UNION ALL

-- 处理'id=a'的上月关联（替换为对应数据库的日期转换逻辑）
SELECT t1.id, t1.year_month, t2.price
FROM t1
JOIN t2 ON t1.id = t2.id AND t2.year_month = DATE_FORMAT(DATE_SUB(STR_TO_DATE(t1.year_month, '%Y-%m'), INTERVAL 1 MONTH), '%Y-%m')
WHERE t1.id = 'a'

这种拆分方式避免了OR条件可能带来的索引失效风险，整体性能更稳定。

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