Google BigQuery查询无数据显示求助：会员与非会员热门起点统计

问题描述 #

拥有超过50万行的数据集，包含start_station_name和member_casual两列，其中member_casual标识用户是会员（member）还是非会员（casual）。需要分别统计两类用户的Top10热门起点，但现有CTE查询执行后显示“无数据可展示”，已尝试调整LIMIT、检查临时表、权限及数据存在性，问题仍未解决。原查询代码如下：

WITH casual_trips AS (
  SELECT start_station_name,
  COUNT(member_casual) AS casual_trip_times
  FROM `cyclisticdataanalysis-402119.2022_Trip_Data.May2022_TripData`
  WHERE member_casual = "casual" 
  GROUP BY start_station_name
  ORDER BY COUNT(member_casual) DESC
  
)

SELECT 
Main.start_station_name,
COUNT(member_casual) AS member_trips,
casual_trips.casual_trip_times AS casual_trip
 FROM `cyclisticdataanalysis-402119.2022_Trip_Data.May2022_TripData`  AS Main
 JOIN casual_trips ON Main.start_station_name=casual_trips.start_station_name
 WHERE member_casual = "%member%"
 GROUP BY Main.start_station_name, casual_trip
 ORDER BY casual_trip DESC,
member_trips DESC

错误原因分析 #

• 匹配条件错误：WHERE member_casual = "%member%"中误用了模糊匹配的百分号，这里需要精确匹配会员用户，应直接写member_casual = 'member'。带百分号的条件会匹配不到任何有效数据，因为数据集里的会员标识是member而非%member%。
• JOIN逻辑偏离需求：使用INNER JOIN仅会保留同时有会员和非会员骑行记录的站点，丢失了仅一类用户使用的站点数据；且原查询试图将两类用户的统计结果关联展示，不符合“分别统计Top10”的核心需求。
• Top10逻辑缺失：原查询全程未添加LIMIT 10筛选Top10站点，整体逻辑未实现目标要求的展示效果。

正确查询方案 #

方案1：分别统计后合并结果 #

通过UNION ALL将两类用户的Top10统计结果合并，清晰区分不同用户群体的热门站点：

-- 统计非会员Top10热门起点
SELECT 
  'casual' AS user_type,
  start_station_name,
  COUNT(*) AS trip_count
FROM `cyclisticdataanalysis-402119.2022_Trip_Data.May2022_TripData`
WHERE member_casual = 'casual'
GROUP BY start_station_name
ORDER BY trip_count DESC
LIMIT 10

UNION ALL

-- 统计会员Top10热门起点
SELECT 
  'member' AS user_type,
  start_station_name,
  COUNT(*) AS trip_count
FROM `cyclisticdataanalysis-402119.2022_Trip_Data.May2022_TripData`
WHERE member_casual = 'member'
GROUP BY start_station_name
ORDER BY trip_count DESC
LIMIT 10

方案2：条件聚合+窗口函数 #

使用窗口函数ROW_NUMBER()按用户类型分组排序，精准筛选每类用户的Top10站点：

WITH station_trips AS (
  SELECT
    start_station_name,
    SUM(CASE WHEN member_casual = 'casual' THEN 1 ELSE 0 END) AS casual_trips,
    SUM(CASE WHEN member_casual = 'member' THEN 1 ELSE 0 END) AS member_trips
  FROM `cyclisticdataanalysis-402119.2022_Trip_Data.May2022_TripData`
  GROUP BY start_station_name
),
ranked_casual AS (
  SELECT
    'casual' AS user_type,
    start_station_name,
    casual_trips AS trip_count,
    ROW_NUMBER() OVER(ORDER BY casual_trips DESC) AS rank
  FROM station_trips
  WHERE casual_trips > 0
),
ranked_member AS (
  SELECT
    'member' AS user_type,
    start_station_name,
    member_trips AS trip_count,
    ROW_NUMBER() OVER(ORDER BY member_trips DESC) AS rank
  FROM station_trips
  WHERE member_trips > 0
)
SELECT user_type, start_station_name, trip_count
FROM ranked_casual WHERE rank <=10
UNION ALL
SELECT user_type, start_station_name, trip_count
FROM ranked_member WHERE rank <=10
ORDER BY user_type, rank

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