CN108959571B

CN108959571B - SQL statement operation method and device, terminal equipment and storage medium

Info

Publication number: CN108959571B
Application number: CN201810725679.9A
Authority: CN
Inventors: 张钦; 朱仲颖; 万伟
Original assignee: Shanghai Dameng Database Co Ltd
Current assignee: Shanghai Dameng Database Co Ltd
Priority date: 2018-07-04
Filing date: 2018-07-04
Publication date: 2020-12-04
Anticipated expiration: 2038-07-04
Also published as: CN108959571A

Abstract

The invention discloses an operation method and device of SQL sentences, terminal equipment and a storage medium. The method comprises the following steps: splitting an expression to be analyzed in an SQL statement according to the operator priority to obtain sub-expressions and the operation priority of each sub-expression; substituting the independent variable number value of the current sub-expression into the operation priority to obtain a current operation result, wherein the independent variable number value comprises an unoperated number value and/or an operation result of a previous sub-expression; and continuously substituting the self-variable number value of the next sub-expression and then calculating to obtain a next operation result until the self-variable number value of the expression to be analyzed is substituted and then calculating to obtain the operation result of the SQL statement. By using the method, the operation efficiency of the expression to be analyzed can be effectively improved.

Description

SQL statement operation method and device, terminal equipment and storage medium

Technical Field

The embodiment of the invention relates to the technical field of data processing, in particular to an operation method and device of SQL (structured query language) statements, terminal equipment and a storage medium.

Background

Structured Query Language (SQL), a database operating Language, is used to access data and Query, update, and manage relational database systems.

When the existing database management system calculates the expression to be analyzed in the SQL sentence, all values of the independent variables are sequentially substituted into the expression to be analyzed to be directly calculated. Therefore, when the expression to be analyzed contains repeated sub-expressions, repeated calculation is carried out on the repeated sub-expressions, the calculation efficiency is low, and the use experience of a user is reduced.

Disclosure of Invention

The operation method, the operation device, the terminal equipment and the storage medium of the SQL statement can improve the execution performance of the expression in the SQL statement.

In a first aspect, an embodiment of the present invention provides an operation method of an SQL statement, including:

splitting an expression to be analyzed in an SQL statement according to the operator priority to obtain sub-expressions and the operation priority of each sub-expression;

substituting the independent variable number value of the current sub-expression into the operation priority to obtain a current operation result, wherein the independent variable number value comprises an unoperated number value and/or an operation result of a previous sub-expression;

and continuously substituting the self-variable number value of the next sub-expression and then calculating to obtain a next operation result until the self-variable number value of the expression to be analyzed is substituted and then calculating to obtain the operation result of the SQL statement.

In a second aspect, an embodiment of the present invention further provides an apparatus for calculating an SQL statement, including:

the splitting module is used for splitting the expression to be analyzed in the SQL statement according to the operator priority to obtain the sub-expressions and the operation priority of each sub-expression;

the current result operation module is used for substituting the independent variable number values of the current sub-expressions into the operation priority and then performing operation to obtain a current operation result, wherein the independent variable number values comprise the non-operated values and/or the operation results of the previous sub-expressions;

and the next result operation module is used for continuously substituting the self-variable value of the next sub-expression and then performing operation to obtain a next operation result until the self-variable value of the expression to be analyzed is substituted and then performing operation to obtain the operation result of the SQL statement.

In a third aspect, an embodiment of the present invention further provides a terminal device, including:

one or more processors;

storage means for storing one or more programs;

the one or more programs are executed by the one or more processors, so that the one or more processors implement the operation method of the SQL statement provided by the embodiment of the invention.

In a fourth aspect, an embodiment of the present invention further provides a computer-readable storage medium, on which a computer program is stored, where the computer program, when executed by a processor, implements the operation method of the SQL statement provided in the embodiment of the present invention.

The embodiment of the invention provides an operation method, an operation device, terminal equipment and a storage medium of SQL sentences. When the expression to be analyzed in the SQL statement is calculated, the expression to be analyzed may be split according to the operator priority, and each sub-expression included in the expression to be analyzed and the operation priority corresponding to each sub-expression are split. Then, selecting a current sub-expression from each sub-expression according to the operation priority, and substituting the self-variable value of the current sub-expression into the current sub-expression for operation to obtain a current operation result; and after the operation is finished, continuously substituting the independent variable number value of the next sub-expression into the next sub-expression for operation to obtain a next operation result, and calculating to obtain the operation result of the SQL statement after substituting the independent variable number value of the expression to be analyzed. By using the technical scheme, the operation amount of the repeated sub-expressions in the expression to be analyzed can be effectively reduced, and the operation efficiency of the expression to be analyzed is improved. When the number of the initial independent variables of the expression to be analyzed is multiple, the multiple sets of independent variable values can be directly substituted until the operation result of the SQL statement is obtained, so that the cost of switching back and forth among different operations is further effectively reduced, and the execution performance of the SQL statement containing the expression is greatly improved.

Drawings

Fig. 1 is a schematic flow chart illustrating an operation method of an SQL statement according to an embodiment of the present invention;

fig. 2 is a schematic flow chart illustrating an operation method of an SQL statement according to a second embodiment of the present invention;

fig. 3 is a schematic structural diagram of an SQL statement operation device according to a third embodiment of the present invention;

fig. 4 is a schematic structural diagram of a terminal device according to a fourth embodiment of the present invention.

Detailed Description

The present invention will be described in further detail with reference to the accompanying drawings and examples. It is to be understood that the specific embodiments described herein are merely illustrative of the invention and are not limiting of the invention. It should be further noted that, for the convenience of description, only some of the structures related to the present invention are shown in the drawings, not all of the structures.

Before discussing exemplary embodiments in more detail, it should be noted that some exemplary embodiments are described as processes or methods depicted as flowcharts. Although a flowchart may describe the operations (or steps) as a sequential process, many of the operations can be performed in parallel, concurrently or simultaneously. In addition, the order of the operations may be re-arranged. The process may be terminated when its operations are completed, but may have additional steps not included in the figure. The processes may correspond to methods, functions, procedures, subroutines, and the like.

Example one

Fig. 1 is a schematic flow chart of an operation method of an SQL statement according to an embodiment of the present invention, where the method is applicable to a case of calculating an expression in the SQL statement, and the method can be executed by an operation apparatus of the SQL statement, where the apparatus can be implemented by software and/or hardware and is generally integrated on a terminal device, and in this embodiment, the terminal device includes but is not limited to: computer, mobile phone or personal digital assistant.

Structured Query Language (SQL), a special purpose programming Language, is a database Query and programming Language for accessing data and querying, updating, and managing relational database systems; and is also an extension of the database script file. The SQL statements are numerous in variety and number, and many statements are often used. When the existing database management system calculates the expression to be analyzed in the SQL sentence, each value of the data is gradually substituted into the expression to be analyzed for calculation. When data is stored in the data table, the data in the data table needs to be substituted into the expression to be analyzed row by row for calculation, so that the calculation efficiency is low, and switching needs to be performed between different operations.

As shown in fig. 1, a method for operating an SQL statement according to an embodiment of the present invention includes the following steps:

s101, splitting the expression to be analyzed in the SQL statement according to the operator priority to obtain the sub-expressions and the operation priority of each sub-expression.

In this embodiment, the expression to be parsed may be understood as an expression to be calculated in the SQL statement. Operator priority may be understood as a criterion that determines the order of execution of operations in an expression to be resolved. The sub-expressions may be understood as expressions included in the expression to be parsed. The operation priority can be understood as a criterion for determining the operation order of each sub-expression.

The expression to be analyzed may contain a plurality of data objects with different data types which are connected by different operators; because the expression to be analyzed has a plurality of operations, different operation sequences may obtain different results. When the expression to be analyzed contains various operations, the operations must be combined in a certain sequence, so that the reasonability of the operations and the correctness and uniqueness of the result can be ensured. The operator priority determines the execution sequence of the operations in the expression to be analyzed.

When the expression to be analyzed is split based on the operator priority, the expression to be analyzed can be split based on the minimum operation unit of the expression to be analyzed and the operator priority; the splitting degree of the expression to be analyzed can be determined according to actual requirements. The operation priority of each sub-expression can be determined based on the split sub-expressions and the operator priority.

For example, assume that the SQL statement is SELECT FROM T1 WHERE C1/2> C2; wherein, the expression "C1/2 > C2" is to be analyzed in the WHERE clause, table 1 is a table for taking values of C1 and C2, wherein C1 and C2 are two columns of table 1.

C1	C2
		1	0
2	1

TABLE 1 value-taking tables for C1 and C2

As can be seen from Table 1, the values of C1 and C2 are (10) and (21). When the existing calculation mode of the expression to be analyzed is adopted, each row of data value needs to be substituted into the expression to be analyzed to obtain an operation result, specifically:

the second row (10) can be substituted into C1/2> C2 to obtain the operation result of the expression to be analyzed. Specifically, the method comprises the following steps: firstly, substituting C1-1 into C1/2 to obtain 0.5; then, the operation result 0.5 of C2 ═ 0 and C1/2 is substituted into the calculation, that is, 0.5>0, and the operation result of the expression to be analyzed is TRUE (TRUE).

And after the values of C1 and C2 are calculated to be (10), substituting the third row (21) into C1/2> C2 to obtain the operation result of the expression to be analyzed. Specifically, the method comprises the following steps: firstly, substituting C1-2 into C1/2 to obtain 1; then, the operation result 1 of C2 ═ 1 and C1/2 is substituted into the calculation, that is, 1>1, to obtain that the operation result of the expression to be analyzed is FALSE (FALSE).

In this step, before calculating the expression to be analyzed, the expression to be analyzed may be split, for example, the expression to be analyzed may be split into sub-expressions C1/2 and C1/2> C2. Wherein, C1/2> C2 is an expression to be analyzed, which can also be regarded as a sub-expression. For example, when each data is stored in a data table, the sub-expressions C1/2 and C1/2> C2 may be respectively arranged as temporary columns. Table 2 is a split table of each sub-expression.

C1	C2	COL3(C1/2)	COL4(C1/2>C2)
				1	0	0.5	TRUE
2	1	1	FALSE

TABLE 2 splitting tables for sub-expressions

As can be seen from table 2, in the embodiment, when the expression to be analyzed is calculated, the expression to be analyzed may be split first, and if there are the same sub-expressions, the same sub-expressions may be combined into a column for calculation, which only needs to be calculated once. In addition, when the present embodiment adopts batch calculation, the values (10) and (21) of C1 and C2 may be calculated simultaneously. Specifically, the method comprises the following steps:

the result of operation 0.5 is obtained by substituting (10) into COL3(C1/2), and then the result of operation 1 is obtained by substituting (21) into COL3 (C1/2). After the calculation results of the C1 and C2 in the COL3(C1/2) are (10) and (21), when the values of C1 and C2 are (10) and (21), the calculation results of the COL4(C1/2> C2) are calculated. The calculation can be performed based on the operation result of COL3 (C1/2). If C2 is 0 and C1/2 is 0.5, i.e. 0.5>0, the result is TRUE. Substituting C2 ═ 1 and C1/2 ═ 1, that is, 1>1, gave a FALSE result.

In summary, when the values of C1 and C2 are calculated as (10) and (21), if the calculation method of the existing expression to be analyzed is adopted, switching between different operations needs to be performed for 3 times; the calculation in this embodiment only needs to be switched 1 time. It can be understood that, when the values of C1 and C2 are increased or the expression to be analyzed is a complex expression, the calculation direction in this embodiment can greatly save the overhead of switching between operations compared to the calculation method of the existing expression to be analyzed.

In this step, after the expression to be analyzed is split, operation can be performed based on each split sub-expression, and finally an operation result of the expression to be analyzed is obtained.

S102, substituting the independent variable number values of the current sub-expression according to the operation priority and then operating to obtain a current operation result, wherein the independent variable number values comprise the non-operated number values and/or the operation result of the previous sub-expression.

In the present embodiment, the current sub-expression may be understood as a sub-expression to be operated on, which is selected from the sub-expressions according to the operation priority. The independent variable value can be understood as the value of each variable in the expression to be analyzed. The arguments may include, among other things, the initial arguments that were not computed and/or the previous sub-expressions. The argument value of the current sub-expression may be understood as the value of the argument contained in the current sub-expression. The current operation result can be understood as the operation result of the current sub-expression. An un-operated value is understood to be the value of the initial argument in the sub-expression that has not been operated on. A previous sub-expression may be understood as a sub-expression that is computed before the current sub-expression.

After each sub-expression is split, the current sub-expression can be operated according to the operation priority in the step, and the current operation result is obtained. Specifically, the current operation result may be obtained by substituting the argument value of the current sub-expression into the current sub-expression. The argument value of the current sub-expression may include an un-operated value and/or an operation result of the previous sub-expression, since the current sub-expression may include the previous sub-expression.

For example, assuming that the expression to be resolved is C1/2+ C1/2> C2, the expression to be resolved may be split into sub-expressions C1/2, C1/2+ C1/2, and C1/2+ C1/2> C2. The operation order of each sub-expression according to the operation priority can be C1/2, C1/2+ C1/2 and C1/2+ C1/2> C2. When the current sub-expression is C1/2+ C1/2, the arguments of the current sub-expression may include the previous sub-expression C1/2. Therefore, when the current operation result of the current sub-expression C1/2+ C1/2 is calculated, the operation result of the previous sub-expression C1/2 can be directly substituted into the current sub-expression operation to obtain the current operation result.

From this to-be-resolved expression (C1/2+ C1/2> C2), it can be seen that C1/2 requires two passes of computation when the initial arguments are substituted directly into the to-be-resolved expression. When the method of the embodiment is adopted, when the expression to be analyzed is analyzed, two C1/2 are corresponding to one sub-expression, and only one calculation is needed.

S103, continuously substituting the independent variable number value of the next sub-expression and then calculating to obtain a next operation result, and calculating to obtain the operation result of the SQL statement after substituting the independent variable number value of the expression to be analyzed.

In the present embodiment, the next sub-expression may be understood as a sub-expression that is operated after the current sub-expression according to the operation priority. The value of the argument of the next sub-expression can be understood as the value of the argument comprised by the next sub-expression. The next operation result can be understood as the operation result of the next sub-expression. The value of the independent variable of the expression to be analyzed can be understood as the value of the independent variable contained in the expression to be analyzed.

Based on the above example of the expression to be resolved being C1/2+ C1/2> C2, when the current sub-expression is C1/2+ C1/2, the next sub-expression may be C1/2+ C1/2> C2. Accordingly, the arguments of the next sub-expression may include the un-computed C2 and the previous sub-expression C1/2+ C1/2. When the next sub-expression C1/2+ C1/2> C2 is calculated, the value of the un-calculated C2 and the operation result of the previous sub-expression C1/2+ C1/2 can be substituted to obtain the next operation result. It can be seen that the next sub-expression is the expression to be analyzed, so the next operation result is the operation result of the SQL statement.

In the operation method of the SQL statement provided in the embodiment of the present invention, when the expression to be analyzed in the SQL statement is calculated, the expression to be analyzed may be split according to the operator priority, and each sub-expression included in the expression to be analyzed and the operation priority corresponding to each sub-expression are split. Then, selecting a current sub-expression from each sub-expression according to the operation priority, and substituting the self-variable value of the current sub-expression into the current sub-expression for operation to obtain a current operation result; and after the operation is finished, continuously substituting the independent variable number value of the next sub-expression into the next sub-expression for operation to obtain a next operation result, and calculating to obtain the operation result of the SQL statement after substituting the independent variable number value of the expression to be analyzed. By using the method, the operation amount of the repeated sub-expressions in the expression to be analyzed can be effectively reduced, and the operation efficiency of the expression to be analyzed is improved. When the number of the initial independent variables of the expression to be analyzed is multiple, the multiple sets of independent variable values can be directly substituted until the operation result of the SQL statement is obtained, so that the cost of switching back and forth among different operations is further effectively reduced, and the execution performance of the SQL statement containing the expression to be analyzed is greatly improved.

Example two

Fig. 2 is a schematic flow chart of an operation method of an SQL statement according to a second embodiment of the present invention, and the second embodiment performs optimization based on the foregoing embodiments. In this embodiment, the current operation result is obtained by substituting the argument value of the current sub-expression according to the operation priority, and is further embodied as: reading a preset number of independent variable values from the un-operated independent variable values; and sequentially substituting the autovariable values of the current sub-expressions in the autovariable values of the preset number according to the operation priority to obtain a current operation result.

Further, in this embodiment, the next operation result is obtained by continuously substituting the independent variable value of the next sub-expression, until the operation result of the SQL statement is obtained by substituting the independent variable value of the expression to be analyzed, which is further optimized as: and continuously substituting the autovariable values of the next sub-expression in the autovariable values of the preset number for operation to obtain a next operation result, and calculating to obtain the operation result of the SQL statement after substituting the autovariable values of the expressions to be analyzed in the autovariable values of the preset number.

On the basis of the optimization, after the next operation is obtained by substituting the next argument value of the next sub-expression in the preset number of argument values until the operation result of the SQL statement is obtained by substituting the argument value of the expression to be analyzed in the preset number of argument values, the optimization includes: and continuously reading the self-variable values of the preset number from the non-operated self-variable values and executing the operation of the current operation result until the operation of the self-variable values is finished.

Further, the optimization of the embodiment further includes: and if error information is received when the current operation result is calculated and/or the next operation result is calculated, directly substituting the numerical value of the initial independent variable of the expression to be analyzed into the expression to be analyzed, and then calculating to obtain the operation result of the SQL statement. Please refer to the first embodiment for a detailed description of the present embodiment.

As shown in fig. 2, a second embodiment of the present invention provides an operation method of an SQL statement, including the following steps:

s201, splitting the expression to be analyzed in the SQL statement according to the operator priority to obtain the sub-expressions and the operation priority of each sub-expression.

S202, reading a preset number of independent variable values from the un-operated independent variable values.

In this embodiment, the value of the non-operated argument may be understood as a value of an argument that is not substituted into the initial argument to obtain the operation result of the expression to be analyzed.

After each sub-expression in the expression to be analyzed is split, the preset number of independent variable values can be read from the un-operated independent variable values in the step. The preset number may be a preset number. The preset number may be set according to the number of sets of the initial argument. Illustratively, C1 and C2 in table 2 are the initial arguments, the number of sets of which is two. That is, the values of the initial arguments are (10) and (21). For table 2, the expression to be resolved is C1/2> C2, the corresponding initial arguments thereof, i.e. the variables it contains, i.e. C1 and C2.

If the number of the sets of the initial argument is M sets, wherein M is a positive integer. This step may read a predetermined number (e.g., N groups, N being a positive integer less than M) of argument values from the un-computed argument values. And then substituting the read self-variable values of the preset number into each sub-expression until the operation result of the SQL statement is obtained through batch operation.

Illustratively, the values of C1 and C2 in table 2 are expanded, see table 3, where table 3 is an expansion table of the values of C1 and C2.

C1	C2	COL3(C1/2)	COL4(C1/2>C2)
				1	0
2	1
				2	2
1	1

TABLE 3C 1 and C2 value expansion tables

As can be seen from table 3, the number of sets of initial arguments C1 and C2 is 4: (10) and (21), (22), and (11), where the preset number may be set to 2. At this time, assuming that none of the 4 sets of data has been calculated, the values of the arguments read in this step may be (10) and (21). The two sets of data may be batch operated upon after reading.

Further, reading a preset number of argument values from the non-computed argument values, comprising: if the group number of the non-operated autovariable values is greater than or equal to the preset number, reading the preset number of autovariable values from the non-operated autovariable values; otherwise, reading all the non-operated autovariable values as the autovariable values of the preset number.

It can be understood that, when reading the preset number of independent variable values from the un-operated independent variable values, it may be determined whether the number of sets of the un-operated independent variable values is greater than or equal to the preset number, and if so, the preset number of independent variable values may be read from the set of un-operated independent variable values for batch operation; otherwise, it can be stated that the number of groups of the currently uneconomical independent variable values is smaller than the preset number, and when the data is read this time, all uneconomical independent variable values can be directly read as the preset number of independent variable values to perform batch operation.

And S203, substituting the autovariable values of the current sub-expressions in the autovariable values of the preset number in sequence according to the operation priority to obtain a current operation result.

After reading the preset number of independent variable values from the un-operated independent variable values, the independent variable values of the current sub-expression in the preset number of independent variable values can be substituted into the current sub-expression for operation to obtain the current operation result. It should be noted that the current operation result obtained at this time is an operation result corresponding to a preset number of independent variable values.

Based on the example in table 3, when the preset number of argument values read from the un-operated argument values are (10) and (21), this step may sequentially substitute argument values C1 ═ 1 and C1 ═ 2 of the current sub-expression C1/2 in (10) and (21) into the current sub-expression operation to obtain the current operation result when calculating the current sub-expression C1/2, where the current operation result is shown in table 4, and table 4 is the current operation result table when the argument values are (10) and (21).

C1	C2	COL3(C1/2)	COL4(C1/2>C2)
				1	0	0.5
2	1	1
				2	2
1	1

TABLE 4 Current operation result Table with argument values of (10) and (21)

As can be seen from table 4, the current operation results when the argument values (10) and (21) have been calculated at this time, and based on this, the operation results of the next sub-expressions when the argument values (10) and (21) are calculated can be calculated.

And S204, continuously substituting the autovariable values of the next sub-expression in the autovariable values in the preset number for operation to obtain a next operation result, and calculating to obtain the operation result of the SQL statement after substituting the autovariable values of the expression to be analyzed in the autovariable values in the preset number.

After the corresponding current operation result is obtained based on the preset number of independent variable values, the step may continue to substitute the independent variable value of the next sub-expression in the preset number of independent variable values into the next sub-expression to obtain the next operation result.

Based on the example of table 4, after determining that the current operation results 0.5 and 1 of the current sub-expression C1/2 are completed, this step may sequentially set the number of arguments C2 of the next sub-expression C1/2> C2 in (10) and (21) to 0, C1/2 to 0.5; substituting C2 to 1 and C1/2 to 1, and calculating to obtain the next calculation result TRUE and FALSE. At this time, the operation results of the SQL statements when the argument values are (10) and (21) are obtained, see table 5, where table 5 is the next operation result when the argument values are (10) and (21).

C1	C2	COL3(C1/2)	COL4(C1/2>C2)
				1	0	0.5	TRUE
2	1	1	FALSE
				2	2
1	1

TABLE 5 results of the next operation when the values of the variables are (10) and (21)

As can be seen from table 5, the operation of the expression to be resolved when the number of arguments is (10) and (21) has been completed. The present embodiment may continue to read a predetermined number of argument values from the current unexecuted argument values for operation.

S205, judging whether error information is received or not when the current operation result and/or the next operation result is calculated, if so, executing S206; if not, go to S207.

Since the operation results of the sub-expressions in this embodiment are calculated in advance, the sub-expressions that do not need to be calculated may be calculated in advance in some expressions to be analyzed. If there is an error in the calculation, the error may not need to be returned to the user. However, the batch operation method in this embodiment cannot determine this. Therefore, when performing batch calculation, if an error occurs, the batch of data (i.e. the selected argument values of the preset number) is converted into non-batch operation, and S206 can be performed; otherwise, it can be stated that no error occurs when the operation result of the SQL statement corresponding to the argument values of the preset number is calculated, S207 may be executed.

S206, directly substituting the numerical value of the initial independent variable of the expression to be analyzed into the expression to be analyzed, and then calculating to obtain the operation result of the SQL statement.

When error information is received when the current operation result is calculated and/or the next operation result is calculated, the numerical value of the initial independent variable in the expression to be analyzed can be directly substituted into the expression to be analyzed for direct operation to obtain the operation result of the SQL statement. It should be noted that when the batch operation obtains the operation result of the SQL statement, the initial argument may refer to a preset number of arguments that generate an error.

For example, assuming that the expression to be analyzed is CASE WHEN C1>1THEN C1/C2 ELSE C2/C1END, when a batch operation is performed by reading a preset number of self-variable values from un-operated self-variable values, the operation results of the sub-expressions C1>1, C1/C2 and C2/C1 can be calculated first until the operation results of CASE WHEN C1>1THEN C1/C2 ELSE C2/C1END are calculated.

Table 6 is an operation result table. Assume that the preset number of read autovariables are (10) and (21).

Table 6 operation result table

As can be seen from table 6, when the sub-expression C1/C2 is calculated, a divide-by-zero error is generated, but this error does not need to be returned to the user, because when the initial argument C1 is calculated to be 1 and C2 is 0, the result 0 of C2/C1 is returned to the FALSE for C1>1 in the expression to be resolved CASE WHEN C1>1THEN C1/C2 ELSE C2/C1END, and the result of C1/C2 does not need to be calculated. Therefore, the self variable quantity values (10) and (21) in the expression to be analyzed can be directly substituted into the expression to be analyzed, and then the operation results 0 and 2 of the SQL statement can be directly obtained through operation.

Furthermore, if the expression to be parsed in Table 6 is modified to CASE WHEN C1>0THEN C1/C2 ELSE C2/C1END, an error should be reported to the user. In this case, errors occur in both batch calculation and non-batch calculation. However, in this embodiment, when an error occurs in the batch operation, the batch operation is switched to the non-batch mode to calculate the batch data, and whether the error is reported to the user is determined by the non-batch calculation mode. The batch operation may be understood as reading a preset number of independent variable values from the un-operated independent variable values until the independent variable values are all operated. The non-batch operation may be understood as adopting the operation in S206, that is, directly substituting the value of the initial argument into the expression to be analyzed to obtain the operation result of the SQL statement.

S207, judging whether the independent variable values are all operated, if so, executing S208; if not, go to S202.

When no error reporting information is received or after error reporting information is received, the numerical value of the initial independent variable is substituted into the expression to be analyzed for operation to obtain the SQL operation result, and the step can further judge whether the numerical values of the independent variables are all operated. I.e. whether there are any more uncommitted argument values. If the average operation is completed, S208 may be executed; otherwise, the process may return to step S202, and continue to read the preset number of argument values from the un-computed argument values until the argument values are all completely computed. Based on table 5, the values of the non-operated arguments are (22) and (11), and two groups are continuously selected this time, i.e. the values (20) and (11) are continuously calculated for the current operation result and the next operation result, the results after calculation are shown in table 7, and table 7 is the final result table.

TABLE 7 Final results Table

As can be seen from table 7, all values of the initial arguments C1 and C2 of the expression C1/2> C2 to be resolved are calculated.

And S208, ending.

And when the independent variables are judged to be operated completely, the operation of the expression to be analyzed can be ended.

The operation method of the SQL statement provided by the embodiment of the invention embodies the operation of the current operation result and the operation of the next operation result; the optimization also comprises error reporting processing operation and operation completion determining operation. By using the method, the operation amount of repeated sub-expressions in the expression to be analyzed can be effectively reduced, and the operation efficiency of the expression to be analyzed is improved. When the expression to be analyzed is operated, the number of the self-variables which are not operated is operated in batch, so that the cost of switching the operation instruction of the computer back and forth among different operations is reduced, and the execution performance of the SQL statement containing the expression can be greatly improved.

More specifically, the specific implementation process of the operation method of the SQL statement in this embodiment may be as follows:

taking the data stored in the form of a table as an example, when the expression in the SQL statement is calculated, the N rows of data (N is a preset value) in the table are taken out each time to perform expression calculation on the N rows of data at one time, so that the overhead of switching the operation instructions of the computer back and forth between different operations is reduced, and the execution performance of the SQL statement including the expression to be analyzed can be greatly improved. Such as:

analyzing an expression to be analyzed, splitting the expression to be analyzed according to operator priority calculated by the expression to be analyzed, and setting a temporary column for an intermediate result (each sub-expression) according to the sequence of calculation priority (operation priority);

secondly, if the number of rows of unread data in the table is larger than N, reading N pieces of data from the table according to a preset batch value N, and executing the third step; if the number of unread data lines is less than N, reading all unread data, and turning to the third step; if not, the calculation is finished and quit is finished;

and thirdly, performing batch operation on all the data read in the second step according to the analysis result of the first step. If there is error in the middle, go to the fourth step; otherwise, the second step is carried out after the batch operation is finished;

and fourthly, calculating the batch of data in a non-batch mode, and then turning to the second step.

EXAMPLE III

Fig. 3 is a schematic structural diagram of an SQL statement operation apparatus according to a third embodiment of the present invention, where the apparatus is applicable to a case of calculating an expression in an SQL statement, and the apparatus may be implemented by software and/or hardware and is generally integrated on a terminal device.

As shown in fig. 3, the SQL statement calculation device includes: a splitting module 31, a current result operation module 32 and a next result operation module 33.

The splitting module 31 is configured to split an expression to be analyzed in an SQL statement according to an operator priority to obtain sub-expressions and operation priorities of the sub-expressions;

a current result operation module 32, configured to substitute a self-variable value of a current sub-expression into the operation priority and then perform an operation to obtain a current operation result, where the self-variable value includes an unexecuted value and/or an operation result of a previous sub-expression;

and a next result operation module 33, configured to continue to substitute the independent variable value of the next sub-expression and then perform operation to obtain a next operation result, until the independent variable value of the expression to be analyzed is substituted and then perform operation to obtain the operation result of the SQL statement.

In this embodiment, the operation device of the SQL statement first splits the expression to be analyzed in the SQL statement by the splitting module 31 according to the operator priority to obtain the sub-expressions and the operation priority of each sub-expression; then, the current result operation module 32 substitutes the independent variable number values of the current sub-expressions according to the operation priority to obtain the current operation result, wherein the independent variable number values comprise the non-operated number values and/or the operation results of the previous sub-expressions; and finally, continuously substituting the independent variable value of the next sub-expression by the next result operation module 33 to obtain the next operation result, and calculating until the independent variable value of the expression to be analyzed is substituted to obtain the operation result of the SQL statement.

The embodiment provides an operation device of an SQL statement, which can effectively reduce the operation amount of repeated sub-expressions in an expression to be analyzed, and improve the operation efficiency of the expression to be analyzed. When the number of the initial independent variables of the expression to be analyzed is multiple, the multiple sets of independent variable values can be directly substituted until the operation result of the SQL statement is obtained, so that the cost of switching back and forth among different operations is further effectively reduced, and the execution performance of the SQL statement containing the expression is greatly improved.

Further, the current result operation module 32 includes: a value reading unit for reading a preset number of independent variable values from the non-operated independent variable values; and the current result operation unit is used for sequentially substituting the independent variable values of the current sub-expressions in the preset number of independent variable values according to the operation priority to obtain a current operation result.

On the basis of the above optimization, the next result operation module 33 is specifically configured to: and continuously substituting the autovariable values of the next sub-expression in the autovariable values of the preset number for operation to obtain a next operation result, and calculating to obtain the operation result of the SQL statement after substituting the autovariable values of the expressions to be analyzed in the autovariable values of the preset number.

Based on the technical scheme, the SQL statement operation device further comprises the following optimization steps: and the operation completion determining module 34 is configured to continue to read a preset number of independent variable values from the unaperated independent variable values and execute the current operation result operation until all the independent variable values are operated.

Further, the value reading unit is specifically configured to: if the group number of the non-operated autovariable values is greater than or equal to the preset number, reading the preset number of autovariable values from the non-operated autovariable values; otherwise, reading all the non-operated autovariable values as the autovariable values of the preset number.

Based on the technical scheme, the SQL statement operation device further comprises the following optimization steps: and the error reporting processing module 35 is configured to, when error reporting information is received during calculation of a current operation result and/or calculation of a next operation result, directly substitute a value of an initial argument of the expression to be analyzed into the expression to be analyzed, and then calculate to obtain an operation result of the SQL statement.

The SQL sentence operation device can execute the operation method of the SQL sentence provided by any embodiment of the invention, and has corresponding functional modules and beneficial effects of the execution method.

Example four

Fig. 4 is a schematic structural diagram of a terminal device according to a fourth embodiment of the present invention. As shown in fig. 4, a terminal device provided in the fourth embodiment of the present invention includes: one or more processors 41 and storage 42; the processor 41 in the terminal device may be one or more, and one processor 41 is taken as an example in fig. 4; storage 42 is used to store one or more programs; the one or more programs are executed by the one or more processors 41, so that the one or more processors 41 implement the operation method of the SQL statement according to any one of the embodiments of the present invention.

The terminal device may further include: an input device 43 and an output device 44.

The processor 41, the storage device 42, the input device 43 and the output device 44 in the terminal equipment may be connected by a bus or other means, and the connection by the bus is exemplified in fig. 4.

The storage device 42 in the terminal device is used as a computer-readable storage medium, and can be used for storing one or more programs, which may be software programs, computer-executable programs, and modules, such as program instructions/modules corresponding to the operation method of the SQL statement provided in the first or second embodiment of the present invention (for example, the modules in the operation device of the SQL statement shown in fig. 3 include the splitting module 31, the current result operation module 32, and the next result operation module 33, and further include the operation completion determining module 34 and the error reporting processing module 35). The processor 41 executes various functional applications and data processing of the terminal device by running software programs, instructions and modules stored in the storage device 42, that is, implements the operation method of the SQL statement in the above method embodiment.

The storage device 42 may include a storage program area and a storage data area, wherein the storage program area may store an operating system, an application program required for at least one function; the storage data area may store data created according to the use of the terminal device, and the like. Further, the storage 42 may include high speed random access memory, and may also include non-volatile memory, such as at least one magnetic disk storage device, flash memory device, or other non-volatile solid state storage device. In some examples, storage 42 may further include memory located remotely from processor 41, which may be connected to the device over a network. Examples of such networks include, but are not limited to, the internet, intranets, local area networks, mobile communication networks, and combinations thereof.

The input means 43 may be used to receive input numeric or character information and generate key signal inputs related to user settings and function control of the terminal device. The output device 44 may include a display device such as a display screen.

And, when the one or more programs included in the above-mentioned terminal device are executed by the one or more processors 41, the programs perform the following operations: splitting an expression to be analyzed in an SQL statement according to the operator priority to obtain sub-expressions and the operation priority of each sub-expression; substituting the independent variable number value of the current sub-expression into the operation priority to obtain a current operation result, wherein the independent variable number value comprises an unoperated number value and/or an operation result of a previous sub-expression; and continuously substituting the self-variable number value of the next sub-expression and then calculating to obtain a next operation result until the self-variable number value of the expression to be analyzed is substituted and then calculating to obtain the operation result of the SQL statement.

EXAMPLE five

An embodiment of the present invention provides a computer-readable storage medium, on which a computer program is stored, where the computer program is used for executing an operation method of an SQL statement when executed by a processor, and the method includes: splitting an expression to be analyzed in an SQL statement according to the operator priority to obtain sub-expressions and the operation priority of each sub-expression; substituting the independent variable number value of the current sub-expression into the operation priority to obtain a current operation result, wherein the independent variable number value comprises an unoperated number value and/or an operation result of a previous sub-expression; and continuously substituting the self-variable number value of the next sub-expression and then calculating to obtain a next operation result until the self-variable number value of the expression to be analyzed is substituted and then calculating to obtain the operation result of the SQL statement.

Optionally, the program may be further configured to execute an operation method of the SQL statement provided in any embodiment of the present invention when executed by the processor.

Computer storage media for embodiments of the invention may employ any combination of one or more computer-readable media. The computer readable medium may be a computer readable signal medium or a computer readable storage medium. A computer readable storage medium may be, for example, but not limited to, an electronic, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any combination of the foregoing. More specific examples (a non-exhaustive list) of the computer readable storage medium would include the following: an electrical connection having one or more wires, a portable computer diskette, a hard disk, a Random Access Memory (RAM), a Read Only Memory (ROM), an Erasable Programmable Read Only Memory (EPROM), a flash Memory, an optical fiber, a portable CD-ROM, an optical storage device, a magnetic storage device, or any suitable combination of the foregoing. A computer readable storage medium may be any tangible medium that can contain, or store a program for use by or in connection with an instruction execution system, apparatus, or device.

A computer readable signal medium may include a propagated data signal with computer readable program code embodied therein, for example, in baseband or as part of a carrier wave. Such a propagated data signal may take a variety of forms, including, but not limited to: an electromagnetic signal, an optical signal, or any suitable combination of the foregoing. A computer readable signal medium may also be any computer readable medium that is not a computer readable storage medium and that can communicate, propagate, or transport a program for use by or in connection with an instruction execution system, apparatus, or device.

Program code embodied on a computer readable medium may be transmitted using any appropriate medium, including but not limited to: wireless, wire, fiber optic cable, Radio Frequency (RF), etc., or any suitable combination of the foregoing.

Computer program code for carrying out operations for aspects of the present invention may be written in any combination of one or more programming languages, including an object oriented programming language such as Java, Smalltalk, C + + or the like and conventional procedural programming languages, such as the "C" programming language or similar programming languages. The program code may execute entirely on the user's computer, partly on the user's computer, as a stand-alone software package, partly on the user's computer and partly on a remote computer or entirely on the remote computer or server. In the case of a remote computer, the remote computer may be connected to the user's computer through any type of network, including a Local Area Network (LAN) or a Wide Area Network (WAN), or the connection may be made to an external computer (for example, through the Internet using an Internet service provider).

It is to be noted that the foregoing is only illustrative of the preferred embodiments of the present invention and the technical principles employed. It will be understood by those skilled in the art that the present invention is not limited to the particular embodiments described herein, but is capable of various obvious changes, rearrangements and substitutions as will now become apparent to those skilled in the art without departing from the scope of the invention. Therefore, although the present invention has been described in greater detail by the above embodiments, the present invention is not limited to the above embodiments, and may include other equivalent embodiments without departing from the spirit of the present invention, and the scope of the present invention is determined by the scope of the appended claims.

Claims

1. An operation method of an SQL statement is characterized by comprising the following steps:

splitting an expression to be analyzed in an SQL statement according to operator priority to obtain sub-expressions and operation priorities of the sub-expressions, respectively setting temporary columns according to the sub-expressions, and combining the same sub-expressions into a column for operation if the same sub-expressions exist;

2. The method of claim 1, wherein the substituting the argument values of the current sub-expressions into the operation priority to obtain the current operation result comprises:

reading a preset number of independent variable values from the un-operated independent variable values;

and sequentially substituting the autovariable values of the current sub-expressions in the autovariable values of the preset number according to the operation priority to obtain a current operation result.

3. The method according to claim 2, wherein the continuing to substitute the argument number value of the next sub-expression to obtain the next operation result until substituting the argument number value of the expression to be analyzed to obtain the operation result of the SQL statement comprises:

and continuously substituting the autovariable values of the next sub-expression in the autovariable values of the preset number for operation to obtain a next operation result, and calculating to obtain the operation result of the SQL statement after substituting the autovariable values of the expressions to be analyzed in the autovariable values of the preset number.

4. The method according to claim 3, further comprising, after the continuing to substitute the argument value of the next sub-expression in the preset number of argument values to obtain a next operation result, and until substituting the argument value of the to-be-analyzed expression in the preset number of argument values to obtain an operation result of the SQL statement:

and continuously reading the self-variable values of the preset number from the non-operated self-variable values and executing the operation of the current operation result until the operation of the self-variable values is finished.

5. The method of claim 2, wherein reading a preset number of argument values from the un-computed argument values comprises:

if the group number of the non-operated autovariable values is greater than or equal to the preset number, reading the preset number of autovariable values from the non-operated autovariable values; otherwise, reading all the non-operated autovariable values as the autovariable values of the preset number.

6. The method of claim 1, further comprising:

and if error information is received when the current operation result is calculated and/or the next operation result is calculated, directly substituting the numerical value of the initial independent variable of the expression to be analyzed into the expression to be analyzed, and then calculating to obtain the operation result of the SQL statement.

7. An apparatus for operating an SQL statement, comprising:

the splitting module is used for splitting the expressions to be analyzed in the SQL sentences according to the operator priority to obtain the sub-expressions and the operation priorities of the sub-expressions, respectively setting temporary columns according to the sub-expressions, and combining the same sub-expressions into a column for operation if the same sub-expressions exist;

8. The apparatus of claim 7, further comprising:

and the error reporting processing module is used for directly substituting the numerical value of the initial independent variable of the expression to be analyzed into the expression to be analyzed and then calculating to obtain the operation result of the SQL statement when error reporting information is received when the current operation result is calculated and/or the next operation result is calculated.

9. A terminal device, comprising:

one or more processors;

storage means for storing one or more programs;

the one or more programs being executed by the one or more processors so that the one or more processors implement the method of operation of the SQL statement according to any of claims 1-6.

10. A computer-readable storage medium on which a computer program is stored, the program implementing the operation method of the SQL statement according to any one of claims 1 to 6 when executed by a processor.