Multi-layer data rapid extraction method and device and storage medium

By sorting and segmenting multi-layer data by primary keys and using the SQL database function, the child table data is stuffed into the dynamic array of the parent table, solving the problem of slow speed when extracting data through loop traversal and achieving more efficient data extract.

CN118964401APending Publication Date: 2024-11-15COSCO SHIPPING
View PDF 0 Cites 0 Cited by

Patent Information

Application Number
CN202410924947.5
Authority / Receiving Office
CN · China
Patent Type
Applications(China)
Current Assignee / Owner
Filing Date
2024-07-10
Publication Date
2024-11-15

AI Technical Summary

Technical Problem

When the amount of data is large, the method of cycling through the database to extract data is slower, resulting in reduced efficiency.

Method used

By sorting the slave table data of multi-layer data based on the primary key of the main table data, and then slicing the child table data into the dynamic array of the parent table after segmentation, reducing the number of repeated filtering of individual data, and using the sorting function of SQL database to speed up extraction speed.

Benefits of technology

It significantly improves the data extraction speed, reduces the number of cycles, and speeds up the extraction speed by 3 to 4 times, solving the problem of slow extraction speed when the data volume is large.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN118964401A_ABST
    Figure CN118964401A_ABST
Patent Text Reader

Abstract

The embodiment of the invention discloses a multi-layer data rapid extraction method and device and a storage medium, and the method comprises the steps that slave table data in a database containing multi-layer data is sorted based on a primary key of primary table data, and data rows of the sorted slave table data have obvious segments; all the data tables in the sorted database are extracted and put into detailData; searching a first data table in the extracted database, searching a first parent table according to the first data table, and creating a first dynamic array in a corresponding row of the first parent table to store data in the first data table; and if the first parent table is empty, searching and processing the second data table and the second parent table until all the extracted data tables in the database are processed one by one. The method can be used for solving the problem that at present, when the data size is large, the speed of a method for circularly traversing a database to extract data is low.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present invention relates to the field of database technology, and in particular to a method, device and storage medium for quickly extracting multi-layer data. Background Art

[0002] At present, most of the data records in the database are based on the third normal form to establish the master-slave table relationship, that is, the slave table inherits the master table ID to establish a connection. When extracting data from the database, the database data is traversed in a loop, and the ID classification of the child and parent tables is used to establish a nested data structure. When the amount of data is particularly large, this method of traversing the data to extract data in a loop is too slow because it will repeatedly screen a single data and reduce efficiency. If the number of loops can be reduced and the number of repeated screenings of a single data can be reduced, the speed of data extraction can be increased.

[0003] In view of the current problem that the method of looping through the database to extract data is slow when the data volume is large, in order to reduce the number of data screening, improve screening efficiency and speed up the extraction speed, it is necessary to further study the multi-layer data fast extraction technology. Summary of the invention

[0004] The embodiments of the present invention provide a multi-layer data rapid extraction method, device and storage medium to solve the problem that the current method of looping through a database to extract data is slow when the amount of data is large.

[0005] In one aspect, the present invention provides a method for rapidly extracting multi-layer data, comprising:

[0006] Sorting the slave table data in a database containing multi-layer data based on the primary key of the master table data, so that the data rows of the sorted slave table data have obvious segments;

[0007] Extract all the data tables in the sorted database and put them into detail_Data;

[0008] Search the first data table in the extracted database, search the parent table first parent table according to the first data table, and create a first dynamic array in the corresponding row of the first parent table to store the data in the first data table; if the first parent table is empty, search and process the second data table and the second parent table until all data tables in the extracted database are processed one by one.

[0009] Furthermore, the step of sorting the slave table data in the database containing multi-layer data based on the primary key of the master table data includes:

[0010] Sort the slave table data in a database containing multi-layer data in descending order based on the primary key of the master table data.

[0011] Furthermore, before the step of creating a first dynamic array in a corresponding row of the first parent table to store the data in the first data table, the method further includes:

[0012] The parameter needAdd is used to determine whether to write the data in the first data table into the current record row of the first parent table. When needAdd=true, the data is written; when needAdd=false, the data is not written.

[0013] Furthermore, the first data table is table, the first parent table is PName, the i-th row record of the first parent table corresponds to the first data table, the state is controlled by the parameter state, the initial value of state is 0, and the step of creating a first dynamic array in the corresponding row of the first parent table to store the data in the first data table includes:

[0014] Initialize the parameters, set state = 0, j = 0;

[0015] Traverse the rows in table table, and use the needAdd function to determine whether the j-th row of table table needs to be put into the i-th dynamic array of PName according to the key fields of the table;

[0016] When needAdd = true, put the data of the jth row in table into the dynamic array of the ith row of PName, and if state = 0, set state = 1; when needAdd = false, determine the value of state, if state = 1, set state = 2; if state = 2, jump out of the loop;

[0017] After exiting the loop, set i=i+1 and enter the next loop.

[0018] Furthermore, after the step of putting the j-th row of data in the table into the dynamic array of the i-th row of PName, the method further includes:

[0019] After each recording of the j-th row of data in the table, a used mark is added to the j-th row of data to prevent repeated judgment.

[0020] In order to solve the problem that the current method of looping through a database to extract data is slow when the amount of data is large, the purpose of the present invention is to provide a method for fast extraction of multi-layer data, including: sorting the slave table data in the database containing multi-layer data based on the primary key of the master table data, and the data rows of the sorted slave table data have obvious segmentation; extracting all the data tables in the sorted database and putting them into detail_Data; searching for the first data table in the extracted database, searching for the parent table first parent table according to the first data table, and creating a first dynamic array in the corresponding row of the first parent table to store the data in the first data table; if the first parent table is empty, searching and processing the second data table and the second parent table, until all the data tables in the extracted database are processed one by one.

[0021] Compared with the prior art, the present invention has the following advantages:

[0022] The data is stored in SQL database, and the function of sorting data in SQL database is used to reduce the number of screening times for a single data, speed up the extraction, find the parent table through the child table, segment the data in the child table according to the ID of the parent table, and insert the data in the child table into the corresponding record row in the parent table after segmentation. This is used to solve the problem that the current method of looping through the database to extract data is slow when the amount of data is large.

[0023] In another aspect, the present invention provides a multi-layer data rapid extraction device, comprising:

[0024] A sorting module is used to sort the slave table data in a database containing multi-layer data based on the primary key of the master table data, and the data rows of the sorted slave table data have obvious segments;

[0025] The extraction module is used to extract all the data tables in the sorted database and put them into detail_Data;

[0026] A writing module is used to search for the first data table in the extracted database, search for the parent table first parent table according to the first data table, and create a first dynamic array in the corresponding row of the first parent table to store the data in the first data table; if the first parent table is empty, search and process the second data table and the second parent table until all data tables in the extracted database are processed one by one.

[0027] Furthermore, the sorting module is also used to:

[0028] Sort the slave table data in a database containing multi-layer data in descending order based on the primary key of the master table data.

[0029] Furthermore, the writing module is also used to:

[0030] The parameter needAdd is used to determine whether to write the data in the first data table into the current record row of the first parent table. When needAdd=true, the data is written; when needAdd=false, the data is not written.

[0031] Furthermore, the first data table is table, the first parent table is PName, the i-th row record of the first parent table corresponds to the first data table, the state is controlled by the parameter state, the initial value of state is 0, and the write module is further used for:

[0032] Initialize the parameters, set state = 0, j = 0;

[0033] Traverse the rows in table table, and use the needAdd function to determine whether the j-th row of table table needs to be put into the i-th dynamic array of PName according to the key fields of the table;

[0034] When needAdd = true, put the data of the jth row in table into the dynamic array of the ith row of PName, and if state = 0, set state = 1; when needAdd = false, determine the value of state, if state = 1, set state = 2; if state = 2, jump out of the loop;

[0035] After exiting the loop, set i=i+1 and enter the next loop.

[0036] In yet another aspect, the present invention provides a computer-readable storage medium storing a computer program, which can implement the above method when executed by a processor. BRIEF DESCRIPTION OF THE DRAWINGS

[0037] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the accompanying drawings required for use in the description of the embodiments of the present invention will be briefly introduced below. Obviously, the accompanying drawings in the following description are only some embodiments of the present invention. For ordinary technicians in this field, other accompanying drawings can be obtained based on these accompanying drawings without paying creative labor.

[0038] Figure 1 The figure is a flow chart of a method for rapidly extracting multi-layer data according to an embodiment of the present invention.

[0039] Figure 2 It is a data structure of a multi-layer model involved in one embodiment of the present invention.

[0040] Figure 3 It is a schematic diagram of a multi-layer data rapid extraction device according to another embodiment of the present invention. DETAILED DESCRIPTION

[0041] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0042] See also Figure 1 A multi-layer data rapid extraction method according to an embodiment of the present invention comprises the following steps:

[0043] Step 11, sorting the slave table data in the database containing multi-layer data based on the primary key of the master table data, and the data rows of the sorted slave table data have obvious segments.

[0044] In this step, it is necessary to first perform custom sorting on the data in the database containing multiple layers of data. In a SQL database, sorting can be performed based on a field in a data table. In an embodiment of the present invention, the data in the slave table needs to be sorted based on the data in the master table, and custom sorting on the data in the database is the basis for subsequent extraction operations.

[0045] Furthermore, the step of sorting the slave table data in the database containing multi-layer data based on the primary key of the master table data includes: sorting the slave table data in the database containing multi-layer data in descending order based on the primary key of the master table data.

[0046] Step 12, extract all the data tables in the sorted database and put them into detail_Data.

[0047] In this step, all the data tables in the SQL database are put into detail_Data. detail_Data is a Map<String,Object> Type, used to store the table name (Key) and Tblnfo (Value) of each table in the form of {Key, Value}. Tblnfo itself is LinkedHashMap<String,0bject> Type, including: {"data", table dataset}, {"KeyFieldList", table key field collection}.

[0048] Step 13, search for the first data table in the extracted database, search for the parent table first parent table based on the first data table, and create a first dynamic array in the corresponding row of the first parent table to store the data in the first data table; if the first parent table is empty, search and process the second data table and the second parent table until all data tables in the extracted database are processed one by one.

[0049] Furthermore, before the step of creating a first dynamic array in a corresponding row of the first parent table to store the data in the first data table, the method further includes:

[0050] The parameter needAdd is used to determine whether to write the data in the first data table into the current record row of the first parent table. When needAdd=true, the data is written; when needAdd=false, the data is not written.

[0051] In the embodiment of the present invention, the parameter needAdd is used to determine whether to put the child table record row into the current record row of the parent table. This parameter is a bool type. When needAdd=true, the data is recorded; when needAdd=false, the data is not recorded.

[0052] Furthermore, the first data table is table, the first parent table is PName, the i-th row record of the first parent table corresponds to the first data table, the state is controlled by the parameter state, the initial value of state is 0, and the step of creating a first dynamic array in the corresponding row of the first parent table to store the data in the first data table includes:

[0053] Initialize the parameters, set state = 0, j = 0;

[0054] Traverse the rows in table table, and use the needAdd function to determine whether the j-th row of table table needs to be put into the i-th dynamic array of PName according to the key fields of the table;

[0055] When needAdd = true, put the data of the jth row in table into the dynamic array of the ith row of PName, and if state = 0, set state = 1; when needAdd = false, determine the value of state, if state = 1, set state = 2; if state = 2, jump out of the loop;

[0056] After exiting the loop, set i=i+1 and enter the next loop.

[0057] Use the parameter state to control the state, the initial value of state is 0; when the first data that needs to be recorded is found, set state = 1; after state = 1, when the first data that does not need to be recorded is found, state = 2; when state = 2, you can jump out of the current loop.

[0058] As an implementation of an embodiment of the present invention, after the step of putting the j-th row of data in the table into the dynamic array of the i-th row of PName, the following step is further included:

[0059] After each recording of the j-th row of data in the table, a used mark is added to the j-th row of data to prevent repeated judgment.

[0060] In step 13, first loop through the table names, and search for the tables one by one in the extracted data table (the table name is denoted as table), and find the parent table name PName of table. If the parent table name PName is empty, it means that table table has no parent table, that is, table table is the top-level parent table, and then search for the next table. If PName is not empty, it means that the data rows in table table need to be inserted into the corresponding rows of table PName. Then create a dynamic array for each row of records in table PName, and take out the corresponding record rows from table table and put them into it. Then complete the data addition. Because the data in table table has been sorted according to the primary key of PName, the data rows in table table should be clearly segmented, as shown in Table 1. Among them, DecklD is the primary key of this table, and ShiptbID is the primary key of the parent table of this table.

[0061] Therefore, when the i-th row of the table PName is put into the table table:

[0062] (1) Initialize the parameters, set state = 0, j = 0. (In a computer array, j = 0 is the first)

[0063] (2) Traverse the rows in table table and determine whether the j-th row of table table needs to be placed in the i-th dynamic array of PName based on the table key field, that is, determine whether needAdd is true or false.

[0064] (3) If needAdd=true in step (2), then the data in the jth row of table needs to be put into the dynamic array of the ith row of PName, and if state=0, then set state=1.

[0065] (4) If needAdd = false in step (2), determine the value of state. If state = 1, set state = 2. If state = 2, exit the loop.

[0066] (5) After exiting the loop, set i=i+1 and enter the next loop.

[0067] After each recording of the j-th row of data in the table, a used mark is added to the j-th row of data to prevent repeated judgment.

[0068]

[0069] Table 1 Data row segmentation example

[0070] Therefore, in the process of extracting data, the basic statement is to put the j-th row record in the table table into the i-th row record in the PName table. This step can be used as the basic statement. Due to the uncertainty of the structure between data tables and the different number of record rows in each table, the time complexity can be judged here based on the number of times the basic statement is run in extracting the table table to the PName table.

[0071] The time complexity of the basic statement is set to 1. It can be assumed that there are m rows in the PName table and n rows in the table table.

[0072] If the loop traversal extraction method is used, if the data in the PName table and the Table table are traversed in a loop to determine whether the j-th row in the Table table needs to be inserted into the i-th row in the PName table, the number of times the basic statement is run is:

[0073] T(n)=O(m·n)#(1)

[0074] The time complexity of the loop traversal extraction method is O(n*m).

[0075] For example, in the multi-layer data fast extraction method of the embodiment of the present invention, since the sorting operation is added, the slave table will be sorted according to the ID of the master table. Therefore, when looping to the next one in the PName table, the loop of the table table can start from the position where the previous loop ended (this step is achieved by marking the elements that have been extracted). Therefore, in fact, the extraction can be completed by traversing the tale table once, and the number of executions of its basic statement is:

[0076] T(n)=O(n)#(1)

[0077] The time complexity of the multi-layer data fast extraction method according to the embodiment of the present invention is O(n).

[0078] From the comparison of the time complexity of the two algorithms, the fast extraction method proposed in this paper is faster than the method of extracting data by loop traversal.

[0079] The following will continue to illustrate the multi-layer data fast extraction method of the embodiment of the present invention with examples. The data structure of the multi-layer model used is as follows: Figure 2 shown.

[0080] The number of record rows in each table is shown in Table 1.

[0081] Table name Shiptb Deck DeckHeight DeckLine DeckEntity DELine quantity 7 71 29 5794 21221 87600

[0082] Table 1 Number of rows in each table

[0083] According to the data structure given in the example and the number of rows between the tables, the number of loops can be roughly calculated.

[0084] The method of extracting data by cyclic traversal is adopted. The number of cycles n1 required by the method of extracting data by cyclic traversal is:

[0085]

[0086] The number of loops n2 required for the method of cyclically traversing and extracting data using the fast extraction method of the embodiment of the present invention is:

[0087]

[0088] Therefore, the number of loops of the fast extraction method of the embodiment of the present invention is much less than that of the loop traversal extraction method, but the fast extraction method has more other statements, and the speed improvement needs to be analyzed according to the specific situation.

[0089] Using JavaScript programming, the results calculated according to different extraction methods are shown in Table 3.

[0090] method Loop through each table to extract Rapid extraction method time 6359ms 2267ms

[0091] According to the results analysis, the fast extraction method proposed in this paper is 3 to 4 times faster than the method of looping through the data table to extract data.

[0092] Therefore, the embodiment of the present invention stores data based on an SQL database, utilizes the function of the SQL database to sort data, reduces the number of screening times for a single data, speeds up the extraction speed, searches the parent table through the child table, segments the data in the child table according to the ID of the parent table, and after segmentation, inserts the data in the child table into the corresponding record row in the parent table, so as to solve the problem that the current method of looping through the database to extract data is slow when the amount of data is large.

[0093] See also Figure 3 A multi-layer data fast extraction device 20 according to another embodiment of the present invention can be used to implement the corresponding steps of the above method embodiment, including:

[0094] A sorting module 21, used to sort the slave table data in the database containing multi-layer data based on the primary key of the master table data, and the data rows of the sorted slave table data have obvious segments;

[0095] Extraction module 22, used to extract all data tables in the sorted database and put them into detail_Data;

[0096] The writing module 23 is used to search for the first data table in the extracted database, search for the parent table first parent table according to the first data table, and create a first dynamic array in the corresponding row of the first parent table to store the data in the first data table; if the first parent table is empty, search and process the second data table and the second parent table until all data tables in the extracted database are processed one by one.

[0097] Furthermore, the sorting module 21 is also used for:

[0098] Sort the slave table data in a database containing multi-layer data in descending order based on the primary key of the master table data.

[0099] Furthermore, the writing module 23 is also used for:

[0100] The parameter needAdd is used to determine whether to write the data in the first data table into the current record row of the first parent table. When needAdd=true, the data is written; when needAdd=false, the data is not written.

[0101] Furthermore, the first data table is table, the first parent table is PName, the i-th row record of the first parent table corresponds to the first data table, the state is controlled by the parameter state, the initial value of state is 0, and the writing module 23 is further used for:

[0102] Initialize the parameters, set state = 0, j = 0;

[0103] Traverse the rows in table table, and use the needAdd function to determine whether the j-th row of table table needs to be put into the i-th dynamic array of PName according to the key fields of the table;

[0104] When needAdd = true, put the data of the jth row in table into the dynamic array of the ith row of PName, and if state = 0, set state = 1; when needAdd = false, determine the value of state, if state = 1, set state = 2; if state = 2, jump out of the loop;

[0105] After exiting the loop, set i=i+1 and enter the next loop.

[0106] In yet another aspect, the present invention provides a computer-readable storage medium storing a computer program, which can implement the above method when executed by a processor.

[0107] Those of ordinary skill in the art will appreciate that the units and algorithm steps of each example described in conjunction with the embodiments disclosed in the embodiments of the present invention can be implemented in electronic hardware, or a combination of computer software and electronic hardware. Whether these functions are performed in hardware or software depends on the specific application and design constraints of the technical solution. Professional and technical personnel can use different methods to implement the described functions for each specific application, but such implementation should not be considered to be beyond the scope of the present invention.

[0108] Those skilled in the art can clearly understand that, for the convenience and brevity of description, the specific working processes of the systems, devices and units described above can refer to the corresponding processes in the aforementioned method embodiments and will not be repeated here.

[0109] The above is only a specific embodiment of the present invention, but the protection scope of the present invention is not limited thereto. Any person skilled in the art can easily think of changes or substitutions within the technical scope disclosed by the present invention, which should be included in the protection scope of the present invention. Therefore, the protection scope of the present invention should be based on the protection scope of the claims.

Claims

1. A method for rapid extraction of multi-layer data, characterized in that: include: Sorting the slave table data in the database containing multi-layer data based on the primary key of the master table data, wherein the data rows of the sorted slave table data have obvious segments; Extract all the data tables in the sorted database and put them into detail_Data; Search the first data table in the extracted database, search the parent table first parent table according to the first data table, and create a first dynamic array in the corresponding row of the first parent table to store the data in the first data table; if the first parent table is empty, search and process the second data table and the second parent table until all the data tables in the extracted database are processed one by one.

2. The multi-layer data rapid extraction method according to claim 1, characterized in that: The step of sorting the slave table data in the database containing multi-layer data based on the primary key of the master table data includes: Sort the slave table data in a database containing multi-layer data in descending order based on the primary key of the master table data.

3. The multi-layer data rapid extraction method according to claim 1 is characterized in that: Before the step of creating a first dynamic array in a corresponding row of the first parent table to store the data in the first data table, the method further includes: The parameter needAdd is used to determine whether to write the data in the first data table into the current record row of the first parent table. When needAdd=true, the data is written; when needAdd=false, the data is not written.

4. The multi-layer data rapid extraction method according to claim 3 is characterized in that: The first data table is table, the first parent table is PName, the i-th row record of the first parent table corresponds to the first data table, the state is controlled by the parameter state, the initial value of state is 0, and the step of creating a first dynamic array in the corresponding row of the first parent table to store the data in the first data table includes: Initialize the parameters, set state = 0, j = 0; Traverse the rows in table table, and use the needAdd function to determine whether the j-th row of table table needs to be put into the i-th dynamic array of PName according to the key fields of the table; When needAdd = true, put the data of the jth row in table into the dynamic array of the ith row of PName, and if state = 0, set state = 1; when needAdd = false, determine the value of state, if state = 1, set state = 2; if state = 2, jump out of the loop; After exiting the loop, set i=i+1 and enter the next loop.

5. The multi-layer data rapid extraction method according to claim 4, characterized in that: After the step of putting the j-th row of data in the table into the dynamic array of the i-th row of PName, the method further includes: After each recording of the j-th row of data in the table, a used mark is added to the j-th row of data to prevent repeated judgment.

6. A multi-layer data rapid extraction device, characterized in that: include: A sorting module, used for sorting the slave table data in the database containing multi-layer data based on the primary key of the master table data, wherein the data rows of the sorted slave table data have obvious segments; The extraction module is used to extract all the data tables in the sorted database and put them into detail_Data; A writing module is used to search for a first data table in the extracted database, search for a parent table, a first parent table, based on the first data table, and create a first dynamic array in a corresponding row of the first parent table to store the data in the first data table; if the first parent table is empty, search and process the second data table and the second parent table until all data tables in the extracted database are processed one by one.

7. The multi-layer data fast extraction device according to claim 6, characterized in that: The sorting module is also used for: Sort the slave table data in a database containing multi-layer data in descending order based on the primary key of the master table data.

8. The multi-layer data fast extraction device according to claim 6, characterized in that: The writing module is also used for: The parameter needAdd is used to determine whether to write the data in the first data table into the current record row of the first parent table. When needAdd=true, the data is written; when needAdd=false, the data is not written.

9. The multi-layer data fast extraction device according to claim 8, characterized in that: The first data table is table, the first parent table is PName, the i-th row record of the first parent table corresponds to the first data table, the state is controlled by the parameter state, the initial value of state is 0, and the writing module is further used for: Initialize the parameters, set state = 0, j = 0; Traverse the rows in table table, and use the needAdd function to determine whether the j-th row of table table needs to be put into the i-th dynamic array of PName according to the key fields of the table; When needAdd = true, put the data of the jth row in table into the dynamic array of the ith row of PName, and if state = 0, set state = 1; when needAdd = false, determine the value of state, if state = 1, set state = 2; if state = 2, jump out of the loop; After exiting the loop, set i=i+1 and enter the next loop.

10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the method according to any one of claims 1 to 5 can be implemented.