NL2SQL generation method based on large language model

Abstract

The application relates to the technical field of artificial intelligence, and particularly provides an NL2SQL generation method based on a large language model. The method comprises the following steps: combining three dimensions of semantic mapping, historical query logs and external schema linkage to perform SQL schema positioning; constructing a sample that fuses hierarchical screening and contrast learning; and based on candidate SQL and multi-dimensional confidence fusion, generating multiple candidate SQL and cross- verifying the multiple candidate SQL. The method improves the recognition accuracy, reduces the error rate of the syntax structure, and improves the reliability of the query result.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of artificial intelligence, and particularly relates to an NL2SQL generation method based on a large language model. BACKGROUND

[0002] In recent years, the rapid development of large language models (LLMs) has brought breakthroughs in natural language to SQL (Text-to-SQL) conversion. Compared with traditional rule-based or statistical learning methods, large models exhibit significant technical advantages. In terms of semantic understanding, large models have strong context modeling capabilities and can accurately capture the true intent of user queries, even complex queries containing ambiguous expressions or multiple condition combinations can be effectively processed.

[0003] In terms of complex query generation, large models exhibit excellent performance. Traditional methods often perform poorly in handling complex scenarios such as multi-table association and nested queries, while large models have programming pattern recognition capabilities obtained through pre-training, which can automatically infer table relationships and generate correct JOIN operations. At the same time, large models support context-aware multi-round dialogue and can optimize current queries based on historical interaction information, significantly improving the interactive experience. For example, when a user first queries the total sales and then asks for grouping by region, the model can maintain context consistency and generate the correct GROUP BY clause. Large models also have excellent generalization ability and scalability. Through the pre-training of massive code and text, large models have mastered rich SQL syntax patterns and business scenario knowledge, and can adapt to the specific needs of different database schemas. In addition, through fine-tuning technology, the performance of the model on specific domains or enterprise private data can be further improved. For example, a model fine-tuned in the financial domain can more accurately handle queries related to complex business rules. These characteristics make large models the core technology foundation for building a new generation of intelligent database query systems.

[0004] Traditional database query systems rely heavily on users' mastery of professional query languages such as SQL, making it difficult for non-technical users to efficiently access data and creating a high usage threshold. Although natural language query systems have reduced this threshold to some extent, existing solutions still have many key defects, limiting their actual application effect.

[0005] There are three key defects in the existing natural language query conversion to structured query language (Natural Language to SQL, NL2SQL) generation method in practical application, which seriously restricts its effect. First, at the semantic analysis level, the system is difficult to achieve accurate identification of tables and fields, especially in dealing with unstructured business terms and complex multi-round dialogue, the field mapping accuracy is generally low. Second, in the SQL generation link, the existing method cannot fully ensure the accuracy of the syntax structure. Most importantly, even if the generated SQL statement is grammatically correct and executable, there will still be a deviation problem that the execution result is inconsistent with the user's real intention. SUMMARY

[0006] Therefore, the present application provides a NL2SQL generation method based on a large language model to improve the accuracy of identification, reduce the error rate of syntax structure, and improve the reliability of query results.

[0007] In a first aspect, the present application provides a NL2SQL generation method based on a large language model, which comprises:

[0008] Step 1, combine semantic mapping, historical query log, and external schema linking three dimensions to locate the SQL schema;

[0009] Step 2, based on step 1, build a sample that integrates hierarchical screening and contrast learning;

[0010] Step 3, based on step 2, generate multiple candidate SQLs and cross-validate them based on candidate SQLs and multi-dimensional confidence fusion.

[0011] Optionally, the step 1 comprises:

[0012] a. Semantic mapping: For expressions with differences between user input and database physical structure, build a domain-specific dictionary to convert fuzzy expressions in user original queries into standard semantic expressions;

[0013] b. Historical query log: To deal with context-dependent queries, maintain a historical query log to store semantic intentions in user sessions;

[0014] The use strategy of the historical query log is that when the model identifies that the user input has information missing, the system will retrieve the historical query log, combine the last query to infer the current intention for semantic completion;

[0015] The storage strategy of the historical query log is to store the table structure information obtained in this query after semantic mapping and semantic completion when a correct query is completed;

[0016] c. External schema linkage: prioritize semantics, expand as a supplement, and dynamically calibrate as a principle;

[0017] First, encode the table structure and business description information and store it in the vector database. The table with the highest semantic similarity to the user is taken as the main core table. Second, use the knowledge graph to expand the potential related tables from the main core table. Finally, dynamically optimize the weight through business feedback and update the historical record log and knowledge graph in a timely manner.

[0018] Optionally, the step 2 comprises:

[0019] h. Hierarchical screening, the examples cover different business scenarios, complexity and SQL structure, the data comes from successfully executed SQL statements in the historical production environment and public data sets, classified by query complexity, respectively encode the query description and SQL keyword, and store in the vector database;

[0020] Encode the data in the historical log or public data set into the vector database, and dynamically adjust the retrieval weight according to the complexity of the problem: for simple problems, focus on text similarity and retrieve similar cases through text retrieval; for complex problems, focus on SQL structure similarity and metadata matching, and retrieve similar SQL structure expressions through keyword retrieval;

[0021] i. Contrastive learning, by adding error examples and their correction logic, retrieve SQL that is similar to the current problem and generates errors, and specify the error reason in the prompt, and through example comparison, to enhance the model's understanding at the SQL level.

[0022] Optionally, the step 3 comprises:

[0023] First, generate N semantic similar expressions for the input that has undergone semantic mapping and semantic completion through a large language model LLM; then, based on each similar expression, perform N independent SQL generation; then, evaluate from three dimensions: execution consistency, model result output credibility, and semantic alignment; finally, calculate the comprehensive confidence of each candidate SQL by weighted fusion of the scores of the above three dimensions, and select the SQL with the highest score as the output result.

[0024] Optionally, it comprises:

[0025] For each candidate Its comprehensive confidence is defined as:

[0026] ;

[0027] Wherein, And ;

[0028] The formula for each component in the comprehensive confidence is defined as:

[0029] Consistency of execution : The proportion of SQL statements with the same execution results, the formula is:

[0030] ;

[0031] Among them, , respectively represent the execution results of query , ; Indicate a function, return 1 when the condition is true, otherwise return 0;

[0032] Model result output credibility : Calculate the token power mean, which is the confidence of the whole SQL statement, the formula is:

[0033] ;

[0034] Among them, represent the probability value of each token output by the last layer of the model after the softmax function;

[0035] Semantic alignment degree : Calculate the similarity between the input and the corresponding similar expression of the candidate SQL, the formula is:

[0036] ;

[0037] Among them, is an adjustable scaling coefficient; represent the sentence vector after the kth similar expression is encoded by the model; represent the sentence vector after the input is encoded by the model; represent the cosine similarity between the query vector and the candidate vector, the formula is:

[0038] .

[0039] In a second aspect, the embodiments of the present application provide a computer readable storage medium, the computer readable storage medium comprises a stored program, wherein when the program runs, the device where the computer readable storage medium is located is controlled to execute the NL2SQL generation method based on the large language model in the first aspect or any possible implementation manner of the first aspect.

[0040] In a third aspect, an electronic device is provided, comprising: one or more processors; a memory; and one or more computer programs, wherein the one or more computer programs are stored in the memory, and the one or more computer programs comprise instructions that, when executed by the device, cause the device to perform the method for NL2SQL generation based on a large language model according to the first aspect or any possible implementation manner of the first aspect.

[0041] The technical solution provided by the present application includes combining semantic mapping, historical query logs, and external schema linking in three dimensions to locate the SQL schema; constructing a sample that integrates hierarchical screening and contrast learning; and generating multiple candidate SQLs based on candidate SQLs and multi-dimensional confidence fusion, and cross- verifying the candidate SQLs. This method improves the accuracy of recognition, reduces the error rate of syntax structure, and improves the reliability of query results. BRIEF DESCRIPTION OF DRAWINGS

[0042] In order to more clearly illustrate the technical solutions of the embodiments of the present application, the drawings needed in the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present application, and other drawings can be obtained by those skilled in the art without creative labor.

[0043] Figure 1 The flowchart of the method for NL2SQL generation based on a large language model provided by the present application is shown in the following figure.

[0044] Figure 2 The schematic diagram of constructing a knowledge graph provided by the present application is shown in the following figure.

[0045] Figure 3 The schematic diagram of an electronic device provided by the present application is shown in the following figure. DETAILED DESCRIPTION

[0046] In order to make the purpose, technical solutions and advantages of the embodiments of the present application clearer, the technical solutions in the embodiments of the present application will be described clearly and completely below with reference to the drawings in the embodiments of the present application. Obviously, the described embodiments are only some embodiments of the present application, not all embodiments. Based on the embodiments in the present application, all other embodiments obtained by those skilled in the art without creative labor are within the scope of protection of the present application.

[0047] It should be clear that the described embodiments are only some embodiments of the present application, not all embodiments. Based on the embodiments in the present application, all other embodiments obtained by those skilled in the art without creative labor are within the scope of protection of the present application.

[0048] The terminology used in the embodiments of the present application is for the purpose of describing particular embodiments only and is not intended to be limiting of the present application. As used in the description of the embodiments of the present application, the singular forms "a," "an," and "the" are intended to include the plural forms as well, unless the context clearly indicates otherwise.

[0049] It should be understood that the term "and / or" as used herein merely describes associated objects, which can exist in three relationships, for example, A and / or B, which can represent three cases: A exists alone, A and B exist together, and B exists alone. In addition, the character " / " herein generally represents an "or" relationship between the front and rear associated objects.

[0050] Depending on the context, the word "if" as used herein can be interpreted as meaning "when" or "while" or "in response to determining" or "in response to detecting." Similarly, depending on the context, the phrase "if it is determined" or "if (a stated condition or event) is detected" can be interpreted as meaning "when it is determined" or "in response to determining" or "when (a stated condition or event) is detected" or "in response to detecting (a stated condition or event)."

[0051] Figure 1 The flowchart of the NL2SQL generation method based on a large language model provided by the embodiments of the present application is shown in Figure 1 The method comprises the following steps:

[0052] Step 1: SQL schema positioning is performed in combination with three dimensions of semantic mapping, historical query logs, and external schema linking.

[0053] In the embodiments of the present application, step 1 comprises:

[0054] a. Semantic mapping: For expressions (such as colloquialization and fuzzification) that differ from the physical structure of the database, a domain-specific lexicon (Domain-specific Lexicon) is constructed to realize term standardization mapping, as shown in Table 1, to convert the fuzzy expressions in the user's original query into standard semantic expressions, so as to more accurately locate the tables and fields in the target database;

[0055] Table 1 Domain-specific Lexicon

[0056] ;

[0057] In the embodiments of the present application, for example, the user input is: "What is the most sold car", and after term standardization mapping, it is: "What are the car brands with the highest sales?" This process significantly improves the accuracy of the model in the schema linking (schema linking) stage.

[0058] b. Historical Query Log: To deal with context-dependent queries (such as omitting the subject, referring to the previous text), a historical query log is maintained to store the semantic intent in the user's session;

[0059] The use strategy of the historical query log is that when the model identifies that the user input has information missing (such as only inputting "how much is it?"), the system will retrieve the historical query log, combine the last query, infer the current intent, and perform semantic completion;

[0060] In the embodiment of the present application, for example, the last query is "what is the most sold car", and the current query is "how much is it?", the inferred current intent is "how much is the best-selling car?", and this mechanism effectively solves the problem of anaphora resolution and context completion.

[0061] The storage strategy of the historical query log is to store the expressions that have been semantically mapped and semantically completed, and when a correct query is completed, the table structure information obtained in this query is also stored, as shown in Table 2.

[0062] Table 2 Storage strategy

[0063] ;

[0064] In the embodiment of the present application, this storage strategy ensures that each log record contains complete and professional semantic information, avoiding information fragmentation. Since the latest record has fused historical context, its semantic integrity is as high as 90% or more, and can be directly used as a context reference for subsequent queries, significantly improving the system's understanding ability for continuous queries.

[0065] c. External schema linking: To avoid the context information overload caused by the full input schema linking strategy, the core table is located through semantic retrieval, the multi-dimensional expansion covers all potential tables, and feedback optimization is continuously iterated, which not only depends on historical data to improve efficiency, but also solves the problem of missing tables without historical data reference through business rules and real-time semantic analysis, finally realizes the goal that the tables related to the user's problem are recalled and the irrelevant tables are filtered, and provides high-quality table structure input for subsequent SQL generation. The principles are semantic priority, expansion as auxiliary, and dynamic calibration;

[0066] First, the table structure and business description information are encoded and stored in a vector database, and the table with the highest semantic similarity to the user is taken as the main core table from the vector database; second, the knowledge graph is used to expand the potential related tables (including tables with historical association and tables without historical association) from the main core table; finally, the weight is dynamically optimized through business feedback, and the historical record log and the knowledge graph are updated in time, as shown in Figure 2 The order table is taken as the core table to construct the knowledge graph, and the construction strategy of the knowledge graph is shown in Table 3.

[0067] Table 3 Construction strategy of knowledge graph

[0068] .

[0069] Step 2, based on step 1, construct a sample that integrates hierarchical screening and contrastive learning.

[0070] In the embodiment of the application, by screening high-quality positive examples with semantic correlation and structural diversity, and introducing error-correction control samples, a more meaningful context learning environment is constructed, which effectively guides the model to generate SQL queries that are grammatically correct and semantically consistent.

[0071] In the embodiment of the application, step 2 includes:

[0072] h, hierarchical screening, the sample covers different business scenarios, complexity and SQL structure (single table query, multi-table JOIN, subquery, etc.), data comes from successfully executed SQL statements in historical production environment, public data sets, classified by query complexity, respectively encode query description (SQL and table structure as metadata) and SQL keyword encoding (query description, table structure, SQL statement as metadata), and store in vector database;

[0073] Encode the data in the historical log or public data set into the vector database, and dynamically adjust the retrieval weight according to the complexity of the problem (such as whether it contains multi-condition, aggregation function, subquery): for simple problems, focus on text similarity, and retrieve similar cases through text retrieval; for complex problems, focus on SQL structure similarity and metadata matching, and retrieve similar SQL structure expressions through keyword retrieval; for example, first retrieve the user intent, identify the query keywords and link information, and if the user question involves keywords such as "GROUP BY, HAVING", etc., then preferentially screen samples containing the keywords.

[0074] i, contrastive learning, by adding error samples and their correction logic, retrieve SQL that is similar to the current problem and generates errors (such as misspelling of field name, omission of JOIN condition), and specify the error reason in the prompt (such as "the following sample is incorrect because it does not associate table A and table B foreign keys, the correct approach is..."), and through sample comparison, to enhance the model's understanding at the SQL level.

[0075] Step 3, based on step 2, generate multiple candidate SQLs based on candidate SQLs and multi-dimensional confidence fusion, and cross-verify them.

[0076] In the embodiment of the application, the strategy filters the optimal SQL by comprehensively evaluating the consistency of execution, the reliability of model result output and the semantic alignment degree of three key dimensions. The three dimensions are cross-verified from the database execution level, the model generation level and the semantic understanding level, and finally the highest weighted confidence is selected as the best SQL.

[0077] In the embodiment of the application, step 3 comprises:

[0078] To enhance the understanding and robustness of query semantics, first, the input after semantic mapping and semantic completion is generated N semantic similar expressions by a large language model LLM; then, N independent SQL generations are performed based on each similar expression, compared with the single generation of multiple results, multiple independent calls can more stably sample high probability paths, and improve the diversity and quality of expressions; then, three dimensions are evaluated, and the three dimensions are execution consistency, model result output reliability and semantic alignment degree; finally, the scores of the above three dimensions are fused by weighting, the comprehensive confidence of each candidate SQL is calculated, and the SQL with the highest score is selected as the output result.

[0079] In the embodiment of the application, it comprises:

[0080] For each candidate , the comprehensive confidence is defined as:

[0081] ;

[0082] Wherein, and ;

[0083] The formula of each component in the comprehensive confidence is defined as:

[0084] Execution consistency : The proportion of SQL statements with the same execution result, and the formula is:

[0085] ;

[0086] Wherein, , respectively represent the execution results of the query , ; Indicates an indicator function, which returns 1 when the condition is true, and 0 otherwise;

[0087] Model result output reliability : The power mean of token is the confidence of the whole SQL statement, and the formula is:

[0088] ;

[0089] in, This represents the probability value of each token output from the last layer of the model after passing through the softmax function;

[0090] Semantic alignment The formula for calculating the similarity between the candidate SQL and the input is as follows:

[0091] ;

[0092] in, This is an adjustable scaling factor; This represents the sentence vector after the k-th similar expression is encoded by the model; This represents the sentence vector after the input has been encoded by the model; The cosine similarity between the query vector and the candidate vector is expressed by the following formula:

[0093] .

[0094] The technical solution provided by this invention includes a method for SQL pattern localization that combines three dimensions: semantic mapping, historical query logs, and external pattern links; constructing a sample that integrates hierarchical filtering and comparative learning; and generating multiple candidate SQLs based on candidate SQL and multi-dimensional confidence fusion and cross-validating them. This method improves the accuracy of identification, reduces the error rate of syntax structure, and enhances the reliability of query results.

[0095] The various steps in the embodiments of the present invention can be performed by an electronic device. This electronic device includes, but is not limited to, tablet computers, portable PCs, and desktop computers.

[0096] This invention provides a computer-readable storage medium including a stored program, wherein, when the program is running, it controls the electronic device containing the computer-readable storage medium to execute the above-described embodiment of the NL2SQL generation method based on a large language model.

[0097] Figure 3 A schematic diagram of an electronic device provided in an embodiment of the present invention, such as... Figure 3 As shown, the electronic device 21 includes a processor 211, a memory 212, and a computer program 213 stored in the memory 212 and executable on the processor 211. When the computer program 213 is executed by the processor 211, it implements the NL2SQL generation method based on the large language model in the embodiment. To avoid repetition, it will not be described in detail here.

[0098] Electronic device 21 includes, but is not limited to, processor 211 and memory 212. Those skilled in the art will understand that...Figure 3 The electronic device 21 is merely an example and does not limit the electronic device 21, which can include more or less components than shown, or combine some components, or different components, for example, the electronic device can also include an input / output device, a network access device, a bus, etc.

[0099] The processor 211 can be a central processing unit (CPU), and can also be other general-purpose processors, a digital signal processor (DSP), an application specific integrated circuit (ASIC), a field-programmable gate array (FPGA) or other programmable logic device, discrete gate or transistor logic, discrete hardware components, etc. The general-purpose processor can be a microprocessor or the processor can also be any conventional processor.

[0100] The memory 212 can be an internal storage unit of the electronic device 21, for example, a hard disk or a memory of the electronic device 21. The memory 212 can also be an external storage device of the electronic device 21, for example, a plug-in hard disk, a smart media card (SMC), a secure digital (SD) card, a flash card, etc. Further, the memory 212 can include both the internal storage unit and the external storage device of the electronic device 21. The memory 212 is used to store computer programs and other programs and data required by the network device. The memory 212 can also be used to temporarily store data that has been output or will be output.

[0101] Those skilled in the art can clearly understand that, for the convenience and brevity of description, the specific working process of the system, device and unit described above can refer to the corresponding process in the foregoing method embodiments, which will not be described here.

[0102] The above only describes the preferred embodiments of the present application and does not limit the present application. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present application shall be included in the protection scope of the present application.

Claims

1. A method for NL2SQL generation based on a large language model, characterized in that, The method comprises: Step 1, combining semantic mapping, historical query log, and external schema linking, to locate the SQL schema; Step 2, based on step 1, to construct a sample that integrates hierarchical screening and contrastive learning; Step 3, based on step 2, to generate multiple candidate SQLs and cross-verify them based on candidate SQLs and multi-dimensional confidence fusion; Step 1 comprises: a. Semantic mapping: For expressions that differ from the user input and the physical structure of the database, construct a domain-specific dictionary to convert ambiguous expressions in the user's original query into standard semantic expressions; b. Historical query log: To deal with context-dependent queries, maintain a historical query log to store the semantic intent in the user's session; The use strategy of the historical query log is that when the model identifies that the user input has missing information, the system will retrieve the historical query log, combine the last query, and infer the current intent to complete the semantics; The storage strategy of the historical query log is to store the table structure information obtained in this query after a correct query is completed; c. External schema linking: Principle of semantic priority, expansion as auxiliary, and dynamic calibration; First, encode the table structure and business description information and store it in the vector database, and take the table with the highest user semantic similarity from the vector database as the main core table; second, use the knowledge graph to expand the potential related tables from the main core table; finally, dynamically optimize the weight through business feedback and timely update the historical record log and knowledge graph; Step 2 comprises: h. Hierarchical screening, the sample covers different business scenarios, complexity, and SQL structure, the data comes from successfully executed SQL statements in the historical production environment and public data sets, classified by query complexity, respectively encode the query description and SQL keyword, and store it in the vector database; Encode the data in the historical log or public data set into the vector database, and dynamically adjust the retrieval weight according to the complexity of the problem: for simple problems, focus on text similarity and retrieve similar cases through text retrieval; for complex problems, focus on SQL structure similarity and metadata matching, and retrieve similar SQL structure expressions through keyword retrieval; i. Contrastive learning, by adding error samples and their correction logic, retrieve SQLs that are similar to the current problem and generate errors, and specify the error reasons in the prompt, and through sample comparison, to enhance the model's understanding at the SQL level; Step 3 comprises: First, generate N semantic similar expressions through the large language model LLM for the input that has undergone semantic mapping and semantic completion; then, based on each similar expression, generate N independent SQLs; then, evaluate from three dimensions: execution consistency, model result output confidence, and semantic alignment; finally, calculate the comprehensive confidence of each candidate SQL by weighted fusion of the scores of the above three dimensions, and select the SQL with the highest score as the output result; Comprehensive confidence component formula definition: For each candidate The overall confidence is defined as: ； wherein and ; ​ Consistency of execution : The percentage of SQL statements whose execution results are the same, whose formula is: ； wherein , respectively represent the execution results of queries , ; represents an indicator function that returns 1 when the condition is true, and 0 otherwise. Model result output credibility : the token power mean is calculated, that is, the confidence of the whole SQL statement, and the formula is: ； wherein, represents the probability value of each token output by the last layer of the model after passing through the softmax function; Semantic alignment degree : the similarity between the similar expression corresponding to the candidate SQL and the input, and the formula is: ； wherein, is an adjustable scaling factor; represents the sentence vector of the kth similar expression after model encoding; represents the sentence vector of the input after model encoding; represents the cosine similarity between the query vector and the candidate vector, and the formula is: 。 2. A computer-readable storage medium, characterized in that, The computer readable storage medium comprises a stored program, wherein the program controls the device where the computer readable storage medium is located to perform the large language model based NL2SQL generation method of claim 1 when the program is running.

3. An electronic device, comprising: comprise: one or more processors; a memory; and one or more computer programs, wherein the one or more computer programs are stored in the memory, and the one or more computer programs comprise instructions that, when executed by the device, cause the device to perform the large language model based NL2SQL generation method of claim 1.

Images