A method and device for realizing database window function

Abstract

The present invention discloses a method and device for realizing a database window function function, and relates to the technical field of database window function. The method comprises: extracting a grouping column set, a sorting column set and a window function set from an SQL statement, scanning and reading data and sorting them according to the grouping column set, then creating a temporary container to store the data of each group, and sorting the data in each group according to the sorting column set, traversing the data of each group, calling the window function to calculate the result, and storing the result in a three-dimensional array, and finally, when a row of data is returned, obtaining the corresponding window function value from the three-dimensional array and filling it into the output column. The present invention can perform operations such as aggregation, sorting, and ranking on the query rows in each partition without changing the number of rows in the original table, thereby improving the flexibility and efficiency of data analysis.

Description

Technical Field

[0001] The present invention relates to the technical field of database window functions, in particular to a method and device for realizing database window function functions. Background Art

[0002] The implementation of database window functions currently has the following defects:

[0003] 1. Performance issues: Window functions usually require sorting and aggregation calculations on large amounts of data, which can lead to performance bottlenecks. Especially when processing large data sets, sorting and aggregation operations can take up a lot of computing resources and time.

[0004] 2. Memory consumption: Since window functions need to store grouped data, sorted results, and calculated results in memory, memory consumption may become a problem for large-scale data sets. If there is insufficient memory, it may cause system crashes or performance degradation.

[0005] 3. Scalability issues: When the amount of data processed is very large, existing database systems may not be able to scale effectively to support the calculation of window functions. This may require additional hardware resources or distributed computing frameworks to handle large-scale data processing tasks.

[0006] 4. Complex query optimization: The use of window functions may increase the complexity of the query, making it difficult for the query optimizer to find the optimal execution plan. This may lead to poor query performance, especially when multiple window functions are used in complex queries.

[0007] 5. Data consistency issues: In a multi-threaded environment, the calculation of window functions may lead to data inconsistency issues. For example, if concurrent modifications occur during the calculation process, it may lead to errors in the calculation results.

[0008] 6. Lack of flexibility: Some database systems may not support all types of window functions, or may only support limited window function options, which limits the flexibility and expressiveness of users in using window functions. Summary of the invention

[0009] In view of the needs and deficiencies of current technology development, the present invention provides a method and device for realizing database window function functions.

[0010] In the first aspect, the present invention provides a method for implementing a database window function. The technical solution adopted to solve the above technical problem is as follows:

[0011] A method for implementing database window function functions, extracting a grouping column set, a sorting column set and a window function set from an SQL statement, scanning and reading data and sorting them according to the grouping column set, then creating a temporary container to store the data of each group, and sorting the data in each group according to the sorting column set, traversing the data of each group, calling the window function to calculate the result, and storing the result in a three-dimensional array, and finally, when a row of data is returned, obtaining the corresponding window function value from the three-dimensional array and filling it into the output column.

[0012] Optionally, a method for implementing a database window function is involved, and the specific implementation process is as follows:

[0013] S1. When the SQL goes through the compilation phase and the planning phase, the grouping column set partition_cols, the sorting column set order_cols, and the window function set windowFuncs are extracted;

[0014] S2. Scan and read the data that meets the conditions and store them in the RowContainer container. After scanning and reading all the data, sort the stored data in the RowContainer container according to the grouping column set partition_cols.

[0015] S3. Create a container partitionRowContainer to temporarily store the data in each group, and define a three-dimensional array windowValues ​​to receive the calculation results.

[0016] S4, traverse the container RowContainer, when a group data scan is completed, sort the data in the container partitionRowContainer according to the sorting column set order_cols;

[0017] S5. Traverse all data in the container partitionRowContainer, call the window functions in the window function set windowFuncs in sequence, and store the calculation results in the three-dimensional array windowValues;

[0018] S6. After the traversal of the container partitionRowContainer is completed, the data of the container partitionRowContainer is cleared, and the process returns to step S4 to continue traversing the next group of data in the container RowContainer;

[0019] S7. After the RowContainer traversal is completed, the window function column value of each row of data is obtained and filled into the three-dimensional array windowValues ​​accordingly;

[0020] S8. When returning a row of data to the upper layer, the window function return column obtains unique data from the three-dimensional array windowValues ​​and fills it into the corresponding window function column;

[0021] S9. After all data are returned, this process ends.

[0022] Further optionally, step S2 is performed to sort the stored data in the container RowContainer according to the grouping column set partition_cols using a quick sort or merge sort algorithm so that data with the same grouping column value are recorded together, so as to perform aggregation operations on the data in subsequent processing.

[0023] Further optionally, executing step S3, defining the created three-dimensional array windowValues ​​including the grouping id, the window function id and the row number id;

[0024] Execute step S8. When returning a row of data to the upper layer, the window function return column obtains unique data from the three-dimensional array windowValues ​​according to the group id, window function id and row number id, and fills it into the corresponding window function column.

[0025] Further optionally, the container partitionRowContainer involved has a built-in clear method; when executing step S6, the built-in clear method of the container partitionRowContainer is used to reset the container, assign the array storing data in the container to empty and assign the attribute recording the size of the container rows to 0, or re-instantiate a new container partitionRowContainer; after completing the clearing operation of the container partitionRowContainer, return to step S4 and continue to traverse the next grouped data in the container RowContainer until all grouped data have been processed.

[0026] In a second aspect, the present invention provides a device for implementing a database window function. The technical solution adopted to solve the above technical problem is as follows:

[0027] A device for implementing a database window function, comprising:

[0028] The collection extraction module is used to extract the grouping column set partition_cols, the sorting column set order_cols and the window function set windowFuncs from the SQL statement;

[0029] The scanning and sorting module is used to scan and read the data that meets the conditions and store them in the container RowContainer; it is also used to sort the stored data in the container RowContainer according to the grouping column set partition_cols after scanning and reading all the data;

[0030] Create a definition module to create a container partitionRowContainer to temporarily store the data in each group, and also to define a three-dimensional array windowValues ​​to receive the calculation results;

[0031] The traversal sorting module is used to traverse the container RowContainer. After a group data scan is completed, the data in the container partitionRowContainer is sorted according to the sorting column set order_cols;

[0032] Traverse the calling module, traverse all the data in the container partitionRowContainer, call the window functions in the window function set windowFuncs in turn, and obtain the window function column value of each row of data;

[0033] The clear return module is used to clear the data of the container partitionRowContainer after the traversal of the container partitionRowContainer is completed, return to the traversal sorting module, and continue to traverse the next group data in the container RowContainer;

[0034] The data filling module is used to fill the window function column value of each row of data into the three-dimensional array windowValues ​​after the container RowContainer traversal is completed;

[0035] The function filling module is used to obtain unique data from the three-dimensional array windowValues ​​through the window function return column when returning a row of data to the upper layer, and fill it into the corresponding window function column;

[0036] The process end module is used to end the entire process after all row data are returned.

[0037] Optionally, after scanning and reading all the data, the scan sorting module involved uses a quick sort or merge sort algorithm to sort the stored data in the container RowContainer according to the grouping column set partition_cols, so that data with the same grouping column values ​​are recorded together, so as to perform aggregation operations on these data in subsequent processing.

[0038] Optionally, the three-dimensional array windowValues ​​defined by the creation definition module includes grouping id, window function id and row number id;

[0039] When the function filling module involved returns a row of data to the upper layer, it returns the column through the window function, obtains unique data from the three-dimensional array windowValues ​​according to the grouping id, window function id and row number id, and fills it into the corresponding window function column.

[0040] Optionally, the container partitionRowContainer has a built-in clear method; after the traversal of the container partitionRowContainer is completed, the clear return module uses the built-in clear method of the container partitionRowContainer to reset the container, assign the array storing data in the container to empty and assign the attribute recording the size of the container rows to 0, or re-instantiate a new container partitionRowContainer;

[0041] After completing the clearing operation of the container partitionRowContainer, the clearing return module returns to the traversal sorting module to continue traversing the next grouped data in the container RowContainer until all the grouped data are processed.

[0042] Compared with the prior art, the method and device for realizing the database window function of the present invention have the following beneficial effects:

[0043] 1. The present invention can use simpler SQL to achieve more complex analysis and calculation. It can aggregate, sort, rank and other operations on the query rows in each partition without changing the number of rows in the original table, thereby improving the flexibility and efficiency of data analysis;

[0044] 2. The present invention groups and sorts data so that the window function can quickly calculate the data in each group. At the same time, a three-dimensional array is used to store the calculation results, which can effectively reduce repeated calculations and memory usage and improve the overall calculation efficiency. By processing and temporarily storing data in batches, large-scale data sets can be processed under limited memory resources. Data inconsistency problems caused by concurrent modifications can be avoided. At the same time, by sorting and grouping the data, the correctness and consistency of the calculation results can be ensured. BRIEF DESCRIPTION OF THE DRAWINGS

[0045] Attached Figure 1 is a flow chart of a method according to Embodiment 1 of the present invention;

[0046] Attached Figure 2 is a module connection block diagram of the second embodiment of the present invention;

[0047] Attached Figure 3 It is a schematic diagram of grouping the stored data in the RowContainer according to the grouping column set in an embodiment of the present invention;

[0048] Attached Figure 4 It is a schematic diagram of window function data filling in an embodiment of the present invention. DETAILED DESCRIPTION

[0049] In order to make the technical solution, the technical problem solved and the technical effect of the present invention more clearly understood, the technical solution of the present invention is clearly and completely described below in conjunction with specific embodiments.

[0050] Embodiment 1:

[0051] Combined with Figure 1 This embodiment proposes a method for implementing a database window function, extracting a grouping column set, a sorting column set and a window function set from an SQL statement, scanning and reading data and sorting them according to the grouping column set, then creating a temporary container to store the data of each group, and sorting the data in each group according to the sorting column set, traversing the data of each group, calling the window function to calculate the result, and storing the result in a three-dimensional array, and finally, when a row of data is returned, the corresponding window function value is obtained from the three-dimensional array and filled into the output column.

[0052] This embodiment involves a method for implementing a database window function, and the specific implementation process is as follows:

[0053] S1. When the SQL goes through the compilation phase and the planning phase, the grouping column set partition_cols, the sorting column set order_cols, and the window function set windowFuncs are extracted.

[0054] S2, scan and read the data that meets the conditions, and store it in the RowContainer; after scanning and reading all the data, combine the attached Figure 3 , according to the grouping column set partition_cols, use the quick sort or merge sort algorithm to sort the stored data in the container RowContainer, so that the data with the same grouping column value are recorded together, so as to perform aggregation operations on these data in the subsequent processing process.

[0055] S3. Create a container partitionRowContainer to temporarily store the data in each group, and define a three-dimensional array windowValues ​​to receive the calculation results.

[0056] The defined three-dimensional array windowValues ​​includes grouping id, window function id and row number id.

[0057] S4, traverse the container RowContainer, when a group data scan is completed, sort the data in the container partitionRowContainer according to the sorting column set order_cols;

[0058] S5. Traverse all data in the container partitionRowContainer, call the window functions in the window function set windowFuncs in sequence, and store the calculation results in the three-dimensional array windowValues;

[0059] S6. After the traversal of the container partitionRowContainer is completed, the data of the container partitionRowContainer is cleared, and the process returns to step S4 to continue traversing the next group of data in the container RowContainer.

[0060] The partitionRowContainer container has a built-in clear method.

[0061] In this step, the built-in clear method of the container partitionRowContainer is used to reset the container, assign the array storing data in the container to empty and assign the attribute recording the size of the container rows to 0, or re-instantiate a new container partitionRowContainer; after completing the clearing operation of the container partitionRowContainer, return to step S4 and continue to traverse the next grouped data in the container RowContainer until all the grouped data are processed.

[0062] S7. After the RowContainer traversal is completed, the window function column value of each row of data is obtained and filled into the three-dimensional array windowValues ​​accordingly;

[0063] S8. When returning a row of data to the upper layer, the window function return column obtains unique data from the three-dimensional array windowValues ​​according to the group id, window function id and row number id, and fills it into the corresponding window function column. Figure 4 ;

[0064] S9. After all data are returned, this process ends.

[0065] Embodiment 2:

[0066] Combined with Figure 2This embodiment provides a device for implementing a database window function, which includes:

[0067] The collection extraction module is used to extract the grouping column set partition_cols, the sorting column set order_cols and the window function set windowFuncs from the SQL statement;

[0068] The scanning and sorting module is used to scan and read the data that meets the conditions and store them in the container RowContainer; it is also used to sort the stored data in the container RowContainer according to the grouping column set partition_cols after scanning and reading all the data;

[0069] Create a definition module to create a container partitionRowContainer with a built-in clear method to temporarily store the data in each group, and also to define a three-dimensional array windowValues ​​including group id, window function id and row number id to receive the calculation results;

[0070] The traversal sorting module is used to traverse the RowContainer container. After a grouped data scan is completed, the stored data in the RowContainer container is sorted according to the grouping column set partition_cols using a quick sort or merge sort algorithm, so that data with the same grouping column value are recorded together, so that the data can be aggregated in the subsequent processing process;

[0071] Traverse the calling module, traverse all the data in the container partitionRowContainer, call the window functions in the window function set windowFuncs in turn, and obtain the window function column value of each row of data;

[0072] The clear return module is used to use the built-in clear method of the container partitionRowContainer to reset the container after the traversal of the container partitionRowContainer is completed, assign the array storing data in the container to empty and assign the attribute recording the number of container rows to 0, or re-instantiate a new container partitionRowContainer, and then return to the traversal sorting module to continue traversing the next grouped data in the container RowContainer;

[0073] The data filling module is used to fill the window function column value of each row of data into the three-dimensional array windowValues ​​after the container RowContainer traversal is completed;

[0074] The function filling module is used to return a row of data to the upper layer, return a column through the window function, obtain unique data from the three-dimensional array windowValues ​​according to the group id, window function id and row number id, and fill it into the corresponding window function column;

[0075] The process end module is used to end the entire process after all row data are returned.

[0076] In summary, by using a method and device for implementing a database window function of the present invention, operations such as aggregation, sorting, and ranking can be performed on query rows in each partition without changing the number of rows in the original table, thereby improving the flexibility and efficiency of data analysis.

[0077] The above specific examples are used to explain the principles and implementation methods of the present invention in detail. These examples are only used to help understand the core technical content of the present invention. Based on the above specific embodiments of the present invention, any improvements and modifications made by technicians in this technical field without departing from the principles of the present invention should fall within the scope of patent protection of the present invention.

Claims

1. A method for implementing a database window function, characterized in that: Extract the grouping column set, sorting column set and window function set from the SQL statement, scan and read the data and sort it according to the grouping column set, then create a temporary container to store the data of each group, and sort the data in each group according to the sorting column set, traverse the data of each group, call the window function to calculate the result, and store the result in a three-dimensional array. Finally, when a row of data is returned, get the corresponding window function value from the three-dimensional array and fill it into the output column.

2. A method for implementing a database window function according to claim 1, characterized in that: The method is implemented based on a database management system, and the specific implementation process is as follows: S1. When the SQL goes through the compilation phase and the planning phase, the grouping column set partition_cols, the sorting column set order_cols, and the window function set windowFuncs are extracted; S2. Scan and read the data that meets the conditions and store them in the RowContainer container. After scanning and reading all the data, sort the stored data in the RowContainer container according to the grouping column set partition_cols. S3. Create a container partitionRowContainer to temporarily store the data in each group, and define a three-dimensional array windowValues ​​to receive the calculation results. S4, traverse the container RowContainer, when a group data scan is completed, sort the data in the container partitionRowContainer according to the sorting column set order_cols; S5. Traverse all data in the container partitionRowContainer, call the window functions in the window function set windowFuncs in sequence, and store the calculation results in the three-dimensional array windowValues; S6. After the traversal of the container partitionRowContainer is completed, the data of the container partitionRowContainer is cleared, and the process returns to step S4 to continue traversing the next group of data in the container RowContainer; S7. After the RowContainer traversal is completed, the window function column value of each row of data is obtained and filled into the three-dimensional array windowValues ​​accordingly; S8. When returning a row of data to the upper layer, the window function return column obtains unique data from the three-dimensional array windowValues ​​and fills it into the corresponding window function column; S9. After all data are returned, this process ends.

3. A method for implementing a database window function according to claim 2, characterized in that: Execute step S2 to sort the stored data in the RowContainer according to the grouping column set partition_cols using a quick sort or merge sort algorithm, so that data with the same grouping column value are recorded together, so as to perform aggregation operations on the data in subsequent processing.

4. A method for implementing a database window function according to claim 2, characterized in that: Execute step S3 to define the created three-dimensional array windowValues ​​including the grouping id, the window function id and the row number id; Execute step S8. When returning a row of data to the upper layer, the window function return column obtains unique data from the three-dimensional array windowValues ​​according to the group id, window function id and row number id, and fills it into the corresponding window function column.

5. A method for implementing a database window function according to claim 2, characterized in that: The partitionRowContainer container has a built-in clear method; When executing step S6, use the built-in clear method of the container partitionRowContainer to reset the container, assign the array storing data in the container to empty and assign the attribute recording the size of the container rows to 0, or re-instantiate a new container partitionRowContainer; after completing the clearing operation of the container partitionRowContainer, return to step S4 and continue to traverse the next grouped data in the container RowContainer until all grouped data have been processed.

6. A device for implementing a database window function, characterized in that: It includes: The collection extraction module is used to extract the grouping column set partition_cols, the sorting column set order_cols and the window function set windowFuncs from the SQL statement; The scanning and sorting module is used to scan and read the data that meets the conditions and store them in the container RowContainer; it is also used to sort the stored data in the container RowContainer according to the grouping column set partition_cols after scanning and reading all the data; Create a definition module to create a container partitionRowContainer to temporarily store the data in each group, and also to define a three-dimensional array windowValues ​​to receive the calculation results; The traversal sorting module is used to traverse the container RowContainer. After a group data scan is completed, the data in the container partitionRowContainer is sorted according to the sorting column set order_cols; Traverse the calling module, traverse all the data in the container partitionRowContainer, call the window functions in the window function set windowFuncs in turn, and obtain the window function column value of each row of data; The clear return module is used to clear the data of the container partitionRowContainer after the traversal of the container partitionRowContainer is completed, return to the traversal sorting module, and continue to traverse the next group data in the container RowContainer; The data filling module is used to fill the window function column value of each row of data into the three-dimensional array windowValues ​​after the container RowContainer traversal is completed; The function filling module is used to obtain unique data from the three-dimensional array windowValues ​​through the window function return column when returning a row of data to the upper layer, and fill it into the corresponding window function column; The process end module is used to end the entire process after all row data are returned.

7. A device for implementing a database window function according to claim 6, characterized in that: After scanning and reading all the data, the scanning and sorting module uses a quick sort or merge sort algorithm to sort the stored data in the container RowContainer according to the grouping column set partition_cols, so that data with the same grouping column value are recorded together, so as to perform aggregation operations on these data in subsequent processing.

8. The device for realizing a database window function according to claim 6, characterized in that: The three-dimensional array windowValues ​​defined by the creation definition module includes group id, window function id and row number id; When the function filling module returns a row of data to the upper layer, it returns a column through the window function, obtains unique data from the three-dimensional array windowValues ​​according to the grouping id, window function id and row number id, and fills it into the corresponding window function column.

9. The device for realizing a database window function according to claim 6, characterized in that: The container partitionRowContainer has a built-in clear method; After the traversal of the container partitionRowContainer is completed, the clear return module uses the built-in clear method of the container partitionRowContainer to reset the container, assign the array storing data in the container to empty and assign the attribute recording the size of the container rows to 0, or re-instantiate a new container partitionRowContainer; After completing the clearing operation of the container partitionRowContainer, the clearing return module returns to the traversal sorting module to continue traversing the next grouped data in the container RowContainer until all the grouped data are processed.

Images