SQL statement generation method, device, equipment and storage medium

Abstract

The present invention discloses a method, device, equipment and storage medium for generating SQL statements. The method comprises: obtaining a natural language query statement; inputting the natural language query statement into a pre-trained target SQL statement generation model to generate a target SQL statement, wherein the target SQL statement generation model has a grammar self-checking function and a logic self-checking function. The embodiments of the present invention are applicable to a wide range of scenarios, and the generated SQL statements have a high accuracy rate, conform to grammar and logic specifications, and can greatly reduce the workload of secondary modification of SQL statements, thereby solving the problems that the current SQL statement generation technology is limited to specific scenarios, has high development costs, and has a low accuracy rate of generated statements.

Description

Technical Field

[0001] The present invention relates to the field of data processing technology, and in particular to a method, device, equipment and storage medium for generating SQL statements. Background Art

[0002] With the rapid development of large model (LM) technology, the performance of large language models (LLM) has been continuously improved, showing a good user experience in the field of text generation, and can generate logical and diverse answers based on user questions. However, the current research on the vertical professional field of NL2SQL (converting natural language NL into SQL statements) is not in-depth enough.

[0003] Currently, there are mainly the following methods for generating SQL statements:

[0004] 1. Model-driven SQL generation. This method uses a formal modeling language to establish strict requirements and design models, and then uses a compilation rule-based SQL generation method to convert semantics such as formal language models into SQL statements to ensure that the generated SQL statements are consistent with the requirements. The main problem with this method is that the premise of the model-driven SQL generation method is to establish a formal language model. Therefore, people who understand the requirements and are familiar with formal language modeling are needed to understand natural language and establish corresponding models according to the problem requirements. This step is relatively difficult and costly, and cannot be widely promoted in all scenarios of SQL queries.

[0005] 2. SQL generation based on a large model. This method is based on SQL pre-training to obtain a large language model that can understand SQL. Based on such a large language model, the requirements description or other prompts in natural language can be used to directly generate SQL statements. The main problem with this method is that the current SQL large model is implemented using the same architecture as the natural language large model, but SQL has the characteristics of simple syntax, clear hierarchy, strict semantics, and unambiguous expression compared to natural language. As a result, the quality of the generated SQL statements is not high and grammatical and logical errors are prone to occur.

[0006] In summary, the existing SQL generation technologies have disadvantages to varying degrees, such as being limited to specific scenarios, high development costs, and low accuracy of generated SQL statements. There is still a lack of effective methods for generating SQL statements that meet grammatical specifications according to user intentions, which poses a challenge to the application and popularization of NL2SQL. Summary of the invention

[0007] The present invention provides a method, device, equipment and storage medium for generating SQL statements, so as to realize accurate generation of SQL statements conforming to grammatical and logical specifications.

[0008] According to one aspect of the present invention, a method for generating an SQL statement is provided, the method comprising:

[0009] Get natural language query statements;

[0010] The natural language query statement is input into a pre-trained target SQL statement generation model to generate a target SQL statement, wherein the target SQL statement generation model has a grammar self-checking function and a logic self-checking function.

[0011] Optionally, the training step of the target SQL statement generation model includes:

[0012] Build an initial SQL statement generation model;

[0013] Performing syntax self-check training on the initial SQL statement generation model based on the training sample and the SQL compiler to obtain a standby SQL statement generation model;

[0014] Based on the training samples, the workflow diagram generation model and the SQL compiler, logic self-check training is performed on the standby SQL statement generation model to obtain a target SQL statement generation model.

[0015] Optionally, the performing grammar self-checking training on the initial SQL statement generation model based on the training sample and the SQL compiler to obtain a spare SQL statement generation model includes:

[0016] Obtaining a training sample, wherein the training sample includes a training query statement and a corresponding standard SQL statement;

[0017] Using the initial SQL statement generation model and the SQL compiler to generate an initial SQL statement corresponding to the training query statement, wherein the initial SQL statement passes the syntax specification check of the SQL compiler;

[0018] Obtaining a training loss function according to the standard SQL statement and the initial SQL statement;

[0019] The initial SQL statement generation model is back-propagated through the training loss function to obtain a backup SQL statement generation model.

[0020] Optionally, the using the initial SQL statement generation model and the SQL compiler to generate the initial SQL statement corresponding to the training query statement, wherein the initial SQL statement passes the syntax specification check of the SQL compiler, includes:

[0021] Inputting the training query statement into the initial SQL statement generation model to obtain a candidate SQL statement;

[0022] Compile the candidate SQL statement using an SQL compiler. If the candidate SQL statement fails to be compiled by the SQL compiler, feed back the compilation result to the initial SQL statement generation model, and regenerate a new candidate SQL statement until it passes the SQL compiler compilation.

[0023] The candidate SQL statement compiled by the SQL compiler is used as the initial SQL statement.

[0024] Optionally, performing logic self-check training on the standby SQL statement generation model based on the training sample, the workflow diagram generation model and the SQL compiler to obtain a target SQL statement generation model includes:

[0025] Obtain training samples containing training query statements;

[0026] Using a workflow diagram generation model to generate a query statement workflow diagram corresponding to the training query statement;

[0027] Using the standby SQL statement generation model to generate a standby SQL statement corresponding to the training query statement, and using the SQL compiler to generate a SQL workflow diagram corresponding to the standby SQL statement;

[0028] Obtaining a graph comparison loss function according to the query statement workflow graph and the SQL workflow graph;

[0029] The backup SQL statement generation model is back-propagated through the graph contrast loss function to obtain a target SQL statement generation model.

[0030] Optionally, the workflow diagram generation model includes a problem step decomposition model, a step chart generation model and a workflow diagram generation sub-model;

[0031] Accordingly, the use of the workflow graph generation model to generate a query statement workflow graph corresponding to the training query statement includes:

[0032] Using a problem step decomposition model to decompose the training query statement into steps to obtain a problem solving step set;

[0033] Using a step chart generation model to generate a problem-solving structure diagram corresponding to the problem-solving step set;

[0034] A workflow diagram generation sub-model is used to generate a query statement workflow diagram corresponding to the problem solving structure diagram.

[0035] Optionally, obtaining a graph comparison loss function according to the query statement workflow graph and the SQL workflow graph includes:

[0036] Calculating graph similarity between the query statement workflow graph and the SQL workflow graph, wherein the graph similarity includes structural similarity and attribute similarity;

[0037] According to the graph similarity and in combination with a given loss function expression, a graph comparison loss function is determined.

[0038] Optionally, after generating the target SQL statement, it also includes:

[0039] The target SQL statement is verified using an SQL compiler and / or a workflow diagram generation model. If the target SQL statement verification fails, the verification result is returned to the target SQL statement generation model, and a new target SQL statement is regenerated until the verification passes.

[0040] Optionally, the using of a SQL compiler and / or a workflow diagram generation model to verify the target SQL statement includes:

[0041] Using an SQL compiler to perform syntax verification on the target SQL statement, and / or using a workflow diagram generation model to perform logic verification on the target SQL statement;

[0042] If the target SQL statement fails the syntax verification or the logic verification, it is determined that the target SQL statement fails the verification; otherwise,

[0043] Determine whether the target SQL statement has passed verification.

[0044] According to another aspect of the present invention, there is provided a device for generating a SQL statement, the device comprising:

[0045] A statement acquisition module is used to acquire natural language query statements;

[0046] The statement generation module is used to input the natural language query statement into a pre-trained target SQL statement generation model to generate a target SQL statement, wherein the target SQL statement generation model has a grammar self-checking function and a logic self-checking function.

[0047] According to another aspect of the present invention, an electronic device is provided, the electronic device comprising:

[0048] at least one processor; and

[0049] a memory communicatively connected to the at least one processor; wherein,

[0050] The memory stores a computer program executable by the at least one processor, and the computer program is executed by the at least one processor so that the at least one processor can execute the SQL statement generating method described in any embodiment of the present invention.

[0051] According to another aspect of the present invention, a computer-readable storage medium is provided, wherein the computer-readable storage medium stores computer instructions, and the computer instructions are used to enable a processor to implement the SQL statement generation method described in any embodiment of the present invention when executed.

[0052] The technical solution of the embodiment of the present invention generates a target SQL statement by inputting the acquired natural language query statement into a pre-trained target SQL statement generation model, wherein the target SQL statement generation model has a grammar self-checking function and a logic self-checking function, thereby solving the problems that the current SQL statement generation technology is limited to specific scenarios, the development cost is high, and the accuracy of the generated statements is low. The embodiment of the present invention has a wide range of applicable scenarios, and the generated SQL statements have high accuracy and comply with grammar and logic specifications, which can greatly reduce the workload of secondary modification of SQL statements.

[0053] It should be understood that the contents described in this section are not intended to identify the key or important features of the embodiments of the present invention, nor are they intended to limit the scope of the present invention. Other features of the present invention will become easily understood through the following description. BRIEF DESCRIPTION OF THE DRAWINGS

[0054] In order to more clearly illustrate the technical solutions in the embodiments of the present invention, the following briefly introduces the drawings required for use in the description of the embodiments. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without creative work.

[0055] Figure 1 This is a flow chart of a method for generating a SQL statement provided in Embodiment 1 of the present invention;

[0056] Figure 2 It is a flow chart of a training method of a target SQL statement generation model in a SQL statement generation method provided in Embodiment 1 of the present invention;

[0057] Figure 3 It is a schematic diagram of the training process from an initial SQL statement generation model to a backup SQL statement generation model in a SQL statement generation method provided in the first embodiment of the present invention;

[0058] Figure 4It is a schematic diagram of a training process from a standby SQL statement generation model to a target SQL statement generation model in a SQL statement generation method provided in Embodiment 1 of the present invention;

[0059] Figure 5 This is a first effect schematic diagram of generating a query statement workflow diagram in a SQL statement generating method provided in the first embodiment of the present invention;

[0060] Figure 6 This is a second effect schematic diagram of generating a query statement workflow diagram in a SQL statement generating method provided in the first embodiment of the present invention;

[0061] Figure 7 It is a third effect schematic diagram of generation of a query statement workflow diagram in a SQL statement generation method provided in the first embodiment of the present invention;

[0062] Figure 8 This is a first effect schematic diagram of generating an SQL workflow diagram in a SQL statement generating method provided in the first embodiment of the present invention;

[0063] Fig. 9 This is a fourth effect schematic diagram of generating a query statement workflow diagram in a SQL statement generating method provided in the first embodiment of the present invention;

[0064] Fig.10 This is a schematic diagram of a second effect of generating an SQL workflow diagram in a SQL statement generating method provided in the first embodiment of the present invention;

[0065] Fig.11 It is a structural diagram of a SQL statement generating device provided according to the second embodiment of the present invention;

[0066] Fig.12 It is a structural schematic diagram of an electronic device for implementing the SQL statement generating method according to an embodiment of the present invention. DETAILED DESCRIPTION

[0067] In order to enable those skilled in the art to better understand the scheme of the present invention, the technical scheme in the embodiments of the present invention will be clearly and completely described below in conjunction with the drawings in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work should fall within the scope of protection of the present invention.

[0068] It should be noted that the terms "first", "second", etc. in the specification and claims of the present invention and the above-mentioned drawings are used to distinguish similar objects, and are not necessarily used to describe a specific order or sequence. It should be understood that the data used in this way can be interchanged where appropriate, so that the embodiments of the present invention described herein can be implemented in an order other than those illustrated or described herein. In addition, the terms "including" and "having" and any variations thereof are intended to cover non-exclusive inclusions, for example, a process, method, system, product or device that includes a series of steps or units is not necessarily limited to those steps or units that are clearly listed, but may include other steps or units that are not clearly listed or inherent to these processes, methods, products or devices.

[0069] Embodiment 1

[0070] Figure 1 A flowchart of a method for generating a SQL statement is provided for the first embodiment of the present invention. This embodiment is applicable to the case of generating a SQL statement corresponding to a natural language query statement. The method can be executed by a SQL statement generating device. The SQL statement generating device can be implemented in the form of hardware and / or software. The SQL statement generating device can be configured in a computer device. Figure 1 As shown, the method includes:

[0071] S110: Obtain a natural language query statement.

[0072] Among them, the natural language query statement can be understood as a question composed of natural language, such as a question asked by a user on a securities platform: What is the Shanghai Composite Index on each trading day in 2022? "What is the Shanghai Composite Index on each trading day in 2022" can be used as a natural language query statement in this embodiment.

[0073] S120, inputting a natural language query statement into a pre-trained target SQL statement generation model to generate a target SQL statement, wherein the target SQL statement generation model has a syntax self-checking function and a logic self-checking function.

[0074] In this embodiment, when a natural language query statement is obtained, the natural language query statement can be input as input data into the target SQL statement generation model, and a target SQL statement is output, where the target SQL statement is a SQL query statement corresponding to the semantics of the natural language query statement. In practical applications, the target SQL statement generation model can be pre-trained, and the target SQL statement generation model can have a grammar self-checking function and a logic self-checking function, that is, the SQL statement generated by the target SQL statement generation model conforms to the SQL grammar specification and the logic specification.

[0075] Optional, Figure 21 is a flow chart of a training method for a target SQL statement generation model in a SQL statement generation method provided by an embodiment of the present invention. Figure 2 As shown, the training steps of the target SQL statement generation model in the SQL statement generation method provided in this embodiment may include:

[0076] A1. Build an initial SQL statement generation model.

[0077] In this embodiment, any model building method can be used to build an initial SQL statement generation model, and the initial SQL statement generation model has the function of generating SQL statements corresponding to natural language query statements. It can be understood that compared with the target SQL statement generation model finally trained in this embodiment, the accuracy of the SQL statements generated by the initial SQL statement generation model is lower.

[0078] In practical applications, the NL2SQL model learning based on LLM can follow the general learning paradigm of large models, and obtain the initial SQL statement generation model through Mask learning, instruction learning, fine-tuning learning, etc. The Mask learning stage can mainly use text corpus containing SQL statements, and instruction learning and fine-tuning learning can mainly use question-answer pair corpus in the form of {Query-SQL}, where "Query" is the question that the user needs to query, and "SQL" is the query statement corresponding to "Query". After training, the NL2SQL model can have the ability to generate relevant SQL statements based on the Query question, and the obtained initial SQL statement generation model can be recorded as the NL2SQL-llm_init model.

[0079] A2. Perform grammar self-check training on the initial SQL statement generation model based on the training samples and the SQL compiler to obtain a backup SQL statement generation model.

[0080] In this embodiment, the SQL compiler can detect whether the SQL statement generated by the initial SQL statement generation model conforms to the grammatical specification, and return the detected result information to the initial SQL statement generation model, so that the initial SQL statement generation model performs enhanced learning, thereby improving the ability of the initial SQL statement generation model to generate SQL statements with correct syntax. After enhanced learning, a backup SQL statement generation model can be obtained, which can be recorded as the NL2SQL-llm_rltf model.

[0081] Furthermore, Figure 3 Schematic diagram of the training process from the initial SQL statement generation model to the standby SQL statement generation model in the SQL statement generation method provided in the embodiment of the present invention. Figure 3 As shown, the training process from the initial SQL statement generation model to the standby SQL statement generation model in A2 may include the following steps:

[0082] A21. Obtain training samples, where the training samples include training query statements and corresponding standard SQL statements.

[0083] Specifically, the training samples for training the backup SQL statement generation model can be the same as the sample set used to build the initial SQL statement generation model, such as including training query statements and corresponding standard SQL statements, that is, question-answer pair corpus in the form of {Query-SQL}, where "Query" can represent the training query statement, that is, the question that the user needs to query, and "SQL" represents the standard SQL statement corresponding to "Query".

[0084] A22. Use the initial SQL statement generation model and the SQL compiler to generate an initial SQL statement corresponding to the training query statement, and the initial SQL statement passes the syntax specification check of the SQL compiler.

[0085] Specifically, the SQL compiler can be used as an evaluator of the SQL statement results generated by the initial SQL statement generation model. The SQL compiler performs syntax evaluation on the SQL statements generated by the initial SQL statement generation model, so that the initial SQL statements generated by the initial SQL statement generation model can pass the syntax specification check, thereby achieving the effect of improving the accuracy of SQL statement generation.

[0086] In one embodiment, A22 can be implemented by:

[0087] The training query statement is input into the initial SQL statement generation model to obtain a candidate SQL statement; the candidate SQL statement is compiled using the SQL compiler. If the candidate SQL statement fails to pass the SQL compiler compilation, the compilation result is fed back to the initial SQL statement generation model, and a new candidate SQL statement is regenerated until it passes the SQL compiler compilation; the candidate SQL statement compiled by the SQL compiler is used as the initial SQL statement.

[0088] This step can be understood as the forward calculation process in the training process, wherein the candidate SQL statement can be understood as an SQL statement whose grammatical correctness has not yet been determined. When a grammatical error occurs in the candidate SQL statement generated by the initial SQL statement generation model, the initial SQL statement generation model can receive error feedback information from the SQL compiler, and regenerate the candidate SQL statement according to the feedback information until the newly generated candidate SQL statement is compiled by the SQL compiler and output as the initial SQL statement. In this embodiment, Calcite can be selected as the SQL compiler.

[0089] A23. Obtain a training loss function based on the standard SQL statement and the initial SQL statement.

[0090] Specifically, since the standard SQL statement is annotated according to actual needs, the initial SQL statement is converted and output by an untrained SQL statement generation model. There will inevitably be errors between the standard SQL statement and the initial SQL statement. The loss between the standard SQL statement and the initial SQL statement can be calculated to form a training loss function to achieve training and parameter adjustment of the initial SQL statement generation model.

[0091] A24. Back-propagate the initial SQL statement generation model through the training loss function to obtain a backup SQL statement generation model.

[0092] Specifically, after obtaining the training loss function, the initial SQL statement generation model can be back-propagated through the training loss function, and the model parameters can be continuously adjusted to finally obtain the backup SQL statement generation model.

[0093] A3. Perform logic self-check training on the standby SQL statement generation model based on the training samples, the workflow diagram generation model and the SQL compiler to obtain the target SQL statement generation model.

[0094] In this embodiment, the workflow diagram generation model and the SQL compiler can be used to detect whether the SQL statements generated by the standby SQL statement generation model are logically self-consistent, and the detected result information is returned to the standby SQL statement generation model, so that the standby SQL statement generation model performs enhanced learning, thereby improving the ability of the standby SQL statement generation model to generate logically self-consistent SQL statements. After enhanced learning, the target SQL statement generation model can be obtained, which can be recorded as the NL2SQL-llm model.

[0095] Furthermore, Figure 4 Schematic diagram of the training process from the standby SQL statement generation model to the target SQL statement generation model in the SQL statement generation method provided in the embodiment of the present invention. Figure 4 As shown, the training process from the standby SQL statement generation model to the target SQL statement generation model in A3 may include the following steps:

[0096] A31. Obtain training samples containing training query statements.

[0097] Specifically, the training samples for training the backup SQL statement generation model can be the same as the sample set used to build the initial SQL statement generation model, such as a question-answer pair corpus in the form of {Query-SQL}, where "Query" can represent the training query statement, that is, the question that the user needs to query, and "SQL" represents the standard SQL statement corresponding to "Query".

[0098] A32. Use the workflow diagram generation model to generate a query statement workflow diagram corresponding to the training query statement.

[0099] Specifically, a workflow graph generation model that can generate a query statement workflow graph corresponding to a training query statement can be pre-trained. The training of this model is not the focus of the present invention and will not be described in detail here. In addition, an existing workflow graph generation model can also be used to generate a query statement workflow graph corresponding to a training query statement, which can be recorded as Query-workflow-DAG.

[0100] In one embodiment, applying the workflow diagram generation model may include a problem step decomposition model, a step chart generation model, and a workflow diagram generation sub-model.

[0101] Accordingly, the generation operation of the query statement workflow diagram in A32 can be implemented in the following ways:

[0102] The problem step decomposition model is used to decompose the training query statement into steps to obtain a set of problem-solving steps; the step chart generation model is used to generate a problem-solving structure diagram corresponding to the problem-solving step set; the workflow diagram generation sub-model is used to generate a query statement workflow diagram corresponding to the problem-solving structure diagram.

[0103] In this embodiment, the solve steps decomposition model (solve steps decomposition LLM model) can use existing large models, such as GPT-4, Claude, etc. You can also develop a large model yourself, and use the corpus in the form of {Query-COT} to train the large model. "Query" can represent the training query statement, that is, the question that the user needs to query, and "COT" can represent the problem-solving steps corresponding to "Query". The trained model can have the ability to generate corresponding problem-solving thinking chains based on the problem. After the model completes learning, the training query statement is input into the problem step decomposition model model, and the problem-solving step set of the training query statement can be output, which can be recorded as Solve-COTs.

[0104] The step chart generation model may include a semantic analysis algorithm, which uses the semantic analysis algorithm to parse the output result Solve-COTs of the problem step decomposition model, and parse out the entities in each step of Solve-COTs and the processing operations between entities, which can be abbreviated as [Xi->OPi->Yi]}, which can represent that the input of operation OPi is Xi and the output is Yi, and i can represent the sequence number of the operation step. It should be noted that the output and input between OPs may be the same object. Linking the triplets of the same object together can form a graph structure, which is the problem-solving structure diagram output by the step chart generation model, which can be recorded as COT-Graph.

[0105] The workflow graph generation sub-model can expand the COT-Graph from left to right according to the time sequence of the Solve-COTs steps, which is the workflow-DAG of how to solve the training query statement Query. The specific expansion method can be: select vertices with only out-degree and no in-degree as the starting point, and arrange them in stages according to the time sequence. The starting point of the same stage is parallel; starting from the selected starting point, expand along the depth according to the time sequence of the OP operation, and put the operations that can be parallel at the same time into the same stage. After the COT-Graph expansion work is completed, the query statement workflow graph corresponding to the training query statement is obtained, which can be recorded as Query-workflow-DAG.

[0106] In a specific example, Figure 5 It is a first effect schematic diagram of generating a query statement workflow diagram in the SQL statement generating method provided in an embodiment of the present invention; Figure 6 It is a second effect schematic diagram of the generation of a query statement workflow diagram in the SQL statement generation method provided in an embodiment of the present invention; Figure 7 It is a third effect schematic diagram of the generation of a query statement workflow diagram in the SQL statement generation method provided in an embodiment of the present invention. Taking the query question "The number of Type B enterprises in Region A and the total amount of their incentives in the past three months" as an example, the problem-solving step set Solve-COTs output by the problem step decomposition model is "Step 1: Use the WHERE clause to filter out the records of Type B enterprises in Region A, and use the AND connection condition to filter out the records of Type B enterprises in the past three months. Step 2: Use the DATE function to format the date of preplan_ann_date into the YYYY-MM-DD format, and use the ADD_MONTHS function to add 3 months to the current date to get the last day. Step 3: Use the SUM function to calculate the sum of s_inc_quantity for each record, and use the COUNT function to calculate the number of records of Type B enterprises in Region A. Step 4: Use the GROUP BY clause to group the calculation results by the number of companies, and use the SUM function to calculate the total amount of incentives for each company." Solve-COTs is parsed using the semantic analysis algorithm in the step chart generation model to parse out the entities in each step of Solve-COTs and the processing operations between entities, which can be simply recorded as follows: Figure 5 The [Xi->OPi->Yi] triples shown in the figure. Linking the triples of the same object together forms a graph structure, which is the problem-solving structure graph COT-Graph generated by the step graph generation model, as shown in Figure 6The workflow graph generation sub-model expands the COT-Graph from left to right according to the time sequence of the Solve-COTs steps, and generates the query statement workflow graph Query-workflow-DAG corresponding to the training query statement, as shown in Figure 7 shown.

[0107] A33. Use the backup SQL statement generation model to generate backup SQL statements corresponding to the training query statements, and use the SQL compiler to generate a SQL workflow diagram corresponding to the backup SQL statements.

[0108] Specifically, the training query statement can be input into the standby SQL statement generation model to obtain the standby SQL statement. The standby SQL statement is input into the SQL compiler, and based on the SQL parsing, logical plan, physical plan generation and other technologies used in Spark or Flink, a SQL workflow graph corresponding to the standby SQL statement can be obtained, which can be recorded as SQL-workflow-DAG.

[0109] In a specific example, Figure 8 It is a first effect schematic diagram of the generation of the SQL workflow diagram in the SQL statement generation method provided in an embodiment of the present invention. Taking the query question "the number of enterprises of type B in region A and the total amount of their incentives in the past three months" as an example, the standby SQL statement generated by the standby SQL statement generation model is "SELECT COUNT(DISTINCT company_name)AS company_count,SUM(incentive_amount)AS total_incentive_amount FROM enterprises WHERE province='region A'AND enterprise_type='type B enterprise'AND date>=DATE_SUB(CURRENT_DATE(),INTERVAL 3MONTH)". Based on the SQL parsing, logical plan, and physical plan generation technologies used in Spark, a DAG workflow diagram of the SQL corresponding to the standby SQL statement can be obtained, which is recorded as SQL-workflow-DAG, such as Figure 8 shown.

[0110] A34. According to the query statement workflow graph and the SQL workflow graph, obtain the graph comparison loss function.

[0111] Specifically, since the query statement workflow graph is generated based on the training query statements, and the SQL workflow graph is generated based on the backup SQL statements generated by the untrained backup SQL statement generation model, there will inevitably be errors between the query statement workflow graph and the SQL workflow graph. The loss between the query statement workflow graph and the SQL workflow graph can be calculated to form a graph comparison loss function to achieve training and parameter adjustment of the backup SQL statement generation model.

[0112] In one embodiment, A34 may be implemented as follows:

[0113] Calculate the graph similarity between the query statement workflow graph and the SQL workflow graph. The graph similarity includes structural similarity and attribute similarity. According to the graph similarity and the given loss function expression, determine the graph comparison loss function.

[0114] In this embodiment, the graph similarity of two workflow graphs can be calculated mainly based on the structure and attributes of the graph, which may include subgraph isomorphism, node similarity, edge similarity, etc., and the graph comparison loss function can be determined using algorithms such as Graph kernel. In addition, argmin(GraphKernel(G1, G2)) can be used as the optimization function to continuously train and continuously reduce the loss between Query-workflow-DAG and SQL-workflow-DAG.

[0115] A35. Back-propagate the backup SQL statement generation model through the graph comparison loss function to obtain the target SQL statement generation model.

[0116] Specifically, after obtaining the graph contrast loss function, the backup SQL statement generation model can be back-propagated through the graph contrast loss function, and the model parameters can be continuously adjusted to finally obtain the target SQL statement generation model.

[0117] In another specific example, Fig. 9 This is a fourth effect schematic diagram of generating a query statement workflow diagram in a SQL statement generating method provided in the first embodiment of the present invention. Fig.10 This is a second effect diagram of the generation of an SQL workflow diagram in a SQL statement generation method provided in the first embodiment of the present invention. The natural language query statement obtained is "query the 10 highest trading days of the Shanghai Composite Index in 2022". In the processing process of the target SQL statement generation model, the generated Query-workflow-DAG is as follows: Fig. 9As shown in the figure, the SQL statement that has passed the check by the SQL compiler Calcite is "SELECT date,shanghai_index FROM stock_prices WHERE date>='2022-01-01'AND date<='2022-12-31'ORDER BY shanghai_index DESC LIMIT 10", and the generated SQL-workflow-DAG is as follows Fig.10 The result of comparing the two pictures is: the similarity is high, so the generated SQL statement is adopted. Using this SQL statement to query the database, we can get the 10 highest trading days of the Shanghai Composite Index in 2022.

[0118] Optionally, the SQL statement generation method provided in this embodiment may further include the following steps:

[0119] After the target SQL statement is generated, the target SQL statement is verified using the SQL compiler and / or the workflow diagram generation model. If the target SQL statement fails the verification, the verification result is returned to the target SQL statement generation model, and a new target SQL statement is regenerated until the verification passes.

[0120] In this embodiment, since the training process of the target SQL statement generation model includes the learning and training of the grammatical self-checking function and the logical self-checking function, the target SQL statement generation model has the grammatical self-checking function and the logical self-checking function. However, in order to further ensure the accuracy of the generation of the target SQL statement, the generated target SQL statement can be subjected to grammatical verification and / or logical verification after the target SQL statement generation model generates the target SQL statement. In practical applications, the target SQL statement can be subjected to grammatical verification through the SQL compiler, or the target SQL statement can be subjected to logical verification through the workflow diagram generation model, or the target SQL statement can be subjected to grammatical and logical verification through the SQL compiler and the workflow diagram generation model at the same time. If the verification passes, the generation operation of this SQL statement can be terminated; if the verification fails, a new target SQL statement needs to be regenerated and verified again until the verification passes.

[0121] In practical applications, if the emphasis is on generating SQL statements quickly and efficiently, it is also possible not to use the SQL compiler and / or workflow diagram generation model verification, and directly use the target SQL statement generated by S120.

[0122] Furthermore, the process of using the SQL compiler and / or the workflow diagram generation model to verify the target SQL statement may include the following specific steps:

[0123] Use an SQL compiler to perform syntax verification on the target SQL statement, and / or use a workflow diagram generation model to perform logic verification on the target SQL statement; if the target SQL statement fails syntax verification or logic verification, it is determined that the target SQL statement verification fails; otherwise, it is determined that the target SQL statement verification passes.

[0124] In actual applications, the target SQL statement can be verified using the SQL compiler and workflow diagram generation model used when training the target SQL statement generation model.

[0125] The technical solution of the embodiment of the present invention generates a target SQL statement by inputting the acquired natural language query statement into a pre-trained target SQL statement generation model, wherein the target SQL statement generation model has a grammar self-checking function and a logic self-checking function, thereby solving the problems that the current SQL statement generation technology is limited to specific scenarios, the development cost is high, and the accuracy of the generated statements is low. The embodiment of the present invention has a wide range of applicable scenarios, and the generated SQL statements have high accuracy and comply with grammar and logic specifications, which can greatly reduce the workload of secondary modification of SQL statements.

[0126] Embodiment 2

[0127] Fig.11 A schematic diagram of the structure of a SQL statement generating device is provided for the third embodiment of the present invention. Fig.11 As shown, the device includes a statement acquisition module 210 and a statement generation module 220.

[0128] The sentence acquisition module 210 is used to acquire natural language query sentences.

[0129] The statement generation module 220 is used to input the natural language query statement into a pre-trained target SQL statement generation model to generate a target SQL statement. The target SQL statement generation model has a grammar self-checking function and a logic self-checking function.

[0130] Optionally, the training step of the target SQL statement generation model includes:

[0131] Build an initial SQL statement generation model;

[0132] Performing syntax self-check training on the initial SQL statement generation model based on the training sample and the SQL compiler to obtain a standby SQL statement generation model;

[0133] Based on the training samples, the workflow diagram generation model and the SQL compiler, logic self-check training is performed on the standby SQL statement generation model to obtain a target SQL statement generation model.

[0134] Optionally, the performing grammar self-checking training on the initial SQL statement generation model based on the training sample and the SQL compiler to obtain a spare SQL statement generation model includes:

[0135] Obtaining a training sample, wherein the training sample includes a training query statement and a corresponding standard SQL statement;

[0136] Using the initial SQL statement generation model and the SQL compiler to generate an initial SQL statement corresponding to the training query statement, wherein the initial SQL statement passes the syntax specification check of the SQL compiler;

[0137] Obtaining a training loss function according to the standard SQL statement and the initial SQL statement;

[0138] The initial SQL statement generation model is back-propagated through the training loss function to obtain a backup SQL statement generation model.

[0139] Optionally, the using the initial SQL statement generation model and the SQL compiler to generate the initial SQL statement corresponding to the training query statement, wherein the initial SQL statement passes the syntax specification check of the SQL compiler, includes:

[0140] Inputting the training query statement into the initial SQL statement generation model to obtain a candidate SQL statement;

[0141] Compile the candidate SQL statement using an SQL compiler. If the candidate SQL statement fails to be compiled by the SQL compiler, feed back the compilation result to the initial SQL statement generation model, and regenerate a new candidate SQL statement until it passes the SQL compiler compilation.

[0142] The candidate SQL statement compiled by the SQL compiler is used as the initial SQL statement.

[0143] Optionally, performing logic self-check training on the standby SQL statement generation model based on the training sample, the workflow diagram generation model and the SQL compiler to obtain a target SQL statement generation model includes:

[0144] Obtain training samples containing training query statements;

[0145] Using a workflow diagram generation model to generate a query statement workflow diagram corresponding to the training query statement;

[0146] Using the standby SQL statement generation model to generate a standby SQL statement corresponding to the training query statement, and using the SQL compiler to generate a SQL workflow diagram corresponding to the standby SQL statement;

[0147] Obtaining a graph comparison loss function according to the query statement workflow graph and the SQL workflow graph;

[0148] The backup SQL statement generation model is back-propagated through the graph contrast loss function to obtain a target SQL statement generation model.

[0149] Optionally, the workflow diagram generation model includes a problem step decomposition model, a step chart generation model and a workflow diagram generation sub-model;

[0150] Accordingly, the use of the workflow graph generation model to generate a query statement workflow graph corresponding to the training query statement includes:

[0151] Using a problem step decomposition model to decompose the training query statement into steps to obtain a problem solving step set;

[0152] Using a step chart generation model to generate a problem-solving structure diagram corresponding to the problem-solving step set;

[0153] A workflow diagram generation sub-model is used to generate a query statement workflow diagram corresponding to the problem solving structure diagram.

[0154] Optionally, obtaining a graph comparison loss function according to the query statement workflow graph and the SQL workflow graph includes:

[0155] Calculating graph similarity between the query statement workflow graph and the SQL workflow graph, wherein the graph similarity includes structural similarity and attribute similarity;

[0156] According to the graph similarity and in combination with a given loss function expression, a graph comparison loss function is determined.

[0157] Optionally, the device further includes a statement verification module 230, which is used to:

[0158] The target SQL statement is verified using an SQL compiler and / or a workflow diagram generation model. If the target SQL statement verification fails, the verification result is returned to the target SQL statement generation model, and a new target SQL statement is regenerated until the verification passes.

[0159] Optionally, the using of a SQL compiler and / or a workflow diagram generation model to verify the target SQL statement includes:

[0160] Using an SQL compiler to perform syntax verification on the target SQL statement, and / or using a workflow diagram generation model to perform logic verification on the target SQL statement;

[0161] If the target SQL statement fails the syntax verification or the logic verification, it is determined that the target SQL statement fails the verification; otherwise,

[0162] Determine whether the target SQL statement has passed verification.

[0163] The SQL statement generating device provided in the embodiment of the present invention can execute the SQL statement generating method provided in any embodiment of the present invention, and has the corresponding functional modules and beneficial effects of the execution method.

[0164] Embodiment 3

[0165] Fig.12 A schematic diagram of the structure of an electronic device 10 that can be used to implement an embodiment of the present invention is shown. The electronic device is intended to represent various forms of digital computers, such as laptop computers, desktop computers, workstations, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. The electronic device can also represent various forms of mobile devices, such as personal digital processing, cellular phones, smart phones, wearable devices (such as helmets, glasses, watches, etc.) and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely examples and are not intended to limit the implementation of the present invention described and / or required herein.

[0166] like Fig.12 As shown, the electronic device 10 includes at least one processor 11, and a memory connected to the at least one processor 11, such as a read-only memory (ROM) 12, a random access memory (RAM) 13, etc., wherein the memory stores a computer program that can be executed by at least one processor, and the processor 11 can perform various appropriate actions and processes according to the computer program stored in the read-only memory (ROM) 12 or the computer program loaded from the storage unit 18 to the random access memory (RAM) 13. In the RAM 13, various programs and data required for the operation of the electronic device 10 can also be stored. The processor 11, the ROM 12, and the RAM 13 are connected to each other through a bus 14. An input / output (I / O) interface 15 is also connected to the bus 14.

[0167] A number of components in the electronic device 10 are connected to the I / O interface 15, including: an input unit 16, such as a keyboard, a mouse, etc.; an output unit 17, such as various types of displays, speakers, etc.; a storage unit 18, such as a disk, an optical disk, etc.; and a communication unit 19, such as a network card, a modem, a wireless communication transceiver, etc. The communication unit 19 allows the electronic device 10 to exchange information / data with other devices through a computer network such as the Internet and / or various telecommunication networks.

[0168] The processor 11 may be a variety of general and / or special processing components with processing and computing capabilities. Some examples of the processor 11 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various special artificial intelligence (AI) computing chips, various processors running machine learning model algorithms, a digital signal processor (DSP), and any appropriate processor, controller, microcontroller, etc. The processor 11 executes the various methods and processes described above, such as a SQL statement generation method.

[0169] In some embodiments, the SQL statement generation method may be implemented as a computer program, which is tangibly contained in a computer-readable storage medium, such as a storage unit 18. In some embodiments, part or all of the computer program may be loaded and / or installed on the electronic device 10 via the ROM 12 and / or the communication unit 19. When the computer program is loaded into the RAM 13 and executed by the processor 11, one or more steps of the SQL statement generation method described above may be performed. Alternatively, in other embodiments, the processor 11 may be configured to execute the SQL statement generation method in any other appropriate manner (e.g., by means of firmware).

[0170] Various implementations of the systems and techniques described above herein can be implemented in digital electronic circuit systems, integrated circuit systems, field programmable gate arrays (FPGAs), application specific integrated circuits (ASICs), application specific standard products (ASSPs), systems on chips (SOCs), load programmable logic devices (CPLDs), computer hardware, firmware, software, and / or combinations thereof. These various implementations can include: being implemented in one or more computer programs that can be executed and / or interpreted on a programmable system including at least one programmable processor, which can be a special purpose or general purpose programmable processor that can receive data and instructions from a storage system, at least one input device, and at least one output device, and transmit data and instructions to the storage system, the at least one input device, and the at least one output device.

[0171] Computer programs for implementing the methods of the present invention may be written in any combination of one or more programming languages. These computer programs may be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing device, so that when the computer program is executed by the processor, the functions / operations specified in the flow chart and / or block diagram are implemented. The computer program may be executed entirely on the machine, partially on the machine, partially on the machine and partially on a remote machine as a stand-alone software package, or entirely on a remote machine or server.

[0172] In the context of the present invention, a computer-readable storage medium may be a tangible medium that may contain or store a computer program for use by or in combination with an instruction execution system, device or equipment. A computer-readable storage medium may include, but is not limited to, electronic, magnetic, optical, electromagnetic, infrared, or semiconductor systems, devices or equipment, or any suitable combination of the foregoing. Alternatively, a computer-readable storage medium may be a machine-readable signal medium. A more specific example of a machine-readable storage medium may include an electrical connection based on one or more lines, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing.

[0173] To provide interaction with a user, the systems and techniques described herein may be implemented on an electronic device having: a display device (e.g., a CRT (cathode ray tube) or LCD (liquid crystal display) monitor) for displaying information to the user; and a keyboard and a pointing device (e.g., a mouse or trackball) through which the user can provide input to the electronic device. Other types of devices may also be used to provide interaction with the user; for example, the feedback provided to the user may be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user may be received in any form (including acoustic input, voice input, or tactile input).

[0174] The systems and techniques described herein may be implemented in a computing system that includes backend components (e.g., as a data server), or a computing system that includes middleware components (e.g., an application server), or a computing system that includes frontend components (e.g., a user computer with a graphical user interface or a web browser through which a user can interact with implementations of the systems and techniques described herein), or a computing system that includes any combination of such backend components, middleware components, or frontend components. The components of the system may be interconnected by any form or medium of digital data communication (e.g., a communication network). Examples of communication networks include: a local area network (LAN), a wide area network (WAN), a blockchain network, and the Internet.

[0175] A computing system may include a client and a server. The client and the server are generally remote from each other and usually interact through a communication network. The client and server relationship is generated by computer programs running on the corresponding computers and having a client-server relationship with each other. The server may be a cloud server, also known as a cloud computing server or cloud host, which is a host product in the cloud computing service system to solve the defects of difficult management and weak business scalability in traditional physical hosts and VPS services.

[0176] It should be understood that the various forms of processes shown above can be used to reorder, add or delete steps. For example, the steps described in the present invention can be executed in parallel, sequentially or in different orders, as long as the desired results of the technical solution of the present invention can be achieved, and this document does not limit this.

[0177] The above specific implementations do not constitute a limitation on the protection scope of the present invention. It should be understood by those skilled in the art that various modifications, combinations, sub-combinations and substitutions can be made according to design requirements and other factors. Any modification, equivalent substitution and improvement made within the spirit and principle of the present invention should be included in the protection scope of the present invention.

Claims

1. A method for generating SQL statements, It is characterized in that include: Obtain natural language query statements; Inputting the natural language query statement into a pre-trained target SQL statement generation model to generate a target SQL statement, wherein the target SQL statement generation model has a syntax self-checking function and a logic self-checking function; The training steps of the target SQL statement generation model include: Build an initial SQL statement generation model; Performing syntax self-check training on the initial SQL statement generation model based on the training sample and the SQL compiler to obtain a standby SQL statement generation model; Based on the training sample, the workflow diagram generation model and the SQL compiler, the standby SQL statement generation model is subjected to logic self-check training to obtain a target SQL statement generation model; The step of performing logic self-check training on the standby SQL statement generation model based on the training sample, the workflow diagram generation model and the SQL compiler to obtain a target SQL statement generation model includes: Obtain training samples containing training query statements; Using a workflow diagram generation model to generate a query statement workflow diagram corresponding to the training query statement; Using the standby SQL statement generation model to generate a standby SQL statement corresponding to the training query statement, and using the SQL compiler to generate a SQL workflow diagram corresponding to the standby SQL statement; Obtaining a graph comparison loss function according to the query statement workflow graph and the SQL workflow graph; The backup SQL statement generation model is back-propagated through the graph contrast loss function to obtain a target SQL statement generation model.

2. The method according to claim 1, It is characterized in that The step of performing grammar self-checking training on the initial SQL statement generation model based on the training sample and the SQL compiler to obtain a standby SQL statement generation model includes: Obtaining a training sample, wherein the training sample includes a training query statement and a corresponding standard SQL statement; Using the initial SQL statement generation model and the SQL compiler to generate an initial SQL statement corresponding to the training query statement, wherein the initial SQL statement passes the syntax specification check of the SQL compiler; Obtaining a training loss function according to the standard SQL statement and the initial SQL statement; The initial SQL statement generation model is back-propagated through the training loss function to obtain a backup SQL statement generation model.

3. The method according to claim 2, It is characterized in that The using the initial SQL statement generation model and the SQL compiler to generate the initial SQL statement corresponding to the training query statement, wherein the initial SQL statement passes the syntax specification check of the SQL compiler, comprises: Inputting the training query statement into the initial SQL statement generation model to obtain a candidate SQL statement; Compile the candidate SQL statement using an SQL compiler. If the candidate SQL statement fails to be compiled by the SQL compiler, feed back the compilation result to the initial SQL statement generation model, and regenerate a new candidate SQL statement until it passes the SQL compiler compilation. The candidate SQL statement compiled by the SQL compiler is used as the initial SQL statement.

4. The method according to claim 1, It is characterized in that The workflow diagram generation model includes a problem step decomposition model, a step chart generation model and a workflow diagram generation sub-model; Accordingly, the use of the workflow graph generation model to generate a query statement workflow graph corresponding to the training query statement includes: Using a problem step decomposition model to decompose the training query statement into steps to obtain a problem solving step set; Using a step chart generation model to generate a problem-solving structure diagram corresponding to the problem-solving step set; A workflow diagram generation sub-model is used to generate a query statement workflow diagram corresponding to the problem solving structure diagram.

5. The method according to claim 1, It is characterized in that The obtaining of a graph comparison loss function according to the query statement workflow graph and the SQL workflow graph includes: Calculating graph similarity between the query statement workflow graph and the SQL workflow graph, wherein the graph similarity includes structural similarity and attribute similarity; According to the graph similarity and in combination with a given loss function expression, a graph comparison loss function is determined.

6. The method according to claim 1, It is characterized in that After generating the target SQL statement, it also includes: The target SQL statement is verified using an SQL compiler and / or a workflow diagram generation model. If the target SQL statement verification fails, the verification result is returned to the target SQL statement generation model, and a new target SQL statement is regenerated until the verification passes.

7. The method according to claim 6, It is characterized in that The using of the SQL compiler and / or the workflow diagram generation model to verify the target SQL statement includes: Using an SQL compiler to perform syntax verification on the target SQL statement, and / or using a workflow diagram generation model to perform logic verification on the target SQL statement; If the target SQL statement fails the syntax verification or the logic verification, it is determined that the target SQL statement fails the verification; otherwise, Determine whether the target SQL statement has passed verification.

8. A SQL statement generating device, It is characterized in that include: A statement acquisition module is used to acquire natural language query statements; A statement generation module, used for inputting the natural language query statement into a pre-trained target SQL statement generation model to generate a target SQL statement, wherein the target SQL statement generation model has a syntax self-checking function and a logic self-checking function; The training steps of the target SQL statement generation model include: Build an initial SQL statement generation model; Performing syntax self-check training on the initial SQL statement generation model based on the training sample and the SQL compiler to obtain a standby SQL statement generation model; Based on the training sample, the workflow diagram generation model and the SQL compiler, the standby SQL statement generation model is subjected to logic self-check training to obtain a target SQL statement generation model; The step of performing logic self-check training on the standby SQL statement generation model based on the training sample, the workflow diagram generation model and the SQL compiler to obtain a target SQL statement generation model includes: Obtain training samples containing training query statements; Using a workflow diagram generation model to generate a query statement workflow diagram corresponding to the training query statement; Using the standby SQL statement generation model to generate a standby SQL statement corresponding to the training query statement, and using the SQL compiler to generate a SQL workflow diagram corresponding to the standby SQL statement; Obtaining a graph comparison loss function according to the query statement workflow graph and the SQL workflow graph; The backup SQL statement generation model is back-propagated through the graph contrast loss function to obtain a target SQL statement generation model.

9. An electronic device, It is characterized in that The electronic device comprises: at least one processor; and a memory communicatively connected to the at least one processor; wherein, The memory stores a computer program executable by the at least one processor, and the computer program is executed by the at least one processor so that the at least one processor can execute the SQL statement generation method according to any one of claims 1 to 7.

10. A computer-readable storage medium, It is characterized in that The computer-readable storage medium stores computer instructions, and the computer instructions are used to enable a processor to implement the SQL statement generation method according to any one of claims 1 to 7 when executed.

Images