BigQuery查询异常：首行route为“to”，有效结果从第二行开始

问题根源 #

• 首行出现无效的"to" route，是因为数据集里存在start_station_name和/或end_station_name为空字符串或NULL的记录。这些无效记录被GROUP BY分组后，CONCAT拼接出了无意义的结果（比如两个字段都为空时，会得到" to "，可能展示时被简化为"to"）。
• 这类无效记录的数量远多于正常骑行记录，所以在ORDER BY num_trips DESC后排在了首位。

解决方法 #

在查询中添加WHERE子句，过滤掉站点名称为空或NULL的无效记录，确保只统计有效骑行数据：

SELECT 
  usertype,
  CONCAT(start_station_name, " to ", end_station_name) AS route,
  COUNT(*) AS num_trips,
  ROUND(AVG(CAST(tripduration AS INT64)/60), 2) AS Duration
FROM `bigquery-public-data.new_york_citibike.citibike_trips`
-- 过滤无效的站点名称记录
WHERE 
  start_station_name IS NOT NULL 
  AND start_station_name != ""
  AND end_station_name IS NOT NULL 
  AND end_station_name != ""
GROUP BY 
  start_station_name, end_station_name, usertype
ORDER BY
  num_trips DESC
LIMIT 10

可选调整 #

如果确认数据中空字符串是合法场景，仅需排除NULL值，可以简化WHERE条件为：

WHERE start_station_name IS NOT NULL AND end_station_name IS NOT NULL

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