Table merging method, processing chip and electronic device
By identifying and calculating the similarity of table data column types, the information fusion between irregular tables is achieved, and the problem of inconsistent formats and time-consuming and labor-consuming in the process of table sorting and summary in the prior art is solved, and the accuracy and efficiency are improved .
Patent Information
- Application Number
- CN202111180761.6
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- Filing Date
- 2021-10-11
- Publication Date
- 2024-11-26
- Estimated Expiration
- 2041-10-11
AI Technical Summary
When handling business forms with inconsistent recording methods of different departments or personnel, the existing technology has problems such as inconsistent format, time-consuming and labor-consuming, accuracy and insufficient information during the collation and summary process.
By identifying the table data column type, calculating the similarity, and merging the table based on similarity matching, the information fusion between irregular tables is achieved. Specific methods include word segmentation, standardization processing and probability distribution calculation of text, numeric and date data columns, calculating similarity, and merging data columns according to preset thresholds.
Improve the accuracy and efficiency of table merging, maximize the use of table data column information, and realize efficient fusion of irregular tables.
Smart Images

Figure CN114254599B_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the field of computer technology, and in particular to a table merging method, a processing chip and an electronic device. Background Art
[0002] At present, for the same type of business, different departments or personnel will adopt different recording methods, resulting in different column names and different writing formats of the content in the cells in the columns; for example, for dates, there are many recording formats such as "year / month / day, year-month-day, year.month.day", etc., which results in inconsistent formats of recorded business tables, which need to be sorted and summarized later.
[0003] However, in the prior art, manual collation and aggregation of business tables requires a lot of time and human resources; collation and aggregation can also be performed using computers through similarity matching of column names, but there are problems with poor accuracy and amount of available information. Summary of the invention
[0004] The purpose of the present invention is to provide a table merging method, a processing chip and an electronic device. When merging two tables in each table pair, the similarity is first calculated for different types of data columns between the two tables, and then the data columns are merged based on the similarity between the same type of data columns between the two tables. That is, the fusion of irregular tables is achieved based on the similarity matching between the data columns, which can maximize and effectively use the information of the data columns in the tables with high accuracy.
[0005] To achieve the above-mentioned purpose, the present invention provides a table merging method, comprising: for multiple tables to be merged, identifying the type of each data column of each table; generating multiple table pairs to be merged based on the multiple tables, and calculating the similarity between the data columns of the same type between the two tables contained in each table pair; based on the similarity between the data columns of the same type between the two tables in each table pair, merging the data columns of the same type between the two tables in each table pair.
[0006] The present invention also provides a processing chip for executing the above table merging method.
[0007] The present invention also provides an electronic device, comprising: at least one processing chip; and a memory communicatively connected to the at least one processing chip; wherein the memory stores instructions executable by the at least one processing chip, and the instructions are executed by the at least one processing chip so that the at least one processing chip can execute the table merging method as described above.
[0008] In an embodiment of the present invention, for a plurality of tables to be merged, the type of each data column of each of the tables is identified, and then a plurality of table pairs to be merged are generated based on the plurality of tables, and the similarity between the data columns of the same type between the two tables contained in each of the table pairs is calculated, and then based on the similarity between the data columns of the same type between the two tables in each of the table pairs, the data columns of the same type between the two tables in each of the table pairs are merged. Thus, when merging the two tables in each table pair, the similarity is first calculated for the different types of data columns between the two tables, and then the data columns are merged in combination with the similarity between the data columns of the same type between the two tables, that is, the fusion between irregular tables is achieved based on the similarity matching between the data columns, and the information of the data columns in the tables can be maximized and effectively used with high accuracy.
[0009] In one embodiment, the similarity between the data columns of the same type between the two tables included in each table pair is calculated, including: for each table in each table pair, respectively segmenting the text data columns of each text type in the table to obtain multiple words, and counting the frequency of each word in each text data column; based on the frequency of the words in the text data columns of the two tables included in each table pair, obtaining the similarity between the text data columns of the two tables in each table pair.
[0010] In one embodiment, the similarity between the same type of data columns between the two tables included in each table pair is calculated, including: for each table in each table pair, respectively standardizing each digital data column in the table and obtaining the probability distribution of each digital data column; based on the probability distribution of the digital data columns in the two tables included in each table pair, obtaining the similarity between the digital data columns between the two tables in each table pair.
[0011] In one embodiment, the similarity between data columns of the same type between two tables included in each table pair is calculated, including: for each table pair, if there are multiple date data columns of date type in both tables in the table pair, multiple date data columns in each table in the table pair are combined to obtain at least one date data column pair, and distribution parameter values are calculated between two date data columns in each date data column pair of each table in the table pair; based on the distribution parameter values of the date data column pairs in the two tables in each table pair, the similarity between the date data column pairs between the two tables in each table pair is obtained.
[0012] In one embodiment, generating multiple table pairs that need to be merged based on the multiple tables includes: selecting one of the multiple tables as a base table; and combining each of the multiple tables except the base table with the base table as the table pairs that need to be merged.
[0013] In one embodiment, based on the similarity between the data columns of the same type between the two tables in each table pair, the data columns of the same type between the two tables in each table pair are merged, including: for a data column pair consisting of two data columns of the first type respectively derived from the two tables in each table pair, selecting at least one data column pair from the data column pairs of the first type whose similarity is greater than a preset similarity threshold according to the similarity from large to small as a matching column pair, wherein the first type is a text type or a date type; and merging the two data columns of the first type respectively derived from the two tables of the same table pair contained in each matching column pair.
[0014] In one embodiment, based on the similarity between the data columns of the same type between the two tables in each table pair, the data columns of the same type between the two tables in each table pair are merged, including: for a data column pair consisting of two data columns of the second type respectively derived from the two tables in each table pair, at least one data column pair is selected from the data column pairs of the second type whose similarity is greater than a preset similarity threshold, with the sum of the similarities of the selected data column pairs being maximized as an optimization target, and each selected data column pair is used as a matching column pair, wherein the second type is a digital type; and the two data columns of the second type respectively derived from the two tables in the same table pair and contained in each matching column pair are merged.
[0015] In one embodiment, based on the frequency of words in the text data columns of the two tables included in each table pair, the similarity between the text data columns between the two tables in each table pair is obtained, including: for two target text data columns respectively derived from the two tables included in each table pair, based on the number of identical words in the words whose frequencies in the two target text data columns meet a first preset condition, the similarity between the target text data columns is obtained.
[0016] In one embodiment, based on the probability distribution of the digital data columns in the two tables included in each table pair, the similarity between the digital data columns between the two tables in each table pair is obtained, including: for two target digital data columns respectively derived from the two tables included in each table pair, based on the probability distribution of the two target digital data columns, a divergence value between the two target digital data columns is calculated as the similarity between the two target digital data columns.
[0017] In one embodiment, for each table in each of the table pairs, each of the digital data columns in the table is standardized and the probability distribution of each of the digital data columns is obtained, including: for each of the tables in each of the table pairs, the digital data columns in the table that meet the second preset condition are removed, and each of the digital data columns remaining in the table after the digital data columns that meet the second preset condition are removed is standardized, and the probability distribution of each of the digital data columns is obtained.
[0018] In one embodiment, calculating the distribution parameter value between two of the date data columns in each of the date data column pairs of each of the tables in the table pair includes: for each of the date data column pairs in each of the tables in the table pair, based on the difference between two cells located in the same row between the two date data columns in the date data column pair, calculating the mean and variance of the difference between the two date data columns in the date data column pair, and calculating the sum of the mean and variance of the date data column pair as the distribution parameter value of the date data column.
[0019] In one embodiment, the calculation formula for the similarity between the two pairs of date data columns respectively derived from the two tables in the table pair is:
[0020]
[0021] Wherein, a and b represent the distribution parameter values of the date data columns of the two target tables respectively. BRIEF DESCRIPTION OF THE DRAWINGS
[0022] Figure 1 is a specific flow chart of the table merging method according to the first embodiment of the present invention;
[0023] Figure 2 is a schematic diagram of Table 1 in the first embodiment of the present invention;
[0024] Figure 3 is a specific flow chart of the table merging method according to the second embodiment of the present invention;
[0025] Figure 4is a lexical frequency statistical graph after word segmentation of the legal status-text data column in Table 1 in the second embodiment of the present invention;
[0026] Figure 5 is a vocabulary frequency statistics chart after word segmentation of the asset highlight-text data column in Table 1 in the second embodiment of the present invention;
[0027] Figure 6 is a specific flow chart of the table merging method according to the third embodiment of the present invention;
[0028] Figure 7 is a schematic diagram of Table 2 according to the third embodiment of the present invention;
[0029] Figure 8 is a schematic diagram of Table 3 in the third embodiment of the present invention;
[0030] Fig. 9 is a schematic diagram of the probability distribution of the principal-digital data column and the probability distribution of the interest-digital data column in Table 2 in the third embodiment of the present invention;
[0031] Fig.10 is a schematic diagram of the probability distribution of the principal-digital data column and the probability distribution of the interest-digital data column in Table 3 in the third embodiment of the present invention;
[0032] Fig.11 is a specific flow chart of the table merging method according to the fourth embodiment of the present invention;
[0033] Fig.12 is a specific flow chart of the table merging method according to the fifth embodiment of the present invention;
[0034] Fig.13 is a schematic diagram of a plurality of tables according to a fifth embodiment of the present invention;
[0035] Fig.14 is a schematic diagram of Table 4 in a sixth embodiment of the present invention;
[0036] Fig.15 4 is a schematic diagram of Table 5 obtained by merging Tables 1 to 4 in the sixth embodiment of the present invention. DETAILED DESCRIPTION
[0037] The following will be described in detail with reference to the accompanying drawings to provide a clearer understanding of the purpose, features and advantages of the present invention. It should be understood that the embodiments shown in the accompanying drawings are not intended to limit the scope of the present invention, but are only intended to illustrate the essential spirit of the technical solution of the present invention.
[0038] In the following description, certain specific details are set forth for the purpose of illustrating various disclosed embodiments to provide a thorough understanding of the various disclosed embodiments. However, those skilled in the relevant art will recognize that the embodiments may be practiced without one or more of these specific details. In other cases, well-known devices, structures, and techniques associated with the present application may not be shown or described in detail to avoid unnecessarily obscuring the description of the embodiments.
[0039] Unless the context requires otherwise, throughout the specification and claims, the word "comprise" and variations such as "include" and "have" should be construed in an open, inclusive sense, ie, should be interpreted as "including, but not limited to."
[0040] References throughout the specification to "one embodiment" or "an embodiment" indicate that a particular feature, structure, or characteristic described in connection with the embodiment is included in at least one embodiment. Thus, the appearances of "in one embodiment" or "in an embodiment" in various places throughout the specification are not necessarily all referring to the same embodiment. Furthermore, the particular features, structures, or characteristics may be combined in any manner in one or more embodiments.
[0041] As used in this specification and the appended claims, the singular forms "a", "an", and "" include plural referents unless the context clearly dictates otherwise. It should be noted that the term "or" is generally employed in its sense including "or / and" unless the context clearly dictates otherwise.
[0042] In the following description, in order to clearly show the structure and working mode of the present invention, many directional words will be used for description, but the words "front", "back", "left", "right", "outside", "inside", "outward", "inward", "up", "down", etc. should be understood as convenient terms and should not be understood as restrictive terms.
[0043] The first embodiment of the present invention relates to a table merging method, which is applied to a processing chip in an electronic device, such as a mobile phone, a tablet computer, a laptop computer, a desktop host, etc. The processing chip can execute the table merging method in this embodiment to merge columns with high similarity in multiple tables.
[0044] The specific process of the table merging method in this embodiment is as follows Figure 1 shown.
[0045] Step 101 : for a plurality of tables to be merged, identify the type of each data column of each table.
[0046] Specifically, for each table, the type of the data column in the table includes any one or any combination of the following: text type, number type, date type; when the electronic device receives each table to be merged, it identifies the content in the cells contained in each data column in the table, and obtains the type of each data column contained in the table based on the format of the content in the cells of each data column. Please refer to Figure 2 , which is a schematic diagram of an exemplary Table 1, it can be identified that the borrower-data column is a text-type data column, the principal-data column is a numeric-type data column, and the types of other data columns are not listed one by one here.
[0047] Step 102: Generate multiple table pairs to be merged based on multiple tables, and calculate the similarity between the same type of data columns between two tables included in each table pair.
[0048] Specifically, if the number of tables to be merged is 2, the two tables are directly combined as the table pair to be merged; if the number of tables to be merged is greater than 2, the tables to be merged can be arranged and combined to obtain multiple table pairs to be merged, and the similarity between the data columns of the same type between the two tables included in each table pair is calculated respectively. Taking the two tables included in any table pair as an example, the similarity between the data columns of text type, the similarity between the data columns of number type, and the similarity between the data columns of date type between the two tables are calculated respectively.
[0049] Step 103 : based on the similarity between the data columns of the same type between the two tables in each table pair, merge the data columns of the same type between the two tables in each table pair.
[0050] Specifically, for each table pair, based on the similarity between the same type of data columns between the two tables in the table pair, two data columns that meet the requirements are selected to form a matching column pair, and the two same type of data columns in the matching column pair are merged, and then the column name of one of the tables in the matching column pair can be selected as the column name of the merged data column, or the column names of the two data columns are retained as the column name of the merged data column. After all table pairs are merged, the table pairs can be further merged, so that all tables can be merged.
[0051] This embodiment provides a table merging method, for multiple tables to be merged, the type of each data column of each table is identified, and then multiple table pairs to be merged are generated based on the multiple tables, and the similarity between the data columns of the same type between the two tables contained in each table pair is calculated, and then based on the similarity between the data columns of the same type between the two tables in each table pair, the data columns of the same type between the two tables in each table pair are merged. Therefore, when merging the two tables in each table pair, the similarity is first calculated for the different types of data columns between the two tables, and then the data columns are merged in combination with the similarity between the data columns of the same type between the two tables, that is, the fusion between irregular tables is achieved based on the similarity matching between the data columns, which can maximize and effectively use the information of the data columns in the tables, and has high accuracy.
[0052] The second embodiment of the present invention relates to a table merging method. Compared with the first embodiment, this embodiment provides a specific implementation method for calculating the similarity between text-type data columns.
[0053] The specific process of the table merging method in this embodiment is as follows Figure 3 shown.
[0054] Step 201, for multiple tables to be merged, identify the type of each data column in each table. This is substantially the same as step 101 of the first embodiment and will not be described in detail here.
[0055] Step 202 includes the following sub-steps:
[0056] Sub-step 2021: generating multiple table pairs to be merged based on multiple tables.
[0057] Specifically, if the number of tables to be merged is 2, the two tables are directly combined as the table pair to be merged; if the number of tables to be merged is greater than 2, the tables to be merged can be arranged and combined to obtain multiple table pairs to be merged, and the similarity between the data columns of the same type between the two tables contained in each table pair is calculated respectively.
[0058] Sub-step 2022: for each table in each table pair, the text data column of each text type in the table is segmented to obtain a plurality of words, and the frequency of each word in each text data column is counted.
[0059] Sub-step 2023 , based on the frequencies of the words in the text data columns of the two tables included in each table pair, obtain the similarity between the text data columns of the two tables in each table pair.
[0060] Specifically, taking any table pair as an example, the table pair includes table A and table B, table A includes N text data columns, and table B includes M text data columns. When calculating the similarity between the text data columns of table A and table B, firstly segment the text data columns in table A and table B to obtain the words contained in each text data column, and then count the frequency of each word in each text data column, thereby obtaining the words and the frequency of the words in the (M+N) text data columns contained in table A and table B, where M and N are both integers greater than or equal to 1. Please refer to Figure 4 and Figure 5 , for Figure 2 Taking Table 1 in FIG. 1 as an example, after the legal status-text data column and the asset highlights-text data column in Table 1 are segmented, the frequency of the obtained words is counted to obtain a statistical chart. Among them, the text data column can be segmented by using bigram (2-gram) segmentation.
[0061] After obtaining the vocabulary and vocabulary frequencies of the (M+N) text data columns contained in Table A and Table B, select a text data column A' from the N text data columns in Table A and select a text data column B' from the M text data columns in Table B, and obtain the similarity between text data column A' and text data column B' based on the vocabulary and the frequency of each vocabulary contained in text data column A' and the vocabulary and the frequency of each vocabulary contained in text data column B'; thus, the N text data columns in Table A and the M text data columns in Table B are arranged and combined to calculate N*M similarities. For example, N=5, that is, Table A includes 5 text data columns, which are recorded as A1, A2, A3, A4, and A5 respectively; M=5, that is, Table B includes 5 text data columns, which are recorded as B1, B2, B3, B4, and B5 respectively. Based on the vocabulary and frequency in the 10 text data columns A1-A5 and B1-B5, the similarities between A1 to A5 and B1 to B5 can be calculated respectively, that is, 25 similarities can be calculated, as shown in the following Table 1, in which only 25 similarity values are given schematically.
[0062] Table 1
[0063] B1 B2 B3 B4 B5 A1 0 4 0 4 0 A 2 0 0 6 0 5 A 3 0 5 0 4 0 A 4 5 0 0 0 7 A 5 0 0 0 0 5
[0064] In one example, based on the frequency of words in the text data columns of the two tables included in each table pair, the similarity between the text data columns of the two tables in each table pair is obtained, including: for two target text data columns respectively derived from the two tables included in each table pair, based on the number of identical words in the words whose frequencies meet the first preset condition in the two target text data columns, the similarity between the target text data columns is obtained. Continuing from the above, a text data column A' is selected from the N text data columns in table A, and a text data column B' is selected from the M text data columns in table B, and the text data column A' and the text data column B' are the target text data columns. When calculating the similarity between the text data column A' and the text data column B', the words that meet the first preset condition are selected from the text data column A' and the text data column B', respectively, and the words that meet the first preset condition in the text data column A' are compared with the words that meet the first preset condition in the text data column B', and the same words are selected therefrom, and the number of the same words is counted, and the number can be used to represent the similarity between the text data column A' and the text data column B'. The first preset condition is, for example, that the frequency of the words in the text data column is the top 50% of the frequency of all the words. Figure 4 , then in the legal status-text data column, the words that meet the first preset condition include: execution, unlitigated, bankruptcy, part, sued, termination, bankruptcy, division, loan, and part. It should be noted that the proportion of the words of the first preset condition in the frequency of all words in the text data column can also be increased or decreased as needed.
[0065] Step 203 includes the following sub-steps:
[0066] Sub-step 2031, for a data column pair consisting of two first-type data columns from two tables respectively derived from each table pair, at least one data column pair is selected as a matching column pair from the first-type data column pairs whose similarity is greater than a preset similarity threshold according to the similarity from large to small, wherein the first type is a text type.
[0067] Specifically, for a data column pair consisting of text data columns of two text types respectively derived from two tables of each table pair, the similarity between the two text data columns in each data column pair is obtained, and a greedy matching algorithm is used to perform similarity matching between the text data columns, and the data column pair with the greatest similarity is preferentially selected. If the similarity of the data column pair is greater than a preset similarity threshold, the data column pair is used as a matching column pair; if the similarity of the data column pair is less than or equal to the preset similarity threshold, the similarity matching is stopped; after the data column pair with the greatest similarity is used as a matching column pair, the data column pair containing any one text data column in the data column pair with the greatest similarity is eliminated, and then the data column pair with the greatest similarity is selected from the remaining data column pairs, and it is determined whether the similarity of the data column pair is greater than the preset similarity threshold, and the above process is repeated until the similarity of the selected data column pair is less than or equal to the preset similarity threshold. Taking Table 1 above as an example, the preset similarity threshold is 5, and the order of data column pairs selected by the greedy matching algorithm is: A4-B5 (7), A2-B3 (6), A3-B2 (5). Among them, giving priority to selecting data column pairs with large similarity can ensure the accuracy of similarity matching as much as possible.
[0068] Sub-step 2032: merge two data columns of the first type contained in each matching column pair and respectively originating from two tables of the same table pair.
[0069] Specifically, after obtaining the matching column pairs contained in each table pair, for each matching column pair, two text data columns in the matching column pair are merged, and the column name of the merged text data column can be selected from the column name of any of the two text data columns.
[0070] The third embodiment of the present invention relates to a table merging method. Compared with the first embodiment, this embodiment provides a specific implementation method for calculating the similarity between digital data columns.
[0071] The specific process of the table merging method in this embodiment is as follows Figure 6 shown.
[0072] Step 301, for multiple tables to be merged, identify the type of each data column in each table. This is substantially the same as step 101 of the first embodiment and will not be described in detail here.
[0073] Step 302 includes the following sub-steps:
[0074] Sub-step 3021: generating multiple table pairs to be merged based on multiple tables.
[0075] Specifically, if the number of tables to be merged is 2, the two tables are directly combined as the table pair to be merged; if the number of tables to be merged is greater than 2, the tables to be merged can be arranged and combined to obtain multiple table pairs to be merged, and the similarity between the data columns of the same type between the two tables contained in each table pair is calculated respectively.
[0076] Sub-step 3022: for each table in each table pair, standardize each digital data column in the table and obtain the probability distribution of each digital data column.
[0077] Specifically, taking any table pair as an example, the table pair includes table A and table B, table A includes P digital data columns, table B includes Q digital data columns, when calculating the similarity between the digital data columns of table A and table B, the P digital data columns in table A and the Q digital data columns in table B are standardized respectively, and the mean of each digital data column after standardization is 0; then the number of data in each digital data column after standardization that falls within different intervals is counted, and the number of each number in each digital data column that falls within different intervals is converted into a percentage, thereby obtaining the probability distribution of each digital data column, where P and Q are both integers greater than or equal to 1. Please refer to Figure 7 and Figure 8 , respectively, Table 2 and Table 3, both Table 2 and Table 3 include principal-digital data columns and interest-digital data columns. Based on the above method, the probability distribution of the principal-digital data column and the probability distribution of the interest-digital data column in Table 2 can be obtained, such as Fig. 9 Based on the above method, the probability distribution of the principal-digital data column and the probability distribution of the interest-digital data column in Table 3 can be obtained, as shown in Fig.10 shown.
[0078] In one example, for each table in each table pair, each digital data column in the table is standardized and the probability distribution of each digital data column is obtained, including: for each table in each table pair, the digital data column that meets the second preset condition is removed from the table, and each digital data column remaining after the digital data column that meets the second preset condition is removed from the table is standardized, and the probability distribution of each digital data column is obtained. That is, before calculating the probability distribution of each digital data column, the digital data column that meets the second preset condition in the table is first removed, thereby reducing the subsequent calculation amount.
[0079] The second preset condition can be a data column of a serial number type, a repeated data column, and a data column obtained by performing a function operation on other data columns. Figure 8Taking Table 3 in as an example, the cells in the serial number-numeric data column contain orderly changing values, so the serial number-numeric data column can be removed; the cells in the package row-numeric data column contain repeated content, so the package row-numeric data column can be removed; the total-numeric data column is the sum of the values of each cell in the principal-numeric data column and the values of the corresponding cells in the interest-numeric data column, so the total-numeric data column can be removed.
[0080] Sub-step 3023, based on the probability distribution of the digital data columns in the two tables included in each table pair, obtain the similarity between the digital data columns between the two tables in each table pair.
[0081] Specifically, after obtaining the probability distribution of the (P+Q) digital data columns contained in Table A and Table B, a digital data column An is selected from the P digital data columns in Table A and a digital data column Bn is selected from the Q digital data columns in Table B. Based on the probability distribution of digital data column An and the probability distribution of digital data column Bn, the similarity between digital data column An and digital data column Bn can be obtained; thus, the P digital data columns in Table A and the Q digital data columns in Table B are arranged and combined to calculate P*Q similarities.
[0082] In one example, based on the probability distribution of the digital data columns in the two tables included in each table pair, the similarity between the digital data columns between the two tables in each table pair is obtained, including: for two target digital data columns respectively derived from the two tables included in each table pair, based on the probability distribution of the two target digital data columns, the divergence value between the two target digital data columns is calculated as the similarity between the two target digital data columns. Continuing from the above, a digital data column An is selected from the N digital data columns in table A, and a digital data column Bn is selected from the M digital data columns in table B, and the digital data column An and the digital data column Bn are the target digital data columns. When calculating the similarity between the digital data column An and the digital data column Bn, based on the probability distribution of the digital data column An and the probability distribution of the digital data column Bn, the divergence between the digital data column An and the digital data column Bn is calculated as the similarity between the digital data column An and the digital data column Bn. Among them, Kullback-Leibler divergence (KL divergence for short) can be used to measure the similarity between the digital data column An and the digital data column Bn. The smaller the KL divergence is, the higher the similarity between the digital data column An and the digital data column Bn is.
[0083] Step 303 includes the following sub-steps:
[0084] Sub-step 3031, for a data column pair consisting of two data columns of the second type respectively derived from two tables of each table pair, at least one data column pair is selected from the data column pairs of the second type whose similarity is greater than a preset similarity threshold, with the sum of the similarities of the selected data column pairs being maximized as the optimization target, and each selected data column pair is used as a matching column pair, wherein the second type is a digital type.
[0085] Specifically, for data column pairs consisting of two digital data columns of digital type respectively derived from two tables of each table pair, the similarity between the two digital data columns in each data column pair is obtained, and data column pairs whose similarity is greater than a preset similarity threshold are screened out, and a global optimal matching algorithm (optimal method) is used to select data column pairs from the data column pairs whose similarity is greater than the preset similarity threshold, so that the sum of the similarities of all the selected data column pairs is maximized, thereby being able to select as many data column pairs as possible.
[0086] For example, N=5, that is, Table A includes 5 digital data columns, which are recorded as A1, A2, A3, A4, and A5 respectively; M=5, that is, Table B includes 5 digital data columns, which are recorded as B1, B2, B3, B4, and B5 respectively. Based on the vocabulary and frequency in the 10 digital data columns A1-A5 and B1-B5, the similarities between A1 to A5 and B1 to B5 can be calculated respectively, that is, 25 similarities can be calculated, as shown in the following Table 2, in which only 25 similarity values are given schematically.
[0087] Table 2
[0088] B1 B2 B3 B4 B5 A1 0 4.5 0 5.5 0 A2 0 0 5.9 0 4.9 A3 0 5.8 0 4.7 0 A4 5.3 0 0 0 6.8 A5 0 0 0 0 5.1
[0089] In the above Table 2, the preset similarity threshold is, for example, 0, and the data column pairs selected by the global optimal matching algorithm include: A1-B4 (5.5), A 2-B 3 (5.9), A 3-B 2 (5.8), A 4-B 1 (5.3), A 5-B 5 (5.1). It should be noted that in this embodiment, only the similarity threshold of 0 is used as an example for explanation, and the similarity threshold can also be set to a value greater than 0. The larger the value selected for the similarity threshold, the higher the matching requirement between the digital data columns.
[0090] Sub-step 3032: merge two data columns of the second type contained in each matching column pair and respectively originating from two tables of the same table pair.
[0091] Specifically, after obtaining the matching column pairs contained in each table pair, for each matching column pair, the two digital data columns in the matching column pair are merged, and the column name of the merged digital data column can be selected from the column name of any of the two digital data columns.
[0092] The fourth embodiment of the present invention relates to a table merging method. Compared with the first embodiment, this embodiment provides a specific implementation method for calculating the similarity between data columns of the date type.
[0093] The specific process of the table merging method in this embodiment is as follows Fig.11 shown.
[0094] Step 401, for multiple tables to be merged, identify the type of each data column in each table. This is substantially the same as step 101 of the first embodiment and will not be described in detail here.
[0095] Step 402 includes the following sub-steps:
[0096] Sub-step 4021, generating multiple table pairs to be merged based on multiple tables.
[0097] Specifically, if the number of tables to be merged is 2, the two tables are directly combined as the table pair to be merged; if the number of tables to be merged is greater than 2, the tables to be merged can be arranged and combined to obtain multiple table pairs to be merged, and the similarity between the data columns of the same type between the two tables contained in each table pair is calculated respectively.
[0098] Sub-step 4022, for each table pair, if there are multiple date data columns of date type in both tables in the table pair, combine the multiple date data columns in each table in the table pair to obtain at least one date data column pair, and calculate the distribution parameter value between the two date data columns in each date data column pair of each table in the table pair.
[0099] Specifically, for each table pair, it is necessary to first determine whether both tables in the table pair include multiple date data columns of date type. If there is any table that does not include multiple date data columns of date type, there is no need to merge the date data columns of date type for the table pair; if both tables in the table pair include multiple date data columns of date type, for each table in the table pair, the multiple date data columns in the table are combined to obtain multiple date data column pairs, and the distribution parameter value between the two date data columns in each date data column pair is calculated respectively; specifically, when combining multiple date data columns in the table, the date data columns in the table can be sorted first, and the sorting method is, for example, to sort the date data columns from small to large according to the median of the date data columns, and then starting from the first sorted date data column, the date data column and the subsequent date data columns are respectively formed into date data column pairs.
[0100] Take any table pair as an example, the table pair includes table A and table B, table A includes X (X is an integer greater than or equal to 2) date data columns, table B includes Y (Y is an integer greater than or equal to 2) date data columns, which are y1, y2, ..., y Y In table A, the X date data columns are sorted as x1, x2, ..., x X , from which we can get the date data column pairs: x1-x2, x1-x3, ..., x1-x X ,x2-x3,x2-x4,…,x2-x X ,x3-x4,x3-x5,…,x3-x X ,……,x X-1 -x X Similarly, in table B, after sorting, the Y date data columns are y1, y2, ..., y Y , from which we can get the date data column pairs: y1-y2, y1-y3, ..., y1-y Y ,y2-y3,y2-y4,…,y2-y Y ,y3-y4,y3-y5,…,y3-y Y ,……,y Y-1 -y Y .
[0101] In one example, calculating the distribution parameter value between two date data columns in each date data column pair of each table in a table pair includes: for each date data column pair of each table in the table pair, based on the difference between two cells located in the same row between the two date data columns in the date data column pair, calculating the mean and variance of the difference between the two date data columns in the date data column pair, and calculating the sum of the mean and variance of the date data column pair as the distribution parameter value of the date data column. Specifically, taking any date data column pair as an example, the date data column pair includes two date data columns from the same table in a table pair, respectively, recorded as date data column x1 and date data column x2, in each row, the value of the cell of date data column x1 is subtracted from the value of the cell of date data column x2 to obtain a difference, thereby obtaining the difference of the date data column pair in each row, then calculating the mean and variance of the difference of the date data column pair in each row, and calculating the sum of the mean and variance as the distribution parameter value of the date data column pair.
[0102] Sub-step 4023, based on the distribution parameter values of the date data column pairs in the two tables of each table pair, obtain the similarity between the date data column pairs between the two tables in each table pair.
[0103] Specifically, taking the above table pair including table A and table B as an example, after obtaining the distribution parameter values of all the date data column pairs in table A and table B, the date data column pairs in table A and the date data column pairs in table B are arranged and combined, and the combinations include: x1-x2 and y1-y2, x1-x2 and y1-y3, ..., x X-1 -x X With y Y-1 -y Y , and then calculate the similarity between two date data column pairs in various combinations. For example, two date data column pairs from Table A and Table B are selected, and the similarity between the two date data column pairs is calculated based on the distribution parameter values of the two date data column pairs.
[0104] In one example, the similarity calculation formula between two date data column pairs from two tables in the same table pair is:
[0105]
[0106] Where a and b represent the distribution parameter values of the date data columns of the two target tables respectively.
[0107] Step 403 includes the following sub-steps:
[0108] Sub-step 4031, for a data column pair consisting of two first-type data columns from two tables respectively derived from each table pair, at least one data column pair is selected as a matching column pair from the first-type data column pairs whose similarity is greater than a preset similarity threshold according to the similarity from large to small, wherein the first type is a date type.
[0109] Specifically, for a data column pair consisting of two date data column pairs of date type respectively derived from two tables of each table pair, the similarity between the two date data column pairs in each data column pair is obtained, and a greedy matching algorithm is used to perform similarity matching between the date data column pairs, and the data column pair with the greatest similarity is preferentially selected. If the similarity of the data column pairs is greater than a preset similarity threshold, the data column pair is used as a matching column pair; if the similarity of the data column pair is less than or equal to the preset similarity threshold, the similarity matching is stopped; after the data column pair with the greatest similarity is used as a matching column pair, the data column pair containing any one date data column pair in the data column pair with the greatest similarity is eliminated, and then the data column pair with the greatest similarity is selected from the remaining data column pairs, and it is determined whether the similarity of the data column pair is greater than the preset similarity threshold, and the above process is repeated until the similarity of the selected data column pair is less than or equal to the preset similarity threshold.
[0110] Taking Table 2 in the third embodiment as an example, A1 to A5 represent 5 date data column pairs in one table in the table pair, B1 to B5 represent 5 date data column pairs in the other table in the same table pair, the preset similarity threshold is 5, and the order of data column pairs selected by the greedy matching algorithm is: A4-B5 (6.8), A2-B3 (5.9), A3-B2 (5.8), A1-B4 (5.5).
[0111] Sub-step 4032: merge two data columns of the first type contained in each matching column pair and respectively originating from two tables of the same table pair.
[0112] Specifically, after the matching column pairs contained in each table pair are obtained, each matching queue includes two date data column pairs of date type. When two date data column pairs in any matching queue are merged, the corresponding date data columns in the two date data column pairs are merged. For example, the matching queue includes: date data column pair x1-x2 and date data column pair y1-y2. When the date data column pair x1-x2 is merged with the date data column pair y1-y2, the date data column x1 is merged with the date data column y1, and the date data column x2 is merged with the date data column y2.
[0113] The fifth embodiment of the present invention relates to a table merging method. Compared with the first embodiment, this embodiment provides a specific method for generating multiple table pairs to be merged based on multiple tables.
[0114] The specific process of the table merging method in this embodiment is as follows Fig.12 shown.
[0115] Step 501, for multiple tables to be merged, identify the type of each data column in each table. This is substantially the same as step 101 of the first embodiment and will not be described in detail here.
[0116] Step 502 includes the following sub-steps:
[0117] Sub-step 5021, selecting a table from multiple tables as a reference table.
[0118] Sub-step 5022: Each table except the reference table in the plurality of tables is combined with the reference table to form a table pair to be merged.
[0119] Sub-step 5023, calculating the similarity between the data columns of the same type between the two tables included in each table pair.
[0120] Please refer to Fig.13 The multiple tables to be merged include Table 1, Table 2 to Table I, where I is an integer greater than 2. One table is selected from the I tables as the base table. Table 1 is taken as the base table as an example below, and then Table 1 is combined with Table 2, Table 1 with Table 3, Table 1 with Table 4, ..., Table 1 with Table I to obtain I-1 table pairs.
[0121] For example, each table includes multiple text data columns, multiple numeric data columns, and multiple date data columns, and then the similarity between the data columns of the same type between two tables in each table pair is calculated.
[0122] Step 503, based on the similarity between the data columns of the same type between the two tables in each table pair, merge the data columns of the same type between the two tables in each table pair. This is substantially the same as step 103 of the first embodiment, except that when merging the data columns of the same type between the two tables in each table pair, the column names of the reference table can be used as the column names of the merged data columns. Since the other tables are merged with the reference table, the merged table pairs can be further merged by matching the column names after merging.
[0123] The sixth embodiment of the present invention relates to a processing chip, which is applied to electronic devices, such as mobile phones, tablet computers, laptop computers, desktop hosts, etc. The processing chip is used in the table merging method in any one of the first to fifth embodiments. Fig.14 , which is a schematic diagram of Table 4, the processing chip combines the table merging method in the first to fifth embodiments to Figure 2 Table 1 in Figure 7 Table 2 in Figure 8 Table 3 in and Fig.14 After merging Table 4 in Fig.15 Table 5 in Table 5 retains the six data columns of borrower, guarantor, interest, principal, legal status, and asset highlights; it should be noted that Fig.15 Only part of the combined Table 5 is given.
[0124] The seventh embodiment of the present invention relates to an electronic device, such as a mobile phone, a tablet computer, a laptop computer, a desktop host, etc. The electronic device includes at least one processor; and a memory connected to the at least one processor in communication; wherein the memory stores instructions executable by the at least one processor, and the instructions are executed by the at least one processor, so that the at least one processor can execute the method for acquiring push information of the target application page of any one of the first to fourth embodiments.
[0125] Among them, the memory and the processor are connected in a bus manner, and the bus may include any number of interconnected buses and bridges, and the bus connects various circuits of one or more processors and memories together. The bus can also connect various other circuits such as peripherals, voltage regulators, and power management circuits, which are well known in the art and are therefore not further described herein. The bus interface provides an interface between the bus and the transceiver. The transceiver can be one element or multiple elements, such as multiple receivers and transmitters, providing a unit for communicating with various other devices on a transmission medium. The data processed by the processor is transmitted on a wireless medium via an antenna, and further, the antenna also receives data and transmits the data to the processor.
[0126] The processor is responsible for managing the bus and general processing, and can also provide various functions, including timing, peripheral interfaces, voltage regulation, power management, and other control functions. Memory can be used to store data used by the processor when performing operations.
[0127] The eighth embodiment of the present invention relates to a computer-readable storage medium storing a computer program, which implements the above method embodiment when executed by a processor.
[0128] Preferred embodiments of the present invention have been described above in detail, but it should be understood that aspects of the embodiments can be modified, if necessary, to employ aspects, features and concepts of the various patents, applications and publications to provide further embodiments.
[0129] These and other changes can be made to the embodiments in light of the above detailed description.In general, in the claims, the terms used should not be considered limited to the specific embodiments disclosed in the specification and the claims, but should be construed to include all possible embodiments along with the full scope of equivalents to which these claims are entitled.
Claims
1. A table merging method, characterized in that: include: For a plurality of tables to be merged, identifying the type of each data column of each of the tables, the type of the data column including at least one of the following: text type, number type, date type; Generating a plurality of table pairs to be merged based on the plurality of tables, and calculating the similarity between the data columns of the same type between the two tables included in each of the table pairs; Based on the similarity between the data columns of the same type between the two tables in each table pair, merging the data columns of the same type between the two tables in each table pair; The step of calculating the similarity between the data columns of the same type between the two tables contained in each table pair includes: For each table in each table pair, the text data columns of each text type in the table are segmented to obtain a plurality of words, and the frequency of each word in each text data column is counted; based on the frequency of words in the text data columns of the two tables included in each table pair, the similarity between the text data columns of the two tables in each table pair is obtained; and / or, For each table in each table pair, respectively perform standardization processing on each digital data column in the table, and obtain the probability distribution of each digital data column; Based on the probability distribution of the digital data columns in the two tables included in each table pair, obtaining the similarity between the digital data columns between the two tables in each table pair; and / or, For each of the table pairs, if there are multiple date data columns of date type in both of the two tables in the table pair, the multiple date data columns in each of the tables in the table pair are combined to obtain at least one date data column pair, and a distribution parameter value between two of the date data columns in each of the date data column pairs in each of the tables in the table pair is calculated; Based on the distribution parameter values of the date data column pairs in the two tables of each table pair, the similarity between the date data column pairs between the two tables of each table pair is obtained.
2. The table merging method according to claim 1, characterized in that: Generating a plurality of table pairs to be merged based on the plurality of tables includes: Selecting one of the plurality of tables as a reference table; Each of the tables except the reference table among the plurality of tables is combined with the reference table to form the table pair to be merged.
3. The table merging method according to claim 1, characterized in that: Based on the similarity between the data columns of the same type between the two tables in each table pair, merging the data columns of the same type between the two tables in each table pair includes: For a data column pair consisting of two data columns of the first type respectively derived from the two tables of each table pair, selecting at least one data column pair as a matching column pair from the data column pairs of the first type whose similarity is greater than a preset similarity threshold according to the similarity from largest to smallest, wherein the first type is a text type or a date type; The two data columns of the first type contained in each matching column pair and respectively originating from two tables of the same table pair are merged.
4. The table merging method according to claim 1, characterized in that: Based on the similarity between the data columns of the same type between the two tables in each table pair, merging the data columns of the same type between the two tables in each table pair includes: For a data column pair consisting of two data columns of the second type in the two tables respectively derived from each table pair, at least one data column pair is selected from the data column pairs of the second type whose similarity is greater than a preset similarity threshold, with the maximum sum of the similarities of the selected data column pairs as an optimization target, and each selected data column pair is used as a matching column pair, wherein the second type is a digital type; The two data columns of the second type contained in each matching column pair and respectively originating from two tables of the same table pair are merged.
5. The table merging method according to claim 1, characterized in that: Based on the frequencies of words in the text data columns of the two tables included in each table pair, obtaining the similarity between the text data columns of the two tables in each table pair includes: For two target text data columns respectively derived from the two tables included in each table pair, the similarity between the target text data columns is obtained based on the number of identical words in the words whose frequencies meet the first preset condition in the two target text data columns.
6. The table merging method according to claim 1, characterized in that: Obtaining the similarity between the digital data columns between the two tables in each table pair based on the probability distribution of the digital data columns in the two tables included in each table pair includes: For two target digital data columns respectively derived from the two tables included in each table pair, a divergence value between the two target digital data columns is calculated as the similarity between the two target digital data columns based on the probability distribution of the two target digital data columns.
7. The table merging method according to claim 1, characterized in that: For each table in each table pair, standardizing each digital data column in the table and obtaining a probability distribution of each digital data column includes: For each of the tables in each of the table pairs, the digital data columns in the table that meet the second preset condition are removed, and the remaining digital data columns in the table after the digital data columns that meet the second preset condition are removed are standardized, and the probability distribution of each digital data column is obtained.
8. The table merging method according to claim 1, characterized in that: Calculating the distribution parameter value between two of the date data columns in each of the date data column pairs of each of the tables in the table pair comprises: For each date data column pair of each of the tables in the table pair, based on the difference between two cells located in the same row between the two date data columns in the date data column pair, the mean and variance of the difference between the two date data columns in the date data column pair are calculated, and the sum of the mean and variance of the date data column pair is calculated as the distribution parameter value of the date data column.
9. The table merging method according to claim 8, characterized in that: The calculation formula for the similarity between the two pairs of date data columns respectively derived from the two tables in the table pair is: Wherein, a and b respectively represent the distribution parameter values of the two pairs of date data columns from the two tables in the table pair.
10. A processing chip, characterized in that: Used to execute the table merging method described in any one of claims 1 to 9.
11. An electronic device, characterized in that: include: at least one processing chip; as well as, A memory in communication with the at least one processing chip; wherein, The memory stores instructions that can be executed by the at least one processing chip, and the instructions are executed by the at least one processing chip to enable the at least one processing chip to perform the table merging method according to any one of claims 1 to 9.
Citation Information
Patent Citations
Table integration method and device
CN110427604A
Method and device for identifying table structure and electronic equipment
CN112528703A
Method and device for identifying table direction, and electronic equipment
CN112560545A
Table merging with row data reduction
US20120173226A1
Information provision system, method, and program
WO2020152804A1
