当前位置：首页 > ds >正文

数据库数据清洗、预处理与质量监控、数据质量的核心概念

ds 2025/5/14 5:47:48

数据库数据清洗、预处理与质量监控、数据质量的核心概念

准确性 (Accuracy)

准确性指数据正确反映其所描述的实体或事件真实状况的程度。准确的数据应当与现实世界中的实际情况一致。

一致性 (Consistency)

一致性指数据在不同表、系统或时间点之间保持逻辑上一致的程度。一致性确保数据遵循业务规则和关系约束。

数据清洗与预处理技术

1. 处理缺失值

-- 识别缺失值
SELECT COUNT(*) FROM customers WHERE phone IS NULL;-- 处理缺失值
UPDATE customers 
SET phone = 'Unknown' 
WHERE phone IS NULL;-- 或者删除包含关键缺失值的记录
DELETE FROM orders 
WHERE customer_id IS NULL;

2. 处理重复数据

-- 识别重复记录
SELECT email, COUNT(*) 
FROM customers 
GROUP BY email 
HAVING COUNT(*) > 1;-- 删除重复记录 (保留一条)
WITH duplicates AS (SELECT email, ROW_NUMBER() OVER(PARTITION BY email ORDER BY id) AS rnFROM customers
)
DELETE FROM duplicates WHERE rn > 1;

3. 标准化数据格式

-- 统一日期格式
UPDATE transactions 
SET transaction_date = TO_DATE(transaction_date, 'YYYY-MM-DD')
WHERE transaction_date ~ '^\d{4}-\d{2}-\d{2}$';-- 统一电话号码格式
UPDATE customers 
SET phone = REGEXP_REPLACE(phone, '[^0-9]', '');

4. 处理异常值

-- 识别异常交易金额
SELECT * FROM transactions 
WHERE amount < 0 OR amount > 100000;-- 修正异常值 (根据业务规则)
UPDATE transactions 
SET amount = 0 
WHERE amount < 0;

数据质量监控

1. 创建数据质量规则表

CREATE TABLE data_quality_rules (rule_id INT PRIMARY KEY,rule_name VARCHAR(100),rule_description VARCHAR(500),check_query TEXT,threshold INT,severity VARCHAR(20)
);-- 示例规则
INSERT INTO data_quality_rules VALUES
(1, 'Null Customer Names', 'Customer names should not be null', 'SELECT COUNT(*) FROM customers WHERE customer_name IS NULL', 0, 'High'),
(2, 'Negative Order Quantities', 'Order quantities should not be negative', 'SELECT COUNT(*) FROM order_items WHERE quantity < 0', 0, 'High'),
(3, 'Invalid Email Formats', 'Emails should follow standard format', 'SELECT COUNT(*) FROM customers WHERE email !~ ''^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+[.][A-Za-z]+$''', 5, 'Medium');

2. 自动化质量检查

-- 创建数据质量检查存储过程
CREATE OR REPLACE PROCEDURE run_data_quality_checks()
LANGUAGE plpgsql
AS $$
DECLARErule_record RECORD;violation_count INT;
BEGINFOR rule_record IN SELECT * FROM data_quality_rules LOOPEXECUTE rule_record.check_query INTO violation_count;IF violation_count > rule_record.threshold THENINSERT INTO data_quality_violations (rule_id, violation_count, check_date)VALUES (rule_record.rule_id, violation_count, CURRENT_DATE);-- 可以添加通知逻辑RAISE NOTICE 'Data quality violation: % - % violations found', rule_record.rule_name, violation_count;END IF;END LOOP;
END;
$$;

案例分析

案例: 电商平台数据清洗

问题描述:

客户表中有重复的电子邮件
订单表中的某些价格与产品表中的价格不一致
客户地址格式不统一

解决方案:

-- 1. 处理重复客户
WITH ranked_customers AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY email ORDER BY created_at) AS rnFROM customers
)
DELETE FROM customers 
WHERE id IN (SELECT id FROM ranked_customers WHERE rn > 1);-- 2. 修复价格不一致问题
UPDATE order_items oi
SET unit_price = p.price
FROM products p
WHERE oi.product_id = p.id AND oi.unit_price != p.price;-- 3. 标准化地址
UPDATE customers
SET address = INITCAP(TRIM(address)),postal_code = REGEXP_REPLACE(postal_code, '[^0-9]', '');

一致性验证示例

-- 检查订单总价是否等于各项目总和
SELECT o.order_id, o.total_amount, SUM(oi.quantity * oi.unit_price) AS calculated_amount,o.total_amount - SUM(oi.quantity * oi.unit_price) AS discrepancy
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id, o.total_amount
HAVING ABS(o.total_amount - SUM(oi.quantity * oi.unit_price)) > 0.01;

准确性验证示例

-- 检查产品价格是否在合理范围内
SELECT product_id, product_name, price
FROM products
WHERE price < 0 OR price > 10000;  -- 假设10000是合理上限-- 检查客户年龄是否合理
SELECT customer_id, birth_date, EXTRACT(YEAR FROM AGE(birth_date)) AS age
FROM customers
WHERE EXTRACT(YEAR FROM AGE(birth_date)) < 18 OR EXTRACT(YEAR FROM AGE(birth_date)) > 120;