如何简化SQL中服务名称字段的脏数据清洗逻辑？

原始查询输出 #

date    customer_name   service_name    price_paid
2021-01-01  Andrew      Cable TV        5000
2021-02-02  Brad        cabletv         5000
2021-03-03  Charlie     Cable TV        5000
2021-02-05  Dan         ISP             6000
2021-02-18  Eric        ISP             6000
2021-10-09  Felix       ISP             6000
2021-09-10  Gerald      isp             6000
2022-03-10  Hubert      Cable TV        5000
2022-04-12  Isaac       i.s.p           6000
2022-04-15  Jason       ISp 
2022-05-23  Karen       Cable T.V   
2022-06-23  Leah        ISP             6000
2022-05-17  Marie       Cable TV        5000
2022-06-11  Norman      ISP             6000

上述结果中service_name字段格式混乱，存在大小写不一致、标点冗余等问题。目前采用多层replace嵌套的方式清洗，但数据量较大时，这种逐个替换的方式会变得繁琐且难以维护。

当前使用的清洗代码 #

sql_query = """
SELECT 
  strftime('%Y', date) 'year',
  strftime('%m', date) 'month',
  strftime('%d', date) 'date',
  customer_name, 
  replace(replace(replace(replace(replace(service_name, 'cabletv', 'Cable TV'), 'Cable T.V', 'Cable TV'), 'i.s.p', 'ISP'), 'isp', 'ISP'),'ISp', 'ISP') service_name,
  COALESCE(price_paid, (SELECT avg(price_paid) FROM df)) as 'price_paid'
FROM df
"""

sql_run(sql_query)

高效替代方案 #

1. 正则+CASE语句统一匹配 #

利用字符串标准化（去除标点、统一大小写）结合CASE语句，一次性匹配所有变体，无需多层嵌套替换。以SQLite为例：

SELECT 
  strftime('%Y', date) 'year',
  strftime('%m', date) 'month',
  strftime('%d', date) 'date',
  customer_name,
  CASE
    WHEN UPPER(REPLACE(service_name, '.', '')) LIKE '%CABLE%TV%' THEN 'Cable TV'
    WHEN UPPER(REPLACE(service_name, '.', '')) = 'ISP' THEN 'ISP'
    ELSE service_name
  END AS service_name,
  COALESCE(price_paid, (SELECT avg(price_paid) FROM df)) as 'price_paid'
FROM df

该方法通过先去除所有点号、转大写，再模糊匹配关键词，覆盖所有拼写变体，逻辑清晰且易于扩展。

2. 映射表关联查询 #

对于枚举值明确的场景，可创建一个服务名称映射表，将所有变体与标准名称关联，通过JOIN实现批量替换：
首先创建并初始化映射表：

CREATE TABLE service_mapping (
  messy_value TEXT PRIMARY KEY,
  standard_name TEXT NOT NULL
);

INSERT INTO service_mapping VALUES
('cabletv', 'Cable TV'),
('Cable T.V', 'Cable TV'),
('i.s.p', 'ISP'),
('isp', 'ISP'),
('ISp', 'ISP'),
('Cable TV', 'Cable TV'),
('ISP', 'ISP');

然后关联查询：

SELECT 
  strftime('%Y', date) 'year',
  strftime('%m', date) 'month',
  strftime('%d', date) 'date',
  d.customer_name,
  COALESCE(m.standard_name, d.service_name) AS service_name,
  COALESCE(d.price_paid, (SELECT avg(price_paid) FROM df)) as 'price_paid'
FROM df d
LEFT JOIN service_mapping m ON d.service_name = m.messy_value;

若需支持模糊匹配（如包含关键词的变体），可修改JOIN条件为UPPER(d.service_name) LIKE UPPER('%' || m.messy_value || '%')，大数据量下建议为映射表的messy_value字段创建索引优化性能。

3. 自定义标准化函数 #

如果使用的SQL环境支持自定义函数（如Python+SQLite、PostgreSQL），可将清洗逻辑封装为函数，简化主查询：
以Python+SQLite为例，注册自定义函数：

import sqlite3

def standardize_service(name):
    if not name:
        return None
    cleaned = name.replace('.', '').strip().upper()
    if 'CABLE' in cleaned and 'TV' in cleaned:
        return 'Cable TV'
    elif cleaned == 'ISP':
        return 'ISP'
    return name

conn = sqlite3.connect('your_database.db')
conn.create_function('standardize_service', 1, standardize_service)

# 简化后的查询
sql_query = """
SELECT 
  strftime('%Y', date) 'year',
  strftime('%m', date) 'month',
  strftime('%d', date) 'date',
  customer_name,
  standardize_service(service_name) AS service_name,
  COALESCE(price_paid, (SELECT avg(price_paid) FROM df)) as 'price_paid'
FROM df
"""

这种方式将所有清洗逻辑集中在函数内，后续新增变体只需修改函数，主查询保持简洁。

内容的提问来源于stack exchange，提问作者Kezia Trifena Chandra