CN108241622B - Query script generation method and device - Google Patents
Query script generation method and device Download PDFInfo
- Publication number
- CN108241622B CN108241622B CN201611204520.XA CN201611204520A CN108241622B CN 108241622 B CN108241622 B CN 108241622B CN 201611204520 A CN201611204520 A CN 201611204520A CN 108241622 B CN108241622 B CN 108241622B
- Authority
- CN
- China
- Prior art keywords
- data
- query
- judging
- grouping
- 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.)
- Active
Links
Images
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/244—Grouping and aggregation
Landscapes
- Engineering & Computer Science (AREA)
- Physics & Mathematics (AREA)
- Theoretical Computer Science (AREA)
- Mathematical Physics (AREA)
- Computational Linguistics (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses a method and a device for generating a query script, relates to the technical field of computers, and mainly aims to improve the query performance of a database. The method comprises the following steps: receiving input query parameters; acquiring metadata for describing data attributes; constructing an analysis tree from the query parameters and the metadata, the analysis tree having a plurality of data nodes; judging whether each data node of the analysis tree meets a preset aggregation condition or not; if so, aggregating the data nodes meeting the preset aggregation condition; and generating a query script according to the aggregated analysis tree. The invention is mainly used for generating the query script.
Description
Technical Field
The invention relates to the field of computers, in particular to a method and a device for generating a query script.
Background
The database is a warehouse which organizes, stores and manages data according to a data structure, and the database has a plurality of types, and is widely applied in various aspects from a simplest table which stores various data to a large database system which can store mass data, such as data query.
In the query script generation mode in the prior art, a query parameter is input into a component to generate a script of a database for executing query operation mainly by generating the component with a corresponding function. However, a packet aggregation operation is involved in the process of generating the query script, and the packet aggregation operation is performed in the last step of generating the script, so that the packet aggregation operation needs to add all query columns which are not in an aggregation function to the database script packet syntax, so that the number of selected columns in the database packet syntax is too large, and the query performance of the database is poor.
Disclosure of Invention
In view of the above problems, the present invention is proposed to provide a method and an apparatus for generating a script, which overcome or at least partially solve the above problems, and can improve the query performance of a database.
In one aspect, the present invention provides a method for generating a query script, including:
receiving input query parameters;
acquiring metadata for describing data attributes; constructing an analysis tree from the query parameters and the metadata, the analysis tree having a plurality of data nodes;
judging whether each data node of the analysis tree meets a preset aggregation condition or not;
if so, aggregating the data nodes meeting the preset aggregation condition;
and generating a query script according to the aggregated analysis tree.
Further, the data nodes of the analysis tree correspond to a data table, and the determining whether each data node of the analysis tree meets a preset aggregation condition includes:
acquiring a data table corresponding to the data node, and determining a query column of the data table;
judging whether an index column exists in the query column, wherein the index column refers to a column related to data calculation;
if so, judging that the data node meets a preset aggregation condition; otherwise, judging that the data node does not accord with the preset aggregation condition.
Further, after determining that the index column exists in the query column and before determining that the data node meets a preset aggregation condition, the method further includes:
acquiring data containing a packet syntax field;
judging whether the data containing the grouping grammar field exists in a data table corresponding to the data node;
if so, judging that the data node meets a preset aggregation condition; otherwise, judging that the data node does not accord with the preset aggregation condition.
Further, after determining that the data containing the packet syntax field exists in the data table corresponding to the data node, before determining that the data node meets a preset aggregation condition, the method further includes:
judging whether the query columns except the index column in the query columns contain the grouping grammar field;
if so, judging that the data node meets a preset aggregation condition; otherwise, judging that the data node does not accord with the preset aggregation condition.
Further, before aggregating the data nodes meeting the preset aggregation condition, the method further includes:
acquiring a field which identifies whether data aggregation can be carried out in the analysis tree;
setting the field for identifying whether the data aggregation can be carried out as the field capable of carrying out the data aggregation;
the generating a query script according to the aggregated analysis tree includes:
reading data information in a data table corresponding to each data node in the aggregated analysis tree;
and writing the data information into a preset template file to generate the query script.
In another aspect, the present invention provides an apparatus for generating a query script, including:
the receiving unit is used for receiving input query parameters;
a first acquisition unit configured to acquire metadata describing a data attribute;
a construction unit for constructing an analysis tree from the query parameters and the metadata, the analysis tree having a plurality of data nodes;
the judging unit is used for judging whether each data node of the analysis tree meets a preset aggregation condition or not;
the aggregation unit is used for aggregating the data nodes meeting the preset aggregation condition if each data node of the analysis tree meets the preset aggregation condition;
and the generating unit is used for generating a query script according to the aggregated analysis tree.
Further, the data nodes of the analysis tree correspond to a data table, and the determining unit includes: the first acquisition module is used for acquiring a data table corresponding to the data node and determining a query column of the data table;
the first judgment module is used for judging whether an index column exists in the query column, wherein the index column refers to a column related to data calculation;
the first judging module is further configured to judge that the data node meets a preset aggregation condition if an index column exists in the query column;
the first judging module is further configured to judge that the data node does not meet a preset aggregation condition if no index column exists in the query column.
Further, the judging unit further includes:
the second acquisition module is used for acquiring data containing a grouping syntax field after judging that the index column exists in the query column and before judging that the data node meets the preset aggregation condition;
the second judging module is used for judging whether the data containing the grouping grammar field exists in a data table corresponding to the data node;
the second judging module is further configured to judge that the data node meets a preset aggregation condition if the data containing the packet syntax field exists in a data table corresponding to the data node;
the second judging module is further configured to judge that the data node does not meet a preset aggregation condition if the data including the packet syntax field does not exist in the data table corresponding to the data node.
Further, the judging unit further includes:
a third judging module, configured to, after judging that the data including the grouping syntax field exists in the data table corresponding to the data node, before judging that the data node meets a preset aggregation condition, judge whether query columns in the query columns other than the index column all include the grouping syntax field;
the third judging module is further configured to judge that the data node meets a preset aggregation condition if all query columns in the query columns except the index column include the grouping syntax field;
the third judging module is further configured to judge that the data node does not meet a preset aggregation condition if the query columns in the query columns except the index column do not include the packet syntax field.
Further, the apparatus further comprises:
a second obtaining unit, configured to obtain a field that identifies whether data aggregation is possible in the parse tree;
a setting unit, configured to set a field indicating whether data aggregation is possible to be performed as the field capable of performing data aggregation;
the generation unit includes:
the reading module is used for reading data information in a data table corresponding to each data node in the aggregated analysis tree;
and the writing module is used for writing the data information into a preset template file to generate the query script.
By means of the technical scheme, the query script generation method and the query script generation device provided by the invention have the advantages that the analysis tree is constructed according to the query parameters and the metadata, the data nodes of the analysis tree record data information in the form of a data table, the data nodes meeting the preset aggregation condition are aggregated by judging whether each piece of data of the analysis tree meets the preset aggregation condition or not, so that the analysis trees meeting the aggregation condition are aggregated in advance as much as possible in the construction process of the analysis tree, the query script is generated according to the aggregated analysis tree, and the problem that the query performance of the database is influenced due to excessive query columns in the database grouping grammar when the script is generated is avoided. Compared with the generation method of the query script in the prior art, the method and the device have the advantages that the analysis trees meeting the preset aggregation conditions are aggregated in advance in the process of generating the query script, so that the number of the query columns in the database grouping grammar in the generated query script is reduced, and the query efficiency of the database is improved.
The foregoing description is only an overview of the technical solutions of the present invention, and the embodiments of the present invention are described below in order to make the technical means of the present invention more clearly understood and to make the above and other objects, features, and advantages of the present invention more clearly understandable.
Drawings
Various other advantages and benefits will become apparent to those of ordinary skill in the art upon reading the following detailed description of the preferred embodiments. The drawings are only for purposes of illustrating the preferred embodiments and are not to be construed as limiting the invention. Also, like reference numerals are used to refer to like parts throughout the drawings. In the drawings:
FIG. 1 is a flow chart illustrating a method for generating a query script according to an embodiment of the present invention;
FIG. 2 is a flow chart illustrating another method for generating a query script according to an embodiment of the present invention;
FIG. 3 is a diagram illustrating a structure of an parse tree according to an embodiment of the present invention;
FIG. 4 is a schematic structural diagram illustrating an apparatus for generating a query script according to an embodiment of the present invention;
fig. 5 is a schematic structural diagram illustrating another apparatus for generating a query script according to an embodiment of the present invention.
Detailed Description
Exemplary embodiments of the present disclosure will be described in more detail below with reference to the accompanying drawings. While exemplary embodiments of the present disclosure are shown in the drawings, it should be understood that the present disclosure may be embodied in various forms and should not be limited to the embodiments set forth herein. Rather, these embodiments are provided so that this disclosure will be thorough and complete, and will fully convey the scope of the disclosure to those skilled in the art.
The embodiment of the invention provides a flow diagram of a method for generating a query script, as shown in fig. 1, the method includes:
101. input query parameters are received.
The query parameter is a query parameter constructed according to the service information in the database, and the parameter query means that in some cases, a query which can be used for many times but uses different values each time needs to be created, and if a query which uses different values each time needs to be created, the parameter can be used in the query, that is, the parameter is a placeholder for a value provided when the query is run. And generating a query script according to the query parameters and the analysis tree by acquiring the query parameters.
102. Metadata describing the data attributes is obtained.
The metadata mainly describes attribute information of the data, and is used for supporting functions such as storage location, historical data, data searching, file recording and the like, and for example, a legend, a library directory card and a business card in daily life can be regarded as the metadata. In a relational database management system, metadata describes the structure and meaning of data, and for example, when managing, maintaining SQL Server or developing database applications, we often need to obtain some information related to the database architecture, such as the number and name of tables and views in a certain database, the index defined on a certain table, the number of columns in a certain view, and so on.
103. And constructing an analysis tree according to the query parameters and the metadata. The parse tree is a tree representation of an abstract syntax structure of the source code, each parse tree may include a plurality of data nodes, each data node represents a structure in the source code, and in addition, different data nodes have different upper and lower hierarchical relationships, for example, one data node may have 1 or more child nodes, or may not have child nodes, and the embodiment of the present invention is not particularly limited.
In addition, the data nodes of the analysis tree may record data information in the form of a data table, where the data table may be in the form of a table, such as a one-dimensional table or a two-dimensional table.
It should be noted that the parse tree is a graph of a data parse structure of a tree structure established by a database according to a data structure, and when data query is performed, a grouping and aggregation operation needs to be performed on data nodes in the parse tree.
104. And judging whether each data node of the analysis tree meets a preset aggregation condition or not.
The step of specifically determining whether each data node of the parse tree meets the preset aggregation condition may include, but is not limited to, first determining whether a data table in the parse tree is an index table, where the index table is a data table including an index column, if the data table in the parse tree is the index table, it indicates that there is a column related to data calculation in the data table, such as calculating an average score, calculating a query column of a highest score or a lowest score, then determining whether data including a grouping syntax field exists in the data table corresponding to the data node, where the grouping syntax field is included for aggregating the query columns that need grouping aggregation, if the data including the grouping syntax field exists in the data table corresponding to the data node, it indicates that the current data table includes a grouping function, and finally determining whether the rest query columns except the index column in the query column of the data table all include a database grouping syntax field, and if the rest query columns of the data table except the index column contain the grouping syntax field, the condition that the rest columns of the query columns except the index column need to use the grouping function except the query columns needing to be calculated is indicated, and then each data node of the analysis tree is judged to meet the preset aggregation condition.
The preset aggregation condition is to aggregate data nodes meeting the preset aggregation condition in the parse tree in advance in the process of constructing the parse tree, so as to reduce the excessive query columns in the database grouping syntax, thereby improving the query performance of the database.
105. And if each data node of the analysis tree meets a preset aggregation condition, aggregating the data nodes meeting the preset aggregation condition.
And if each data node of the analysis tree is judged to meet the preset aggregation condition, each data node of the analysis tree is polymerizable, and the data nodes meeting the preset aggregation condition are further aggregated by adopting an aggregation function.
The above common aggregation function and sum function sum, maximum function max, minimum function min, average function avg, and the like are used to calculate the functions of the values of the query columns.
106. And generating a query script according to the aggregated analysis tree.
For the embodiment of the invention, the data nodes meeting the preset aggregation condition in the analysis tree are aggregated in advance, and the data information is written into the preset template file to generate the query script by reading the data information in the data table corresponding to each data node in the aggregated analysis tree, so that the performance of data query through the query script is improved.
According to the query script generation method provided by the embodiment of the invention, an analysis tree is constructed according to query parameters and metadata, data nodes of the analysis tree record data information in a data table form, and the data nodes meeting preset aggregation conditions are aggregated by judging whether each datum of the analysis tree meets the preset aggregation conditions or not, so that the analysis trees meeting the aggregation conditions are aggregated in advance as much as possible in the construction process of the analysis tree, and then the query script is generated according to the aggregated analysis tree, thereby avoiding the influence on query performance of a database due to excessive query columns in database grouping syntax when the script is generated. Compared with the generation method of the query script in the prior art, the method and the device have the advantages that the analysis trees meeting the preset aggregation conditions are aggregated in advance in the process of generating the query script, so that the number of the query columns in the database grouping grammar in the generated query script is reduced, and the query efficiency of the database is improved.
An embodiment of the present invention provides another method for generating a query script, as shown in fig. 2, where the method includes:
201. input query parameters are received.
This step is the same as the method described in step 101 of fig. 1, and is not described herein again.
202. Obtaining metadata describing data attributes
It should be noted that, the method for obtaining metadata used for describing data attributes in the embodiment of the present invention is not limited, and the metadata can be obtained by using a system storage process and a system function provided by SQL server, and the system storage process and the system function provide an abstraction layer between a system table and the metadata, so that the metadata of a current database object can be obtained without directly querying the system table.
203. And constructing an analysis tree according to the query parameters and the metadata.
In the process of generating the query script, the structure of the analysis tree is usually used to represent the association relationship between some data, for example, the association relationship may be the department of the upper and lower levels of an enterprise, a column structure, a commodity classification, etc., and the databases of various local area relationships at present record and store data information in the form of a data table, so that the nodes of the analysis tree also record data information in the form of a data table.
Illustratively, a food family is explained, as shown in fig. 3, the food is organized by category, color and variety, the analysis tree with the food as root node is shown in the figure, the food is divided into two categories of fruit and meat, the fruit and the meat are respectively used as child nodes, the fruit with different colors is further divided into yellow banana and red cherry, and the fruit with different colors is divided into beef and pork.
204. And acquiring a data table corresponding to the data node, and determining a query column of the data table.
The data table may be a two-dimensional table, where each row in the two-dimensional table is recorded as a record, or called a tuple, and each column in the two-dimensional table is recorded as a field, or called an attribute. The query columns here are fields or attributes in a two-dimensional table.
205. And judging whether the index column exists in the query column.
For the embodiment of the present invention, whether the index column exists in the query column may be determined by determining whether the data table is an index table, where the index table is a data table including the index column, and the index column refers to a column in the data table related to data calculation, such as a query column for calculating a total number, an average value, a maximum value, or a minimum value.
For the embodiment of the invention, whether an index column exists in a query column of a data table in an analysis tree is judged, if so, the data table in the analysis tree is an index table, whether the index column needing to be calculated exists in the data table is further judged, and if so, the numerical value of the data calculation related to the index column needs to be realized through grouping and aggregation operation of numerical values in other query columns.
206. And if the index column exists in the query column, acquiring data containing a grouping syntax field.
The grouping syntax field is used for grouping data according to a specified rule, that is, firstly, one data set is divided into a plurality of small areas, then, data processing is performed on the plurality of small areas, for example, there are categories a, b, and c, each category corresponds to a respective subclass, and each subclass corresponds to a respective number, and when the categories are selected and the numbers of the categories a, b, and c are classified and summarized, the results are returned as a ═ 18, b ═ 19, and c ═ 37.
207. And judging whether the data containing the grouping grammar field exists in a data table corresponding to the data node.
For the embodiment of the present invention, the step of determining whether the data containing the packet syntax field comes from the data table corresponding to the data node may be performed by determining whether the data containing the packet syntax field comes from a query column of the data table, if not, further determining whether the data containing the packet syntax field comes from another column outside the query column of the data table, and if so, indicating that the packet syntax field necessarily comes from the data table in the parse tree.
208. And if the data containing the grouping grammar field exists in the data table corresponding to the data node, judging whether the query columns except the index column in the query columns contain the grouping grammar field.
According to the embodiment of the invention, whether the query columns except the index column in the query columns of the data table contain the grouping syntax field is judged to further confirm that all the query columns except the index column in the data table participate in the grouping aggregation operation of the index column, so that the numerical value of the index column can be calculated by adopting an aggregation function according to the numerical values of the query classes of the non-index columns.
209. And if the query columns except the index column in the query columns contain the grouping grammar field, judging that the data node meets the preset aggregation condition.
The condition that the preset aggregation condition is met refers to that the data node of the parse tree meets all the judgment conditions in the above steps 205 to 208, and it can be determined that the data node of the parse tree is polymerizable, for the embodiment of the present invention, after the data node is judged to meet the aggregation condition, a field that identifies whether data aggregation can be performed in the parse tree is obtained, and by setting the field that identifies whether data aggregation can be performed as being capable of performing data aggregation, that is, setting the field that is capable of performing data aggregation as true, and simultaneously, marking the identification fields of all the direct data nodes of the parse tree as being polymerizable.
210. And generating a query script according to the aggregated analysis tree.
For the embodiment of the present invention, the query script is generated according to the aggregated analysis tree by reading the data information in the data table corresponding to each data node in the aggregated analysis tree, and then writing the data information into a preset template file to generate the query script.
After the data nodes of the analysis tree are judged to be polymerizable, the data nodes in the analysis tree are polymerized in advance according to the identification fields, so that excessive query columns in the database grouping syntax can be reduced, and the query performance of data is improved.
For the embodiment of the present invention, specific application scenarios may be as follows, but are not limited to the following scenarios, including: when constructing the analysis tree of the query script, providing a data table containing student names, mathematic scores, Chinese scores, average scores and total score attributes, further constructing the analysis tree according to query parameters and metadata, judging whether index columns related to data calculation exist in query columns of the data table in the analysis tree, such as average numbers or total numbers, if so, further judging whether data containing library grouping grammar fields come from the query columns of the data table in the analysis tree, namely judging whether the query columns in the data table contain the mathematic scores and the Chinese scores, calculating total scores according to the sum of numerical values of the mathematic scores and the Chinese scores, calculating the average scores according to the average values of the mathematic scores and the Chinese scores, performing grouping aggregation operation, further judging whether the rest query columns except the index columns of the query columns of the data table in the analysis tree contain the database grouping grammar fields, the method comprises the steps of judging that all student names, mathematical scores and language scores of query columns in a data table except for average score and total score attributes are fields of grouping grammar, finally judging that data nodes of the analysis tree are polymerizable, marking identification fields of the analysis tree as polymerizable, polymerizing the analysis tree according to the identification fields, and generating a query script according to the polymerized analysis tree.
In the process of generating the query script, the database in the prior art puts the grouping aggregation operation to be executed at last when the grouping aggregation operation is involved, and the query columns which are not in the aggregation function are added into the database grouping grammar, so that the query performance of the database is not ideal due to excessive query columns in the database grouping grammar.
Further, as a specific implementation of the method shown in fig. 1, an embodiment of the present invention provides a device for generating a query script, where the embodiment of the device corresponds to the foregoing method embodiment, and for convenience of reading, the device does not describe details in the foregoing method embodiment one by one, but it should be clear that the device in this embodiment can correspondingly implement all the contents in the foregoing method embodiment, as shown in fig. 4, the device includes:
the receiving unit 31 may be configured to receive an input query parameter, where the receiving unit 31 is a main function module in the apparatus for receiving the query parameter, and the query parameter is a query parameter constructed according to service information in a database;
the first obtaining unit 32 may be configured to obtain metadata describing data attributes, where the first obtaining unit 32 is a main functional module in the apparatus for obtaining metadata, and may specifically obtain metadata by using a system storage process and a system function provided by SQLSever;
a constructing unit 33, configured to construct an analysis tree according to the query parameters and the metadata, where the analysis tree has a plurality of data nodes, and the constructing unit 33 is a main functional module in the apparatus for constructing the analysis tree;
a determining unit 34, configured to determine whether each data node of the analysis tree meets a preset aggregation condition, where the determining unit 34 is a main function module of the apparatus that determines whether a data node meets the preset aggregation condition, and the preset aggregation condition is to aggregate the data nodes meeting the condition in advance in a process of constructing the analysis tree;
the aggregation unit 35 may be configured to aggregate data nodes that meet a preset aggregation condition if each data node of the analysis tree meets the preset aggregation condition, where the aggregation unit 35 is a main function module that aggregates data nodes that meet the preset aggregation condition in the present apparatus, and specifically aggregates data nodes of the analysis tree through an aggregation function;
the generating unit 36 may be configured to generate a query script according to the aggregated analysis tree, where the generating unit 36 is a main function module of the device that generates the query script, and specifically generates the query script by reading data information in a data table corresponding to each data node in the aggregated analysis tree and writing the data information into a preset template file.
The embodiment of the invention provides a device for generating a query script,
the method comprises the steps of constructing an analysis tree according to query parameters and metadata, recording data information by data nodes of the analysis tree in a data table mode, aggregating the data nodes meeting preset aggregation conditions by judging whether each data of the analysis tree meets the preset aggregation conditions, aggregating the analysis trees meeting the aggregation conditions as far as possible in the construction process of the analysis tree, and generating a query script according to the aggregated analysis tree, so that the problem that query columns in database grouping syntax are too many to influence the query performance of a database when the script is generated is avoided. Compared with the generation method of the query script in the prior art, the method and the device have the advantages that the analysis trees meeting the preset aggregation conditions are aggregated in advance in the process of generating the query script, so that the number of the query columns in the database grouping grammar in the generated query script is reduced, and the query efficiency of the database is improved.
Further, as shown in fig. 5, another apparatus for generating a query script is provided in an embodiment of the present invention, where the determining unit 34 includes:
a first obtaining module 341, configured to obtain a data table corresponding to the data node, and determine a query column of the data table;
a first determining module 342, configured to determine whether an index column exists in the query column, where the index column refers to a column related to data calculation;
the first determining module 342 may be further configured to determine that the data node meets a preset aggregation condition if an index column exists in the query column;
the first determining module 342 may be further configured to determine that the data node does not meet a preset aggregation condition if the index column does not exist in the query column.
Further, the judging unit 34 further includes:
the second obtaining module 343, after determining that the index column exists in the query column, before determining that the data node meets a preset aggregation condition, is configured to obtain data including a packet syntax field;
a second determining module 344, configured to determine whether the data containing the packet syntax field exists in a data table corresponding to the data node;
the second determining module 344 is further configured to determine that the data node meets a preset aggregation condition if the data including the packet syntax field exists in a data table corresponding to the data node;
the second determining module 344 may be further configured to determine that the data node does not meet a preset aggregation condition if the data including the packet syntax field does not exist in the data table corresponding to the data node.
Further, the judging unit 34 further includes:
the third determining module 345, after determining that the data containing the grouping syntax field exists in the data table corresponding to the data node, may be configured to determine whether query columns in the query columns, except for the index column, all contain the grouping syntax field before determining that the data node meets a preset aggregation condition;
the third determining module 345 may be further configured to determine that the data node meets a preset aggregation condition if all query columns in the query columns except the index column include the grouping syntax field;
the third determining module 345 may be further configured to determine that the data node does not meet the preset aggregation condition if the query columns in the query columns other than the index column do not include the packet syntax field.
Further, the apparatus further comprises:
a second obtaining unit 37, configured to obtain a field in the parse tree, where the field identifies whether data aggregation is possible;
a setting unit 38, configured to set the field for identifying whether data aggregation is enabled as data aggregation enabled;
the generating unit 36 includes:
a reading module 361, configured to read data information in a data table corresponding to each data node in the aggregated analysis tree;
the writing module 362 may be configured to write the data information into a preset template file, so as to generate the query script. For another method for generating a query script provided in the embodiment of the present invention, data nodes of an analysis tree that meet a preset aggregation condition are aggregated in advance in a process of generating the query script, so that the number of columns of query columns in a database block syntax in the generated query script is reduced, and query efficiency of a database is further improved.
The server includes a processor and a memory, the receiving unit 31, the first acquiring unit, the constructing unit 33, the judging unit 34, the aggregating unit 35, the generating unit 36, and the like are stored in the memory as program units, and the processor executes the program units stored in the memory to realize corresponding functions.
The processor comprises a kernel, and the kernel calls the corresponding program unit from the memory. The kernel can be set to be one or more than one, labor is saved by adjusting kernel parameters, and the query performance of the database is improved.
The memory may include volatile memory in a computer readable medium, Random Access Memory (RAM) and/or nonvolatile memory such as Read Only Memory (ROM) or flash memory (flash RAM), and the memory includes at least one memory chip.
The present application further provides a computer program product adapted to perform program code for initializing the following method steps when executed on a data processing device: receiving input query parameters; acquiring metadata for describing data attributes; constructing an analysis tree from the query parameters and the metadata, the analysis tree having a plurality of data nodes; judging whether each data node of the analysis tree meets a preset aggregation condition or not; if so, aggregating the data nodes meeting the preset aggregation condition; and generating a query script according to the aggregated analysis tree.
As will be appreciated by one skilled in the art, embodiments of the present application may be provided as a method, system, or computer program product. Accordingly, the present application may take the form of an entirely hardware embodiment, an entirely software embodiment or an embodiment combining software and hardware aspects. Furthermore, the present application may take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, and the like) having computer-usable program code embodied therein.
The present application is described with reference to flowchart illustrations and/or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the application. It will be understood that each flow and/or block of the flow diagrams and/or block diagrams, and combinations of flows and/or blocks in the flow diagrams and/or block diagrams, can be implemented by computer program instructions. These computer program instructions may be provided to a processor of a general purpose computer, special purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, create means for implementing the functions specified in the flowchart flow or flows and/or block diagram block or blocks.
These computer program instructions may also be stored in a computer-readable memory that can direct a computer or other programmable data processing apparatus to function in a particular manner, such that the instructions stored in the computer-readable memory produce an article of manufacture including instruction means which implement the function specified in the flowchart flow or flows and/or block diagram block or blocks.
These computer program instructions may also be loaded onto a computer or other programmable data processing apparatus to cause a series of operational steps to be performed on the computer or other programmable apparatus to produce a computer implemented process such that the instructions which execute on the computer or other programmable apparatus provide steps for implementing the functions specified in the flowchart flow or flows and/or block diagram block or blocks.
In a typical configuration, a computing device includes one or more processors (CPUs), input/output interfaces, network interfaces, and memory.
The memory may include forms of volatile memory in a computer readable medium, Random Access Memory (RAM) and/or non-volatile memory, such as Read Only Memory (ROM) or flash memory (flash RAM). The memory is an example of a computer-readable medium.
Computer-readable media, including both non-transitory and non-transitory, removable and non-removable media, may implement information storage by any method or technology. The information may be computer readable instructions, data structures, modules of a program, or other data. Examples of computer storage media include, but are not limited to, phase change memory (PRAM), Static Random Access Memory (SRAM), Dynamic Random Access Memory (DRAM), other types of Random Access Memory (RAM), Read Only Memory (ROM), Electrically Erasable Programmable Read Only Memory (EEPROM), flash memory or other memory technology, compact disc read only memory (CD-ROM), Digital Versatile Discs (DVD) or other optical storage, magnetic cassettes, magnetic tape magnetic disk storage or other magnetic storage devices, or any other non-transmission medium that can be used to store information that can be accessed by a computing device. As defined herein, a computer readable medium does not include a transitory computer readable medium such as a modulated data signal and a carrier wave.
The above are merely examples of the present application and are not intended to limit the present application. Various modifications and changes may occur to those skilled in the art. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present application should be included in the scope of the claims of the present application.
Claims (12)
1. A method for generating a query script, comprising:
receiving input query parameters;
acquiring metadata for describing data attributes;
constructing an analysis tree according to the query parameters and the metadata, wherein the analysis tree is provided with a plurality of data nodes, and the data nodes of the analysis tree record data information in the form of data tables;
judging whether each data node of the analysis tree meets a preset aggregation condition or not, wherein the judging step comprises the following steps: judging whether a data table in the analysis tree is an index table, wherein the index table is a data table containing index columns; judging whether data containing grouping syntax fields exist in a data table corresponding to the data nodes or not, wherein the data containing the grouping syntax fields are used for aggregating query columns needing grouping aggregation; if the data containing the grouping syntax field exists in the data table corresponding to the data node, the grouping function is included in the current data table; finally, judging whether other query columns except the index column in the query columns of the data table contain database grouping syntax fields, if the other query columns except the index column in the query columns of the data table contain the grouping syntax fields, indicating that the other query columns except the index column need to be calculated and the other columns in the data table need to use grouping functions, and further judging that each data node of the analysis tree meets preset aggregation conditions;
if so, aggregating the data nodes meeting the preset aggregation condition;
and generating a query script according to the aggregated analysis tree.
2. The method of claim 1, wherein the data nodes of the parse tree correspond to a data table, and the determining whether each data node of the parse tree meets a preset aggregation condition comprises:
acquiring a data table corresponding to the data node, and determining a query column of the data table;
judging whether an index column exists in the query column, wherein the index column refers to a column related to data calculation;
if so, judging that the data node meets a preset aggregation condition; otherwise, judging that the data node does not accord with the preset aggregation condition.
3. The method according to claim 2, wherein after determining that the index column exists in the query column and before determining that the data node meets a preset aggregation condition, the method further comprises:
acquiring data containing a packet syntax field;
judging whether the data containing the grouping grammar field exists in a data table corresponding to the data node;
if so, judging that the data node meets a preset aggregation condition; otherwise, judging that the data node does not accord with the preset aggregation condition.
4. The method according to claim 3, wherein after determining that the data containing the packet syntax field exists in the data table corresponding to the data node, before determining that the data node meets a preset aggregation condition, the method further comprises:
judging whether the query columns except the index column in the query columns contain the grouping grammar field;
if so, judging that the data node meets a preset aggregation condition; otherwise, judging that the data node does not accord with the preset aggregation condition.
5. The method according to any one of claims 2-4, wherein before aggregating data nodes meeting preset aggregation conditions, the method further comprises:
acquiring a field which identifies whether data aggregation can be carried out in the analysis tree;
setting the field for identifying whether the data aggregation can be carried out as the field capable of carrying out the data aggregation;
the generating a query script according to the aggregated analysis tree includes:
reading data information in a data table corresponding to each data node in the aggregated analysis tree;
and writing the data information into a preset template file to generate the query script.
6. An apparatus for generating a query script, comprising:
the receiving unit is used for receiving input query parameters;
a first acquisition unit configured to acquire metadata describing a data attribute;
the construction unit is used for constructing an analysis tree according to the query parameters and the metadata, the analysis tree is provided with a plurality of data nodes, and the data nodes of the analysis tree record data information in the form of a data table;
the judging unit is used for judging whether each data node of the analysis tree meets a preset aggregation condition or not, and comprises the following steps: judging whether a data table in the analysis tree is an index table, wherein the index table is a data table containing index columns; judging whether data containing grouping syntax fields exist in a data table corresponding to the data nodes or not, wherein the data containing the grouping syntax fields are used for aggregating query columns needing grouping aggregation; if the data containing the grouping syntax field exists in the data table corresponding to the data node, the grouping function is included in the current data table; finally, judging whether other query columns in the query columns of the data table except the index column contain the database grouping syntax fields, if so, indicating that the other query columns in the query columns of the data table except the index column need to be calculated and all the other columns in the data table need to use grouping functions, and further judging that each data node of the analysis tree accords with a preset aggregation condition;
the aggregation unit is used for aggregating the data nodes meeting the preset aggregation condition if each data node of the analysis tree meets the preset aggregation condition;
and the generating unit is used for generating a query script according to the aggregated analysis tree.
7. The apparatus of claim 6, wherein the data nodes of the parse tree correspond to data tables, and wherein the determining unit comprises:
the first acquisition module is used for acquiring a data table corresponding to the data node and determining a query column of the data table;
the first judgment module is used for judging whether an index column exists in the query column, wherein the index column refers to a column related to data calculation;
the first judging module is further configured to judge that the data node meets a preset aggregation condition if an index column exists in the query column;
the first judging module is further configured to judge that the data node does not meet a preset aggregation condition if no index column exists in the query column.
8. The apparatus according to claim 7, wherein the judging unit further comprises:
the second acquisition module is used for acquiring data containing a grouping syntax field after judging that the index column exists in the query column and before judging that the data node meets the preset aggregation condition;
the second judging module is used for judging whether the data containing the grouping grammar field exists in a data table corresponding to the data node;
the second judging module is further configured to judge that the data node meets a preset aggregation condition if the data containing the packet syntax field exists in a data table corresponding to the data node;
the second judging module is further configured to judge that the data node does not meet a preset aggregation condition if the data containing the packet syntax field does not exist in the data table corresponding to the data node.
9. The apparatus according to claim 8, wherein before determining that the data node meets the preset aggregation condition, the determining unit further comprises:
a third judging module, configured to, after judging that the data including the grouping syntax field exists in the data table corresponding to the data node, judge whether query columns other than the index column in the query column all include the grouping syntax field;
the third judging module is further configured to judge that the data node meets a preset aggregation condition if all query columns in the query columns except the index column include the grouping syntax field;
the third judging module is further configured to judge that the data node does not meet a preset aggregation condition if the query columns in the query columns except the index column do not include the packet syntax field.
10. The apparatus according to any one of claims 7-9, further comprising:
a second obtaining unit, configured to obtain a field that identifies whether data aggregation is possible in the parse tree;
a setting unit, configured to set a field indicating whether data aggregation is possible to be performed as the field capable of performing data aggregation;
the generation unit includes:
the reading module is used for reading data information in a data table corresponding to each data node in the aggregated analysis tree;
and the writing module is used for writing the data information into a preset template file to generate the query script.
11. A storage medium, comprising a stored program, wherein when the program runs, a device in which the storage medium is located is controlled to execute the method for generating a query script according to any one of claims 1 to 5.
12. A processor, wherein the processor is configured to execute a program, wherein the program executes the method for generating a query script according to any one of claim 1 to claim 5.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201611204520.XA CN108241622B (en) | 2016-12-23 | 2016-12-23 | Query script generation method and device |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201611204520.XA CN108241622B (en) | 2016-12-23 | 2016-12-23 | Query script generation method and device |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN108241622A CN108241622A (en) | 2018-07-03 |
| CN108241622B true CN108241622B (en) | 2022-07-05 |
Family
ID=62703983
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201611204520.XA Active CN108241622B (en) | 2016-12-23 | 2016-12-23 | Query script generation method and device |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN108241622B (en) |
Families Citing this family (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN109840257B (en) * | 2018-12-15 | 2023-11-21 | 中国平安人寿保险股份有限公司 | Database query method, database query device, computer device and readable storage medium |
| CN111782670A (en) * | 2020-06-29 | 2020-10-16 | 中国工商银行股份有限公司 | Method and device for automatically processing data resource operation |
Citations (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101246495A (en) * | 2008-03-21 | 2008-08-20 | 深圳国人通信有限公司 | Method for constructing query scheme and executing data query and statistics |
| CN105912595A (en) * | 2016-04-01 | 2016-08-31 | 华南理工大学 | Data origin collection method of relational databases |
Family Cites Families (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20140046876A1 (en) * | 2010-06-29 | 2014-02-13 | Morf Dynamics Pty Ltd | System and method of providing a computer-generated response |
| KR101368024B1 (en) * | 2012-03-29 | 2014-02-27 | 주식회사 엘지씨엔에스 | Method of managing script, server performing the same and storage media storing the same |
-
2016
- 2016-12-23 CN CN201611204520.XA patent/CN108241622B/en active Active
Patent Citations (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101246495A (en) * | 2008-03-21 | 2008-08-20 | 深圳国人通信有限公司 | Method for constructing query scheme and executing data query and statistics |
| CN105912595A (en) * | 2016-04-01 | 2016-08-31 | 华南理工大学 | Data origin collection method of relational databases |
Non-Patent Citations (1)
| Title |
|---|
| 多Agent聚合模型的研究与实现;郭改改;《中国优秀博硕士学位论文全文数据库(硕士)信息科技辑》;20141115;全文 * |
Also Published As
| Publication number | Publication date |
|---|---|
| CN108241622A (en) | 2018-07-03 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN105183735B (en) | The querying method and inquiry unit of data | |
| CN108932257B (en) | Multi-dimensional data query method and device | |
| US10552423B2 (en) | Semantic tagging of nodes | |
| CN110895544B (en) | Interface data processing method, device, system and storage medium | |
| CN115129782A (en) | Partition level connection method and device for distributed database | |
| CN113112208A (en) | Electric power material storage management method based on three-dimensional visualization model | |
| US20180357278A1 (en) | Processing aggregate queries in a graph database | |
| JP2022050327A (en) | Computer-implemented system and method using digital twin and graph-based structure | |
| US20210365458A1 (en) | Data imprints techniques for use with data retrieval methods | |
| CN115905630A (en) | Graph database query method, device, equipment and storage medium | |
| CN108241622B (en) | Query script generation method and device | |
| CN108241620B (en) | Query script generation method and device | |
| CN114297204A (en) | Data storage and retrieval method and device for heterogeneous data source | |
| US20130124484A1 (en) | Persistent flow apparatus to transform metrics packages received from wireless devices into a data store suitable for mobile communication network analysis by visualization | |
| CN110765100B (en) | Label generation method and device, computer readable storage medium and server | |
| CN108241624B (en) | Query script generation method and device | |
| CN110019357B (en) | Database query script generation method and device | |
| CN108228604B (en) | Model construction method based on memory object, information query method and device | |
| CN115293243A (en) | Method, device and equipment for realizing intelligent matching of data assets | |
| US20220342887A1 (en) | Predictive query processing | |
| CN108121733B (en) | Data query method and device | |
| CN111125087A (en) | Data storage method and device | |
| CN110019198B (en) | Data query method and device | |
| CN110609926A (en) | Data tag storage management method and device | |
| CN115455035B (en) | Data query model construction method and computer-readable storage medium |
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 |
Address after: 100083 No. 401, 4th Floor, Haitai Building, 229 North Fourth Ring Road, Haidian District, Beijing Applicant after: Beijing Guoshuang Technology Co.,Ltd. Address before: 100086 Cuigong Hotel, 76 Zhichun Road, Shuangyushu District, Haidian District, Beijing Applicant before: Beijing Guoshuang Technology Co.,Ltd. |
|
| CB02 | Change of applicant information | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |