BigQuery SQL关联表时SUM函数计算结果异常求助

问题原因 #

当直接按Id关联daily_activity（简称DA）和sleep_day（简称SD）时，若同一用户在两张表中都有多条记录（比如DA有N条日活动数据，SD有M条日睡眠数据），会产生笛卡尔积——每条DA记录会和每条SD记录匹配，最终得到N*M条记录。此时执行SUM会重复统计DA的步数M次、SD的睡眠时长N次，导致结果远大于实际值。

解决方案 #

方案1：按日期+用户维度关联（日常统计场景） #

两张表都是日维度数据，正确的关联逻辑应为用户Id + 日期，确保每日的活动和睡眠数据一一对应，从源头避免笛卡尔积。注意需统一日期格式（比如SD的SleepDay可能是MM/DD/YYYY字符串，需转换为DATE类型和DA的ActivityDate匹配）：

SELECT
  DA.Id,
  DA.ActivityDate,
  SUM(DA.TotalSteps) AS 每日总步数,
  SUM(SD.TotalMinutesAsleep) AS 每日总睡眠时长
FROM
  `你的项目名.数据集名.daily_activity` DA
LEFT JOIN
  `你的项目名.数据集名.sleep_day` SD
ON
  DA.Id = SD.Id
  -- 根据实际日期格式调整解析规则，示例为MM/DD/YYYY转标准DATE
  AND PARSE_DATE('%m/%d/%Y', SD.SleepDay) = DA.ActivityDate
GROUP BY
  DA.Id, DA.ActivityDate

• 用LEFT JOIN可保留没有睡眠数据的活动日期记录；若仅需同时有活动和睡眠数据的日期，改用INNER JOIN。

方案2：先分别汇总再关联（用户维度总统计场景） #

如果需要统计每个用户的总步数和总睡眠时长，先对两张表单独按用户Id汇总，再关联汇总结果，彻底避免笛卡尔积：

WITH 汇总活动数据 AS (
  SELECT
    Id,
    SUM(TotalSteps) AS 总步数
  FROM
    `你的项目名.数据集名.daily_activity`
  GROUP BY
    Id
),
汇总睡眠数据 AS (
  SELECT
    Id,
    SUM(TotalMinutesAsleep) AS 总睡眠时长
  FROM
    `你的项目名.数据集名.sleep_day`
  GROUP BY
    Id
)
SELECT
  COALESCE(AA.Id, ASL.Id) AS 用户Id,
  AA.总步数,
  ASL.总睡眠时长
FROM
  汇总活动数据 AA
FULL OUTER JOIN
  汇总睡眠数据 ASL
ON
  AA.Id = ASL.Id

• 用FULL OUTER JOIN可保留仅活动数据或仅睡眠数据的用户；若仅需同时有两类数据的用户，改用INNER JOIN；若需保留所有活动用户，改用LEFT JOIN。

额外检查点 #

• 确认两张表的日期字段格式是否一致，若格式不同需用PARSE_DATE或FORMAT_DATE统一后再关联；
• 检查是否存在同一用户同一日期有多条记录的情况（比如DA中同一Id+ActivityDate有多条数据），若有需先去重或在汇总时处理。

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