Method of implementing percentile_cont analysis function in OpenGauss

Abstract

The present invention belongs to the technical field of relational database management and operating system, and in particular, to a method for implementing a percentile_cont analysis function in OpenGauss and its use. The present invention proposes for the first time a method for implementing a percentile_cont analysis function in the query plan execution phase of an OpenGauss database, and through the method, the OpenGauss database can support the operation of the percentile_cont analysis function, thereby overcoming the defect that the OpenGauss database does not support the percentile_cont analysis function, providing strong technical support for the application of the percentile_cont analysis function in the OpenGauss database management system, helping to better exert database performance and significantly improve database work efficiency.

Description

Technical Field

[0001] The invention belongs to the technical field of relational database management and operating system, and in particular relates to a method for realizing a percentile_cont analysis function in OpenGauss and a use thereof. Background Art

[0002] OpenGauss is an open source relational database management system. Its kernel is derived from PostgreSQL. As an open source and free database platform, it aims to encourage community contribution and cooperation. 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] 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.

[0008] For the following example:

[0009] 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);

[0010] 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.

[0011] The final output is:

[0012]

[0013] However, the percentile_cont analysis function is not implemented in the current OpenGauss database, which limits the application of the percentile_cont analysis function in the OpenGauss database management system, affects the performance of the database, and reduces work efficiency. Summary of the invention

[0014] In order to overcome the defect that the current OpenGauss database does not support the percentile_cont analysis function, the present invention provides a solution. The present invention first proposes a method for implementing the percentile_cont analysis function in OpenGauss.

[0015] The method of the present invention comprises the following steps:

[0016] (i) Semantic analysis stage: converting the object statement into the internal representation of the function;

[0017] (ii) Query plan execution phase: When executing a window aggregate function, the data is grouped according to the specified grouping rules, and the data in each group is sorted. Then, for each row of records in each group, the transient function of the window aggregate function is called. The transient function processes the input data according to business needs. After the transient function completes the processing of all data in each group, the terminal function of the window aggregate function is continued to be called for each data in each group. The terminal function processes the above data according to the processing result of the transient function, executes the calculation process and obtains the final calculation result, and then returns and outputs the calculation result.

[0018] Furthermore, step (1) of the method of the present invention includes the following processing:

[0019] (1) Search for a function in the system table based on the function name and parameter type;

[0020] (2) Processing parameter types: converting the actual input parameters into parameter types according to the function definition in the system table; the parameter type conversion refers to converting the numeric parameters into a broader type according to the function definition in the system table. For example, if the parameter in the function definition is required to be a 32-bit integer, but the actual input parameter is a 16-bit integer, it needs to be converted into a 32-bit integer.

[0021] (3) Check the over statement and within statement to see whether the field in the over statement is a subset of the sort field of the entire query statement. If so, the check is completed. If not, it means that there is a sort field conflict, and the check program reports an error and exits;

[0022] (4) Generate an internal representation of the window aggregation function and fill in internal information, wherein the internal information includes parameter type, return value type, system ID of the function, etc.

[0023] Furthermore, the transient function in step (ii) of the method of the present invention processes the input data according to business needs, including the following processing process:

[0024] (1) Create an execution environment. When the data in the group is called for the first time, the execution environment is first created, the first parameter percentile of percentile_cont is obtained, the stored data counter is set to 0, and a sorted data structure is created to store each data in the second parameter. The execution environment created by this step will be passed to the termination function later. The termination function completes the calculation process and outputs the results in this execution environment.

[0025] (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.

[0026] Furthermore, the information recorded in the sorted data structure in the above method includes the data type, alignment, sorting method and available memory size of the data in each group.

[0027] Furthermore, the termination function in step (ii) of the method of the present invention processes the above data according to the processing result of the transient function, including the following processing process:

[0028] (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.

[0029] (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:

[0030] A. If array_length is 0, it returns 0;

[0031] B. If array_length is greater than 0, the value of the first point first_pos is floor(percentile*(array_length-1)), that is, percentile*array_length is rounded down, and the value of the second point second_pos is 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 first_val, and the value of the second point data number_array[second_pos] is sencond_val, and then the difference ratio proportion between the two is calculated, the calculation formula is percentile*(array_length-1)-first_pos, and finally use first_val sencond_val proportion to make a difference: first_val+(proportion*(second_val-first_val) to obtain the final calculation result;

[0032] (3) The execution environment state is set to complete the processing of the current group, and the next group to be processed needs to be received, and the calculation result obtained in the previous step is stored in the corresponding field of the execution environment;

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

[0034] In addition, the present invention also relates to the use of the method for implementing the percentile_cont analysis function in OpenGauss in OpenGauss database management or operating system.

[0035] In summary, the present invention proposes for the first time a method for implementing the percentile_cont analysis function in the query planning stage of the OpenGauss database. Through this method, the OpenGauss database can support the operation of the percentile_cont analysis function, thereby providing strong technical support for the application of the percentile_cont analysis function in the OpenGauss database management system, helping to better exert the database performance and significantly improve the database work efficiency. BRIEF DESCRIPTION OF THE DRAWINGS

[0036] 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.

[0037] Figure 1 It is a schematic diagram of the implementation process of the method of the present invention. DETAILED DESCRIPTION

[0038] 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.

[0039] 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.

[0040] Embodiment 1: A method for implementing the percentile_cont analysis function in OpenGauss.

[0041] The method comprises the following steps (see Figure 1 ):

[0042] (I) Semantic analysis stage: converting the object statement into the internal representation of the function, which specifically includes the following processing steps:

[0043] (1) Search for a function in the system table based on the function name and parameter type;

[0044] (2) Processing parameter types: converting the actual input parameters into parameter types according to the function definition in the system table; the parameter type conversion refers to converting the numeric parameters into a broader type according to the function definition in the system table. For example, if the parameter in the function definition is required to be a 32-bit integer, but the actual input parameter is a 16-bit integer, it needs to be converted into a 32-bit integer.

[0045] (3) Check the over statement and within statement to see whether the field in the over statement is a subset of the sort field of the entire query statement. If so, the check is completed. If not, it means that there is a sort field conflict, and the check program reports an error and exits;

[0046] (4) Generate an internal representation of the window aggregation function and fill in internal information, wherein the internal information includes parameter type, return value type, system ID of the function, etc.

[0047] (II) Query plan execution stage: When executing the window aggregate function, the data is grouped according to the specified grouping rules, and the data in each group is sorted. Then, for each row of records in each group, the transient function of the window aggregate function is called. The transient function processes the input data according to business needs. After the transient function completes the processing of all data in each group, the terminal function of the window aggregate function is continued to be called for each data in each group. The terminal function processes the above data according to the processing result of the transient function, executes the calculation process and obtains the final calculation result, and then returns and outputs the calculation result. The specific processing includes the following:

[0048] (1) Transient function processing

[0049] (a) Create an execution environment. When the data in a group is called for the first time, the execution environment is first created, the first parameter percentile of percentile_cont is obtained, the stored data counter is set to 0, and a sorted data structure is created 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. The execution environment created by this step will be passed to the termination function later. The termination function completes the calculation process and outputs the results in this execution environment.

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

[0051] (2) Termination function processing

[0052] (a) 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;

[0053] (b) 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:

[0054] If array_length is 0, it returns 0;

[0055] If array_length is greater than 0, the value of the first point first_pos is floor(percentile*(array_length-1)), that is, the result of percentile*array_length rounded down, and the value of the second point second_pos is ceiling(percentile*(array_length-1)), that is, the result of percentile*array_length 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 first_val, and the value of the second point data number_array[second_pos] is sencond_val, and then the difference ratio proportion between the two is calculated, the calculation formula is percentile*(array_length-1)-first_pos, and finally use first_val sencond_val proportion to make a difference: first_val+(proportion*(second_val-first_val) to obtain the final calculation result;

[0056] (c) Setting the execution environment state to complete processing of the current group and receiving the next group to be processed, and storing the calculation result obtained in the previous step into the field corresponding to the execution environment;

[0057] (d) Return the final calculation result.

[0058] Example 2: Application of the method of Example 1 in a specific case.

[0059] 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);

[0060] The above SQL requires that the value field be grouped and sorted according to the value of the color field, and then the value at the 0.5 position in each group of data is taken. Here, the data will be divided into two groups: the first group is the group with color red (the data in the group are 1 and 3); the second group is the group with color blue (the data in the group are 2 and 4).

[0061] When obtaining the difference value at the 0.5 position within each group, the following transient function and terminal function are called for each group.

[0062] The execution process of the transient function is as follows:

[0063] (1) If it is the first time to call the data in the group, first create the execution environment, get the first parameter of percentile_cont, that is, 0.5, store it as percentile, set the stored data counter to 0, and create a sorted data structure to store each data in the second parameter. This sorted data structure will record the data type of the data in each group, whether it is int32, the current int32 alignment, the ascending sorting method, the available memory size, etc.; the execution environment created by this step 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;

[0064] (2) Obtain each sorted data in the group from the lower-level node, store it in the sorting data structure of the execution environment, and increment the stored data counter by 1.

[0065] The execution process of the finalization function is as follows:

[0066] (1) If the processing of this group has been completed, the processing result of this group is returned directly; otherwise, proceed to the next step.

[0067] (2) The current array_length is 2. Sort the stored data in the group according to the alignment of int32 and the ascending sorting method. For the group whose color is red, the data sorting is 1 3, the value of first_pos is 0, the value of second_pos is 1, and the proportion is 0.5. Therefore, the calculation formula is 1+(3-1)*0.5=2, and the final result is 2. Similarly, for the group whose color is blue, the final result is 3. After processing the data of this group, the processing result is cached and returned.

[0068] 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.

[0069] 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 implementing percentile_cont analysis function in OpenGauss, characterized in that: The method comprises the following steps:

1. Semantic analysis: converting object statements into internal representations of functions. (II) Query plan execution phase: When executing the window aggregate function, the data is grouped according to the specified grouping rules, and the data in each group is sorted. Then, for each row of records in each group, the transient function of the window aggregate function is called. The transient function processes the input data according to business needs. After the transient function completes the processing of all data in each group, the terminal function of the window aggregate function is continued to be called for each data in each group. The terminal function processes the above data according to the processing result of the transient function, executes the calculation process and obtains the final calculation result, and then returns and outputs the calculation result. The transient function in step (ii) processes the input data according to business needs, including the following processing: (1) Create an execution environment. When the data in the group is called for the first time, the execution environment is first created, the first parameter percentile of percentile_cont is obtained, the stored data counter is set to 0, and a sorted data structure is created to store each data in the second parameter; The execution environment created in this step will be passed to the finalization function later, and the finalization function completes the calculation process and outputs the results in this execution environment; (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; The termination function in step (ii) processes the above data according to the processing result of the transient function, including the following processing process: (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. (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: A. If array_length is 0, it returns 0; B. If array_length is greater than 0, the value of the first point first_pos is floor(percentile*(array_length-1)), which is the result of percentile*array_length rounded down, and the value of the second point second_pos is ceiling(percentile*(array_length-1)), which is the result of percentile*array_length 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 first_val, and the value of the second point data number_array[second_pos] is sencond_val, and then the difference ratio proportion between the two is calculated. The calculation formula is percentile*(array_length-1) - first_pos. Finally, use first_val sencond_val proportion to make a difference: first_val + (proportion*(second_val - first_val) to obtain the final calculation result. (3) Set the execution environment state 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; (4) Return the final calculation result.

2. The method for implementing percentile_cont analysis function in OpenGauss according to claim 1, characterized in that: Step (1) includes the following processing: (1) Search for a function in the system table based on the function name and parameter type; (2) Process parameter types and convert the actual input parameters into different types according to the function definition in the system table; (3) Check the over statement and within statement to see whether the field in the over statement is a subset of the sort field of the entire query statement. If so, the check is completed. If not, it means that there is a sort field conflict, and the check program reports an error and exits. (4) Generate the internal representation of the window aggregation function and fill in the internal information.

3. The method for implementing percentile_cont analysis function in OpenGauss according to claim 1, characterized in that: The information recorded in the sorted data structure includes the data type, alignment, sorting method and available memory size of the data in each group.

4. Use of the method for implementing the percentile_cont analysis function in OpenGauss according to any one of claims 1 to 3 in OpenGauss database management or operating system.

Images