Data statistics method based on large data volume

Abstract

The invention relates to the technical field of data processing, in particular to a data statistical method based on a large data volume, which comprises the following steps of: S1, defining a many-to-many first subject table and a second subject table of different data magnitudes in a relational database; s2, acquiring a mapping relation between the first main body table and a second main body table, traversing the second main body table, acquiring IDs of all records corresponding to each record in the second main body table in the first main body table, and splicing an ID list of nodes on a path by traversing a path from a node where the ID is located to a root node, after splicing is completed, an array is formed and stored in a field added to a corresponding record in the first main body table, and storage of the data mapping relation is completed; and S3, completing storage of a data mapping relationship between the first main body table and the second main body table for subsequent adjustment, statistics or query calling. According to the method, the data storage cost can be greatly reduced, the data maintenance cost can be reduced, and real-time statistics and query of a large amount of data can be realized.

Description

Technical Field

[0001] The present invention relates to the technical field of data processing, and in particular to a data statistics method based on large amounts of data. Background Art

[0002] A relational database is a database based on a relational model. It uses a table structure of rows and columns to organize data. This type of database is one of the most widely used database forms. However, in the application field of large data volumes, the gradually increasing amount of data puts a heavy load on traditional relational databases, resulting in difficulties in data storage and poor data access performance, which is not conducive to data statistics. Therefore, in order to further improve the efficiency of storing, counting, and querying data in many-to-many relationships, the prior art generally completes data storage through the method of sharding libraries and tables, and then queries and counts data through the keys used for routing through sharding libraries and tables. For example, a relational database, power grid model data storage and retrieval method disclosed in a Chinese patent (Announcement No.: CN110008289B) is disclosed. The relational database in the patented technology includes a master node and a slave node; the database service included in the master node is the master database, and the database service included in the slave node is the slave database; the master database is used to store information including metadata. The information from the slave database is used to store information including model data content; the storage method includes the following steps: sending the model data to the master node of the relational database; extracting the sharding feature field of the model data; calculating the slave node serial number according to the sharding feature field; storing the slave node serial number in the mapping table in the master database according to the mapping relationship between the sharding feature field value and the slave node serial number, and storing the model data content in the slave database corresponding to the slave node serial number; the distributed relational database can perform high-concurrency independent storage and independent retrieval of sharding information, but when facing data statistics of billions of orders of magnitude, for example, the master database is of billions of orders of magnitude and the slave database is of millions of orders of magnitude, the order of magnitude of the mapping table to be created is at least one billion orders of magnitude, at this time, based on the sharding feature field of the master database as the core of the key, implementing ID statistics and queries in the slave database will greatly occupy system resources, and it will not be possible to return results in time within the time required by the business. At the same time, the mapping table with a large amount of data will also increase the storage cost and maintenance cost to a certain extent. Summary of the invention

[0003] The technical problem to be solved by the present invention is as follows: the existing data statistics method is not applicable to data statistics work with large amounts of data. Completing the data storage work by the method of sub-library and sub-table will generate a mapping table with a huge amount of data, which will occupy a large amount of computing power resources during data statistics, and also increase storage costs and maintenance costs.

[0004] In order to solve the above technical problems, the present invention adopts the following technical solution: a data statistics method based on large data volume, comprising the following steps: S1: defines a many-to-many first and second main tables of different data levels in a relational database; S2: Obtain the mapping relationship between the first main body table and the second main body table, traverse the second main body table, obtain the IDs of all records corresponding to each record in the first main body table in the second main body table, traverse the path from the node where the ID is located to the root node, splice the ID list of the nodes on the path, form an array after splicing, and store it in the field added to the corresponding record in the first main body table to complete the storage of the data mapping relationship; S3: Complete the storage of the data mapping relationship between the first main table and the second main table for subsequent adjustment, statistics or query calls.

[0005] When the present invention is working, the IDs of related records are spliced ​​together by delimiters and then stored in the fields added to the corresponding records to realize the storage of data mapping relationships, which can avoid the generation of large-scale mapping tables, thereby greatly reducing data storage costs and data maintenance costs. At the same time, it can realize fast query of the second main table, thereby realizing real-time statistics and query of large amounts of data, improving system statistics and query performance, and there is no need to sacrifice the real-time and accuracy of the data.

[0006] Preferably, in step S2, a mapping relationship between the first main body table and the second main body table is obtained, the second main body table is traversed, and the IDs of all records corresponding to each record in the first main body table in the second main body table are obtained. By traversing the path from the node where the ID is located to the root node, the ID list of the nodes on the path is spliced, and after the splicing is completed, an array is formed and stored in the field added to the corresponding record in the first main body table. When the storage of the data mapping relationship is completed, the following steps are adopted: A1: Add corresponding fields to several records in the first main table; A2: define the corresponding record in the second main table as the root node, traverse the second main table, and obtain at least one corresponding record as a specific target node according to the mapping relationship between the first main table and the second main table; A3: Obtain a list of IDs of all records on the path from the root node of the rooted directed acyclic graph to the specific target node, concatenate them using a preset delimiter, and store them in the fields of the corresponding records in the first main table to complete the storage of the data mapping relationship.

[0007] Preferably, in step A1, the field is set to a JSON type field, the value of the field is set to a JSON array structure, and the field includes a list of IDs of all records on a path from a root node of at least one rooted directed acyclic graph to a specific target node.

[0008] When the present invention is working, it can realize the storage of the data mapping relationship between the first main table and the second main table, and can support real-time statistics of nodes at any level of the rooted directed acyclic graph, facilitate fuzzy matching of the second main table, have good real-time statistics and query performance, and can further improve data statistical efficiency and data statistical effect.

[0009] Preferably, step S2 also includes the following steps: when adding, deleting or updating the data mapping relationship between the records in the first main table and the records in the second main table, the non-relational database is synchronously written, and a unique identifier is created through the ID of the corresponding record in the first main table. The data structure of the corresponding value is consistent with the record in the first main table.

[0010] Preferably, the step S2 further includes the following steps: when adding, deleting or updating the data mapping relationship between the records in the first main table and the records in the second main table, synchronously maintaining the data structure in the relational database and the data structure in the non-relational database.

[0011] Preferably, in step S3, when the storage of the data mapping relationship between the first main table and the second main table is completed for subsequent adjustment, statistics or query calls, the following steps are adopted: when counting the data mapping relationship between the corresponding records in the second main table and the first main table, the second main table is traversed to obtain the ID list of all records from the root node to the record, and after splicing several IDs in sequence using delimiters, the fields of several records of the first main table are fuzzily matched backwards in the non-relational database to achieve real-time statistics of nodes at any level of the rooted directed acyclic graph.

[0012] Preferably, in step S3, when backward fuzzy matching is performed on the fields of several records of the first subject table in a non-relational database, the following steps are adopted: when backward fuzzy matching is performed in a non-relational database, the backward fuzzy grammar is set to one of forward fuzzy, backward fuzzy and full fuzzy.

[0013] Preferably, in step A3, the following steps are also included: obtaining a list of IDs of all records on the path from the root node of the rooted directed acyclic graph to the specific target node, splicing them with a preset delimiter and storing them in the field of the corresponding record in the first main table. When the storage of the data mapping relationship is completed, the following steps are adopted: recursively finding all the upper nodes in the second main table, splicing them with a preset delimiter, arranging the ID of the root node on the far left, arranging the ID of the specific target node on the far right, and filling the middle order with the IDs of all records from the root node to the specific target node.

[0014] Preferably, in step S4, the following steps are also included: B1: Add a data mapping relationship between the first record in the first main table and the second record in the second main table; B2: Query the ID list from the second record to the root node in the second main table, assemble the ID list into an array, query the first record in the first main table, and read the fields of the first record; B3: traverse several elements of the array, and determine in turn whether the field of the first record contains several elements of the array. If it does, do not insert the element. If it does not, add the element to the field of the record. After traversing all elements of the array, go to step B4. B4: Determine whether the field is updated. If yes, synchronize the updated field to the field of the first record in the first main table. Otherwise, terminate the adding operation.

[0015] Preferably, in step S4, the following steps are also included: C1: Delete the data mapping relationship between the first record in the first subject table and the second record in the second subject table; C2: Query the first record of the first subject table and read the fields of the first record; C3: traverse several elements of the array of the field of the first record, and determine whether each element ends with the ID of the second record as the end of the ID list. When the element ends with the ID of the second record as the end of the ID list, delete the element, and after traversing all elements of the array of the field of the first record, go to step C4; C4: Determine whether the field is updated. If so, synchronize the updated field to the field of the first record in the first main table. Otherwise, terminate the deletion operation.

[0016] The beneficial technical effects of the present invention include: 1. The present invention can realize the storage of data mapping relationship by splicing the IDs of related records through delimiters and storing them in the fields added to the corresponding records, which can avoid the generation of large-scale mapping tables, thereby greatly reducing data storage costs and data maintenance costs. At the same time, it can realize fast query of the second main table, thereby realizing real-time statistics and query of large amounts of data, improving system statistics and query performance, and there is no need to sacrifice the real-time and accuracy of the data.

[0017] 2. The present invention can realize the storage of the data mapping relationship between the first main table and the second main table, and can support real-time statistics of nodes at any level of the rooted directed acyclic graph, facilitate fuzzy matching of the second main table, have good real-time statistics and query performance, and can further improve data statistical efficiency and data statistical effect.

[0018] Other features and advantages of the present invention will be disclosed in detail in the following specific embodiments and drawings. BRIEF DESCRIPTION OF THE DRAWINGS

[0019] The present invention will be further described below in conjunction with the accompanying drawings: Figure 1 It is a workflow diagram of a data statistics method based on large amounts of data; Figure 2 is a workflow diagram of step S2 in a data statistics method based on large amounts of data; Figure 3 A workflow diagram for adding data mapping relationships; Figure 4 The following is a workflow diagram for deleting data mapping relationships. DETAILED DESCRIPTION

[0020] The technical solutions of the embodiments of the present invention are explained and described below in conjunction with the drawings of the embodiments of the present invention, but the following embodiments are only preferred embodiments of the present invention, not all. Based on the embodiments in the implementation mode, other embodiments obtained by those skilled in the art without creative work are all within the protection scope of the present invention.

[0021] In the following description, terms such as "inside", "outside", "up", "down", "left", "right", etc. that indicate directions or positional relationships are only used to facilitate the description of the embodiments and simplify the description, and do not indicate or imply that the referred device or element must have a specific direction, be constructed and operate in a specific direction. Therefore, it should not be understood as a limitation of the present invention. Embodiment 1:

[0022] Please see attached Figure 1 This embodiment discloses a data statistics method based on large data volume, including the following steps: S1: defines a many-to-many first and second main tables of different data levels in a relational database; S2: Obtain the mapping relationship between the first main body table and the second main body table, traverse the second main body table, obtain the IDs of all records corresponding to each record in the first main body table in the second main body table, traverse the path from the node where the ID is located to the root node, splice the ID list of the nodes on the path, form an array after splicing, and store it in the field added to the corresponding record in the first main body table to complete the storage of the data mapping relationship; S3: Complete the storage of the data mapping relationship between the first main table and the second main table for subsequent adjustment, statistics or query calls.

[0023] When this embodiment is working, the data mapping relationship can be stored by splicing the IDs of related records through delimiters and storing them in the fields added to the corresponding records. This can avoid the generation of large-scale mapping tables, thereby greatly reducing data storage costs and data maintenance costs. At the same time, it can realize fast query of the second main table, thereby realizing real-time statistics and queries of large amounts of data, improving system statistics and query performance, and not having to sacrifice the real-time and accuracy of the data.

[0024] For better, please refer to the attached Figure 2 In step S2, the mapping relationship between the first main body table and the second main body table is obtained, the second main body table is traversed, and the IDs of all records corresponding to each record in the first main body table in the second main body table are obtained. By traversing the path from the node where the ID is located to the root node, the ID list of the nodes on the path is spliced. After the splicing is completed, an array is formed and stored in the field added to the corresponding record in the first main body table. When the storage of the data mapping relationship is completed, the following steps are adopted: A1: Add corresponding fields to several records in the first main table; A2: define the corresponding record in the second main table as the root node, traverse the second main table, and obtain at least one corresponding record as a specific target node according to the mapping relationship between the first main table and the second main table; A3: Obtain a list of IDs of all records on the path from the root node of the rooted directed acyclic graph to the specific target node. In the specific implementation, the root node selects records without parent nodes, concatenates them with a preset delimiter and stores them in the fields of the corresponding records in the first main table to complete the storage of the data mapping relationship.

[0025] In the specific implementation, in the step A1, the field is set to a JSON type field, and the value of the field is set to a JSON array structure. The field includes an ID list of all records on the path from the root node of at least one rooted directed acyclic graph to the specific target node. When there are multiple paths from the root node to the specific target node, the value of the field will be an array of multiple elements. Preferably, the ID of the root node is arranged at the far left of the ID list, and the ID of the specific target node is arranged at the far right. The IDs of the corresponding records are filled in sequence in the middle of the ID list, thereby completing the storage of the data mapping relationship between the first main table and the second main table, thereby realizing the query and statistics of the business based on the rooted directed acyclic graph, and being able to keep the stored data volume consistent with the order of the first main table. In the specific implementation, the ID list is a long string.

[0026] When this embodiment is working, it can realize the storage of the data mapping relationship between the first main table and the second main table, and can support real-time statistics of nodes at any level of the rooted directed acyclic graph, facilitate fuzzy matching of the second main table, and has good real-time statistics and query performance, which can further improve data statistical efficiency and data statistical effect.

[0027] Please see attached Figure 3 In the specific implementation, in the step S4, the following steps are also included: B1: Add a data mapping relationship between the first record in the first main table and the second record in the second main table; B2: Query the ID list from the second record to the root node in the second main table, assemble the ID list into an array, query the first record in the first main table, and read the fields of the first record; B3: traverse several elements of the array, and determine in turn whether the field of the first record contains several elements of the array. If it does, do not insert the element. If it does not, add the element to the field of the record. After traversing all elements of the array, go to step B4. B4: Determine whether the field is updated. If yes, synchronize the updated field to the field of the first record in the first main table. Otherwise, terminate the adding operation.

[0028] Please see attached Figure 4 Preferably, in step S4, the following steps are also included: C1: Delete the data mapping relationship between the first record in the first subject table and the second record in the second subject table; C2: Query the first record of the first subject table and read the fields of the first record; C3: traverse several elements of the array of the field of the first record, and determine whether each element ends with the ID of the second record as the end of the ID list. When the element ends with the ID of the second record as the end of the ID list, delete the element, and after traversing all elements of the array of the field of the first record, go to step C4; C4: Determine whether the field is updated. If so, synchronize the updated field to the field of the first record in the first main table. Otherwise, terminate the deletion operation. Embodiment 2:

[0029] This embodiment provides a data statistics method based on large amounts of data. The similarities with other embodiments are not repeated here, and the differences are described in detail below.

[0030] In this embodiment, step S2 also includes the following steps: when adding, deleting or updating the data mapping relationship between the records in the first main table and the records in the second main table, the non-relational database is synchronously written, and a unique identifier is created through the ID of the corresponding record in the first main table. The data structure of the corresponding value is consistent with the record in the first main table.

[0031] Preferably, the step S2 further includes the following steps: when adding, deleting or updating the data mapping relationship between the records in the first main table and the records in the second main table, the data structure in the relational database and the data structure in the non-relational database are synchronously maintained.

[0032] In the specific implementation, in the step S3, when the storage of the data mapping relationship between the first main table and the second main table is completed for subsequent adjustment, statistics or query calls, the following steps are adopted: when counting the data mapping relationship between the corresponding records in the second main table and the first main table, the second main table is traversed to obtain the ID list of all records from the root node to the record, and after splicing several IDs in sequence using delimiters, the fields of several records of the first main table are fuzzily matched backwards in the non-relational database to achieve real-time statistics of nodes at any level of the rooted directed acyclic graph.

[0033] Preferably, in step S3, when backward fuzzy matching is performed on the fields of several records of the first subject table in the non-relational database, the following steps are adopted. When backward fuzzy matching is performed in the non-relational database, the backward fuzzy syntax is set to one of front fuzzy, back fuzzy and full fuzzy, which can be selected according to actual needs.

[0034] As a further improvement of this embodiment, in step A3, the following steps are also included: obtaining a list of IDs of all records on the path from the root node of the rooted directed acyclic graph to the specific target node, splicing them with a preset delimiter and storing them in the field of the corresponding record in the first main table. When the storage of the data mapping relationship is completed, the following steps are adopted: recursively finding all the upper nodes in the second main table, splicing them with a preset delimiter, arranging the ID of the root node on the far left, arranging the ID of the specific target node on the far right, and filling the middle order with the IDs of all records from the root node to the specific target node.

[0035] In this embodiment, in the relational database and the non-relational database, the fields recorded in the first subject table, wherein the value of each field includes the direct superior node from the root node to the specific target node, therefore, when performing statistics of backward fuzzy matching in the non-relational database, statistics are performed on any level from the root node to the specific target node, and the records in the subordinate first subject table can be covered. For example, the first subject table is provided with a record C, which has a field E, and its value is [{ "path":"1:2:3:4:5"},{ "path": "1:2:7:8:5"},{ "path": "1:4:9:11:12"}], when counting the record "4" in the second main table, that is, setting the specific target node to "4", when counting the total number of all subordinate nodes of the specific target node "4", that is, counting the total number of all related records between the record "4" and the first main table, you can use "1:2:3:4" or "1:4" to perform fuzzy matching in a non-relational database, and return the result for aggregation, so as to complete a data statistics, and the statistical time can be returned in seconds.

[0036] The beneficial technical effects of this embodiment include: the present invention can realize the storage of data mapping relationship by splicing the IDs of related records through delimiters and storing them in the fields added to the corresponding records, which can avoid the generation of large-scale mapping tables, thereby greatly reducing data storage costs and data maintenance costs, and at the same time can realize fast query of the second main table, thereby realizing real-time statistics and query of large amounts of data, improving system statistics and query performance, and without sacrificing the real-time and accuracy of the data.

[0037] The above is only a specific embodiment of the present invention, but the protection scope of the present invention is not limited thereto. Those skilled in the art should understand that the present invention includes but is not limited to the contents described in the drawings and the above specific embodiments. Any modification that does not deviate from the functional and structural principles of the present invention will be included in the scope of the claims.

Claims

1. A data statistics method based on large amounts of data, characterized in that: The following steps are involved: S1: defines a many-to-many first and second main tables of different data levels in a relational database; S2: Obtain the mapping relationship between the first main body table and the second main body table, traverse the second main body table, obtain the IDs of all records corresponding to each record in the first main body table in the second main body table, traverse the path from the node where the ID is located to the root node, splice the ID list of the nodes on the path, form an array after splicing, and store it in the field added to the corresponding record in the first main body table to complete the storage of the data mapping relationship; S3: Complete the storage of the data mapping relationship between the first main table and the second main table for subsequent adjustment, statistics or query calls.

2. The data statistics method based on large amounts of data according to claim 1, characterized in that: In the step S2, the mapping relationship between the first main body table and the second main body table is obtained, the second main body table is traversed, and the IDs of all records corresponding to each record in the first main body table in the second main body table are obtained. By traversing the path from the node where the ID is located to the root node, the ID list of the nodes on the path is spliced. After the splicing is completed, an array is formed and stored in the field added to the corresponding record in the first main body table. When the storage of the data mapping relationship is completed, the following steps are adopted: A1: Add corresponding fields to several records in the first main table; A2: define the corresponding record in the second main table as the root node, traverse the second main table, and obtain at least one corresponding record as a specific target node according to the mapping relationship between the first main table and the second main table; A3: Obtain a list of IDs of all records on the path from the root node of the rooted directed acyclic graph to the specific target node, concatenate them using a preset delimiter, and store them in the fields of the corresponding records in the first main table to complete the storage of the data mapping relationship.

3. The data statistics method based on large amounts of data according to claim 2, characterized in that: In step A1, the field is set to a JSON type field, and the value of the field is set to a JSON array structure. The field includes a list of IDs of all records on a path from a root node of at least one rooted directed acyclic graph to a specific target node.

4. The data statistics method based on large amounts of data according to claim 2, characterized in that: In the step S2, the following steps are also included: when adding, deleting or updating the data mapping relationship between the records in the first main table and the records in the second main table, the non-relational database is synchronously written, and a unique identifier is created through the ID of the corresponding record in the first main table. The data structure of the corresponding value is consistent with the record in the first main table.

5. The data statistics method based on large amounts of data according to claim 4, characterized in that: In the step S2, the following steps are also included: when adding, deleting or updating the data mapping relationship between the records in the first main table and the records in the second main table, the data structure in the relational database and the data structure in the non-relational database are synchronously maintained.

6. The data statistics method based on large amounts of data according to claim 4, characterized in that: In step S3, when the storage of the data mapping relationship between the first main table and the second main table is completed for subsequent adjustment, statistics or query calls, the following steps are adopted: when counting the data mapping relationship between the corresponding records in the second main table and the first main table, the second main table is traversed to obtain the ID list of all records from the root node to the record, and after splicing several IDs in sequence using delimiters, the fields of several records of the first main table are fuzzily matched backwards in the non-relational database to achieve real-time statistics of nodes at any level of the rooted directed acyclic graph.

7. The data statistics method based on large amounts of data according to claim 6, characterized in that: In step S3, when backward fuzzy matching is performed on the fields of several records of the first subject table in the non-relational database, the following steps are adopted. When backward fuzzy matching is performed in the non-relational database, the backward fuzzy grammar is set to one of front fuzzy, back fuzzy and full fuzzy.

8. The data statistics method based on large amounts of data according to claim 2, characterized in that: In step A3, a list of IDs of all records on a path from the root node of the rooted directed acyclic graph to a specific target node is obtained, spliced ​​by a preset delimiter and stored in a field of a corresponding record in the first main table. When the storage of the data mapping relationship is completed, the following steps are adopted: all upper nodes in the second main table are found recursively, spliced ​​by a preset delimiter, the ID of the root node is arranged on the far left, the ID of the specific target node is arranged on the far right, and the IDs of all records from the root node to the specific target node are filled in sequence in the middle.

9. The data statistics method based on large amounts of data according to claim 1, characterized in that: In the step S4, the following steps are also included: B1: Add a data mapping relationship between the first record in the first main table and the second record in the second main table; B2: Query the ID list from the second record to the root node in the second main table, assemble the ID list into an array, query the first record in the first main table, and read the fields of the first record; B3: traverse several elements of the array, and determine in turn whether the field of the first record contains several elements of the array. If it does, do not insert the element. If it does not, add the element to the field of the record. After traversing all elements of the array, go to step B4. B4: Determine whether the field is updated. If yes, synchronize the updated field to the field of the first record in the first main table. Otherwise, terminate the adding operation.

10. The data statistics method based on large data volume according to claim 1, characterized in that: In the step S4, the following steps are also included: C1: Delete the data mapping relationship between the first record in the first subject table and the second record in the second subject table; C2: Query the first record of the first subject table and read the fields of the first record; C3: traverse several elements of the array of the field of the first record, and determine whether each element ends with the ID of the second record as the end of the ID list. When the element ends with the ID of the second record as the end of the ID list, delete the element, and after traversing all elements of the array of the field of the first record, go to step C4; C4: Determine whether the field is updated. If so, synchronize the updated field to the field of the first record in the first main table. Otherwise, terminate the deletion operation.

Images