Database query statement optimization method, medium, product and equipment

Abstract

The invention provides a database query statement optimization method, a medium, a product and equipment. The method comprises the following steps: acquiring and analyzing a database query statement; judging whether the database query statement contains a partition window function and an aggregation function at the same time or not; under the condition that the database query statement simultaneously comprises a partition window function and an aggregation function, judging whether a partition reference column of the partition window function has a uniqueness constraint and a non-null constraint or not; under the condition that the partition reference column has the unique constraint and the non-empty constraint, determining the aggregation type of the aggregation function; and equivalently rewriting the database query statement according to the aggregation type to obtain an optimized query statement. Through the method, redundant window operation and aggregation operation in the database query statement can be reduced, so that the query performance of the database is improved.

Description

Technical Field

[0001] This invention relates to the field of database technology, and in particular to a method, medium, product, and device for optimizing database query statements. Background Technology

[0002] In the current database technology field, the combination of window functions and aggregate functions is widely used, often to achieve complex data processing needs such as partition aggregation and ranking statistics. Existing database query optimizers have basic execution plan optimization capabilities and can optimize regular query statements by selecting scan methods and index matching, but they have limitations in optimizing queries that combine window functions and aggregate functions. Current technologies, regardless of the actual characteristics of the data partitions, follow a fixed execution flow of "scanning data → partition sorting → window aggregation," without adapting the calculation logic of aggregate functions to specific scenarios.

[0003] This generalized processing approach often leads to numerous redundant operations when executing related queries, increasing CPU overhead and memory usage, prolonging query response time, and wasting database resources. The performance bottleneck of existing technologies is particularly pronounced in high-concurrency query and large-scale data processing scenarios, making it difficult to meet the actual needs of efficient data processing. Therefore, there is an urgent need for a targeted optimization technique for window function combination queries to improve database query performance and resource utilization efficiency. Summary of the Invention

[0004] In view of the above problems, the present invention proposes a method, medium, product and device for optimizing database query statements to overcome the above problems or at least partially solve the above problems.

[0005] One objective of this invention is to reduce redundant operations in database query statements.

[0006] A further objective of this invention is to improve the query performance of database query statements.

[0007] Specifically, the present invention provides a method for optimizing database query statements, comprising: Obtain and parse the database query statement; Determine whether a database query statement contains both partition window functions and aggregate functions; When a database query statement contains both partition window functions and aggregate functions, determine whether the partition reference column of the partition window function has uniqueness constraints and non-null constraints. Determine the aggregation type of the aggregation function when the partitioned reference column has uniqueness constraints and non-null constraints; The database query statement is rewritten equivalently based on the cluster type to obtain the optimized query statement.

[0008] Optionally, the aggregation type includes single-value aggregation functions, which are functions that perform aggregation operations on a single attribute value of the target column; The steps for equivalent rewriting of a database query statement based on the cluster type include: When the aggregation type is a single-value aggregation function, determine the target column of the aggregation function; Eliminate partition window functions and aggregate functions from database query statements, and change the database query statements to queries for the target columns.

[0009] Optionally, single-valued aggregation functions include aggregation functions of the types sum, min, max, and avg.

[0010] Optionally, the aggregation type includes logical decision aggregate functions, which are functions that make logical decisions on a single attribute value of the target column; The steps for equivalent rewriting of a database query statement based on the cluster type include: When the aggregation type is a logical decision-type aggregation function, determine the target column and the decision logic of the aggregation function; Generate corresponding target condition judgment expressions based on the target column and the judgment logic; Eliminate partition window functions and aggregate functions from database query statements, and replace database query statements with query statements that use target conditional expressions.

[0011] Optionally, logical decision-making aggregate functions include aggregate functions of type count and rank.

[0012] Optionally, the partition window function is a window function that includes the `partition by` keyword and is used to partition data according to the partition reference column.

[0013] Optionally, after the step of equivalently rewriting the database query statement based on the cluster type, the following may also be included: Verify whether the result set returned by the rewritten optimized query statement is consistent with the result set returned by the original database query statement; If the result set is consistent, retain the optimized query statement; If the result set is inconsistent, roll back the rewrite operation and output an optimization failure message.

[0014] According to another aspect of the present invention, a computer-readable storage medium is also provided, on which a computer program is stored, wherein the computer program, when executed by a processor, implements the steps of the optimization method for any of the above-described database query statements.

[0015] According to another aspect of the present invention, a computer program product is also provided, comprising a computer program that, when executed by a processor, implements the steps of the optimization method for any of the above-described database query statements.

[0016] According to another aspect of the present invention, a computer device is also provided, including a memory, a processor, and a machine-executable program stored in the memory and running on the processor, wherein the processor executes the machine-executable program to implement the steps of the optimization method for any of the above-described database query statements.

[0017] The database query statement optimization method of the present invention first obtains and parses the database query statement; then determines whether the database query statement contains both a partition window function and an aggregate function; if the database query statement contains both a partition window function and an aggregate function, it determines whether the partition reference column of the partition window function has uniqueness constraints and non-null constraints; if the partition reference column has uniqueness constraints and non-null constraints, it determines the aggregation type of the aggregate function; and finally, it rewrites the database query statement equivalently based on the aggregation type to obtain the optimized query statement. This method can reduce redundant window operations and aggregation operations in database query statements, thereby improving database query performance.

[0018] The above and other objects, advantages and features of the present invention will become more apparent to those skilled in the art from the following detailed description of specific embodiments of the invention in conjunction with the accompanying drawings. Attached Figure Description

[0019] The following sections will describe some specific embodiments of the invention in detail by way of example and not limitation, with reference to the accompanying drawings. The same reference numerals in the drawings denote the same or similar parts or portions. Those skilled in the art should understand that these drawings are not necessarily drawn to scale. In the drawings: Figure 1 This is a flowchart illustrating a method for optimizing database query statements according to an embodiment of the present invention; Figure 2 This is a flowchart illustrating a method for optimizing database query statements according to another embodiment of the present invention; Figure 3 This is a schematic diagram of a computer program product according to an embodiment of the present invention; Figure 4 This is a schematic diagram of a computer-readable storage medium according to an embodiment of the present invention; and Figure 5 This is a schematic diagram of a computer device according to an embodiment of the present invention. Detailed Implementation

[0020] Those skilled in the art should understand that the embodiments described below are merely a part of the embodiments of the present invention, and not all of the embodiments of the present invention. These partial embodiments are intended to explain the technical principles of the present invention and are not intended to limit the scope of protection of the present invention. Based on the embodiments provided by the present invention, all other embodiments obtained by those skilled in the art without creative effort should still fall within the scope of protection of the present invention.

[0021] It should be noted that the logic and / or steps represented in the flowchart or otherwise described herein, for example, can be considered as a sequenced list of executable instructions for implementing logical functions, and can be specifically implemented in any computer-readable medium for use by, or in conjunction with, an instruction execution system, apparatus or device (such as a computer-based system, a processor-included system or other system that can fetch and execute instructions from, an instruction execution system, apparatus or device).

[0022] In the current database technology field, SQL (Structured Query Language) is a commonly used example of a database query statement. SQL statements offer various functions such as data manipulation and data definition. This language is interactive and provides great convenience to users. In specific query processing, SQL join operations are the core means of achieving multi-table data association and complex logical queries, but some SQL statements have room for optimization.

[0023] An optional SQL example is shown in equation (1): select sum(a) over (partition by b) from s1 Formula (1) Column b is a unique and non-empty column.

[0024] The execution logic of the SQL statement shown in equation (1) is as follows; 1. Read the data from column a (the column to be summed) and column b (the partition column) of all rows in table s1; 2. Partition the data according to the values ​​in column b, grouping rows with the same b value into one partition; 3. Within each partition, sum the values ​​in column 'a' of all rows in that partition (sum(a)). 4. For each row of data, return the sum of column a of its corresponding partition. The final result set has the same number of rows as the total number of rows in table s1 (the aggregate result of each row corresponding to its corresponding partition).

[0025] Analysis reveals that the SQL statement shown in equation (1) has significant flaws in execution logic and resource utilization, resulting in a clear bottleneck in query performance optimization. Specifically, the core execution logic of this SQL statement is to partition by column b and then sum column a. However, the uniqueness and non-emptiness of column b mean that each partition contains only one row of data. The essential purpose of partitioning is to achieve multi-row convergence calculation, but there is no need for aggregation of multiple rows of data after partitioning. The partitioning step does not generate any actual computational value and is logically redundant. Furthermore, its sorting step has become a meaningless pre-operation: when the window function is executed, it needs to be sorted by column b to achieve partition classification. However, the uniqueness of column b means that the b values ​​of all rows are not repeated. After sorting, no effective same-value grouping can be formed. The sorting operation can only produce unordered independent data rows, which neither serves the partitioning logic nor adds an extra data processing step, resulting in a lengthy execution chain.

[0026] In addition, while the semantics of sum(a) are to sum multiple values, since a single partition contains only one row of data, the summation result is equivalent to the original value in column 'a' of that row, turning the aggregate function into a data scanning operation. However, the existing execution logic still requires starting the aggregation calculation engine to perform the complete process of numerical accumulation and result storage, resulting in unnecessary computational overhead.

[0027] This shows that there is still considerable room for optimization in the current database field for this type of database query statement.

[0028] Therefore, this invention provides a method for optimizing database query statements. Figure 1 This is a flowchart illustrating a method for optimizing database query statements according to an embodiment of the present invention, as shown below. Figure 1 As shown, the optimization method for this database query statement includes at least the following steps S101 to S104.

[0029] Step S101: Obtain the database query statement. In this step, the database query statement can generally be obtained by user input or automatically generated by the database program during operation due to business requirements. Those skilled in the art can decide on the method of obtaining the database query statement based on the actual situation.

[0030] Step S102: Determine whether the database query statement contains both partition window functions and aggregate functions. Partition window functions are window functions containing the `partition by` keyword, used to partition data according to the partition reference column. Aggregate functions generally refer to functions that perform aggregation or statistical calculations on a data set, such as `sum`, `min`, `max`, `avg`, `count`, and `rank`.

[0031] Taking Equation (1) as an example, in the SQL statement shown in Equation (1), sum(a) is an aggregate function and over(partition by b) is a partition window function. Therefore, the statement satisfies the condition of containing both at the same time.

[0032] Step S103: If the determination in step S102 is yes, determine whether the partition reference column of the partition window function has uniqueness constraints and non-null constraints.

[0033] If the condition is met in step S102, extract the partition reference column specified by partition by in the partition window function (such as data column b in equation (1), and verify whether the column satisfies the following conditions by querying the database table structure metadata: Uniqueness constraints: The column has primary key constraints, unique index constraints, etc. (ensuring that the column values ​​in each row are not repeated); NOT NULL constraint: The column has a NOT NULL constraint (ensuring no NULL values).

[0034] This is because if the partition reference column is unique and non-empty, then each partition contains only one row of data. In this case, the calculation result of the aggregation function can be directly derived (without aggregation), and the elimination of redundant operations has a theoretical basis. If this constraint is not met, the partition may contain multiple rows of data, and aggregation operations are necessary, making optimization impossible. This step can accurately identify special optimizable scenarios, avoid invalid rewriting of non-optimizable statements, and ensure the rationality of the optimization logic and the correctness of the results.

[0035] Step S104: If the determination in step S103 is yes, determine the aggregation type of the aggregation function.

[0036] If the determination in step S103 is yes, then based on the semantics and computational characteristics of the aggregation function, it is classified into single-value aggregation functions or logical decision-based aggregation functions. This is because different types of aggregation functions have different semantics and derivation logic, and the corresponding rewriting rules also differ. For example, single-value aggregation functions can be directly mapped to target column queries, while logical decision-based functions need to be rewritten and classified through conditional judgment expressions before precise equivalent rewriting operations can be performed.

[0037] Step S105: Rewrite the database query statement based on the aggregation type to obtain an optimized query statement.

[0038] In some optional embodiments, the aggregation type may generally include single-value aggregation functions, which are functions that perform aggregation operations on a single attribute value of a target column. Single-value aggregation functions may generally include aggregation functions of type sum, min, max, and avg.

[0039] Optionally, the steps of equivalently rewriting a database query statement based on the aggregation type may generally include: when the aggregation type is a single-value aggregate function, determining the target column of the aggregate function; eliminating the partition window function and aggregate function in the database query statement, and changing the database query statement into a query statement that queries the target column.

[0040] Since the partition reference column b is unique and non-empty, and each partition contains only one row of data, the results of sum(a), min(a), max(a), and avg(a) are all equivalent to the original values ​​in column a of that row. The aggregation calculation semantics of the aggregate function are no longer meaningful and can be completely replaced by directly querying the target column, thereby eliminating redundant steps such as sorting, partitioning, and aggregation. As shown in the SQL statement in equation (1), the target column of its aggregate function is a; then, the redundant operations are eliminated, that is, the partition window function (over(partition by b)) and the aggregate function (sum(...)) in the statement are deleted, and finally the optimized statement is generated: the original statement is rewritten into a simple query statement that only queries the target column. The optimized result is shown in equation (2): select a from s1 (Formula 2) The specific execution flow of the SQL statement shown in equation (2) is as follows: Data scanning: The database performs a full table scan only on table s1 and reads all rows of data in column a of the table (no need to read other columns, such as column b in equation (1)). Result Return: The result set is returned by directly organizing all the values ​​in column a obtained from the scan, without any additional calculation steps such as sorting, partitioning, aggregation, or conditional judgment.

[0041] Compared to the SQL statement shown in Equation (1), Equation (2) eliminates all redundant operations (sorting, partitioning, aggregation) that are irrelevant to the core objective of obtaining column a data, while ensuring that the results are completely consistent. It reduces the complexity of partitioning and aggregation queries to simple column queries, achieving ultimate optimization in three dimensions: logic, resources, and performance. It not only meets the business data requirements but also minimizes the consumption of database resources.

[0042] In some alternative embodiments, the aggregation type may also include logical decision aggregation functions, which are functions that make logical decisions on a single attribute value of the target column. Logical decision aggregation functions include aggregation functions of type count and rank.

[0043] The steps for equivalently rewriting a database query based on its aggregation type generally include: when the aggregation type is a logical decision aggregate function, determining the target column and decision logic of the aggregate function; generating the corresponding target condition expression based on the target column and decision logic; eliminating the partition window function and aggregate function in the database query, and transforming the database query into a query using the target condition expression. Logical decision aggregate functions generally refer to aggregate functions whose aggregation results can be directly derived from fixed rules corresponding to the function semantics, in scenarios where the partition reference column is unique and non-nullable. These include types such as count, rank, and row_number, and their core characteristic is that the aggregation result is a fixed value or a finite result set.

[0044] In a row-by-row partitioning scenario, the results of logical judgment functions follow fixed patterns. For example, `count(a)` can only be 0 (a is NULL) or 1 (a is not NULL), and `rank / row_number` can only be 1. Therefore, there's no need to perform aggregation or ranking calculations; the results can be obtained through simple conditional judgments. This eliminates redundant window functions and aggregate functions. This optimization simplifies the "scan + sort + partition + aggregate" process to a "scan + conditional judgment" process, avoiding the memory consumption of sorting and the computational overhead of aggregation, while ensuring the results are completely consistent with the original statement.

[0045] This method eliminates complex operations such as sorting, partitioning, and redundant aggregation, simplifying the multi-step execution process into scanning or scanning plus simple judgment operations. This significantly shortens the execution time of the corresponding SQL statements, while reducing the memory usage required for sorting, the CPU overhead required for aggregation, and the I / O reading consumption of invalid columns, thereby improving the overall resource throughput of the database and thus improving the database query performance.

[0046] In addition, although the above rewriting logic is based on strict semantic equivalence deduction, special cases may exist in practical applications (such as untimely updates to database metadata, differences in the handling of special characters or NULL values, etc.), leading to deviations in the rewritten results. Adding a validation step can further ensure the accuracy of query results and avoid business data errors caused by optimization.

[0047] Specifically, after the step of equivalently rewriting the database query statement according to the aggregation type, it may also include: verifying whether the result set returned by the rewritten optimized query statement is consistent with the result set returned by the original database query statement; if the result sets are consistent, retain the optimized query statement; if the result sets are inconsistent, roll back the rewriting operation and output an optimization failure message.

[0048] This method can improve the reliability and stability of the optimization method without affecting the core optimization performance, and reduce the risk of technology implementation.

[0049] Figure 2 This is a flowchart illustrating a method for optimizing database query statements according to another embodiment of the present invention, as shown below. Figure 2 As shown, the optimization method for this database query statement includes at least the following steps S201 to S208.

[0050] Step S201: Obtain the database query statement.

[0051] Step S202: Determine whether the database query statement contains both a partition window function and an aggregate function. If step S202 determines otherwise, the optimization operation ends. A partition window function is a window function containing the `partition by` keyword, used to partition data according to the partition reference column. Aggregate functions generally refer to functions that perform aggregation or statistical calculations on a dataset, such as `sum`, `min`, `max`, `avg`, `count`, and `rank`.

[0052] Step S203: If the determination in step S202 is yes, determine whether the partition reference column of the partition window function has uniqueness constraints and non-null constraints. If the determination in step S203 is no, then the current optimization operation ends.

[0053] If the condition is met in step S202, the partition reference column specified by partition by in the partition window function (such as data column b in equation (1)) is extracted. By querying the database table structure metadata, it is verified whether the column simultaneously satisfies the uniqueness constraint and the null constraint. This is because if the partition reference column is unique and not null, then each partition contains only one row of data. At this time, the calculation result of the aggregation function can be directly derived (without aggregation), and there is a theoretical basis for eliminating redundant operations. If the constraint is not met, the partition may contain multiple rows of data, and aggregation operations are necessary, making optimization impossible. This step can accurately identify special scenarios that can be optimized, avoid invalid rewriting of non-optimizable statements, and ensure the rationality of the optimization logic and the correctness of the results.

[0054] Step S204: If the determination in step S203 is yes, determine the aggregation type of the aggregation function. An aggregation function is a function used to aggregate, statistically analyze, or rank a dataset. This invention focuses on two types of functions used in combination with partitioning window functions: one is single-value aggregation functions (e.g., sum, min, max, avg), where the aggregation result is directly equal to the unique value of the target column within the partition when the partition reference column is unique and not empty. The other is logical decision-based aggregation functions (e.g., count, rank, row_number), where the aggregation result can be determined by derivation through fixed logical rules when the partition reference column is unique and not empty.

[0055] If the determination in step S203 is yes, then based on the semantics and computational characteristics of the aggregate functions, they are classified into single-value aggregate functions or logical decision aggregate functions. This is because different types of aggregate functions have different semantics and derivation logic, and the corresponding rewriting rules also differ. For example, single-value aggregate functions can be directly mapped to target column queries, while logical decision functions need to be rewritten and classified through conditional judgment expressions before precise equivalent rewriting operations can be performed.

[0056] Step S205: If the function is determined to be a single-value aggregation function in step S204, determine the target column of the aggregation function.

[0057] Step S206: Eliminate the partition window function and aggregate function in the database query statement, and change the database query statement to a query statement that queries the target column. In the scenario where "the partition reference column is unique and not empty", the results of sum(a), min(a), max(a), and avg(a) are all equal to the target column value of the unique row within the partition. The existence of aggregate functions and window functions only adds redundant operations and has no actual computational value; directly querying the target column can completely replace the original complex calculation, and the result semantics are consistent.

[0058] Step S207: If the function is determined to be a logical decision-type aggregate function in step S204, determine the target column and the decision logic of the aggregate function.

[0059] An alternative example is shown in equation (3): select count(a) over (partition by b) from s1 Formula (3) Where b is a unique and non-empty column. The SQL statement shown in equation (3) has the following execution logic; 1. Read the data from column a (the column to be counted) and column b (the partition column) of all rows in table s1; 2. Partition the data according to the values ​​in column b, grouping rows with the same b value into one partition; 3. Count the number of rows with non-NULL values ​​in column 'a' within each partition (count(a) only counts non-NULL values); 4. Return the count of the partition to which each row belongs for each row of table s1. The final result set has the same number of rows as the original table.

[0060] However, column b in this SQL statement has uniqueness and non-null constraints, meaning that each row of b values ​​is unique. Therefore, each partition has only one row of data. In this case, the result of count(a) is only 0 (a is null) or 1 (a is not null). However, the database will still execute the complete partitioning and counting process, resulting in redundant operations.

[0061] Step S208: Generate the corresponding target condition judgment expression based on the target column and the judgment logic.

[0062] Specifically, for count(target column), a case when target column is null then 0 else 1 end can be generated; for rank / row_number, a fixed value of 1 can be generated.

[0063] Taking equation (3) as an example, the target column is a, the judgment logic is non-null judgment, and the generated expression is case when a isnull then 0 else 1 end.

[0064] This expression is an equivalent replacement for the result of a logical decision function. It can directly obtain a result that is completely consistent with the original aggregation function through simple conditional judgment, without the need to perform complex window aggregation or ranking calculations.

[0065] Step S209: Eliminate the partition window function and aggregate function in the database query statement, and change the database query statement to a query statement using the target conditional expression.

[0066] The optimized result of equation (3) is shown in equation (4): select case when a is null then 0 else 1 end as count from s1 Formula (4) The specific execution flow of the SQL statement shown in equation (4) is as follows: 1. Read the data in column a of all rows in table s1 (no need to read other columns); 2. Perform null value checks on column 'a' for each row: 3. If column 'a' is NULL (empty), return a result of 0; 4. If column a is not empty (has a specific value), then return result 1; 5. Name the result of each row as column count, and finally return a result set that matches the total number of rows in table s1.

[0067] Compared to equation (3), this statement does not involve complex operations such as partitioning, sorting, and aggregation. It achieves the effect of "recording null values ​​as 0 and non-null values ​​as 1" only through basic condition judgment. It is an equivalent simplification of equation (3). When executing, it only requires a full table scan and row-by-row judgment, without any redundant calculation steps.

[0068] This method allows for the design of differentiated optimization logic for different types of aggregate functions, thereby completely eliminating redundant logic in partition window functions and aggregate functions, transforming complex queries into simple queries natively supported by the database, and greatly improving the database's query performance.

[0069] The flowcharts provided in this embodiment are not intended to indicate that the operations of the method will be performed in any particular order, or that all operations of the method are included in every case. Furthermore, the method may include additional operations. Within the scope of the technical concept provided by the method in this embodiment, additional variations can be made to the above method.

[0070] It should be understood that in some embodiments, the components may be implemented using hardware, software, firmware, or a combination thereof. In the above embodiments, multiple steps or methods may be implemented using software or firmware stored in memory and executed by a suitable instruction execution system.

[0071] This embodiment also provides a computer program product 10, a computer-readable storage medium 20, and a computer device 30. Figure 3 This is a schematic diagram of a computer program product 10 according to an embodiment of the present invention. Figure 4 This is a schematic diagram of a computer-readable storage medium 20 according to an embodiment of the present invention. Figure 5 This is a schematic diagram of a computer device 30 according to an embodiment of the present invention. The computer program product 10 includes a computer program 11, which, when executed by the processor 32, implements the steps of the database query statement optimization method described above. A computer-readable storage medium 20 stores the computer program 11 thereon, which, when executed by the processor 32, implements the steps of the database query statement optimization method described above. The computer device 30 may include a memory 31, a processor 32, and the computer program 11 stored on the memory 31 and running on the processor 32.

[0072] The computer program 11 used to perform the operations of this invention may be assembly instructions, Instruction Set Architecture (ISA) instructions, machine instructions, machine-dependent instructions, microcode, firmware instructions, state setting data, integrated circuit configuration data, or source code or object code written in any combination of one or more programming languages ​​and procedural programming languages. The computer program 11 may execute entirely on the user's computer, partially on the user's computer, as a standalone software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In the latter case, the remote computer may be connected to the user's computer via any type of network, including a Local Area Network (LAN) or Wide Area Network (WAN), or may be connected to an external computer (e.g., via the Internet using an Internet service provider). In some embodiments, to perform aspects of this invention, electronic circuits, including, for example, programmable logic circuits, Field-Programmable Gate Arrays (FPGAs), or Programmable Logic Arrays (PLAs), may execute computer-readable program instructions to personalize the electronic circuits by utilizing state information from computer-readable program instructions.

[0073] For the purposes of this embodiment, computer program product 10 is a related product containing computer program 11. For the purposes of this embodiment, computer-readable storage medium 20 is a tangible device capable of holding and storing computer program 11, and can be any device capable of containing, storing, communicating, propagating, or transmitting program 11 for use by or in conjunction with an instruction execution system, apparatus, or device. More specific examples (a non-exhaustive list) of computer-readable storage medium 20 include: portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), static random access memory (SRAM), portable optical disc read-only memory (CD-ROM), digital versatile disc (DVD), memory stick, floppy disk, mechanical encoding device, and any suitable combination thereof.

[0074] Computer device 30 can be, for example, a server, desktop computer, laptop computer, tablet computer, or smartphone. In some examples, computer device 30 can be a cloud computing node. Computer device 30 can be described in the general context of computer system executable instructions (such as program modules) executed by a computer system. Typically, program modules can include routines, programs, object programs, components, logic, data structures, etc., that perform specific tasks or implement specific abstract data types. Computer device 30 can be implemented in a distributed cloud computing environment where tasks are performed by remote processing devices linked through a communication network. In a distributed cloud computing environment, program modules can reside on local or remote computing system storage media, including storage devices.

[0075] Computer device 30 may include a processor 32 adapted to execute stored instructions and a memory 31 that provides temporary storage space for the operation of said instructions during operation. The processor 32 may be a single-core processor, a multi-core processor, a computing cluster, or any other configuration. The memory 31 may include random access memory (RAM), read-only memory, flash memory, or any other suitable storage system.

[0076] Computer device 30 may also include a network adapter / interface and an input / output (I / O) interface. The I / O interface allows external devices that can be connected to the computer device to input and output data. The network adapter / interface provides communication between the computer device and a network, typically represented as a communication network.

[0077] Therefore, those skilled in the art should recognize that although numerous exemplary embodiments of the present invention have been shown and described in detail herein, many other variations or modifications conforming to the principles of the present invention can be directly determined or derived from the disclosure of the present invention without departing from the spirit and scope of the invention. Thus, the scope of the present invention should be understood and construed as covering all such other variations or modifications.

Claims

1. A method for optimizing database query statements, comprising: Obtain and parse the database query statement; Determine whether the database query statement contains both a partition window function and an aggregate function; When the database query statement contains both the partition window function and the aggregate function, determine whether the partition reference column of the partition window function has uniqueness constraints and non-null constraints; If the partition reference column has uniqueness constraints and non-null constraints, determine the aggregation type of the aggregation function; The database query statement is rewritten equivalently based on the aggregation type to obtain an optimized query statement.

2. The method for optimizing database query statements according to claim 1, wherein, The aggregation type includes single-value aggregation functions, which are functions that perform aggregation operations on a single attribute value of a target column; The step of equivalently rewriting the database query statement according to the aggregation type includes: When the aggregation type is the single-value aggregation class aggregation function, determine the target column of the aggregation function; Remove the partition window function and the aggregate function from the database query statement, and change the database query statement to a query statement that queries the target column.

3. The method for optimizing database query statements according to claim 2, wherein, The single-valued aggregation functions include aggregation functions of type sum, min, max, and avg.

4. The method for optimizing database query statements according to claim 1, wherein, The aggregation type includes logical decision-type aggregation functions, which are functions that perform logical decisions on a single attribute value of the target column; The step of equivalently rewriting the database query statement according to the aggregation type includes: When the aggregation type is the logical decision-type aggregation function, determine the target column and decision logic of the aggregation function; Generate corresponding target condition judgment expressions based on the target column and the judgment logic; Eliminate the partition window function and the aggregate function in the database query statement, and change the database query statement to a query statement using the target condition judgment expression.

5. The method for optimizing database query statements according to claim 4, wherein, The logical decision-making aggregate functions include aggregate functions of type count and rank.

6. The method for optimizing database query statements according to claim 1, wherein, The partition window function is a window function that includes the `partition by` keyword and is used to partition data according to the partition reference column.

7. The method for optimizing database query statements according to claim 3, wherein, After the step of equivalently rewriting the database query statement according to the cluster type, the method further includes: Verify whether the result set returned by the rewritten optimized query statement is consistent with the result set returned by the original database query statement; If the result set is consistent, the optimized query statement is retained; If the result set is inconsistent, roll back the rewrite operation and output an optimization failure message.

8. A computer-readable storage medium having a computer program stored thereon, the computer program being executed by a processor to implement the steps of the method for optimizing a database query statement according to any one of claims 1 to 7.

9. A computer program product comprising a computer program that, when executed by a processor, implements the steps of the method for optimizing a database query statement according to any one of claims 1 to 7.

10. A computer device comprising a memory, a processor, and a machine-executable program stored in the memory and running on the processor, wherein the processor, when executing the machine-executable program, implements the steps of the method for optimizing a database query statement according to any one of claims 1 to 7.

Images