如何在BigQuery/SQL中按条件实现字符串动态拼接增减列？

原始数据 #

column1       column2
product1    action_of_interest1
product2    action_of_interest1
product3    random_action
product1    action_of_interest2

需求说明 #

需要新增new_column列，规则如下：

• 当column2的值为action_of_interest1时，将对应column1的内容追加到new_column的逗号分隔字符串中
• 当column2的值为action_of_interest2时，从new_column的字符串中移除对应column1的内容
• 当column2的值为random_action时，保持new_column内容不变

期望结果 #

column1          column2                         new_column
product1     action_of_interest1                   product1
product2     action_of_interest1                  product1,product2
product3     random_action                    product1,product2
product1     action_of_interest2                  product2

解决方案 #

直接操作字符串维护动态集合会很繁琐，用数组来管理当前产品集合是更高效的方式。以下是BigQuery的实现代码：

WITH ordered_data AS (
  -- 给数据添加行号保证处理顺序（如果原表有天然排序字段比如时间戳，替换此处排序逻辑即可）
  SELECT 
    column1, 
    column2,
    ROW_NUMBER() OVER () AS row_num
  FROM your_table
),
running_state AS (
  -- 处理第二行及以后的记录，基于上一行的数组状态更新
  SELECT
    column1,
    column2,
    row_num,
    ARRAY(
      SELECT DISTINCT val
      FROM UNNEST(
        CASE column2
          WHEN 'action_of_interest1' THEN ARRAY_CONCAT(LAG(current_products) OVER (ORDER BY row_num), [column1])
          WHEN 'action_of_interest2' THEN ARRAY_REMOVE(LAG(current_products) OVER (ORDER BY row_num), column1)
          ELSE LAG(current_products) OVER (ORDER BY row_num)
        END
      ) AS val
    ) AS current_products
  FROM ordered_data
  WHERE row_num > 1
  
  UNION ALL
  
  -- 初始化第一行的数组状态
  SELECT
    column1,
    column2,
    row_num,
    [column1] AS current_products
  FROM ordered_data
  WHERE row_num = 1
  
  ORDER BY row_num
)
-- 将数组转换为逗号分隔的字符串
SELECT
  column1,
  column2,
  ARRAY_TO_STRING(current_products, ',') AS new_column
FROM running_state
ORDER BY row_num;

代码说明 #

1. ordered_data：给每条记录分配行号，确保处理顺序和原始数据一致——BigQuery的表本身没有固定顺序，必须明确排序规则，有天然排序字段的话直接替换ROW_NUMBER() OVER ()即可。
2. running_state：用数组维护当前产品集合：
   • 第一行单独初始化，把第一个产品放入数组
   • 后续行通过LAG()获取上一行的数组状态，根据动作类型执行追加、移除或保持操作
   • 用UNNEST+DISTINCT避免重复添加同一个产品（如果不需要去重，删掉DISTINCT即可）
3. 最后用ARRAY_TO_STRING把数组转换为逗号分隔的字符串，得到目标列。

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