BigQuery中如何基于数组条件关联items与conditions表？

请考虑以下两个表：

CREATE OR REPLACE TABLE `test.items` AS (
        SELECT 1 AS ID, 1 AS Foo, 100 AS Bar,
UNION ALL SELECT 2 AS ID, 1 AS Foo, 155 AS Bar,
UNION ALL SELECT 3 AS ID, 1 AS Foo, 219 AS Bar,
UNION ALL SELECT 4 AS ID, 2 AS Foo, 155 AS Bar,
UNION ALL SELECT 5 AS ID, 2 AS Foo, 200 AS Bar,
UNION ALL SELECT 6 AS ID, 2 AS Foo, 300 AS Bar
);

CREATE OR REPLACE TABLE `test.conditions` AS (
  SELECT 1 AS Foo, ["50..150", "2**"] AS Conditions,
  UNION ALL SELECT 2 AS Foo, ["3*0", "200..300", "155"] AS Conditions
)

items表数据：

conditions表数据：

需要设计一个类似items左连接conditions的查询（保留所有条件，仅保留匹配的items），满足以下规则：

• 单个条件可匹配多个items
• 两表的Foo列必须匹配
• items表中的Bar列需匹配Conditions数组中的任意一个条件

此外，Conditions采用非标准语法，匹配规则如下：

• M..N：匹配M到N之间的所有值（包含边界）
• M*N：匹配以M开头、N结尾的3字符字符串
• M**：匹配以M开头的3字符字符串
• 其他情况：精确匹配

我能把这些逻辑转换成正则表达式，但在查询架构设计上遇到了困难。我猜测应该结合LOGICAL_OR和UDF实现，示例代码如下：

WITH step1 AS (
  SELECT
    Foo,
    Bar,
    Conditions
  FROM `test.conditions`
  LEFT JOIN `test.items` USING(Foo)
)

SELECT * FROM step1 WHERE (LOGICAL_OR(MyFunc(Bar, Conditions)))

核心思路 #

先拆分Conditions数组为单行记录，让每个条件单独与items表记录匹配，再用内置函数实现每种条件的匹配逻辑（无需自定义UDF），最后按需聚合结果。

实现代码 #

方案1：获取所有匹配的item-条件对

WITH expanded_conditions AS (
  -- 拆分条件数组为单行
  SELECT
    c.Foo,
    condition
  FROM `test.conditions` c, UNNEST(c.Conditions) condition
),
matched_pairs AS (
  SELECT
    i.ID,
    i.Foo,
    i.Bar,
    ec.condition
  FROM `test.items` i
  JOIN expanded_conditions ec ON i.Foo = ec.Foo
  WHERE
    -- 处理范围匹配 M..N
    (REGEXP_CONTAINS(ec.condition, r'^\d+\.\.\d+$') 
     AND i.Bar BETWEEN CAST(SPLIT(ec.condition, '..')[OFFSET(0)] AS INT64) 
                   AND CAST(SPLIT(ec.condition, '..')[OFFSET(1)] AS INT64))
    -- 处理3字符首尾匹配 M*N
    OR (REGEXP_CONTAINS(ec.condition, r'^\d\*\d$')
        AND LENGTH(CAST(i.Bar AS STRING)) = 3
        AND LEFT(CAST(i.Bar AS STRING), 1) = LEFT(ec.condition, 1)
        AND RIGHT(CAST(i.Bar AS STRING), 1) = RIGHT(ec.condition, 1))
    -- 处理3字符开头匹配 M**
    OR (REGEXP_CONTAINS(ec.condition, r'^\d\*\*$')
        AND LENGTH(CAST(i.Bar AS STRING)) = 3
        AND LEFT(CAST(i.Bar AS STRING), 1) = LEFT(ec.condition, 1))
    -- 处理精确匹配
    OR (CAST(i.Bar AS STRING) = ec.condition)
)
SELECT * FROM matched_pairs

方案2：保留所有原始条件组，关联匹配的items

如果需要还原为“每个条件组对应匹配items”的结构，可使用聚合函数：

WITH expanded_conditions AS (
  SELECT
    c.Foo,
    condition,
    c.Conditions AS original_conditions
  FROM `test.conditions` c, UNNEST(c.Conditions) condition
),
matched_items AS (
  SELECT
    ec.Foo,
    ec.original_conditions,
    i.ID,
    i.Bar
  FROM expanded_conditions ec
  LEFT JOIN `test.items` i 
    ON ec.Foo = i.Foo
    AND (
      (REGEXP_CONTAINS(ec.condition, r'^\d+\.\.\d+$') 
       AND i.Bar BETWEEN CAST(SPLIT(ec.condition, '..')[OFFSET(0)] AS INT64) 
                     AND CAST(SPLIT(ec.condition, '..')[OFFSET(1)] AS INT64))
      OR (REGEXP_CONTAINS(ec.condition, r'^\d\*\d$')
          AND LENGTH(CAST(i.Bar AS STRING)) = 3
          AND LEFT(CAST(i.Bar AS STRING), 1) = LEFT(ec.condition, 1)
          AND RIGHT(CAST(i.Bar AS STRING), 1) = RIGHT(ec.condition, 1))
      OR (REGEXP_CONTAINS(ec.condition, r'^\d\*\*$')
          AND LENGTH(CAST(i.Bar AS STRING)) = 3
          AND LEFT(CAST(i.Bar AS STRING), 1) = LEFT(ec.condition, 1))
      OR (CAST(i.Bar AS STRING) = ec.condition)
    )
)
SELECT
  Foo,
  original_conditions AS Conditions,
  ARRAY_AGG(DISTINCT ID IGNORE NULLS) AS matched_item_ids,
  ARRAY_AGG(DISTINCT Bar IGNORE NULLS) AS matched_bars
FROM matched_items
GROUP BY Foo, original_conditions

说明 #

• 拆分数组后，每个条件独立匹配，避免了数组处理的复杂度
• 用内置正则和字符串函数实现所有匹配规则，无需依赖UDF，性能更优
• 方案2通过LEFT JOIN和IGNORE NULLS确保保留所有原始条件组，即使没有匹配的items也会显示空数组

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