A group aggregation query method, device, system and medium

Abstract

The present invention discloses a grouping aggregation query method, device, system and medium, including: receiving a query statement of grouping aggregation and parsing to generate an initial execution plan of a tree structure, wherein the aggregation node in the initial execution plan is a single-point aggregation node; obtaining the distribution rule of a database table, and matching the distribution rule with the query statement by field; optimizing the initial execution plan according to different optimization strategies based on the field matching result, and generating a corresponding optimized execution plan; wherein the single-point aggregation node in different optimized execution plans is split into local aggregation nodes and data processing nodes with different operators; scheduling and executing according to the optimized execution plan, and obtaining a corresponding grouping query result. By first matching the distribution rule of the table with the query statement by field, and performing corresponding optimization processing on the initial execution plan based on different matching results, the optimization effect is maximized under different field matching conditions, and the query efficiency and performance are improved.

Description

Technical Field

[0001] The present invention relates to the field of database technology, and in particular to a group aggregation query method, device, system and medium. Background Art

[0002] A distributed database is a database system that can support concurrent access and data processing of different scales. SQL queries in distributed databases are generally divided into statement parsing, execution plan generation, execution plan optimization, execution plan segmentation, and scheduling execution. The execution plan is to represent the operations to be executed by the SQL statement in the executor in a tree. Each node in the tree represents the operator type of each operation, such as table scanning, filtering, table joining, projection, aggregation, etc.

[0003] Among them, grouped aggregate query is a database query operation that combines the two steps of grouping and aggregation to classify, summarize and calculate data. That is, the records in the data set are first grouped according to some common attributes or conditions, and then the data in each group is aggregated to obtain some statistical information. Usually, the amount of data that needs to be transmitted through the network for grouped aggregate query is very large, which makes the query response slow and the query efficiency low. Summary of the invention

[0004] In view of the above-mentioned deficiencies in the prior art, the purpose of the present invention is to provide a group aggregation query method, device, system and medium that can be applied to financial technology or other related fields, aiming to reduce the transmission data flow of group aggregation query and improve query efficiency and performance.

[0005] The technical solution of the present invention is as follows:

[0006] A grouping and aggregation query method, comprising:

[0007] Receive a query statement of group aggregation and parse it to generate an initial execution plan of a tree structure, wherein the aggregation node in the initial execution plan is a single-point aggregation node;

[0008] Obtaining a distribution rule of a database table, and performing field matching between the distribution rule and the query statement;

[0009] According to the field matching result, the initial execution plan is optimized according to different optimization strategies to generate corresponding optimized execution plans; wherein the single point aggregation node in different optimized execution plans is split into local aggregation nodes and data processing nodes with different operators;

[0010] The optimized execution plan is used for scheduling and execution to obtain corresponding group query results.

[0011] In one embodiment, the obtaining of the distribution rule of the database table and field matching of the distribution rule with the query statement includes:

[0012] Obtaining a distribution rule of a database table, and parsing the distribution rule to obtain a predefined sharding field or partition field;

[0013] Extracting the grouping field of the current grouping aggregation query from the query statement;

[0014] The sharding field or the partitioning field is field matched with the grouping field.

[0015] In one embodiment, optimizing the initial execution plan according to different optimization strategies based on the field matching results to generate a corresponding optimized execution plan includes:

[0016] When the fields match consistently, the initial execution plan is optimized according to the first optimization strategy to generate a first optimized execution plan;

[0017] When the field matches are inconsistent, the initial execution plan is optimized according to a second optimization strategy to generate a second optimized execution plan.

[0018] In one embodiment, when the fields match consistently, optimizing the initial execution plan according to a first optimization strategy to generate a first optimized execution plan specifically includes:

[0019] When the fields match consistently, the single-point aggregation node in the initial execution plan is split into local aggregation nodes, and a data stream merging node is generated after the local aggregation node as the first optimized execution plan.

[0020] In one embodiment, when the optimized execution plan is the first optimized execution plan, the scheduling execution according to the optimized execution plan to obtain the corresponding group query result includes:

[0021] Scheduling execution according to the first optimization execution plan, performing calculations at the local aggregation nodes through the local aggregation operator to obtain local aggregation intermediate results;

[0022] The local aggregation intermediate results are merged at the data stream merging node through the newly added data stream merging operator to obtain corresponding grouping query results.

[0023] In one embodiment, when the field matches are inconsistent, optimizing the initial execution plan according to the second optimization strategy to generate a second optimized execution plan specifically includes:

[0024] When the field matching is inconsistent, the single-point aggregation node in the initial execution plan is split into local aggregation nodes, and a final aggregation node is generated after the local aggregation node as the second optimized execution plan.

[0025] In one embodiment, when the optimized execution plan is the second optimized execution plan, the scheduling execution according to the optimized execution plan to obtain the corresponding group query result includes:

[0026] Scheduling execution according to the second optimization execution plan, performing calculations at the local aggregation nodes through the local aggregation operator to obtain local aggregation intermediate results;

[0027] Data exchange operations are performed through the data exchange operator to transfer all local aggregation intermediate results from each local aggregation node to a data node;

[0028] The local aggregation intermediate results in the data nodes are calculated by the final aggregation operator at the final aggregation node to obtain the corresponding group query results.

[0029] A grouping and aggregation query device, comprising:

[0030] A parsing module, used for receiving a query statement of group aggregation and parsing to generate an initial execution plan of a tree structure, wherein the aggregation node in the initial execution plan is a single-point aggregation node;

[0031] A field matching module is used to obtain the distribution rule of the database table and perform field matching between the distribution rule and the query statement;

[0032] An optimization module, used to optimize the initial execution plan according to different optimization strategies based on the field matching results, and generate a corresponding optimized execution plan; wherein the single point aggregation node in different optimized execution plans is split into local aggregation nodes and data processing nodes with different operators;

[0033] The execution module is used to schedule execution according to the optimized execution plan to obtain corresponding group query results.

[0034] A group aggregation query system, the system comprising at least one processor; and

[0035] a memory communicatively connected to the at least one processor; wherein,

[0036] The memory stores instructions that can be executed by the at least one processor, and the instructions are executed by the at least one processor to enable the at least one processor to execute the above-mentioned group aggregation query method.

[0037] A non-volatile computer-readable storage medium stores computer-executable instructions. When the computer-executable instructions are executed by one or more processors, the one or more processors can execute the above-mentioned group aggregation query method.

[0038] Beneficial effects: The present invention discloses a group aggregation query method, device, system and medium. Compared with the prior art, the embodiments of the present invention first match the distribution rules of the table with the query statement in the group aggregation query, and optimize the initial execution plan accordingly based on different matching results, so as to maximize the optimization effect under different field matching conditions, thereby improving the query efficiency and performance. BRIEF DESCRIPTION OF THE DRAWINGS

[0039] The present invention will be further described below with reference to the accompanying drawings and embodiments, in which:

[0040] Figure 1 A flow chart of a grouping and aggregation query method provided by an embodiment of the present invention;

[0041] Figure 2 A schematic diagram of the functional modules of a group aggregation query device provided by an embodiment of the present invention;

[0042] Figure 3 A schematic diagram of the hardware structure of the group aggregation query system provided in an embodiment of the present invention. DETAILED DESCRIPTION

[0043] In order to make the purpose, technical solution and effect of the present invention clearer and more specific, the present invention is further described in detail below. It should be understood that the specific embodiments described herein are only used to explain the present invention and are not intended to limit the present invention. The embodiments of the present invention are described below in conjunction with the accompanying drawings.

[0044] The present invention proposes a grouping and aggregation query method, such as Figure 1 As shown, the group aggregation query method provided in this embodiment includes the following steps:

[0045] S101, receiving a query statement of group aggregation and parsing it to generate an initial execution plan of a tree structure, wherein the aggregation node in the initial execution plan is a single-point aggregation node.

[0046] In this embodiment, when the database system receives a SQL query statement for grouping and aggregation, which requires grouping and aggregation operations on data (such as grouping by a certain field and calculating the sum or average of each group), the query statement is first parsed, including understanding the intent of the SQL statement, determining the table and field, checking permissions, etc. After the parsing is completed, an initial execution plan with a tree structure is generated. This execution plan describes in detail how to execute the query, including the specific steps of data retrieval, processing, and return. Each node in the execution plan represents the operator type of each operation, such as table scanning, filtering, table joining, projection, aggregation, etc.

[0047] Before the execution plan is optimized, the aggregation type of group aggregation statements is single-point aggregation, that is, in the initial execution plan, all aggregation operations are regarded as single-point aggregation, regardless of whether the data is actually distributed on multiple physical nodes, all data aggregation operations are assumed to be completed on a single node. This converts the user's query intent into an internal representation that the database can understand and execute, laying the foundation for subsequent optimization and execution.

[0048] S102: Obtain distribution rules of a database table, and perform field matching between the distribution rules and the query statement.

[0049] In this embodiment, in a distributed database, after data is imported into the system, the same field value may be dispersed on different servers according to different distribution rules, or even on the same server, the same field may be distributed in different shards and partitions due to the existence of data sharding and partitioning. Therefore, the distribution rule of the current table is obtained from the cache or other storage table structure, which defines how the data is distributed on different servers or shards, and the data distribution rule is matched with the received query statement to determine whether the specified fields are consistent, thereby identifying the relationship between the physical distribution of the data and the logical requirements of the query, and providing key information for subsequent execution plan optimization to minimize the amount of data transmission and save system resources.

[0050] S103. Optimize the initial execution plan according to different optimization strategies based on the field matching results to generate corresponding optimized execution plans; wherein the single-point aggregation node in different optimized execution plans is split into local aggregation nodes and data processing nodes with different operators.

[0051] In this embodiment, different optimization strategies are adopted according to the results of field matching to adapt to the optimization requirements of different query situations. The optimized execution plan will split the original single-point aggregation node into local aggregation nodes and possible data processing nodes. These nodes will perform specific data processing tasks according to different optimization strategies. That is, in the execution plan optimization stage, the optimization methods adopted under different query conditions and the optimization results obtained are different. By adaptively optimizing and adjusting the execution plan, local aggregation nodes and corresponding data processing nodes are introduced, so that the database can process group aggregation queries more efficiently, minimize unnecessary data transmission and calculation according to the actual query situation, and thus improve the overall query performance.

[0052] S104: Perform scheduling execution according to the optimized execution plan to obtain corresponding group query results.

[0053] In this embodiment, after the optimized execution plan is generated, the query operation is scheduled and executed according to the optimized execution plan. That is, the query is executed and computing resources are allocated according to the instructions in the optimized execution plan, the corresponding local aggregation and operations of different data processing nodes are performed, and finally the result of the grouped query is obtained. By first matching the distribution rules of the table with the query statement in the grouped aggregation query, and performing corresponding optimization processing on the initial execution plan based on different matching results, the optimization effect is improved as much as possible, so that the query results can be returned quickly and efficiently under different field matching conditions, thereby improving the query efficiency and performance.

[0054] In one embodiment, step S102 includes:

[0055] Obtaining a distribution rule of a database table, and parsing the distribution rule to obtain a predefined sharding field or partition field;

[0056] Extracting the grouping field of the current grouping aggregation query from the query statement;

[0057] The sharding field or the partitioning field is field matched with the grouping field.

[0058] In this embodiment, the distribution rules of the predefined current table are obtained from the cache or other storage table structure. The distribution rules define how the data is distributed in multiple nodes or partitions of the database, specifically how the data is sharded and partitioned, wherein the sharding of data usually involves the dispersion of data across multiple servers, and the partitioning of data may logically divide the data within the same server, that is, the distribution rules may include sharding rules and partitioning rules, thereby determining the data storage location. By parsing the obtained distribution rules, the predefined sharding fields or partitioning fields are parsed and extracted, thereby accurately identifying the physical storage mode of the data in the database.

[0059] After that, the currently submitted SQL query statement is analyzed, and the grouping fields of the current grouping and aggregation query are extracted from the query statement. These fields are the basis for the user to group the data, and the grouping fields are usually the fields involved in the GROUP BY clause. For example, if the query executed by the user is SELECT sum(salary) FROM employees GROUP BY department_id, then department_id is the grouping field. By extracting the grouping fields, we can identify the fields that we currently want to organize and aggregate data according to, which serves as a reliable basis for query optimization.

[0060] Match the parsed sharding fields or partitioning fields with the grouping fields to confirm whether these fields are the same or related, so as to determine whether the physical distribution of the data matches the logical requirements of the query. If the sharding fields or partitioning fields are consistent with the grouping fields, it means that the data has been physically organized in the way required by the query, so a specific optimization strategy different from the general optimization strategy can be adopted to maximize the query optimization effect.

[0061] In one embodiment, step S103 includes:

[0062] When the fields match consistently, the initial execution plan is optimized according to the first optimization strategy to generate a first optimized execution plan;

[0063] When the field matches are inconsistent, the initial execution plan is optimized according to a second optimization strategy to generate a second optimized execution plan.

[0064] In this embodiment, if it is confirmed that the sharding field or partitioning field of the database table matches the grouping field in the query statement, the first optimization strategy is used to optimize the initial execution plan. Since the data is already distributed in different shards or partitions according to the grouping field in this case, the first optimization strategy can use this data distribution feature to reduce the amount of data transmission between different nodes and the amount of calculation of the aggregation operation. For example, if a query is grouped and aggregated by department_id, and the sharding rule of the table is also based on department_id, then the data on each shard is already grouped by department_id, so the aggregation operation can be completed independently on each shard.

[0065] If it is confirmed that the sharding field or partition field of the database table does not match the grouping field in the query statement, the second optimization strategy is used to optimize the initial execution plan. Since the physical distribution of the data in this case does not match the logical requirements of the query, the query needs to be processed according to the conventional optimization strategy. For example, perform aggregation operations on multiple nodes, and then transmit the results through the network to one node for the final aggregation calculation.

[0066] This embodiment determines whether the query can be optimized by using the physical distribution of the data through the field matching result. If the match is successful, a specific optimization strategy different from the conventional optimization strategy can be adopted to reduce the movement and reorganization of data, thereby improving the query efficiency. If the match is not successful, the conventional optimization strategy is adopted to process the query to ensure that the performance is not affected.

[0067] In one embodiment, when the fields match consistently, optimizing the initial execution plan according to a first optimization strategy to generate a first optimized execution plan specifically includes:

[0068] When the fields match consistently, the single-point aggregation node in the initial execution plan is split into local aggregation nodes, and a data stream merging node is generated after the local aggregation node as the first optimized execution plan.

[0069] In this embodiment, when there are multiple shards in a table and no partitions, if it is determined that the shard field matches the grouping field, the single-point aggregation node in the initial execution plan is split into local aggregation nodes. The local aggregation node can independently perform aggregation operations on each shard or partition where the data is located, instead of concentrating all data at one point for aggregation, thereby reducing the transmission requirements of data between different shards or partitions. And a data stream merging node is generated after the local aggregation node, and the data stream merging node can directly merge the results of each local aggregation to form a global aggregation result and generate a first optimized execution plan. Since the data stream merging node only needs to perform a simple merging operation without recalculating the aggregation function, the query response speed and overall system performance can be significantly improved. Similarly, when there is one shard in a table but multiple partitions, if it is determined that the partition field matches the grouping field, the corresponding first optimized execution plan is generated according to the same optimization process, and the local aggregation and data stream merging nodes are used to reduce the data transmission and calculation amount as much as possible, thereby improving the query performance.

[0070] In one embodiment, when the optimization execution plan is a first optimization execution plan, step S104 includes:

[0071] Scheduling execution according to the first optimization execution plan, performing calculations at the local aggregation nodes through the local aggregation operator to obtain local aggregation intermediate results;

[0072] The local aggregation intermediate results are merged at the data stream merging node through the newly added data stream merging operator to obtain corresponding grouping query results.

[0073] In this embodiment, when the shard partition field matches the group field, the query operation is started based on the first optimized execution plan generated when the field matches. The local aggregation operator works on each local aggregation node, which corresponds to each shard or partition in the database, and performs aggregation operations on the data in each shard or partition, such as calculating the sum, average, maximum or minimum value, etc. The local aggregation process is carried out in parallel, and each shard or partition completes its aggregation task independently to generate the intermediate results of the local aggregation, thereby improving the parallelism and efficiency of data processing.

[0074] At the same time, a new data stream merge operator MergeOperator is created to process the data stream merge node. After the local aggregation is completed, since the local aggregation on each shard or partition is actually processing all the data of the grouping field, the result of the local aggregation is the global aggregation result of the grouping field value. The newly added data stream merge operator can directly merge the local aggregation intermediate results at the data stream merge node to obtain the final group query result, without the need to calculate the aggregation function after the merger, thereby optimizing the query by using the physical distribution of the data, saving the calculation of a large amount of intermediate data, and thus improving the query performance and response speed.

[0075] In one embodiment, when the field matches are inconsistent, optimizing the initial execution plan according to the second optimization strategy to generate a second optimized execution plan specifically includes:

[0076] When the field matching is inconsistent, the single-point aggregation node in the initial execution plan is split into local aggregation nodes, and a final aggregation node is generated after the local aggregation node as the second optimized execution plan.

[0077] In this embodiment, when the shard field or partition field does not match the grouping field, the initial execution plan is processed by a conventional optimization method, and the single-point aggregation node in the initial execution plan is split into a local aggregation node and a final aggregation node to generate a corresponding second optimized execution plan. The local aggregation node is responsible for independently performing preliminary aggregation operations on each shard or partition where the data is located, and the final aggregation node calculates the aggregation function again on the merged local aggregation results, thereby processing the merging of results from different shards or partitions. It ensures that even if the data distribution does not match the query requirements, the query results can be correctly calculated, and local aggregation is used to reduce network transmission and calculation, thereby improving query performance while ensuring the accuracy of the results.

[0078] In one embodiment, when the optimized execution plan is the second optimized execution plan, step S104 includes:

[0079] Scheduling execution according to the second optimization execution plan, performing calculations at the local aggregation nodes through the local aggregation operator to obtain local aggregation intermediate results;

[0080] Data exchange operations are performed through the data exchange operator to transfer all local aggregation intermediate results from each local aggregation node to a data node;

[0081] The local aggregation intermediate results in the data nodes are calculated by the final aggregation operator at the final aggregation node to obtain the corresponding group query results.

[0082] In this embodiment, when the shard partition field does not match the group field, the query operation is started based on the second optimized execution plan generated when the field matches are inconsistent. Similarly, the local aggregation operator works on each local aggregation node, which corresponds to each shard or partition in the database, and performs aggregation operations on the data in each shard or partition, such as calculating the sum, average, maximum or minimum value, etc. The local aggregation process is carried out in parallel, and each shard or partition completes its aggregation task independently to generate the intermediate results of the local aggregation.

[0083] After the local aggregation is completed, the data exchange operator is used to transfer the local aggregation intermediate results of all shards or partitions to a central data node for the final aggregation calculation. Specifically, the ExchangeOperator (data exchange operator) can read the data written to the output buffer in the previous stage through the network and transmit it to the central data node, so as to aggregate the same grouping fields (group key) together in preparation for the final aggregation calculation. Although the data exchange operation involves the network transmission of data, the amount of data that needs to be transmitted has been reduced through local aggregation, so the transmission cost and computing efficiency can be balanced.

[0084] After all local aggregate intermediate results are transmitted to the central data node, the final aggregation operator calculates the aggregation function on the local aggregate intermediate results in the central data node on the final aggregation node, thereby generating a global grouped aggregation query result, ensuring that the query result can be correctly calculated even when the data distribution does not match the query requirements.

[0085] It should be noted that there is not necessarily a certain order between the above-mentioned steps. A person skilled in the art can understand, based on the description of the embodiments of the present invention, that in different embodiments, the above-mentioned steps may have different execution orders, that is, they may be executed in parallel, may be executed interchangeably, and so on.

[0086] Another embodiment of the present invention provides a group aggregation query device, such as Figure 2 As shown, the device 1 comprises:

[0087] A grouping and aggregation query device, comprising:

[0088] The parsing module 11 is used to receive the query statement of group aggregation and parse and generate an initial execution plan of tree structure, wherein the aggregation node in the initial execution plan is a single point aggregation node;

[0089] A field matching module 12 is used to obtain the distribution rule of the database table and perform field matching between the distribution rule and the query statement;

[0090] The optimization module 13 is used to optimize the initial execution plan according to different optimization strategies based on the field matching results, and generate a corresponding optimized execution plan; wherein the single point aggregation node in different optimized execution plans is split into local aggregation nodes and data processing nodes with different operators;

[0091] The execution module 14 is used to schedule execution according to the optimized execution plan to obtain corresponding group query results.

[0092] The module referred to in the present invention refers to a series of computer program instruction segments that can perform specific functions, which are more suitable for describing the execution process of group aggregation queries than programs. For the specific implementation methods of each module, please refer to the corresponding method embodiments above, which will not be repeated here.

[0093] Another embodiment of the present invention provides a group aggregation query system, such as Figure 3 As shown, the system 10 includes:

[0094] One or more processors 110 and memory 120, Figure 3 A processor 110 is used as an example for description. The processor 110 and the memory 120 may be connected via a bus or other means. Figure 3 The example of connecting through bus is taken in the following.

[0095] The processor 110 is used to complete various control logics of the system 10, and it can be a general-purpose processor, a digital signal processor (DSP), an application-specific integrated circuit (ASIC), a field programmable gate array (FPGA), a single-chip microcomputer, an ARM (Acorn RISC Machine) or other programmable logic device, a discrete gate or transistor logic, a discrete hardware component, or any combination of these components. In addition, the processor 110 can also be any traditional processor, microprocessor or state machine. The processor 110 can also be implemented as a combination of computing devices, for example, a combination of a DSP and a microprocessor, a plurality of microprocessors, one or more microprocessors combined with a DSP and / or any other such configuration.

[0096] The memory 120, as a non-volatile computer-readable storage medium, can be used to store non-volatile software programs, non-volatile computer executable programs and modules, such as program instructions corresponding to the group aggregation query method in the embodiment of the present invention. The processor 110 executes various functional applications and data processing of the system 10 by running the non-volatile software programs, instructions and units stored in the memory 120, that is, implements the group aggregation query method in the above method embodiment.

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

[0098] One or more units are stored in the memory 120, and when executed by one or more processors 110, the following steps are implemented:

[0099] Receive a query statement of group aggregation and parse it to generate an initial execution plan of a tree structure, wherein the aggregation node in the initial execution plan is a single-point aggregation node;

[0100] Obtaining a distribution rule of a database table, and performing field matching between the distribution rule and the query statement;

[0101] According to the field matching result, the initial execution plan is optimized according to different optimization strategies to generate corresponding optimized execution plans; wherein the single point aggregation node in different optimized execution plans is split into local aggregation nodes and data processing nodes with different operators;

[0102] The optimized execution plan is used for scheduling and execution to obtain corresponding group query results.

[0103] In one embodiment, the obtaining of the distribution rule of the database table and field matching of the distribution rule with the query statement includes:

[0104] Obtaining a distribution rule of a database table, and parsing the distribution rule to obtain a predefined sharding field or partition field;

[0105] Extracting the grouping field of the current grouping aggregation query from the query statement;

[0106] The sharding field or the partitioning field is field matched with the grouping field.

[0107] In one embodiment, optimizing the initial execution plan according to different optimization strategies based on the field matching results to generate a corresponding optimized execution plan includes:

[0108] When the fields match consistently, the initial execution plan is optimized according to the first optimization strategy to generate a first optimized execution plan;

[0109] When the field matches are inconsistent, the initial execution plan is optimized according to a second optimization strategy to generate a second optimized execution plan.

[0110] In one embodiment, when the fields match consistently, optimizing the initial execution plan according to a first optimization strategy to generate a first optimized execution plan specifically includes:

[0111] When the fields match consistently, the single-point aggregation node in the initial execution plan is split into local aggregation nodes, and a data stream merging node is generated after the local aggregation node as the first optimized execution plan.

[0112] In one embodiment, when the optimized execution plan is the first optimized execution plan, the scheduling execution according to the optimized execution plan to obtain the corresponding group query result includes:

[0113] Scheduling execution according to the first optimization execution plan, performing calculations at the local aggregation nodes through the local aggregation operator to obtain local aggregation intermediate results;

[0114] The local aggregation intermediate results are merged at the data stream merging node through the newly added data stream merging operator to obtain corresponding grouping query results.

[0115] In one embodiment, when the field matches are inconsistent, optimizing the initial execution plan according to the second optimization strategy to generate a second optimized execution plan specifically includes:

[0116] When the field matching is inconsistent, the single-point aggregation node in the initial execution plan is split into local aggregation nodes, and a final aggregation node is generated after the local aggregation node as the second optimized execution plan.

[0117] In one embodiment, when the optimized execution plan is the second optimized execution plan, the scheduling execution according to the optimized execution plan to obtain the corresponding group query result includes:

[0118] Scheduling execution according to the second optimization execution plan, performing calculations at the local aggregation nodes through the local aggregation operator to obtain local aggregation intermediate results;

[0119] Data exchange operations are performed through the data exchange operator to transfer all local aggregation intermediate results from each local aggregation node to a data node;

[0120] The local aggregation intermediate results in the data nodes are calculated by the final aggregation operator at the final aggregation node to obtain the corresponding group query results.

[0121] An embodiment of the present invention provides a non-volatile computer-readable storage medium, wherein the computer-readable storage medium stores computer-executable instructions, and the computer-executable instructions are executed by one or more processors, for example, to execute the above-described Figure 1 The method comprises steps S101 to S104.

[0122] As an example, non-volatile storage media can include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable ROM (EEPROM), or flash memory. Volatile memory can include random access memory (RAM) as external cache memory. By way of illustration and not limitation, RAM can be obtained in many forms such as synchronous RAM (SRAM), dynamic RAM, (DRAM), synchronous DRAM (SDRAM), double data rate SDRAM (DDRSDRAM), enhanced SDRAM (ESDRAM), Synchlink DRAM (SLDRAM), and direct Rambus RAM (DRRAM). The disclosed memory components or memories of the operating environment described herein are intended to include one or more of these and / or any other suitable types of memory.

[0123] In summary, the present invention discloses a group aggregation query method, device, system and medium. The method receives a query statement of group aggregation and parses it to generate an initial execution plan of a tree structure, wherein the aggregation node in the initial execution plan is a single-point aggregation node; obtains the distribution rule of the database table, and matches the distribution rule with the query statement by field; optimizes the initial execution plan according to different optimization strategies based on the field matching result, and generates a corresponding optimized execution plan; wherein the single-point aggregation node in different optimized execution plans is split into local aggregation nodes and data processing nodes with different operators; and schedules and executes according to the optimized execution plan to obtain the corresponding group query result. By first matching the distribution rule of the table with the query statement by field during the group aggregation query, and performing corresponding optimization processing on the initial execution plan based on different matching results, the optimization effect can be maximized under different field matching conditions, and the query efficiency and performance can be improved.

[0124] Of course, those skilled in the art can understand that all or part of the processes in the above-mentioned embodiments can be implemented by instructing related hardware (such as a processor, a controller, etc.) through a computer program, and the computer program can be stored in a non-volatile computer-readable storage medium, and the computer program can include the processes of the above-mentioned method embodiments when executed. The storage medium can be a memory, a disk, a floppy disk, a flash memory, an optical storage device, etc.

[0125] It should be understood that the application of the present invention is not limited to the above examples. For ordinary technicians in this field, improvements or changes can be made based on the above description. All these improvements and changes should fall within the scope of protection of the claims attached to the present invention.

Claims

1. A grouping and aggregation query method, characterized in that: include: Receive a query statement of group aggregation and parse it to generate an initial execution plan of a tree structure, wherein the aggregation node in the initial execution plan is a single-point aggregation node; Obtaining a distribution rule of a database table, and performing field matching between the distribution rule and the query statement; According to the field matching result, the initial execution plan is optimized according to different optimization strategies to generate corresponding optimized execution plans; wherein the single point aggregation node in different optimized execution plans is split into local aggregation nodes and data processing nodes with different operators; The optimized execution plan is used for scheduling and execution to obtain corresponding group query results.

2. The grouping and aggregation query method according to claim 1, characterized in that: The obtaining of the distribution rule of the database table and field matching of the distribution rule with the query statement includes: Obtaining a distribution rule of a database table, and parsing the distribution rule to obtain a predefined sharding field or partition field; Extracting the grouping field of the current grouping aggregation query from the query statement; The sharding field or the partitioning field is field matched with the grouping field.

3. The grouping and aggregation query method according to claim 1, characterized in that: The step of optimizing the initial execution plan according to different optimization strategies based on the field matching results to generate a corresponding optimized execution plan includes: When the fields match consistently, the initial execution plan is optimized according to the first optimization strategy to generate a first optimized execution plan; When the field matches are inconsistent, the initial execution plan is optimized according to a second optimization strategy to generate a second optimized execution plan.

4. The grouping aggregation query method according to claim 3 is characterized in that: When the fields match consistently, optimizing the initial execution plan according to the first optimization strategy to generate a first optimized execution plan specifically includes: When the fields match consistently, the single-point aggregation node in the initial execution plan is split into local aggregation nodes, and a data stream merging node is generated after the local aggregation node as the first optimized execution plan.

5. The grouping aggregation query method according to claim 4, characterized in that: When the optimized execution plan is the first optimized execution plan, the scheduling execution is performed according to the optimized execution plan to obtain the corresponding group query result, including: Scheduling execution according to the first optimization execution plan, performing calculations at the local aggregation nodes through the local aggregation operator to obtain local aggregation intermediate results; The local aggregation intermediate results are merged at the data stream merging node through the newly added data stream merging operator to obtain corresponding grouping query results.

6. The grouping aggregation query method according to claim 3, characterized in that: When the field matches are inconsistent, optimizing the initial execution plan according to the second optimization strategy to generate a second optimized execution plan specifically includes: When the field matching is inconsistent, the single-point aggregation node in the initial execution plan is split into local aggregation nodes, and a final aggregation node is generated after the local aggregation node as the second optimized execution plan.

7. The grouping aggregation query method according to claim 6, characterized in that: When the optimized execution plan is the second optimized execution plan, the scheduling execution is performed according to the optimized execution plan to obtain the corresponding group query result, including: The second optimization execution plan is scheduled for execution, and a local aggregation operator is used to perform calculations at the local aggregation node to obtain a local aggregation intermediate result. The data exchange operator performs data exchange operations to transfer all local aggregation intermediate results from each local aggregation node to a data node; The local aggregation intermediate results in the data nodes are calculated by the final aggregation operator at the final aggregation node to obtain the corresponding group query results.

8. A grouping aggregate query device, characterized in that: include: A parsing module, used for receiving a query statement of group aggregation and parsing to generate an initial execution plan of a tree structure, wherein the aggregation node in the initial execution plan is a single-point aggregation node; A field matching module is used to obtain the distribution rule of the database table and perform field matching between the distribution rule and the query statement; An optimization module, used to optimize the initial execution plan according to different optimization strategies based on the field matching results, and generate a corresponding optimized execution plan; wherein the single point aggregation node in different optimized execution plans is split into local aggregation nodes and data processing nodes with different operators; The execution module is used to schedule execution according to the optimized execution plan to obtain corresponding group query results.

9. A grouping and aggregation query system, characterized in that: The system includes at least one processor; and, a memory communicatively connected to the at least one processor; wherein, The memory stores instructions that can be executed 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 group aggregation query method described in any one of claims 1-7.

10. A non-volatile computer-readable storage medium, characterized in that: The non-volatile computer-readable storage medium stores computer-executable instructions, which, when executed by one or more processors, enable the one or more processors to execute the group aggregation query method described in any one of claims 1-7.

Images