代码之家 › 专栏 › 技术社区 › Michael Owen

在MySQL中,如何根据优先级和最长结束日期删除重复行?

sql-delete case mysql

Michael Owen · 技术社区 · 6 年前

我有一个大约85000行数据的数据库,其中有一个带有日期和参考号的客户列表。这些数据是通过使用大量CSV文件组合在一起的,因此我在数据库中有大量重复数据,我现在正试图根据一组规则删除这些数据。您会注意到,数据集中一致的一点是参考号,因为无论客户的记录被添加到数据库中多少次,每个客户都有一个唯一的参考号。

我收集了一组样本数据,尝试构建一个逻辑,下面是我的create和insert语句:

CREATE TABLE `rules_sample` (
 `id` int(11) NOT NULL DEFAULT 0,
 `Name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
 `Start_Date` date DEFAULT NULL,
 `End_Date` date DEFAULT NULL,
 `Ref_No` mediumtext CHARACTER SET utf8 DEFAULT NULL,
 `Filename` varchar(255) CHARACTER SET utf8 DEFAULT NULL
);


INSERT INTO `rules_sample` (`id`, `Name`, `Start_Date`, `End_Date`, `Ref_No`, `Filename`) VALUES
(172251, 'Mr. Humpty Dumpty', '2018-01-01', '2018-01-30', '110001239', 'Unknown'),
(956757, 'Humpty Dumpty', '2018-02-01', '2019-02-01', '110001239', 'Main 1'),
(957765, 'Humpty Dumpty', '2017-02-01', '2018-02-01', '110001239', 'Main 1'),
(958415, 'Humpty Dumpty', '2016-02-01', '2017-01-31', '110001239', 'Main 1'),
(958635, 'Mr Humpty Dumpty', '2014-11-13', '2015-11-13', '110001239', 'Main 1'),
(1104524, 'Mr. Humpty Dumpty', '2018-01-30', '2017-08-03', '110001239', 'Unknown'),
(1104662, 'Humpty Dumpty', '2018-02-01', '2017-08-05', '110001239', 'Unknown'),
(1114207, 'Humpty Dumpty', '2017-02-01', '2018-02-01', '110001239', 'Unknown'),
(1114504, 'Mr Humpty Dumpty', '2014-11-13', '2015-11-13', '110001239', 'Unknown'),
(1348915, 'Mr. Humpty Dumpty', '2018-01-30', '2019-01-30', '110001239', 'Other_Data'),
(92625, 'Mickey Mouse', '2018-08-09', '2018-08-08', '110003936', 'Unknown'),
(93713, 'Mr&Mrs M Mouse', '2017-06-23', '2019-06-22', '110003936', 'Unknown'),
(94978, 'Mickey Mouse', '2018-08-09', '2020-08-08', '110003936', 'Unknown'),
(847136, 'Mickey Mouse', '2020-08-08', '2020-08-08', '110003936', 'Data'),
(847193, 'Mickey Mouse', '2018-08-08', '2018-08-08', '110003936', 'Data'),
(847379, 'Mr&Mrs M Mouse', '2019-06-22', '2019-06-22', '110003936', 'Data'),
(858126, 'Mr&Mrs M Mouse', '2019-08-08', '2019-08-08', '110003936', 'Data'),
(1288753, 'Mr&Mrs M Mouse', '2018-06-22', '2019-06-22', '110003936', 'ABC Services'),
(930743, '.', '2020-08-14', '2020-08-14', '116000074', 'ABC Services'),
(930980, '.', '2020-07-22', '2020-07-22', '116000074', 'ABC Services'),
(931226, '.', '2020-06-30', '2020-06-30', '116000074', 'ABC Services'),
(931804, '.', '2020-05-13', '2020-05-13', '116000074', 'ABC Services'),
(932008, '.', '2020-05-03', '2020-05-03', '116000074', 'ABC Services'),
(932230, '.', '2020-04-26', '2020-04-26', '116000074', 'ABC Services'),
(932644, '.', '2020-04-10', '2020-04-10', '116000074', 'ABC Services'),
(933416, '.', '2020-03-17', '2020-03-17', '116000074', 'ABC Services'),
(933591, '.', '2020-03-08', '2020-03-08', '116000074', 'ABC Services'),
(933887, '.', '2020-02-27', '2020-02-27', '116000074', 'ABC Services'),
(934965, '.', '2020-01-21', '2020-01-21', '116000074', 'ABC Services');

现在我的规则基于两个因素,1)最高结束日期2)基于文件名的优先级列表(如下所示)。

Priority | Filename
1 | Main 1
2 | Data
3 | ABC Services
4 | Other_Data
5 | Unknown

我采取的第一步是编写以下代码,以便对结束日期进行排序:

SELECT 
    T1.*,
    Row_Number() OVER (PARTITION BY Ref_No ORDER BY End_Date DESC) rank
FROM 
    rules_sample T1
    ;

这给了我以下输出:

id  Name    Start_Date  End_Date    Mpan_MPR    Data_Source rank
956757  Humpty Dumpty   2018-02-01  2019-02-01  110001239   Main 1 1
1348915 Mr. Humpty Dumpty   2018-01-30  2019-01-30  110001239   Other_Data 2
957765  Humpty Dumpty   2017-02-01  2018-02-01  110001239   Main 1 3
1114207 Humpty Dumpty   2017-02-01  2018-02-01  110001239   Unknown 4
172251  Mr. Humpty Dumpty   2018-01-01  2018-01-30  110001239   Unknown 5
1104662 Humpty Dumpty   2018-02-01  2017-08-05  110001239   Unknown 6
1104524 Mr. Humpty Dumpty   2018-01-30  2017-08-03  110001239   Unknown 7
958415  Humpty Dumpty   2016-02-01  2017-01-31  110001239   Main 1 8
958635  Mr Humpty Dumpty    2014-11-13  2015-11-13  110001239   Main 9
1114504 Mr Humpty Dumpty    2014-11-13  2015-11-13  110001239   Unknown 10
94978   Mickey Mouse    2018-08-09  2020-08-08  110003936   Unknown 1
847136  Mickey Mouse    2020-08-08  2020-08-08  110003936   Data 2
858126  Mr&Mrs M Mouse  2019-08-08  2019-08-08  110003936   Data 3
93713   Mr&Mrs M Mouse  2017-06-23  2019-06-22  110003936   Unknown 4
847379  Mr&Mrs M Mouse  2019-06-22  2019-06-22  110003936   Data 5
1288753 Mr&Mrs M Mouse  2018-06-22  2019-06-22  110003936   ABC Services 6
92625   Mickey Mouse    2018-08-09  2018-08-08  110003936   Unknown 7
847193  Mickey Mouse    2018-08-08  2018-08-08  110003936   Data 8
930743  .   2020-08-14  2020-08-14  116000074   ABC Services 1
930980  .   2020-07-22  2020-07-22  116000074   ABC Services 2
931226  .   2020-06-30  2020-06-30  116000074   ABC Services 3
931804  .   2020-05-13  2020-05-13  116000074   ABC Services 4
932008  .   2020-05-03  2020-05-03  116000074   ABC Services 5
932230  .   2020-04-26  2020-04-26  116000074   ABC Services 6
932644  .   2020-04-10  2020-04-10  116000074   ABC Services 7
933416  .   2020-03-17  2020-03-17  116000074   ABC Services 8
933591  .   2020-03-08  2020-03-08  116000074   ABC Services 9
933887  .   2020-02-27  2020-02-27  116000074   ABC Services 10
934965  .   2020-01-21  2020-01-21  116000074   ABC Services 11

现在,我正在努力的是如何将优先级列表包含到我的代码中。我的最终输出应该如下所示:

id  Name    Start_Date  End_Date    Mpan_MPR    Data_Source
956757  Humpty Dumpty   2018-02-01  2019-02-01  110001239   Main 1
847136  Mickey Mouse    2020-08-08  2020-08-08  110003936   Data
930743  .       2020-08-14  2020-08-14  116000074   ABC Services

我将解释我的最终输出是如何计算的:

对于110001239来说,这非常简单,排名为1的结束日期有文件名Main 1,其优先级为列表中的1,因此应删除其余数据。

110003936有点棘手,因为最高结束日期的文件名未知,但其优先级为5,现在第二个结束日期与第一个结束日期相同,并且该文件名是优先级为2的数据,因此应保留秩为2的行,并删除其余的行。

116000074,很简单,因为结束日期的排名1应该被保留,因为我们在所有记录中只有一个文件名。

需要注意的一个关键问题是,结束日期会覆盖文件名的优先级。我的代码需要考虑可能所有文件名都在同一参考号内的记录,但这将基于最长结束日期。

希望一切都有意义。

0 回复 | 直到 6 年前

GMB 6 年前

如果我跟对了你可以用 string function field() 作为窗口函数中的第二个排序标准:

row_number() over (
    partition by ref_no 
    order by 
        end_date desc, 
        field(filename, 'Main 1', 'Data', 'ABC Services', 'Other_data', 'Unknown')
) rank

字段() 返回列表中第一个匹配值的索引-如果 filename 有价值 'Data' 例如,它将返回2。

事实上有一个小陷阱 字段() 返回 0 如果该值与列表中的任何内容不匹配。如果可能发生这种情况,那么您可能希望按相反的顺序排序:

row_number() over (
    partition by ref_no 
    order by 
        end_date desc, 
        field(filename, 'Unknown', 'Other_data', 'ABC Services', 'Data', 'Main 1') desc
) rank