Hash aggregation method and device based on limit statement in openGuass

Abstract

The present application relates to the technical field of database, provide a kind of Hash aggregation method and device based on Limit statement in openGuass, the method of the present application, comprising: according to query SQL statement generation Limit operator and HashAgg operator, wherein, Limit operator includes limit value, HashAgg operator includes aggregation function and grouping field information;Limit value in Limit operator is passed and saved to HashAgg operator;Execute query SQL, calculate the hash value of grouping field in data line, and construct HashBucket hash bucket according to the non-repeated number of occurrence of the hash value of grouping field and limit value;The aggregated field in the data of the same grouping field with the same hash value is placed in the same HashBucket hash bucket corresponding;After data scanning is completed, the aggregated field in each HashBucket hash bucket is aggregated by aggregation function, and the aggregation result is output to client.The present application can reduce the grouping and aggregation time of data in database and resource consumption as a whole.

Description

Technical Field

[0001] This invention relates to the field of database technology, and in particular to a hash aggregation method and apparatus based on the Limit statement in openGuass. Background Technology

[0002] Aggregate functions are statistical functions widely used in various database systems such as OLAP and OLTP databases for various analytical calculation scenarios. Commonly used aggregate functions include SUM, AVG, COUNT, MIN, and MAX. The execution time and resource consumption of aggregate functions are important metrics for database performance.

[0003] In practical applications, aggregate functions typically require grouping data based on fields before aggregation. Furthermore, since the aggregation output may generate a large amount of result set information, it is often necessary to add a limit clause to restrict the result set.

[0004] Because modern database models are generally iterator models, operators only exchange intermediate results. This leads to some operators performing tasks that other operators don't need. Particularly in certain database implementations, grouping and aggregation are typically implemented using the HashAgg operator, and Limit clauses are implemented using the Limit operator. The HashAgg operator hands over the generated data to the Limit operator for processing, but it doesn't know the specific limit value of the Limit operator. This causes the HashAgg operator to process some data that the Limit operator doesn't need, resulting in longer execution time and increased resource consumption.

[0005] Therefore, how to provide a more efficient method for data grouping and aggregation has become an urgent technical problem to be solved. Summary of the Invention

[0006] In view of this, in order to overcome the shortcomings of the prior art, the present invention provides a hash aggregation method and apparatus based on Limit statement in openGuass. By passing a limit value between the HashAgg operator and the Limit operator, the HashAgg operator can conditionally perform data aggregation operation according to the limit value, which can reduce execution time and reduce resource consumption.

[0007] On one hand, this invention provides a hash aggregation method based on Limit statements in openGuass, comprising:

[0008] Step S1: Generate Limit and HashAgg operators based on the query SQL statement. The Limit operator includes a limit value, and the HashAgg operator includes an aggregation function and grouping field information.

[0009] Step S2: Pass and save the limit value in the Limit operator to the HashAgg operator;

[0010] Step S3: Execute the query SQL, calculate the hash value of the grouping field in the data row, and construct the HashBucket based on the number of non-repeating occurrences and the limit value of the hash value of the grouping field;

[0011] Step S4: Place the aggregated fields of data with the same hash value grouping field into the corresponding HashBucket hash bucket;

[0012] Step S5: After the data scan is completed, aggregate calculations are performed on the aggregate fields in each HashBucket using aggregate functions, and the aggregate results are output to the client.

[0013] Furthermore, in step S3 of the hash aggregation method based on the Limit statement in openGuass of the present invention, calculating the hash value of the grouping field in the data row includes: scanning the data row through the HashAgg operator and using a hash function to calculate the hash value of the grouping field in each row of data.

[0014] Furthermore, in step S3 of the hash aggregation method based on the Limit statement in the openGuass of this invention, constructing a HashBucket based on the non-repeating number of times the hash value of the grouping field appears and the limit value includes:

[0015] If the number of non-repeating occurrences of the hash value of the grouping field is less than or equal to the limit, construct a number of HashBuckets equal to the number of non-repeating occurrences of the hash value of the grouping field.

[0016] If the number of non-repeating occurrences of the hash value of the grouping field exceeds a certain limit, construct a number of HashBuckets equal to the limit.

[0017] Furthermore, step S4 of the hash aggregation method based on the Limit statement in openGuass of the present invention further includes: discarding the data when data with the same hash value grouping field does not have a corresponding HashBucket.

[0018] On the other hand, the present invention provides a hash aggregation device based on the Limit statement in openGuass, comprising:

[0019] The planner module is used to generate Limit and HashAgg operators based on the query SQL statement. The Limit operator contains the limit value, and the HashAgg operator contains the aggregation function and grouping field information.

[0020] The limit value passing module is used to pass the limit value in the Limit operator and save it to the HashAgg operator;

[0021] The hash bucket module is used to execute query SQL, calculate the hash value of the grouping field in the data row, and construct a HashBucket based on the number of non-repeating occurrences and a limit value of the hash value of the grouping field; aggregate fields in data with grouping fields that have the same hash value are put into the corresponding HashBucket.

[0022] The data aggregation module is used to perform aggregation calculations on the aggregation fields in each HashBucket after the data scan is completed, and output the aggregation results to the client.

[0023] Furthermore, the hash bucket module in the hash aggregation device based on the Limit statement in openGuass of this invention is used to: scan data rows by the HashAgg operator and calculate the hash value of the grouping field in each row of data using a hash function.

[0024] Furthermore, the hash bucket module in the hash aggregation device based on the Limit statement in openGuass of this invention is used for:

[0025] If the number of non-repeating occurrences of the hash value of the grouping field is less than or equal to the limit, construct a number of HashBuckets equal to the number of non-repeating occurrences of the hash value of the grouping field.

[0026] If the number of non-repeating occurrences of the hash value of the grouping field exceeds a certain limit, construct a number of HashBuckets equal to the limit.

[0027] Furthermore, the step hash bucket module in the hash aggregation device based on the Limit statement in openGuass of the present invention is used to: discard the data when data with the same hash value grouping field does not have a corresponding HashBucket.

[0028] The present invention also provides a computer-readable storage medium having a computer program stored thereon, wherein when the computer program is executed, it performs the steps of the hash aggregation method based on the Limit statement in openGuass described above.

[0029] Finally, the present invention provides a computer device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the program to implement the steps of the hash aggregation method based on the Limit statement in openGuass described above.

[0030] The hash aggregation method and apparatus based on the Limit statement in openGuass of this invention have the following beneficial effects:

[0031] 1. By associating the HashAgg operator and the Limit operator, the number of HashBuckets to be built in the HashAgg operator is limited according to the limit value, thereby reducing memory usage;

[0032] 2. By reducing the number of HashBuckets, the amount of invalid data participating in subsequent aggregation calculations is reduced, thereby improving CPU utilization;

[0033] 3. Since the Limit operator has already passed the limit value to the HashAgg operator in advance, the data generated by the HashAgg operator will definitely meet the requirements of the Limit operator. Therefore, the Limit operator hardly needs to do any work, which greatly reduces resource consumption.

[0034] 4. In summary, this can reduce the overall time and resource consumption for data grouping and aggregation in the database. Attached Figure Description

[0035] To more clearly illustrate the technical solutions of the embodiments of the present invention, the drawings used in the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0036] Figure 1 This is a flowchart of the hash aggregation method based on the Limit statement in openGuass, which is an exemplary first embodiment of the present invention.

[0037] Figure 2 This is an architectural diagram of the hash aggregation device based on the Limit statement in openGuass, an exemplary third embodiment of the present invention.

[0038] Figure 3 This is a schematic diagram of the element composition of a query SQL statement in an exemplary fourth embodiment of the present invention.

[0039] Figure 4 This is a schematic diagram illustrating the decomposition of the query SQL into Limit and HashAgg operators in an exemplary fourth embodiment of the present invention.

[0040] Figure 5 This is a schematic diagram illustrating how the limit value N in the Limit operator is transferred and saved to the HashAgg operator in an exemplary fourth embodiment of the present invention.

[0041] Figure 6This is a schematic diagram illustrating the data structure in an exemplary fourth embodiment of the present invention.

[0042] Figure 7 This is a schematic diagram illustrating the calculation of the hash value V of grouping fields D and E using a hash function in an exemplary fourth embodiment of the present invention.

[0043] Figure 8 This is a schematic diagram of the HashBucket constructed in the exemplary fourth embodiment of the present invention.

[0044] Figure 9 This is a schematic diagram illustrating how aggregate fields A, B, and C in grouping fields with the same hash value V are placed into the same corresponding HashBucket in the exemplary fourth embodiment of the present invention.

[0045] Figure 10 This is a schematic diagram comparing the number of Harbin systems before and after optimization in an exemplary fourth embodiment of the present invention.

[0046] Figure 11 This is a schematic diagram illustrating the discarding of data that does not have a corresponding hash bucket in the fourth exemplary embodiment of the present invention.

[0047] Figure 12 This is a schematic diagram illustrating how the aggregation functions are used to perform aggregation calculations on the aggregation fields A, B, and C in each HashBucket in the exemplary fourth embodiment of the present invention, and how the aggregation results are output to the client. Detailed Implementation

[0048] The embodiments of the present invention will now be described in detail with reference to the accompanying drawings.

[0049] It should be noted that, in the absence of conflict, the following embodiments and features can be combined with each other; and, based on the embodiments of this disclosure, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this disclosure.

[0050] It should be noted that various aspects of embodiments within the scope of the appended claims are described below. It will be apparent that the aspects described herein can be embodied in a wide variety of forms, and any particular structure and / or function described herein is merely illustrative. Based on this disclosure, those skilled in the art will understand that one aspect described herein can be implemented independently of any other aspect, and two or more of these aspects can be combined in various ways. For example, any number of aspects set forth herein can be used to implement the device and / or practice the method. Additionally, this device and / or method can be implemented using structures and / or functionalities other than one or more of the aspects set forth herein.

[0051] The names used in the following embodiments are explained as follows:

[0052] SQL query statement: A structured language for manipulating databases. Based on the description in the language, the data is processed and the result set information is returned to the client.

[0053] HashAgg operator: A method of aggregation using a hash algorithm in the group by clause. It calculates a hash value based on the grouping field after group by, groups the data according to the hash value, and groups the data with the same hash value into one group. Then it calculates the aggregation result for each group.

[0054] Limit operator: The implementation of filtering data based on a limit value in the limit clause. The limit operator stores the limit value, filters the input data and outputs it. When the amount of input data exceeds the limit value, no new data will be output.

[0055] HashBucket: A data structure that stores data with the same hash value. Data with the same hash value in the HashAgg operator are put into the same HashBucket so that the aggregation result can be calculated based on this HashBucket later.

[0056] Figure 1 This is a flowchart of a hash aggregation method based on the Limit statement in openGuass according to an exemplary first embodiment of the present invention, as follows: Figure 1 As shown, the method in this embodiment includes:

[0057] Step S1: Generate Limit and HashAgg operators based on the query SQL statement. The Limit operator includes a limit value, and the HashAgg operator includes an aggregation function and grouping field information.

[0058] Step S2: Pass and save the limit value in the Limit operator to the HashAgg operator;

[0059] Step S3: Execute the query SQL, calculate the hash value of the grouping field in the data row, and construct the HashBucket based on the number of non-repeating occurrences and the limit value of the hash value of the grouping field;

[0060] Step S4: Place the aggregated fields of data with the same hash value grouping field into the corresponding HashBucket hash bucket;

[0061] Step S5: After the data scan is completed, aggregate calculations are performed on the aggregate fields in each HashBucket using aggregate functions, and the aggregate results are output to the client.

[0062] The second exemplary embodiment of the present invention provides a hash aggregation method based on the Limit statement in openGuass. This embodiment is... Figure 1 A preferred embodiment of the method shown.

[0063] In step S3 of the method in this embodiment, calculating the hash value of the grouping field in the data row includes: scanning the data row by the HashAgg operator and using a hash function to calculate the hash value of the grouping field in each row of data.

[0064] In step S3 of this embodiment, the hash bucket is constructed based on the number of non-repeating occurrences and a limit value of the hash value of the grouping field, including:

[0065] If the number of non-repeating occurrences of the hash value of the grouping field is less than or equal to the limit, construct a number of HashBuckets equal to the number of non-repeating occurrences of the hash value of the grouping field.

[0066] If the number of non-repeating occurrences of the hash value of the grouping field exceeds a certain limit, construct a number of HashBuckets equal to the limit.

[0067] Step S4 of the method in this embodiment further includes: discarding the data when data with the same hash value grouping field does not have a corresponding HashBucket hash bucket.

[0068] Figure 2 This is an architectural diagram of a hash aggregation device based on the Limit statement in OpenGuass according to an exemplary third embodiment of the present invention, as follows: Figure 2 As shown, the apparatus of this embodiment includes:

[0069] The planner module is used to generate Limit and HashAgg operators based on the query SQL statement. The Limit operator contains the limit value, and the HashAgg operator contains the aggregation function and grouping field information.

[0070] The limit value passing module is used to pass the limit value in the Limit operator and save it to the HashAgg operator;

[0071] The hash bucket module is used to execute query SQL, calculate the hash value of the grouping field in the data row, and construct a HashBucket based on the number of non-repeating occurrences and a limit value of the hash value of the grouping field; aggregate fields in data with grouping fields that have the same hash value are put into the corresponding HashBucket.

[0072] The data aggregation module is used to perform aggregation calculations on the aggregation fields in each HashBucket after the data scan is completed, and output the aggregation results to the client.

[0073] The hash bucket module in this embodiment is used to: scan data rows using the HashAgg operator, and calculate the hash value of the grouping field in each row of data using a hash function; when the number of non-repeating occurrences of the hash value of the grouping field is less than or equal to a limit value, construct a number of HashBuckets equal to the number of non-repeating occurrences of the hash value of the grouping field; when the number of non-repeating occurrences of the hash value of the grouping field is greater than the limit value, construct a number of HashBuckets equal to the limit value; when data with the same hash value grouping field does not correspond to the same HashBucket, the data is discarded.

[0074] The exemplary fourth embodiment of the present invention provides a hash aggregation method based on the Limit statement in openGuass. This embodiment is a preferred embodiment of the methods shown in the first and second embodiments. The method of this embodiment is as follows:

[0075] Figure 3 This is a schematic diagram illustrating the elements of the query SQL statement in this embodiment. Figure 4 This is a diagram illustrating the decomposition of a SQL query into Limit and HashAgg operators; for example... Figure 3 and Figure 4 As shown, Limit and HashAgg operators are generated based on the query SQL statement. The Limit operator contains the limit value N, and the HashAgg operator includes the aggregation function and grouping field information.

[0076] like Figure 5 As shown, before executing the query SQL, the limit value N in the Limit operator is passed and saved to the HashAgg operator.

[0077] like Figure 6 , Figure 7 and Figure 8As shown, when executing the SQL query, a hash value V is calculated for the grouping fields D and E in each row of data using a hash function. A HashBucket is constructed based on the number of unique occurrences of the hash value V and a constraint N. If the number of unique occurrences M of the hash value V is less than or equal to the constraint N, M HashBuckets are constructed, equal to the number of unique occurrences of the hash value V. If the number of unique occurrences M of the hash value V is greater than the constraint N, N HashBuckets are constructed, equal to the constraint N. Figure 6 This is a schematic diagram illustrating the composition of data in this embodiment; Figure 7 This is a schematic diagram illustrating the calculation of the hash value V of grouping fields D and E using a hash function in this embodiment. Figure 8 This is a schematic diagram of the HashBucket constructed for this embodiment.

[0078] like Figure 9 As shown, aggregate fields A, B, and C in data with the same hash value V grouping field are placed into the same corresponding HashBucket.

[0079] like Figure 10 As shown, as the query SQL is continuously executed, the data rows are continuously scanned by the HashAgg operator. Based on the calculation results of the hash function of the grouping fields D and E, different hash values ​​V appear. The number of HashBuckets will continue to grow until M. However, when the number of HashBuckets grows to be equal to the limit value N, the number of HashBuckets will no longer grow, and no new HashBuckets will be generated based on the calculation results of the hash function of the grouping fields D and E.

[0080] like Figure 11 As shown, when data with the same hash value V in grouping fields D and E do not correspond to the same HashBucket, the data is discarded.

[0081] like Figure 12 As shown, after the data scan is completed, the aggregation functions are used to perform aggregation calculations on the aggregation fields A, B, and C in each HashBucket, and the aggregation results are output to the client.

[0082] The present invention also provides a computer-readable storage medium having a computer program stored thereon, wherein when the computer program is executed, it performs the steps of the hash aggregation method based on the Limit statement in openGuass described above.

[0083] Finally, the present invention provides a computer device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the program to implement the steps of the hash aggregation method based on the Limit statement in openGuass described above.

[0084] The above description is merely a specific embodiment of the present invention, but the scope of protection of the present invention is not limited thereto. Any variations or substitutions that can be easily conceived by those skilled in the art within the technical scope disclosed in the present invention should be included within the scope of protection of the present invention. Therefore, the scope of protection of the present invention should be determined by the scope of the claims.

Claims

1. An openGuass hash aggregation method based on a Limit statement, the method comprising: Step S1: generating a Limit operator and a HashAgg operator according to a query SQL statement, wherein the Limit operator includes a limit value, and the HashAgg operator includes an aggregation function and grouping field information; Step S2: passing and saving the limit value in the Limit operator to the HashAgg operator; Step S3: executing the query SQL, calculating a hash value of the grouping field in a data row, and constructing a HashBucket hash bucket according to a non-repeated number of occurrences of the hash value of the grouping field and the limit value; Step S4: placing aggregated fields in data having the same hash value grouping field into a corresponding same HashBucket hash bucket; Step S5: after data scanning is completed, performing aggregation calculation on the aggregated fields in each HashBucket hash bucket by using the aggregation function, and outputting an aggregation result to a client; In Step S3, the hash value of the grouping field in the data row is calculated, including: scanning the data row by using the HashAgg operator, and calculating the hash value of the grouping field in each row of data by using a hash function; In Step S3, the HashBucket hash bucket is constructed according to the non-repeated number of occurrences of the hash value of the grouping field and the limit value, including: When the non-repeated number of occurrences of the hash value of the grouping field is less than or equal to the limit value, a number of HashBucket hash buckets equal to the non-repeated number of occurrences of the hash value of the grouping field is constructed; When the non-repeated number of occurrences of the hash value of the grouping field is greater than the limit value, a number of HashBucket hash buckets equal to the limit value is constructed; Step S4 further includes: when data having the same hash value grouping field does not have a corresponding same HashBucket hash bucket, discarding the data.

2. An openGuass hash aggregation device based on Limit statement, characterized in that, The apparatus comprises: a planner module configured to generate a Limit operator and a HashAgg operator according to a query SQL statement, wherein the Limit operator includes a limit value, and the HashAgg operator includes an aggregation function and grouping field information; a limit value passing module configured to pass and save the limit value in the Limit operator to the HashAgg operator; a hash bucket module configured to execute the query SQL, calculate a hash value of a grouping field in a data row, and construct a HashBucket hash bucket according to a non-repeated number of occurrences of the hash value of the grouping field and the limit value; and place aggregated fields in data having the same hash value grouping field into a corresponding same HashBucket hash bucket; a data aggregation module configured to, after data scanning is completed, perform aggregation calculation on the aggregated fields in each HashBucket hash bucket by using the aggregation function, and output an aggregation result to a client; and The Hash bucket module is configured to: scan data rows by the HashAgg operator, and calculate hash values of a grouping field in each data row by using a hash function; when a non-repeated number of the hash value of the grouping field is less than or equal to a limit value, construct HashBucket hash buckets equal in number to the non-repeated number of the hash value of the grouping field; and when the non-repeated number of the hash value of the grouping field is greater than the limit value, construct HashBucket hash buckets equal in number to the limit value. The Hash bucket module is configured to discard data having the same hash value grouping field when the data does not correspond to the same HashBucket hash bucket.

3. A computer-readable storage medium, characterized in that, The computer readable storage medium stores a computer program, and the computer program is executed to perform the method of claim 1.

4. A computer device, comprising: The computer device comprises a memory, a processor, and a computer program stored on the memory and executable on the processor, and the processor executes the program to implement the steps of the method of claim 1.

Images