CN116610697A - Query method, storage medium and device for database query statement - Google Patents
Query method, storage medium and device for database query statement Download PDFInfo
- Publication number
- CN116610697A CN116610697A CN202310640009.8A CN202310640009A CN116610697A CN 116610697 A CN116610697 A CN 116610697A CN 202310640009 A CN202310640009 A CN 202310640009A CN 116610697 A CN116610697 A CN 116610697A
- Authority
- CN
- China
- Prior art keywords
- query
- tree
- clause
- expression
- target column
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
- 238000000034 method Methods 0.000 title claims abstract description 53
- 230000002776 aggregation Effects 0.000 claims abstract description 52
- 238000004220 aggregation Methods 0.000 claims abstract description 52
- 238000004458 analytical method Methods 0.000 claims abstract description 16
- 230000014509 gene expression Effects 0.000 claims description 111
- 230000006870 function Effects 0.000 claims description 51
- 230000008569 process Effects 0.000 abstract description 12
- 238000010586 diagram Methods 0.000 description 9
- 238000012545 processing Methods 0.000 description 7
- 238000005457 optimization Methods 0.000 description 4
- 230000000717 retained effect Effects 0.000 description 4
- 238000001914 filtration Methods 0.000 description 2
- 238000012986 modification Methods 0.000 description 2
- 230000004048 modification Effects 0.000 description 2
- 238000004364 calculation method Methods 0.000 description 1
- 230000001413 cellular effect Effects 0.000 description 1
- 238000010276 construction Methods 0.000 description 1
- 238000005516 engineering process Methods 0.000 description 1
- 230000005055 memory storage Effects 0.000 description 1
- 230000003287 optical effect Effects 0.000 description 1
- 239000013307 optical fiber Substances 0.000 description 1
- 238000012546 transfer Methods 0.000 description 1
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
- G06F16/2438—Embedded query languages
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
- G06F16/244—Grouping and aggregation
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F8/00—Arrangements for software engineering
- G06F8/40—Transformation of program code
- G06F8/41—Compilation
- G06F8/42—Syntactic analysis
- G06F8/427—Parsing
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F8/00—Arrangements for software engineering
- G06F8/40—Transformation of program code
- G06F8/41—Compilation
- G06F8/43—Checking; Contextual analysis
- G06F8/436—Semantic checking
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y02—TECHNOLOGIES OR APPLICATIONS FOR MITIGATION OR ADAPTATION AGAINST CLIMATE CHANGE
- Y02D—CLIMATE CHANGE MITIGATION TECHNOLOGIES IN INFORMATION AND COMMUNICATION TECHNOLOGIES [ICT], I.E. INFORMATION AND COMMUNICATION TECHNOLOGIES AIMING AT THE REDUCTION OF THEIR OWN ENERGY USE
- Y02D10/00—Energy efficient computing, e.g. low power processors, power management or thermal management
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Computational Linguistics (AREA)
- Mathematical Physics (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- Software Systems (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The application provides a query method, a storage medium and a device for database query sentences. The query method of the database query statement comprises the following steps: acquiring a query statement; carrying out grammar analysis on the query statement to obtain a grammar tree; carrying out semantic analysis on the grammar tree, and constructing an initial query tree and a target column list; checking whether the query statement has nested calls of the aggregation function; if the initial query tree exists, separating the initial query tree into a father query tree and a son query tree according to the target column list; queries are performed using the parent query tree and the child query tree. The query of the query statement in the preset form is realized by layering the query tree, so that the server can process the simplified query statement with the nested calling aggregation function, the writing process of the query statement with the nested calling aggregation function is simplified, and the writing efficiency is improved.
Description
Technical Field
The present application relates to the field of database technologies, and in particular, to a method, a storage medium, and an apparatus for querying a database query statement.
Background
The structured query language (Structured Query Language, abbreviated as SQL) is a standard language of databases, and is a database language with extremely strong universality. There is a special function-aggregate function for SQL statements. The aggregation function is peculiar in that it inputs a set of rows, rather than a single row. E.g., MAX function, finding the maximum value from the data of a set of rows; AVG function, averages data for a set of rows.
In the use process of the database, secondary aggregation is sometimes required to be performed on the returned results of the aggregation function, for example, the MAX function is used to find the highest salary of each part, and the AVG function is used to average the highest salary of all departments. The method is limited by the processing mode of the SQL sentences by the current database, so that the SQL sentences which are secondarily aggregated on the return result of the aggregation function are complex, and the SQL sentence writing work of the user is greatly limited.
Disclosure of Invention
An object of the present application is to provide a query method, a storage medium, and a device for a database query statement that can solve any of the above problems.
In particular, the application provides a query method of database query sentences, which comprises the following steps:
acquiring a query statement;
carrying out grammar analysis on the query statement to obtain a grammar tree;
carrying out semantic analysis on the grammar tree, and constructing an initial query tree and a target column list;
checking whether the query statement has nested calls of the aggregation function;
if the initial query tree exists, separating the initial query tree into a father query tree and a son query tree according to the target column list;
and querying by utilizing the parent query tree and the child query tree.
Optionally, the step of performing semantic parsing on the syntax tree includes:
checking whether a target column expression of the query statement has nested call of the aggregation function, and if so, recording mark information;
the step of checking whether the query statement has a nested call to the aggregate function includes:
judging whether the query statement has nested calling of the aggregation function according to the marking information.
Optionally, the step of performing semantic parsing on the syntax tree further includes:
adding a table referenced by an expression of the FROM clause in the query statement into an initial query tree;
the target column expression is added to the target column list, marked as an output target column.
Optionally, under the condition that the query sentence has the clause of the first preset kind, the step of performing semantic parsing on the syntax tree further includes:
and adding the expression of the clause of the first preset category in the query statement sentence into the target column list, and marking the expression as a non-output target column.
Optionally, the clauses of the first preset category include an ORDER BY clause, a GROUP BY clause, and an OVER clause.
Optionally, the step of separating the initial query tree into a parent query tree and a child query tree according to the target column list comprises:
constructing a new query tree as a parent query tree, and adding the initial query tree as a child query tree into the parent query tree;
traversing the expressions in the target column list corresponding to the child query tree, judging whether the expressions are output target columns, and if yes, adding the expressions into the target column list of the parent query tree;
if not, judging whether the expression belongs to the GROUP BY clause, if so, reserving the expression in a target column list of the sub-query tree, and if not, deleting the expression;
after the expression in the target column list corresponding to the child query tree is traversed, traversing the expression in the target column list of the parent query tree, checking whether the expression in the target column list of the parent query tree contains an aggregation function as a parameter, and if so, pushing the parameter back to the target column list of the child query tree.
Optionally, under the condition that the query sentence has the clause of the second preset kind, the step of performing semantic parsing on the syntax tree further includes:
checking whether the expression of the clause of the second preset type meets the preset requirement, and if yes, outputting error information.
Optionally, the clauses of the second preset category include a WHERE clause, a HAVING clause, a LIMIT clause, and a distict clause, and the step of checking whether the expression of the clause of the second preset category meets the preset requirement includes:
checking whether the expression type of the WHERE clause in the query statement is Boolean type, if so, meeting the requirements;
checking whether the expression type of the HAVING clause in the query statement is Boolean type, if so, meeting the requirements;
checking whether the expression type of the LIMIT clause in the query statement is a long integer type, and if so, conforming to the requirement;
checking whether the expression of the DISTINCT clause in the query statement exists in the ORDER BY clause, and if yes, meeting the requirements.
According to another aspect of the present application, there is also provided a machine-readable storage medium having stored thereon a machine-executable program which, when executed by a processor, implements a query method of a database query statement according to any of the above.
According to yet another aspect of the present application, there is also provided a computer device including a memory, a processor, and a machine executable program stored on the memory and running on the processor, and the processor implementing a query method of a database query statement according to any one of the above when executing the machine executable program.
The query method of the database query statement constructs the query tree and the target column list in the semantic analysis stage, and separates the query tree into a father query tree and a son query tree according to the target column list under the condition that the query statement has nested call of the aggregation function. That is, by optimizing the semantic analysis stage of the query statement, the query of the query statement in the preset form is realized by layering the query tree, so that the server can process the simplified query statement with the nested calling aggregation function, the writing process of the query statement with the nested calling aggregation function is simplified, and the writing efficiency is improved.
The above, as well as additional objectives, advantages, and features of the present application will become apparent to those skilled in the art from the following detailed description of a specific embodiment of the present application when read in conjunction with the accompanying drawings.
Drawings
Some specific embodiments of the application will be described in detail hereinafter by way of example and not by way of limitation with reference to the accompanying drawings. The same reference numbers will be used throughout the drawings to refer to the same or like parts or portions. It will be appreciated by those skilled in the art that the drawings are not necessarily drawn to scale. In the accompanying drawings:
FIG. 1 is a schematic flow diagram of a method of querying a database query statement in accordance with one embodiment of the application;
FIG. 2 is a schematic flow chart of steps for semantic parsing of a grammar tree in a query method for a database query statement according to one embodiment of the application;
FIG. 3 is a schematic flow chart diagram of the steps in a query method of a database query statement in separating a query tree into a parent query tree and a child query tree according to a list of target columns in accordance with one embodiment of the application;
FIG. 4 is a schematic flow chart diagram of a method of querying a database query statement in accordance with another embodiment of the application;
FIG. 5 is a schematic flow chart of steps for semantic parsing of a semantic tree in a method of querying a database query statement according to another embodiment of the application;
FIG. 6 is a schematic flow chart diagram of steps for rewriting a query tree in a query method of a database query statement in accordance with one embodiment of the application;
FIG. 7 is a schematic diagram of a machine-readable storage medium according to one embodiment of the application;
FIG. 8 is a schematic diagram of a computer device according to one embodiment of the application.
Detailed Description
It should be understood by those skilled in the art that the embodiments described below are only some embodiments of the present application, but not all embodiments of the present application, and the some embodiments are intended to explain the technical principles of the present application and are not intended to limit the scope of the present application. All other embodiments, which can be obtained by a person skilled in the art without any inventive effort, based on the embodiments provided by the present application, shall still fall within the scope of protection of the present application.
It should be noted that the logic and/or steps represented in the flowcharts or otherwise described herein, for example, may be considered as a ordered listing of executable instructions for implementing logical functions, and may be embodied in any computer-readable medium for use by or in connection with an instruction execution system, apparatus, or device, such as a computer-based system, processor-containing system, or other system that can fetch the instructions from the instruction execution system, apparatus, or device and execute the instructions.
At present, SQL (Structured Query Language ) statements that perform secondary aggregation on the returned results of the aggregation function are relatively complex. For example, for a single aggregation of the highest salary of employees in each section of the query employee table, the SQL statement is: select max (salary) from employees group by dep _id. If average calculation is performed on the highest salary, namely secondary aggregation, the SQL statement is: select avg (max_salary) from (select max (salary) as max_ salary from employees group by dep _id). It can be seen that the writing of the secondary aggregated SQL statement is very complex.
By using the improved query method of the scheme, SQL sentences can be simplified, and the secondary query can be simplified into select avg (max) from employees group by dep _id, so that the writing efficiency is improved.
The query method of the present application will be described below with reference to examples.
As shown in fig. 1, in one embodiment, a query method of a database query statement generally includes:
step S101, a query sentence is acquired. Specifically, that is, an SQL statement is received that needs to be executed.
Step S102, carrying out grammar analysis on the query sentence to obtain a grammar tree. Specifically, it is checked whether the query sentence complies with the grammar rules, such as whether various symbols are correct, whether the spelling of the keyword is correct, and so on. Finally, a representation form of the SQL sentence in the database, namely a grammar tree, is obtained. A grammar tree is a tree consisting of various levels of structures that directly describe the query grammar.
And step S103, carrying out semantic analysis on the grammar tree, and constructing an initial query tree and a target column list. Semantic parsing is performed on the grammar tree, that is, on the query statement, for example, checking whether a relationship name and an attribute name in the query statement exist and are valid. In the event that there is no error in the query statement, an initial query tree and a target column list are constructed. The query tree is an internal representation of the relational algebraic expression of the SQL statement within the database.
Referring to fig. 2, specifically, step S103 includes:
step S201, checking whether the target column expression of the query statement has nested call of the aggregation function, and if so, recording the mark information. Specifically, the target column expression, i.e., the post-select expression, takes the query statement select avg (max (salary)) from employees group by dep _id as an example, the target column expression, i.e., "avg (max (salary)"). By examining the target column expression, it is known whether there is a nested call to the aggregation function, i.e., a secondary aggregation. While for "avg (max (salary))", the keywords of both avg and max aggregation functions are checked, so it is determined that there is a nested call of the aggregation function.
Step S202, adding a table referenced by the expression of the FROM clause in the query statement to the initial query tree. Specifically, the FROM clause identifies the data source of the query statement, i.e., which table is queried. By processing the expression of the FROM clause, the table name is added to the initial query tree, that is, the query table as the initial query tree.
In step S203, the target column expression is added to the target column list, and marked as an output target column. Specifically, taking the query statement select avg (max (salary)) from employees group by dep _id as an example, the target column expression "avg (max (salary)) is added to the target column list, and labeled as the output target column.
Step S204, adding the expression of the clause of the first preset category in the query statement sentence into the target column list, and marking as a non-output target column. Wherein the clauses of the first preset category include an ORDER BY clause, a GROUP BY clause, and an OVER clause. Specifically, expressions following the keywords of the clause of the first preset category are added to the target column list and marked as non-output target columns.
Step S205, checking whether the expression of the clause of the second preset category meets the preset requirement, and if yes, outputting error information. The clauses of the second preset category include a WHERE clause, a HAVING clause, a LIMIT clause, and a DISTINCT clause. The WHERE clause is used for condition filtering and the HAVING clause is also used for condition filtering, but the HAVING clause needs to be used after the GROUP BY clause. The LIMIT clause is used to LIMIT the number of returned results. The DISTINCT clause is used to limit the value of the current range variable to eliminate duplicate values in subsequent queries. Specifically, the method comprises the following steps:
checking whether the expression type of the WHERE clause in the query statement is Boolean type, if so, meeting the requirements; checking whether the expression type of the HAVING clause in the query statement is Boolean type, if so, meeting the requirements; checking whether the expression type of the LIMIT clause in the query statement is a long integer type, and if so, conforming to the requirement; checking whether the expression of the DISTINCT clause in the query statement exists in the ORDER BY clause, and if yes, meeting the requirements. If any clause is not in accordance with the requirement, outputting error information, and stopping the query flow.
Step S104, checking whether the query statement has nested calls of the aggregation function. Specifically, whether the query statement has nested call of the aggregation function is judged according to the marking information. Whether nested call of the aggregation function exists or not is known according to the target column expression in the semantic parsing process, and marking information is recorded. By checking the tag information, it can be checked whether the query statement has nested calls to the aggregate function.
Step S105, if present, the initial query tree is separated into a parent query tree and a child query tree according to the target column list.
Referring to fig. 3, specifically, the present step includes:
in step S301, a new query tree is constructed as a parent query tree, and the initial query tree is added as a child query tree to the parent query tree. Specifically, a new query tree is built as the parent query tree, and the initial query tree is added to the parent query tree as a table, i.e., a data source.
Step S302, traversing the expressions in the target column list corresponding to the sub-query tree. Specifically, expressions in a target column list corresponding to the initial query tree are processed one by one.
Step S303, judging whether the expression is the output target column, if so, executing step S304, otherwise, executing step S308. Specifically, the expressions added to the target column list are all marked in the semantic parsing stage, and according to the marking, it is possible to check whether the expression is an output target column.
In step S304, the expression is added to the target column list of the parent query tree. If the expression is the output target column, the expression is pulled up into the parent query tree. Taking query statement select avg (max (salary)) from employees group by dep _id as an example, in the semantic parsing stage, the expression "avg (max (salary)) is added to the target column list of the query tree and marked as an output target column. Therefore, in the stage of constructing a new query tree, the expression "avg (max (salary)) is added to the target column list of the parent query tree.
Step S305, traversing the expressions in the target column list of the parent query tree. Specifically, after the traversing of the expressions in the target column list corresponding to the child query tree is completed, traversing the expressions in the target column list of the parent query tree.
Step S306, checking whether the expression in the target column list of the parent query tree contains the aggregation function as a parameter, if so, executing step S307. That is, it is checked whether the expression is a parameter containing nested aggregation functions, in which the aggregation function of the inner layer, i.e., the aggregation function of the outer layer, is a parameter, i.e., the aggregation function is a parameter.
Step S307, the parameters are pushed back into the target column list of the sub-query tree. I.e., pushing the inner clusters back down into the sub-query tree. For example, the expression "avg (max (salary))," which is the parameter for the inner layer aggregate, is pushed back into the target column list of the sub-query tree as the outer layer aggregate avg. While the outer layer aggregation remains in the parent query tree.
Step S308, judging whether the expression belongs to the GROUP BY clause, if so, executing step S309, and if not, executing step S310. Specifically, in the semantic parsing stage, expressions of the ORDER BY clause, the GROUP BY clause, and the OVER clause are added to the target column list and marked as non-output target columns. That is, the non-output target column may belong to the ORDER BY clause, the GROUP BY clause, or the OVER clause, so that it is determined whether the expression belongs to the GROUP BY clause.
Step S309, the expression is retained in the target column list of the sub-query tree. If the expression belongs to the GROUP BY clause, the expression is retained in the target column list of the sub-query tree.
In step S310, the expression is deleted. If an expression does not belong to the GROUP BY clause, the expression is deleted, i.e., the expression is not processed during execution.
Step S106, inquiring by using the parent inquiring tree and the child inquiring tree. After the construction of the parent query tree and the child query tree is completed, query sentences are executed by utilizing the parent query tree and the child query tree.
If the query statement does not have the nested call of the aggregation function, the initial query tree is used to execute the query statement.
In the solution of the embodiment, by constructing the query tree and the target column list in the semantic parsing stage, in the case that there is a nested call of the aggregation function in the query statement, the query tree is separated into the parent query tree and the child query tree according to the target column list. That is, by optimizing the semantic analysis stage of the query statement, the query of the query statement in the preset form is realized by layering the query tree, so that the server can process the simplified query statement with the nested calling aggregation function, the writing process of the query statement with the nested calling aggregation function is simplified, and the writing efficiency is improved.
Referring to FIG. 4, in one embodiment, a method of querying a database query statement generally includes:
step S401, receiving a query statement sent by a client. I.e., receiving the SQL statement that needs to be executed.
Step S402, grammar analysis is carried out on the query sentence, and a grammar tree is obtained. Specifically, whether the query statement accords with the grammar rule is checked, and finally, a representation form of the SQL statement in the database, namely a grammar tree, is obtained.
Step S403, semantic analysis is carried out on the grammar tree, and an initial query tree is constructed. And carrying out semantic analysis on the query statement to construct a query tree and a target column list.
Referring to fig. 5, the present step includes:
step S501, processing the target column expression in the query statement. Specifically, the target column expression is a post-select expression. Including checking whether the target column expression of the query statement has a nested call to the gather function, and if so, recording tag information. And adding the target column expression to the target column list, marked as an output target column.
Step S502, the FROM clause in the query statement is processed. Specifically, a table referenced by an expression of the FROM clause in the query statement is added to the query tree.
Step S503, process the WHERE clause in the query statement. Specifically, it is checked whether the expression type of the WHERE clause is boolean type.
Step S504, processing the HAVING clause in the query statement. Specifically, it is checked whether the expression type of the HAVING clause is boolean.
In step S505, LIMIT clauses in the query sentence are processed. Specifically, it is checked whether the expression type of the LIMIT clause is a long integer type.
Step S506, processing the DISTINCT clause in the query statement. Specifically, it is checked whether the expression of the distict clause exists in the ORDER BY clause.
In step S507, the ORDER BY clause in the query statement is processed. Specifically, the expression of the ORDER BY clause is added to the target column list and marked as a non-output target column.
Step S508, process the GROUP BY clause in the query statement. Specifically, the expression of the GROUP BY clause is added to the target column list and marked as a non-output target column.
Step S509, processing the OVER clause in the query statement. Specifically, expressions of the ORDER BY and the component BY clauses inside the OVER clause are added to the target column list and marked as non-output target columns.
Step S404, when the query statement has the aggregation function of nested call, the initial query tree is rewritten. Specifically, in the case where there is an aggregation function for nested calls to the query statement, the initial query tree is separated into a parent query tree and a child query tree according to the target column list.
Referring to fig. 6, the present step includes:
in step S601, a new query tree is constructed as a parent query. Specifically, a new query tree is constructed as a parent query tree, and the initial query tree is added to the parent query tree as a data source.
Step S602, traversing a target column list corresponding to the sub-query tree. Specifically, expressions in a target column list corresponding to a query tree formed in a semantic analysis stage are processed one by one.
Step S603, detecting whether the expression is the output target column, if so, executing step S604, and if not, executing step S607.
In step S604, the expression is pulled up into the parent query tree. I.e., adding the expression to the target column list of the parent query tree.
Step S605 traverses the target column list of the parent query. Specifically, after the traversing of the expressions in the target column list corresponding to the child query tree is completed, traversing the expressions in the target column list of the parent query tree.
Step S606, pushing the nested aggregation function parameter expressions back to the sub-query tree. Specifically, it is checked whether the expression marked as the output target column contains a nested aggregation function, and in the case of containing a nested aggregation function, the inner aggregation expression of the nested aggregation function is pushed back to the sub-query tree.
Step S607, if the expression belongs to the GROUP BY clause, executing step S608 if yes, and executing step S609 if no.
In step S608, the expression is retained in the sub-query tree. The expression is retained in the target column list of the sub-query tree.
In step S609, the expression is deleted from the sub-query tree. I.e., the expression is not processed any more during execution.
Step S405, query rewriting is performed on the query tree. Specifically, in the case where there is an aggregation function for nested calls in the query statement, the query tree for this step is a hierarchical query tree, that is, a query tree that includes a child query tree and a parent query tree. In the case that the query statement does not have the aggregation function of nested call, the query tree for this step is the initial query tree. Algebraic optimization and physical optimization are performed on the query tree, wherein the algebraic optimization and the physical optimization are the prior art and are not described herein.
In step S406, a query path and a query plan are generated. Specifically, the optimizer of the server generates all possible query paths according to the rule configured by the optimizer, and selects a path with the smallest cost as a query plan according to the cost size.
Step S407, executing the query plan and returning the result. Specifically, the query statement is executed according to the query plan and the results are returned.
That is, steps S405 to S407 are procedures performed on the query sentence according to the query tree.
In the solution of the embodiment, by constructing the query tree and the target column list in the semantic parsing stage, in the case that there is a nested call of the aggregation function in the query statement, the query tree is separated into the parent query tree and the child query tree according to the target column list. That is, by optimizing the semantic analysis stage of the query statement, the query of the query statement in the preset form is realized by layering the query tree, so that the server can process the simplified query statement with the nested calling aggregation function, the writing process of the query statement with the nested calling aggregation function is simplified, and the writing efficiency is improved.
The present embodiment also provides a machine-readable storage medium and a computer device. FIG. 7 is a schematic diagram of a machine-readable storage medium 10 according to one embodiment of the application. Fig. 8 is a schematic diagram of a computer device 20 according to one embodiment of the application.
The machine-readable storage medium 10 has stored thereon a machine-executable program 11, which when executed by a processor implements the query method of the database query statement of any of the embodiments described above.
The computer device 20 may include a memory 210, a processor 220, and a machine executable program 11 stored on the memory 210 and running on the processor 220, and the processor 220 implements the query method of the database query statement of any of the embodiments described above when executing the machine executable program 11.
It should be noted that the logic and/or steps represented in the flow diagrams or otherwise described herein, e.g., a ordered listing of executable instructions for implementing logical functions, can be embodied in any machine-readable storage medium for use by or in connection with an instruction execution system, apparatus, or device, such as a computer-based system, processor-containing system, or other system that can fetch the instructions from the instruction execution system, apparatus, or device and execute the instructions.
For the purposes of this description of embodiments, a machine-readable storage medium 10 can be any apparatus that can contain, store, communicate, propagate, or transport the program for use by or in connection with the instruction execution system, apparatus, or device. More specific examples (a non-exhaustive list) of the computer-readable medium would include the following: an electrical connection (electronic device) having one or more wires, a portable computer diskette (magnetic device), a Random Access Memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber device, and a portable compact disc read-only memory (CDROM). Additionally, the machine-readable storage medium 10 may even be paper or other suitable medium upon which the program is printed, as the program may be electronically captured, via, for instance, optical scanning of the paper or other medium, then compiled, interpreted, or otherwise processed in a suitable manner, if necessary, and then stored in a computer memory.
It is to be understood that portions of the present application may be implemented in hardware, software, firmware, or a combination thereof. In the above-described embodiments, the various steps or methods may be implemented in software or firmware stored in a memory and executed by a suitable instruction execution system.
The computer device 20 may be, for example, a server, a desktop computer, a notebook computer, a tablet computer, or a smartphone. In some examples, computer device 20 may be a cloud computing node. The computer device 20 may be described in the general context of computer system-executable instructions, such as program modules, being executed by a computer system. Generally, program modules may include routines, programs, objects, components, logic, data structures, etc. that perform particular tasks or implement particular abstract data types. Computer device 20 may be practiced in distributed cloud computing environments where tasks are performed by remote processing devices that are linked through a communications network. In a distributed cloud computing environment, program modules may be located in both local and remote computing system storage media including memory storage devices.
Computer device 20 may include a processor 220 adapted to execute stored instructions, a memory 210 providing temporary storage for the operation of the instructions during operation. Processor 220 may be a single core processor, a multi-core processor, a computing cluster, or any number of other configurations. Memory 210 may include Random Access Memory (RAM), read only memory, flash memory, or any other suitable storage system.
Processor 220 may be connected via a system interconnect (e.g., PCI-Express, etc.) to an I/O interface (input/output interface) adapted to connect computer device 20 to one or more I/O devices (input/output devices). The I/O devices may include, for example, a keyboard and a pointing device, which may include a touch pad or touch screen, among others. The I/O device may be a built-in component of the computer device 20 or may be a device externally connected to the computing device.
The processor 220 may also be linked through a system interconnect to a display interface suitable for connecting the computer device 20 to a display device. The display device may include a display screen as a built-in component of the computer device 20. The display device may also include a computer monitor, television, projector, or the like, that is externally connected to the computer device 20. Further, a network interface controller (network interface controller, NIC) may be adapted to connect the computer device 20 to a network through a system interconnect. In some embodiments, the NIC may use any suitable interface or protocol (such as an internet small computer system interface, etc.) to transfer data. The network may be a cellular network, a radio network, a Wide Area Network (WAN), a Local Area Network (LAN), or the internet, among others. The remote device may be connected to the computing device through a network.
The flowcharts provided by this embodiment are not intended to indicate that the operations of the method are to be performed in any particular order, or that all of the operations of the method are included in all of each case. Furthermore, the method may include additional operations. Additional variations may be made to the above-described methods within the scope of the technical ideas provided by the methods of the present embodiments.
By now it should be appreciated by those skilled in the art that while a number of exemplary embodiments of the application have been shown and described herein in detail, many other variations or modifications of the application consistent with the principles of the application may be directly ascertained or inferred from the present disclosure without departing from the spirit and scope of the application. Accordingly, the scope of the present application should be understood and deemed to cover all such other variations or modifications.
Claims (10)
1. A method of querying a database query statement, comprising:
acquiring a query statement;
carrying out grammar analysis on the query statement to obtain a grammar tree;
carrying out semantic analysis on the grammar tree, and constructing an initial query tree and a target column list;
checking whether the query statement has a nested call of an aggregation function;
if so, separating the initial query tree into a parent query tree and a child query tree according to the target column list;
and querying by utilizing the parent query tree and the child query tree.
2. The query method of database query statements as claimed in claim 1, wherein said step of semantically parsing said syntax tree comprises:
checking whether a target column expression of the query statement has nested call of an aggregation function, and if so, recording mark information;
the step of checking whether the query statement has a nested call to an aggregate function comprises:
judging whether the query statement has nested calling of the aggregation function according to the marking information.
3. The query method of database query statements as claimed in claim 2, wherein said step of semantically parsing said syntax tree further comprises:
adding a table referenced by an expression of a FROM clause in a query statement to the initial query tree;
the target column expression is added to the target column list, marked as an output target column.
4. A method of querying a database query statement as claimed in claim 3, wherein the step of semantically parsing the syntax tree in the presence of a clause of a first predetermined kind in the query statement further comprises:
and adding the expression of the clause of the first preset category in the query statement sentence into the target column list, and marking the expression as a non-output target column.
5. The query method of database query sentences as claimed in claim 4, wherein said first preset category of clauses includes an ORDER BY clause, a GROUP BY clause and an OVER clause.
6. The query method of database query statements of claim 5, wherein said step of separating said initial query tree into a parent query tree and a child query tree according to said target column list comprises:
constructing a new query tree as the father query tree, and adding the initial query tree as the child query tree into the father query tree;
traversing the expression in the target column list corresponding to the child query tree, judging whether the expression is an output target column, and if so, adding the expression into the target column list of the parent query tree;
if not, judging whether the expression belongs to a GROUP BY clause, if so, reserving the expression in a target column list of the sub-query tree, and if not, deleting the expression;
after the traversing of the expressions in the target column list corresponding to the child query tree is completed, traversing the expressions in the target column list of the parent query tree, checking whether the expressions in the target column list of the parent query tree contain an aggregation function as a parameter, and if so, pushing the parameter back to the target column list of the child query tree.
7. The query method of a database query sentence according to claim 2, wherein said step of semantically parsing said syntax tree further includes, in the presence of a clause of a second preset kind in said query sentence:
and checking whether the expression of the clause of the second preset type meets the preset requirement, and if yes, outputting error information.
8. The query method of database query sentences according to claim 7, wherein the clauses of the second preset category include a WHERE clause, a HAVING clause, a LIMIT clause and a distict clause, and the step of checking whether the expression of the clause of the second preset category meets preset requirements comprises:
checking whether the expression type of the WHERE clause in the query statement is Boolean type, if so, meeting the requirements;
checking whether the expression type of the HAVING clause in the query statement is Boolean type, if so, meeting the requirements;
checking whether the expression type of the LIMIT clause in the query statement is a long integer type, and if so, conforming to the requirement;
checking whether the expression of the DISTINCT clause in the query statement exists in the ORDER BY clause, and if yes, meeting the requirements.
9. A machine-readable storage medium having stored thereon a machine-executable program which when executed by a processor implements a method of querying a database query statement in accordance with any of claims 1 to 8.
10. A computer device comprising a memory, a processor and a machine executable program stored on the memory and running on the processor, and the processor implementing a method of querying a database query statement according to any of claims 1 to 8 when executing the machine executable program.
Priority Applications (1)
Application Number | Priority Date | Filing Date | Title |
---|---|---|---|
CN202310640009.8A CN116610697A (en) | 2023-05-31 | 2023-05-31 | Query method, storage medium and device for database query statement |
Applications Claiming Priority (1)
Application Number | Priority Date | Filing Date | Title |
---|---|---|---|
CN202310640009.8A CN116610697A (en) | 2023-05-31 | 2023-05-31 | Query method, storage medium and device for database query statement |
Publications (1)
Publication Number | Publication Date |
---|---|
CN116610697A true CN116610697A (en) | 2023-08-18 |
Family
ID=87674545
Family Applications (1)
Application Number | Title | Priority Date | Filing Date |
---|---|---|---|
CN202310640009.8A Pending CN116610697A (en) | 2023-05-31 | 2023-05-31 | Query method, storage medium and device for database query statement |
Country Status (1)
Country | Link |
---|---|
CN (1) | CN116610697A (en) |
Cited By (1)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
US20230418574A1 (en) * | 2022-06-28 | 2023-12-28 | Microsoft Technology Licensing, Llc | Using a semantic tree of a compiler to execute a semantic code query against source code |
-
2023
- 2023-05-31 CN CN202310640009.8A patent/CN116610697A/en active Pending
Cited By (1)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
US20230418574A1 (en) * | 2022-06-28 | 2023-12-28 | Microsoft Technology Licensing, Llc | Using a semantic tree of a compiler to execute a semantic code query against source code |
Similar Documents
Publication | Publication Date | Title |
---|---|---|
US11100174B2 (en) | Simple web search | |
CN110291517B (en) | Query language interoperability in graph databases | |
US8285707B2 (en) | Method of querying relational database management systems | |
US7676453B2 (en) | Partial query caching | |
US20090070300A1 (en) | Method for Processing Data Queries | |
US9898502B2 (en) | System and method for executing a SPARQL query | |
CN102193922A (en) | Method and device for accessing database | |
CN110019314B (en) | Dynamic data packaging method based on data item analysis, client and server | |
CN112579610A (en) | Multi-data source structure analysis method, system, terminal device and storage medium | |
US20140379753A1 (en) | Ambiguous queries in configuration management databases | |
US20200320082A1 (en) | Advanced multiprovider optimization | |
CN116610697A (en) | Query method, storage medium and device for database query statement | |
US8015178B2 (en) | System and method to determine a single SQL BOM solve | |
GB2544281A (en) | Method and system for data validation in knowledge extraction apparatus | |
CN117215570A (en) | Code generation method of application program interface, terminal device and storage medium | |
CN111782195A (en) | Query method based on adding annotation on request parameter and splicing into SQL | |
CN115934760A (en) | Database query statement optimization method, storage medium and computer device | |
CN116361184A (en) | Data searching method, device, medium and computer equipment | |
CN115391424A (en) | Database query processing method, storage medium and computer equipment | |
CN114138821A (en) | Database query method, system and electronic equipment | |
CN115374121A (en) | Database index generation method, machine-readable storage medium and computer equipment | |
CN108664573A (en) | A kind of quick processing system of big data and method with double-channel data library | |
US20190370242A1 (en) | Electronic database and method for forming same | |
CN116680299B (en) | Database query method, system, storage medium and device | |
US11250010B2 (en) | Data access generation providing enhanced search models |
Legal Events
Date | Code | Title | Description |
---|---|---|---|
PB01 | Publication | ||
PB01 | Publication | ||
SE01 | Entry into force of request for substantive examination | ||
SE01 | Entry into force of request for substantive examination | ||
CB02 | Change of applicant information | ||
CB02 | Change of applicant information |
Country or region after: China Address after: 100102 201, 2 / F, 101, No. 5 building, No. 7 Rongda Road, Chaoyang District, Beijing Applicant after: China Electronics Technology Group Jincang (Beijing) Technology Co.,Ltd. Address before: 100102 201, 2 / F, 101, No. 5 building, No. 7 Rongda Road, Chaoyang District, Beijing Applicant before: BEIJING KINGBASE INFORMATION TECHNOLOGIES Inc. Country or region before: China |