NL2SQL generation method based on large language model

Abstract

The invention 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 steps that SQL mode positioning is conducted by combining three dimensions of semantic mapping, historical query logs and external mode links; constructing a sample fusing hierarchical screening and comparative learning; based on the candidate SQL and the multi-dimensional confidence fusion, the multiple candidate SQL are generated and subjected to cross validation, and the method improves the recognition accuracy, reduces the error rate of a grammar structure and improves the reliability of a 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 field can more accurately handle queries related to complex business rules. These characteristics make large models the core technical 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. 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

[0005] 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.

[0006] In a first aspect, the present application provides a NL2SQL generation method based on a large language model, which comprises: Step 1, combining semantic mapping, historical query log, and external schema linking in three dimensions, SQL schema positioning is performed; Step 2, based on step 1, a sample integrating hierarchical screening and contrast learning is constructed; Step 3, based on step 2, multiple candidate SQLs are generated and cross-validated based on candidate SQLs and multi-dimensional confidence fusion.

[0007] Optionally, the step 1 comprises: a. Semantic mapping: For expressions with differences between user input and database physical structure, a domain-specific dictionary is constructed to convert fuzzy expressions in user original queries into standard semantic expressions; b. Historical query log: To deal with context-dependent queries, a historical query log is maintained to store semantic intentions in user sessions; 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; The storage strategy of the historical query log is to store the table structure information obtained in this query after the semantic mapping and semantic completion when a correct query is completed; c. External schema linking: based on the principles of semantic priority, expansion assistance, and dynamic calibration; First, the table structure and business description information are encoded and stored in the vector database, and the table with the highest semantic similarity of 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 from the main core table; finally, the weight is dynamically optimized through business feedback, and the historical record log and knowledge graph are updated in time.

[0008] Optionally, the step 2 comprises: 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 encoded query description and SQL keyword, stored 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 examples and their correction logic, retrieve SQL that is similar to the current problem and generates errors, and mark the error reason in the prompt, and through example comparison, to enhance the model's understanding at the SQL level.

[0009] Optionally, the step 3 comprises: First, the input after semantic mapping and semantic completion is generated N semantic similar expressions by the large language model LLM; then, N independent SQL generations are performed based on each similar expression; then, evaluation is performed from three dimensions, the three dimensions are execution consistency, model result output credibility 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.

[0010] Optionally, it comprises: For each candidate , the comprehensive confidence is defined as: ; Wherein, and ; The formula of each component in the comprehensive confidence is defined as: Execution consistency : The proportion of SQL statements with the same execution results, the formula is: ; Wherein, , respectively represent the execution results of the query , ; 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 as the confidence of the entire SQL statement, and the formula is: ; Among them, represents the probability value of each token output by the last layer of the model after the softmax function; Semantic alignment degree : The similarity between the similar expression corresponding to the candidate SQL and the input is calculated, and the formula is: ; Among them, is an adjustable scaling coefficient; represents the sentence vector after the kth similar expression is encoded by the model; represents the sentence vector after the input is encoded by the model; represents the cosine similarity between the query vector and the candidate vector, and the formula is: .

[0011] In a second aspect, the embodiments of the present application provide a computer readable storage medium, the computer readable storage medium comprising a stored program, wherein the program controls the device where the computer readable storage medium is located 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 when the program is running.

[0012] In a third aspect, the embodiments of the present application provide an electronic device, 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, which when executed by the device, cause the device 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.

[0013] The technical scheme provided by the present application comprises 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; based on the candidate SQL and multi-dimensional confidence fusion, generating multiple candidate SQLs and cross- verifying them, which improves the accuracy of recognition, reduces the error rate of the syntax structure, and improves the reliability of the query result. BRIEF DESCRIPTION OF DRAWINGS

[0014] In order to more clearly illustrate the technical solutions of the embodiments of the present application, the drawings needed to be used in the embodiments will be briefly introduced as follows. Obviously, the drawings in the following description are only some embodiments of the present application, and other drawings can be obtained by those skilled in the art without creative effort on the basis of these drawings.

[0015] Figure 1 The flow chart of the NL2SQL generation method based on a large language model provided by the embodiments of the present application is shown in the figure. Figure 2 The schematic diagram of constructing a knowledge graph provided by the embodiments of the present application is shown in the figure. Figure 3 The schematic diagram of an electronic device provided by the embodiments of the present application is shown in the figure. DETAILED DESCRIPTION

[0016] In order to make the objects, 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 in conjunction with the drawings in the embodiments of the present application. Obviously, the described embodiments are only some embodiments of the present application, not all the embodiments. Based on the embodiments in the present application, all other embodiments obtained by those skilled in the art without creative effort fall within the scope of protection of the present application.

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

[0018] The terms used in the embodiments of the present application are only for the purpose of describing the specific embodiments, and are not intended to limit the present application. The singular forms "a", "said" and "the" used in the embodiments of the present application are also intended to include the plural forms, unless the context clearly indicates otherwise.

[0019] It should be understood that the term "and / or" used herein is only to describe the association relationship of the associated objects, which means that there can be three relationships, for example, A and / or B, which can represent the three cases of A alone, A and B together, and B alone. In addition, the character " / " in this paper generally represents that the front and rear associated objects are a "or" relationship.

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

[0021] Figure 1 A flowchart of the NL2SQL generation method based on a large language model provided by an embodiment of the present application is shown in FIG. 1, which includes the following steps. Figure 1 Step 1: SQL schema positioning in combination with semantic mapping, historical query log, and external schema linking.

[0022] In the embodiment of the present application, step 1 includes: a. Semantic mapping: For expressions (such as colloquial or fuzzy expressions) that differ from the physical structure of the database, a domain-specific lexicon is constructed to realize standardized mapping of terms, as shown in Table 1, to convert fuzzy expressions in the user's original query into standard semantic expressions, thereby more accurately positioning the tables and fields in the target database. Table 1: Domain-specific lexicon ; In the embodiment 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 stage.

[0023] b. Historical query log: To deal with context-dependent queries (such as omitting the subject or referring to the previous text), a historical query log is maintained 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 is missing information (such as only inputting "How much is it?"), the system will retrieve the historical query log, combine the last query, and infer the current intent to perform semantic completion. 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?", then the inferred current intent is "What is the price of the car with the highest sales?". This mechanism effectively solves the problem of anaphora resolution and context completion.

[0024] ​The storage strategy of the historical query log stores the expression after semantic mapping and semantic completion, and stores the table structure information found in this query when a correct query is finished, as shown in Table 2. Table 2 Storage strategy ; In the embodiment of the application, the 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, which can be directly used as a context reference for subsequent queries, significantly improving the understanding ability of the system for continuous queries.

[0025] c. External schema linking: To avoid the context information overload caused by the full-amount input schema linking strategy, the core table is located through semantic retrieval, the multi-dimensional expansion covers the full-amount potential table, and the feedback optimization is iterated continuously, which not only depends on the historical data to improve the efficiency, but also solves the missing table problem without historical data reference through business rules and real-time semantic analysis, finally realizes the goal that the related tables of user problems are recalled and irrelevant tables are filtered, and provides high-quality table structure input for subsequent SQL generation. The principle is semantic priority, expansion assistance, and dynamic calibration; First, the table structure and business description information are encoded and stored in the vector database, and the table with the highest semantic similarity to the user is taken from the vector database as the main core table; 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 Table 3 shows that 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.

[0026] Table 3 Construction strategy of knowledge graph .

[0027] Step 2, based on step 1, a sample is constructed by fusing hierarchical screening and contrast learning.

[0028] In the embodiment of the application, high-quality positive examples with semantic relevance and structural diversity are screened, and error-correction contrast samples are introduced to construct a context learning environment with more teaching significance, which effectively guides the model to generate SQL queries with correct grammar and consistent semantics.

[0029] In the embodiment of the application, step 2 includes: h. Hierarchical screening, 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 the 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; Encode data in historical logs or public data sets into vector database, dynamically adjust retrieval weight according to problem complexity (such as whether it contains multi-condition, aggregation function, subquery): simple problem focuses on text similarity, retrieve similar cases through text retrieval; complex problems focus on SQL structure similarity and metadata matching, retrieve similar SQL structure through keyword retrieval; for example, first retrieve user intent, identify query keywords and link information, if the user question involves keywords such as "GROUP BY, HAVING", etc., then prefer to filter samples containing this keyword.

[0030] i. Contrastive learning, by adding error samples and their correction logic, retrieve similar SQL that generates errors (such as field name spelling errors, JOIN condition omissions), and note the error reason in the prompt (such as "the following sample results in errors due to the absence of foreign keys between table A and table B, the correct approach is..."), compare samples to enhance the model's understanding at the SQL level.

[0031] Step 3, according to step 2, based on the candidate SQL and multi-dimensional confidence fusion, generate multiple candidate SQL and cross-verify them.

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

[0033] In the embodiment of the application, step 3 includes: To enhance the understanding and robustness of query semantics, first generate N semantic similar expressions through a large language model LLM for the input after semantic mapping and semantic completion; then, based on each similar expression, perform N independent SQL generation. Compared with the single generation of multiple results, multiple independent calls can more stably sample high-probability paths, improving expression diversity and quality; 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.

[0034] In the embodiments of the present application, the method comprises: For each candidate The comprehensive confidence is defined as: ; Wherein, And ; The formula of each component in the comprehensive confidence is defined as: Consistency of execution The formula of the proportion of SQL statements with the same execution result is: ; Wherein, , respectively represent the execution results of the query , ; Indicates a indicator function, which returns 1 when the condition is true, otherwise returns 0; Model result output credibility The formula of calculating the token power mean is the confidence of the whole SQL statement: ; Wherein, Indicates the probability value of each token output by the last layer of the model after the softmax function; Semantic alignment degree The formula of calculating the similarity between the similar expression corresponding to the candidate SQL and the input is: ; Wherein, Is an adjustable scaling coefficient; Indicates the sentence vector of the kth similar expression after model coding; Indicates the sentence vector of the input after model coding; Indicates the cosine similarity between the query vector and the candidate vector, and the formula is: .

[0035] In the technical scheme provided by the present application, the method comprises combining semantic mapping, historical query log and external schema linking three dimensions to locate the SQL schema; Constructing a sample that combines hierarchical screening and contrast learning; Based on the candidate SQL and multi-dimensional confidence fusion, generate multiple candidate SQL and cross-verify them, which improves the accuracy of recognition, reduces the error rate of syntax structure, and improves the reliability of query results.

[0036] The various steps of the embodiments of the present application can be executed by an electronic device. The electronic device includes, but is not limited to, a tablet computer, a portable PC, a desktop computer, and the like.

[0037] The embodiments of the present application provide a computer-readable storage medium, which includes a stored program. When the program is running, the computer-readable storage medium controls an electronic device where the computer-readable storage medium is located to execute the embodiments of the NL2SQL generation method based on a large language model.

[0038] Figure 3 A schematic diagram of an electronic device provided by the embodiments of the present application is shown in FIG. 21. As shown in FIG. 21, 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. The computer program 213 is executed by the processor 211 to implement the NL2SQL generation method based on a large language model in the embodiments. To avoid repetition, details are not described herein. Figure 3

[0039] The electronic device 21 includes, but is not limited to, the processor 211 and the memory 212. Those skilled in the art can understand that the electronic device 21 is only an example and does not constitute a limitation on the electronic device 21, and can include more or fewer components than those shown, or combine certain components, or different components, for example, the electronic device can also include an input / output device, a network access device, a bus, and the like. Figure 3 The electronic device 21 is only an example and does not constitute a limitation on the electronic device 21, and can include more or fewer components than those shown, or combine certain components, or different components, for example, the electronic device can also include an input / output device, a network access device, a bus, and the like.

[0040] The processor 211 can be a central processing unit (CPU), and can also be other general-purpose processors, digital signal processors (DSPs), application specific integrated circuits (ASICs), field programmable gate arrays (FPGAs) or other programmable logic devices, discrete gates or transistor logic components, discrete hardware components, or the like. The general-purpose processor can be a microprocessor or the processor can also be any conventional processor.

[0041] ​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. equipped on the electronic device 21. Further, the memory 212 can also 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.

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

[0043] The above only describes the preferred embodiments of the present application and is not intended to 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.

2. The method of claim 1, wherein, 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 information missing, the system will retrieve the historical query log, combine the last query to infer the current intent for semantic completion; The storage strategy of the historical query log is to store the table structure information obtained in this query after the correct query is completed; c. External schema linking: prioritize semantics, expand as a supplement, and dynamically calibrate; First, encode the table structure and business description information and store it in the vector database, and retrieve the table with the highest 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 update the historical record log and knowledge graph in a timely manner.

3. The method of claim 2, wherein, Step 2 comprises: h. Hierarchical screening: samples cover different business scenarios, complexity, and SQL structure, data comes from successfully executed SQL statements in the historical production environment and public data sets, classified by query complexity, respectively encode query description and SQL keywords, and store 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, enhance the model's understanding at the SQL level.

4. The method of claim 3, wherein, 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, generate N independent SQLs based on each similar expression; 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.

5. The method of claim 4, wherein, Comprise: For each candidate The overall confidence is defined as: ； wherein and ; The formula for each component in the comprehensive confidence is defined as: 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: 。 6. 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 in any one of claims 1 to 5 when the program is running.

7. 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 in any one of claims 1 to 5.

Images