A method and system for automatic data cleansing of similar duplicates

Abstract

The application discloses a kind of similar repeated record data cleaning automation method, this method is by calculating the repetition rate of field, realize the field of automatic selection for sorting and automatic determination of similarity weight, and according to whether the head and tail records in window are similar repetition, dynamically adjust window size, to realize the data cleaning automation of similar repeated record;Reduce labor cost, reduce the influence of human factors on results;The application also provides a system for implementing the above-mentioned method of the application, cooperation between modules, repeated calculation and comparison, processing, reduce manual participation, obtain more accurate processing results in data cleaning process, high degree of automation.

Description

Technical Field

[0001] This invention belongs to the field of data cleaning applications, specifically involving an automated method and system for cleaning similar duplicate records. Background Technology

[0002] In recent years, China's information technology has been developing rapidly. Both companies and government agencies are accelerating the implementation of management informatization. These enterprise information systems record a large amount of data. In order to extract valuable information from them and ensure the consistency of the collected data, it is necessary to improve the quality of the data through data cleaning.

[0003] Based on whether or not domain-specific knowledge is required, data cleaning can be divided into two main categories: domain-independent data cleaning and domain-specific data cleaning. Domain-independent data cleaning provides a general, domain-agnostic framework that automates the cleaning process and can be applied across various industries. Domain-specific data cleaning primarily handles data cleaning tasks specific to a particular industry or those with high data quality requirements.

[0004] Cleaning similar and duplicate records is an important issue in domain-independent data cleaning. Currently, most methods for identifying and detecting similar and duplicate records use a "sorting & comparison" approach. This involves selecting a field and sorting records based on its content, causing records with similar content in that field to appear adjacent to each other. A fixed window size is selected, and only the first record within the window is compared to the remaining records to determine if they are similar or duplicated. For example, with a window size of 1, the first and second records within the window are compared to determine if they are similar or duplicated. The similarity between two records is calculated by comparing their similarity to a given threshold; if it exceeds the threshold, they are considered similar and duplicated. The similarity score is obtained by calculating the similarity of each field in the two records and then summing them in a weighted manner. After determining the similarity or duplicate status of records within the current window, the window continues to slide downwards until the first record in the window becomes the last record in the entire window.

[0005] Compared to calculating the pairwise similarity between all records, this "sorting & comparison" method significantly reduces computation, but some problems still exist. The initial selection of the sorting fields requires manual intervention. Since the placement of similar duplicate records adjacently largely depends on the chosen sorting fields, the method's effectiveness is heavily influenced by human factors. The window size remains fixed during the sliding process; a window that is too large prevents calculations between potentially identical duplicate records, leading to unnecessary computation, while a window that is too small prevents similar duplicate records from appearing in the same window, resulting in omissions. Calculating the similarity between two records requires a weighted summation of the similarity scores for each field, and determining these field similarity weights also necessitates manual intervention. All these issues significantly complicate the data cleaning process for similar duplicate records.

[0006] In view of this, the present invention is hereby proposed. Summary of the Invention

[0007] The technical problem to be solved by the present invention is to overcome the shortcomings of the prior art and provide an automated method and system for cleaning similar duplicate records. By combining field duplication rate and dynamic variable window, the manual intervention in the cleaning process of similar duplicate records is reduced.

[0008] To solve the above-mentioned technical problems, the basic concept of the technical solution adopted by the present invention is as follows:

[0009] An automated method for cleaning similar duplicate records includes the following steps:

[0010] S1. Define the concept of field repetition rate, calculate the field repetition rate of each field, select the field with the lowest repetition rate as the sorting field, and sort them.

[0011] S2. Set judgment rules, calculate the similarity between the first record and the remaining records in the default window according to the rules, and determine whether they are similar duplicate records by comparing them with a set threshold.

[0012] S3. Determine whether the first and last records in the window are similar duplicate records. If they are similar duplicate records, expand the window size and execute step S2. If the first and last records in the window are not similar duplicate records, add the results of the determination of similar duplicate records to the set of similar duplicate records and slide to the next window. The initial size of the next window is the default size.

[0013] S4. Repeat steps S2 and S3 until the first record in the window is the last record among all records, thus obtaining a set of similar duplicate records;

[0014] S5. Next, select the field with the second lowest repetition rate and sort it. Repeat steps S2 to S4 to obtain a set of similar duplicate records under this sorting.

[0015] S6. Merge the sets of similar duplicate records obtained by sorting based on two different fields to determine the similar duplicate records;

[0016] S7. Process the identified similar duplicate records.

[0017] Furthermore, in the aforementioned automated data cleaning method for similar duplicate records, the formula for calculating the field duplication rate is as follows:

[0018]

[0019] Furthermore, in the above-mentioned automated data cleaning method for similar duplicate records, step S2 includes:

[0020] S21. Set the default window size value;

[0021] S22. Calculate the similarity of each field in all records within the window, and calculate the similarity of fields using the appropriate method according to the field type;

[0022] S23. Determine the weight corresponding to the similarity of each field based on the repetition rate of each field in step S1., and sum the weighted similarities of each field in the default window to obtain the similarity of all records in the window;

[0023] S24. Set a threshold, and determine whether the records are similar or duplicates by comparing the similarity of all records in the window with the given threshold.

[0024] S25. Calculate the similarity between the first record in the window and each of the remaining records in turn, and determine whether they are similar duplicate records by comparing them with the threshold.

[0025] S26. Determine whether the first and last records in the window are similar duplicate records. If they are similar duplicate records, increase the window size based on the current size.

[0026] Furthermore, in the above-mentioned automated data cleaning method for similar duplicate records, step S22 includes:

[0027] For Boolean fields, the similarity is 1 when the field content is the same, and 0 when the field content is different.

[0028] For numeric fields, the relative distance between two values ​​is used to measure similarity; the specific similarity calculation formula is as follows:

[0029]

[0030] Where S(x1,x2) represents the similarity between two fields, and x1 and x2 are two numerical fields to be calculated;

[0031] For character fields, the similarity between two strings is measured using string edit distance; the similarity calculation formula is:

[0032]

[0033] Where S(A,B) represents the similarity between two strings, and A and B represent two character fields. A and L B These represent the string lengths of A and B, respectively, and edit(A,B) represents the edit distance between A and B.

[0034] Furthermore, in the above-mentioned automated data cleaning method for similar duplicate records, in step S23: the similarity weight is calculated according to the following formula:

[0035]

[0036] Where p is the number of fields in each record, r i Let w be the field repetition rate of the i-th field. i Let be the field similarity weight for the i-th field;

[0037] The formula for calculating the similarity of all records within the window can be expressed as:

[0038]

[0039] Where S i This represents the field similarity of the i-th field among all records within the window.

[0040] Furthermore, in the above-mentioned automated data cleaning method for similar duplicate records, step S6. merges the sets of similar duplicate records obtained by sorting based on two different fields, and determines that the similar duplicate records include:

[0041] For elements common to both sets, one of them is added to the merged set and marked as having a frequency of 2. For elements unique to both sets, both are added to the merged set and marked as having a frequency of 1. In the merged set of similar duplicate records, elements with a frequency of 2 are considered similar duplicate records in both sorting results, while elements with a frequency of 1 are considered similar duplicate records only in one sorting result.

[0042] Furthermore, in the above-mentioned automated data cleaning method for similar duplicate records, step S7. processing the identified similar duplicate records includes:

[0043] The automated processing involves randomly selecting one record from the merged set of similar duplicate records and saving it, while deleting the remaining records.

[0044] Semi-automatic processing: Based on the merged set of similar duplicate records, for records that are determined to be similar duplicates and appear twice, one record is randomly selected and saved, the remaining records are deleted, and the remaining similar duplicate records that appear once are manually processed.

[0045] Manual processing is used to display the merged set of similar duplicate records, and the user decides whether to keep or delete the records.

[0046] On the other hand, the present invention also provides an automated data cleaning system for similar duplicate records, including...

[0047] Field Analysis Module: Used to define the concept of field repetition rate, calculate the field repetition rate of each field, and determine the field similarity weight;

[0048] Similar Duplicate Record Identification Module: This module selects the two fields with the lowest and second lowest duplication rates, sorts them separately, and compares them using a sliding window to obtain sets of similar duplicate records for each.

[0049] Similar Duplicate Record Merging Module: This module merges two sets of similar duplicate records and marks the number of times each similar duplicate record appears.

[0050] Similar Duplicate Record Processing Module: Used to select one of the following processing methods—automatic, semi-automatic, or manual—to process the identified similar duplicate records.

[0051] Furthermore, in the aforementioned automated data cleaning system for similar duplicate records, the field analysis module calculates the repetition rate using the following formula during the definition process:

[0052]

[0053] In the process of determining field similarity weights, the field analysis module calculates the similarity of each field of all records within the window according to the default window size, and determines the weight corresponding to the similarity of each field based on the repetition rate of each field.

[0054] The field analysis module determines the weight of each field's similarity based on the repetition rate of each field, and then sums the weighted similarities of each field for each record in the window to obtain the similarity of all records in the window.

[0055] Furthermore, in the aforementioned automated data cleaning system for similar duplicate records, the similar duplicate record identification module first obtains the field duplication rate of each field calculated by the field analysis module, and selects the fields with the lowest and second lowest duplication rates as the sorting fields for sorting; wherein:

[0056] First, select the field with the lowest repetition rate as the sorting field and sort it. Obtain the field similarity weight calculated by the field analysis module and the similarity of all records in the window. Then, set a threshold and determine whether they are similar duplicate records by comparing the similarity of all records in the window with the given threshold.

[0057] The similarity between the first record in the window and each of the remaining records is calculated sequentially, and the similarity is compared with the threshold to determine whether they are similar duplicate records.

[0058] Determine if the first and last records in the window are similar duplicate records. If they are similar duplicate records, increase the window size based on the current size.

[0059] The process continues until the first and last records in the window are not similar duplicate records. Records that are identified as similar duplicate records are added to the set of similar duplicate records, and the process slides to the next window. The process ends when the first record in the current window is the last record among all records, thus obtaining the set of similar duplicate records.

[0060] By adopting the above technical solution, the present invention has the following beneficial effects compared with the prior art:

[0061] The present invention provides an automated data cleaning method for similar duplicate records. By calculating and comparing the duplication rate of fields, it automates the two processes that originally required manual intervention: selecting sorting fields and determining field similarity weights. This reduces labor costs and the impact of human factors on the results. It also uses a dynamically variable window to reduce the risks caused by a fixed window that is too small or too large. Furthermore, it selects two fields for sorting to reduce the possibility of similar duplicate records not being adjacent when sorting by a single field.

[0062] The system of this invention is used to implement the above-mentioned method of this invention. The modules cooperate and coordinate with each other, repeatedly calculate, compare and process, reduce human intervention, obtain more accurate processing results in the data cleaning process, and have a high degree of automation. Attached Figure Description

[0063] Figure 1 A flowchart illustrating a specific embodiment of an automated data cleaning method for similar duplicate records provided by the present invention;

[0064] Figure 2 This is a schematic diagram illustrating the calculation of field repetition rate in the method of this invention;

[0065] Figure 3 This is a schematic diagram of the sliding window in the method of the present invention;

[0066] Figure 4 The logical block diagram of the automated data cleaning system for similar duplicate records provided by the present invention. Detailed Implementation

[0067] The present invention will be further described below with reference to the accompanying drawings and specific embodiments to help understand the content of the present invention.

[0068] like Figure 1 As shown, an automated method for cleaning similar duplicate records includes the following steps:

[0069] S1. Define the concept of field repetition rate. Based on the calculated field repetition rate of each field, select the field with the lowest repetition rate as the sorting field and sort it.

[0070] In this invention, defining the concept of field repetition rate means defining the calculation rules for field repetition rate. The formula for calculating field repetition rate in this step is as follows:

[0071]

[0072] The duplication rate means that records containing the exact same content in this field are identical. The rules for calculating the number of duplicate records for a field can be combined with... Figure 2 As shown, Figure 2 Table 2a (left) contains 10 records, each with a gender field. Records numbered 1, 2, 4, 8, 9, and 10 all have the gender field "Male," forming one set of duplicate fields (5 records). Records numbered 3, 5, 6, and 7 all have the gender field "Female," forming another set of duplicate fields (3 records). Therefore, the total number of duplicate gender fields across these 10 records is 8, resulting in a 80% gender repetition rate. Similarly, ... Figure 2 Table 2b (right) contains 5 sets of duplicate fields, with a total of 5 duplicate fields and a field duplication rate of 50%. The field duplication rate for each field can be calculated using the rules described above.

[0073] S2. Calculate the similarity between the first record and the remaining records within the window. This includes:

[0074] S21. Set the default window size value;

[0075] In a specific embodiment of the method of the present invention, the default window size is preferably set to 1, that is, there are a total of 2 records in the default window; since the window can only be expanded and not shrunk in subsequent steps, the default window size should be as small as possible to avoid unnecessary calculations caused by an excessively large window at the beginning.

[0076] S22. Calculate the similarity of each field in all records within the window, and use the appropriate method to calculate the similarity of fields according to their different types.

[0077] In this embodiment, the window size is 1, and the similarity of each field of the two records within the window is calculated.

[0078] Specifically, in this step, for Boolean fields, the similarity is 1 when the field content is the same, and 0 when the field content is different;

[0079] For numeric fields, the relative distance between two values ​​is used to measure similarity; the specific similarity calculation formula is as follows:

[0080]

[0081] Where S(x1,x2) represents the similarity between two fields, and x1 and x2 are two numerical fields to be calculated;

[0082] For character fields, the similarity between two strings is measured using the edit distance. There are three types of editing operations: "insert," "delete," and "replace." The edit distance is the minimum number of edit operations required to convert between two strings; the larger the edit distance, the lower the similarity between the two strings. The similarity calculation formula is:

[0083]

[0084] Where S(A,B) represents the similarity between two strings, and A and B represent two character fields. A and L B These represent the string lengths of A and B, respectively, and edit(A,B) represents the edit distance between A and B.

[0085] S23. Determine the weight corresponding to the similarity of each field based on the repetition rate of each field in S1., and sum the weighted similarities of each field in S21 to obtain the similarity of all records in the window.

[0086] The lower the repetition rate, the higher the weight. Assuming there are p fields in total, and the repetition rate of the i-th field is r... i Then the field similarity weight w of the i-th field i It can be represented as:

[0087]

[0088] Taking a window size of 1 as an example, the formula for calculating the similarity between two records within the window can be expressed as:

[0089]

[0090] Where S i Let be the field similarity of the i-th field between two records.

[0091] S24. Set a threshold, and determine whether the records are similar or duplicates by comparing the similarity of all records in the window with the given threshold.

[0092] In this embodiment, the preferred threshold is 0.75, but the threshold can be adjusted according to actual needs. Two records within the window are compared; if the similarity is greater than or equal to the threshold, the two records are considered similar duplicates; otherwise, they are considered not similar duplicates.

[0093] S25. Calculate the similarity between the first record in the window and each of the remaining records in turn, and determine whether they are similar duplicate records by comparing them with the threshold.

[0094] S26. Determine whether the first and last records in the window are similar duplicate records. If they are similar duplicate records, increase the window size based on the current size. The preferred increase value is 1, that is, if the first and last records in the window are similar duplicate records, the window size setting value is increased by 1 based on the current size; of course, the increase value is not limited to 1.

[0095] S3. Repeat steps S21-S26 in S2 until the first and last records in the window are not similar duplicate records. Add the results that are judged to be similar duplicate records to the set of similar duplicate records, and slide to the next window.

[0096] according to Figure 3 As can be seen, during the sliding window process, the second record of the current window becomes the first record of the next window, and the sizes of two adjacent windows are not necessarily the same.

[0097] S4. Repeat steps S2-S3 until the first record in the current window is the last record of all records. This will give you a set of similar duplicate records.

[0098] By traversing all windows, similar duplicate records that may exist in the entire data table are detected and identified.

[0099] S5. Next, select the field with the second lowest repetition rate and sort it. Repeat steps S2-S4 once to obtain a set of similar duplicate records under this sorting.

[0100] Sort by selecting two fields separately, which can reduce the occurrence of similar duplicate records that are not adjacent, which is more likely to occur when sorting by only one field.

[0101] S6. Merge the sets of similar duplicate records obtained by sorting based on two different fields.

[0102] For elements common to both sets, one of them is added to the merged set and marked as having a frequency of 2. For elements unique to both sets, both are added to the merged set and marked as having a frequency of 1. In the merged set of similar duplicate records, elements appearing twice are considered similar duplicate records in both sorting results, while elements appearing once are considered similar duplicate records only in one sorting result.

[0103] S7. Process the identified similar duplicate records.

[0104] This step includes automated processing, semi-automated processing, and manual processing.

[0105] The automated processing involves randomly selecting one record from the merged set of similar duplicate records and saving it, while deleting the remaining records.

[0106] Semi-automatic processing: Based on the obtained merged set of similar duplicate records, for records that are determined to be similar duplicates and appear twice, one record is randomly selected and saved, the remaining records are deleted, and the remaining similar duplicate records that appear once are processed by relevant personnel.

[0107] Manual processing displays the merged set of similar duplicate records, and relevant personnel decide whether to retain or delete the records.

[0108] Choose one of the three processing methods mentioned above as needed.

[0109] The method of this invention standardizes and automates the processes of selecting sorting fields and determining field similarity weights, which originally required manual intervention, by calculating and comparing the repetition rate of fields. This reduces labor costs, minimizes the impact of human factors on the results, and improves the accuracy of data cleaning results. In the process of calculating similarity, a dynamically variable window is used to reduce the risks caused by a fixed window that is too small or too large. At least two fields are selected for sorting, which reduces the possibility of duplicate records that are not adjacent when sorted by one field.

[0110] On the other hand, the present invention also provides an automated data cleaning system for similarity-based duplicate records that implements the above-described method, such as... Figure 4 As shown, including:

[0111] Field Analysis Module: Used to define the concept of field repetition rate, calculate the field repetition rate of each field, and determine the field similarity weight;

[0112] Similar Duplicate Record Identification Module: This module selects two fields for sorting, compares them using a sliding window, and obtains a set of similar duplicate records for each.

[0113] Similar Duplicate Record Merging Module: This module is used to merge two sets of similar duplicate records. Elements common to both sets are added to the merged set and marked as having a frequency of 2. Elements unique to both sets are added to the merged set and marked as having a frequency of 1 for each set.

[0114] Similar Duplicate Record Processing Module: This module allows you to select one of three processing methods—automatic, semi-automatic, or manual—to process the identified similar duplicate records.

[0115] Specifically, the field analysis module automatically determines the field similarity weight by calculating the field repetition rate; the calculation formula is as follows during the process of defining the field repetition rate concept:

[0116]

[0117] During the process of determining field similarity weights, the field analysis module calculates the similarity of each field in all records within the window based on the default window size, and determines the weight corresponding to the similarity of each field based on the repetition rate of each field.

[0118] The similarity calculation for each field of all records within the window is performed using appropriate methods depending on the field type:

[0119] For Boolean fields, the similarity is 1 when the field content is the same, and 0 when the field content is different.

[0120] For numeric fields, the relative distance between two values ​​is used to measure similarity; the specific similarity calculation formula is as follows:

[0121]

[0122] Where S(x1,x2) represents the similarity between two fields, and x1 and x2 are two numerical fields to be calculated;

[0123] For character fields, the similarity between two strings is measured using the edit distance. There are three types of editing operations: "insert," "delete," and "replace." The edit distance is the minimum number of edit operations required to convert between two strings; the larger the edit distance, the lower the similarity between the two strings. The similarity calculation formula is:

[0124]

[0125] Where S(A,B) represents the similarity between two strings, and A and B represent two character fields. A and L B These represent the string lengths of A and B, respectively, and edit(A,B) represents the edit distance between A and B.

[0126] The field analysis module determines the weight corresponding to the similarity of each field based on the repetition rate of each field, and then sums the weighted similarities of each field for each record within the window to obtain the similarity of all records within the window. The formula is expressed as follows:

[0127]

[0128] Where p is the number of fields in each record, r i Let w be the field repetition rate of the i-th field. i Let be the field similarity weight for the i-th field;

[0129] The formula for calculating the similarity of all records within the window can be expressed as:

[0130]

[0131] Where S i This represents the field similarity of the i-th field among all records within the window.

[0132] The similar duplicate record identification module automatically selects two fields based on the field duplication rate and sorts them separately. By judging whether the first and last records in the window are similar duplicate records, the window size is dynamically adjusted to obtain the set of similar duplicate records for each.

[0133] First, obtain the field repetition rate of each field calculated by the field analysis module, and then select the fields with the lowest and second lowest repetition rates as the sorting fields and sort them.

[0134] First, select the field with the lowest repetition rate as the sorting field and sort it. Obtain the field similarity weight calculated by the field analysis module and the similarity of all records in the window. Then, set a threshold and determine whether they are similar duplicate records by comparing the similarity of all records in the window with the given threshold. If the similarity is greater than or equal to the threshold, the records in the window are considered to be similar duplicate records; otherwise, the records in the window are considered not to be similar duplicate records.

[0135] The similarity between the first record in the window and each of the remaining records is calculated sequentially, and the similarity is compared with the threshold to determine whether they are similar duplicate records.

[0136] Determine whether the first and last records in the window are similar duplicate records. If they are, increase the window size based on the current size. The preferred increase value is 1, meaning that if the first and last records in the window are similar duplicate records, the window size is increased by 1 based on the current size; however, the increase value is not limited to 1.

[0137] The process continues until the first and last records in the window are not similar duplicate records. Records deemed similar duplicates are added to the set of similar duplicate records, and the process moves to the next window. This continues until the first record in the current window is the very first and last of all records, at which point the set of similar duplicate records is obtained.

[0138] Then, select the field with the second lowest repetition rate for sorting, repeat the calculation and comparison, and obtain a set of similar duplicate records under this sorting.

[0139] During the merging process of the two sets of similar duplicate records, the module merges the two sets of similar duplicate records and marks the occurrence count of the similar duplicate records: elements common to both sets are added to the merged set and marked as having a count of 2; elements unique to both sets are added to the merged set and marked as having a count of 1. In the merged set of similar duplicate records, elements that occur 2 times are considered similar duplicate records in both sorting results, while elements that occur 1 time are considered similar duplicate records only in one sorting result.

[0140] The similar duplicate record processing module processes the identified similar duplicate records using one of three methods: automated, semi-automated, or manual.

[0141] The automated processing involves randomly selecting one record from the merged set of similar duplicate records and saving it, while deleting the remaining records.

[0142] Semi-automatic processing: Based on the obtained merged set of similar duplicate records, for records that are determined to be similar duplicates and appear twice, one record is randomly selected and saved, the remaining records are deleted, and the remaining similar duplicate records that appear once are processed by relevant personnel.

[0143] Manual processing displays the merged set of similar duplicate records, and relevant personnel decide whether to retain or delete the records.

[0144] The system of this invention is used to implement the automated data cleaning method for similar duplicate records described above. The execution steps and principles of each module can be found in the relevant descriptions of the method described above, and will not be repeated here. The modules of this system cooperate and coordinate, repeatedly calculating, comparing, and processing, reducing manual intervention and obtaining more accurate processing results in the data cleaning process, resulting in a high degree of automation.

[0145] The above description is only a preferred embodiment of the present invention. It should be noted that for those skilled in the art, several improvements and modifications can be made without departing from the principle of the present invention, and these improvements and modifications should also be considered within the scope of protection of the present invention.

Claims

1. An automated method for cleaning similar duplicate records, characterized in that: The method comprises the following steps: S1. defining the concept of field repetition rate, calculating the field repetition rate of each field, selecting the field with the lowest repetition rate as the sorted field and performing sorting; S2. setting a judgment rule, calculating the similarity between the first record and the remaining records in the default window according to the rule, and judging whether it is a similar duplicate record by setting a threshold; S3. judging whether the first and last records in the window are similar duplicate records, if they are similar duplicate records, then expanding the window size and executing step S2, if the first and last records in the window are not similar duplicate records, then adding the result of judging similar duplicate records to the set of similar duplicate records and sliding to the next window, the initial size of the next window is the default size; S4. repeating steps S2 and S3 until the first record in the window is the last record in all records, and obtaining the set of similar duplicate records; S5. selecting the second-lowest repetition rate field for sorting, and repeating steps S2 to S4 to obtain the set of similar duplicate records under this sorting; S6. merging the sets of similar duplicate records obtained according to the sorting of two different fields to determine the similar duplicate records; S7. processing the identified similar duplicate records, Wherein, when defining the concept of field repetition rate, the field repetition rate calculation formula is as follows: The step S2 comprises: S21. setting the value of the default window size; S22. calculating the similarity of each field of all records in the window, and calculating the similarity of the field according to the corresponding method according to the type of the field; S23. determining the weight corresponding to the similarity of each field according to the repetition rate of each field in step S1, and weighting and summing the similarity of each field in the default window to obtain the similarity of all records in the window; S24. setting a threshold, and judging whether it is a similar duplicate record by comparing the similarity of all records in the window with the size of the given threshold; S25. calculating the similarity between the first record and each remaining record in the window in turn, and judging whether it is a similar duplicate record by comparing with the threshold; S26. judging whether the first and last records in the window are similar duplicate records, if they are similar duplicate records, then expanding the window size based on the current size.

2. The method of claim 1, wherein: The step S22 comprises: For a Boolean field, the similarity is 1 when the field content is the same, and the similarity is 0 when the field content is different; For a numerical field, the relative distance between two numerical values is used to measure the similarity; the specific similarity calculation formula is Wherein, S(x1, x2) represents the similarity of two fields, x1 and x2 are two numerical fields to be calculated; For a character field, the string edit distance is used to measure the similarity of two strings; the similarity calculation formula is where S(A, B) represents the similarity of two strings, A and B represent two character fields, and A and L B represent the string length of A and B, respectively, and edit(A, B) represents the edit distance of A and B.

3. The method of claim 1, wherein: In the step S23, the similarity weight is calculated according to the following formula: where p is the number of fields in each record, r i is the field repetition rate of the i-th field, w i is the field similarity weight of the i-th field; The similarity calculation formula of all records in the window can be expressed as: where S i is the field similarity of the i-th field of all records within the window.

4. The method of claim 3, wherein: Step S6. merging the sets of similar duplicate records obtained according to the sorting of two different fields to determine the similar duplicate records comprises: For the elements common to the two sets, one of them is put into the merged set and the occurrence frequency is marked as 2, and for the elements unique to the two sets, they are put into the merged set and the occurrence frequency is marked as 1 respectively; in the merged set of similar repeated records, the elements with the occurrence frequency of 2 are considered as similar repeated records in both sorting results, and the elements with the occurrence frequency of 1 are considered as similar repeated records in only one sorting result.

5. The method of claim 4, wherein: The step S7 of processing the identified similar repeated records comprises: Automatic processing, according to the obtained merged set of similar repeated records, for the records determined as similar repeated records, one of them is randomly selected and saved, and the remaining records are deleted; Semi-automatic processing, according to the obtained merged set of similar repeated records, for the records determined as similar repeated records and with the occurrence frequency of 2, one of them is randomly selected and saved, and the remaining records are deleted, and the remaining similar repeated records with the occurrence frequency of 1 are manually processed; Manual processing, the merged set of similar repeated records is displayed, and the retention and deletion of records are manually determined.

6. A data cleansing automation system for similar repeated records, characterized by: Comprise The field analysis module is used to define the field repetition rate concept, calculate the field repetition rate of each field and determine the field similarity weight; The similar repeated record identification module is used to select two fields with the lowest and second-lowest repetition rates respectively for sorting, and obtain similar repeated record sets respectively by comparison through a sliding window; The similar repeated record merging module is used to merge the two obtained similar repeated record sets and mark the occurrence frequency of similar repeated records; The similar repeated record processing module is used to select one of automatic processing, semi-automatic processing and manual processing to process the identified similar repeated records, In the process of defining the field repetition rate concept, the calculation formula of the field analysis module is as follows: In the process of determining the field similarity weight, the field analysis module calculates the similarity of each field of all records in the window according to the default window size, and determines the weight corresponding to each field similarity according to the repetition rate of each field; The field analysis module determines the weight corresponding to each field similarity according to the repetition rate of each field, and obtains the similarity of all records in the window by weighted summation of the similarity of each field of each record in the window, The similar repeated record identification module first obtains the field repetition rate of each field calculated by the field analysis module, and selects the fields with the lowest and second-lowest repetition rates respectively as the sorting fields and performs sorting; wherein: The field with the lowest repetition rate is selected as the sorting field and sorted first, the field similarity weight and the similarity of all records in the window calculated by the field analysis module are obtained, and then a threshold is set to determine whether it is a similar repeated record by comparing the similarity of all records in the window with the given threshold; The similarity between the first record in the window and each remaining record is calculated in turn, and whether it is a similar repeated record is determined by comparing with the threshold; Determine whether the first and last records in the window are similar repeated records, and if they are, expand the window size based on the current size.

7. The automated system for cleaning of similar duplicate records of data as claimed in claim 6 wherein: The similar repetitive record identification module Until the first and last records in the window are not similar repetitive records, the result of judging as similar repetitive records is added to the set of similar repetitive records, and sliding to the next window; until the first record in the current window is the last first record in all records, the set of similar repetitive records is obtained.

Images