BigQuery SQL实现除单列取值不同外其余列值重复查询

问题描述 #

• 现有BigQuery业务表，需实现查询效果：除指定差异列取不同规则值外，其余所有列的取值按匹配规则重复填充
• 前期尝试使用隐式交叉连接写法未得到预期结果，已尝试代码如下：

SELECT a.* from `table1` a, `table2` b
where a.area_avg_flag = b.area_avg_flag

错误原因 #

上述写法的核心问题是未对关联维度做去重处理，也没有正确构造「固定维度值+差异列枚举值」的笛卡尔积关系：当右表table2中关联键area_avg_flag存在重复记录时，结果会出现多余重复行；如果右表枚举值不全，又会出现缺行问题。

正确写法 #

根据差异列的取值来源，分两种场景实现：

场景1：差异列为固定枚举值 #

无需额外维护枚举表，直接用BigQuery原生UNNEST函数构造枚举值数组做笛卡尔积，性能最优，示例代码：

SELECT
  t.* EXCEPT(待填充的差异列名),
  enum_val AS 待填充的差异列名
FROM
  `table1` t,
  UNNEST(['差异列枚举值1', '差异列枚举值2', '差异列枚举值3']) AS enum_val
-- 若需按area_avg_flag做值过滤，直接在WHERE子句加匹配规则即可

语法说明：BigQuery中表与UNNEST返回的数组直接逗号连接，等价于对数组中每一个枚举值，和左表每一行做笛卡尔积，天然实现非差异列重复、差异列轮询填充的效果。

场景2：差异列取值存储在另一张表table2中 #

需要先对关联表做去重，避免重复值导致结果行数异常，示例代码：

SELECT
  a.* EXCEPT(待填充的差异列名),
  b.待填充的差异列名
FROM
  `table1` a
CROSS JOIN
  (
    SELECT DISTINCT area_avg_flag, 待填充的差异列名
    FROM `table2`
  ) b
WHERE
  a.area_avg_flag = b.area_avg_flag

注意点：子查询中必须用DISTINCT对关联键和差异列做去重，否则如果table2中同一个area_avg_flag对应多条重复的差异列记录，最终结果会生成多余的重复行。

校验规则 #

查询写完后可抽样校验三个点：

• 非差异列的取值和原表对应行完全一致，无错位
• 原表每一行对应的结果行数，等于该area_avg_flag匹配到的差异列取值个数
• 差异列取值完全覆盖要求的枚举范围，无缺漏

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