BigQuery中获取Step1与Step2公共值：Step1全为null时取Step2值

需求 #

在BigQuery中实现：获取Step1与Step2的公共customer_id值；若Step1的customer_id全为null，则取Step2的所有customer_id。

现有问题示例 #

场景1：Step_a全为null时（不符合预期）

现有查询：

with step_a as (select null as customer_id union all
    select null as customer_id union all
    select null as customer_id union all
    select null as customer_id),
    
step_b as (select 1 as customer_id union all
    select 2 as customer_id union all
    select 3 as customer_id union all
    select 4 as customer_id )

(Select customer_id from step_a)
      intersect distinct
 (select customer_id from step_b)    

• 预期结果：

  customer_id 
  1
  2
  3
  4
  

• 实际结果：无返回值

场景2：Step_a非null时（符合预期）

现有查询：

with step_a as (select 1 as customer_id union all
        select 2 as customer_id union all
        select 3 as customer_id union all
        select 4 as customer_id),
   
step_b as (select 1 as customer_id union all
        select 2 as customer_id union all
        select 3 as customer_id union all
        select 4 as customer_id )

(Select customer_id from step_a)
  intersect distinct
(select customer_id from step_b)

• 结果：符合预期，返回：

  customer_id 
  1
  2
  3
  4
  

需覆盖的全量场景 #

• 场景1：Step_a全为null，Step_b为1、2 → 输出：1、2
• 场景2：Step_a为1、2，Step_b为5 → 输出：无
• 场景3：Step_a为1、2，Step_b为1、2、5 → 输出：1、2
• 场景4：Step_a为5，Step_b全为null → 输出：无

解决方案 #

以下SQL可满足所有场景需求：

WITH step_a AS (
    -- 替换为你的Step1实际查询逻辑
    SELECT NULL AS customer_id UNION ALL
    SELECT NULL AS customer_id UNION ALL
    SELECT NULL AS customer_id UNION ALL
    SELECT NULL AS customer_id
),
step_b AS (
    -- 替换为你的Step2实际查询逻辑
    SELECT 1 AS customer_id UNION ALL
    SELECT 2 AS customer_id UNION ALL
    SELECT 3 AS customer_id UNION ALL
    SELECT 4 AS customer_id
),
step_a_stats AS (
    SELECT COUNT(DISTINCT customer_id) AS non_null_count
    FROM step_a
)
SELECT DISTINCT b.customer_id
FROM step_b b
CROSS JOIN step_a_stats s
WHERE 
    (s.non_null_count = 0 AND b.customer_id IS NOT NULL)
    OR b.customer_id IN (SELECT customer_id FROM step_a WHERE customer_id IS NOT NULL)

逻辑说明

1. 通过step_a_stats统计Step_a中非null的customer_id数量
2. 条件分支：
   • 若non_null_count = 0（Step_a全为null），则选取Step_b所有非null的customer_id
   • 否则，选取Step_b中存在于Step_a非null集合中的customer_id
3. 自动处理Step_b全为null的场景，此时返回空结果，符合需求

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