Query statement optimization method and device, equipment and storage medium

Abstract

One or more embodiments of the invention provide a query statement optimization method and device, equipment and a storage medium. The method comprises the following steps: identifying each aggregation function in grouped query statements; for each aggregation function, judging whether parameters of the aggregation function can be calculated by a grouping expression or not; if yes, obtaining the number of parameter values of the aggregation function in each group; and under the condition that only one parameter value exists, representing the aggregation function according to parameters in the aggregation function to obtain an optimized query statement. According to the method, redundant grouping aggregation functions are eliminated, a better execution plan is obtained on the premise of ensuring the query equivalence, the overhead of the query request is reduced, and the query execution efficiency is improved.

Description

Technical Field

[0001] One or more embodiments of the present specification relate to the field of computer technology, and in particular, to a query statement optimization method, apparatus, device, and storage medium. Background Art

[0002] Query statements are the main interface for users to interact with database systems. Users use query statements to request and operate data in the database. Grouping and aggregation functions are crucial query statements in data analysis. They allow users to classify and summarize large amounts of data and extract key indicators, which helps to extract valuable information from complex data, support decision-making, improve data processing efficiency, and provide a basis for data visualization.

[0003] However, when writing more complex grouping query statements, users may write redundant grouping aggregations, and the database needs to perform additional calculations, occupying additional CPU and memory resources to process these unnecessary operations, resulting in increased query overhead. In the case of large amounts of data, redundant grouping query statements may result in excessively long response times, thus affecting the performance of the entire system. Summary of the invention

[0004] In view of this, one or more embodiments of the present specification provide a query statement optimization solution to reduce the overhead of query request calls and improve query execution efficiency.

[0005] To achieve the above objectives, one or more embodiments of this specification provide the following technical solutions:

[0006] According to a first aspect of one or more embodiments of this specification, a query statement optimization method is proposed, including:

[0007] Identify the aggregate functions in grouped queries;

[0008] For each aggregate function, determine whether the parameters of the aggregate function can be calculated by the grouping expression;

[0009] If yes, then obtain the number of parameter values ​​of the aggregation function in each group;

[0010] When there is only one parameter value, the aggregate function is characterized according to the parameters in the aggregate function to obtain an optimized query statement.

[0011] In some embodiments, determining whether the parameters of the aggregate function can be calculated by a grouping expression includes:

[0012] In the case where the parameter of the aggregate function is a column in the grouping expression, or is calculated only by the column in the grouping expression, it is determined that the parameter of the aggregate function can be calculated by the grouping expression.

[0013] In some embodiments, the method further comprises:

[0014] Determine whether the data type of the output of the optimized query statement is consistent with the data type of the output of the query statement before optimization;

[0015] In case of inconsistency, type conversion is performed on the output of the optimized query statement.

[0016] In some embodiments, the grouping query statement is a SELECT statement and includes a GroupBy expression.

[0017] In some embodiments, the method further comprises:

[0018] Determine whether any of the keywords rollup, grouping_sets, and cube exists in the grouping query statement;

[0019] If not present, the aggregation function is represented by the parameters in the aggregation function.

[0020] According to a second aspect of one or more embodiments of this specification, a query statement optimization device is provided, including:

[0021] An identification unit, used to identify each aggregation function in a group query statement;

[0022] A judging unit, used for judging, for each aggregate function, whether a parameter of the aggregate function can be calculated by a grouping expression;

[0023] An acquiring unit, configured to acquire the number of parameter values ​​of the aggregate function in each group when the parameter can be calculated by a grouping expression;

[0024] The rewriting unit is used to characterize the aggregate function according to the parameters in the aggregate function when there is only one parameter value, so as to obtain an optimized query statement.

[0025] In some embodiments, the determining unit is specifically configured to:

[0026] In the case where the parameter of the aggregate function is a column in the grouping expression, or is calculated only by the column in the grouping expression, it is determined that the parameter of the aggregate function can be calculated by the grouping expression.

[0027] In some embodiments, the apparatus further comprises a conversion unit, configured to:

[0028] Determine whether the data type of the output of the optimized query statement is consistent with the data type of the output of the query statement before optimization;

[0029] In case of inconsistency, type conversion is performed on the output of the optimized query statement.

[0030] In some embodiments, the grouping query statement is a SELECT statement and includes a GroupBy expression.

[0031] In some embodiments, the device further comprises a removal unit, configured to:

[0032] Determine whether any of the keywords rollup, grouping_sets, and cube exists in the grouping query statement;

[0033] If not present, the aggregation function is represented by the parameters in the aggregation function.

[0034] According to a third aspect of one or more embodiments of this specification, an electronic device is provided, including:

[0035] processor;

[0036] a memory for storing processor-executable instructions;

[0037] The processor implements the steps of the method proposed in the above embodiment by running the executable instructions.

[0038] According to a fourth aspect of one or more embodiments of the present specification, a computer-readable storage medium is provided, on which computer instructions are stored, and when the instructions are executed by a processor, the steps of the method provided in the above embodiment are implemented.

[0039] According to a fifth aspect of one or more embodiments of the present specification, a computer program product is proposed, comprising a computer program / instruction, which implements the steps of the method proposed in the above embodiment when executed by a processor.

[0040] The query statement optimization scheme proposed in the embodiment of this specification, for each aggregate function in the group query statement, when the parameters can be calculated by the group expression and the number of parameter values ​​of the aggregate function in each group is only one, represents the aggregate function according to the parameters in the aggregate function, eliminates redundant group aggregate functions, obtains a better execution plan while ensuring query equivalence, reduces the overhead of query requests, and speeds up query execution efficiency. BRIEF DESCRIPTION OF THE DRAWINGS

[0041] Figure 1This is an application scenario diagram of a query statement optimization method provided by an exemplary embodiment.

[0042] Figure 2 It is a flowchart of a query statement optimization method provided by an exemplary embodiment.

[0043] Figure 3 This is a flowchart of another query statement optimization provided by an exemplary embodiment.

[0044] Figure 4 It is a block diagram of a code generating device provided by an exemplary embodiment.

[0045] Figure 5 It is a structural schematic diagram of a device provided by an exemplary embodiment. DETAILED DESCRIPTION

[0046] Exemplary embodiments will be described in detail herein, examples of which are shown in the accompanying drawings. When the following description refers to the drawings, the same numbers in different drawings represent the same or similar elements unless otherwise indicated. The implementations described in the following exemplary embodiments do not represent all implementations consistent with one or more embodiments of this specification. Instead, they are merely examples of devices and methods consistent with some aspects of one or more embodiments of this specification as detailed in the appended claims.

[0047] It should be noted that: in other embodiments, the steps of the corresponding method are not necessarily performed in the order shown and described in this specification. In some other embodiments, the steps included in the method may be more or less than those described in this specification. In addition, a single step described in this specification may be decomposed into multiple steps for description in other embodiments; and multiple steps described in this specification may be combined into a single step for description in other embodiments.

[0048] To help understand this specification, technical terms related to this specification are first introduced.

[0049] Query rewriting: rewrite a SQL query request into another SQL query request and ensure that the semantics of the two are completely equivalent.

[0050] Group By expression: also called grouping key. When using Group By syntax in SQL query, the query results will be grouped according to the value of Group By expression.

[0051] Aggregate function: A SQL syntax that uses values ​​from multiple rows to calculate a result. For example, the SUM function will sum the values ​​of multiple rows, and the MAX function will calculate the maximum value among the values ​​of multiple rows.

[0052] Group aggregation: When an aggregate function is used with the Group By syntax, the aggregate function is calculated independently in each group, otherwise it is calculated using all rows in the query result.

[0053] Distinct option: Some aggregate functions can specify the Distinct option to remove duplicate data during calculation. For example, if the Distinct option is specified when using the SUM function, duplicate values ​​will only be added up once in each group.

[0054] Projection column: An expression after the SELECT keyword and before the FROM keyword, used to describe the result columns that a SQL query requires to be returned.

[0055] HAVING clause: A SQL syntax that describes data filtering conditions. It is generally used together with group aggregation to filter the data in the group after the grouping is completed.

[0056] In specific business scenarios, users often need to group data in a table according to certain columns and apply certain aggregate functions to the grouped data to extract corresponding information.

[0057] For example, suppose that in the healthcare data table tb1, column c1 represents the patient's age, column c2 represents the blood pressure value, column c3 represents the patient ID, and column c4 represents the disease diagnosis result. If you need to identify which age groups and blood pressure levels have a larger patient population, or which combinations may be associated with specific health risks, the grouping query statement written by the user may be:

[0058] SELECT max(c1),min(c1+c2),count(distinct c3)FROM tbl GROUP BY c1,c2

[0059] Based on this query statement, the conventional algorithm is to first scan the entire tb1 table, divide each row of data into corresponding groups according to the value of the GroupBy expression (c1, c2), and then calculate the values ​​of max(c1), minx(c1+c2), and count(distinct c3) for the data in each group. However, in some cases, the value of the aggregate function may not need to be aggregated but can be directly obtained based on the parameters. In this case, the aggregate function is redundant, and executing the query request will result in unnecessary query overhead.

[0060] In order to solve such a problem, an embodiment of the present application proposes a query statement optimization solution, which eliminates redundant aggregation functions by rewriting group query statements to reduce the query request overhead and speed up query execution efficiency.

[0061] A query statement optimization method provided in an embodiment of the present application is described below.

[0062] like Figure 1 The figure shows an application scenario diagram of a query statement optimization method in an embodiment of the present application. Figure 1 The system includes: a service terminal 10 and a database 20 , wherein the database 20 at least includes a query optimizer 210 and an execution engine 220 .

[0063] The service terminal 10 may also be referred to as a client or application terminal, which is an interface for users to interact with the database 20. It may be a command line interface (CLI), a graphical user interface (GUI), or an application program that sends a query request through an application program interface (API). The user enters a query request in the service terminal 10, and the request is then sent to the database 20.

[0064] The query optimizer 210 in the database 20 is a part of the database management system. It receives the query request from the service terminal 10, first optimizes the query request, then analyzes the optimized query request, considers different execution strategies, and selects an optimal execution plan. This execution plan describes how the database executes the query to obtain the result, including which indexes to use, the connection order, the data retrieval method, etc.

[0065] The execution engine 220 in the database 20 is the part of the database management system that actually executes queries. It operates the database according to the execution plan generated by the query optimizer 210, including accessing data files, performing data operations (such as selection, projection, connection, aggregation, etc.), and returning query results to the service terminal 10.

[0066] The description in this application only details a single service terminal 10 and a database 20, but those skilled in the art should understand that the parts shown are intended to represent the operation of the service terminal 10 and the database 20 involved in the technical solution of this application, and do not imply that there is a limitation on the number, type or location of the service terminal 10 and the database 20. It should be noted that if additional modules are added to the illustrated environment or individual modules are removed therefrom, the underlying concepts of the exemplary embodiments of this application will not be changed.

[0067] It should be noted that the code generation method proposed in this application is not only applicable to Figure 1 The application scenario shown is also applicable to any device that requires query statement optimization.

[0068] Figure 2 A flowchart of a query statement optimization method provided by an embodiment of the present application is shown. Figure 1 The query optimizer executes in Figure 2As shown, the method includes:

[0069] Step 201, identifying each aggregate function in the group query statement.

[0070] In the embodiment of the present disclosure, the statement for query rewriting is a group query statement. The group query statement may be a SELECT statement, and the statement includes a GroupBy expression.

[0071] In some embodiments, the aggregate function in the group query statement may be identified in the following manner:

[0072] First, get the list of aggregate functions. That is, determine which functions are considered aggregate functions. Generally, aggregate functions include:

[0073] AVG(): calculates the average value;

[0074] COUNT(): count;

[0075] MAX(): find the maximum value;

[0076] MIN(): find the minimum value;

[0077] MEDIAN(): Find the median;

[0078] SUM(): sum;

[0079] GROUP_CONCAT(): Concatenates multiple values ​​into a single string.

[0080] Those skilled in the art should understand that the above aggregation functions are only examples and other types of aggregation functions may also be included, which is not limited in the present disclosure.

[0081] Next, determine the position of the SELECT keyword in the query statement, and start from the SELECT keyword and traverse each expression. If the expression starts with one of the above aggregate functions (e.g., COUNT), then identify that the expression is an aggregate function.

[0082] Step 202, determining whether the parameters of the aggregate function can be calculated by the grouping expression.

[0083] In this step, check the parameters (fields or expressions) used in the aggregate function to determine whether they depend only on the columns in the grouping expression. If the parameter of the aggregate function is a column in the grouping expression, or can be calculated only from the columns in the grouping expression, then this parameter can be calculated by the grouping expression. This calculation includes but is not limited to the four arithmetic operations, as well as commonly used deterministic operations. If the parameter of the aggregate function is not related to the grouping expression, such as GROUP BY department and the aggregate function SUM(employee_id), if employee_id is not part of the grouping expression, then this parameter cannot be calculated by the grouping expression. Or, if the parameter is obtained by non-deterministic calculation, for example, the parameter contains random values, then it is considered that the parameter cannot be calculated by the grouping expression.

[0084] Step 203: If the parameter of the aggregate function can be calculated by the grouping expression, the number of parameter values ​​of the aggregate function in each group is obtained.

[0085] When the grouping key contains the primary key, or there is a unique constraint on any grouping column, the grouping expression has a unique constraint, so that each group has only one column of data. However, when the grouping key does not have a unique constraint, each group may contain multiple rows of data. In this case, the number of different parameter values ​​of the aggregate function contained in these multiple rows of data is counted, that is, the number of duplicate or unique parameter values.

[0086] Step 204: When there is only one parameter value, characterize the aggregate function according to the parameters in the aggregate function to obtain an optimized query statement.

[0087] If the number of parameter values ​​is greater than one, it means that the group contains different parameter values; if the number is one, it means that the parameter values ​​in the group are the same, that is, the value of the aggregate function does not depend on the number of rows in the group.

[0088] When the number of parameter values ​​is one or there is only one parameter value, it can be rewritten as an equivalent expression that does not contain an aggregate function and is characterized according to the parameter value.

[0089] By traversing each aggregate function in the group query and executing steps 201 to 204 for each aggregate function, it is possible to rewrite all or part of the aggregate functions in the query request.

[0090] The query statement optimization scheme proposed in the embodiment of this specification, for each aggregate function in the group query statement, when the parameters can be calculated by the group expression and the number of parameter values ​​of the aggregate function in each group is only one, represents the aggregate function according to the parameters in the aggregate function, eliminates redundant group aggregate functions, obtains a better execution plan while ensuring query equivalence, reduces the overhead of query requests, and speeds up query execution efficiency.

[0091] In some embodiments, before rewriting the aggregate function, it is also possible to determine whether any of the rollup, grouping_sets, and cube keywords exist in the grouping query statement. If not, rewriting is performed; if so, rewriting is not performed.

[0092] This is because when rollup, grouping_sets, and cube are used in group query statements, these advanced grouping functions provide specific semantics and flexibility. If they are rewritten, the necessary combinations may not be generated, or unnecessary repeated calculations may be generated.

[0093] In some embodiments, after optimizing and rewriting the grouping query statement, it is also necessary to determine whether the data type of the output of the rewritten query statement is consistent with the data type of the output of the query statement before optimization; if not, the output of the optimized query statement is type-converted to ensure that the data type of the output of the rewritten statement meets the user's expectations.

[0094] The following describes in detail, with respect to a specific aggregate function, a method for performing equivalent rewriting when it is determined that the aggregate function is of a rewritable type.

[0095] Assume that the general form of an aggregate function is aggr(param), where aggr is the name of the aggregate function and param is the parameter of the aggregate function.

[0096] For MIN / MAX / MEDIAN(param), the rewritten expression is the parameter itself;

[0097] For BIT_OR(param), when the parameter has a non-NULL constraint, the rewritten expression is the parameter itself. If there is no non-NULL constraint, it can be rewritten as "case when param is not null then paramelse 0end". That is, if the parameter is not null (that is, it has a value), then the value of the parameter is used; if the parameter is null (that is, it has no value), then the number 0 is used as its value;

[0098] For BIT_AND(param), when the parameter has a non-NULL constraint, the rewritten expression is the parameter itself. If there is no non-NULL constraint, it can be rewritten as "case when param is not null then paramelse UINT64_MAX end". That is, if the parameter is not null (that is, it has a value), then the value of the parameter is used; if the parameter is null (that is, it has no value), then the maximum value of the 64-bit integer is used as its value;

[0099] For COUNT(DISTINCT param), when the parameter has a non-NULL constraint, it is rewritten as a constant 1. If there is no non-NULL constraint, it can be rewritten as case when param is not null then 1else 0end. That is, if the parameter is not null (that is, there is a value), it returns 1; if the parameter is null (that is, there is no value), it returns 0;

[0100] For SUM(DISTINCT param) / GROUP_CONCAT(DISTINCT param), when the parameter has a non-NULL constraint, the rewritten expression is the parameter itself. If there is no non-NULL constraint, it can be rewritten as "casewhen param is not null then param else null end". That is, if the parameter is not null (that is, there is a value), then the parameter value is used; if the parameter is null (that is, there is no value), NULL is returned;

[0101] For AVG (DISTINCT param), since the AVG function will be rewritten as SUN / COUNT in the preprocessing stage of rewriting, only when AVG has the DISTINCT option, it will be rewritten as SUM (DISTINCT) / COUNT (DISTINCT), and the numerator and denominator can be rewritten as the above forms respectively.

[0102] It should be noted that for the three aggregate functions COUNT / SUM / GROUP_CONCAT, if there is no DISTINCT option, the value of the function is not uniquely determined, and therefore cannot be rewritten according to the optimization method extracted in the embodiments of this specification.

[0103] The above-mentioned rewriting method of the aggregate function is only an example, and this specification does not intend to limit the specific type of the aggregate function to be rewritten.

[0104] References below Figure 3The process shown in the figure takes the group query statement "SELECT max(c1), min(c1+c2), count(distinct c3) FROM tbl GROUP BY c1, c2" as an example to describe in detail the query statement optimization method proposed in the embodiment of this specification.

[0105] First, determine whether the group query statement is a SELECT statement and contains a GroupBy expression. If it is determined to be a SELECT statement containing a GroupBy expression, determine whether any of the rollup, grouping_sets, and cube keywords does not exist in the query statement. If not, determine that the group query statement meets the prerequisite for the optimization rewrite proposed in the embodiment of this specification.

[0106] Next, identify the aggregate functions in the group query statement. In this example, the aggregate functions included can be identified as max(c1), min(c1+c2 ) , count(distinct c3).

[0107] For each identified aggregate function, determine whether the parameters of the aggregate function can be calculated by the grouping expression. In this example, the grouping expression is "GroupBy c1, c2", and the grouping key contains two columns: c1 and c2, that is, the query results will be grouped based on each unique combination of c1 and c2. For the aggregate function max(c1), the parameter c1 of the function is a column in the grouping expression, so it can be calculated by the grouping expression; for min(c1+c2), the parameter (c1+c2) of the function is calculated only by the columns in the grouping expression, so it can also be calculated by the grouping expression; for the aggregate function count(distinct c3), since the parameters of the function are not related to the grouping expression, it cannot be calculated by the grouping expression.

[0108] For aggregate functions that can be calculated by grouping expressions, further determine the number of their parameter values ​​in each group. If there is only one parameter value, the value of this aggregate function can also be uniquely determined, and the aggregate function expression can be simplified to a form that does not contain an aggregate function. For example, if the number of duplicate parameter values ​​in each group is one, the value of max(c1) is c1, and the value of min(c1+c2) is c1+c2.

[0109] Therefore, the above group query statement can be rewritten as:

[0110] "SELECT c1,c1+c2,count(distinct c3)FROM tbl GROUP BY c1,c2"

[0111] When the data output type of the rewritten grouping query statement is inconsistent with that of the statement before the rewrite, the output of the rewritten query statement is type-converted to make the output type consistent; if they are consistent, no type conversion is required, and the final optimized query statement is obtained.

[0112] In the embodiments of the present disclosure, the aggregate function that can be rewritten may appear at any position in the query, such as in a projection column, a subquery, or a HAVING clause.

[0113] See another example of a grouped query statement. The initial grouped query statement is as follows:

[0114]

[0115] In this query, the last table in the LEFT JOIN is a subquery. However, when the selectivity of the WHERE condition is high, the cost of this subquery is very high.

[0116] The query statement optimization method proposed in the embodiment of this specification identifies the aggregate functions MAX(d.item_id) and MAX(d.sid). When the grouping expression is d.sid, d.item_id, it can be seen that the parameters of these two aggregate functions can be calculated by the grouping expression, and the number of parameter values ​​of these two aggregate functions in each group is one. According to the rewriting method described above, in each group determined by the grouping key d.sid, d.item_id, the two aggregate functions MAX(d.item_id) and MAX(d.sid) in the projection column are equivalent to their parameters themselves.

[0117] Since the two projected columns are the grouping keys, GroupBy can be further rewritten as DISTINCT:

[0118] "SELECT DISTINCT

[0119] d.item_id AS item_id,

[0120] d.sid AS sid,

[0121] FROM

[0122] d

[0123] LEFT JOIN o ON d.oid=o.uuid

[0124] LEFT JOIN p ON d.sid=p.sid AND d.item_id=p.uuid

[0125] WHERE

[0126] o.status=1"

[0127] And because this subquery is used as the right table of LEFT JOIN, the columns referenced by the join condition t.sid = view.sid AND t.item_id = view.item_id are all DISTINCT columns, this join will not cause the increase or decrease of rows in the left table of LEFT JOIN, so this LEFT JOIN can actually be eliminated directly.

[0128] Finally, the entire query can be rewritten as:

[0129] SELECT t.*,p.info

[0130] FROM t LEFT JOIN p ON t.sid=p.sid AND t.item_id=p.uuid;

[0131] In the above rewriting process, the first step of eliminating redundant grouping aggregate functions is very critical, and it is a prerequisite for triggering subsequent optimization. In other words, if the optimizer can eliminate redundant grouping aggregate functions as much as possible, in addition to reducing the overhead of aggregate function calls and directly speeding up query execution efficiency, it can also further trigger other rewriting rules and indirectly speed up query execution efficiency. For example, eliminating aggregate functions in HAVING can further trigger filter pushdown; eliminating aggregate functions in projection columns can further trigger GroupBy to Distinct, etc.

[0132] Figure 4 FIG. 1 is a block diagram of a code generating device provided by an exemplary embodiment. Figure 4 As shown, the device includes: a code generating device, including:

[0133] An identification unit 401, used to identify each aggregation function in a group query statement;

[0134] A judging unit 402 is used to judge, for each aggregate function, whether a parameter of the aggregate function can be calculated by a grouping expression;

[0135] An acquiring unit 403 is used to acquire the number of parameter values ​​of the aggregation function in each group when the parameter can be calculated by a grouping expression;

[0136] The rewriting unit 404 is used to characterize the aggregation function according to the parameters in the aggregation function to obtain an optimized query statement when there is only one parameter value.

[0137] In some embodiments, the determining unit is specifically configured to:

[0138] In the case where the parameter of the aggregate function is a column in the grouping expression, or is calculated only by the column in the grouping expression, it is determined that the parameter of the aggregate function can be calculated by the grouping expression.

[0139] In some embodiments, the apparatus further comprises a conversion unit, configured to:

[0140] Determine whether the data type of the output of the optimized query statement is consistent with the data type of the output of the query statement before optimization;

[0141] In case of inconsistency, type conversion is performed on the output of the optimized query statement.

[0142] In some embodiments, the grouping query statement is a SELECT statement and includes a GroupBy expression.

[0143] In some embodiments, the device further comprises a removal unit, configured to:

[0144] Determine whether any of the keywords rollup, grouping_sets, and cube exists in the grouping query statement;

[0145] If not present, the aggregation function is represented by the parameters in the aggregation function.

[0146] Figure 5 is a schematic structural diagram of a device provided by an exemplary embodiment. Figure 5 At the hardware level, the device includes a processor 502, an internal bus 504, a network interface 506, a memory 508, and a non-volatile memory 510, and may also include hardware required for other services. One or more embodiments of this specification may be implemented based on software, such as the processor 502 reading the corresponding computer program from the non-volatile memory 510 into the memory 508 and then running it. Of course, in addition to the software implementation, one or more embodiments of this specification do not exclude other implementations, such as logic devices or a combination of software and hardware, etc., that is, the execution subject of the following processing flow is not limited to each logic unit, but can also be hardware or logic devices.

[0147] The systems, devices, modules or units described in the above embodiments may be implemented by computer chips or entities, or by products with certain functions. A typical implementation device is a computer, which may be in the form of a personal computer, a laptop computer, a cellular phone, a camera phone, a smart phone, a personal digital assistant, a media player, a navigation device, an email transceiver, a game console, a tablet computer, a wearable device or a combination of any of these devices.

[0148] In a typical configuration, a computer includes one or more processors (CPU), input / output interfaces, network interfaces, and memory.

[0149] The memory may include non-permanent storage in a computer-readable medium, random access memory (RAM) and / or non-volatile memory in the form of read-only memory (ROM) or flash RAM. The memory is an example of a computer-readable medium.

[0150] Computer-readable media include permanent and non-permanent, removable and non-removable media that can be used to store information by any method or technology. Information can be computer-readable instructions, data structures, program modules or other data. Examples of computer storage media include, but are not limited to, phase change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technology, compact disc read-only memory (CD-ROM), digital versatile disc (DVD) or other optical storage, magnetic cassettes, disk storage, quantum memory, graphene-based storage media or other magnetic storage devices or any other non-transmission media that can be used to store information that can be accessed by a computing device. As defined herein, computer-readable media does not include temporary computer-readable media (transitory media), such as modulated data signals and carrier waves.

[0151] It should also be noted that the terms "include", "comprises" or any other variations thereof are intended to cover non-exclusive inclusion, so that a process, method, commodity or device including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, commodity or device. In the absence of more restrictions, the elements defined by the sentence "comprises a ..." do not exclude the existence of other identical elements in the process, method, commodity or device including the elements.

[0152] The above is a description of a specific embodiment of the specification. Other embodiments are within the scope of the appended claims. In some cases, the actions or steps recorded in the claims can be performed in an order different from that in the embodiments and still achieve the desired results. In addition, the processes depicted in the drawings do not necessarily require the specific order or continuous order shown to achieve the desired results. In some embodiments, multitasking and parallel processing are also possible or may be advantageous.

[0153] The terms used in one or more embodiments of this specification are only for the purpose of describing specific embodiments, and are not intended to limit one or more embodiments of this specification. The singular forms of "a", "said" and "the" used in one or more embodiments of this specification and the appended claims are also intended to include plural forms, unless the context clearly indicates other meanings. It should also be understood that the term "and / or" used herein refers to and includes any or all possible combinations of one or more associated listed items.

[0154] It should be understood that although the terms first, second, third, etc. may be used to describe various information in one or more embodiments of this specification, these information should not be limited to these terms. These terms are only used to distinguish the same type of information from each other. For example, without departing from the scope of one or more embodiments of this specification, the first information may also be referred to as the second information, and similarly, the second information may also be referred to as the first information. Depending on the context, the word "if" as used herein may be interpreted as "at the time of" or "when" or "in response to determining".

[0155] The above description is merely a preferred embodiment of one or more embodiments of the present specification and is not intended to limit one or more embodiments of the present specification. Any modifications, equivalent substitutions, improvements, etc. made within the spirit and principles of one or more embodiments of the present specification shall be included in the scope of protection of one or more embodiments of the present specification.

Claims

1. A query statement optimization method, comprising: Identify the aggregate functions in grouped queries; For each aggregate function, determine whether the parameters of the aggregate function can be calculated by the grouping expression; If yes, then obtain the number of parameter values ​​of the aggregation function in each group; When there is only one parameter value, the aggregate function is characterized according to the parameters in the aggregate function to obtain an optimized query statement.

2. According to the method of claim 1, the step of determining whether the parameters of the aggregate function can be calculated by a grouping expression comprises: In the case where the parameter of the aggregate function is a column in the grouping expression, or is calculated only by the column in the grouping expression, it is determined that the parameter of the aggregate function can be calculated by the grouping expression.

3. The method according to claim 1, further comprising: Determine whether the data type of the output of the optimized query statement is consistent with the data type of the output of the query statement before optimization; In case of inconsistency, type conversion is performed on the output of the optimized query statement.

4. According to the method of claim 1, the grouping query statement is a SELECT statement and contains a GroupBy expression.

5. The method according to claim 1, further comprising: Determine whether any of the keywords rollup, grouping_sets, and cube exists in the grouping query statement; If not present, the aggregation function is represented by the parameters in the aggregation function.

6. A query statement optimization device, comprising: An identification unit, used to identify each aggregation function in a group query statement; A judging unit, used for judging, for each aggregate function, whether a parameter of the aggregate function can be calculated by a grouping expression; An acquiring unit, configured to acquire the number of parameter values ​​of the aggregate function in each group when the parameter can be calculated by a grouping expression; The rewriting unit is used to characterize the aggregate function according to the parameters in the aggregate function when there is only one parameter value, so as to obtain an optimized query statement.

7. The device according to claim 6, wherein the judging unit is specifically configured to: In the case where the parameter of the aggregate function is a column in the grouping expression, or is calculated only by the column in the grouping expression, it is determined that the parameter of the aggregate function can be calculated by the grouping expression.

8. An electronic device comprising: processor; a memory for storing processor-executable instructions; The processor implements the method according to any one of claims 1 to 5 by running the executable instructions.

9. A computer-readable storage medium having computer instructions stored thereon, which, when executed by a processor, implement the steps of the method according to any one of claims 1 to 5.

10. A computer program product, comprising a computer program / instruction, which, when executed by a processor, implements the steps of the method according to any one of claims 1 to 5.

Images