Sql generation method based on large language model and storage medium

Abstract

The application relates to the technical field of artificial intelligence, in particular to a SQL generation method based on a large language model and a storage medium, the SQL generation method based on the large language model comprises the following steps: extracting global and single table schema information from a database, respectively processing the global and single table schema information to generate a global description set and a column description set in natural language description; obtaining a user input question and related business knowledge to generate a semantic index corresponding to the user input question; and guiding a large language model to generate an output SQL. Through the global description set and the column description set, the large language model can perform a large amount of description information expansion on the database at two levels of global and local, so that the model can quickly understand the meaning represented by corresponding information in the database according to the description set expanded in advance when searching information in the database subsequently, the understanding ability of the system for the content of the database is improved, and the response speed and accuracy when generating the SQL subsequently are improved.

Description

[Technical Field]

[0002] This invention relates to the field of artificial intelligence technology, and in particular to a SQL generation method and storage medium based on a large language model. [Background Technology]

[0004] SQL (Structured Query Language) is a standardized language used to manage and manipulate relational databases. In the field of artificial intelligence, when a system needs to interact with structured data in a database, it typically needs to use SQL to instruct the system to perform various data processing operations.

[0005] To facilitate computers in translating users' natural language requests into a series of executable operations, technologies exist for generating SQL from natural language text. However, traditional SQL generation techniques have limited ability to understand complex natural language, and their processing capabilities and response speeds vary depending on the database structure. Furthermore, the accuracy of generating more complex SQL needs improvement. [Summary of the Invention]

[0007] To address the limitations of existing text-to-SQL generation technologies in understanding complex natural language and processing databases, resulting in low accuracy of the generated SQL, this invention provides a SQL generation method and storage medium based on a large language model.

[0008] The present invention provides a SQL generation method based on a large language model, comprising the following steps: extracting global schema information and foreign key constraints from a database; forming a global hint based on the global schema information and foreign key constraints; inputting the global hint into a large language model for information expansion to generate a global description set described in natural language; the global schema information includes table names, column names, column data types, column nullability, and primary key constraints in the database; the global description set is a natural language description of the database at the global level; extracting specific schema information for each table from the database; forming a local hint based on the specific schema information and the global description set; inputting the local hint into a large language model for information expansion to generate a column description set described in natural language; the specific schema information includes table names, column names, column data types, column nullability, primary key constraints, relevant foreign key constraints, and column example values; the column description set is a natural language description of the column information in each table of the database; obtaining user input questions; and combining the global description set and the column description set to generate a query matching the user input. A semantic index related to the question is obtained through the following methods: combining a global description set, a column description set, and few-sample hints, a long contextual retrieval set related to the user input question is obtained from the database through a large language model; a keyword set is generated based on the user input question and related business knowledge; the corresponding semantic retrieval set is obtained according to the keyword set and the column description set; the long contextual retrieval set and the semantic retrieval set are merged to obtain the semantic index; the semantic index, the global description set, and the column description set are combined to guide the large language model to generate output SQL; the process of guiding the large language model to generate output SQL specifically includes the following steps: logically understanding the user input question based on the global description set, and extracting query information and condition information; reasoning based on the query information, and determining valid query fields and filtering conditions based on the column description set; filtering and collecting question-related information related to the user input question in the database according to the query fields and the filtering conditions, merging the question-related information and inputting it into the large language model to generate initial SQL; inputting the initial SQL into the large language model, and generating output SQL after secondary generation.

[0009] Preferably, the global description set includes database description, table description, and foreign key relationship description; the global description set also includes the identification and interpretation of data naming conventions in the database.

[0010] Preferably, the process of expanding the local prompts into a large language model to generate a column description set further includes the following steps: identifying whether each table contains columns using enumeration types; if so, analyzing the business meaning of the enumeration type values ​​and generating a business meaning mapping for each enumeration type corresponding to its values; identifying whether each table contains encoding or ID information; if so, inferring its encoding rules and business meaning, and generating a business meaning mapping for each encoding and ID information.

[0011] Preferably, the large language model input information further includes the global description set and the column description set.

[0012] Preferably, the keyword set includes semantic keywords obtained by inputting the user input question into a large language model, and slice keywords obtained by directly slicing the user input question.

[0013] Preferably, the process of retrieving columns related to the user input question from the database based on the keyword set and the column description set, and then merging them to obtain a semantic retrieval set, specifically includes the following steps: encoding the database to obtain a value vector library; retrieving and obtaining location values ​​in the value vector library based on the keyword set and the column description set respectively; determining the corresponding columns in the database based on the location values; merging all columns corresponding to the location values ​​to obtain the semantic retrieval set.

[0014] Preferably, the value vector library uses the Embedding semantic retrieval algorithm to calculate similarity and retrieve the location values.

[0015] Preferably, after obtaining the output SQL through secondary generation, the process further includes the following steps: using a sampling-based model self-improvement technique to perform multiple rounds of optimization on the output SQL; generating multiple candidate output SQLs by changing the sampling path, and voting to select the optimal result.

[0016] To solve the above-mentioned technical problems, the present invention also provides a computer-readable storage medium having a computer program stored thereon, wherein when the computer program is executed by a processor, it implements the SQL generation method based on a large language model as described in any of the preceding claims.

[0017] Compared with existing technologies, the SQL generation method and storage medium based on a large language model of the present invention have the following advantages:

[0018] 1. The SQL generation method based on a large language model of the present invention includes the following steps: extracting global and individual table schema information from the database, processing them respectively to generate a global description set and a column description set in natural language; acquiring user input questions and related business knowledge, generating a semantic index corresponding to the user input questions; and guiding the large language model to generate output SQL. Through the global description set and column description set, the large language model expands the database with a large amount of descriptive information at both the global and local levels. This facilitates the model's ability to quickly understand the meaning of the corresponding information in the database when retrieving information from the database later, based on the pre-expanded description set. This improves the system's ability to understand the database content, thereby improving the response speed and accuracy of subsequent SQL generation.

[0019] 2. The global description set of this invention includes database description, table description, and foreign key relationship description, as well as the identification and interpretation of data naming conventions in the database. By identifying and understanding the rules and meanings of data naming, the system's understanding of the database theme is further deepened, facilitating the alignment of the semantics of various data and preventing misinterpretations of data information due to misunderstandings of business information or different naming abbreviations. This greatly improves the accuracy of the system's understanding of information in the database.

[0020] 3. In this invention, it is possible to identify whether each table contains columns using enumeration types or encoded ID information, and to analyze and infer the business meaning mapping corresponding to the enumeration types and encoded ID information. This helps to improve the ability of the large language model to understand user-defined data and further infer its corresponding usage scenarios, thus avoiding the situation where the system generates incorrect SQL due to misunderstanding of the application scenarios and user-defined data.

[0021] 4. In this invention, few-shot learning is performed by combining the global description set and the column description set to obtain a long contextual retrieval set related to the user input question in the database; a keyword set is generated based on the user input question and relevant business knowledge; the corresponding semantic retrieval set is obtained according to the keyword set and the column description set; the long contextual retrieval set and the semantic retrieval set are merged to obtain a semantic index. Through the above two different retrieval paths, the system performs retrieval under two paths: traversing the entire database and locating a local part of the database after semantic matching of column information. While ensuring the completeness of the retrieval results, it is beneficial for the system to quickly match information with higher relevance, greatly improving the generation efficiency and accuracy of the semantic index.

[0022] 5. The keyword set of this invention includes semantic keywords obtained by inputting user input questions into a large language model, and slice keywords obtained by directly slicing user input questions. On the one hand, slice keywords expand the recall scope, enlarge the candidate set, and avoid missing relevant data in the search database; on the other hand, semantic keywords narrow the search scope and improve the accuracy of the search.

[0023] 6. In this invention, the database is encoded to obtain a value vector library. Retrieval is performed in the value vector library based on both the keyword set and the column description set to obtain the location values. All location values ​​are then merged into their corresponding columns in the database to obtain a semantic retrieval set. By establishing the value vector library, semantic similarity is converted into vector proximity, improving the system's ability to understand and compute data within the database, thereby enhancing the accuracy of subsequent SQL generation.

[0024] 7. This invention, based on a global description set, logically understands user input questions, extracting query and condition information; combines a column description set to determine valid query fields and filtering conditions; filters and collects information related to user input questions from the database, merges it, and inputs it into a large language model to obtain initial SQL; then, the initial SQL is used to generate output SQL. This four-stage reasoning process helps the large language model understand user input questions at both the overall and local database levels, improving its ability to understand complex problems and implicit requirements in context, thereby increasing the accuracy of subsequent SQL generation by the large language model.

[0025] 8. In this invention, a model self-improvement technique is concurrently employed to perform multiple rounds of optimization on the output SQL and to change the sampling path to obtain multiple candidate output SQLs, which are then voted to select the optimal result. Through the SQL optimization mechanism of self-improvement and multi-sampling, the generation of errors or noise is further reduced, and the reliability of the final output SQL is improved.

[0026] 9. The present invention also provides a computer-readable storage medium that has the same beneficial effects as the SQL generation method based on the large language model described above, which will not be elaborated here. [Attached Image Description]

[0028] To more clearly illustrate the technical solutions in the embodiments of the present invention, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0029] Figure 1 This is a flowchart of the SQL generation method based on a large language model provided in the first embodiment of the present invention.

[0030] Figure 2 This is a flowchart of step S2 of the SQL generation method based on a large language model provided in the first embodiment of the present invention.

[0031] Figure 3 This is a flowchart of step S3 of the SQL generation method based on a large language model provided in the first embodiment of the present invention.

[0032] Figure 4 This is a flowchart of step S32 of the SQL generation method based on a large language model provided in the first embodiment of the present invention.

[0033] Figure 5 This is a flowchart of step S4 of the SQL generation method based on a large language model provided in the first embodiment of the present invention.

[0034] Figure 6 This is an example diagram of step S1 of the SQL generation method based on a large language model provided in the first embodiment of the present invention.

[0035] Figure 7 This is an example diagram of step S2 of the SQL generation method based on a large language model provided in the first embodiment of the present invention.

[0036] Figure 8 This is an example diagram of step S3 of the SQL generation method based on a large language model provided in the first embodiment of the present invention.

[0037] Figure 9 This is an example diagram of step S32 of the SQL generation method based on a large language model provided in the first embodiment of the present invention.

[0038] Figure 10 This is an example diagram of step S4 of the SQL generation method based on a large language model provided in the first embodiment of the present invention.

Detailed Implementation Methods

[0040] To make the objectives, technical solutions, and advantages of this invention clearer, the invention will be further described in detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the invention.

[0041] Please see Figure 1 and Figure 6 The first embodiment of the present invention provides a SQL generation method based on a large language model, comprising the following steps:

[0042] S1: Extract global schema information and foreign key constraints from the database, form global hints based on global schema information and foreign key constraints, input the global hints into the large language model for information expansion, and generate a global description set described by natural language.

[0043] Understandably, the global description set is a natural language description of the database at the global level, which allows the system to align data between tables and the relationship between semantics and data without having to perform multiple semantic inferences. This greatly reduces the possible semantic errors and improves the system's ability to locate data information in the database.

[0044] As an optional implementation, the schema information extracted from the database by the system is in DDL format. DDL (Data Definition Language) is a language used to define and modify the database structure. It has the characteristic of clearly declaring the database structure, data information, constraints, and comments at one time, which is beneficial for the system to understand the schema information and subsequently execute corresponding instructions.

[0045] Understandably, global schema information includes table names, column names, column data types, whether columns can be nullable, and primary key constraints in the database. Foreign key constraints are inter-table key constraints.

[0046] As an optional implementation, the system can further incorporate comments from the database or user input to help generate global prompts.

[0047] Understandably, based on the above information, the system can, on the one hand, directly output relatively accurate prompts even with zero samples; on the other hand, it facilitates the automatic development of prompt guidance models, eliminating the need for manual input of prompt templates into the system to generate global prompts.

[0048] As an optional implementation, the system incorporates a small number of samples during the formation of global prompts, performs few-sample learning, and generates corresponding Markdown format prompts. It further fine-tunes the results generated by the prompt guidance model by comparing with the samples, which greatly reduces the probability of semantic drift or ambiguity in global prompts, thereby improving the accuracy of the global description set generated by the large language model based on global prompts.

[0049] As an optional implementation, the system generates global prompts in markdown format; markdown is a lightweight markup language with concise syntax, which saves resources and is easy for the system to parse quickly, thus improving the efficiency of the system in generating global description sets.

[0050] Understandably, the global description set, based on global schema information, expands the overall information of the database with abstract descriptive information, making it easier for the system to have a basic understanding of the database subject and overall content, reducing the overall complexity of the database information and the cost of subsequent database content retrieval, thereby improving the efficiency of the system in obtaining specified content from the database.

[0051] S2: Extract specific schema information for each table from the database, form local hints based on the specific schema information and the global description set, and input the local hints into the large language model for information expansion to generate a column description set described by natural language.

[0052] As an optional implementation, the specific schema information is schema information in DDL format, including table name, column name, column data type, whether the column can be nullable, primary key constraint, related foreign key constraint, and column example value; the system inputs a small number of samples for few-sample learning and uses markdown format to organize and generate local prompts.

[0053] Understandably, a column description set is a natural language description of the column information in each table of a database. More specifically, while ensuring that its length does not exceed a certain limit, a column description set includes the data type of the corresponding column, possible user-written table columns and their actual business descriptions, related primary and foreign key constraints, and sample display values ​​after format optimization.

[0054] Understandably, inputting a global description set helps the system control the overall semantic environment of the database, accurately understand the meaning of each column of local data in the database, and avoid the system outputting local prompts that may cause the large language model to have an AI illusion due to semantic ambiguity or other reasons when analyzing column information.

[0055] Understandably, the column description set extracts the feature information of specific columns based on specific schema information, which makes it easier for the system to quickly locate specific columns with specified information content when calling the database later, greatly improving the efficiency of the system in calling database information.

[0056] For example, the "Transaction Information Table" in the database contains a field "txn_amt". The large language model, based on the global description set and specific column value content pattern information, generates a description for the corresponding field "txn_amt": "Full name: transaction_amount, refers to the transaction amount. It is usually expressed in the local currency unit and records positive values ​​(such as income, deposits) or negative values ​​(such as expenses, withdrawals, fees). This column is generally used to analyze user spending behavior, cash flow, and business performance."

[0057] S3: Obtain the user input question, combine the global description set and the column description set, and generate a semantic index corresponding to the user input question.

[0058] As an optional implementation, the system may selectively acquire business knowledge related to the user input question to assist in understanding the overall business information and business environment of the database.

[0059] S4: Combines semantic indexes, global description sets, and column description sets to guide large language models in generating output SQL.

[0060] Understandably, the global description set and column description set greatly enrich the information in the database at both the global and local levels, providing a wealth of descriptive information for the model to subsequently understand the database content. This improves the system's ability to understand the database content and, consequently, enhances the system's response speed and accuracy when generating SQL.

[0061] Furthermore, the global description set includes database descriptions, table descriptions, and foreign key relationship descriptions; the global description set also includes the identification and interpretation of data naming conventions in the database.

[0062] The database description (DB description) explains the business positioning, core entities, and main content of the entire database; the table description explains the business meaning, primary key constraints, granularity, and lifecycle of a single table; the foreign key relationship description explains what relationships exist between two tables, how they are related, and the business semantics of the related fields.

[0063] Understandably, database descriptions help the system understand the overall business direction of the database; table descriptions organize the specific information in the database and provide corresponding coordinates, which facilitates the system's subsequent data retrieval in the database; foreign key relationship descriptions represent the association indexes between two different tables, which is beneficial for the system to query the data relationships between tables.

[0064] To facilitate the elimination of potential ambiguities in internal data when a database is transferred to different teams, databases typically have certain naming conventions, and internal data is mostly named according to these conventions.

[0065] Understandably, by recognizing and understanding the database's naming conventions, the system further deepens its understanding of the database's subject matter and can perform semantic alignment on various data. This avoids misjudging data information due to misunderstandings of business information or misinterpretations of naming abbreviations, thereby improving the system's accuracy in understanding information in the database and consequently enhancing the authenticity and accuracy of the generated SQL.

[0066] For example, the database "User-Product Management Status Table" contains a field "usr_name". When the large language model recognizes and interprets "usr_name", it finds that the prefix "usr_" in the database generally represents "user-related tables or fields", and thus identifies the meaning of the field "usr_name" as "username". When a user requests "find the names of all registered users", the large language model can accurately locate this column by recognizing the "usr_" prefix, without being confused with other fields in the database that end with "name" (such as the "prod_name" field, which means storing product names).

[0067] For example, the database's "Transaction Information Table" contains a field "txn_amt". When the large language model recognizes and interprets "txn_amt", it determines, based on the table name, the column values, and the large language model's own knowledge, that the full name corresponding to the abbreviation field "txn_amt" is "transaction_amount", thus analyzing the Chinese name of the field as "transaction amount". When a user requests to "find the transaction amount", the large language model locates the "txn_amt" column in the database.

[0068] Understandably, recognizing and understanding naming conventions also helps the system to promptly correct potential naming errors in the database based on specific data and business environment, preventing incorrect data naming from affecting the accuracy of subsequent database retrieval.

[0069] Please see Figure 1 , Figure 2 and Figure 7 Furthermore, step S2 specifically includes the following steps:

[0070] S21: Identify whether each table contains columns using enumeration types. If so, analyze the business meaning of the enumeration type values ​​and generate a mapping of the business meaning of each enumeration type corresponding to its values.

[0071] Enumeration types are a type of user-defined data type that assigns meaningful names to specific constants, improving the readability of specific data and reducing the system's understanding cost of the database.

[0072] Understandably, when generating column description sets, the large language model identifies the data units and precision information of columns with special formats and simultaneously analyzes the implicit dependencies between different columns. Furthermore, as an optional implementation, the large language model identifies the units and precision information of special format columns such as timestamps, amounts, and percentages in the database and analyzes the state transitions and calculation derivation relationships between columns.

[0073] S22: Identify whether each table contains encoding or ID information. If so, infer its encoding rules and business meaning, and generate a business meaning mapping corresponding to each encoding and ID information.

[0074] Enumerated types and encoded ID information are typically used in databases as indexes containing business information and referenced through constraints such as foreign keys, thereby enabling fast retrieval of corresponding content.

[0075] Understandably, analyzing the business meanings corresponding to enumeration types and encoded ID information and establishing corresponding business meaning mappings is equivalent to providing a precise semantic alignment between the business meanings and user-defined data for the large language model. On the one hand, this helps improve the large language model's ability to understand custom data and its reasoning ability to infer its usage scenarios; on the other hand, it eliminates the AI ​​illusion caused by the large language model's incorrect semantic understanding of enumeration types and encoded ID information, and reduces the probability of the system generating erroneous SQL.

[0076] Please see Figure 3 and Figure 8 Furthermore, step S3 specifically includes the following steps:

[0077] S31: Input the user input question into the large language model to generate a keyword set; and combine the global description set and the column description set to retrieve columns in the database related to the user input question, and merge them to obtain a long contextual retrieval set.

[0078] As an optional implementation, the large language model further incorporates relevant business knowledge to extract keywords and adds them to the keyword set, thereby further improving the completeness of the search scope covered by the keywords.

[0079] Understandably, a long contextual retrieval set is a set of results obtained after a global search of a database. By retaining as much contextual information as possible from the database, it improves the accuracy and completeness of the retrieval results.

[0080] As an optional implementation, the system inputs the complete database schema along with the global description set and column description set into the large language model, organizes it using DDL format to form corresponding prompts, and then performs long contextual retrieval by combining the user's input question, possible relevant business knowledge, and a small number of samples.

[0081] Understandably, the global description set provides a supplement to the relationships between database tables, which helps large language models determine whether cross-table queries are needed when performing long-context retrieval.

[0082] Understandably, the column description set provides a business description of local information in the database, which makes it easier for the large language model to understand the user intent contained in the user input question more accurately, and greatly improves the accuracy of the search results in matching user intent.

[0083] S32: Based on the keyword set and the column description set, retrieve columns related to the user input question from the database, and merge them to obtain a semantic retrieval set.

[0084] Understandably, a semantic retrieval set is a collection of results obtained by performing targeted searches within a database based on keyword indexes. It features fast generation speed and high scalability, improving retrieval efficiency and real-time results when searching large databases.

[0085] S33: Merge the long contextual retrieval set and the semantic retrieval set to obtain the semantic index.

[0086] As an optional implementation, after merging the long context retrieval set and the semantic retrieval set, the system further adds a global description set and a column description set to finally obtain a semantic index.

[0087] Understandably, by using the two different retrieval paths described above, the system ensures the accuracy and completeness of the retrieval results as comprehensively as possible while increasing the rate at which retrieval results are returned. This facilitates the system in quickly matching information that is more relevant to the user's input question, and greatly improves the efficiency and accuracy of semantic index generation.

[0088] Understandably, long contextual retrieval and semantic retrieval dynamically identify key entities and key values ​​in the user's input question, realizing the concretization and entityification of fuzzy natural language, which facilitates the accurate location of the specific information in the database corresponding to the query target.

[0089] Furthermore, the keyword set includes semantic keywords obtained by inputting user input questions into a large language model, and slice keywords obtained by directly slicing user input questions.

[0090] Direct slicing is an information extraction method that typically segments target content based on fixed rules or locations. Understandably, slicing keywords quickly obtains literal entity information contained in the user's input question, and has a large recall range, reducing the risk of missed detections when generating semantic retrieval sets.

[0091] As an optional implementation, the large language model uses a sliding window approach, starting from the first character of the user's input question, and segments every N characters into a keyword; more specifically, here N=5, that is, every 5 characters of the user's input question are segmented into a keyword.

[0092] Understandably, semantic keywords are highly relevant indexes compiled by the large language model after understanding the business meaning and business environment information in the input information. This narrows the search scope when generating semantic retrieval sets, thereby improving the accuracy of the search. In addition, semantic keywords also help the system disambiguate the search results and improve the accuracy of generating semantic retrieval sets.

[0093] For example, a user input question is "Query the total order amount for each customer in the first quarter of 2023". The large language model first analyzes the semantics of the user input question, obtaining semantic keywords: [customer, order, amount, first quarter of 2023]; then, based on the direct slicing rule of slicing every 5 characters, it extracts entity information from the user input question, obtaining slicing keywords: [query 202, query 2023, 2023 year, 023 year, 23 year first, 3 year first quarter, year first quarter, first quarter each, quarter each, quarter each customer, degree each customer, each customer, each customer's, customer's order, customer's order total, order total amount, order total amount]. After merging the semantic keywords and slicing keywords, a keyword set is obtained.

[0094] As an optional implementation, semantic keywords also include the results generated after relevant business knowledge is input into the large language model, and slice keywords also include the results obtained by directly slicing relevant business knowledge.

[0095] Please see Figure 3 , Figure 4 , Figure 8 and Figure 9 Furthermore, step S32 specifically includes the following steps:

[0096] S321: Encode the database to obtain a value vector library.

[0097] Numerical vectors are arrays of real numbers obtained by mapping the original data to a fixed dimension. They convert abstract semantic similarity into concrete vector similarity, which helps the system to improve its ability to understand and calculate data information in the database at the same time.

[0098] As an optional implementation, the system combines the column name of each column in the database with the corresponding column description set, and then encodes it to obtain a value vector library, which further improves the ease of locating information in the database.

[0099] S322: Search the value vector library based on the keyword set and the column description set respectively to obtain the location value.

[0100] Understandably, the column description set provides the value vector library with natural language descriptions and enumerated value meanings of the corresponding columns in the database, thus improving the accuracy of semantic matching.

[0101] As an optional implementation, the system sets a similarity threshold; when the system searches in the value vector library, it recalls based on the keyword set and obtains the top five results that exceed the similarity threshold, thereby improving the return efficiency of the location value and the keyword relevance.

[0102] S323: Determine the corresponding column in the database based on the location value, merge all columns corresponding to the location values, and obtain the semantic retrieval set.

[0103] As an optional implementation, the system further supplements all returned columns with relevant primary key constraints and foreign key constraints to obtain a semantic retrieval set composed of DDL format pattern information related to the user input question.

[0104] Understandably, the value vector library enables computability between data within the database and user-input questions, improving the system's semantic understanding of user-input questions, which in turn helps improve the accuracy of subsequent SQL generation.

[0105] Understandably, the establishment of the value vector library also helps to transform the original database, which is complex and consumes a lot of memory, into a low-dimensional library that consumes less memory, thereby reducing the resource costs required for the system to perform searches within it and greatly improving search efficiency.

[0106] Furthermore, the value vector library uses the Embedding semantic retrieval algorithm to calculate similarity and retrieve the location values.

[0107] Embedding semantic retrieval is a recall algorithm that performs nearest neighbor search based on vectors converted from text. It has the advantages of semantic generalization and strong retrieval ability for short search targets. Understandably, after the keyword set and column description set are processed by the Embedding semantic retrieval algorithm, they respectively obtain column description embedding and keyword embedding.

[0108] As an optional implementation, the large language model merges the column description embedding and the keyword embedding, and uses the BM25 algorithm and the cosine similarity algorithm to perform secondary recall on the merged set, further improving the completeness and accuracy of the final semantic retrieval set.

[0109] Please see Figure 1 , Figure 5 and Figure 10 Furthermore, step S4 specifically includes the following steps:

[0110] S41: Based on the global description set, perform logical understanding of the user input question and extract query information and condition information.

[0111] Understandably, the global description set incorporates business context information from the database, thereby improving the large language model's ability to understand the business environment of user input questions.

[0112] S42: Based on the query information, reason and combine the column description set to determine the valid query fields and filtering conditions.

[0113] S43: Based on the above query fields and filtering conditions, filter and collect information related to the user input question in the database, merge the above question-related information and input it into the large language model to generate the initial SQL.

[0114] S44: Input the initial SQL into the large language model, and obtain the output SQL after secondary generation.

[0115] When users input long or complex questions, the generated SQL will also increase significantly in length and complexity, and large language models are more likely to produce errors and noise when generating long and difficult SQL.

[0116] Understandably, the step of generating SQL a second time helps the large language model to further verify whether there are logical errors or semantic understanding errors in the initial SQL and to make corresponding corrections.

[0117] Understandably, through a four-stage thought chain reasoning, the large language model understands the meaning of the user's input question at both the global and local levels, and accordingly generates query fields and filtering conditions that are more conducive to the database to filter the corresponding data information. This allows the large language model to filter information in the database and obtain SQL, greatly improving its ability to understand complex questions and implicit requirements in context, and thus improving the accuracy of the SQL generated by the large language model.

[0118] Furthermore, step S44 is followed by the following steps:

[0119] S45: The output SQL is optimized in multiple rounds using a sampling-based model self-improvement technique.

[0120] S46: By changing the sampling path, generate multiple candidate output SQLs and vote to select the optimal result.

[0121] As an optional implementation, the large language model executes the above two steps concurrently, selects the optimal output SQL, and then outputs it.

[0122] Understandably, self-improvement techniques check the correctness and readability of the output SQL, and multi-sampling voting reduces the occasional errors that may occur during SQL generation. The combination of these two optimization mechanisms further reduces the generation of errors or noise, and improves the reliability of the final output SQL.

[0123] A second embodiment of the present invention provides a computer-readable storage medium storing a computer program thereon. When executed by a processor, the computer program implements the SQL generation method based on a large language model as described in any of the first embodiments. It can have the same beneficial effects as the SQL generation method based on a large language model in the first embodiment, and will not be elaborated further here.

[0124] In the embodiments provided by this invention, it should be understood that "B corresponding to A" means that B is associated with A, and B can be determined based on A. However, it should also be understood that determining B based on A does not mean determining B solely based on A; B can also be determined based on A and / or other information.

[0125] It should be understood that the phrase "one embodiment" or "an embodiment" throughout the specification means that a specific feature, structure, or characteristic related to the embodiment is included in at least one embodiment of the invention. Therefore, "in one embodiment" or "in an embodiment" appearing throughout the specification does not necessarily refer to the same embodiment. Furthermore, these specific features, structures, or characteristics can be combined in any suitable manner in one or more embodiments. Those skilled in the art should also recognize that the embodiments described in the specification are optional embodiments, and the actions and modules involved are not necessarily essential to the invention.

[0126] In various embodiments of the present invention, it should be understood that the sequence number of each process does not necessarily imply the order of execution. The execution order of each process should be determined by its function and internal logic, and should not constitute any limitation on the implementation process of the embodiments of the present invention.

[0127] The flowcharts and block diagrams in the accompanying drawings illustrate the architecture, functionality, and operation of possible implementations of systems, methods, and computer program products according to various embodiments of this application. In this regard, each block in a flowchart or block diagram may represent a module, segment, or portion of code containing one or more executable instructions for implementing a specified logical function. It should also be noted that in some alternative implementations, the functions indicated in the blocks may occur in a different order than those indicated in the drawings. For example, two consecutively indicated blocks may actually be executed substantially in parallel, and they may sometimes be executed in reverse order, depending on the functions involved. It is particularly important to note that each block in the block diagrams and / or flowcharts, and combinations of blocks in the block diagrams and / or flowcharts, can be implemented using a dedicated hardware-based system that performs the specified function or operation, or using a combination of dedicated hardware and computer instructions.

[0128] The above provides a detailed description of the intelligent agent task processing method and storage medium disclosed in the embodiments of the present invention. Specific examples have been used to illustrate the principles and implementation methods of the present invention. The descriptions of the above embodiments are only for the purpose of helping to understand the method and core ideas of the present invention. At the same time, for those skilled in the art, there will be changes in the specific implementation methods and application scope based on the ideas of the present invention. Therefore, the content of this specification should not be construed as a limitation of the present invention. Any modifications, equivalent substitutions, and improvements made within the principles of the present invention should be included within the protection scope of the present invention.

Claims

1. A SQL generation method based on a large language model, characterized in that, Includes the following steps: Global schema information and foreign key constraints are extracted from the database. Global hints are generated based on the global schema information and foreign key constraints. The global hints are then input into a large language model for information expansion to generate a global description set described in natural language. The global schema information includes the table name, column name, column data type, whether the column can be null, and primary key constraints in the database. The global description set is a natural language description of the database at the global level. The database extracts specific schema information for each table. Based on this schema information and the global description set, local hints are formed. These local hints are then input into a large language model for information expansion to generate a column description set described in natural language. The specific schema information includes table name, column name, column data type, whether a column can be nullable, primary key constraints, related foreign key constraints, and column example values. The column description set is a natural language description of the column information in each table of the database. The user input question is obtained, and a semantic index related to the user input question is generated by combining the global description set and the column description set. The semantic index is specifically obtained by the following method: combining the global description set, the column description set and few-sample prompts, and obtaining a long contextual retrieval set related to the user input question in the database through a large language model. Based on user input questions and relevant business knowledge, a keyword set is generated; the corresponding semantic search set is obtained based on the keyword set and the column description set. By merging the long contextual retrieval set and the semantic retrieval set, a semantic index is obtained; By combining the semantic index, the global description set, and the column description set, the large language model is guided to generate output SQL. The process of guiding the large language model to generate output SQL specifically includes the following steps: based on the global description set, logically understand the user input question and extract query information and condition information; perform reasoning based on the query information and determine valid query fields and filtering conditions in conjunction with the column description set; based on the query fields and the filtering conditions, filter and collect question-related information in the database that is related to the user input question, merge the question-related information and input it into the large language model to generate initial SQL; input the initial SQL into the large language model and generate the output SQL a second time.

2. The SQL generation method based on a large language model as described in claim 1, characterized in that: The global description set includes database descriptions, table descriptions, and foreign key relationship descriptions; the global description set also includes the identification and interpretation of data naming conventions in the database.

3. The SQL generation method based on a large language model as described in claim 1, characterized in that: The process of inputting the local prompts into a large language model for information expansion to generate a column description set further includes the following steps: Identify whether each table contains columns that use enumeration types. If so, analyze the business meaning of the values ​​of the enumeration types and generate a mapping of the business meaning of each value for each enumeration type. Identify whether each table contains encoding or ID information. If so, infer its encoding rules and business meaning, and generate a business meaning mapping corresponding to each encoding and ID information.

4. The SQL generation method based on a large language model as described in claim 1, characterized in that: The input information of the large language model also includes the global description set and the column description set.

5. The SQL generation method based on a large language model as described in claim 1, characterized in that: The keyword set includes semantic keywords obtained by inputting the user input question into a large language model, and slice keywords obtained by directly slicing the user input question.

6. The SQL generation method based on a large language model as described in claim 1, characterized in that: Based on the keyword set and the column description set, the process of retrieving columns related to the user input question from the database and merging them to obtain a semantic retrieval set specifically includes the following steps: Encode the database to obtain a value vector library; The location value is obtained by searching the value vector library based on the keyword set and the column description set respectively; The corresponding column in the database is determined based on the location value, and all columns corresponding to the location values ​​are merged to obtain the semantic retrieval set.

7. The SQL generation method based on a large language model as described in claim 6, characterized in that: The value vector library uses the Embedding semantic retrieval algorithm to calculate similarity and retrieve location values.

8. The SQL generation method based on a large language model as described in claim 1, characterized in that: After obtaining the output SQL through secondary generation, the following steps are also included: The output SQL is optimized in multiple rounds using a sampling-based model self-improvement technique. By changing the sampling path, multiple candidate output SQLs are generated, and the optimal result is selected by voting.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by the processor, it implements the SQL generation method based on a large language model as described in any one of claims 1-8.

Images