使用SUM(CASE WHEN)查询伦敦犯罪数据返回异常值的技术求助

核心问题：字符串精确匹配失败 #

你遇到的数值不符（比如Brent行政区的Criminal Damage to Motor Vehicle显示0），大概率是因为CASE语句里的字符串和minor_category字段的实际值不精确匹配——可能存在大小写差异、额外空格、拼写细微偏差（比如英式/美式拼写）。

第一步：验证目标分类的实际名称 #

先运行以下查询，确认Brent行政区实际存在的minor_category值及对应总数：

SELECT DISTINCT minor_category, SUM(value) AS total
FROM `bigquery-public-data.london_crime.crime_by_lsoa`
WHERE borough = 'Brent'
GROUP BY minor_category
ORDER BY total DESC;

执行后你能看到该行政区所有犯罪类型的精确名称，对比你写的'Criminal Damage to Motor Vehicle'是否一致。

第二步：修正原查询 #

方案1：统一大小写匹配

如果是大小写问题，用LOWER()函数统一转换后再匹配，避免大小写敏感问题：

SELECT
  borough,
  SUM(CASE WHEN LOWER(minor_category) = 'rape' THEN value ELSE 0 END) AS Rape_Total,
  SUM(CASE WHEN LOWER(minor_category) = 'criminal damage to motor vehicle' THEN value ELSE 0 END) AS Criminal_Damage_Total
FROM
  `bigquery-public-data.london_crime.crime_by_lsoa`
GROUP BY
  borough;

方案2：使用精确分类名替换

直接用第一步查询到的精确minor_category值替换CASE语句里的字符串，确保完全匹配。

更简洁的行转列方案：使用PIVOT语法 #

BigQuery支持PIVOT语法，可以更直观地实现行转列，同时减少字符串匹配出错的概率：

SELECT *
FROM `bigquery-public-data.london_crime.crime_by_lsoa`
PIVOT (
  SUM(value) AS total
  FOR minor_category IN ('Rape', 'Criminal Damage to Motor Vehicle') -- 替换为第一步查到的精确名称
)
ORDER BY borough;

内容的提问来源于stack exchange，提问作者Barney De Jongh