BigQuery统计旧金山共享单车站点用户数及平均时长SQL咨询

SELECT
  start_station_name,
  COUNTIF(subscriber_type = 'Subscriber') AS total_Subscriber,
  COUNTIF(subscriber_type = 'Customer') AS total_nonSubscriber,
  ROUND(AVG(CASE WHEN subscriber_type = 'Subscriber' THEN duration_sec END), 2) AS avg_duration_Subscriber,
  ROUND(AVG(CASE WHEN subscriber_type = 'Customer' THEN duration_sec END), 2) AS avg_duration_nonSubscriber
FROM
  `bigquery-public-data.san_francisco_bikeshare.bikeshare_trips`
WHERE
  start_station_name IS NOT NULL
GROUP BY
  start_station_name
ORDER BY
  start_station_name

逻辑说明 #

• 采用条件聚合实现多维度统计，无需子查询或表关联，适配BigQuery大数据量查询场景
• 平均骑行时长默认保留2位小数，可自行修改ROUND函数的参数调整精度，或删除ROUND函数返回原始计算精度
• 过滤了起始站点为空的无效记录，可根据你的分析需求删除该过滤条件

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