如何在SQL/PostgreSQL/MySQL或Kettle中实现分隔编码转映射字符串？

以下提供两种可直接落地的实现方式： #

方案1：PostgreSQL SQL语句实现 #

适合数据已经落库到PostgreSQL，或支持在入库后执行SQL转换的场景，无需额外开发Kettle逻辑：

-- 假设原数据表为source_table，编码映射表为code_map
SELECT 
    string_agg(COALESCE(m.value, '未知'), ',' ORDER BY ord) AS new_value
    -- 可带上原表其他需要输出的字段
FROM 
    source_table t,
    unnest(string_to_array(t.value, '→')) WITH ORDINALITY AS arr(code, ord)
LEFT JOIN 
    code_map m ON arr.code::INT = m.id
GROUP BY 
    t.主键字段, t.value -- 按原表唯一行标识分组即可，无主键可用pg内置ctid代替

逻辑说明：

1. 用string_to_array将「→」分隔的字符串拆分为数组
2. 用unnest将数组拆分为独立行，WITH ORDINALITY保留编码的原始顺序
3. 左连编码映射表获取对应值，COALESCE可处理无匹配编码的默认值
4. 按原始行分组，用string_agg按原始顺序拼接为逗号分隔的字符串

方案2：Pentaho Data Integration（Kettle）组件实现 #

适合不想写SQL、偏好可视化组件处理的场景，步骤如下：

• 1. 接入源数据：添加「表输入」组件，读取原表的value字段及其他需要的业务字段
• 2. 拆分字段：添加「拆分字段为行」组件，拆分字段选择value，分隔符填写→，生成新字段命名为code，勾选「生成序列字段」用于记录编码原始顺序
• 3. 映射匹配：
  • 若映射表数据固定且数量少：直接用「值映射」组件，手动录入所有编码与映射值的对应关系
  • 若映射表数据量大或会动态更新：新增「表输入」组件读取编码映射表，再通过「流查找」组件，将拆分得到的code与映射表的ID关联，获取对应的映射值
• 4. 分组拼接：添加「分组」组件，按原行唯一标识（如原表主键、原始value字段）分组，聚合规则选择「连接字符串」，待连接字段选择映射后的 value，分隔符填写,，同时按之前生成的序列字段排序，保证拼接顺序与原始一致
• 5. 输出结果：将处理后的字段写入PostgreSQL目标表即可

注意事项 #

两种方案均需提前处理编码无匹配的场景，可根据业务需求设置默认值，避免拼接结果出现空值。

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