Oracle数据库数据编程SQL＜01. 课外关注：数据库查重方法全面详解＞

-- 单列查重
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 1;

-- 多列组合查重
SELECT column1, column2, COUNT(*)
FROM table_name
GROUP BY column1, column2
HAVING COUNT(*) > 1;

2. 使用窗口函数

-- 使用ROW_NUMBER()标记重复行
SELECT *,
       ROW_NUMBER() OVER(PARTITION BY column1, column2 ORDER BY id) AS rn
FROM table_name;

-- 提取重复记录
WITH dup_check AS (
    SELECT *,
           ROW_NUMBER() OVER(PARTITION BY column1, column2 ORDER BY id) AS rn
    FROM table_name
)
SELECT * FROM dup_check WHERE rn > 1;

3. 使用自连接

-- 查找完全重复的行
SELECT a.*
FROM table_name a
JOIN table_name b 
ON a.column1 = b.column1 
AND a.column2 = b.column2
AND a.id <> b.id;  -- 排除自连接

二、高级查重技术

1. 模糊查重（相似度匹配）

1.1 使用SOUNDEX函数（语音相似）

SELECT a.name, b.name
FROM customers a, customers b
WHERE a.id < b.id  -- 避免重复比较
AND SOUNDEX(a.name) = SOUNDEX(b.name);

1.2 使用Levenshtein距离（编辑距离）

-- Oracle需要自定义函数
CREATE OR REPLACE FUNCTION levenshtein( s1 IN VARCHAR2, s2 IN VARCHAR2 )
RETURN NUMBER IS [...];

SELECT a.name, b.name
FROM products a, products b
WHERE a.id < b.id
AND levenshtein(a.name, b.name) <= 3;  -- 允许3个字符差异

1.3 使用UTL_MATCH包（Oracle）

SELECT a.name, b.name, 
       UTL_MATCH.JARO_WINKLER_SIMILARITY(a.name, b.name) AS similarity
FROM customers a, customers b
WHERE a.id < b.id
AND UTL_MATCH.JARO_WINKLER_SIMILARITY(a.name, b.name) > 90;  -- 相似度>90%

2. 大数据量查重优化

2.1 使用HASH值比较

-- 创建哈希列
ALTER TABLE large_table ADD hash_value RAW(16);

-- 更新哈希值（组合关键列）
UPDATE large_table 
SET hash_value = DBMS_CRYPTO.HASH(
    UTL_I18N.STRING_TO_RAW(column1||column2||column3, 'AL32UTF8'),
    2);  -- 2=MD4算法

-- 查找重复哈希
SELECT hash_value, COUNT(*)
FROM large_table
GROUP BY hash_value
HAVING COUNT(*) > 1;

2.2 分区查重

-- 按日期分区查重
SELECT user_id, action_date, COUNT(*)
FROM user_actions
PARTITION (p_2023_01)  -- 指定分区
GROUP BY user_id, action_date
HAVING COUNT(*) > 1;

3. 跨表查重

-- 查找两个表中都存在的记录
SELECT a.*
FROM table1 a
WHERE EXISTS (
    SELECT 1 FROM table2 b
    WHERE a.key_column = b.key_column
);

-- 使用INTERSECT
SELECT key_column FROM table1
INTERSECT
SELECT key_column FROM table2;

三、数据库特定查重方法

1. Oracle查重技术

1.1 使用分析函数

-- 查找重复并保留最新记录
SELECT *
FROM (
    SELECT t.*,
           ROW_NUMBER() OVER(PARTITION BY id_number, name ORDER BY create_date DESC) AS rn
    FROM persons
) 
WHERE rn = 1;  -- 只保留每组最新记录