如何在BigQuery中用内连接和子查询实现多表关联查询

问题 #

在BigQuery中有6张表：walking、running、swimming、cycling四张活动表，以及user表、fav_activity表，表结构与数据如下：

活动表结构与数据 #

walking表

running表

swimming表

cycling表

用户相关表结构与数据 #

user表

fav_activity表

所有表的activity_date、activity_time（每分钟一条记录）和userID字段匹配，需要基于这些匹配字段关联所有表，展示指定日期的各活动数值，最终关联用户表后得到如下格式结果：

需要说明如何在BigQuery中通过INNER JOIN和子查询实现该关联查询。

实现方案 #

方法一：直接使用INNER JOIN关联所有表 #

以其中一张活动表作为主表，通过activity_date、activity_time、userID三个字段依次关联其他活动表，再关联user表和fav_activity表（若不需要展示fav_activity字段，可省略该表关联）。

SQL代码如下：

SELECT
  w.activity_date,
  w.activity_time,
  w.userID,
  u.age,
  u.height,
  u.weight,
  w.value AS walking_value,
  r.value AS running_value,
  s.value AS swimming_value,
  c.value AS cycling_value
  -- 如需展示fav_activity字段，可添加：fa.fav_activity, fa.freq_activity, fa.schedule
FROM
  `your-project.your-dataset.walking` w
INNER JOIN
  `your-project.your-dataset.running` r
ON
  w.activity_date = r.activity_date
  AND w.activity_time = r.activity_time
  AND w.userID = r.userID
INNER JOIN
  `your-project.your-dataset.swimming` s
ON
  w.activity_date = s.activity_date
  AND w.activity_time = s.activity_time
  AND w.userID = s.userID
INNER JOIN
  `your-project.your-dataset.cycling` c
ON
  w.activity_date = c.activity_date
  AND w.activity_time = c.activity_time
  AND w.userID = c.userID
INNER JOIN
  `your-project.your-dataset.user` u
ON
  w.userID = u.userID
INNER JOIN
  `your-project.your-dataset.fav_activity` fa
ON
  w.userID = fa.userId
-- 筛选指定日期
WHERE
  w.activity_date IN ('2023-03-11', '2023-03-12')
ORDER BY
  w.activity_date, w.activity_time;

说明：

• 以walking表作为主表，INNER JOIN会自动过滤掉无匹配记录的行，确保结果中每条数据都有对应时间、用户的所有活动数据；
• 用三个字段联合关联，保证时间和用户的唯一性匹配；
• 字段别名使用_value替代.，避免BigQuery中字段名包含特殊字符的语法问题。

方法二：使用子查询预聚合活动数据 #

先通过子查询合并所有活动表数据，再用PIVOT转置为列，最后关联用户表。这种方式在活动表数量较多时更简洁易维护。

SQL代码如下：

WITH combined_activities AS (
  SELECT activity_date, activity_time, userID, 'walking' AS activity_type, value FROM `your-project.your-dataset.walking`
  UNION ALL
  SELECT activity_date, activity_time, userID, 'running' AS activity_type, value FROM `your-project.your-dataset.running`
  UNION ALL
  SELECT activity_date, activity_time, userID, 'swimming' AS activity_type, value FROM `your-project.your-dataset.swimming`
  UNION ALL
  SELECT activity_date, activity_time, userID, 'cycling' AS activity_type, value FROM `your-project.your-dataset.cycling`
),
pivoted_activities AS (
  SELECT
    activity_date,
    activity_time,
    userID,
    walking,
    running,
    swimming,
    cycling
  FROM combined_activities
  PIVOT (
    MAX(value) FOR activity_type IN ('walking', 'running', 'swimming', 'cycling')
  )
)
SELECT
  pa.activity_date,
  pa.activity_time,
  pa.userID,
  u.age,
  u.height,
  u.weight,
  pa.walking AS walking_value,
  pa.running AS running_value,
  pa.swimming AS swimming_value,
  pa.cycling AS cycling_value
FROM pivoted_activities pa
INNER JOIN `your-project.your-dataset.user` u
ON pa.userID = u.userID
INNER JOIN `your-project.your-dataset.fav_activity` fa
ON pa.userID = fa.userId
WHERE pa.activity_date IN ('2023-03-11', '2023-03-12')
ORDER BY pa.activity_date, pa.activity_time;

说明：

• combined_activities子查询将四张活动表合并为统一结构，便于后续转置；
• pivoted_activities子查询通过PIVOT将活动类型转置为列，直接获取每个时间点、用户的各活动数值；
• 新增活动表时，只需在UNION ALL中添加对应表即可，扩展性更强。

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