如何在BigQuery SQL中按品牌分组提取产品字符串差异列？

原始数据 #

目标结果 #

需求说明：按brand分组，提取同组内product字符串的差异部分作为新列word，同时需要了解如何用regexp_contains判断并返回对应值。

解决方案 #

一、提取同组内产品名的差异部分 #

根据数据模式的固定程度，有两种实现方式：

1. 针对固定模式的品牌直接正则提取

如果每个品牌的产品名格式固定（比如colgate的产品都是品牌名 + 差异词 + 规格），可以直接用REGEXP_EXTRACT结合CASE语句精准提取，以BigQuery为例：

SELECT
  product,
  brand,
  CASE brand
    WHEN 'colgate' THEN REGEXP_EXTRACT(product, r'colgate (.*) 250gr')
    WHEN 'relx' THEN REGEXP_EXTRACT(product, r'relx pod pro (.*) - 1pod')
    WHEN 'jinro' THEN REGEXP_EXTRACT(product, r'soju jinro chamisul (.*) 360ml')
  END AS word
FROM your_table

其他SQL引擎（如MySQL、PostgreSQL）语法类似，只需调整正则函数的名称（比如MySQL用REGEXP_SUBSTR）。

2. 动态提取公共部分（适用于模式不固定的场景）

如果品牌的产品名模式不固定，可以先计算同组内所有产品名的最长公共前缀和后缀，再截取中间的差异部分。以BigQuery为例：

WITH grouped_data AS (
  SELECT
    brand,
    ARRAY_AGG(product) AS product_list
  FROM your_table
  GROUP BY brand
),
common_parts AS (
  SELECT
    brand,
    -- 计算最长公共前缀
    (SELECT STRING_AGG(SUBSTR(p, 1, pos), '')
     FROM UNNEST(GENERATE_ARRAY(1, MIN(LENGTH(p)))) pos
     WHERE ALL(REGEXP_CONTAINS(p, '^' || SUBSTR(p, 1, pos)) FOR p IN product_list)) AS common_prefix,
    -- 计算最长公共后缀
    (SELECT STRING_AGG(SUBSTR(p, LENGTH(p)-pos+1, 1), '' ORDER BY pos DESC)
     FROM UNNEST(GENERATE_ARRAY(1, MIN(LENGTH(p)))) pos
     WHERE ALL(REGEXP_CONTAINS(p, SUBSTR(p, LENGTH(p)-pos+1) || '$') FOR p IN product_list)) AS common_suffix
  FROM grouped_data
)
SELECT
  t.product,
  t.brand,
  TRIM(SUBSTR(t.product, LENGTH(c.common_prefix)+1, LENGTH(t.product)-LENGTH(c.common_prefix)-LENGTH(c.common_suffix))) AS word
FROM your_table t
JOIN common_parts c ON t.brand = c.brand

二、用regexp_contains判断并返回对应值 #

regexp_contains的作用是判断字符串是否匹配指定正则，结合CASE语句可以实现条件化返回：

1. 验证提取结果的有效性

比如检查提取的word是否仅包含字母和空格：

SELECT
  product,
  brand,
  word,
  CASE
    WHEN REGEXP_CONTAINS(word, r'^[a-z ]+$') THEN '有效'
    ELSE '无效'
  END AS word_validity
FROM (
  -- 嵌套上面的提取查询
  SELECT
    product,
    brand,
    CASE brand
      WHEN 'colgate' THEN REGEXP_EXTRACT(product, r'colgate (.*) 250gr')
      WHEN 'relx' THEN REGEXP_EXTRACT(product, r'relx pod pro (.*) - 1pod')
      WHEN 'jinro' THEN REGEXP_EXTRACT(product, r'soju jinro chamisul (.*) 360ml')
    END AS word
  FROM your_table
)

2. 直接通过正则匹配返回word

如果不想按品牌分情况，也可以用regexp_contains判断产品名模式后提取：

SELECT
  product,
  brand,
  CASE
    WHEN REGEXP_CONTAINS(product, r'colgate .* 250gr') THEN REGEXP_EXTRACT(product, r'colgate (.*) 250gr')
    WHEN REGEXP_CONTAINS(product, r'relx pod pro .* - 1pod') THEN REGEXP_EXTRACT(product, r'relx pod pro (.*) - 1pod')
    WHEN REGEXP_CONTAINS(product, r'soju jinro chamisul .* 360ml') THEN REGEXP_EXTRACT(product, r'soju jinro chamisul (.*) 360ml')
  END AS word
FROM your_table

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