如何为表中缺失的contact_type插入数值列全为0的行？

核心思路 #

1. 生成所有(market, brand, contact_type)的全量组合
2. 左连接原表筛选出缺失的组合
3. 补全缺失行的字段值：非数值字段复用对应(market, brand)组合的现有值，数值字段设为0

具体代码 #

WITH 
-- 1. 定义所有可选的contact_type值
all_contact_types AS (
  SELECT 'Digital' AS contact_type UNION ALL
  SELECT 'No_Permission' UNION ALL
  SELECT 'Email' UNION ALL
  SELECT 'Post' UNION ALL
  SELECT 'SMS' UNION ALL
  SELECT 'Phone'
),
-- 2. 获取原表中所有唯一的(market, brand)组合，以及对应的非数值字段值（取该组合任意一行即可）
unique_market_brand AS (
  SELECT 
    market,
    brand,
    -- 这里列出所有非数值字段，比如假设还有country、region等，根据实际表结构调整
    ANY_VALUE(country) AS country,
    ANY_VALUE(region) AS region
  FROM `your-project.your-dataset.your-table`
  GROUP BY market, brand
),
-- 3. 生成全量的(market, brand, contact_type)组合
full_combinations AS (
  SELECT 
    umb.market,
    umb.brand,
    umb.country,
    umb.region,
    act.contact_type
  FROM unique_market_brand umb
  CROSS JOIN all_contact_types act
),
-- 4. 左连接原表，筛选出缺失的组合并补全数值字段
final_result AS (
  SELECT
    fc.market,
    fc.brand,
    fc.country,
    fc.region,
    fc.contact_type,
    -- 数值字段：原表有值则取原值，否则设为0
    COALESCE(t.permission_volume, 0) AS permission_volume,
    COALESCE(t.volume_last_month, 0) AS volume_last_month
    -- 其他数值字段按同样方式处理
  FROM full_combinations fc
  LEFT JOIN `your-project.your-dataset.your-table` t
    ON fc.market = t.market
    AND fc.brand = t.brand
    AND fc.contact_type = t.contact_type
)
SELECT * FROM final_result

关键说明 #

• all_contact_types CTE：明确列出所有可选的contact_type值，确保覆盖全部需要补全的类型
• unique_market_brand CTE：用ANY_VALUE()获取每个(market, brand)组合的非数值字段值，因为要求这些字段在同一组合下一致，取任意一行即可
• full_combinations CTE：通过CROSS JOIN生成所有可能的组合，确保没有遗漏
• 数值字段处理：用COALESCE()函数，当原表对应行不存在时自动替换为0

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