PostgreSQL数据清洗实战：用string_agg合并地址字段，我这样整理混乱的客户信息

PostgreSQL数据清洗实战用string_agg合并地址字段我这样整理混乱的客户信息客户信息表中的地址字段分散是个常见痛点。想象一下同一客户的省、市、详细地址分散在不同行导出Excel时地址被拆得七零八落业务部门每次都要手动拼接。作为数据工程师我最近用PostgreSQL的string_agg函数彻底解决了这个问题——现在客户地址能自动合并成广东省深圳市南山区科技园路1号这样的标准格式。1. 问题场景当客户地址变成拼图游戏上周市场部投诉CRM导出的客户清单没法用——同一个客户的地址分散在3条记录里。原始数据表结构是这样的CREATE TABLE customer_address ( customer_id INT, address_type VARCHAR(10), -- 值可能是province,city,detail address_value VARCHAR(100) );典型数据示例customer_idaddress_typeaddress_value1001province广东省1001city深圳市1001detail南山区科技园路1号1002province浙江省1002city杭州市注意实际业务中可能还包含NULL值、空字符串或重复记录这些都会影响最终合并效果2. 基础解法用GROUP BY和string_agg拼接字符串最直接的解决方案是先用CASE WHEN转换字段类型再用string_agg合并SELECT customer_id, string_agg( CASE WHEN address_type province THEN address_value WHEN address_type city THEN address_value WHEN address_type detail THEN address_value ELSE NULL END, ) AS full_address FROM customer_address GROUP BY customer_id;这样会得到customer_idfull_address1001广东省 深圳市 南山区科技园路1号1002浙江省 杭州市但这个方法有三大缺陷无法控制地址部件的顺序可能变成深圳市 广东省无法处理NULL值导致的额外空格无法自定义不同部件间的分隔符3. 进阶方案带排序和条件过滤的智能合并更专业的做法是为每个地址部件设置排序权重并过滤空值SELECT customer_id, string_agg( address_value, ORDER BY CASE address_type WHEN province THEN 1 WHEN city THEN 2 WHEN detail THEN 3 ELSE 4 END ) AS full_address FROM customer_address WHERE address_value IS NOT NULL AND address_value ! GROUP BY customer_id;关键改进点ORDER BY子句确保地址按省→市→详细顺序排列WHERE条件排除NULL和空字符串仍然使用空格作为分隔符但可以替换为chr(10)获得换行效果4. 生产环境中的实战技巧在实际项目中我还总结了这些经验4.1 处理特殊分隔符需求当需要更复杂的分隔符时如省市区用空格最后加邮编SELECT customer_id, string_agg( CASE WHEN address_type postcode THEN (邮编 || address_value) ELSE address_value END, CASE WHEN address_type IN (province,city) THEN ELSE END ) AS full_address FROM customer_address GROUP BY customer_id;4.2 多层嵌套聚合对于需要先按类型合并再整体合并的场景WITH type_agg AS ( SELECT customer_id, string_agg(address_value, ORDER BY address_type) AS partial_address FROM customer_address GROUP BY customer_id, address_type ) SELECT customer_id, string_agg(partial_address, chr(10)) AS formatted_address FROM type_agg GROUP BY customer_id;4.3 性能优化备忘录处理百万级数据时为customer_id和address_type创建复合索引考虑先用CTE过滤无效数据再聚合对于固定模式地址可以改用concat_ws函数5. 扩展应用场景这个技术不仅适用于地址合并生成导出报表将多行订单明细合并为单个单元格SELECT order_id, string_agg(product_name || × || quantity, , ) AS items FROM order_details GROUP BY order_id;API数据聚合响应前端时需要将标签数组合并SELECT blog_id, string_agg( || tag_name || , , ) AS tags_json FROM blog_tags GROUP BY blog_id;日志分析合并同一用户的多个操作记录SELECT user_id, string_agg( action_time || || action_type, chr(10) ORDER BY action_time ) AS action_sequence FROM user_logs GROUP BY user_id;最近一次数据迁移项目中这个技巧帮我们减少了90%的手动拼接工作。有个容易忽略的细节当合并后的字符串超长时可能需要设置SET max_length_for_sort_data 1000000避免截断。