Oracle SQL 数据清洗详解

适用版本：Oracle Database 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

---

1. 概述

数据清洗保证数据质量[1]：

详细见：Oracle 数据仓库 ETL 详解。

---

2. 去重

2.1 ROWID

DELETE FROM employees WHERE ROWID IN (
  SELECT rid FROM (
    SELECT ROWID rid, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) rn
    FROM employees
  ) WHERE rn > 1
);

2.2 保留最新

DELETE FROM employees WHERE ROWID IN (
  SELECT rid FROM (
    SELECT ROWID rid, 
      ROW_NUMBER() OVER (PARTITION BY email ORDER BY updated_at DESC) rn
    FROM employees
  ) WHERE rn > 1
);

2.3 临时表

CREATE TABLE emp_dedup AS
SELECT * FROM (
  SELECT e.*, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) rn
  FROM employees e
) WHERE rn = 1;

TRUNCATE TABLE employees;
INSERT INTO employees SELECT * FROM emp_dedup;

---

3. NULL 处理

3.1 替换

UPDATE employees SET 
  salary = NVL(salary, 0),
  email = NVL(email, 'unknown@example.com'),
  phone = COALESCE(phone, 'N/A'),
  status = NVL(status, 'ACTIVE');

3.2 删除

DELETE FROM employees WHERE id IS NULL;
DELETE FROM employees WHERE email IS NULL;

3.3 统计

SELECT 
  COUNT(*) AS total,
  COUNT(salary) AS not_null_sal,
  COUNT(*) - COUNT(salary) AS null_sal
FROM employees;

---

4. 字符串

4.1 清洗

UPDATE employees SET 
  name = TRIM(name),                              -- 去空格
  email = LOWER(TRIM(email)),                     -- 小写
  phone = REGEXP_REPLACE(phone, '[^0-9]', ''),    -- 仅数字
  name = REGEXP_REPLACE(name, '\s+', ' ');        -- 多空格

4.2 标准化

UPDATE employees SET 
  name = INITCAP(name),                           -- 首字母大写
  gender = UPPER(gender),
  status = UPPER(status);

4.3 校验

-- 邮箱格式
SELECT * FROM employees 
WHERE NOT REGEXP_LIKE(email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

-- 手机号
SELECT * FROM employees 
WHERE NOT REGEXP_LIKE(phone, '^1[3-9][0-9]{9}$');

详细见：Oracle 正则表达式详解。

---

5. 数值

5.1 类型转换

UPDATE stg_emp SET salary = TO_NUMBER(salary_str, '999999.99')
WHERE REGEXP_LIKE(salary_str, '^[0-9]+\.?[0-9]*$');

5.2 范围

-- 异常值
SELECT * FROM employees WHERE salary < 0;
SELECT * FROM employees WHERE salary > 1000000;

-- 修正
UPDATE employees SET salary = NULL WHERE salary < 0;

5.3 四舍五入

UPDATE employees SET salary = ROUND(salary, 2);

---

6. 日期

6.1 转换

UPDATE stg_emp SET hire_date = TO_DATE(hire_str, 'YYYY-MM-DD')
WHERE hire_str IS NOT NULL;

-- 多格式
UPDATE stg_emp SET hire_date = 
  CASE 
    WHEN REGEXP_LIKE(hire_str, '^\d{4}-\d{2}-\d{2}$') THEN TO_DATE(hire_str, 'YYYY-MM-DD')
    WHEN REGEXP_LIKE(hire_str, '^\d{2}/\d{2}/\d{4}$') THEN TO_DATE(hire_str, 'MM/DD/YYYY')
    ELSE NULL
  END;

6.2 范围

-- 异常
SELECT * FROM employees WHERE hire_date > SYSDATE;
SELECT * FROM employees WHERE hire_date < DATE '1900-01-01';

-- 修正
UPDATE employees SET hire_date = NULL WHERE hire_date > SYSDATE;

---

7. 引用完整性

7.1 检查

-- 孤儿记录
SELECT * FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM departments d WHERE d.id = e.dept_id);

7.2 修正

-- 设置 NULL
UPDATE employees SET dept_id = NULL
WHERE NOT EXISTS (SELECT 1 FROM departments d WHERE d.id = employees.dept_id);

-- 删除
DELETE FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM departments d WHERE d.id = e.dept_id);

-- 默认部门
UPDATE employees SET dept_id = 99
WHERE NOT EXISTS (SELECT 1 FROM departments d WHERE d.id = employees.dept_id);

---

8. 业务规则

8.1 检查

-- 薪水范围
SELECT * FROM employees WHERE salary < 1000 OR salary > 100000;

-- 邮箱重复
SELECT email, COUNT(*) FROM employees GROUP BY email HAVING COUNT(*) > 1;

-- 日期逻辑
SELECT * FROM employees WHERE hire_date > termination_date;

8.2 修正

-- 薪水
UPDATE employees SET salary = 1000 WHERE salary < 1000;

-- 日期
UPDATE employees SET termination_date = NULL 
WHERE hire_date > termination_date;

---

9. 数据合并

9.1 同表

-- 合并重复记录
MERGE INTO employees target
USING (
  SELECT MIN(id) AS keep_id, email, MAX(name) AS name, MAX(salary) AS salary
  FROM employees
  GROUP BY email
  HAVING COUNT(*) > 1
) source
ON (target.id = source.keep_id)
WHEN MATCHED THEN UPDATE SET 
  target.name = source.name,
  target.salary = source.salary;

-- 删除重复
DELETE FROM employees WHERE id NOT IN (
  SELECT MIN(id) FROM employees GROUP BY email
);

9.2 跨表

-- 合并
INSERT INTO employees (id, name, email)
SELECT id, name, email FROM new_employees
WHERE NOT EXISTS (SELECT 1 FROM employees WHERE email = new_employees.email);

详细见：Oracle MERGE 语句详解。

---

10. 数据标准化

10.1 编码

-- 性别
UPDATE employees SET gender = 
  CASE UPPER(gender)
    WHEN 'M' THEN 'M'
    WHEN 'MALE' THEN 'M'
    WHEN 'F' THEN 'F'
    WHEN 'FEMALE' THEN 'F'
    ELSE 'U'
  END;

-- 状态
UPDATE employees SET status = 
  CASE UPPER(status)
    WHEN 'A' THEN 'ACTIVE'
    WHEN 'I' THEN 'INACTIVE'
    WHEN 'ACTIVE' THEN 'ACTIVE'
    WHEN 'INACTIVE' THEN 'INACTIVE'
    ELSE 'ACTIVE'
  END;

10.2 单位

-- 货币
UPDATE sales SET amount_usd = amount * 
  CASE currency 
    WHEN 'CNY' THEN 0.14
    WHEN 'EUR' THEN 1.1
    ELSE 1
  END;

---

11. 数据质量报告

SELECT 
  'TOTAL' AS metric, COUNT(*) AS value FROM employees
UNION ALL
  SELECT 'NULL_EMAIL', COUNT(*) FROM employees WHERE email IS NULL
UNION ALL
  SELECT 'DUP_EMAIL', COUNT(*) - COUNT(DISTINCT email) FROM employees
UNION ALL
  SELECT 'INVALID_EMAIL', COUNT(*) FROM employees 
    WHERE NOT REGEXP_LIKE(email, '^[A-Za-z0-9._%+-]+@')
UNION ALL
  SELECT 'NULL_SALARY', COUNT(*) FROM employees WHERE salary IS NULL
UNION ALL
  SELECT 'INVALID_SALARY', COUNT(*) FROM employees WHERE salary < 0;

---

12. 应用场景

12.1 ETL

- 数据加载
- 清洗
- 转换
- 加载到仓库

12.2 数据迁移

- 系统升级
- 平台迁移
- 清洗

12.3 数据治理

- 质量
- 标准化
- 监控

详细见：Oracle 数据仓库 ETL 详解。

---

13. 自动化

13.1 过程

CREATE OR REPLACE PROCEDURE clean_employees IS
  v_count NUMBER;
BEGIN
  -- 去重
  DELETE FROM employees WHERE ROWID IN (
    SELECT rid FROM (
      SELECT ROWID rid, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) rn
      FROM employees
    ) WHERE rn > 1
  );
  
  -- NULL
  UPDATE employees SET 
    salary = NVL(salary, 0),
    email = NVL(email, 'unknown@example.com');
  
  -- 标准化
  UPDATE employees SET 
    name = TRIM(INITCAP(name)),
    email = LOWER(TRIM(email));
  
  COMMIT;
END;
/

13.2 调度

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'clean_employees',
    job_type => 'PLSQL_BLOCK',
    job_action => 'BEGIN clean_employees; END;',
    repeat_interval => 'FREQ=WEEKLY',
    enabled => TRUE
  );
END;
/

---

14. 性能

14.1 批量

-- BULK
DECLARE
  TYPE id_tab IS TABLE OF NUMBER;
  v_ids id_tab;
BEGIN
  SELECT id BULK COLLECT INTO v_ids FROM employees WHERE salary IS NULL;
  
  FORALL i IN 1..v_ids.COUNT
    UPDATE employees SET salary = 0 WHERE id = v_ids(i);
END;
/

详细见：Oracle BULK COLLECT 与 FORALL 详解。

14.2 索引

- 临时禁用
- 加载后重建

---

15. 最佳实践

1. 备份：先备份
2. 分批：大数据
3. 测试：先验证
4. 日志：记录
5. 可逆：可回滚
6. 校验：数据质量
7. 自动化：调度
8. 监控：质量
9. 文档：流程
10. 审计：变更

---

16. 参考资料

[1] Oracle Database Data Warehousing Guide 19c, “ETL” https://docs.oracle.com/en/database/oracle/oracle-database/19/dwh/