企业官网建设流程全解析

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) );

典型数据示例：

注意：实际业务中可能还包含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;

这样会得到：

但这个方法有三大缺陷：

1. 无法控制地址部件的顺序（可能变成"深圳市 广东省"）
2. 无法处理NULL值导致的额外空格
3. 无法自定义不同部件间的分隔符

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. 扩展应用场景

这个技术不仅适用于地址合并：

1. 生成导出报表：将多行订单明细合并为单个单元格

   SELECT order_id, string_agg(product_name || '×' || quantity, ', ') AS items FROM order_details GROUP BY order_id;

2. API数据聚合：响应前端时需要将标签数组合并

   SELECT blog_id, string_agg('"' || tag_name || '"', ', ') AS tags_json FROM blog_tags GROUP BY blog_id;

3. 日志分析：合并同一用户的多个操作记录

   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避免截断。