Snowflake中BETWEEN关联邮编返回多条结果的问题排查与解决

在Snowflake中通过邮编关联两张表：一张表存储单个邮编，另一张表存储邮编区间（ZIP_CODE_FROM到ZIP_CODE_TO）及对应时效（LEADTIME），邮编包含纯数字和字母数字组合。相同的关联SQL在SQL Server中返回预期的1条结果，但Snowflake返回多条不符合预期的记录。

原SQL语句：

SELECT DISTINCT RR.SHIP_TO_ZIP, LT.ZIP_CODE_FROM, LT.ZIP_CODE_TO, LT.LEADTIME 
FROM
CARR RR LEFT JOIN AGREEMENTS  LT 
WHERE RR.COUNTRY = LT.COUNTRY 
AND RR.SHIP_TO_ZIP BETWEEN LT.ZIP_CODE_FROM AND LT.ZIP_CODE_TO 

源数据：
CARR表

AGREEMENTS表

预期结果仅包含40121-40141对应的记录，但Snowflake实际返回三条记录。尝试过将纯数字邮编转为数值类型的临时方案，但效率较低。

核心差异是字符串类型的BETWEEN比较逻辑不同：

• SQL Server对纯数字组成的字符串执行BETWEEN时，会自动按数值大小比较；
• Snowflake则严格遵循字符串字典序比较，即逐字符按ASCII码值对比：
  • '40136'作为字符串，首字符4的ASCII码大于'10'的首字符1，同时小于'79'的首字符7，因此被判定在'10'-'79'区间内；
  • 同理，'40136'的前三位401与'4010'的前三位一致，第四位3大于0，且'40136'的第三位1小于'4029'的第三位2，因此也被判定在'4010'-'4029'区间内；
  • 最终三条区间都满足字符串字典序的BETWEEN条件，所以返回三条记录。

由于邮编包含字母数字组合，无法全部转为数值类型，因此需要统一字符串长度（补前导零），让字符串字典序与实际的邮编数值序一致：

方案1：固定长度补零（已知最大邮编长度） #

假设所有邮编的最大长度为5位，用LPAD函数补前导零后再做区间比较：

SELECT DISTINCT RR.SHIP_TO_ZIP, LT.ZIP_CODE_FROM, LT.ZIP_CODE_TO, LT.LEADTIME 
FROM CARR RR 
JOIN AGREEMENTS LT 
  ON RR.COUNTRY = LT.COUNTRY 
  AND LPAD(RR.SHIP_TO_ZIP, 5, '0') BETWEEN LPAD(LT.ZIP_CODE_FROM, 5, '0') AND LPAD(LT.ZIP_CODE_TO, 5, '0')

（注：原SQL的LEFT JOIN加WHERE条件等价于JOIN，改为JOIN可提升查询效率）

方案2：动态获取最大长度（适配未知长度的场景） #

如果不确定邮编的最大长度，可先查询所有邮编的最大长度再补零：

WITH max_zip_length AS (
  SELECT GREATEST(
    MAX(LENGTH(SHIP_TO_ZIP)),
    MAX(LENGTH(ZIP_CODE_FROM)),
    MAX(LENGTH(ZIP_CODE_TO))
  ) AS max_len
  FROM CARR, AGREEMENTS
)
SELECT DISTINCT RR.SHIP_TO_ZIP, LT.ZIP_CODE_FROM, LT.ZIP_CODE_TO, LT.LEADTIME 
FROM CARR RR 
JOIN AGREEMENTS LT 
  ON RR.COUNTRY = LT.COUNTRY 
CROSS JOIN max_zip_length m
WHERE LPAD(RR.SHIP_TO_ZIP, m.max_len, '0') BETWEEN LPAD(LT.ZIP_CODE_FROM, m.max_len, '0') AND LPAD(LT.ZIP_CODE_TO, m.max_len, '0')

性能优化建议 #

• 给AGREEMENTS表创建组合索引：CREATE INDEX idx_agreements_zip ON AGREEMENTS(COUNTRY, LPAD(ZIP_CODE_FROM, 5, '0'), LPAD(ZIP_CODE_TO, 5, '0'));（根据实际长度调整）
• 若查询频繁，可在AGREEMENTS表中新增计算列存储补零后的区间值，避免每次查询重复计算：

  ALTER TABLE AGREEMENTS ADD COLUMN ZIP_FROM_PADDED STRING AS LPAD(ZIP_CODE_FROM, 5, '0') STORED;
  ALTER TABLE AGREEMENTS ADD COLUMN ZIP_TO_PADDED STRING AS LPAD(ZIP_CODE_TO, 5, '0') STORED;
  

  之后查询直接使用计算列即可。

内容的提问来源于stack exchange，提问作者Rui Sá-Dias