SQL中迭代处理逗号分隔值实现指定表结构转换的问题咨询

整体实现逻辑 #

整个需求的处理分为三个核心步骤：

• 拆分逗号分隔的Bar字段，把一行拆成多行，每行对应Bar中的一个值，同时保留该行原有的Foo和Baz字段值
• 把拆分得到的单个Bar值和Baz字段拼接，得到BarBaz字段
• 按BarBaz字段分组，将同组内的Foo值去重后用逗号拼接，得到最终结果

不同数据库实现代码 #

不同数据库对字符串拆分的语法支持不同，以下是主流数据库的实现示例：

Spark SQL / Hive #

直接用内置的split+explode函数完成拆分：

SELECT
  CONCAT(single_bar, Baz) AS BarBaz,
  CONCAT_WS(',', COLLECT_SET(Foo)) AS Foo
FROM 
  source_table
  LATERAL VIEW EXPLODE(SPLIT(Bar, ',')) t AS single_bar
GROUP BY
  CONCAT(single_bar, Baz)
ORDER BY
  BarBaz

PostgreSQL #

用string_to_array将字符串转数组，再用unnest拆成多行：

SELECT
  CONCAT(single_bar, Baz) AS BarBaz,
  STRING_AGG(DISTINCT Foo, ',' ORDER BY Foo) AS Foo
FROM 
  source_table,
  UNNEST(STRING_TO_ARRAY(Bar, ',')) AS t(single_bar)
GROUP BY
  CONCAT(single_bar, Baz)
ORDER BY
  BarBaz

MySQL #

MySQL没有原生的拆分函数，用递归CTE实现拆分逻辑：

WITH RECURSIVE split_cte AS (
  -- 初始层：提取第一个逗号前的元素
  SELECT 
    Foo,
    Baz,
    SUBSTRING_INDEX(Bar, ',', 1) AS single_bar,
    SUBSTRING(Bar, LENGTH(SUBSTRING_INDEX(Bar, ',', 1)) + 2) AS remaining_bar
  FROM source_table
  WHERE Bar IS NOT NULL AND Bar != ''
  UNION ALL
  -- 递归层：循环拆分剩余的字符串
  SELECT 
    Foo,
    Baz,
    SUBSTRING_INDEX(remaining_bar, ',', 1) AS single_bar,
    SUBSTRING(remaining_bar, LENGTH(SUBSTRING_INDEX(remaining_bar, ',', 1)) + 2) AS remaining_bar
  FROM split_cte
  WHERE remaining_bar IS NOT NULL AND remaining_bar != ''
)
SELECT 
  CONCAT(single_bar, Baz) AS BarBaz,
  GROUP_CONCAT(DISTINCT Foo ORDER BY Foo SEPARATOR ',') AS Foo
FROM split_cte
GROUP BY CONCAT(single_bar, Baz)
ORDER BY BarBaz

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