百万级表关联查询异常：需筛选指定Profile_ID及日期范围数据

表结构与数据 #

Table A（交易表） #

Store_id, Purchase_dt, Amount
--------  -----------  ------
1001       02JAN19     12.20
1001       05MAY20     13.30
1002       07JUL21     10.97

Table B（门店配置表） #

Store_id, Valid_from, Valid_to, Profile_ID
--------  ----------  --------  ----------
1001      01JAN17     08JUL19    56
1001      09JUL19     12DEC99    60
1002      01JAN20     12DEC99    70

查询需求 #

通过Store_id关联两张表，筛选出Purchase_dt处于Valid_from与Valid_to之间，且Profile_ID为60或70的交易数据，预期结果如下：

Store_id, Purchase_dt, Amount, Profile_ID
--------  -----------  ------
1001       05MAY20     13.30    60
1002       07JUL21     10.97    70

尝试的SQL语句 #

Select
a.Store_id,
a.Purchase_dt,
a.Amount,
b.Profile_ID
from
table_a a,
table_b b
where
a.Store_id = b.Store_id
and
a.Purchase_dt between b.Valid_from and b.Valid_to
and 
b.Profile_ID in (60,70)

问题排查与解决 #

核心原因：日期解析错误 #

12DEC99会被数据库解析为1999年12月12日，而非代表“永久有效”的未来日期。这就导致Store_id=1001的2020年5月5日交易、Store_id=1002的2021年7月7日交易，都因Purchase_dt晚于Valid_to（1999年），无法匹配到目标Profile_ID的记录。

解决方案 #

方案1：更新永久有效日期为数据库最大支持日期

先修正Table B中代表“永久有效”的日期值，以Oracle为例：

UPDATE table_b 
SET Valid_to = DATE '9999-12-31' 
WHERE Valid_to = DATE '1999-12-12';

其他数据库可替换为对应最大日期，比如MySQL用'9999-12-31'，SQL Server用'9999-12-31'。之后执行原查询即可得到预期结果。

方案2：兼容旧日期值的查询语句

如果无法修改表数据，可在查询中添加兼容逻辑：

SELECT
    a.Store_id,
    a.Purchase_dt,
    a.Amount,
    b.Profile_ID
FROM table_a a
JOIN table_b b ON a.Store_id = b.Store_id
WHERE
    a.Purchase_dt >= b.Valid_from
    AND (a.Purchase_dt <= b.Valid_to OR b.Valid_to = DATE '1999-12-12')
    AND b.Profile_ID IN (60, 70)

百万级数据性能优化 #

由于两张表数据量较大，建议：

• 改用显式JOIN替代逗号连接表的写法（如上述优化后的查询）
• 创建复合索引提升关联效率：
  • Table A：CREATE INDEX idx_a_store_purchase ON table_a(Store_id, Purchase_dt);
  • Table B：CREATE INDEX idx_b_store_profile_date ON table_b(Store_id, Profile_ID, Valid_from, Valid_to);

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