BigQuery执行指定SQL查询出现空白行的原因及解决方法

我严格按照证书课程讲师的内容输入了以下BigQuery查询语句，执行后结果第一行近乎空白，但讲师运行相同语句却没有这个问题。我希望得到10条完整的数据，请问该如何消除这条空白行？

查询语句：

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`
GROUP BY
start_station_name, end_station_name, usertype
ORDER BY
num_trips DESC
LIMIT 10

解决方法 #

首行空白是因为数据集里存在start_station_name或end_station_name为空值的记录，这类记录拼接出的route字段为空，且对应的骑行次数（num_trips）极高，所以排序后排在首位。只需在查询中添加过滤条件排除空值即可：

修改后的查询语句：

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
GROUP BY
start_station_name, end_station_name, usertype
ORDER BY
num_trips DESC
LIMIT 10

添加WHERE子句后，会排除所有站点名称为空的记录，确保拼接出的route都是有效的站点路径，最终返回的10条结果就都是完整数据了。讲师运行时没出现问题，大概率是其使用的数据集已过滤过空值，或者当时数据中这类高数量的空值记录不存在。

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