BigQuery（BQ）中按广告主分组实现每行IP数组与其余行的差集计数

现有样例数据表 #

WITH tbl_campaign_ipmapping AS
(
  SELECT 'advertiser1' as advertiser, 'campaign1' as campaign,  ['10.0.0.0','20.0.0.0','30.0.0.0', '40.0.0.0'] AS ip_array UNION ALL
  SELECT 'advertiser1' as advertiser, 'campaign2' as campaign, ['10.0.0.0', '20.0.0.0', '50.0.0.0']  UNION ALL 
  SELECT 'advertiser1' as advertiser, 'campaign3' as campaign, ['10.0.0.0', '40.0.0.0', '60.0.0.0', '70.0.0.0', '80.0.0.0']  UNION ALL 
  SELECT 'advertiser1' as advertiser, 'campaign4' as campaign, ['10.0.0.0', '20.0.0.0', '30.0.0.0'] UNION ALL 
  
  SELECT 'advertiser2' , 'campaign1' ,  ['10.1.1.1','20.1.1.1','30.1.1.1', '40.1.1.1']  UNION ALL
  SELECT 'advertiser2' , 'campaign2' , ['10.1.1.1', '20.1.1.1', '50.1.1.1']  UNION ALL 
  SELECT 'advertiser2' , 'campaign3' , ['10.1.1.1', '40.1.1.1', '60.1.1.1', '70.1.1.1', '80.1.1.1']  UNION ALL 
  SELECT 'advertiser2', 'campaign4' , ['10.1.1.1', '20.1.1.1', '30.1.1.1']  

) select * from tbl_campaign_ipmapping

需求说明 #

输入指定广告主（示例为advertiser1）时，对该广告主下的每一条推广活动数据，将其ip_array和该广告主其余所有推广活动的ip_array对比，统计其余活动存在但当前活动不存在的IP数量。
参考逻辑输出示例如下：

advertiser1, campaign1, 4 -- 对应IP为['50.0.0.0', '60.0.0.0', '70.0.0.0', '80.0.0.0']
advertiser1, campaign2, 5 -- 对应IP为['30.0.0.0', '40.0.0.0', '60.0.0.0', '70.0.0.0', '80.0.0.0']
advertiser1, campaign3, 3 -- 对应IP为['20.0.0.0','30.0.0.0', '50.0.0.0']
advertiser1, campaign4, 5 -- 对应IP为['40.0.0.0', '50.0.0.0', '60.0.0.0', '70.0.0.0', '80.0.0.0']

实现代码 #

-- 定义目标广告主参数，可按需修改
DECLARE target_advertiser STRING DEFAULT 'advertiser1';

WITH tbl_campaign_ipmapping AS
(
  SELECT 'advertiser1' as advertiser, 'campaign1' as campaign,  ['10.0.0.0','20.0.0.0','30.0.0.0', '40.0.0.0'] AS ip_array UNION ALL
  SELECT 'advertiser1' as advertiser, 'campaign2' as campaign, ['10.0.0.0', '20.0.0.0', '50.0.0.0']  UNION ALL 
  SELECT 'advertiser1' as advertiser, 'campaign3' as campaign, ['10.0.0.0', '40.0.0.0', '60.0.0.0', '70.0.0.0', '80.0.0.0']  UNION ALL 
  SELECT 'advertiser1' as advertiser, 'campaign4' as campaign, ['10.0.0.0', '20.0.0.0', '30.0.0.0'] UNION ALL 
  
  SELECT 'advertiser2' , 'campaign1' ,  ['10.1.1.1','20.1.1.1','30.1.1.1', '40.1.1.1']  UNION ALL
  SELECT 'advertiser2' , 'campaign2' , ['10.1.1.1', '20.1.1.1', '50.1.1.1']  UNION ALL 
  SELECT 'advertiser2' , 'campaign3' , ['10.1.1.1', '40.1.1.1', '60.1.1.1', '70.1.1.1', '80.1.1.1']  UNION ALL 
  SELECT 'advertiser2', 'campaign4' , ['10.1.1.1', '20.1.1.1', '30.1.1.1']  
),
-- 筛选目标广告主全量数据
target_ad_data AS (
  SELECT * FROM tbl_campaign_ipmapping WHERE advertiser = target_advertiser
)
SELECT 
  advertiser,
  campaign,
  -- 统计其余活动存在、当前活动不存在的去重IP数量
  ARRAY_LENGTH(ARRAY(
    SELECT DISTINCT ip
    FROM target_ad_data b, UNNEST(b.ip_array) ip
    WHERE b.campaign != a.campaign
      AND ip NOT IN UNNEST(a.ip_array)
  )) AS exclusive_ip_count
FROM target_ad_data a
ORDER BY campaign;

逻辑说明 #

1. 先通过参数target_advertiser指定要查询的广告主，可直接修改参数值切换查询对象
2. 筛选出目标广告主的所有推广活动数据，缩小计算范围
3. 逐行遍历每个推广活动，拉取其余所有活动的去重IP，过滤掉当前活动已有的IP后统计剩余数量

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