BigQuery中使用CASE语句创建交通碰撞数据视图问题求助

问题核心原因 #

你排查的结论完全正确：进入经纬度匹配行政区的分支时，ds.borough已经是空值，额外添加的AND tz_loc.borough = ds.borough条件会导致匹配永远不成立，该条件属于冗余逻辑，直接删除即可。除此之外原有代码还存在几处逻辑、语法瑕疵需要同步修正。

具体修改点 #

• 删除经纬度匹配分支下冗余的AND tz_loc.borough = ds.borough判断条件
• 修正经纬度判断逻辑：只有经纬度同时非空时才能生成空间点进行匹配，将原判断中的OR改为AND，避免坐标生成报错
• 修正第二版代码中子查询的语法错误（括号位置错误、UPPER(tz_loc.borough) as STRING语法错误）
• 删除不符合需求的行政区前缀AA、CC，匹配你最初的逻辑要求
• 修正最后一个分支的判断逻辑：仅当borough为空且经纬度任意一个为空时，才标记为Unknown

修正后完整SQL #

CREATE VIEW `your-project-id.your_dataset_id.collisions_data_borough` AS
SELECT 
  CAST(timestamp AS DATE) AS collision_date, 
  COUNT(*) AS NUM_COLLISIONS, 
  CASE 
    WHEN ds.borough IS NOT NULL THEN CAST(UPPER(ds.borough) AS STRING)
    WHEN ds.borough IS NULL AND ds.latitude IS NOT NULL AND ds.longitude IS NOT NULL THEN (
      SELECT CAST(UPPER(tz_loc.borough) AS STRING) 
      FROM `bigquery-public-data.new_york_taxi_trips.taxi_zone_geom` tz_loc 
      WHERE ST_DWithin(tz_loc.zone_geom, ST_GeogPoint(CAST(ds.longitude AS FLOAT64), CAST(ds.latitude AS FLOAT64)), 0)
      LIMIT 1 -- 避免极少数边界点匹配到多个行政区的异常
    )
    ELSE "Unknown"
  END AS NEIGHBORHOOD,
  SUM(CAST(number_of_cyclist_killed AS INT64)) AS CYCLISTS_KILLED,
  SUM(CAST(number_of_cyclist_injured AS INT64)) AS CYCLISTS_INJURED,
  SUM(CAST(number_of_motorist_killed AS INT64)) AS MOTORISTS_KILLED,
  SUM(CAST(number_of_motorist_injured AS INT64)) AS MOTORISTS_INJURED,
  SUM(CAST(number_of_pedestrians_killed AS INT64)) AS PEDS_KILLED,
  SUM(CAST(number_of_pedestrians_injured AS INT64)) AS PEDS_INJURED,
  SUM(CAST(number_of_persons_killed AS INT64)) AS PERSONS_KILLED,
  SUM(CAST(number_of_persons_injured AS INT64)) AS PERSONS_INJURED
FROM `bigquery-public-data.new_york_mv_collisions.nypd_mv_collisions` ds
GROUP BY collision_date, NEIGHBORHOOD;

可选性能优化方案 #

如果处理的数据量较大，标量子查询的性能会低于JOIN写法，可以改用LEFT JOIN实现相同逻辑，代码如下：

CREATE VIEW `your-project-id.your_dataset_id.collisions_data_borough` AS
SELECT 
  CAST(ds.timestamp AS DATE) AS collision_date, 
  COUNT(*) AS NUM_COLLISIONS, 
  CASE 
    WHEN ds.borough IS NOT NULL THEN CAST(UPPER(ds.borough) AS STRING)
    WHEN ds.borough IS NULL AND ds.latitude IS NOT NULL AND ds.longitude IS NOT NULL THEN CAST(UPPER(tz_loc.borough) AS STRING)
    ELSE "Unknown"
  END AS NEIGHBORHOOD,
  SUM(CAST(number_of_cyclist_killed AS INT64)) AS CYCLISTS_KILLED,
  SUM(CAST(number_of_cyclist_injured AS INT64)) AS CYCLISTS_INJURED,
  SUM(CAST(number_of_motorist_killed AS INT64)) AS MOTORISTS_KILLED,
  SUM(CAST(number_of_motorist_injured AS INT64)) AS MOTORISTS_INJURED,
  SUM(CAST(number_of_pedestrians_killed AS INT64)) AS PEDS_KILLED,
  SUM(CAST(number_of_pedestrians_injured AS INT64)) AS PEDS_INJURED,
  SUM(CAST(number_of_persons_killed AS INT64)) AS PERSONS_KILLED,
  SUM(CAST(number_of_persons_injured AS INT64)) AS PERSONS_INJURED
FROM `bigquery-public-data.new_york_mv_collisions.nypd_mv_collisions` ds
LEFT JOIN `bigquery-public-data.new_york_taxi_trips.taxi_zone_geom` tz_loc
ON ST_DWithin(tz_loc.zone_geom, ST_GeogPoint(CAST(ds.longitude AS FLOAT64), CAST(ds.latitude AS FLOAT64)), 0)
GROUP BY collision_date, NEIGHBORHOOD;

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