SQL标量子查询返回多行错误及ALL值字段处理咨询

报错原因 #

原始SQL末尾的select (select * from rule_field) from rule是错误的：子查询select * from rule_field返回多行多列结果，而外层查询期望标量值（单个值），因此触发「Scalar subquery produced more than one element」错误。这部分属于测试代码，实际核心需求是基于rule表的非'ALL'字段关联kvp，过滤fact表数据。

核心需求实现方案 #

最终SQL #

WITH rule AS (
  SELECT 
    src_fotc_business_segment,
    src_fotc_product,
    src_reporting_unit,
    src_affiliate_unit_code,
    src_mi_function_code,
    src_mica_code,
    src_cost_centre
  FROM `dataset.test_rule`
  WHERE alloc_set = 'Corp Centre Re-Segmentation'
),
-- 将rule表的7个字段转成键值对，过滤掉'ALL'
rule_unpivot AS (
  SELECT 'fotc_business_segment' AS field_name, src_fotc_business_segment AS value FROM rule WHERE src_fotc_business_segment <> 'ALL'
  UNION ALL
  SELECT 'fotc_product' AS field_name, src_fotc_product AS value FROM rule WHERE src_fotc_product <> 'ALL'
  UNION ALL
  SELECT 'booking_entity_identifier' AS field_name, src_reporting_unit AS value FROM rule WHERE src_reporting_unit <> 'ALL'
  UNION ALL
  SELECT 'affiliate_chartfield_code' AS field_name, src_affiliate_unit_code AS value FROM rule WHERE src_affiliate_unit_code <> 'ALL'
  UNION ALL
  SELECT 'mi_function_code' AS field_name, src_mi_function_code AS value FROM rule WHERE src_mi_function_code <> 'ALL'
  UNION ALL
  SELECT 'mica_code' AS field_name, src_mica_code AS value FROM rule WHERE src_mica_code <> 'ALL'
  UNION ALL
  SELECT 'cost_centre_identifier' AS field_name, src_cost_centre AS value FROM rule WHERE src_cost_centre <> 'ALL'
),
-- 预计算每个字段对应的有效取值集合（从kvp匹配parent_name或component_name）
filtered_values AS (
  SELECT 
    ru.field_name,
    kvp.component_name
  FROM rule_unpivot ru
  LEFT JOIN `dataset.test_kvp` kvp 
    ON kvp.parent_name = ru.value OR kvp.component_name = ru.value
  GROUP BY ru.field_name, kvp.component_name
),
-- 每个字段的有效取值转成数组，方便后续判断
field_value_arrays AS (
  SELECT 
    field_name,
    ARRAY_AGG(component_name) AS valid_values
  FROM filtered_values
  GROUP BY field_name
)
SELECT f.*
FROM `dataset.test_source` f
-- 逐个字段判断：如果该字段有有效取值则过滤，否则不限制
WHERE 
  (SELECT IFNULL(ARRAY_LENGTH(valid_values), 0) = 0 OR f.fotc_business_segment IN UNNEST(valid_values)) 
  FROM field_value_arrays WHERE field_name = 'fotc_business_segment'
  AND
  (SELECT IFNULL(ARRAY_LENGTH(valid_values), 0) = 0 OR f.fotc_product IN UNNEST(valid_values)) 
  FROM field_value_arrays WHERE field_name = 'fotc_product'
  AND
  (SELECT IFNULL(ARRAY_LENGTH(valid_values), 0) = 0 OR f.booking_entity_identifier IN UNNEST(valid_values)) 
  FROM field_value_arrays WHERE field_name = 'booking_entity_identifier'
  AND
  (SELECT IFNULL(ARRAY_LENGTH(valid_values), 0) = 0 OR f.affiliate_chartfield_code IN UNNEST(valid_values)) 
  FROM field_value_arrays WHERE field_name = 'affiliate_chartfield_code'
  AND
  (SELECT IFNULL(ARRAY_LENGTH(valid_values), 0) = 0 OR f.mi_function_code IN UNNEST(valid_values)) 
  FROM field_value_arrays WHERE field_name = 'mi_function_code'
  AND
  (SELECT IFNULL(ARRAY_LENGTH(valid_values), 0) = 0 OR f.mica_code IN UNNEST(valid_values)) 
  FROM field_value_arrays WHERE field_name = 'mica_code'
  AND
  (SELECT IFNULL(ARRAY_LENGTH(valid_values), 0) = 0 OR f.cost_centre_identifier IN UNNEST(valid_values)) 
  FROM field_value_arrays WHERE field_name = 'cost_centre_identifier'

关键逻辑说明 #

1. rule_unpivot：一次性将rule的7个字段转成键值对结构，自动过滤掉值为'ALL'的记录，避免冗余子查询。
2. filtered_values：关联kvp表，获取每个rule值对应的有效component_name（包含parent_name匹配和component_name自身匹配的情况）。
3. field_value_arrays：将每个字段的有效取值转成数组，方便快速判断是否存在过滤条件。
4. fact表过滤：对每个字段做如下判断：
   • 如果该字段的有效取值数组长度为0（即rule中该字段全是'ALL'），则返回true（不限制）
   • 如果有有效取值，则用IN UNNEST(valid_values)匹配fact表的对应字段

简化版可选方案（针对BigQuery） #

如果使用BigQuery，也可以用更简洁的条件判断：

WITH rule AS (
  SELECT * FROM `dataset.test_rule` WHERE alloc_set = 'Corp Centre Re-Segmentation'
),
kvp AS (
  SELECT * FROM `dataset.test_kvp`
)
SELECT f.*
FROM `dataset.test_source` f
WHERE
  -- fotc_business_segment过滤：非ALL则匹配，否则不限制
  (EXISTS(SELECT 1 FROM rule WHERE src_fotc_business_segment <> 'ALL') 
   AND f.fotc_business_segment IN (
     SELECT component_name FROM kvp 
     WHERE parent_name IN (SELECT src_fotc_business_segment FROM rule WHERE src_fotc_business_segment <> 'ALL')
     OR component_name IN (SELECT src_fotc_business_segment FROM rule WHERE src_fotc_business_segment <> 'ALL')
   ))
  OR NOT EXISTS(SELECT 1 FROM rule WHERE src_fotc_business_segment <> 'ALL')
  AND
  -- fotc_product过滤：非ALL则匹配，否则不限制
  (EXISTS(SELECT 1 FROM rule WHERE src_fotc_product <> 'ALL') 
   AND f.fotc_product IN (
     SELECT component_name FROM kvp 
     WHERE parent_name IN (SELECT src_fotc_product FROM rule WHERE src_fotc_product <> 'ALL')
     OR component_name IN (SELECT src_fotc_product FROM rule WHERE src_fotc_product <> 'ALL')
   ))
  OR NOT EXISTS(SELECT 1 FROM rule WHERE src_fotc_product <> 'ALL')
  -- 其他字段同理，复制上述逻辑替换字段名即可

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