GaussDB数据库SQL系列：数据去重技术全解析

一、数据去重的重要性

在数据库应用中，数据重复问题普遍存在且影响深远：

数据准确性：重复记录会导致统计偏差（如订单量多算）
查询效率：重复数据增加I/O消耗，降低查询性能
存储成本：冗余数据占用额外存储空间
数据分析：影响机器学习训练质量，导致模型偏差
以电商订单表为例，同一用户可能因网络重试产生多笔相同订单，需通过去重保留有效数据。

二、常用去重方法及实战案例

DISTINCT关键字（基础去重）

-- 查询不重复的城市列表
SELECT DISTINCT city FROM orders;

-- 多列组合去重
SELECT DISTINCT user_id, product_id 
FROM purchase_log;

特点：自动处理所有选中列的重复值，但无法指定保留规则。

GROUP BY分组去重

-- 获取最新订单（保留每个用户的最后订单）
SELECT user_id, MAX(order_date) AS last_order
FROM orders
GROUP BY user_id;

进阶应用：结合聚合函数实现复杂去重逻辑。

窗口函数（高级去重）

-- 使用ROW_NUMBER保留第一条记录
WITH ranked_orders AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC) AS rn
  FROM orders
)
DELETE FROM ranked_orders WHERE rn > 1;

优势：支持复杂排序规则，可精细控制保留策略。

DELETE语句精准去重

-- 使用ROWID物理删除重复记录
DELETE FROM (
  SELECT t.*, 
         ROWID AS rid,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY create_time) AS rn
  FROM users t
)
WHERE rn > 1;

注意：需提前备份数据，建议在事务中执行。

三、高阶去重技巧

临时表分步处理

-- 创建临时表保存去重结果
CREATE TEMP TABLE temp_orders AS
SELECT DISTINCT * FROM original_orders;

-- 清空原表后插入
TRUNCATE TABLE original_orders;
INSERT INTO original_orders SELECT * FROM temp_orders;

动态去重（结合业务规则）

-- 根据时间窗口去重（保留最近30天数据）
DELETE FROM logs
WHERE log_id IN (
  SELECT log_id 
  FROM (
    SELECT log_id,
           ROW_NUMBER() OVER (PARTITION BY device_id 
                             ORDER BY log_time DESC) AS rn
    FROM logs
    WHERE log_time < SYSDATE - 30
  ) 
  WHERE rn > 1
);

增量去重策略

-- 仅处理新增数据（假设last_processed_time为处理时间戳）
INSERT INTO dedup_table
SELECT * FROM new_data
MINUS SELECT * FROM dedup_table;

四、性能优化建议

索引策略：在分区字段和排序字段建立组合索引
批量处理：使用分页查询避免大事务
并行处理：对TB级数据启用并行查询
存储优化：使用列存表加速分析型去重
示例优化：

-- 创建优化索引
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date DESC);

-- 分批次删除
DECLARE
  batch_size INT := 10000;
BEGIN
  LOOP
    DELETE FROM orders o
    WHERE ROWID IN (
      SELECT rid FROM (
        SELECT ROWID,
               ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) rn
        FROM orders
      ) WHERE rn > 1
      AND ROWNUM <= batch_size
    );
    EXIT WHEN SQL%ROWCOUNT = 0;
    COMMIT;
  END LOOP;
END;

五、典型应用场景

场景推荐方法典型SQL模式
用户行为去重 ROW_NUMBER+PARTITION BY 按用户ID分组保留最新行为
订单清洗 DELETE+ROW_NUMBER 按订单号分组保留最大订单ID
日志归并 GROUP BY+聚合函数按设备ID分组统计最大值/最小值
数据同步去重 MINUS/MERGE 源表与目标表的差异对比

六、避坑指南

误删风险：操作前务必开启审计日志，建议使用FLASHBACK功能
性能陷阱：避免全表扫描，优先使用索引过滤
空值处理：注意NULL值的特殊处理（DISTINCT会合并NULL）
锁竞争：大表操作选择业务低峰期，使用ONLINE DDL工具

总结

GaussDB提供了丰富的去重手段，从基础的DISTINCT到复杂的窗口函数，开发者应根据：

数据规模（GB/ TB级）
业务规则复杂度
实时性要求
系统资源状况
选择合适方案。

posted @ 2025-05-26 16:23 喜酱喜酱阅读(6) 评论(0) 收藏举报

刷新页面返回顶部

GaussDB