新手求助：BigQuery中如何将43张同主键表合并为一张？

一、JOIN vs UNION ALL：选对工具 #

• UNION ALL：这才是你场景的正确选择。适用于结构完全相同的多张表（列名、数据类型一致），作用是把分散在多张表的行数据合并成一张（比如你按时间拆分的出行记录Trips表）。
• JOIN：绝对不适合你的情况。它是用来关联结构不同但有主键关联的表（比如用户表和订单表），如果用在同结构分表上，会因为主键重复导致数据大量冗余甚至逻辑错误。

二、BigQuery新手友好的合并方法 #

1. 通配符表（最简便，无需重复写UNION ALL）

如果你的43张表命名有规律（比如Trips_YYYY_MM格式），直接用*匹配所有表：

SELECT
  started_at,
  ended_at,
  member_casual,
  DATETIME_DIFF(ended_at, started_at, MINUTE) AS duration,
  EXTRACT(DAYOFWEEK FROM started_at) AS day_of_week
FROM `cyclistic-406707.Trips_2020_to_2023.Trips_*`
-- 可选：过滤特定时间段的表，比如只取2020-2023的月度表
WHERE _TABLE_SUFFIX BETWEEN '2020_01' AND '2023_10'

BigQuery会自动将所有符合命名规则的表合并成一个数据集，直接使用即可。

2. 保存为视图（方便后续复用）

如果需要反复使用合并后的数据集，把上述查询保存为视图：

1. 在BigQuery控制台运行查询后，点击「保存视图」
2. 命名视图（例如All_Trips_Combined）并选择对应数据集
3. 后续直接查询FROM cyclistic-406707.Trips_2020_to_2023.All_Trips_Combined``即可

3. 手动UNION ALL（不推荐，仅适用于表数量极少的情况）

若表命名无规律，只能手动拼接，但43张表会非常繁琐，示例如下：

SELECT * FROM `cyclistic-406707.Trips_2020_to_2023.Trips_2020_01`
UNION ALL
SELECT * FROM `cyclistic-406707.Trips_2020_to_2023.Trips_2020_02`
-- ... 重复至最后一张表

三、你的SQL示例优化版（合并所有表后计算） #

将原单表查询改成合并所有Trips表后的逻辑，计算全时间段casual用户的平均出行时长：

SELECT AVG(duration) AS avg_casual_duration
FROM (
  SELECT
    duration,
    member_casual
  FROM (
    SELECT
      DATETIME_DIFF(ended_at, started_at, MINUTE) AS duration,
      member_casual
    FROM `cyclistic-406707.Trips_2020_to_2023.Trips_*`
    WHERE _TABLE_SUFFIX BETWEEN '2020_01' AND '2023_10'
  )
  WHERE duration >= 0.1 AND duration <= 1440
)
WHERE member_casual = 'casual'

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