电脑技术学习

如何删除数据库中的冗余数据（翻译）

dn001

作为数据库的开发者，我们经常面临着要找出及删除数据库中冗余数据的任务，如果数据库中有大量的冗余数据（占总数的百分比太多），数据的精确性和可靠性将受到影响，同时也影响着数据库的性能，那么如何解决这个问题呢？下面我将探讨关于这个问题的这个解决方案，oracle也为我们提供了一个解决方案，但是Ｏｒａｃｌｅ提供的解决方案不够完美，遇到大批量数据那个解决方案工作起来很慢

应该怎么删除冗余数据呢？
在这里我们应用一个ＰＬ／ＳＱｌ方案（一个自定义的存储过程）或者一个ＳＱＬ语句的解决方案（使用一个分析的函数ＲＡＮＫ（）和一个嵌套的子查询）来消除冗余数据然后控制应该保留的记录


什么是冗余数据？
冗余数据就是一个数据表中，这个表中的行包含了一些相同的值，这些值理论上来说应该是唯一的（这些值一般来说能确定一条记录）例如，像社会保险号，姓与名的集合．那么我们把这么含有相同信息的行中包含的数据叫做冗余数据，现在所有的数据库表中都有主键约束，主键中记录了一行记录中的唯一值，从数据库的角度来看，每一行都是唯一的，但是从我们用户角度看来，这些记录都是相同的记录，因为它们都包含相同的键值（Ｆｉｒｓｔ　Ｎａｍｅ　＋　Ｌａｓｔ　Ｎａｍｅ），即使他们有不同的主键
IDLast Name;;;;First Name City;Phone
---- --------------- ---------- --------------- ----------
1005 Krieger Jeff;;;;San Ramon;;;;9252997100
1012 Krieger Jeff;;;;San Ramon;;;;9252997100
1017 Krieger Jeff;;;;San Ramon;;;;9252997100

那么这些冗余数据是怎么出现的那？通常有两种情况：１.从不同的表中加载或者合并数据

通过图形化的用户接口来输入数据，然后由计算机来生成一个唯一的键，并做为这一条记录的主键

那么怎样找到冗余数据呢？让我们来创建一个叫作Ｃｕｓｔｏｍｅｒ　的表并向其中加入冗余数据，看表１，正如你所看到的，我们并没有在这个表上做什么限制来防止冗余数据，下面这么代码创建了一个唯一约束，来防止冗余数据的生成
SQL
Listing 1. 创建Ｃｕｓｔｏｍｅｒ表
这个表中我们故意加入了冗余数据
DROP TABLE Customers CASCADE CONSTRAINTS;

CREATE TABLE Customers(
Id INTEGER NOT NULL,
LastName VARCHAR2(15) NOT NULL,
FirstName VARCHAR2(10),
Address VARCHAR2(20),
City VARCHAR2(15),
State CHAR(2),
Zip VARCHAR2(10),
Phone VARCHAR2(10),
RecDate DATE,
CONSTRAINT Customers_PK
PRIMARY KEY (ID))
TABLESPACE TALLYDATA;


INSERT INTO Customers
VALUES (1000, 'Bradley', 'Tom', '2450 3rd Str. #12',
'San Francisco', 'CA', '94114', '4156679230',
TO_DATE('01-JAN-2000', 'DD-MON-YYYY'));

INSERT INTO Customers
VALUES (1001, 'Stone', 'Tony', '12 Main St. Apt. 3',
'Oakland', 'CA', '94342', '5104562881',
TO_DATE('12-MAR-2001', 'DD-MON-YYYY'));

INSERT INTO Customers
VALUES (1002, 'Chang', 'Jim', '425 26th Ave.',
'Seattle', 'WA', '98103', '8182454400',
TO_DATE('15-JUN-2000', 'DD-MON-YYYY'));

INSERT INTO Customers
VALUES (1003, 'Loney', 'Julie', '12 Keith St.',
'Castro Valley', 'CA', '94546', '5103300721',
TO_DATE('22-NOV-2000', 'DD-MON-YYYY'));

INSERT INTO Customers
VALUES (1004, 'King', 'Chuck', '100 Broadway St.',
'Pleasant Hill', 'CA', '95114', '9254247701',
TO_DATE('14-APR-2001', 'DD-MON-YYYY'));

INSERT INTO Customers
VALUES (1005, 'Krieger', 'Jeff', '120 Mercury Rd.',
'San Ramon', 'CA', '95114', '9252997100',
TO_DATE('02-FEB-2001', 'DD-MON-YYYY'));

INSERT INTO Customers
VALUES (1006, 'Monroe', 'John', '122 West Ave.',
'Phoenix', 'AZ', '85004', '9252997100',
TO_DATE('02-OCT-2000', 'DD-MON-YYYY'));

INSERT INTO Customers
VALUES (1007, 'Lord', 'Don', '573 Hill View',
'Atlanta', 'GA', '30303', '3036578900',
TO_DATE('12-DEC-2000', 'DD-MON-YYYY'));

INSERT INTO Customers
VALUES (1008, 'Griffith', 'David', '10 Fulton Rd. Apt.4',
'San Francisco', 'CA', '94121', '7236578900',
TO_DATE('15-DEC-1999', 'DD-MON-YYYY'));

INSERT INTO Customers
VALUES (1009, 'Simon', 'Michael', '573 Hill View',
'Santa Monica', 'CA', '90402', '8185689200',
TO_DATE('22-MAY-1999', 'DD-MON-YYYY'));

INSERT INTO Customers
VALUES (1010, 'Simon', 'Michael', '573 Hill View',
'Santa Monica', 'CA', '90402', '8185689200',
TO_DATE('02-OCT-1999', 'DD-MON-YYYY'));

INSERT INTO Customers
VALUES (1011, 'Stone', 'Tony', '12 Main St. Apt. 3',
'Oakland', 'CA', '94342', '5104562881',
TO_DATE('07-DEC-1999', 'DD-MON-YYYY'));

INSERT INTO Customers
VALUES (1012, 'Krieger', 'Jeff', '120 Mercury Rd.',
'San Ramon', 'CA', '95114', '9252997100',
TO_DATE('15-JUN-1999', 'DD-MON-YYYY'));

INSERT INTO Customers
VALUES (1013, 'Blue', 'Don', '12250 Saturn Rd.',
'Pleasanton', 'CA', '95434', '9252332400',
TO_DATE('09-SEP-1999', 'DD-MON-YYYY'));

INSERT INTO Customers
VALUES (1014, 'Stone', 'Tony', '12 Main St. Apt. 3',
'Oakland', 'CA', '94342', '5104562881',
TO_DATE('11-APR-2000', 'DD-MON-YYYY'));

INSERT INTO Customers
VALUES (1015, 'Mason', 'Paul', '53 Orange Way',
'Las Vegas', 'NV', '89101', '5104562881',
TO_DATE('04-JUL-2000', 'DD-MON-YYYY'));

INSERT INTO Customers
VALUES (1016, 'Stone', 'Tony', '12 Main St. Apt. 3',
'Oakland', 'CA', '94342', '5104562881',
TO_DATE('30-DEC-2000', 'DD-MON-YYYY'));

INSERT INTO Customers
VALUES (1017, 'Krieger', 'Jeff', '120 Mercury Rd.',
'San Ramon', 'CA', '95114', '9252997100',
TO_DATE('11-JAN-2001', 'DD-MON-YYYY'));

INSERT INTO Customers
VALUES (1018, 'Blake', 'Becky', '1099 Venus St.',
'Salt Lake City', 'UT', '84106', '8016543501',
TO_DATE('12-JUN-2001', 'DD-MON-YYYY'));

INSERT INTO Customers
VALUES (1019, 'Stone', 'Tony', '12 Main St. Apt. 3',
'Oakland', 'CA', '94342', '5104562881',
TO_DATE('03-NOV-2000', 'DD-MON-YYYY'));

INSERT INTO Customers
VALUES (1020, 'Hill', 'Larry', '2220 Bench St.',
'Santa Rosa', 'CA', '94533', '7072279800',
TO_DATE('24-AUG-2000', 'DD-MON-YYYY'));

COMMIT;
看下面的代码我在姓，和名这两个字段上加上唯一约束，（当然你可以在创建表的时候加上这一约束，来防止冗余数据）
ALTER TABLE Customers
ADD CONSTRAINT Customers_LastFirst
UNIQUE (LastName, FirstName);

Ｃｕｓｔｏｍｅｒ表中的冗余键是ＬａｓｔＮａｍｅ和ＦｉｒｓｔＮａｍｅ的集合，我们把含有冗余键的数据进行分组并进行统计．

SELECT LastName, FirstName, COUNT(*)FROM Customers
GROUP BY LastName, FirstName
ORDER BY LastName, FirstName;

Ｌｉｓｔｉｎｇ　２显示了这条语句的输出，我们可以看到有三行的输出大于１，这也就意味着表中含有３组冗余数据．
Listing 2. 找出冗余

LASTNAME;;;;;FIRSTNAME;COUNT(*)
--------------- ---------- ----------
BlakeBecky;;;;1
Blue;Don 1
Bradley Tom 1
ChangJim 1
Griffith;;;;;David;;;;1
Hill;Larry;;;;1
King;Chuck;;;;1
Krieger Jeff;;;;;3
LoneyJulie;;;;1
Lord;Don 1
MasonPaul;;;;;1
Monroe; John;;;;;1
SimonMichael;;2
StoneTony;;;;;5

14 rows selected.

我们在语句中加入Ｈａｖｉｎｇ（）语句来过滤出非冗余数据．
SELECT LastName, FirstName, COUNT(*)
FROM Customers
GROUP BY LastName, FirstName
HAVING COUNT(*) > 1;

SQL
Listing 3. 过滤冗余
加入Ｈａｖｉｎｇ（）语句来过滤出非冗余数据．
LASTNAME;;;;;FIRSTNAME;COUNT(*)
--------------- ---------- ----------
Krieger Jeff;;;;;3
SimonMichael;;2
StoneTony;;;;;5

3 rows selected.
Ｌｉｓｔｉｎｇ　３显示了以上代码的输入，尽管如此，这些查询结果并没有显示出能标识每一行的字段，我们将上一语句做为一个嵌套查询来显示标识这些记录的ＩＤ
SELECT ID, LastName, FirstName
FROM Customers
WHERE (LastName, FirstName) IN
(SELECT LastName, FirstName
FROM Customers
GROUP BY LastName, FirstName
HAVING COUNT(*) > 1)
ORDER BY LastName, FirstName;

Ｌｉｓｔｉｎｇ　４显示出了以上代码的结果，这些查询显示了有三组冗余，共有十行，我们应该保留这些组中的1005，1009，1001这些记录然后删除1012，1017，1010，1011，1016，1019，1014这些冗余的条目．
SQL
Listing 4. 找出唯一的键
语句的输出
ID LASTNAME;;;;;FIRSTNAME
----- --------------- ----------
1005 Krieger Jeff
1012 Krieger Jeff
1017 Krieger Jeff
1009 SimonMichael
1010 SimonMichael
1001 StoneTony
1011 StoneTony
1016 StoneTony
1019 StoneTony
1014 StoneTony

10 rows selected.

Ｏｒａｃｌｅ公司给出的一个解决方案
Ｏｒａｃｌｅ　公司给我们提供一个见删除冗余数据的一个方案，这个方案使用了Ｏｒａｃｌｅ公司自己的一个集合函数ＭＩＮ（）或者ＭＡＸ（）来解决这一问题ＭＩＮ（）函数可以得到每一组中（冗余的非冗余的），应保留的所有值．（正如我们所见，输入出不包含那些大ＩＤ的冗余值

SELECT MIN(ID) AS ID, LastName, FirstName
FROM Customers
GROUP BY LastName, FirstName;
这一条命令的输出
Listing 5. Output of MIN() query
这一条命令显示了所有的非冗余的数据，其它的行则应该被删除
ID LASTNAME;;;;;FIRSTNAME
----- --------------- ----------
1018 BlakeBecky
1013 Blue;Don
1000 Bradley Tom
1002 ChangJim
1008 Griffith;;;;;David
1020 Hill;Larry
1004 King;Chuck
1005 Krieger Jeff
1003 LoneyJulie
1007 Lord;Don
1015 MasonPaul
1006 Monroe; John
1009 SimonMichael
1001 StoneTony

14 rows selected.


这样你就可以删除那些不在这个表中的所有的行，同样将上一条语句作为一个子查询，构造一个语句

DELETE FROM Customers
WHERE ID NOT IN
(SELECT MIN(ID)
FROM Customers
GROUP BY LastName, FirstName);

尽管如此，理论是可行的，但是这个方案并不是那么有效，因为这样一来，ＤＢＭＳ要完成两个表的扫描来完成这项任务，对于大量的数据来说，这简直是不可行的，为了测试他的性能，我创建了Ｃｕｓｔｏｍｅｒ表，大约有5000，00０行，45，000冗余行，（９％）以上这个命令运行了一个小时，没有输出结果，它耗尽了我的耐心，所以我杀死了这个进程
这个方案的令外这个方案还有一个缺点，你不能控制每一个组中你要保留的行


一种ＰＬ／ＳＱｌ解决方案：使用存储过程删除冗余数据，叫做ＤｅｌｅＤｕｐｌｉｃａｔｅ的存储过程，这个过程的结构很清晰的．
SQL
Listing 6. The DeleteDuplicate stored procedure

它将这些冗余行选择一到一个游标中，然后从表中取出每一个冗余行来进行与游标中的行进行比对，然后决定是否删除
CREATE OR REPLACE PROCEDURE DeleteDuplicates(
pCommitBatchSize IN INTEGER := 5000) IS

CURSOR csr_Duplicates IS
SELECT ID, LastName, FirstName
FROM Customers
WHERE (LastName, FirstName) IN
(SELECT LastName, FirstName
FROM Customers
GROUP BY LastName, FirstName
HAVING COUNT(*) > 1)
ORDER BY LastName, FirstName;

／＊保存上一次的姓和名＊／
vLastName Customers.LastName%TYPE := NULL;
vFirstName Customers.FirstName%TYPE := NULL;

vCounter INTEGER := 0;

BEGIN

FOR vDuplicates IN csr_Duplicates
LOOP
IF vLastName IS NULL OR
(vDuplicates.LastName != vLastName
OR NVL(vDuplicates.FirstName, ' ') != NVL(vFirstName, ' '))
THEN
／＊第一次取出行或者是一个新行
保存它的姓和名的值＊／
vLastName := vDuplicates.LastName;
vFirstName := vDuplicates.FirstName;

ELSE
／＊冗余数据，删除它＊／
DELETE
FROM Customers
WHERE ID = vDuplicates.ID;

vCounter := vCounter + 1;

／＊提交结果＊／
/* Commit every pCommitBatchSize rows */
IF MOD(vCounter, pCommitBatchSize) = 0
THEN
COMMIT;
END IF;

END IF;
END LOOP;

IF vCounter > 0
THEN
COMMIT;
END IF;


DBMS_OUTPUT.PUT_LINE(TO_CHAR(vCounter) ||
' duplicates have been deleted.');

EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('Error ' ||
TO_CHAR(SQLCODE) || ': ' || SQLERRM);
ROLLBACK;

END DeleteDuplicates;

它将冗余数据选择到一个游标中，并根据（ＬａｓｔＮａｍｅ，ＦｉｒｓｔＮａｍｅ）来分组（在我们这个方案中），然后打开游标然后循环地取出每一行，然后用与先前的取出的键值进行比较，如果这是第一次取出这个值，或者这个值不是冗余键，那么跳过这个记录然后取下一个，不然的话，这就是这个组中的冗余记录，所以删掉它．
让我们运行一下这个存储过程
BEGIN
DeleteDuplicates;
END;
/

SELECT LastName, FirstName, COUNT(*)
FROM Customers
GROUP BY LastName, FirstName
HAVING COUNT(*) > 1;

最后一个查询语句没有返回值，所以冗余数据没有了从表中取冗余数据的过程完全是由定义在csr_Duplicates 这个游标中的ＳＱＬ语句来实现的，ＰＬ／ＳＱｌ只是用来实现删除冗余数，那么能不能完全用ＳＱＬ语句来实现呢？

二．ＳＱＬ解决方案，使用ＲＡＮＫ（）删除冗余数据
Ｏｒａｃｌｅ8i分析函数ＲＡＮＫ（）来枚举每一个组中的元素，在我们的方案中，　我们应用这个方案，我们使用这个函数动态的把冗余数据连续的排列起来加上编号，组由Ｐａｒｔｉｎｔｉｏｎ　ｂｙ　这个语句来分开，然后用Ｏｒｄｅｒ　ｂｙ　进行分组
SELECT ID, LastName, FirstName,
RANK() OVER (PARTITION BY LastName,
FirstName ORDER BY ID) SeqNumber
FROM Customers
ORDER BY LastName, FirstName;

SQL
Listing 7. Output of single SQL statement that uses RANK()
显示的是根据记录的条数的个数来显示尤其对于冗余数据
ID LASTNAME;;;;;FIRSTNAMESEQNUMBER
----- --------------- ---------- ----------
1018 BlakeBecky;;;;1
1013 Blue;Don 1
1000 Bradley Tom 1
1002 ChangJim 1
1008 Griffith;;;;;David;;;;1
1020 Hill;Larry;;;;1
1004 King;Chuck;;;;1
1005 Krieger Jeff;;;;;1
1012 Krieger Jeff;;;;;2
1017 Krieger Jeff;;;;;3
1003 LoneyJulie;;;;1
1007 Lord;Don 1
1015 MasonPaul;;;;;1
1006 Monroe; John;;;;;1
1009 SimonMichael;;1
1010 SimonMichael;;2
1001 StoneTony;;;;;1
1011 StoneTony;;;;;2
1014 StoneTony;;;;;3
1016 StoneTony;;;;;4
1019 StoneTony;;;;;5

我们可以看一到，ＳｅｑＮｕｍｂｅｒ这一列中的数值，冗余数据是根据ＩＤ号由小到大进行的排序，所有的冗余数据的ＳｑｌＮｕｍｂｅｒ都大于一，所有的非冗余数据都等于一，所以我们取自己所需，删除那么没用的
SELECT ID, LastName, FirstName
FROM
(SELECT ID, LastName, FirstName,
RANK() OVER (PARTITION BY LastName,
FirstName ORDER BY ID) AS SeqNumber
FROM Customers)
WHERE SeqNumber > 1;


SQL
Listing 8. 冗余键的键值
有七行必须被删除
ID LASTNAME;;;;;FIRSTNAME
----- --------------- ----------
1012 Krieger Jeff
1017 Krieger Jeff
1010 SimonMichael
1011 StoneTony
1014 StoneTony
1016 StoneTony
1019 StoneTony

7 rows selected.

这显示有七行需要删除，还是用上一个表我测试了一下这个代码，它用了７７秒种就删除了所有的数据准备好了用Ｓｑｌ语句来删除冗余数据，版本一它执行了135秒

DELETE
FROM CUSTOMERS
WHERE ID IN
(SELECT ID
FROM
(SELECT ID, LastName, FirstName,
RANK() OVER (PARTITION BY LastName,
FirstName ORDER BY ID) AS SeqNumber
FROM Customers)
WHERE SeqNumber > 1);
我们可以看到最后的两行语句对表中的数据进行了排序，这不是有效的，所以我们来优化一下最后一个查询语句，把Ｒａｎｋ（）函数应用到只含有冗余数据的组，而不是所有的列
下面这个语句是比较有效率的，虽然它不像上一个查询那样精简
SELECT ID, LastName, FirstName
FROM
(SELECT ID, LastName, FirstName,
RANK() OVER (PARTITION BY LastName,
FirstName ORDER BY ID) AS SeqNumber
FROM
(SELECT ID, LastName, FirstName
FROM Customers
WHERE (LastName, FirstName) IN
(SELECT LastName, FirstName
FROM Customers
GROUP BY LastName, FirstName
HAVING COUNT(*) > 1)))
WHERE SeqNumber > 1;
选择冗余数据只用了２６秒钟，这样就提高了67％的性能，这样就提高
了将这个作为子查询的删除查询的效率，
DELETE
FROM Customers
WHERE ID IN
(SELECT ID
FROM
(SELECT ID, LastName, FirstName,
RANK() OVER (PARTITION BY LastName,
FirstName ORDER BY ID) AS SeqNumber
FROM
(SELECT ID, LastName, FirstName
FROM Customers
WHERE (LastName, FirstName) IN
(SELECT LastName, FirstName
FROM Customers
GROUP BY LastName, FirstName
HAVING COUNT(*) > 1)))
WHERE SeqNumber > 1);
现在只用了47秒钟的就完成的上面的任务，比起上一个136秒，这是一个很大的进步，相比之下，存储过程用了56秒，这样存储过程有些慢了使用ＰＬ／ＳＱＬ语句我们和我们以上的代码，会得到更好的更精确的代码，和提高你代码的执行效率，虽然对于从数据库中枚举数据ＰＬ／ＳＱＬ对于Ｓｑｌ两者没有什么差别，但是对于数据的比较上，ＰＬ／ＳＱＬ就比ＳＱＬ要快很多，但是如果冗余数据量比较小的话，我们尽量使用ＳＱＬ而不使用ＰＬ／ＳＱＬ

如果你的数据表没有主键的话，那么你可以参考其它技术

Ｒａｎｋ（）其它的方法
使用Ｒａｎｋ（）函数你可以对选择你所保留的数据，（或者是小ＩＤ的或者是大ＩＤ　的，就由ＲＥＣＤａｔｅ这个列来决定这种情况下，你可以把ＲＥｃｄａｔｅ加入到（Ｏｒｄｅｒ　ｂｙ　）子句中，倒序或者正序


这是一种保留最大Ｉｄ的一种解决方案

DELETE
FROM Customers
WHERE ID IN
(SELECT ID
FROM
(SELECT ID, LastName, FirstName,
RANK() OVER (PARTITION BY LastName,
FirstName ORDER BY RecDate DESC, ID) AS SeqNumber
FROM
(SELECT ID, LastName, FirstName, RecDate
FROM Customers
WHERE (LastName, FirstName) IN
(SELECT LastName, FirstName
FROM Customers
GROUP BY LastName, FirstName
HAVING COUNT(*) > 1)))
WHERE SeqNumber > 1);

这种技术保证了你可以控制每一个表中的保留的组，假设你有一个数据库，有一个促销或者有一个折扣信息，比如一个团体可以使用这种促销５次，或者个人可以使用这个折扣三次，为了指出要保留的组的个数，你可以在where 和having子句中进行设置，那么你将删除所有大于你设置有数的冗余组
DELETE
FROM Customers
WHERE ID IN
(SELECT ID
FROM
(SELECT ID, LastName, FirstName,
RANK() OVER (PARTITION BY LastName,
FirstName ORDER BY ID) AS SeqNumber
FROM
(SELECT ID, LastName, FirstName
FROM Customers
WHERE (LastName, FirstName) IN
(SELECT LastName, FirstName
FROM Customers
GROUP BY LastName, FirstName
HAVING COUNT(*) > 3)))
WHERE SeqNumber > 3);
As you can see, using the RANK() function allows you to eliminate duplicates in a
single SQL statement and gives you more capabilities by extending the power of your
queries.
正如你所见使用Ｒａｎｋ（）可以消除冗余数据而且能给你很大的可伸展性

上一篇 SQL SERVER 2000系统支持的跟踪函数

下一篇 使用SQL Mail收发和自动处理邮件中的扩展存储过程简介

相关文章