Data table analysis method and device, equipment, storage medium and program product

Abstract

The invention relates to the technical field of computers, a data table analysis method and device, equipment, a storage medium and a program product. The method comprises the steps that a first data table and a second data table as well as same index fields and same dimension fields in the first data table and the second data table are obtained; based on the same index field and the same dimension field, data extraction is carried out in the first data table and the second data table, first data and second data are obtained respectively, and a third data table is formed; based on the first distribution difference corresponding to the index data under the index field in the third data table, determining difference dimension data in the third data table; determining a second distribution difference corresponding to the index data of the corresponding index field according to the combination of the different dimension data; and determining a reason causing the difference between the first data table and the second data table based on the size of the second distribution difference. According to the method, the consistency problem of the heterogeneous data can be verified under the condition that a primary key is not needed.

Description

Technical Field

[0001] The present disclosure relates to the field of computer technology, and in particular to a data table analysis method, device, equipment, storage medium and program product. Background Art

[0002] Common data processing procedures include extraction, transformation, and loading (ETL). In the ETL process, the data consistency judgment of two data tables is a basic issue. For example, the ETL processing logic may be optimized, and a data table can be obtained before and after the optimization. At this time, it is necessary to analyze the consistency of the two data tables to determine the cause of the difference. Summary of the invention

[0003] In view of this, the present disclosure provides a data table analysis method, device, equipment, storage medium and program product to solve the problem of data table analysis.

[0004] In a first aspect, the present disclosure provides a method for analyzing a data table, the method comprising:

[0005] Obtaining a first data table and a second data table, and the same indicator fields and the same dimension fields in the first data table and the second data table, wherein each data record in the data table includes indicator data under the indicator field and dimension data under the dimension field;

[0006] Based on the same indicator field and the same dimension field, data is extracted from the first data table and the second data table to obtain first data and second data respectively, thereby forming a third data table;

[0007] Determine the difference dimension data in the third data table based on the first distribution difference corresponding to the indicator data under the indicator field in the third data table;

[0008] For each combination of the difference dimension data, determining a second distribution difference corresponding to the indicator data of the corresponding indicator field;

[0009] Based on the size of the second distribution difference, a reason causing the difference between the first data table and the second data table is determined.

[0010] In a second aspect, the present disclosure provides a data table analysis device, the device comprising:

[0011] A data acquisition module, used to acquire the first data table and the second data table, and the same indicator field and the same dimension field in the first data table and the second data table, wherein each data record in the data table includes indicator data under the indicator field and dimension data under the dimension field;

[0012] A data extraction module, used for extracting data from the first data table and the second data table based on the same indicator field and the same dimension field, obtaining first data and second data respectively, and forming a third data table;

[0013] A first distribution difference determination module, configured to determine difference dimension data in the third data table based on a first distribution difference corresponding to the indicator data under the indicator field in the third data table;

[0014] A second distribution difference determination module, used to determine, for each combination of the difference dimension data, a second distribution difference corresponding to the indicator data of the corresponding indicator field;

[0015] The difference reason determination module is used to determine the reason causing the difference between the first data table and the second data table based on the size of the second distribution difference.

[0016] In a third aspect, the present disclosure provides an electronic device comprising: a memory and a processor, the memory and the processor being communicatively connected to each other, the memory storing computer instructions, and the processor executing the data table analysis method of the first aspect or any corresponding embodiment thereof by executing the computer instructions.

[0017] In a fourth aspect, the present disclosure provides a computer-readable storage medium having computer instructions stored thereon, the computer instructions being used to enable a computer to execute the data table analysis method of the first aspect or any corresponding embodiment thereof.

[0018] In a fifth aspect, the present disclosure provides a computer program product, including computer instructions, which are used to enable a computer to execute the data table analysis method of the above-mentioned first aspect or any corresponding embodiment.

[0019] The data table analysis method provided by the embodiment of the present disclosure obtains the first data table and the second data table to be compared, and the same indicator field and the same dimension field in the two data tables; based on the same indicator field and the same dimension field, data is extracted in the two data tables respectively to obtain the first data and the second data, and form a third data table. Then, the difference dimension data in the third data table is determined for the first distribution difference corresponding to the indicator data under the indicator field in the third data table; the second difference corresponding to the indicator data of the corresponding indicator field is determined for each combination of the difference dimension data; and the reason for the difference between the first data table and the second data table is determined based on the size of the second distribution difference. In the analysis method, by extracting the data under the same indicator field and the same dimension field in the two data tables to be compared, the third data table is formed to reduce the amount of data processing and improve the efficiency of subsequent data analysis. Further, the first distribution difference corresponding to the indicator data under the indicator field in the third data table is used to determine the difference dimension data, that is, the distribution difference of the indicator data is used for data screening, and the difference dimension data and its corresponding indicator data are retained for subsequent further analysis. Furthermore, through various combinations obtained from each difference dimension data, the second distribution difference corresponding to the indicator data of the phase shift indicator field is determined respectively. Due to the determination of the difference dimension data combination, it can be guaranteed that further analysis is performed on all difference combinations, which can improve the accuracy of the reasons for the difference between the first data table and the second data packet. This method can verify the consistency problem of heterogeneous data without the need for a primary key, and has a wide range of applications. BRIEF DESCRIPTION OF THE DRAWINGS

[0020] In order to more clearly illustrate the specific embodiments of the present disclosure or the technical solutions in the related technologies, the drawings required for use in the specific embodiments or the related technical descriptions will be briefly introduced below. Obviously, the drawings described below are some embodiments of the present disclosure. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative work.

[0021] Figure 1 is a flowchart of a method for analyzing a data table according to an embodiment of the present disclosure;

[0022] Figure 2 is a flowchart of another data table analysis method according to an embodiment of the present disclosure;

[0023] Figure 3 is a flowchart of another data table analysis method according to an embodiment of the present disclosure;

[0024] Figure 4 is a structural block diagram of a data table analysis device according to an embodiment of the present disclosure;

[0025] Figure 5 It is a schematic diagram of the hardware structure of the electronic device according to an embodiment of the present disclosure. DETAILED DESCRIPTION

[0026] In order to make the purpose, technical solution and advantages of the embodiments of the present disclosure clearer, the technical solution in the embodiments of the present disclosure will be clearly and completely described below in conjunction with the drawings in the embodiments of the present disclosure. Obviously, the described embodiments are part of the embodiments of the present disclosure, rather than all the embodiments. Based on the embodiments in the present disclosure, all other embodiments obtained by those skilled in the art without creative work are within the scope of protection of the present disclosure.

[0027] It is understandable that before using the technical solutions disclosed in the embodiments of the present disclosure, the types, scope of use, usage scenarios, etc. of the personal information involved in the present disclosure should be informed to the user and the user's authorization should be obtained in an appropriate manner in accordance with relevant laws and regulations.

[0028] For example, in response to receiving an active request from a user, a prompt message is sent to the user to clearly prompt the user that the operation requested to be performed will require obtaining and using the user's personal information. Thus, the user can autonomously choose whether to provide personal information to software or hardware such as an electronic device, application, server, or storage medium that performs the operation of the technical solution of the present disclosure according to the prompt message.

[0029] As an optional but non-limiting implementation, in response to receiving an active request from the user, the prompt information may be sent to the user in the form of a pop-up window, in which the prompt information may be presented in text form. In addition, the pop-up window may also carry a selection control for the user to choose "agree" or "disagree" to provide personal information to the electronic device.

[0030] It is understandable that the above notification and the process of obtaining user authorization are merely illustrative and do not constitute a limitation on the implementation of the present disclosure. Other methods that meet the relevant laws and regulations may also be applied to the implementation of the present disclosure.

[0031] It is understandable that the data involved in this technical solution (including but not limited to the data itself, the acquisition or use of the data) shall comply with the requirements of relevant laws, regulations and relevant provisions.

[0032] In the related art, for data tables T1 and T2 that need to be compared, full text comparison is generally performed by using the same primary key or constructing a primary key. The primary key comparison method is applicable when the data tables have the same structure and have a primary key or can construct a primary key. If the two data tables are heterogeneous, have no primary key, have completely different data rows, and have a series of indicators that need to be compared, the above primary key comparison method is difficult to apply.

[0033] In addition, the above-mentioned method of comparing primary keys is prone to false positives. This is because when comparing the full text of the same primary key data, for business-specific field types, such as double, the truncation of precision will cause a large number of data differences, leading to unnecessary troubles.

[0034] Based on this, the data table analysis method provided by the embodiment of the present disclosure realizes the analysis of the data table by means of data distribution difference, and can be used to solve the data analysis problem when the data table T1 and the data table T2 are heterogeneous. Among them, data heterogeneity means that the structures of the two tables are not exactly the same, the primary key cannot be constructed, there are multiple indicators that need to be compared, and the number of rows of data is completely different.

[0035] According to an embodiment of the present disclosure, an embodiment of a method for analyzing a data table is provided. It should be noted that the steps shown in the flowchart of the accompanying drawings can be executed in a computer system such as a set of computer executable instructions, and although a logical order is shown in the flowchart, in some cases, the steps shown or described can be executed in an order different from that shown here.

[0036] In this embodiment, a data table analysis method is provided, which can be used in a server. Figure 1 is a flow chart of a method for analyzing a data table according to an embodiment of the present disclosure, such as Figure 1 As shown, the process includes the following steps:

[0037] Step S101, obtaining a first data table and a second data table, and identical indicator fields and identical dimension fields in the first data table and the second data table.

[0038] Each data record in the data table includes indicator data under the indicator field and dimension data under the dimension field.

[0039] The data table is used to record data, and each data record includes indicator data under the indicator field and dimension data under the dimension field. The form of the data table can be shown in Table 1:

[0040] Table 1 Data sheet

[0041] Dimension Field 1 Dimension Field 2 Indicator field 1 Indicator field 2 Data Record 1 Dimensional Data11 Dimensional data 12 Indicator data 11 Indicator data 12 Data Record 2 Dimensional data 21 Dimensional data 22 Indicator data 21 Indicator data 22 Data Record 3 Dimensional data 31 Dimensional data 32 Indicator data 31 Indicator data 32

[0042] It should be noted that Table 1 only shows that each data record includes 2 dimension fields and 2 indicator fields, but this does not limit the protection scope of the present disclosure. The number of dimension fields and indicator fields is determined according to actual business needs and is not limited here.

[0043] For example, different detection methods are used to detect the usage status of the same server, where data table T1 corresponds to detection method 1, and a data record includes: the memory usage of server 1 at time point t1 is a1, and the number of running processes is a2; data table T2 corresponds to detection method 2, and a data record includes: the memory usage of server 1 at time point t1 is b1, and the number of running processes is b2. Then, the server and time point can be considered as dimension fields in the data table, and server 1 and time point t1 are corresponding dimension data; memory usage and number of running processes can be considered as indicator fields in the data table, memory usage a1 and number of running processes a2 are indicator data corresponding to the indicator field in data table T1, and memory usage b1 and number of running processes b2 are indicator data corresponding to the indicator field in data table T2.

[0044] Furthermore, for a data table, an indicator field may be a field corresponding to data having physical meaning, and a dimension field may be a dimension used to represent data division. It should be noted that the indicator field and the dimension field are determined according to actual needs and are not limited here.

[0045] The first data table and the second data table are two data tables to be compared, which may be from the same data source and obtained after different ETL processing; or they may be any two data tables to be compared, etc. Here, there is no limitation on the sources of the first data table and the second data table.

[0046] The same indicator field refers to the field with the same indicator field in the two data tables, which can be obtained by comparing the indicator fields in the two data tables. Similarly, the same dimension field refers to the field with the same dimension field in the two data tables, which can also be obtained by comparing the dimension fields in the two data tables.

[0047] For the first data table and the second data table, in addition to the same indicator fields and the same dimension fields, they may also include the corresponding remaining indicator fields and the remaining dimension fields. For example, data table T1: total number of rows M1, dimension columns A_1, A_2, ..., A_{k1}, B_1, B_2, ..., B_{k2}; indicator columns C_1, C_2, ..., C_{k3}, D_1, D_2, ..., D_{k4}; data table T1: total number of rows M2, dimension columns A_1, A_2, ..., A_{k1}, E_1, E_2, ..., E_{k5}; indicator columns C_1, C_2, ..., C_{k3}, F_1, F_2, ..., F_{k6}. The same dimension fields in the first data table and the second data table include A_1, A_2, ..., A_{k1}, and the same indicator fields include C_1, C_2, ..., C_{k3}. B_1, B_2, ..., B_{k2} and D_1, D_2, ..., D_{k4} are unique dimension fields and indicator fields in the first data table; E_1, E_2, ..., E_{k5} and F_1, F_2, ..., F_{k6} are unique dimension fields and indicator fields in the second data table.

[0048] Step S102: extract data from the first data table and the second data table based on the same indicator field and the same dimension field, obtain the first data and the second data respectively, and form a third data table.

[0049] After obtaining the same indicator field and the same dimension field, field extraction is performed in the first data table and the second data table by using field matching, and indicator data corresponding to all the same indicator fields and dimension data corresponding to all the same dimension fields can be extracted from the first data table and the second data table, respectively, to obtain first data and second data, respectively. Based on the extracted first data and second data, they are stored in the form of a data table to obtain a third data table.

[0050] In this solution, the data distribution differences are analyzed based on the same indicator field and the same dimension field to determine the reasons for the differences in the data table.

[0051] Step S103: determining difference dimension data in the third data table based on the first distribution difference corresponding to the indicator data under the indicator field in the third data table.

[0052] The third data table includes the indicator fields and their corresponding indicator data, dimension fields and their corresponding dimension data from the first data table; and the indicator fields and their corresponding indicator data, dimension fields and their corresponding dimension data from the second data table.

[0053] For the indicator field in the third data table, the first distribution difference between the indicator data from the first data table and the indicator data from the second data table can be determined for each data record. The first distribution difference can be calculated by Euclidean distance, cosine distance, etc. Of course, other methods can also be used to calculate the first distribution difference, and the specific calculation method is not limited here.

[0054] After obtaining the first distribution difference corresponding to each data record in the third data table, it can be determined whether there is a difference between the data record in the two data tables based on the size of the first distribution difference. Accordingly, the difference dimension data in the third data table can be determined.

[0055] Step S104: determining, for each combination of difference dimension data, a second distribution difference corresponding to the indicator data of the corresponding indicator field.

[0056] Since some of the difference dimension data come from the first data table and some come from the second data table, based on this, the difference dimension data combination is obtained by determining the combination of difference dimension data from different data tables.

[0057] For the difference dimension data combination, the corresponding indicator data from different data tables is obtained, and the second distribution difference corresponding to the indicator data of the indicator field can be further determined. The second distribution difference can be calculated in the same or similar manner as the first distribution difference, and of course, it can also be calculated in other ways, which are not limited here.

[0058] Step S105: determining the reason for the difference between the first data table and the second data table based on the size of the second distribution difference.

[0059] The larger the second distribution difference is, the larger the difference in the indicator data in the two data tables is. Based on this, combined with the dimension data of the dimension indicator corresponding to the second distribution difference, the cause of the difference between the first data table and the second data table can be located. For example, it can be determined which dimension data differences are caused, etc.

[0060] The data table analysis method provided in this embodiment reduces the amount of data processing and improves the efficiency of subsequent data analysis by extracting the data under the same indicator field and the same dimension field in the two data tables to be compared to form a third data table. Further, the difference dimension data is determined by using the first distribution difference corresponding to the indicator data under the indicator field in the third data table, that is, the distribution difference of the indicator data is used for data screening, and the difference dimension data and its corresponding indicator data are retained for subsequent further analysis. Further, through various combinations obtained from each difference dimension data, the second distribution difference corresponding to the indicator data of the phase shift indicator field is determined respectively. Due to the determination of the difference dimension data combination, it can be guaranteed that further analysis is performed on all difference combinations, which can improve the accuracy of the reasons for the difference between the first data table and the second data packet. This method can verify the consistency problem of heterogeneous data without the need for a primary key, and has a wide range of applications.

[0061] In this embodiment, a data table analysis method is provided, which can be used in a server. Figure 2 is a flow chart of a method for analyzing a data table according to an embodiment of the present disclosure, such as Figure 2 As shown, the process includes the following steps:

[0062] Step S201, obtaining a first data table and a second data table, and identical indicator fields and identical dimension fields in the first data table and the second data table.

[0063] Each data record in the data table includes indicator data under the indicator field and dimension data under the dimension field.

[0064] For details, please see Figure 1 Step S101 of the illustrated embodiment will not be described in detail here.

[0065] Step S202: extract data from the first data table and the second data table based on the same indicator field and the same dimension field, obtain the first data and the second data respectively, and form a third data table.

[0066] Specifically, the above step S202 includes:

[0067] Step S2021, obtaining the preset indicator field in the same indicator field, and the preset dimension field in the same dimension field.

[0068] Among them, the preset indicator fields and preset dimension fields are determined based on business needs.

[0069] The analysis of the data table can be an analysis of all the same indicator fields and all the same dimension fields; or it can be a screening in the same indicator fields and the same dimension fields based on business needs, that is, the subsequent distribution differences are determined for the preset indicator fields in the same indicator fields and the preset dimension fields in the same dimension fields.

[0070] For example, the same dimension fields in the first data table and the second data table include A_1, A_2, ..., A_{k1}, and the same index fields include C_1, C_2, ..., C_{k3}. The preset dimension fields are: A_1, A_2; the preset index fields are C_1, C_2.

[0071] The determination of the preset dimension fields and the preset indicator fields can be determined directly according to business needs, or can be obtained by excluding the same dimension fields and the same indicator fields according to business needs, etc. There is no limitation on the determination method of the preset dimension fields and the preset indicator fields, which can be set according to actual needs.

[0072] Step S2022: Based on the preset indicator field and the preset dimension field, data is extracted from the first data table and the second data table respectively to obtain the first data and the second data.

[0073] After determining the preset indicator fields and the preset dimension fields, corresponding data extraction is performed from the first data table, including extraction of corresponding indicator data and dimension data, to obtain first data in the first data table; corresponding data extraction is performed from the second data table, including extraction of corresponding indicator data and dimension data, to obtain second data in the second data table.

[0074] The data extraction method of the first data table is similar to that of the second data table, and is described here by taking the first data table as an example. In the first data table, the same dimension fields and the same indicator fields can be marked first, and then the preset dimension fields can be matched in the same dimension fields to obtain the dimension data of the preset dimension fields in the first data table. Similarly, the preset indicator fields can be matched in the same indicator fields to obtain the indicator data of the preset indicator fields in the first data.

[0075] Step S2023, forming a third data table based on the preset indicator field and the preset dimension field, and the corresponding first data and second data.

[0076] In the third data table, the first data table and the second data table are distinguished based on the preset indicator field. For example, for the preset indicator field C1, the preset indicator field corresponding to the first data table can be expressed as C1_1, and the preset indicator field corresponding to the second data table can be expressed as C1_2; similarly, for the preset dimension field A1, the preset dimension field corresponding to the first data table can be expressed as A1_1, and the preset indicator field corresponding to the second data table can be expressed as A1_2.

[0077] After the preset indicator field and the preset dimension field are determined in the third data table, the corresponding first data and second data can be filled to form the third data table. Alternatively, the first data and the second data can be filled, and then the corresponding preset indicator field and preset dimension field can be filled to form the third data table. Alternatively, the third data table can also be obtained by other methods, which are not limited here, and can be set according to actual needs.

[0078] In some optional implementations, the above step S2023 includes:

[0079] Step a1: if there are identical data records in the first data and the second data, the identical data records are merged to obtain merged data.

[0080] Step a2, forming a third data table based on the preset indicator field, the preset dimension field, and the merged data.

[0081] Since the first data and the second data come from different data tables, they may also have the same data records. For example, each data record in the first data and the second data may be matched, and if they can be matched, it means that the same data records exist.

[0082] In the case where identical data records exist, data merging is performed on the identical data records, that is, only one record is retained, thereby obtaining merged data.

[0083] After the data is merged, the preset indicator field and the preset dimension field are combined to form a third data table. For example, there is a data record 1 in the first data: preset dimension field A1_1, dimension data is a1, preset dimension field A2_1, dimension data is a2, preset indicator field C1_1, indicator data is c1, preset indicator field C2_1, indicator data is c2.

[0084] There is a data record 2 in the second data: preset dimension field A1_2, dimension data is a1, preset dimension field A2_2, dimension data is a2, preset indicator field C1_2, indicator data is c1, preset indicator field C2_2, indicator data is c2.

[0085] By comparison, it can be seen that data record 1 is the same as data record 2, so only one data record may be retained, that is, data record 1 in the first data is deleted, or data record 2 in the second data is deleted.

[0086] Data merging is performed for the same data records, thus reducing the amount of data processing.

[0087] Step S203: determining difference dimension data in the third data table based on the first distribution difference corresponding to the indicator data under the indicator field in the third data table.

[0088] Specifically, the above step S203 includes:

[0089] Step S2031, for the dimension data under each preset dimension field in the third data table, first indicator data corresponding to the dimension data and belonging to the first data table and second indicator data belonging to the second data table are determined respectively.

[0090] There may be multiple preset dimension fields in the third data table. Therefore, for each preset dimension field, the first indicator data belonging to the first data table and the second indicator data belonging to the second data table are extracted from the data records where the dimension data under each preset dimension field is located from the third data table.

[0091] For example, there are 3 data records in the third data table, each of which includes dimension data corresponding to each preset dimension field and indicator data corresponding to each preset indicator field of the first data table, and dimension data corresponding to each preset dimension field and indicator data corresponding to each preset indicator field of the second data table. If there is no corresponding dimension data or indicator data, 0 can be used to represent it.

[0092] Then, dimensional data may be extracted for each preset dimensional field, and then combined with the data table to which the dimensional data belongs, first indicator data belonging to the first data table and second indicator data belonging to the second data table may be determined.

[0093] In some optional implementations, the above step S2031 includes:

[0094] Step b1, if there are at least two identical dimension data under the same preset dimension field in the third data table, extract the third indicator data corresponding to the at least two identical dimension data and belonging to the first data table and the fourth indicator data belonging to the second data table.

[0095] Step b2, summing up all third indicator data under the same preset indicator dimension to obtain the first indicator data under the preset indicator dimension in the first data table.

[0096] Step b3, summing up all fourth indicator data under the same preset indicator dimension to obtain second indicator data under the preset indicator dimension in the second data table.

[0097] Since the third data table is obtained by the first data in the first data table and the second data in the second data table, there may be at least two identical dimension data under the same preset dimension field of the third data table. For example, under the preset dimension field A1_1, there are two identical dimension data a2, and accordingly, there are two data records for the dimension data a2, and the two data records include two indicator data under the same preset indicator dimension. Therefore, the two indicator data are summed to obtain one indicator data under the same preset indicator dimension.

[0098] That is, if there are at least two identical dimension data under the same preset dimension field, then the third indicator data belonging to the first data table and the fourth indicator data belonging to the second data table are extracted from the data records to which the at least two identical dimension data belong.

[0099] Furthermore, the data of the preset indicator dimension are summed for the third indicator data and the fourth indicator data respectively to obtain the first indicator data and the second indicator data under the preset indicator dimension.

[0100] In the case where there are at least two identical dimension data under the same preset dimension field, all third indicator data and fourth indicator data under the same preset indicator dimension are summed up to obtain first indicator data and second indicator data. The summation process can reduce the amount of data processing while ensuring data accuracy.

[0101] Step S2032: determine a first distribution difference between the first indicator data and the second indicator data.

[0102] The first indicator data comes from the first data table, and the second indicator data comes from the second data table. The distribution difference is calculated for the indicator data from different data tables to obtain the first distribution difference. As described above, the calculation of the distribution difference can be obtained by Euclidean distance, cosine distance or other methods, which are not limited here.

[0103] Step S2033: Determine the difference dimension data under each preset dimension field based on the size of the first distribution difference.

[0104] After obtaining the first distribution difference corresponding to the dimensional data under each preset dimensional field, the dimensional data corresponding to the first distribution difference not equal to 0 can be determined as the difference dimensional data; or the first distribution difference can be compared with the preset difference threshold to obtain the difference dimensional data.

[0105] In some optional implementations, the above step S2033 includes:

[0106] Step c1, comparing the first distribution difference with a preset difference threshold.

[0107] Step c2: Determine the dimension data corresponding to the first distribution difference being greater than a preset difference threshold as difference dimension data.

[0108] The first distribution difference is compared with the preset difference threshold, and if the first distribution difference is greater than the preset difference threshold, it means that the dimension data corresponding to the first distribution difference is difference dimension data. The difference dimension data can be understood as the difference in indicator data caused by the difference in dimension data, that is, the first distribution difference is greater than the preset difference threshold.

[0109] After the first distribution difference is obtained, it is compared with a preset difference threshold with adjustable precision, so that the difference dimension data can be accurately screened out.

[0110] In some optional implementations, the preset difference threshold is related to the data accuracy of the data table. For example, if the data accuracy of the data table is 0.01, the preset difference threshold can be set to 0.01. Of course, it can also be set to other values, and there is no limitation on its specific value here, and it can be set according to actual needs.

[0111] By setting a preset difference threshold related to the data accuracy of the data table, false alarms can be reduced, greatly improving the efficiency of data difference locating.

[0112] Step S204: determining the second distribution difference corresponding to the indicator data of the corresponding indicator field for each combination of the difference dimension data.

[0113] Specifically, the above step S204 includes:

[0114] Step S2041, determining a combination of the difference dimension data based on the data table to which the difference dimension data belongs.

[0115] After the processing of step S203, the difference dimension data is obtained. The difference dimension data corresponds to the data table, and then, the difference dimension data is combined according to the data table to which the difference dimension data belongs to obtain a combination of the difference dimension data.

[0116] For example, the difference dimension data belonging to the first data table include a1, a2 and a3, and the difference dimension data belonging to the second data table include b1 and b2. The difference dimension data are combined according to the different data tables they belong to, that is, including but not limited to (a1, b1)(a1, b2)(a2, b1)(a2, b2)(a1, a2, b1)(a1, a2, a3, b1) and so on.

[0117] Step S2042: query the third data table based on the combination of the difference dimension data to determine the third indicator data and the fourth indicator data corresponding to the combination of the difference dimension data.

[0118] Among them, the third indicator data belongs to the first data table, and the fourth indicator data belongs to the second data table.

[0119] After obtaining the combination of difference dimension data according to the above method, the third data table is queried according to the combined dimension data to obtain the third indicator data belonging to the first data table and the fourth indicator data belonging to the second data table corresponding to each combination of difference dimension data.

[0120] Step S2043, determining a second distribution difference between the third indicator data and the fourth indicator data.

[0121] For the third indicator data and the fourth indicator data from different data tables, the distribution difference is calculated to obtain the second distribution difference. In other words, for each combination of difference dimension data, a second distribution difference can be obtained.

[0122] Step S205: Based on the size of the second distribution difference, determine the reason for the difference between the first data table and the second data table. Figure 1 Step S105 of the illustrated embodiment will not be described in detail here.

[0123] The data table analysis method provided in this embodiment determines the corresponding first indicator data and second indicator data from the first data table and the second data table for the dimension data under each preset dimension field, thereby calculating the first distribution difference between the two indicator data, which can ensure the accuracy of the first distribution difference. For the data table to which the difference dimension data belongs, the difference dimension data is combined and determined, which ensures the completeness and accuracy of the determined combination, thereby ensuring the accuracy of the second distribution difference calculated on this basis.

[0124] In this embodiment, a data table analysis method is provided, which can be used in a server. Figure 3 is a flow chart of a method for analyzing a data table according to an embodiment of the present disclosure, such as Figure 3 As shown, the process includes the following steps:

[0125] Step S301, obtaining a first data table and a second data table, and identical indicator fields and identical dimension fields in the first data table and the second data table.

[0126] Each data record in the data table includes indicator data under the indicator field and dimension data under the dimension field. Figure 1 Step S101 of the illustrated embodiment will not be described in detail here.

[0127] Step S302: Based on the same indicator field and the same dimension field, data is extracted from the first data table and the second data table to obtain the first data and the second data, respectively, to form a third data table. Figure 2 Step S202 of the illustrated embodiment will not be described in detail here.

[0128] Step S303: Determine the difference dimension data in the third data table based on the first distribution difference corresponding to the indicator data under the indicator field in the third data table. Figure 2 Step S203 of the illustrated embodiment will not be described in detail here.

[0129] Step S304: for each combination of difference dimension data, determine the second distribution difference corresponding to the indicator data of the corresponding indicator field. Figure 1 Step S204 of the illustrated embodiment will not be described in detail here.

[0130] Step S305: determining the reason for the difference between the first data table and the second data table based on the size of the second distribution difference.

[0131] Specifically, the above step S305 includes:

[0132] Step S3051, sorting the second distribution differences.

[0133] After determining the second distribution differences corresponding to the combinations of the difference dimension data, the second distribution differences are sorted, either in a descending order or in a descending order, and there is no limitation on the sorting direction.

[0134] Step S3052: determine the dimensional data causing the difference between the first data table and the second data table according to the sorting result.

[0135] Among them, the reasons for the differences include the existence of different dimensional data.

[0136] The larger the value of the second distribution difference is, the greater the difference in the indicator data in the two data tables is, and accordingly, the greater the proportion of the indicator data difference caused by the combination of the corresponding difference dimension data is. For example, the difference dimension data with the largest second distribution difference can be determined as the reason for the difference between the first data table and the second data table.

[0137] The data table analysis method provided in this embodiment uses the preset indicator fields and preset dimension fields determined by business needs to further filter the same indicator fields and the same dimension fields, thereby further reducing the amount of data. Since the preset indicator fields and preset dimension fields are related to business needs, even if data filtering is performed, it will not affect the accuracy of data analysis. Based on the sorting results of the second distribution difference, it is determined that the cause of the difference between the two data tables includes dimensional data, thereby achieving accurate positioning of the cause of the difference between the data tables.

[0138] As one or more specific application embodiments of the present disclosure, after processing the data from the same data source using different ETL, two data tables are obtained, as shown in Table 2 and Table 3, which respectively represent the first data table T1 and the second data table T2. Specifically,

[0139] Table 2 First data table T1

[0140] A1_1 A1_2 A1_3 B_1 C1_1 C1_2 1a 1b 1c 6 1 2 2a 2b 2c 7 1 3 2a 3b 3c 8 2 2

[0141] Table 3 Second data table T2

[0142] A2_1 A2_2 A2_3 E_1 C2_1 C2_2 1a 1b 1c u 1 2 2a 2b 2c f 1 3 3a 3b 2c z 1 1 4a 3b 3c g 1 1

[0143] As can be seen from the above table, the same dimension fields in the first data table T1 and the second data table T2 include A1_1, A1_2, A1_3 and A2_1, A2_2, A2_3, B_1 is a unique dimension field in the first data table T1, and E_1 is a unique dimension field in the second data table T2. The same dimension fields in the first data table T1 and the second data table T2 include C1_1, C1_2 and C2_1, C2_2.

[0144] The first data table T1 and the second data table T2 are extracted based on the same dimension fields to obtain a construction table T3, as shown in Table 4:

[0145] Table 4 Construction table T3

[0146]

[0147] Then, taking each dimension field as an object, calculate the distribution difference of the indicator data corresponding to the dimension data under each dimension field. The data related to the dimension field A_1 is shown in Table 5:

[0148] Table 5 Data related to dimension field A_1

[0149]

[0150] As can be seen from Table 4, the dimension field A_1 includes two identical dimension data 2a. Then, the data records belonging to the dimension data 2a are merged to obtain the indicator data under the corresponding indicator field. That is, the indicator data corresponding to the indicator fields C1_1, C1_2, C2_1, and C2_2 shown in Table 5 are obtained. The Euclidean distance is calculated for the indicator data belonging to different data tables to obtain the corresponding distribution differences. As can be seen from the calculation results of Table 5, the difference dimension data includes 2a, 3a, and 4a.

[0151] The data related to dimension field A_2 is shown in Table 6:

[0152] Table 6 Data related to dimension field A_2

[0153] A_2 C1_1 C1_2 C2_1 C2_2 Err(T1, T2) 1b 1 2 1 2 0 2b 1 3 1 3 0 3b 2 2 2 2 0

[0154] As can be seen from Table 4, the dimension field A_2 includes three identical dimension data 3b. Then, the data records to which the dimension data 3b belongs are merged to obtain the indicator data under the corresponding indicator field. That is, the indicator data corresponding to the indicator fields C1_1, C1_2, C2_1, and C2_2 shown in Table 6 are obtained. The Euclidean distance is calculated for the indicator data belonging to different data tables to obtain the corresponding distribution differences. As can be seen from the calculation results of Table 6, there is no difference in dimension data.

[0155] The data related to dimension field A_3 is shown in Table 7:

[0156] Table 7 Data related to dimension field A_3

[0157]

[0158] As can be seen from Table 4, the dimension field A_3 includes two identical dimension data 2c. Then, the data records to which the dimension data 2c belongs are merged to obtain the indicator data under the corresponding indicator field. That is, the indicator data corresponding to the indicator fields C1_1, C1_2, C2_1, and C2_2 shown in Table 7 are obtained. The Euclidean distance is calculated for the indicator data belonging to different data tables to obtain the corresponding distribution differences. As can be seen from the calculation results of Table 7, the difference dimension data includes 2c and 3c.

[0159] From the analysis of Tables 5 to 7, it can be seen that the set of difference dimension data M = {{2a, 3a, 4a}, {2c, 3c}}. For the set of difference dimension data, the difference dimension data is combined. The second distribution difference is calculated for each combination. The second distribution differences corresponding to some combinations are shown in Table 8:

[0160] Table 8 Combination of difference dimension data

[0161]

[0162] After calculating the second distribution difference, the data can be sorted according to the size of the second distribution difference and the number of difference dimension data in each data record. For example, the second distribution difference is first compared with the preset difference threshold, and the second distribution difference greater than the preset difference threshold is screened out. On this basis, the remaining second distribution differences are sorted in order from large to small. If there are second distribution differences of the same size in the remaining second distribution differences, they are sorted in order from large to small according to the number of difference dimension data in the data record. Accordingly, the results shown in Table 9 can be obtained:

[0163] Table 9 Differences in the second distribution after sorting

[0164]

[0165] It can be seen from Table 9 that the main reason for the difference between the first data table and the second data table is that {2a, 3c} in the first data table T1 does not exist in the second data table T2.

[0166] This method verifies whether the data in two data tables are consistent by analyzing the differences in the distribution of indicator data on the same dimension of the two data tables, so that the cause of the difference can be quickly located.

[0167] In the present embodiment, a data table analysis device is also provided, which is used to implement the above-mentioned embodiments and preferred implementation modes, and the descriptions thereof will not be repeated. As used below, the term "module" may implement a combination of software and / or hardware of a predetermined function. Although the devices described in the following embodiments are preferably implemented in software, the implementation of hardware, or a combination of software and hardware, is also possible and contemplated.

[0168] This embodiment provides a data table analysis device, such as Figure 4 As shown, including:

[0169] The data acquisition module 401 is used to acquire the first data table and the second data table, and the same indicator fields and the same dimension fields in the first data table and the second data table. Each data record in the data table includes indicator data under the indicator field and dimension data under the dimension field.

[0170] The data extraction module 402 is used to extract data from the first data table and the second data table based on the same indicator field and the same dimension field, obtain the first data and the second data respectively, and form a third data table.

[0171] The first distribution difference determination module 403 is used to determine the difference dimension data in the third data table based on the first distribution difference corresponding to the indicator data under the indicator field in the third data table.

[0172] The second distribution difference determination module 404 is used to determine the second distribution difference corresponding to the indicator data of the corresponding indicator field for each combination of the difference dimension data.

[0173] The difference reason determination module 405 is used to determine the reason causing the difference between the first data table and the second data table based on the size of the second distribution difference.

[0174] In some optional implementations, the data extraction module 402 includes:

[0175] The field acquisition unit is used to acquire preset indicator fields in the same indicator field and preset dimension fields in the same dimension field. The preset indicator fields and preset dimension fields are determined based on business requirements.

[0176] The data extraction unit is used to extract data from the first data table and the second data table based on the preset indicator field and the preset dimension field to obtain the first data and the second data.

[0177] The data table generating unit is used to form a third data table based on the preset indicator field and the preset dimension field, and the corresponding first data and second data.

[0178] In some optional implementations, the data table generating unit includes:

[0179] The data merging subunit is used to merge the same data records if there are the same data records in the first data and the second data to obtain merged data.

[0180] The data table generation subunit is used to form a third data table based on the preset indicator field and the preset dimension field, and the merged data.

[0181] In some optional implementations, the first distribution difference determination module 403 includes:

[0182] The indicator data determination unit is used to determine, for the dimension data under each preset dimension field in the third data table, first indicator data corresponding to the dimension data and belonging to the first data table and second indicator data belonging to the second data table.

[0183] The first distribution difference determining unit is used to determine a first distribution difference between the first indicator data and the second indicator data.

[0184] The difference dimension data determining unit is used to determine the difference dimension data under each preset dimension field based on the size of the first distribution difference.

[0185] In some optional implementations, the indicator data determination unit includes:

[0186] The indicator data extraction subunit is used to extract the third indicator data corresponding to the at least two identical dimension data and belonging to the first data table and the fourth indicator data belonging to the second data table if there are at least two identical dimension data under the same preset dimension field in the third data table.

[0187] The first summing subunit is used to sum all third indicator data under the same preset indicator dimension to obtain the first indicator data under the preset indicator dimension in the first data table.

[0188] The second summing subunit is used to sum all fourth indicator data under the same preset indicator dimension to obtain second indicator data under the preset indicator dimension in the second data table.

[0189] In some optional implementations, the difference dimension data determining unit includes:

[0190] The comparison subunit is used to compare the first distribution difference with a preset difference threshold.

[0191] The difference dimension data determining subunit is used to determine the dimension data corresponding to the first distribution difference being greater than a preset difference threshold as the difference dimension data.

[0192] In some optional implementations, the preset difference threshold is related to the data accuracy of the data table.

[0193] In some optional implementations, the second distribution difference determination module 404 includes:

[0194] The combination determining unit is used to determine the combination of the difference dimension data based on the data table to which the difference dimension data belongs.

[0195] The query unit is used to query the third data table based on the combination of difference dimension data, determine the third indicator data and the fourth indicator data corresponding to the combination of difference dimension data, the third indicator data belongs to the first data table, and the fourth indicator data belongs to the second data table.

[0196] The second distribution difference determining unit is used to determine a second distribution difference between the third indicator data and the fourth indicator data.

[0197] In some optional implementations, the difference cause determination module 405 includes:

[0198] A sorting unit is used to sort the second distribution differences.

[0199] The cause determination unit is used to determine the dimensional data causing the difference between the first data table and the second data table according to the sorting result, and the cause of the difference includes the dimensional data of the difference.

[0200] The further functional description of each of the above modules and units is the same as that of the above corresponding embodiments and will not be repeated here.

[0201] The data table analysis device in this embodiment is presented in the form of a functional unit, where the unit refers to an ASIC (Application Specific Integrated Circuit) circuit, a processor and memory that executes one or more software or fixed programs, and / or other devices that can provide the above functions.

[0202] The present disclosure also provides an electronic device having the above Figure 4 The data table shown is an analysis device.

[0203] See also Figure 5 , Figure 5 is a schematic diagram of the structure of an electronic device provided by an optional embodiment of the present disclosure, such as Figure 5 As shown, the electronic device includes: one or more processors 10, a memory 20, and interfaces for connecting various components, including high-speed interfaces and low-speed interfaces. The various components are connected to each other using different buses for communication, and can be installed on a common mainboard or installed in other ways as needed. The processor can process instructions executed in the electronic device, including instructions stored in or on the memory to display graphical information of the GUI on an external input / output device (such as a display device coupled to the interface). In some optional embodiments, if necessary, multiple processors and / or multiple buses can be used together with multiple memories and multiple memories. Similarly, multiple electronic devices can be connected, and each device provides some necessary operations (for example, as a server array, a group of blade servers, or a multi-processor system). Figure 5 A processor 10 is taken as an example.

[0204] The processor 10 may be a central processing unit, a network processor or a combination thereof. The processor 10 may further include a hardware chip. The hardware chip may be a dedicated integrated circuit, a programmable logic device or a combination thereof. The programmable logic device may be a complex programmable logic device, a field programmable gate array, a general purpose array logic or any combination thereof.

[0205] The memory 20 stores instructions executable by at least one processor 10, so that at least one processor 10 executes the method shown in the above embodiment.

[0206] The memory 20 may include a program storage area and a data storage area, wherein the program storage area may store an operating system, an application required for at least one function; the data storage area may store data created according to the use of the electronic device, etc. In addition, the memory 20 may include a high-speed random access memory, and may also include a non-transient memory, such as at least one disk storage device, a flash memory device, or other non-transient solid-state storage device. In some optional embodiments, the memory 20 may optionally include a memory remotely arranged relative to the processor 10, and these remote memories may be connected to the electronic device via a network. Examples of the above-mentioned network include, but are not limited to, the Internet, an intranet, a local area network, a mobile communication network, and combinations thereof.

[0207] The memory 20 may include a volatile memory, such as a random access memory; the memory may also include a non-volatile memory, such as a flash memory, a hard disk or a solid state drive; the memory 20 may also include a combination of the above types of memory.

[0208] The electronic device further comprises a communication interface 30 for the electronic device to communicate with other devices or a communication network.

[0209] The embodiments of the present disclosure also provide a computer-readable storage medium. The above-mentioned method according to the embodiments of the present disclosure can be implemented in hardware, firmware, or can be implemented as a computer code that can be recorded in a storage medium, or can be implemented as a computer code that is originally stored in a remote storage medium or a non-temporary machine-readable storage medium and will be stored in a local storage medium and downloaded through a network, so that the method described herein can be stored in such software processing on a storage medium using a general-purpose computer, a dedicated processor, or programmable or dedicated hardware. Among them, the storage medium can be a magnetic disk, an optical disk, a read-only storage memory, a random access memory, a flash memory, a hard disk or a solid-state drive, etc.; further, the storage medium can also include a combination of the above-mentioned types of memory. It can be understood that a computer, a processor, a microprocessor controller, or programmable hardware includes a storage component that can store or receive software or computer code. When the software or computer code is accessed and executed by a computer, a processor, or hardware, the method shown in the above embodiment is implemented.

[0210] A part of the present disclosure may be applied as a computer program product, such as a computer program instruction, which, when executed by a computer, can call or provide the method and / or technical solution according to the present disclosure through the operation of the computer. Those skilled in the art should understand that the existence of computer program instructions in computer-readable media includes, but is not limited to, source files, executable files, installation package files, etc., and accordingly, the way in which computer program instructions are executed by a computer includes, but is not limited to: the computer directly executes the instruction, or the computer compiles the instruction and then executes the corresponding compiled program, or the computer reads and executes the instruction, or the computer reads and installs the instruction and then executes the corresponding installed program. Here, the computer-readable medium can be any available computer-readable storage medium or communication medium accessible to the computer.

[0211] Although the embodiments of the present disclosure have been described in conjunction with the accompanying drawings, those skilled in the art may make various modifications and variations without departing from the spirit and scope of the present disclosure, and such modifications and variations are all within the scope defined by the appended claims.

Claims

1. A method for analyzing a data table, characterized in that: The method comprises: Obtaining a first data table and a second data table, and the same indicator fields and the same dimension fields in the first data table and the second data table, wherein each data record in the data table includes indicator data under the indicator field and dimension data under the dimension field; Based on the same indicator field and the same dimension field, data is extracted from the first data table and the second data table to obtain first data and second data respectively, thereby forming a third data table; Determine the difference dimension data in the third data table based on the first distribution difference corresponding to the indicator data under the indicator field in the third data table; For each combination of the difference dimension data, determining a second distribution difference corresponding to the indicator data of the corresponding indicator field; Based on the size of the second distribution difference, a reason causing the difference between the first data table and the second data table is determined.

2. The method according to claim 1, characterized in that: The extracting data from the first data table and the second data table based on the same indicator field and the same dimension field to obtain first data and second data respectively to form a third data table includes: Acquire a preset indicator field in the same indicator field and a preset dimension field in the same dimension field, wherein the preset indicator field and the preset dimension field are determined based on business requirements; Based on the preset indicator field and the preset dimension field, respectively extract data from the first data table and the second data table to obtain the first data and the second data; The third data table is formed based on the preset indicator field and the preset dimension field, and the corresponding first data and the second data.

3. The method according to claim 2, characterized in that The forming of the third data table based on the preset indicator field and the preset dimension field, and the corresponding first data and the second data, includes: If the first data and the second data have the same data records, merging the same data records to obtain merged data; The third data table is formed based on the preset indicator field, the preset dimension field, and the merged data.

4. The method according to claim 1, characterized in that: The determining of difference dimension data in the third data table based on the first distribution difference corresponding to the indicator data under the indicator field in the third data table includes: For the dimension data under each of the preset dimension fields in the third data table, respectively determine first indicator data corresponding to the dimension data and belonging to the first data table and second indicator data belonging to the second data table; determining a first distribution difference between the first indicator data and the second indicator data; Based on the size of the first distribution difference, the difference dimension data under each of the preset dimension fields is determined.

5. The method according to claim 4, characterized in that The determining, for each dimension data under each preset dimension field in the third data table, first indicator data corresponding to the dimension data and belonging to the first data table and second indicator data belonging to the second data table respectively includes: If at least two identical dimension data exist under the same preset dimension field in the third data table, extracting the third indicator data corresponding to the at least two identical dimension data and belonging to the first data table and the fourth indicator data belonging to the second data table; Summing all the third indicator data under the same preset indicator dimension to obtain the first indicator data under the preset indicator dimension belonging to the first data table; All the fourth indicator data under the same preset indicator dimension are summed to obtain the second indicator data under the preset indicator dimension in the second data table.

6. The method according to claim 4, characterized in that The determining the difference dimension data under each of the preset dimension fields based on the size of the first distribution difference includes: comparing the first distribution difference with a preset difference threshold; The dimension data corresponding to the first distribution difference being greater than the preset difference threshold is determined as the difference dimension data.

7. The method according to claim 6, characterized in that The preset difference threshold is related to the data accuracy of the data table.

8. The method according to claim 1, characterized in that The determining, for each combination of the difference dimension data, a second distribution difference corresponding to the indicator data of the corresponding indicator field includes: Determining a combination of the difference dimension data based on a data table to which the difference dimension data belongs; Based on the combination of the difference dimension data, the third data table is queried to determine the third indicator data and the fourth indicator data corresponding to the combination of the difference dimension data, wherein the third indicator data belongs to the first data table, and the fourth indicator data belongs to the second data table; The second distribution difference between the third indicator data and the fourth indicator data is determined.

9. The method according to claim 1, characterized in that: The determining, based on the size of the second distribution difference, a reason causing the difference between the first data table and the second data table, includes: sorting the second distribution differences; The dimensional data causing the difference between the first data table and the second data table is determined according to the sorting result, and the reason for the difference includes the dimensional data causing the difference.

10. A data table analysis device, characterized in that: The device comprises: A data acquisition module, used to acquire the first data table and the second data table, and the same indicator field and the same dimension field in the first data table and the second data table, wherein each data record in the data table includes indicator data under the indicator field and dimension data under the dimension field; A data extraction module, used for extracting data from the first data table and the second data table based on the same indicator field and the same dimension field, obtaining first data and second data respectively, and forming a third data table; A first distribution difference determination module, configured to determine difference dimension data in the third data table based on a first distribution difference corresponding to the indicator data under the indicator field in the third data table; A second distribution difference determination module, used to determine, for each combination of the difference dimension data, a second distribution difference corresponding to the indicator data of the corresponding indicator field; The difference reason determination module is used to determine the reason causing the difference between the first data table and the second data table based on the size of the second distribution difference.

11. An electronic device, characterized in that: include: A memory and a processor, wherein the memory and the processor are communicatively connected to each other, the memory stores computer instructions, and the processor executes the data table analysis method according to any one of claims 1 to 9 by executing the computer instructions.

12. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores computer instructions, and the computer instructions are used to enable a computer to execute the data table analysis method according to any one of claims 1 to 9.

13. A computer program product, characterized in that The method comprises computer instructions for causing a computer to execute the data table analysis method according to any one of claims 1 to 9.

Images