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

最新推荐文章于 2022-06-23 15:53:04 发布

sunbingzibo

最新推荐文章于 2022-06-23 15:53:04 发布

阅读量3.1k

点赞数

文章标签：数据库 insert duplicates date delete integer

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

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

什么是冗余数据？
冗余数据就是一个数据表中，这个表中的行包含了一些相同的值，这些值理论上来说应该是唯一的（这些值一般来说能确定一条记录）例如，像社会保险号，姓与名的集合．那么我们把这么含有相同信息的行中包含的数据叫做冗余数据，现在所有的数据库表中都有主键约束，主键中记录了一行记录中的唯一值，从数据库的角度来看，每一行都是唯一的，但是从我们用户角度看来，这些记录都是相同的记录，因为它们都包含相同的键值（Ｆｉｒｓｔ　Ｎａｍｅ　＋　Ｌａｓｔ　Ｎａｍｅ），即使他们有不同的主键
ID   Last 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(*)
--------------- ---------- ----------
Blake           Becky               1
Blue            Don                 1
Bradley         Tom                 1
Chang           Jim                 1
Griffith        David               1
Hill            Larry               1
King            Chuck               1
Krieger         Jeff                3
Loney           Julie               1
Lord            Don                 1
Mason           Paul                1
Monroe          John                1
Simon           Michael             2
Stone           Tony                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
Simon           Michael             2
Stone           Tony                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 Simon           Michael
1010 Simon           Michael
1001 Stone           Tony
1011 Stone           Tony
1016 Stone           Tony
1019 Stone           Tony
1014 Stone           Tony

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 Blake           Becky
1013 Blue            Don
1000 Bradley         Tom
1002 Chang           Jim
1008 Griffith        David
1020 Hill            Larry
1004 King            Chuck
1005 Krieger         Jeff
1003 Loney           Julie
1007 Lord            Don
1015 Mason           Paul
1006 Monroe          John
1009 Simon           Michael
1001 Stone           Tony

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        FIRSTNAME   SEQNUMBER
----- --------------- ---------- ----------
1018 Blake           Becky               1
1013 Blue            Don                 1
1000 Bradley         Tom                 1
1002 Chang           Jim                 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 Loney           Julie               1
1007 Lord            Don                 1
1015 Mason           Paul                1
1006 Monroe          John                1
1009 Simon           Michael             1
1010 Simon           Michael             2
1001 Stone           Tony                1
1011 Stone           Tony                2
1014 Stone           Tony                3
1016 Stone           Tony                4
1019 Stone           Tony                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 Simon           Michael
1011 Stone           Tony
1014 Stone           Tony
1016 Stone           Tony
1019 Stone           Tony

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.
正如你所见使用Ｒａｎｋ（）可以消除冗余数据而且能给你很大的可伸展性