Aggregate query method, device, equipment and computer-readable storage medium

By obtaining the target connection key and using frequency sequence statistics table, the data bloat problem caused by inter-table connections in traditional CAQ methods is solved, and efficient connection aggregation query is realized, which significantly improves efficiency and resource utilization.

CN118643070BActive Publication Date: 2024-11-19MATRIX ORIGIN (SHENZHEN) INFORMATION TECHNOLOGY CO LTD
View PDF 0 Cites 0 Cited by

Patent Information

Application Number
CN202411112272.0
Authority / Receiving Office
CN · China
Patent Type
Patents(China)
Current Assignee / Owner
Filing Date
2024-08-14
Publication Date
2024-11-19
Estimated Expiration
2044-08-14

AI Technical Summary

Technical Problem

The traditional join aggregation query method causes data bloating due to inter-table connection operations, which seriously affects query efficiency and resource utilization.

Method used

By obtaining the target connection keys for the aggregate query requirements, using the preset frequency sequence to count the original data table, generating frequency statistics tables, and connecting these tables according to the aggregate connection function specified by the aggregate query requirements to generate data aggregation tables to avoid direct connections Huge amount of intermediate data generated by multiple original tables.

Benefits of technology

It effectively curbs data bloat, reduces resource consumption for connected aggregation queries, and significantly improves query efficiency.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN118643070B_ABST
    Figure CN118643070B_ABST
Patent Text Reader

Abstract

The present application relates to the technical field of database computing engines, and in particular to an aggregate query method, apparatus, device, and computer-readable storage medium, the method comprising: in response to an aggregate query instruction, obtaining a target connection key corresponding to an aggregate query requirement; determining multiple original data tables connected by the target connection key, and performing statistics on the data tuples of each original data table based on a preset frequency column to obtain a frequency statistics table of each original data table, the frequency statistics table at least including data tuples carrying tuple cumulative values; connecting each frequency statistics table according to an aggregate connection function specified by the aggregate query requirement to generate a data aggregation table. The present application avoids the data expansion caused by the connection operation between tables in the traditional connection aggregation query method, and significantly improves the efficiency of the connection aggregation query.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present application relates to the technical field of database computing engines, and in particular to an aggregate query method, apparatus, device, and computer-readable storage medium. Background Art

[0002] With the continuous development of database computing engine technology, users have put forward higher requirements for the efficiency of join and aggregation queries. In database management systems, CAQ (Cartesian Aggregation Query) is a common and complex query type that combines CQ (Connection Query) and A (Aggregation) to extract, combine and aggregate data from multiple related tables. However, the traditional CAQ processing method first performs inter-table join operations and then applies aggregate functions. It is very easy to cause explosive growth of data (i.e. data inflation) due to the Cartesian product effect in the join process, which seriously affects the efficiency of queries and resource utilization.

[0003] Therefore, how to avoid data expansion caused by inter-table join operations to improve the efficiency of join aggregation queries is a technical problem that needs to be solved urgently. Summary of the invention

[0004] The main purpose of the present application is to provide an aggregate query method, apparatus, device and computer-readable storage medium, aiming to avoid data expansion caused by inter-table join operations to improve the efficiency of join aggregate queries.

[0005] To achieve the above object, the present application provides an aggregate query method, which includes:

[0006] In response to the aggregate query instruction, obtain the target connection key corresponding to the aggregate query requirement;

[0007] Determine multiple original data tables connected by the target connection key, and perform statistics on data tuples of each original data table based on a preset frequency column to obtain a frequency statistics table of each original data table, wherein the frequency statistics table at least includes data tuples carrying tuple cumulative values;

[0008] The frequency statistics tables are connected according to the aggregation connection function specified by the aggregation query requirement to generate a data aggregation table.

[0009] In one embodiment, the step of performing statistics on the data tuples of each original data table based on a preset frequency column to obtain a frequency statistics table of each original data table includes:

[0010] In each of the original data tables, a preset frequency column is set at the end of the data tuple, and each of the data tuples is counted according to the counting key of the frequency column to obtain a data tuple carrying a tuple cumulative value;

[0011] The frequency statistics table is constructed based on the preset data structure and the data tuples carrying the tuple cumulative values.

[0012] In one embodiment, the step of constructing the frequency statistics table according to the preset data structure and the data tuples carrying the tuple cumulative values ​​includes:

[0013] Determine an element column and an element count column corresponding to a preset data structure, and use the data tuple carrying the tuple cumulative value as a data structure tuple, where there are multiple data structure tuples;

[0014] Each of the data structure tuples is sequentially arranged below the element column, and the tuple cumulative value carried by each arranged data structure tuple is sequentially set below the element count column arranged side by side with the element column to obtain the frequency statistics table.

[0015] In one embodiment, the step of connecting the frequency statistics tables according to the aggregate join function specified by the aggregate query requirement to generate a data aggregation table includes:

[0016] Determine an equivalent tuple identifier corresponding to the aggregate join function specified by the aggregate query requirement;

[0017] The data blocks to be connected of each frequency statistics table are determined according to the equivalent tuple identifier, and each data block to be connected is aggregated and connected according to the aggregate connection function to generate a data aggregation table.

[0018] In one embodiment, the step of determining the data blocks to be connected of each frequency statistics table according to the equivalent tuple identifier includes:

[0019] In response to the equivalent tuple identifier, obtaining the tuple to be connected corresponding to each of the frequency statistics tables; and searching for a data tuple matching the corresponding tuple to be connected from each of the frequency statistics tables;

[0020] According to the matched data tuples in each frequency statistics table and the tuple cumulative values ​​carried by the matched data tuples, the data blocks to be connected of each frequency statistics table are constructed.

[0021] In one embodiment, the step of performing aggregating connection on each of the to-be-connected data blocks according to the aggregate connection function to generate a data aggregation table includes:

[0022] Aggregate and connect the data tuples with the same equivalent element values ​​between different data blocks to be connected to obtain an intermediate structure table of aggregate links;

[0023] Determine the element operation rule corresponding to the aggregation connection function, and perform operation processing on the intermediate structure table according to the element operation rule to obtain a data aggregation table.

[0024] In one embodiment, after the step of determining the multiple original data tables connected by the target connection key, the aggregate query method includes:

[0025] In each of the original data tables, detecting whether the processing amount of the data tuple exceeds a preset parallel processing threshold;

[0026] If the processing amount does not exceed the parallel processing threshold, executing the step of performing statistics on each data tuple of the original data table based on a preset frequency column;

[0027] If the processing volume exceeds the parallel processing threshold, the excess data table is divided according to the preset number of processing cores to obtain the same number of divided data tables as the number of processing cores, and statistics are synchronously performed on each of the divided data tables based on the frequency column to obtain a frequency statistics table of the excess data table, wherein the excess data table is the original data table whose processing volume exceeds the parallel processing threshold.

[0028] In addition, to achieve the above-mentioned purpose, the present application also provides an aggregate query device, which includes:

[0029] A response module, used to respond to the aggregate query instruction and obtain the target connection key corresponding to the aggregate query requirement;

[0030] A statistical module, used for determining a plurality of original data tables connected by the target connection key, and performing statistics on the data tuples of each of the original data tables based on a preset frequency column to obtain a frequency statistical table of each of the original data tables, wherein the frequency statistical table at least includes data tuples carrying tuple cumulative values;

[0031] The aggregation module is used to connect the frequency statistics tables according to the aggregation connection function specified by the aggregation query requirement to generate a data aggregation table.

[0032] Each functional module of the aggregate query device of the present application implements the steps of the aggregate query method of the present application as described above during operation.

[0033] In addition, to achieve the above-mentioned purpose, the present application also provides a device, which includes a memory, a processor, and an aggregation query program stored on the memory and executable on the processor, and the aggregation query program implements the steps of the above-mentioned aggregation query method when executed by the processor.

[0034] In addition, to achieve the above-mentioned purpose, the present application also provides a storage medium, which is a computer-readable storage medium, and an aggregation query program is stored on the computer-readable storage medium. When the aggregation query program is executed by the processor, the steps of the above-mentioned aggregation query method are implemented.

[0035] The present application provides an aggregate query method that optimizes the Cartesian product effect and data explosion problems caused by the traditional CAQ method due to the table connection operation, and significantly improves the efficiency of the connection aggregate query. Specifically, in response to the aggregate query instruction, the target connection key corresponding to the aggregate query requirement can be quickly and accurately obtained, and then the multiple original data tables that the target connection key needs to connect can be accurately obtained; next, the present application uses a preset frequency column to count each original data table connected by the target connection key, so that each generated frequency statistics table not only includes all data tuples recorded in the corresponding original data table, but also effectively compresses the data volume of the data tuple by accumulating the tuple cumulative value of each data tuple, and also avoids the huge amount of intermediate data generated by directly connecting multiple original data tables in the traditional CAQ method, thereby effectively curbing data expansion. Next, according to the aggregate connection function specified by the aggregate query requirement, the aggregate connection operation is performed on each frequency statistics table, and the required data aggregation table can be quickly and accurately generated, which not only effectively reduces the resource consumption of the connection aggregate query, but also significantly improves the efficiency of the connection aggregate query. BRIEF DESCRIPTION OF THE DRAWINGS

[0036] Figure 1 It is a flowchart of the first embodiment of the aggregate query method of the present application;

[0037] Figure 2 It is a statistical flow chart of data tuples involved in the aggregate query method of this application;

[0038] Figure 3 It is a flowchart of the second embodiment of the aggregate query method of the present application;

[0039] Figure 4 It is a schematic diagram of the polymerization operation involved in the embodiment of the present application;

[0040] Figure 5 is another schematic diagram of polymerization operation involved in the embodiment of the present application;

[0041] Figure 6 It is another schematic diagram of a polymerization operation involved in the embodiment of the present application;

[0042] Figure 7 It is a structural diagram of an aggregate query device involved in an embodiment of the present application;

[0043] Figure 8 This is a schematic diagram of the structure of the aggregate query device involved in the embodiment of the present application.

[0044] The realization of the purpose, functional features and advantages of this application will be further explained in conjunction with embodiments and with reference to the accompanying drawings. DETAILED DESCRIPTION

[0045] The present application embodiment provides an aggregate query method, referring to Figure 1 As shown, Figure 1 It is a flowchart of the first embodiment of the aggregate query method of the present application.

[0046] Here, exemplary embodiments are described in detail, and examples thereof are shown in the accompanying drawings. When the following description refers to the drawings, unless otherwise indicated, the same numbers in different drawings represent the same or similar elements. The implementations described in the following exemplary embodiments do not represent all implementations consistent with the present application.

[0047] CAQ (Cartesian Aggregation Query) usually involves a combination of CQ (ConnectionQuery) and A (Aggregation). CQ is responsible for establishing connections between multiple tables and returning qualified combined results; the A part further aggregates these combined results according to the specified grouping columns to generate the final query results. In traditional database systems, processing CAQ queries generally involves first joining each related table in pairs, and then performing group aggregation on the joined results. However, there is a serious problem with the two-by-two joins in this processing flow: since each join involves a Cartesian product (especially when there is no index), the join results tend to expand significantly, resulting in a surge in the amount of intermediate result data, which seriously affects query performance and also brings additional memory / storage overhead.

[0048] Specifically, the defects and challenges of the existing technology are as follows:

[0049] 1. Data expansion: Since each connection will have a local Cartesian product, the result set will expand rapidly, increasing the memory and disk resource usage. In addition, the intermediate data needs to be scanned, sorted, and aggregated multiple times, which further increases the database's computing load and slows down the query speed. On the other hand, data expansion requires more data transmission and storage, which creates a greater burden on network and disk I / O. The transmission of intermediate data results in a multi-node distributed system takes up a lot of bandwidth, and the frequent reading and writing in the storage system causes the disk I / O load to be too high, resulting in reduced performance of other tasks.

[0050] 2. Performance issues: The increase in intermediate data sets directly affects the latency of data processing. A large amount of intermediate data requires frequent read and write operations, which requires a large amount of computing resources to sort and aggregate data, thus seriously reducing the response time of query operations and the overall performance of the system. Especially in distributed systems, the system may also need to frequently exchange intermediate data to other nodes, causing network congestion. At the same time, a large amount of network transmission will also lead to longer query latency.

[0051] 3. Resource competition and other load impacts: Large amounts of computing and storage requirements will also lead to increased resource competition in the database system. Large amounts of CPU, memory, and IO usage not only reduce the execution efficiency of the current query task, but also interfere with other loads running in the system, such as extending the response time of other system operations and even causing service instability.

[0052] 4. Maintenance complexity: Current database solutions often rely on complex indexing and query plan optimization algorithms to alleviate data expansion problems, but this introduces additional maintenance complexity and increases the burden on database administrators and developers.

[0053] Therefore, in order to solve the above-mentioned defects of traditional CAQ queries, the present application provides an aggregate query method, apparatus, device and computer-readable storage medium.

[0054] It should be noted that the execution subject of this embodiment may be a computing service device with data processing, network communication and program running functions, such as a tablet computer, a personal computer, a mobile phone, a database system, etc., or a device capable of realizing the above functions, such as an aggregate query device, etc. The following takes the aggregate query device as an example to illustrate this embodiment and the following embodiments.

[0055] The aggregate query method of this application includes:

[0056] Step S10: In response to the aggregate query instruction, obtain the target connection key corresponding to the aggregate query requirement.

[0057] In this embodiment, the aggregate query device provided in the present application is communicatively connected to the human-computer exchange interface, and the human-computer exchange interface can be integrated into the aggregate query device, or can be externally provided to the aggregate query device, and connected to the aggregate query device via wired or wireless communication. Specifically, the aggregate query device provided in the present application responds to the aggregate query instruction sent by the human-computer exchange interface, so that the target connection key corresponding to the aggregate query demand can be quickly and accurately obtained, thereby being able to accurately capture the core association points in the user's query intent, that is, to determine which fields or key values ​​need to be used as the basis for connecting different original data tables, thereby avoiding unnecessary data table scanning and calculations, and thus significantly improving the response speed and efficiency of the connected aggregate query.

[0058] It should be noted that the target connection key can be understood as a field or field combination used to establish a relationship between multiple original data tables. For example, the data table library set by the aggregate query device stores several original data tables, such as data table A, data table B, data table C, ..., data table R and data table S, etc. If the target connection key is represented by , that is, the target connection key indicates that data table R and data table S are to be connected.

[0059] Step S20: Determine multiple original data tables connected by the target connection key, and count the data tuples of each original data table based on a preset frequency column to obtain a frequency statistics table of each original data table, wherein the frequency statistics table at least includes data tuples carrying tuple cumulative values.

[0060] In this embodiment, after determining the multiple original data tables connected by the target connection key, the present application innovatively introduces a preset frequency column, and introduces the preset frequency column into each original data table, and generates a frequency statistics table for each original data table, thereby retaining all data tuples in the original data table while also achieving data compression of the original data table, which not only effectively reduces the amount of data tuples stored in the data table, but also effectively curbs the huge amount of intermediate data that may be generated when directly connecting multiple tables, thereby effectively curbing the data expansion phenomenon.

[0061] It should be noted that the frequency column set in the present application is essentially an integer column, which indicates that the number of times a data tuple appears is obtained by increasing the value of the frequency column recording the number of times the data tuple appears (i.e., the tuple cumulative value), rather than inserting a new row in the original data table to record the data tuple, thereby effectively reducing the size of intermediate results in subsequent CQ operations.

[0062] In a specific embodiment, referring to Table 1, the original data table shown in Table 1 is a data table R, which can be represented by a table Indicates that the table The original data storage content is shown in Table 1. The data table R stores multiple data tuples in sequence, namely ; ; ; ; ;in, are all elements in the data tuple, and the elements For elements as well as For example, when the data table R is used in an e-commerce shopping scenario, the element Can represent user ID, element Can represent the purchase of goods, elements It can indicate the number of orders placed.

[0063]

[0064] Next, referring to Table 2, the present application introduces the preset frequency column into the data table R to generate the frequency statistics table of the data table R shown in Table 2. Among them, the Z column in Table 2 represents the frequency column, and the number 3 recorded in the Z column can be understood as the data tuple " " appears 3 times (i.e., the cumulative value of the tuple). The number 2 recorded in the Z column can be understood as the data tuple " "The number of occurrences (i.e. the cumulative value of the tuple) is 2 times.

[0065]

[0066] In another embodiment, if a new instruction for adding a new data tuple is detected in a frequency statistics table, all data tuples in the frequency statistics table are traversed according to the new data tuple. If the target data tuple (i.e., the same data tuple as the new data tuple) is obtained after the traversal, the number of tuples of the new data tuple is added to the tuple cumulative value of the target data tuple in the frequency column to obtain the updated tuple cumulative value. For example, refer to Figure 2 , Figure 2 This is a data tuple statistics flow chart involved in the aggregate query method of this application. When a row of data tuples is inserted into the frequency statistics table of data table R ; then in column Z, the "data tuple " is added to the cumulative value of the tuple (i.e. 3) of the "data tuple "The number of new additions (i.e. 1) is updated to obtain the "data tuple " has a tuple cumulative value of 4. In addition, if the data tuple that is the same as the newly added data tuple is not traversed, a new row is added after the last row of data tuples in the frequency statistics table to set the newly added data tuple, and the number of tuples of the newly added data tuple in the frequency column is used as the tuple cumulative value of the newly added data tuple.

[0067] Step S30: Connect the frequency statistics tables according to the aggregation connection function specified by the aggregation query requirement to generate a data aggregation table.

[0068] In this embodiment, after determining the aggregation join function specified by the aggregation query requirement, and determining the equivalent tuple identifier pointed to by the aggregation join function, efficient integration of multiple frequency statistical tables is achieved. Specifically, the data blocks to be connected related to A (aggregation operation) of each frequency statistical table can be directly located based on the equivalent tuple identifier, avoiding unnecessary data traversal, thereby effectively reducing the consumption of computing resources; next, all data blocks to be connected are accurately aggregated according to the aggregation join function, so that a data aggregation table with a clear structure and accurate content can be efficiently generated, which not only effectively reduces the resource consumption of the connection aggregation query, but also significantly improves the efficiency of the connection aggregation query.

[0069] In summary, the present application provides an aggregate query method that optimizes the Cartesian product effect and data explosion problems caused by the traditional CAQ method due to the table connection operation, and significantly improves the efficiency of the connection aggregate query. Specifically, in response to the aggregate query instruction, the target connection key corresponding to the aggregate query requirement can be quickly and accurately obtained, and then the multiple original data tables that the target connection key needs to connect can be accurately obtained; next, the present application uses a preset frequency column to count each original data table connected to the target connection key, so that each generated frequency statistics table not only includes all data tuples recorded in the corresponding original data table, but also effectively compresses the data volume of the data tuple by accumulating the tuple cumulative value of each data tuple, and also avoids the huge amount of intermediate data generated by directly connecting multiple original data tables in the traditional CAQ method, thereby effectively curbing data expansion. Next, according to the aggregate connection function specified by the aggregate query requirement, the aggregate connection operation is performed on each frequency statistics table, and the required data aggregation table can be generated quickly and accurately, which not only effectively reduces the resource consumption of the connection aggregation query, but also significantly improves the efficiency of the connection aggregation query.

[0070] Further, based on the first embodiment of the aggregate query method of the present application, a second embodiment of the aggregate query method of the present application is proposed, referring to Figure 3 As shown, Figure 3 It is a flow chart of the second embodiment of the aggregate query method of the present application.

[0071] Further, in some feasible embodiments, the above step S20: performing statistics on the data tuples of each original data table based on a preset frequency column to obtain a frequency statistics table of each original data table, further includes the following implementation steps S201-S203:

[0072] Step S201: In each of the original data tables, a preset frequency column is set at the end of the data tuple, and each of the data tuples is counted according to the counting key of the frequency column to obtain a data tuple carrying a tuple cumulative value.

[0073] In this embodiment, for each original data table, a preset frequency column is set at the end of the data tuple, so as to achieve direct tracking of the number of times the data tuple occurs. Specifically, each data tuple is counted according to the counting key of the frequency column, so that the data tuple carrying the tuple cumulative value can be accurately obtained, that is, the tuple cumulative value can accurately reflect the number of times the data tuple carrying the tuple cumulative value appears in the original data table.

[0074] Step S202: construct the frequency statistics table according to the preset data structure and the data tuples carrying the tuple cumulative values.

[0075] In this embodiment, a frequency statistics table can be accurately constructed according to the preset data structure and the data tuples carrying the tuple cumulative values, thereby effectively improving the efficiency and accuracy of subsequent CAQ queries.

[0076] Furthermore, in some other feasible embodiments, the above step S202: constructing the frequency statistics table according to the preset data structure and the data tuples carrying the tuple cumulative values, further includes the following implementation steps S2021-S2023:

[0077] Step S2021: determining an element column and an element count column corresponding to a preset data structure, and using the data tuple carrying the tuple cumulative value as a data structure tuple, wherein there are multiple data structure tuples;

[0078] Step S2022: Arrange each of the data structure tuples in sequence below the element column, and set the tuple cumulative value carried by each arranged data structure tuple in sequence below the element count column arranged side by side with the element column to obtain the frequency statistics table.

[0079] In this embodiment, the element column and element count column corresponding to the preset data structure are first determined, and the data tuple carrying the tuple cumulative value is taken as the data structure tuple, and the number of data structure tuples is multiple; then, each data structure tuple is sorted according to the sorting rule defined by the element column, and the sorted data structure tuples are arranged in sequence under the element column, and then the tuple cumulative values ​​carried by the arranged data structure tuples are arranged in sequence under the element count column arranged side by side with the element column, so that a frequency statistics table reflecting the number of occurrences of the data tuples can be quickly and accurately generated.

[0080] Further, in some feasible embodiments, the above step S30: connecting the frequency statistics tables according to the aggregate join function specified by the aggregate query requirement to generate a data aggregation table, further includes the following implementation steps S301 to S302:

[0081] Step S301: Determine the equivalent tuple identifier corresponding to the aggregate join function specified by the aggregate query requirement.

[0082] In this embodiment, the aggregation query device set up in this application includes multiple aggregation functions. After determining the function identifier corresponding to the aggregation query requirement, the aggregation function matching the function identifier found from the multiple aggregation functions is used as the aggregation join function, and the equivalent tuple identifier corresponding to the aggregation join function can be accurately obtained.

[0083] It should be noted that aggregate functions may include sum(*), avg(*), min(*) and max(*), as well as compound operations of the above sum(*), avg(*), min(*) and max(*); among which sum(*) refers to calculating the sum of the values ​​in a certain element column, avg(*) refers to calculating the average value of the values ​​in a certain element column, min(*) refers to calculating the minimum value of the values ​​in a certain element column, and min(*) refers to calculating the maximum value of the values ​​in a certain element column.

[0084] For example, refer to Figure 4 , Figure 4 is a schematic diagram of the aggregation operation involved in the embodiment of the present application. Figure 4 The aggregate function sum(a) group by id is shown to perform the aggregation operation and obtain Figure 4 The aggregation result table shown is the aggregation column, which represents an intermediate state of the aggregation operation. The aggregation function sum(a)group by id represents the sum of the values ​​of the element a column and groups the sum results according to the element id column.

[0085] Step S302: determining the data blocks to be connected of each of the frequency statistics tables according to the equivalent tuple identifiers, and performing aggregation connection on each of the data blocks to be connected according to the aggregation connection function to generate a data aggregation table.

[0086] In this embodiment, the data blocks to be connected in each frequency statistics table are accurately located according to the equivalent tuple identifier, and these data blocks to be connected are efficiently aggregated and connected according to the aggregation connection function, so that a structured data aggregation table can be quickly generated, which not only reduces unnecessary data scanning and calculation, but also ensures the accuracy of the aggregation results, thereby significantly improving the efficiency of the connection aggregation query.

[0087] In a specific embodiment, refer to Figure 5 , Figure 5 It is a schematic diagram of the aggregation connection involved in the embodiment scheme of the present application. Figure 5 The aggregation join functions are set as: sum(Ra), sum(Sa), Z ( , R.id=S.id), for data table R and data table S according to Figure 5 The set aggregate join function is used to perform aggregate join. Specifically, a preset frequency column (i.e., column Z) is introduced to count the data tuples consisting of the values ​​of the element id column and the values ​​of the element a column in the data table R, and the data tuples consisting of the values ​​of the element id column and the values ​​of the element a column in the data table S, and the result is Figure 5 The frequency statistics table of data table R and the frequency statistics table of data table S are shown in the figure; next, based on the frequency statistics table of data table R and the frequency statistics table of data table S, the data tuples with the same value of the element id column in the two tables (i.e., the frequency statistics table of data table R and the frequency statistics table of data table S) are aggregated and connected as the data blocks to be connected to generate Figure 5 The data aggregation table shown.

[0088] It should be noted that the data blocks to be connected set in this application are data tuples with the same value of the target element column in multiple frequency statistics tables. Figure 5 The data tuples with the same value in the element id column in the two tables (the frequency statistics table of data table R and the frequency statistics table of data table S) are shown. Specifically, Figure 5 The data blocks to be connected involved in the frequency statistics table of data table R are data tuples , and data tuples ; Figure 5 The data blocks to be connected involved in the frequency statistics table of data table S are data tuples , data tuple , and data tuples Next, the data tuples with the same value of the element id column in the two tables are combined. After the combination, the value of 1 in the element id column of the two tables appears 6 times (that is, the Z column records the cumulative value of the tuple when the element id column value is 1), the value of sum(Ra) is 12, and the value of sum(Sa) is 6. In addition, the value of 2 in the element id column of the two tables after the combination appears 4 times (that is, the Z column records the cumulative value of the tuple when the element id column value is 2), the value of sum(Ra) is 4, and the value of sum(Sa) is 4.

[0089] In addition, it should be noted that Figure 5The equivalent tuple identifier corresponding to the aggregate join function shown is R.id=S.id, which can be understood as the value of the element id column in the data table R must be equal to the value of the element id column in the data table S. Figure 5 The aggregate join function shown can be understood as summing the values ​​of the element a column in data table R and the element a column in data table S when joining data table R and data table S. Finally, the values ​​of the same element id column in the two tables (i.e., data table R and data table S) are grouped, and the number of times the same value of the element id column of the two tables appears after the connection is counted through the Z column.

[0090] Furthermore, in some other feasible embodiments, the above step S302: determining the data blocks to be connected of each frequency statistics table according to the equivalent tuple identifier, may also include the following implementation steps S3021-S3022:

[0091] Step S3021: in response to the equivalent tuple identifier, obtain the tuple to be connected corresponding to each of the frequency statistics tables; and search for a data tuple matching the corresponding tuple to be connected from each of the frequency statistics tables;

[0092] Step S3022: constructing the data blocks to be connected of each frequency statistics table according to the matched data tuples in each frequency statistics table and the tuple cumulative values ​​carried by the matched data tuples.

[0093] In this embodiment, the equivalent tuple identifier is used as an index, so that the tuple to be connected corresponding to each frequency statistical table can be quickly found, and then the data tuple matching the corresponding tuple to be connected is found from each frequency statistical table, and then the data block to be connected of each frequency statistical table is constructed based on the matching data tuples in each frequency statistical table and the tuple cumulative value carried by the matching data tuple, thereby effectively improving the efficiency of subsequent aggregate queries.

[0094] Further, in one embodiment, referring to Figure 6 , Figure 6 It is another schematic diagram of a polymerization operation involved in the embodiment of the present application. Figure 6 The aggregation join functions are: S.uid, sum(Ra), sum(Sa), Z ( , R.id=S.id). For data table R and data table S, follow Figure 6 Specifically, the preset frequency column (i.e., column Z) is introduced to count the data tuples consisting of the values ​​of the element id column, the element uid column, and the element a column in the data table R, and the data tuples consisting of the values ​​of the element id column, the element uid column, and the element a column in the data table S, and the result is Figure 6 The frequency statistics table of data table R and the frequency statistics table of data table S are shown in the figure; next, based on the frequency statistics table of data table R and the frequency statistics table of data table S, the data tuples with the same value of the element id column in the two tables (i.e., the frequency statistics table of data table R and the frequency statistics table of data table S) are aggregated and connected as the data blocks to be connected to generate Figure 6 The data aggregation table shown.

[0095] Further, in some feasible embodiments, the above step S302: performing aggregating connection on each of the to-be-connected data blocks according to the aggregate connection function to generate a data aggregation table may also include the following implementation steps A10 to A20:

[0096] Step A10: aggregate and connect the data tuples with the same equivalent element values ​​between different data blocks to be connected to obtain an intermediate structure table of aggregate links.

[0097] In this embodiment, data tuples with the same equivalent element values ​​in different data blocks to be connected are aggregated and connected, and the intermediate structure table of the aggregate link can be accurately obtained, thereby effectively integrating data from multiple data sources but with the same key attributes, providing strong data support for the subsequent generation of data aggregation tables.

[0098] For example, Figure 5 The data tuples with the same value of the element id column in the two tables are a form of expression for the data tuples with the same equivalent element value in different data blocks to be connected. Specifically, Figure 5 The data tuples set in the frequency statistics table of the data table S And the data tuple , and the data tuples set with the frequency statistics table of data table R Refers to data tuples with the same value in the element id column of the two tables.

[0099] Step A20: Determine the element operation rule corresponding to the aggregation connection function, and perform operation processing on the intermediate structure table according to the element operation rule to obtain a data aggregation table.

[0100] In this embodiment, the intermediate structure table is precisely processed according to the element operation rules corresponding to the aggregation join function, so that the required data aggregation table can be generated quickly and accurately, which not only effectively reduces the resource consumption of the connection aggregation query, but also significantly improves the efficiency of the connection aggregation query.

[0101] Further, in some other feasible embodiments, after the above step S20: determining the multiple original data tables connected by the target connection key, the aggregate query method may further include the following implementation steps B10 to B30:

[0102] Step B10: In each of the original data tables, detecting whether the processing amount of the data tuple exceeds a preset parallel processing threshold;

[0103] In this embodiment, it is evaluated whether the processing amount of the data tuples in each original data table exceeds the preset parallel processing threshold. Specifically, it is detected in each original data table whether the processing amount of the data tuples exceeds the preset parallel processing threshold, so that the allocation of computing resources can be optimized according to the detection result.

[0104] Step B20: If the processing amount does not exceed the parallel processing threshold, the step of performing statistics on each data tuple in the original data table based on a preset frequency column is performed.

[0105] In this embodiment, if the processing amount of the data tuples in each original data table does not exceed the preset parallel processing threshold, the step of performing statistics on the data tuples in each original data table based on the preset frequency column is executed. That is to say, for the original data table whose processing amount does not exceed the parallel processing threshold (i.e., the data table that does not exceed the limit), the data table that does not exceed the limit is directly statistically processed according to the preset frequency column, thereby maintaining the simplicity and efficiency of the original statistical processing flow and further improving the query efficiency of subsequent connection aggregation.

[0106] Step B30: If the processing volume exceeds the parallel processing threshold, the excess data table is divided according to the preset number of processing cores to obtain the same number of divided data tables as the number of processing cores, and statistics are synchronously performed on each of the divided data tables based on the frequency column to obtain a frequency statistics table of the excess data table, wherein the excess data table is the original data table whose processing volume exceeds the parallel processing threshold.

[0107] In this embodiment, if there is an original data table whose processing volume exceeds the parallel processing threshold (i.e., an over-limit data table), the over-limit data table is divided according to the preset number of processing cores to obtain a divided data table with the same number of processing cores, and statistics are synchronously performed on each divided data table according to the frequency column to obtain a frequency statistics table of the over-limit data table. That is, this application utilizes the parallel processing capability of the multi-core processor in the aggregate query device to significantly improve the statistical efficiency under large data volumes, while reducing the delay and waiting time when processing large-scale data sets, thereby optimizing the query performance and response speed of the connection aggregation.

[0108] For example, the aggregation join function Take this as an example to illustrate.

[0109] The data table R is shown in Table 1 below:

[0110]

[0111] Table 1

[0112] The data table S is shown in Table 3 below:

[0113]

[0114] Table 3

[0115] Furthermore, the degree of parallelism is set according to the number of available CPU cores (i.e., the number of processing cores of a multi-core processor). Here, it is assumed that the degree of parallelism is 2.

[0116] Furthermore, the data table R and the data table S are preprocessed, and each data block of the data table R and the data table S is constructed in parallel, including calculating the Z column value, the aggregate function value (aggregate column) of the element a column in each table, and grouping.

[0117] The data table S0 is shown in Table 4 below:

[0118]

[0119] Table 4

[0120] Data Table S1 is shown in Table 5 below:

[0121]

[0122] Table 5

[0123] The data table R0 is shown in Table 6 below:

[0124]

[0125] Table 6

[0126] Furthermore, the combined data table S is shown in Table 7 below:

[0127]

[0128] Table 7

[0129] Further, according to the aggregation connection function Perform data table R0 and merged data table S The connection operation of The table of connection operation settings is shown in Table 8 below:

[0130]

[0131] Table 8

[0132] Furthermore, based on algebraic operation rules, the result of the merged connection operation is shown in Table 9 below:

[0133]

[0134] Table 9

[0135] In summary, the post-join aggregation query (CAQ) algorithm set up in the present application is intended to reduce the computational overhead and memory overhead of CAQ queries. The traditional CAQ implementation method is usually to perform group aggregation (group aggregation) after data connection (join). This process will cause data to overflow (Spill) from high-performance storage devices to low-performance storage devices (for example, from registers to cache, from cache to memory, from memory to disk). This data overflow (spill) process will significantly increase the system load and reduce query performance. That is, the present application proposes an improved CAQ algorithm (i.e., aggregation query method) to optimize the process of connection and aggregation operations, reduce data expansion during the calculation process, reduce memory usage, further reduce the probability of data being spilled to low-speed storage, and improve computing performance. In general, the present application significantly reduces the computational and memory overhead, improves query efficiency, and reduces the system load by improving the existing CAQ algorithm, which is very suitable for large-scale data analysis and processing application scenarios.

[0136] In addition, this application also provides an aggregate query device, please refer to Figure 7 , Figure 7 Schematic diagram of the structure of the aggregate query device involved in the embodiment of the present application. The aggregate query device provided in the present application includes:

[0137] The response module H01 is used to respond to the aggregate query instruction and obtain the target connection key corresponding to the aggregate query requirement;

[0138] A statistical module H02 is used to determine multiple original data tables connected by the target connection key, and to perform statistics on the data tuples of each original data table based on a preset frequency column to obtain a frequency statistical table of each original data table, wherein the frequency statistical table at least includes data tuples carrying tuple cumulative values;

[0139] Aggregation module H03 is used to connect the frequency statistics tables according to the aggregation connection function specified by the aggregation query requirement to generate a data aggregation table.

[0140] Each functional module of the aggregate query device of the present application implements the steps of the aggregate query method of the present application as described above during operation.

[0141] In addition, this application also provides an aggregate query device. Figure 8 , Figure 8 The schematic diagram of the structure of the aggregate query device involved in the embodiment of the present application is shown in FIG. The device in the embodiment of the present application may be a device for locally running the aggregate query method.

[0142] The present application provides an aggregate query device, which includes: at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor, and the instructions are executed by the at least one processor so that the at least one processor can execute the aggregate query device method in the above-mentioned embodiment one.

[0143] Reference below Figure 8 , which shows a schematic diagram of the structure of an aggregate query device suitable for implementing an embodiment of the present application. The aggregate query device in the embodiment of the present application may include but is not limited to mobile terminals such as mobile phones, laptop computers, digital broadcast receivers, PDAs (Personal Digital Assistants), PADs (Portable Application Descriptions), PMPs (Portable Media Players), vehicle-mounted terminals (such as vehicle-mounted navigation terminals), etc., and fixed terminals such as digital TVs, desktop computers, etc. Figure 8 The aggregate query device shown is merely an example and should not bring any limitation to the functions and scope of use of the embodiments of the present application.

[0144] like Figure 8 As shown, the aggregate query device may include a processing device 1001 (e.g., a central processing unit, a graphics processor, etc.), which can perform various appropriate actions and processes according to a program stored in a read-only memory (ROM: Read Only Memory) 1002 or a program loaded from a storage device 1003 to a random access memory (RAM: Random Access Memory) 1004. In RAM1004, various programs and data required for the operation of the aggregate query device are also stored. The processing device 1001, ROM1002, and RAM1004 are connected to each other through a bus 1005. An input / output (I / O) interface 1006 is also connected to the bus. Generally, the following devices can be connected to the I / O interface 1006: an input device 1007 including, for example, a touch screen, a touch pad, a keyboard, a mouse, an image sensor, a microphone, an accelerometer, a gyroscope, etc.; an output device 1008 including, for example, a liquid crystal display (LCD: Liquid Crystal Display), a speaker, a vibrator, etc.; a storage device 1003 including, for example, a magnetic tape, a hard disk, etc.; and a communication device 1009. The communication device 1009 may allow the aggregate query device to communicate with other devices wirelessly or by wire to exchange data. Although the figure shows an aggregate query device with various devices, it should be understood that it is not required to implement or have all the devices shown. More or fewer devices may be implemented or have alternatively.

[0145] In particular, according to the embodiments disclosed in the present application, the process described above with reference to the flowchart can be implemented as a computer software program. For example, the embodiments disclosed in the present application include a computer program product, which includes a computer program carried on a computer-readable medium, and the computer program includes a program code for executing the method shown in the flowchart. In such an embodiment, the computer program can be downloaded and installed from a network through a communication device, or installed from a storage device 1003, or installed from a ROM 1002. When the computer program is executed by the processing device 1001, the above-mentioned functions defined in the method of the embodiment disclosed in the present application are executed.

[0146] The aggregate query device provided by the present application adopts the aggregate query device method in the above embodiment, which can solve the technical problem of low reliability of the aggregate query device. Compared with the prior art, the beneficial effects of the aggregate query device provided by the present application are the same as the beneficial effects of the aggregate query device method provided by the above embodiment, and other technical features in the aggregate query device are the same as the features disclosed in the method of the previous embodiment, which will not be repeated here.

[0147] In addition, the present application provides a computer-readable storage medium on which an aggregate query program is stored, and when the aggregate query program is executed by a processor, the steps of the above-mentioned aggregate query method are implemented.

[0148] It should be noted that, in this article, the terms "include", "comprises" or any other variations thereof are intended to cover non-exclusive inclusion, so that a process, method, article or system including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, article or system. In the absence of further restrictions, an element defined by the sentence "comprises a ..." does not exclude the existence of other identical elements in the process, method, article or system including the element.

[0149] The serial numbers of the above-mentioned embodiments of the present application are for description only and do not represent the advantages or disadvantages of the embodiments.

[0150] Through the description of the above implementation methods, those skilled in the art can clearly understand that the above-mentioned embodiment methods can be implemented by means of software plus a necessary general hardware platform, and of course by hardware, but in many cases the former is a better implementation method. Based on such an understanding, the technical solution of the present application, or the part that contributes to the prior art, can be embodied in the form of a software product, which is stored in a computer-readable storage medium (such as ROM / RAM, magnetic disk, optical disk) as described above, and includes a number of instructions for a device (which can be a mobile phone, a computer, a server, or a network device, etc.) to execute the methods described in each embodiment of the present application.

[0151] The above are only preferred embodiments of the present application, and are not intended to limit the patent scope of the present application. Any equivalent structure or equivalent process transformation made using the contents of the present application specification and drawings, or directly or indirectly applied in other related technical fields, are also included in the patent protection scope of the present application.

Claims

1. An aggregate query method, characterized in that: The aggregate query method comprises: In response to the aggregate query instruction, a target connection key corresponding to the aggregate query requirement is obtained, where the target connection key is used to indicate that multiple original data tables are to be connected; Determine multiple original data tables connected by the target connection key, and perform statistics on data tuples of each original data table based on a preset frequency column to obtain a frequency statistics table of each original data table, wherein the frequency statistics table at least includes data tuples carrying tuple cumulative values; Connect the frequency statistics tables according to the aggregate join function specified by the aggregate query requirement to generate a data aggregation table; The step of connecting the frequency statistics tables according to the aggregate join function specified by the aggregate query requirement to generate a data aggregate table includes: Determine an equivalent tuple identifier corresponding to the aggregate join function specified by the aggregate query requirement; Determine the data blocks to be connected of each of the frequency statistics tables according to the equivalent tuple identifiers, and perform aggregation connection on each of the data blocks to be connected according to the aggregation connection function to generate a data aggregation table; The step of determining the data blocks to be connected of each frequency statistics table according to the equivalent tuple identifier comprises: In response to the equivalent tuple identifier, obtaining the tuple to be connected corresponding to each of the frequency statistics tables; and searching for a data tuple matching the corresponding tuple to be connected from each of the frequency statistics tables; Constructing the data blocks to be connected of each frequency statistics table according to the matched data tuples in each frequency statistics table and the tuple cumulative values ​​carried by the matched data tuples; The step of performing aggregating connection on each of the data blocks to be connected according to the aggregate connection function to generate a data aggregation table comprises: Aggregate and connect the data tuples with the same equivalent element values ​​between different data blocks to be connected to obtain an intermediate structure table of aggregate links; Determine the element operation rule corresponding to the aggregation connection function, and perform operation processing on the intermediate structure table according to the element operation rule to obtain a data aggregation table.

2. The aggregate query method according to claim 1, characterized in that: The step of performing statistics on the data tuples of each original data table based on a preset frequency column to obtain a frequency statistics table of each original data table comprises: In each of the original data tables, a preset frequency column is set at the end of the data tuple, and each of the data tuples is counted according to the counting key of the frequency column to obtain a data tuple carrying a tuple cumulative value; The frequency statistics table is constructed based on the preset data structure and the data tuples carrying the tuple cumulative values.

3. The aggregate query method according to claim 2, characterized in that: The step of constructing the frequency statistics table according to the preset data structure and the data tuples carrying the tuple cumulative values ​​comprises: Determine an element column and an element count column corresponding to a preset data structure, and use the data tuple carrying the tuple cumulative value as a data structure tuple, where there are multiple data structure tuples; Each of the data structure tuples is sequentially arranged below the element column, and the tuple cumulative value carried by each arranged data structure tuple is sequentially set below the element count column arranged side by side with the element column to obtain the frequency statistics table.

4. The aggregate query method according to claim 1, characterized in that: After the step of determining the multiple original data tables connected by the target connection key, the aggregate query method includes: In each of the original data tables, detecting whether the processing amount of the data tuple exceeds a preset parallel processing threshold; If the processing amount does not exceed the parallel processing threshold, executing the step of performing statistics on each data tuple of the original data table based on a preset frequency column; If the processing volume exceeds the parallel processing threshold, the excess data table is divided according to the preset number of processing cores to obtain the same number of divided data tables as the number of processing cores, and statistics are synchronously performed on each of the divided data tables based on the frequency column to obtain a frequency statistics table of the excess data table, wherein the excess data table is the original data table whose processing volume exceeds the parallel processing threshold.

5. An aggregate query device, characterized in that: The aggregate query device comprises: A response module, used for obtaining a target connection key corresponding to the aggregate query requirement in response to the aggregate query instruction, wherein the target connection key is used for indicating that multiple original data tables are to be connected; A statistical module, used for determining a plurality of original data tables connected by the target connection key, and performing statistics on the data tuples of each of the original data tables based on a preset frequency column to obtain a frequency statistical table of each of the original data tables, wherein the frequency statistical table at least includes data tuples carrying tuple cumulative values; An aggregation module, used to connect the frequency statistics tables according to the aggregation connection function specified by the aggregation query requirement to generate a data aggregation table; The aggregation module is further used to determine the equivalent tuple identifier corresponding to the aggregation join function specified by the aggregation query requirement; determine the data block to be connected of each frequency statistics table according to the equivalent tuple identifier, and perform aggregation connection on each data block to be connected according to the aggregation join function to generate a data aggregation table; The aggregation module is also used to respond to the equivalent tuple identifier to obtain the tuple to be connected corresponding to each frequency statistical table; and search for the data tuple matching the corresponding tuple to be connected from each frequency statistical table; construct the data block to be connected of each frequency statistical table based on the matching data tuple in each frequency statistical table and the tuple cumulative value carried by the matching data tuple; the aggregation module is also used to aggregate and connect the data tuples with the same equivalent element values ​​between different data blocks to be connected to obtain an intermediate structure table of the aggregate link; determine the element operation rule corresponding to the aggregate connection function, and perform operation processing on the intermediate structure table according to the element operation rule to obtain a data aggregation table.

6. An aggregate query device, characterized in that: The aggregate query device includes a memory, a processor, and an aggregate query program stored in the memory and executable on the processor. When the processor executes the aggregate query program, the steps of the aggregate query method according to any one of claims 1 to 4 are implemented.

7. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores an aggregate query program, and when the aggregate query program is executed by a processor, the steps of the aggregate query method according to any one of claims 1 to 4 are implemented.