BigQuery左外连接：保留右表唯一值且不重复主键

解决思路 #

1. 先清理ua表，保留每个dateHourMinute下唯一的defaultChannelGrouping值，从根源避免重复
2. 给admin表的每条记录按时间分组生成序号，再和对应时间下的唯一分组值做匹配，确保每个admin的id对应不同分组，同时覆盖所有右表的分组类型

具体SQL实现 #

WITH unique_ua AS (
  -- 提取每个时间维度下的唯一分组值
  SELECT DISTINCT
    dateHourMinute,
    defaultChannelGrouping
  FROM `your-project.your-dataset.ua` -- 替换成你的ua表完整路径
),
admin_ranked AS (
  -- 给同时间戳下的admin记录按id排序生成序号
  SELECT
    id,
    timestamp,
    ROW_NUMBER() OVER (PARTITION BY timestamp ORDER BY id) AS row_num
  FROM `your-project.your-dataset.admin` -- 替换成你的admin表完整路径
),
ua_ranked AS (
  -- 给同时间维度下的唯一分组值生成序号
  SELECT
    dateHourMinute,
    defaultChannelGrouping,
    ROW_NUMBER() OVER (PARTITION BY dateHourMinute ORDER BY defaultChannelGrouping) AS row_num
  FROM unique_ua
)
-- 左外连接，通过时间匹配+序号取模分配分组
SELECT
  a.id,
  a.timestamp,
  COALESCE(u.defaultChannelGrouping, '无匹配分组') AS defaultChannelGrouping
FROM admin_ranked a
LEFT JOIN ua_ranked u
  ON a.timestamp = u.dateHourMinute
  -- 用取模循环分配，确保每个admin记录对应不同分组，覆盖所有类型
  AND MOD(a.row_num, (SELECT COUNT(*) FROM ua_ranked WHERE dateHourMinute = a.timestamp)) = u.row_num - 1
ORDER BY a.id;

代码说明 #

• unique_ua：先去重ua表，每个时间点只保留不重复的分组类型，避免后续连接产生重复数据
• admin_ranked：给同一时间戳下的admin记录排序编号，用来后续分配不同的分组
• ua_ranked：给每个时间点下的唯一分组排序编号，和admin的序号对应
• 连接条件里的MOD函数：如果某个时间点有N个唯一分组，admin的序号对N取模后，会循环匹配这N个分组，保证每个admin id对应不同分组，且所有分组类型都能被覆盖
• COALESCE：处理admin表中没有匹配到ua表的记录，给个默认值

备选随机分配方案 #

如果不需要严格循环分配，只是要每个admin id对应唯一分组，可以用随机匹配的方式：

WITH unique_ua AS (
  SELECT DISTINCT
    dateHourMinute,
    defaultChannelGrouping,
    RAND() AS rand_score
  FROM `your-project.your-dataset.ua`
),
admin_with_rand AS (
  SELECT
    id,
    timestamp,
    RAND() AS rand_score
  FROM `your-project.your-dataset.admin`
)
SELECT
  a.id,
  a.timestamp,
  COALESCE(u.defaultChannelGrouping, '无匹配分组') AS defaultChannelGrouping
FROM admin_with_rand a
LEFT JOIN unique_ua u
  ON a.timestamp = u.dateHourMinute
-- 给每个admin id匹配最接近的随机分数对应的分组，确保唯一
QUALIFY ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY ABS(a.rand_score - u.rand_score)) = 1
ORDER BY a.id;

这个方案通过随机数匹配，每个admin id会对应一个唯一的分组类型，同样能覆盖所有右表的分组。

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