CASE语句多条件判断后表数据重复、字段空值问题排查与优化求助

问题根因分析 #

1. CASE列返回NULL：原CASE语句未覆盖所有数据分支，当出现pbs.pb_plano_assinatura为NULL、年付场景下付款记录状态不在指定集合、付款金额为NULL等情况时，没有匹配的WHEN分支就会返回NULL。
2. 数据行重复：你当前应该是直接将主表与付款表p做关联，没有提前对付款表按pb_id去重取首笔符合要求的记录，1对多的关联关系会生成重复行。未加不等于年付的条件时，年付分支的过滤条件会自动过滤掉多余的付款行，加了条件后非年付分支没有付款表的过滤约束，重复行就会暴露。

优化方案 #

1. 预计算重复逻辑减少冗余 #

将重复调用的入职日期判断、付款表首单逻辑提前抽成子查询/CTE，避免重复计算同时降低出错概率。

2. 补全CASE兜底分支 #

新增ELSE分支兜底未覆盖的场景，既避免NULL值也方便后续排查规则覆盖范围。

3. 提前预处理付款表 #

先对付款表p按pb_id分组，提前筛选出符合状态要求的首笔付款记录，再和主表关联，从根源避免1对多关联产生的重复行。

4. 分层简化CASE判断逻辑 #

先判断套餐类型，再在对应分类下判断时间、优惠等条件，不用每个WHEN都重复拼接套餐判断条件。

优化后代码示例 #

WITH pre_payment AS (
  -- 提前预处理付款表，取每个pb_id首笔符合要求的付款记录
  SELECT 
    pb_id,
    value,
    originalDueDate
  FROM (
    SELECT 
      p.*,
      ROW_NUMBER() OVER (PARTITION BY p.pb_id ORDER BY p.originalDueDate ASC) AS rn
    FROM p
    WHERE p.status IN('RECEIVED' ,'CONFIRMED', 'RECEIVED_IN_CASH')
  ) t
  WHERE rn = 1
),
base_data AS (
  -- 预计算所有需要用到的判断字段
  SELECT
    o.*,
    pbs.*,
    pp.value AS first_pay_value,
    -- 抽重复的入职日期判断逻辑
    CASE 
      WHEN o.order_created_at < pbs.pb_dt_onboarding THEN DATE(pbs.pb_dt_primeiro_onboarding)
      ELSE DATE(pbs.pb_dt_onboarding)
    END AS calc_onboarding_date,
    -- 处理套餐类型NULL的情况
    COALESCE(pbs.pb_plano_assinatura, '未知') AS plan_type
  FROM 你原有的多表关联逻辑
  LEFT JOIN pre_payment pp ON pbs.pb_id = pp.pb_id
)
SELECT
  *,
  CASE
    -- 先处理特殊ID
    WHEN pb_id = 'ddcdff35-a044-5ccc-a65d-7e51692b269a' THEN 'Anual Entrada'
    -- 非年付逻辑
    WHEN plan_type != 'Anual' THEN
      CASE
        WHEN calc_onboarding_date < DATE('2021-02-25') THEN 'Mensal Antigo'
        WHEN calc_onboarding_date >= DATE('2021-02-25') AND calc_onboarding_date < DATE('2021-06-02') THEN
          CASE WHEN pb_codigo_promocional IS NULL THEN 'Mensal Pgto Entrada' ELSE 'Mensal Cupom' END
        WHEN calc_onboarding_date >= DATE('2021-06-02') THEN 'Mensal Gratuidade'
      END
    -- 年付逻辑
    WHEN plan_type = 'Anual' THEN
      CASE
        WHEN first_pay_value >= 250.0 THEN 'Anual Entrada'
        ELSE
          CASE
            WHEN calc_onboarding_date < DATE('2021-02-25') THEN 'Mensal Antigo'
            WHEN calc_onboarding_date >= DATE('2021-02-25') AND calc_onboarding_date < DATE('2021-06-02') THEN
              CASE WHEN pb_codigo_promocional IS NULL THEN 'Mensal Pgto Entrada' ELSE 'Mensal Cupom' END
            WHEN calc_onboarding_date >= DATE('2021-06-02') THEN 'Mensal Gratuidade'
          END
      END
    -- 兜底分支
    ELSE '其他'
  END AS pb_safra
FROM base_data

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