BigQuery中COUNT与CONCAT联用返回多余路线行的修正方法

问题原因 #

数据集bigquery-public-data.new_york_citibike.citibike_trips中存在大量无效记录，这些记录的start_station_name、end_station_name为NULL，且无有效骑行时长。当起止站点名均为NULL时，CONCAT(NULL, " to ", NULL)会直接返回你看到的异常值to，这类无效记录总量远高于正常骑行记录，所以排序后出现在首行。

修正方案 #

在查询中添加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 end_station_name IS NOT NULL
    AND tripduration IS NOT NULL
GROUP BY   
    start_station_name, end_station_name, usertype
ORDER BY 
    num_trips DESC 
LIMIT 10

补充说明 #

如果仅需要临时移除首行异常结果不需要过滤其他无效记录，也可以在WHERE条件中添加route != "to"，但更推荐直接过滤空值的方案，从根源避免无效数据干扰统计结果。

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