使用CONCAT拼接SQL查询首行出现'to'，求修改方案

问题根源是数据中存在大量start_station_name、end_station_name和usertype为空或NULL的异常记录，这些记录被聚合后，CONCAT的结果只剩下中间的"to"（显示时可能压缩了空格），而duration为NULL是因为对应的tripduration也为NULL。

只需在查询中添加WHERE子句过滤掉这些无效记录即可：

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 != ''
  AND usertype IS NOT NULL
GROUP BY
  start_station_name, end_station_name, usertype
ORDER BY
  num_trips DESC 
LIMIT 10

补充说明 #

• WHERE条件确保只统计有有效起点、终点和用户类型的行程记录，彻底排除异常聚合行。
• 如果数据中存在首尾带空格的无效字符串，可将条件简化为TRIM(start_station_name) != ''，同时保留IS NOT NULL判断，避免遗漏各类无效值。

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