如何排查BigQuery中简单SQL查询返回0结果的问题

问题背景 #

SQL初学者执行BigQuery查询后返回0条结果，核对bigquery-public-data.austin_crime.crime数据集时，确认存在数百条description字段值为"THEFT BY SHOPLIFTING"的记录，但查询无匹配结果。需求为按zipcode维度提取所有入店行窃相关犯罪数据，用于Tableau地图可视化制作。

原查询语句 #

SELECT
  *
FROM bigquery-public-data.austin_crime.crime
WHERE year = 2016
  AND description = "THEFT BY SHOPLIFTING"
  AND zipcode IS NOT NULL
ORDER BY
  zipcode
LIMIT 10000;

问题根因 #

该数据集description字段的目标值存在不可见前后尾随空白字符（空格、换行符等），肉眼查看字段值和目标字符串完全一致，但直接做等值匹配时，带空白字符的字段值和输入的常量字符串长度不一致，无法命中匹配。

排查验证方法 #

先执行以下语句确认字段实际值情况，统计不同格式的目标记录数量：

SELECT
  description,
  LENGTH(description) AS value_length,
  COUNT(*) AS record_cnt
FROM bigquery-public-data.austin_crime.crime
WHERE UPPER(TRIM(description)) = "THEFT BY SHOPLIFTING"
GROUP BY description, value_length

执行后可以看到，命中的记录value_length会大于"THEFT BY SHOPLIFTING"本身的长度（20），证明存在多余空白字符。

修正后可用查询 #

如果需要拉取明细数据，用以下语句即可返回正确结果：

SELECT
  *
FROM bigquery-public-data.austin_crime.crime
WHERE year = 2016
  AND UPPER(TRIM(description)) = "THEFT BY SHOPLIFTING"
  AND zipcode IS NOT NULL
ORDER BY zipcode
LIMIT 10000;

如果是用于Tableau做填充地图/点地图，不需要拉取全量明细，直接按zipcode聚合统计犯罪数量即可，查询性能更高，导入Tableau后直接将zipcode设置为邮编地理角色就能直接生成可视化：

SELECT
  zipcode,
  COUNT(*) AS shoplifting_crime_total
FROM bigquery-public-data.austin_crime.crime
WHERE year = 2016
  AND UPPER(TRIM(description)) = "THEFT BY SHOPLIFTING"
  AND zipcode IS NOT NULL
GROUP BY zipcode
ORDER BY zipcode

语句说明 #

• TRIM(description)：去除字段值前后所有类型的空白字符，解决肉眼可见值一致但匹配失败的问题
• UPPER()：兜底处理可能存在的大小写不一致问题，避免漏匹配大小写格式异常的记录

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