Methods for optimizing the execution of multiple percentile_cont analysis functions in the database

Abstract

The present invention relates to the technical field of relational database management, and in particular to a method for optimizing the execution of multiple percentile_cont analysis functions in a database. The method comprises parsing output columns in a semantic analysis phase, obtaining function group output columns by grouping, obtaining percentile_cont groups by grouping according to function IDs, grouping output columns with the same characteristics into the same group and marking them in an internal representation, and sequentially executing transient functions and terminal functions of multiple percentile_cont analysis functions in the same group in an execution phase. In the database parsing and execution phases, the method makes full use of the correlation of operations and the data locality of analysis functions, shares the data grouping and sorting process among multiple analysis functions in the group, and improves the execution speed of SQL and the application efficiency of percentile_cont analysis functions in database management systems.

Description

Technical Field

[0001] The present invention relates to the technical field of relational database management (RDBMS), and in particular to a method for optimizing the execution of multiple percentile_cont analysis functions in a database. Background Art

[0002] In a typical relational database management system such as OpenGauss, processing a query is usually divided into the following three stages:

[0003] (1) Lexical and grammatical analysis mainly converts the user's input text (SQL) into an internal data structure, generally called a grammar parse tree, and verifies the correctness of the grammar, ultimately obtaining a grammar parse tree representing SQL.

[0004] (2) Query optimization: The grammatical parsing tree obtained in the previous stage is optimized based on rules and physical costs to generate an optimal query plan.

[0005] (3) Execute the query plan generated in the previous stage (generally using an iterator), obtain the query results and return them to the user.

[0006] In the process of data analysis for databases, a series of analysis functions are often used. Percentile_cont is a commonly used and very valuable analysis function. Percentile_cont is an inverse distribution function that assumes a continuous distribution model. The function has a percentage value and a sorted group, and returns an interpolated value within the given percentage value range of the sorted group.

[0007] However, in current relational databases, there is still a lack of effective optimization execution methods for multiple percentile_cont analysis functions, which limits the application of percentile_cont analysis functions in database management systems, affects database performance, and reduces work efficiency. Summary of the invention

[0008] (1) Purpose of the Invention

[0009] In order to overcome the above-mentioned deficiencies of the prior art and improve the application efficiency of percentile_cont analysis functions in database management systems, the present invention proposes for the first time a solution for optimizing the execution of multiple percentile_cont functions by utilizing the correlation of operations and data locality during the parsing and execution phases of the database.

[0010] (2) Technical solution

[0011] In a first aspect, the present invention provides a method for optimizing the execution of multiple percentile_cont analysis functions in a database, comprising the following steps:

[0012] 1. Semantic Analysis

[0013] (1) Parse the output column;

[0014] (2) Group the parsed output columns to obtain function group output columns;

[0015] (3) Group the function group output column according to the function ID to obtain the group whose function ID is percentile_cont;

[0016] (4) According to the preset rules, the group with the function ID percentile_cont is regrouped, the output columns with the same characteristics are grouped into the same group, and the group to which they belong is marked in the internal representation;

[0017] 2. Implementation

[0018] (5) executing the transient functions of multiple percentile_cont analysis functions in the same group obtained in step (4);

[0019] (6) Execute the termination function of each percentile_cont analysis function mentioned above.

[0020] Preferably, in step (2) of the method of the present invention, the parsed output columns are grouped into common field group, constant group, expression group, and function group output columns.

[0021] Furthermore, the function ID in step (3) of the method of the present invention is the serial number of the built-in function in each database, and a certain function can be uniquely identified according to the function ID.

[0022] Furthermore, the step (4) of classifying the output columns with the same characteristics into the same group in the method of the present invention refers to classifying the output columns with the same sorting field, sorting method, and grouping field into the same group.

[0023] Furthermore, the method of the present invention further comprises: before executing the transient function of the first percentile_cont analysis function in the same group in step (5), the data objects are pre-grouped and sorted, and the obtained ordered grouped data are placed in an environment that can be accessed by subsequent percentile_cont analysis functions; then the transient functions of each percentile_cont analysis function in the same group are executed one by one; each transient function sequentially accesses the above-mentioned ordered grouped data to complete the establishment of the initialization state.

[0024] Furthermore, the termination function of executing each percentile_cont analysis function described above in step (6) of the method of the present invention has an input derived from a calculation result obtained from an initialization state of a transient function of the function.

[0025] Furthermore, the method of the present invention further comprises: when all the groups are processed, the process ends; otherwise, the process returns to the execution phase step (5) to process the next group.

[0026] In a second aspect, the present invention further provides a system for optimizing the execution of multiple percentile_cont analysis functions in a database, comprising:

[0027] Parsing module: used to parse the output column;

[0028] Output column grouping module: used to group the parsed output columns to obtain function group output columns;

[0029] Function grouping module: used to group the function group output columns according to the function ID, and obtain the group whose function ID is percentile_cont;

[0030] Feature grouping module: used to regroup the group with function ID percentile_cont and group the output columns with the same features into the same group;

[0031] Marking module: used to mark the group to which each output column belongs in the internal representation;

[0032] Data grouping and sorting module: used to group and sort data objects to obtain ordered grouped data;

[0033] Transient function execution module: used to execute the transient function of percentile_cont analysis function;

[0034] Termination function execution module: used to execute the termination function of percentile_cont analysis function.

[0035] (3) Beneficial effects

[0036] In summary, the present invention proposes for the first time a method for optimizing the execution of multiple percentile_cont analysis functions for relational databases. During the parsing and execution stages of the database, the present invention fully utilizes the correlation of operations and the data locality of analysis functions, shares the data grouping and sorting process among multiple analysis functions in the group, and optimizes the execution of multiple percentile_cont functions, thereby improving the execution speed of SQL and the application efficiency of percentile_cont analysis functions in database management systems. BRIEF DESCRIPTION OF THE DRAWINGS

[0037] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the following briefly introduces the drawings required for use in the embodiments of the present invention. Obviously, the following drawings are only some embodiments recorded in the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative work.

[0038] Figure 1 It is a flow chart for implementing the method of the present invention. DETAILED DESCRIPTION

[0039] In order to make the purpose, technical solution and advantages of the present invention clearer, the technical solution of the present invention will be clearly and completely described below in conjunction with specific embodiments and corresponding drawings. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of them. The present invention can also be implemented or applied through other different specific implementation methods. The details in this specification can also be modified or changed in various ways based on different viewpoints and applications without departing from the spirit of the present invention.

[0040] At the same time, it should be understood that the protection scope of the present invention is not limited to the specific embodiments described below; it should also be understood that the terms used in the embodiments of the present invention are for describing specific embodiments rather than for limiting the protection scope of the present invention.

[0041] Embodiment 1: A method for optimizing the execution of multiple percentile_cont analysis functions in a database

[0042] The percentile_cont function takes two parameters as input: the first is a floating point type, which is a decimal between 0 and 1, called a percentile; the second is an indefinite one-dimensional array, which is an ordered array, with ascending and descending order specified by SQL. The elements in this array are all numeric data, such as integers or floating point types.

[0043] For the following example:

[0044] SELECT color,percentile_cont(0.5)WITHIN GROUP(ORDER BY value)over(partition by color)FROM(VALUES('red',1),('red',3),('blue',2),('blue',4))v(color,value);

[0045] It is to group the records of 4 rows with two fields (color and value): ('red',1), ('red',3), ('blue',2), ('blue',4) on the color field, so that two groups will be obtained: the first group is ('red',1), ('red',3); the second group is ('blue',2), ('blue',4), and then sort the value field of each group to get the interpolated value at the 0.5 percentile.

[0046] The final output is:

[0047]

[0048] According to the current execution method of percentile_cont analysis function, the SQL processing process with percentile_cont function is as follows:

[0049] (I) Lexical and Syntactic Analysis Stage

[0050] As described in the previous process, in this stage, SQL processing mainly converts the percentile_cont function into an internal representation, such as the function's execution address, parameter type, etc., to provide necessary meta information for the subsequent execution stage.

[0051] (II) Query plan execution phase

[0052] 1. Group the input data, that is, group the four records ('red',1), ('red',3), ('blue',2), ('blue',4). Mainly based on the semantics of SQL, group the first field value and put the records with the same first field value into the same group.

[0053] 2. Execute the transient function of the window aggregation function, that is, take each row of records in each group as input and pass it to the transient function for processing. The transient function processing process is as follows:

[0054] (1) When the data in a group is called for the first time, an execution environment is first created. This execution environment will be passed to the termination function later, and the termination function will complete the calculation process and output the results in this execution environment. The execution environment obtains the first parameter percentile of percentile_cont, sets the stored data counter to 0, and creates a sorted data structure to store each data in the second parameter. The information recorded in the data structure includes the data type, alignment, sorting method, and available memory size of the data in each group.

[0055] (2) The input group data is stored in the sorting data structure of the execution environment, and the stored data counter is incremented by 1.

[0056] 3. After the transient function has processed all the data in the group, the terminator function of the window aggregation function is called for each data in the group. The processing process of the terminator function is as follows:

[0057] (1) In the execution environment of the transient function, determine whether the processing of the current group has been completed. If the processing of the current group has been completed, return the result directly from the cached field. If the processing of the current group has not been completed, proceed to the following steps.

[0058] (2) Get percentile and the number of stored data array_length, sort the number_array stored in the array according to the data, and perform the following calculation process to calculate the final result:

[0059] (a) If array_length is 0, returns 0.

[0060] (b) If array_length is greater than 0, the value of the first point first_pos is taken as floor(percentile*(array_length-1)), that is, percentile*array_length is rounded down, and the value of the second point second_pos is taken as ceiling(percentile*(array_length-1)), that is, percentile*array_length is rounded up; at this time, if the value of first_pos is equal to the value of second_pos, the value of the first_pos data in the number_array array is directly returned; if the value of first_pos is not equal to the value of second_pos, the value of the first point data number_array[first_pos] is taken as first_val, and the value of the second point data number_array[second_pos] is taken as sencond_val, and then the difference ratio proportion between the two is calculated, and the calculation formula is percentile*(array_length-1)-first_pos. Finally, first_val sencond_val proportion is used to make a difference: first_val+(proportion*(second_val-first_val) to obtain the final calculation result.

[0061] (3) The execution environment state is set to complete processing of the current group, receive the next group to be processed, and store the calculation result obtained in the previous step into the corresponding field of the execution environment.

[0062] (4) Return the final calculation result.

[0063] 4. If the grouping is finished, it means that all groups have been processed and you can exit; if the grouping is not finished yet, return to step 2 and continue processing the data of the next group.

[0064] As mentioned above, in the entire SQL processing process, data grouping and sorting takes up a lot of time and computer resources. Especially for the following types of SQL:

[0065] SELECT dep,percentile_cont(0.1)WITHIN GROUP(ORDER BY sal)over(partition by dep),percentile_cont(0.2)WITHIN GROUP(ORDER BY sal)over(partition by dep),percentile_cont(0.3)WITHIN GROUP(ORDER BY sal)over(partition by dep)FROM t;

[0066] Table t is a table with multiple fields, two of which are dep for department and sal for salary. This SQL calculates the salaries of the top 10%, top 20%, and top 30% of employees by department.

[0067] For this type of SQL, the aforementioned execution scheme does not consider sharing sorting partition results between multiple analysis functions, so repeated calculations will occur. In order to solve this problem, the present invention makes the following improvements to the above SQL containing multiple percentile_cont functions to improve function execution efficiency.

[0068] The method for optimizing the execution of multiple percentile_cont analysis functions of the present invention comprises the following steps (see Figure 1 ):

[0069] 1. Semantic Analysis

[0070] (1) Parse the output column.

[0071] (2) Group the parsed output columns into common field group, constant group, expression group, and function group output columns.

[0072] (3) The function group output column is grouped according to the function ID. The function ID is the number of the built-in function in each database. A function can be uniquely identified based on the function ID, and a group whose function ID is percentile_cont is obtained.

[0073] (4) The group with the function ID percentile_cont is grouped again according to the preset rules, and the output columns with the same sorting field, sorting method, and grouping field are grouped into the same group, and the group to which they belong is marked in the internal representation.

[0074] After the above steps, we get the output column with the same sort field, sort method, group field, and the function is percentile_cont.

[0075] 2. Implementation

[0076] In the execution phase, we execute several percentile_cont analysis functions in the same group obtained in the semantic analysis phase together.

[0077] (5) Before executing the transient function of the first percentile_cont analysis function in the same group obtained in step (4), the data objects are grouped and sorted in advance, and the obtained ordered grouped data is placed in an environment that can be accessed by subsequent percentile_cont analysis functions; then the transient functions of each percentile_cont analysis function in the same group are executed one by one; each transient function accesses the above-mentioned ordered grouped data in turn to complete the establishment of the initialization state.

[0078] (6) Execute the termination function of each of the percentile_cont analysis functions. The specific process of executing the termination function is the same as the execution process of the aforementioned prior art. The input of the termination function comes from the calculation result obtained from the initialization state of the transient function of the function.

[0079] (7) When all groups have been processed, the process ends; otherwise, return to the execution phase step (5) to process the next group.

[0080] After the above steps, we have made full use of the data locality of the percentile_cont analysis function and shared the data grouping and sorting process among multiple analysis functions in the group, thereby greatly improving the execution speed of the entire SQL.

[0081] Embodiment 2: A system for optimizing the execution of multiple percentile_cont analysis functions in a database, comprising:

[0082] Parsing module: used to parse the output column;

[0083] Output column grouping module: used to group the parsed output columns to obtain function group output columns;

[0084] Function grouping module: used to group the function group output columns according to the function ID, and obtain the group whose function ID is percentile_cont;

[0085] Feature grouping module: used to regroup the group with function ID percentile_cont and group the output columns with the same features into the same group;

[0086] Marking module: used to mark the group to which each output column belongs in the internal representation;

[0087] Data grouping and sorting module: used to group and sort data objects to obtain ordered grouped data;

[0088] Transient function execution module: used to execute the transient function of percentile_cont analysis function;

[0089] Termination function execution module: used to execute the termination function of percentile_cont analysis function;

[0090] The above modules are implemented according to the technical solution of Example 1.

[0091] The various embodiments of the present invention are described in a progressive manner, and the same or similar parts between the various embodiments can be referred to each other, and each embodiment focuses on the differences from other embodiments.

[0092] The above description is only an embodiment of the present invention and is not intended to limit the present invention. For those skilled in the art, the present invention may have various modifications and variations. Any modification, replacement, etc. made within the spirit and principle of the present invention should be included in the scope of protection of the claims of the present invention.

Claims

1. A method for optimizing the execution of multiple percentile_cont analysis functions in a database, characterized in that: The method comprises the following steps:

1. Semantic Analysis (1) Parse the output column; (2) Group the parsed output columns into common field groups, constant groups, and expression groups, and obtain function group output columns; (3) Group the function group output column according to the function ID to obtain the group whose function ID is percentile_cont; (4) According to the preset rules, the group with the function ID percentile_cont is regrouped, and the output columns with the same sorting field, sorting method, and grouping field are grouped into the same group, and the group to which they belong is marked in the internal representation; 2. Implementation (5) Execute the transient functions of multiple percentile_cont analysis functions in the same group obtained in step (4); before executing the transient function of the first percentile_cont analysis function in the same group, pre-group and sort the data objects, and place the obtained ordered grouped data in an environment that can be accessed by subsequent percentile_cont analysis functions; then execute the transient functions of each percentile_cont analysis function in the same group one by one; each transient function sequentially accesses the above-mentioned ordered grouped data to complete the establishment of the initialization state; (6) Execute the termination function of each percentile_cont analysis function mentioned above.

2. The method according to claim 1, characterized in that The function ID in step (3) is the number of the built-in function in each database, and a function is uniquely identified based on the function ID.

3. The method according to claim 1, characterized in that: The step (6) executes the termination function of each percentile_cont analysis function, and the input of the termination function comes from the calculation result obtained from the initialization state of the transient function of the function.

4. The method according to claim 1, characterized in that: The method further includes: when all the groups are processed, ending the process; otherwise, returning to the execution phase step (5) to process the next group.

5. A system for optimizing the execution of multiple percentile_cont analysis functions in a database, characterized in that: When the system is running, the steps of the method for optimizing the execution of multiple percentile_cont analysis functions in the database of claim 1 are implemented, and the system comprises: Parsing module: used to parse the output column; Output column grouping module: used to group the parsed output columns to obtain function group output columns; Function grouping module: used to group the function group output columns according to the function ID, and obtain the group whose function ID is percentile_cont; Feature grouping module: used to regroup the group with function ID percentile_cont and group the output columns with the same features into the same group; Marking module: used to mark the group to which each output column belongs in the internal representation; Data grouping and sorting module: used to group and sort data objects to obtain ordered grouped data; Transient function execution module: used to execute the transient function of percentile_cont analysis function; Termination function execution module: used to execute the termination function of percentile_cont analysis function.

Images