Time series oriented repeated similar data cleaning optimization method

Abstract

The present invention discloses a time series-oriented repeated similar data cleaning optimization method. Repeated similar data cleaning is the most commonly used technology in data preprocessing and an important means to improve data quality. Therefore, improving the timeliness of large-scale repeated similar data detection is of great significance. In this regard, the present invention proposes a repeated similar data detection algorithm oriented to time series characteristics. The algorithm is based on the traditional nearest neighbor sorting algorithm SNM, supports dynamic adjustment of window size, and supports window jump sliding. The introduction of this algorithm greatly reduces the number of comparisons of data records, and both theoretical and experimental results show that the algorithm can effectively improve the detection efficiency of repeated similar data.

Description

Technical Field

[0001] The present invention relates to the technical field of data processing methods, and in particular to a time series oriented repeated similar data cleaning optimization method. Background Art

[0002] With the extensive application of smart devices and sensor technology, a type of data with typical characteristics has emerged in big data processing - time series data. Time series data has frequent and important applications in various fields such as business, meteorology, agriculture, aerospace, and biological sciences. Its characteristics are that data is generated over time, the data volume is huge, and real-time analysis and processing requirements are high. Due to various factors, such as equipment anomalies, network fluctuations, repeated input, merging of heterogeneous data with the same structure, and merging of multiple sources, a lot of repeated and similar data is generated in the same time unit in the time series data record. In view of the fact that time series data is usually large in volume, more efficient repeated and similar data detection and cleaning algorithms are very important.

[0003] At present, there have been many studies on duplicate and similar data detection and cleaning. For example, many researchers have proposed the idea of ​​partition processing, the essence of which is to divide large blocks of data into smaller subsets, so that duplicate and similar records distributed in the same sub-partition are only compared with the records in the partition instead of the entire data set during the comparison process. In the partitioning idea, the most commonly used methods are block and window technology. Block technology first divides the data set into independent subsets suitable for processing, and then uses clustering algorithms in the independent subsets for further processing; window technology uses a sliding window to select a fixed size of data and compare it with the target data in turn.

[0004] Among them, algorithms based on window technology have been widely studied and applied, and some achievements have been made in duplicate data detection. For example, the algorithm based on "sort and merge" first sorts the data by key features, and then identifies duplicate similar records by comparing whether the features of adjacent records are equal or similar. Based on the idea of ​​"sort and merge", researchers have proposed the SNM (Sorted Neighborhood Method) algorithm, the Sorted Blocks Method (SBM) algorithm, and the Multi-Pass Sorted Neighborhood (MPN) algorithm. The main idea of ​​the SNM algorithm is to sort the data records by key attributes first, and group the duplicate similar records together; then move a window of a fixed size of w, and each new data record entering the window will be compared with the previous w-1 records. When the comparison is completed, the window will continue to move down one grid. There are two main shortcomings of this algorithm. First, it is difficult to choose the size w of the fixed window. A window that is too small is prone to missing duplicate records, while a window that is too large increases the time complexity. Second, the window slides in a single grid, which is inefficient. After each data comparison in the window is completed, the window only slides forward one grid. In data segments with low repetition rates, the efficiency is very low. MPN further concentrates similar data and then identifies similar data. However, this algorithm not only uses SNM multiple times, but also requires multiple selections of different features for sorting, and the computational time complexity is higher than SNM. Summary of the invention

[0005] An object of the present invention is to solve at least the above problems and to provide at least the advantages which will be described hereinafter.

[0006] Based on the above considerations, the present invention proposes a repeated similar data detection algorithm DS-SNM (Dynamic Sliding-Sorted Neighborhood Method) based on window technology and targeting the characteristics of time series data, which reduces the number of comparisons of non-repeated data and greatly reduces the time complexity of the algorithm.

[0007] The present invention proposes a time-series oriented repeated similar data cleaning optimization method, comprising the following steps:

[0008] Step 1: Sort the records according to the selected keywords to obtain a sorted record set D, wherein the keywords include a time attribute T and an entity identifier N0;

[0009] Step 2: Initialize the window parameters for comparing duplicate similar records;

[0010] Step 3: Compare the first record D in the window i and the last record D i+w-1If the entity identifier N0 attribute is the same entity, go to step 4, otherwise go to step 6;

[0011] Step 4: Calculate the first record D in the window i To the last record D i+w-1 Theoretical number of records S num , the theoretical record number S num Compared with the window size w, if the theoretical window size S num If it is the same as the actual window size w, the window size is enlarged;

[0012] If the first record in the window is D i and the last record D i+w-1 If the time is the same, the window size is enlarged;

[0013] Otherwise, it means there are duplicate records or missing records in the window, and go to step 5;

[0014] Step 5: Use binary search to find the earliest duplicate record and go to step 7;

[0015] Step 6: If the entity has changed, use the binary search method to backtrack to find the location pos where the entity has changed, and slide the window directly to the place where the entity has changed, and the window size remains unchanged;

[0016] Step 7: Use SNM to compare the remaining records in the window, calculate the repetition rate DRate of the window, and reduce the window size according to the repetition rate DRate.

[0017] Preferably, in step 1, records of the same entity are arranged in similar places, and for the same entity, records are sorted by time.

[0018] Preferably, in step 2, the window size w is initialized to 2, and the maximum value of the window size is set to w max .

[0019] Preferably, in step 4, when the theoretical window size S num When it is the same as the actual window size w, that is, S num =w, it means that all records in the window are non-duplicate records, and there is no need to compare the data in the window again. The window size is adjusted to twice the current window size. If the window size is already larger than the maximum window size w max , then w=w max , and the window slides directly to D i+w-1 This round ends.

[0020] Preferably, in step 4, when the first record D in the window i and the last record D i+w-1The recording time is the same, that is, T i =T i+w-1 , it means that the window contains repeated and similar records, and the window size is adjusted to twice the current window; if the window size after adjustment is larger than w max , the window size is still set to the maximum value, that is, w = w max , and slide the window directly to the last record D in the window i+w-1 This round ends.

[0021] Preferably, in step seven, if the repetition rate DRate of the current window is more than twice that of the previous window, the window size is correspondingly reduced to 1 / 2 of the current window; if the records in the window are repeated in pairs, the window size is reduced to 2.

[0022] Preferably, in step 4, the data sampling frequency is f, and the records in the window are <D i ,D i+1 ,D i+2 ,......,D i+w-1 >, the first data D i , the corresponding timestamp is T i , the last piece of data D i+w-1 , the corresponding timestamp is T i+w-1 , for the same entity, from T i to T i+w-1 The theoretical number of records between should be:

[0023] S num =(T i+w-1 -T i )*f.

[0024] Preferably, in step 7, the number of repeated similar records in the window is set to d num , then the repetition similarity of the records in the window is:

[0025] DRate=d num / w.

[0026] The present invention has at least the following beneficial effects:

[0027] Duplicate similar data cleaning is the most commonly used technology in data preprocessing and an important means to improve data quality. Therefore, improving the timeliness of large-scale duplicate similar data detection is of great significance. In this regard, the present invention proposes a duplicate similar data detection algorithm oriented to time series characteristics. The algorithm is based on the traditional neighbor sorting algorithm SNM (basic sorted-neighborhood method), supports dynamic adjustment of window size, and supports window jump sliding. The proposed algorithm greatly reduces the number of comparisons of data records, and both theoretical and experimental results show that the algorithm can effectively improve the detection efficiency of duplicate similar data.

[0028] Other advantages, objectives and features of the present invention will be embodied in part through the following description, and in part will be understood by those skilled in the art through study and practice of the present invention. BRIEF DESCRIPTION OF THE DRAWINGS

[0029] Figure 1 is a schematic diagram showing the time consumption comparison between DS-SNM and SNM under different window sizes and different data set sizes;

[0030] FIG2 is a schematic diagram showing the comparison of the number of comparisons between DS-SNM and SNM under different window sizes and different data set sizes;

[0031] FIG3 is a schematic diagram showing the effect of window size on the number of comparisons. DETAILED DESCRIPTION

[0032] The present invention is further described in detail below in conjunction with the accompanying drawings so that those skilled in the art can implement the invention with reference to the description.

[0033] It should be understood that terms such as “having”, “including” and “comprising” used in the present invention do not specify the existence or addition of one or more other elements or combinations thereof.

[0034] As we all know, in actual applications, duplicate and similar data are usually unevenly distributed. For example, in the data of a certain equipment information management system, some areas have particularly concentrated duplicate and similar records, some areas are very sparse, and most areas have no duplicate records at all. Such data distribution characteristics require the window algorithm to dynamically adjust the window size according to the actual situation. When duplicate and similar records are dense, the window should be reduced, and when duplicate records are sparse, the window should be increased. The automatic expansion and contraction capability of the window size can be used to adapt to the distribution of duplicate and similar records, thereby improving the efficiency of data cleaning.

[0035] On the other hand, for time series data, the time attribute is the key feature for sorting and comparison. The main reason for repeated similarity in time series data is that multiple data records are generated at the same sampling time point. Taking the data of a certain equipment information management system as an example, its data mainly comes from the collection and reporting of a certain equipment at various time points during its service. The sample characteristic attributes include dozens of indicators such as intact rate, failure rate, and remaining life. These data lay the foundation for the subsequent evaluation, prediction and early warning of equipment quality. However, since the system data is mainly entered manually, and there are many steps of manual intervention in a single business process, the data quality is not high, and repeated similar records are easily generated. For example, multiple units repeatedly report, miss report, misreport, and there are subjective indicators. In this scenario, the amount of data is large, and it is required that a single equipment can only have one valid data in the same time unit.

[0036] The aviation quality control data is shown in Table 1. Type indicates the type of equipment, No indicates the id of the equipment, type and No uniquely identify a piece of equipment, and characteristic 1 and characteristic 2 are other attributes of the equipment. Except for characteristic 2, the recording time and entity identifier of items 3 and 4 are the same, so they are similar records, while the records of items 3 and 5 are exactly the same, so they are duplicate records.

[0037] Table 1 Example of repeated data

[0038] Recording time No type Feature 1 Feature 2 2022-01-02 12:13:50 f123 aaa F10 F20 2022-01-03 12:13:50 f123 aaa F10 F22 2022-01-04 12:13:50 T123 aaa F11 F20 2022-01-04 12:13:50 T123 aaa F11 F21 2022-01-04 12:13:50 T123 aaa F11 F20 2022-01-05 12:13:50 T123 aaa F11 F21

[0039] Based on the above data characteristics, the present invention proposes a DS-SNM algorithm for time series data on the basis of the SNM algorithm without reducing the recall rate. The purpose is to improve the problems of difficult window size selection and low efficiency of single-grid window movement in the SNM algorithm.

[0040] DS-SNM mainly introduces the following technical solutions:

[0041] (1) Use the time series characteristics to reduce the number of comparisons. In the SNM window comparison, assuming the window size is w, the last record in the sliding window will be compared w-1 times with the first w-1 records in the window each time. Time series data has a natural time series characteristic. In theory, for the same entity, each sampling time point t i There is only one piece of data. Assuming there are n sampling points in a time block, there should be only n pieces of data. Based on this, we optimize the data comparison in the window. Assume that in window w, entity A has n time series data, and the first piece of data is D i The timestamp of the data is t1, and the last data is D i+w-1, the timestamp of the data is t2, the sampling frequency of the data record is f, when (t2-t1)*f=n, it means that there is no duplicate data in the window, and no additional comparison is required for the data in the window. Therefore, ideally, only one comparison is required in a window, which greatly reduces the number of comparisons.

[0042] (2) Use a jump sliding window to improve the efficiency of window movement. In the SNM algorithm, after each comparison of the window is processed, the displacement of a record will be automatically moved downward. For a data set D containing n data, the window needs to be moved n-w+1 times in total. The present invention optimizes the window sliding method and provides different sliding strategies according to different situations. Assume that there are n time series data in the current window, which are <D0,D1,......,D n-1 >, if the theoretical sampling times in the window period is also equal to n, and the entity types of the first record and the last record in the window are the same, it means that there are no duplicate records in the window, then we directly move the window to the end of the window, that is, D n-1 is the first record of the next window; when the corresponding n records in this window are compared from beginning to end, it is found that there is more than one entity in the window. We first use the binary search method to find the position where the entity changes earliest, and then slide the window to the change position.

[0043] (3) Window size adaptation. The SNM algorithm uses a fixed-size window. The choice of window size not only has a great impact on the efficiency of clearing similar duplicate records, but also affects the recognition accuracy. To this end, the present invention introduces a window adaptation strategy. When the records in the current window are completely non-duplicate or completely duplicated, the next window size is adjusted to twice the current one; when the repetition rate in the current window is more than twice the repetition rate of the previous window, the next window size is reduced to half of the current one; when the data in the current window are repeated in pairs, the window size is directly reduced to 2.

[0044] Example:

[0045] A time series oriented repeated similar data cleaning optimization method comprises the following steps:

[0046] Step 1: Sort the records according to the selected keywords to obtain a sorted record set D, wherein the keywords include a time attribute T and an entity identifier N0; sort the records of the same entity in similar places, and for the same entity, the records are sorted by time.

[0047] Step 2: Initialize the window parameters for comparing duplicate similar records; initialize the size of the window w to 2 and set the maximum window size w max .

[0048] Let the window size be w, the initial value be 2, and the maximum value be wmax , the data sampling frequency is f, and the records in the window are <D i ,D i+1 ,D i+2 ,......,D i+w-1 >, each record contains at least two characteristic attributes: timestamp T and entity identifier N0. The first data D i , the corresponding timestamp is T i , the last piece of data D i+w-1 , the corresponding timestamp is T i+w-1 , then, for the same entity, from T i to T i+w-1 The theoretical number of records between should be:

[0049] S num =(T i+w-1 -T i )*f (1)

[0050] Let the number of repeated similar records in the window be d num , then the repetition similarity of the records in the window is:

[0051] DRate=d num / w (2)

[0052] Step 3: Compare the first record D in the window i and the last record D i+w-1 If the entity identifier N0 attribute is the same entity, go to step 4, otherwise go to step 6;

[0053] Step 4: Calculate the first record D in the window i and the last record D i+w-1 Theoretical number of records S num , the theoretical record number S num Compared with the window size w, if the theoretical window size S num The same as the actual window size w, the window size is enlarged; specifically, when the theoretical window size S num When it is the same as the actual window size w, that is, S num =w, it means that all records in the window are non-duplicate records, and there is no need to compare the data in the window again. The window size is adjusted to twice the current window size. If the window size is already larger than the maximum window size w max , then w=w max , and the window slides directly to D i+w-1 This round ends.

[0054] If the first record in the window is D i and the last record D i+w-1The time of the first record D in the window is the same, then the window size is enlarged; specifically, when the first record D in the window i and the last record D i+w-1 The recording time is the same, that is, T i =T i+w-1 , it means that the window contains repeated and similar records, and the window size is adjusted to twice the current window; if the window size after adjustment is larger than w max , the window size is still set to the maximum value, that is, w = w max , and slide the window directly to the last record D in the window i+w-1 This round ends.

[0055] Otherwise, it means there are duplicate records or missing records in the window, and go to step 5;

[0056] Step 5: Use binary search to find the earliest duplicate record and go to step 7;

[0057] Step 6: If the entity has changed, use the binary search method to backtrack to find the location pos where the entity has changed, and slide the window directly to the place where the entity has changed, and the window size remains unchanged;

[0058] Step 7: Use SNM to compare the remaining records in the window, calculate the window repetition rate DRate, and reduce the window size according to the repetition rate DRate. Specifically, if the repetition rate DRate of the current window is more than twice the repetition rate DRate of the previous window, the window size is reduced to 1 / 2 of the current window; if the records in the window are repeated in pairs, the window size is reduced to 2. The repetition in pairs here means: the first and second records are repeated, the second and third records are not repeated, the third and fourth records are repeated, the fourth and fifth records are not repeated, and so on.

[0059] Comparative Example:

[0060] The present invention applies DS-SNM to the data of a certain equipment information management system, and selects 500,000 continuous records for experiments. The data sampling rate is one record per day, and each record contains multiple attributes such as unit, time, equipment model, factory number, status, etc. The equipment model and factory number are used to uniquely identify a single equipment entity, and in theory, the record table should have only one record per equipment per day. However, in practice, there are many duplicate data because multiple units may record a single equipment at the same time in a day, or similar records may be manually input multiple times.

[0061] Different window sizes of SNM and DS-SNM algorithms are used, with DS-SNM as the largest window size, and data sets of different magnitudes, ranging from 50,000 to 500,000, are used for timeliness analysis. The experimental results are compared in Figures 1, 2, and 3.

[0062] From the experimental results, we can see that the improved algorithm DS-SNM has greatly improved the efficiency compared with the original SNM algorithm under different scales of data sets, and the efficiency improvement is more obvious as the order of magnitude increases. At the same time, the number of comparisons of the original SNM algorithm will increase exponentially as the window becomes larger, resulting in a significant increase in time consumption, while the increase in the number of comparisons brought about by the increase in the maximum window in DS-SNM is slight, so the performance of this algorithm is more robust to changes in window size.

[0063] As mentioned above, time series data is generated over time, the data volume is large, and the timeliness requirement is high. Therefore, it is very necessary to reduce the time complexity of the detection algorithm. The present invention improves the traditional SNM algorithm for repeated similar data detection and cleaning of time series data, and proposes a DS-SNM algorithm. The algorithm introduces a variety of effective mechanisms, which greatly reduces the number of data comparisons and reduces the time complexity of the algorithm. The algorithm is particularly suitable for large-scale time series data processing, and experimental results also show that the algorithm significantly improves the detection efficiency of repeated similar data.

[0064] Although the embodiments of the present invention have been disclosed as above, they are not limited to the applications listed in the specification and the implementation modes, and they can be fully applied to various fields suitable for the present invention. For those familiar with the art, additional modifications can be easily implemented. Therefore, without departing from the general concept defined by the claims and the scope of equivalents, the present invention is not limited to the specific details and the illustrations shown and described herein.

Claims

1. A time-series oriented repeated similar data cleaning optimization method, characterized in that: The following steps are involved: Step 1: Sort the records according to the selected keywords to obtain a sorted record set D, wherein the keywords include a time attribute T and an entity identifier N0; Step 2: Initialize the window parameters for comparing duplicate similar records; Step 3: Compare the first record D in the window i and the last record D i+w-1 If the entity identifier N0 attribute is the same entity, go to step 4, otherwise go to step 6; Step 4: Calculate the first record D in the window i To the last record D i+w-1 Theoretical number of records S num , the theoretical record number S num Compared with the window size w, if the theoretical window size S num If it is the same as the actual window size w, the window size is enlarged; If the first record in the window is D i and the last record D i+w-1 If the time is the same, the window size is enlarged; Otherwise, it means there are duplicate records or missing records in the window, and go to step 5; Step 5: Use binary search to find the earliest duplicate record and go to step 7; Step 6: If the entity has changed, use the binary search method to backtrack to find the location pos where the entity has changed, and slide the window directly to the place where the entity has changed, and the window size remains unchanged; Step 7: Use SNM to compare the remaining records in the window, calculate the repetition rate DRate of the window, and reduce the window size according to the repetition rate DRate; Among them, in the step 4, the data sampling frequency is f, and the records in the window are <D i ,D i+1 ,D i+2 ,......,D i+w-1 >, the first data D i , the corresponding timestamp is T i , the last piece of data D i+w-1 , the corresponding timestamp is T i+w-1 , for the same entity, from T i to T i+w-1 The theoretical number of records between should be: S num =(T i+w-1 -T i )*f。 2. The time series oriented repeated similar data cleaning optimization method according to claim 1, characterized in that: In the step 1, the records of the same entity are arranged in close places, and for the same entity, the records are sorted by time.

3. The time-series oriented repeated similar data cleaning optimization method according to claim 2, characterized in that: In step 2, the window size w is initialized to 2, and the maximum value of the window size is set to w max .

4. The time-series oriented repeated similar data cleaning optimization method according to claim 3, characterized in that: In step 4, when the theoretical window size S num When it is the same as the actual window size w, that is, S num =w, it means that all records in the window are non-duplicate records, and there is no need to compare the data in the window again. The window size is adjusted to twice the current window; If the window size is already larger than the maximum window w max , then w=w max , and the window slides directly to D i+w-1 This round ends.

5. The time-series oriented repeated similar data cleaning optimization method according to claim 4, characterized in that: In step 4, when the first record D in the window i and the last record D i+w-1 The recording time is the same, that is, T i =T i+w-1 , it means that the window contains only repeated and similar records, and the window size is adjusted to twice the current window; If the window size is larger than w after adjustment max , the window size is still set to the maximum value, that is, w = w max , and slide the window directly to the last record D in the window i+w-1 This round ends.

6. The time-series oriented repeated similar data cleaning optimization method according to claim 5, characterized in that: In step seven, if the repetition rate DRate of the current window is more than twice that of the previous window, the window size is reduced to 1 / 2 of the current window; if the records in the window are repeated in pairs, the window is reduced to 2.

7. The time-series oriented repeated similar data cleaning optimization method according to claim 6, characterized in that: In step 7, let the number of repeated similar records in the window be d num , then the repetition similarity of the records in the window is: DRate=d num / w。

Images