A text-to-SQL conversion method based on large language model

Abstract

A large language model-based text-to-structured query language conversion method includes: trimming database schema information, eliminating tables and columns not required for SQL queries through relevance scoring and solving Steiner tree problems; screening contextual examples, using a cross-encoder language model to predict text-to-SQL similarity scores, and selecting the examples most relevant to the query; injecting the trimmed database table and column information and the screened contextual examples into a prompt word template, and using the large language model to generate SQL query statements. This invention can be used to optimize the automatic natural language to SQL conversion process, improve its conversion accuracy, provide a fast and convenient database retrieval mechanism for non-SQL users, and provide a wider range of data access methods for large language model retrieval enhancement generation mechanisms such as RAGs.

Description

Technical Field

[0001] The present invention relates to the fields of computer natural language processing and data retrieval, and in particular to a text-to-structured query language (Text-to-SQL, also referred to as Text2SQL) conversion method. Background Art

[0002] Text2SQL (Text to Structured Query Language) technology aims to convert data query requirements (text) described in natural language into the structured query language (SQL) used for relational database retrieval. It has been widely used in natural language processing applications such as intelligent database query, automated business reporting, intelligent question-answering, and personalized data analysis, and is of great significance. In recent years, the mainstream approach in this field has been to fine-tune large language models (LLMs) for Text2SQL tasks and use the results of the fine-tuning for natural language semantic understanding and SQL generation. However, when deploying Text2SQL applications, due to the complexity of actual business domain content, large language models often cannot fully understand the meaning and structure of data content such as tables and columns in the actual database, resulting in poor performance in real-world production applications. Therefore, it is crucial to effectively incorporate relevant information, such as database table information and table column information used in actual production applications, into the large model generation process. This database table description information introduced into the large model generation process is collectively referred to as schema information, or Schema Information, and the process of introducing schema information is called Schema Linking. Furthermore, due to the in-context learning (ICL) characteristics of large language models, adding appropriate SQL generation examples to the model prompts during the model SQL generation process will help effectively stimulate the model's SQL generation performance. Therefore, it is also necessary to consider integrating context learning into the large model SQL generation process. The context learning process of large language models can be divided into two categories: zero-shot learning and few-shot learning. Zero-shot learning focuses on adding guiding descriptions to the prompts, leveraging the instruction-following characteristics of the large language model to guide the model's output, thereby enhancing model generation intelligence and reducing bias. Few-shot learning focuses on providing several exemplary input-output pairs in a specific format in the prompts for the large language model to use as a reference during the generation process.The difficulty with schema linking lies in the sheer number of database tables involved in real-world problems. Therefore, if all database tables and their column descriptions are incorporated into the large model prompts, the prompt length will exceed the large model window length (ContextLength) limit, impacting generation. Furthermore, the presence of a large amount of problem-irrelevant database table and column descriptions in the prompts will reduce the signal-to-noise ratio of the prompts and affect the output quality of the large model. Therefore, it is necessary to filter and streamline the existing database table and column information for each data retrieval problem. Regarding context learning, due to the limitations of the large model window length, it is impossible to include all the large number of examples in the prompts. Therefore, selecting high-quality Text-to-SQL examples (usually in the form of Text-to-SQL question-answer pairs) that are more relevant to the problem becomes another key factor affecting the quality and correctness of SQL generated by the large model.

[0003] It should be noted that the information disclosed in the above background technology section is only used to understand the background of this application, and therefore may include information that does not constitute prior art known to ordinary technicians in this field. Summary of the Invention

[0004] The main purpose of the present invention is to overcome the defects in the above-mentioned background technology and provide a text-to-structured query language conversion method based on a large language model.

[0005] To achieve the above object, the present invention adopts the following technical solutions:

[0006] A method for converting text into structured query language based on a large language model, comprising the following steps:

[0007] Z1: Pruning database schema information, eliminating tables and columns not required for the target SQL query through relevance scoring and Steiner tree problem solving;

[0008] Z2: Filters contextual examples, uses a cross-encoder language model to predict text-to-SQL similarity scores, and selects the examples most relevant to the query;

[0009] Z3: Injects the trimmed database table and column information and the filtered context samples into the prompt word template and uses the large language model to generate SQL query statements.

[0010] Furthermore, step Z1 specifically includes: using a classifier modified from a table column relevance ranking classifier based on RESDSQL, training the classifier using natural language query question-SQL example pairs in a Text-to-SQL scenario; given a natural language question x, using the classifier to give a relevance score for each table and column in each table with respect to the natural language question x, and marking the tables and columns whose scores exceed a threshold; modeling the table column pruning task as a Steiner tree problem, solving it using a burst search algorithm, and obtaining the final pruned database outline by retaining potentially useful tables.

[0011] Furthermore, in step Z1, relevant tables and columns in the database are marked to tailor the database schema information, specifically including:

[0012] receiving a database schema and a natural language query as input, wherein the database schema includes a set of tables, a set of columns for each table, a set of primary keys, and a set of foreign key-primary key pairs;

[0013] A modified table column relevance ranking classifier is used. This classifier is based on the RESDSQL model and is trained using natural language query questions and SQL example pairs.

[0014] During the training phase, the classifier learns to predict the relevance of tables and columns in the database to a given natural language query question, and uses a focal loss function to improve its ability to handle imbalanced data;

[0015] In the inference phase, the classifier provides a relevance score for each table and column, and marks the tables and columns with scores exceeding a set threshold as relevant to the query;

[0016] By extracting the tables and columns in the SQL sample corresponding to the natural language query question, we determine whether each table and column in the database schema appears, and use this as the label value of the predicted value.

[0017] Furthermore, in step Z1, the table column cutting stage specifically includes:

[0018] Convert the database schema into a graph representation, where the node set represents the tables in the database, the edge set represents the relationship between the tables, each edge represents the connection between the foreign key and the primary key, and each edge is given the same weight;

[0019] The table column pruning task is modeled as a Steiner tree problem, where the goal is to extract the minimum connected subgraph containing all the tables to be retained;

[0020] A burst search algorithm is used to solve the Steiner tree problem to identify subsets of tables and columns that are both related and interconnected;

[0021] For tables whose relevance scores are lower than the set threshold, we check whether they have foreign key-primary key connections with nodes in the identified connected subgraph; if so, we include these tables in the final set of retained tables to improve recall and avoid error propagation problems;

[0022] For each retained table, retain the columns whose scores exceed the threshold and their primary keys, ensuring that each table retains enough column information to maintain its semantic integrity;

[0023] During pruning, primary and foreign key information that is not relevant to the remaining tables and columns is removed.

[0024] Furthermore, step Z2 specifically includes: training a cross-encoder language model to predict the Text-to-SQL similarity score between a pair of questions. In the prediction phase, the trained model is applied to each Text-to-SQL example in the Text-to-SQL example set, the current natural language query, the Text-to-SQL example set, and the pruned database schema. The model is then used to obtain the k highest-scoring examples, i.e., the k Text-to-SQL examples most relevant to the natural language query.

[0025] Furthermore, in step Z2, the context sample screening method specifically includes:

[0026] Receive a Text-to-SQL sample set, which includes multiple question-result SQL sample pairs;

[0027] Given the current natural language query question and database schema, find a subset of samples that are most relevant to the current question in the sample set;

[0028] Train a cross-encoder language model that predicts Text-to-SQL similarity scores between question pairs;

[0029] During the training phase, the model input includes a database schema and a pair of questions, and the output is the Text-to-SQL similarity score of the question pair. The label value is a combination of the cosine similarity of the two questions and the structural similarity of the corresponding SQL expressions.

[0030] The loss function uses a function based on the difference between the model output and the label value to optimize the model's ability to predict the similarity of samples;

[0031] In the prediction phase, the trained model and the current natural language query are used to perform a similarity score on each example in the sample set.

[0032] Based on the obtained similarity scores, k samples with the highest scores are selected, which are the most relevant to the current natural language query question.

[0033] Furthermore, in step Z3, a pre-trained large language model such as Qwen-72B that has been fine-tuned for the code generation task is used to generate SQL query statements based on the prompt word template.

[0034] A computer-readable storage medium stores a computer program, which, when executed by a processor, implements the text-to-structured query language conversion method based on a large language model.

[0035] A computer program product includes a computer program. When the computer program is executed by a processor, the method for converting text into structured query language based on a large language model is implemented.

[0036] The present invention has the following beneficial effects:

[0037] The present invention proposes a large language model-based text-to-structured query language conversion method. The method is a large language model text-to-SQL optimization method that combines outline information clipping with context sample screening. It can be used to optimize the automatic conversion process from natural language to structured query language, improve its conversion accuracy, provide a fast and convenient database retrieval mechanism for non-SQL users, and provide a wider range of data access methods for large language model retrieval-augmented generation (RAG) and other mechanisms.

[0038] The present invention proposes a large language model Text-to-SQL optimization method that combines outline information tailoring and context sample screening. It designs a tailoring scheme for database outlines and a screening algorithm for context samples. It better associates user queries with domain-specific database table outlines, extracts corresponding key information and injects prompt words, thereby improving the stimulation effect of the context learning characteristics of the large language model while improving the problem of SQL generation deviation caused by the large language model's insufficient perception of the domain-specific database data content and composition.

[0039] Compared with the existing technology, the main advantages of the present invention are as follows: To address the problem that a large number of database tables makes it difficult to accommodate the large model window length, while traditional database table pruning methods can lead to missing database table information, the present invention proposes a database table pruning method based on foreign keys as table semantic connections, thereby removing portions of database table information that are not relevant to natural language queries and improving the database table signal-to-noise ratio. To address the problem that an excessive number of context samples makes it difficult to accommodate the large model window length, while traditional context sample screening methods are not compatible with Text-to-SQL features, the present invention proposes a screening algorithm for Text-to-SQL context samples, thereby selecting Text-to-SQL samples that are more relevant to the question and of higher quality. This improves the effectiveness of stimulating the context learning features of the large language model while also addressing the problem of biased SQL generation caused by the large language model's insufficient perception of domain-specific database data content and composition. This improves the accuracy of SQL generation and can be used to provide a fast and convenient database retrieval mechanism for non-SQL users and a wider range of data access methods for the large language model Retrieval Enhancement Generation (RAG) mechanism.

[0040] Other beneficial effects of the embodiments of the present invention will be further described below. BRIEF DESCRIPTION OF THE DRAWINGS

[0041] Figure 1 This is an overall flow chart of a Text-to-SQL method for converting text to structured query language based on a large language model according to a preferred embodiment of the present invention;

[0042] Figure 2 This is a flow chart of a database schema pruning method according to a preferred embodiment of the present invention;

[0043] Figure 3 This is a flow chart of a context sample screening method for a Text-to-SQL task according to a preferred embodiment of the present invention. DETAILED DESCRIPTION

[0044] The following is a detailed description of the embodiments of the present invention. It should be emphasized that the following description is only exemplary and is not intended to limit the scope of the present invention and its application.

[0045] The Text-to-SQL problem can be defined as follows: A relational database can be formally represented as in It is a collection of tables in the database. It is a collection of columns. It is a collection of cell values. Each table t i By a column c i The set consists of each column ci By a cell value v i The collection composition of Represents the primary key, Represents a foreign key-primary key pair. Database schema That is, the table List Foreign key relationships Given a natural language query question x described by a natural language text, and a database The goal of the Text-to-SQL problem is to translate question x into an SQL query y and make y available in the database. Execute the query and get the result to answer question x. For the Text-to-SQL problem based on the large model, the goal is to find y' to approximate the optimal y*, where LLM represents the selected large language model, P represents the corresponding prompt word provided to the large model, according to the question q, database and some additional sample information (i.e., ready-made Text-to-SQL question-answer pairs) ε = {(x1, y1), (x2, y2), ..., (x |ε| ,y |ε| )} generates P.

[0046] To simplify the database schema The expression in the prompt word to avoid the prompt word length reaching the window limit of the large model, while enhancing the outline The proportion of the part related to question x in the prompt words / signal-to-noise ratio needs to be based on the database outline A pruning algorithm for the outline is proposed to remove parts of the outline that are irrelevant to question x. In order to better serve the contextual learning characteristics of the large model, it is necessary to design a screening algorithm for contextual examples ε while reducing the length of contextual learning examples in the prompt words, thereby improving the contextual learning performance of the large model. To address the above needs, the present invention proposes a large language model Text-to-SQL optimization method that combines outline information pruning with contextual example screening. A pruning scheme for the database outline and a screening algorithm for contextual examples are designed to better associate user queries with domain-specific database table outlines, extract corresponding key information and inject prompt words, thereby improving the stimulation effect of the contextual learning characteristics of the large language model while improving the problem of SQL generation deviation caused by the large language model's insufficient perception of the domain-specific database data content and composition, improving the accuracy of SQL generation, and can be used to provide a fast and convenient database retrieval mechanism for non-SQL users, and provide a wider form of data access for the large language model retrieval enhancement generation (RAG) mechanism.

[0047] See Figure 1 The embodiment of the present invention provides a method for converting text to structured query language based on a large language model, comprising the following steps:

[0048] Step Z1: Prune the database schema information and eliminate tables and columns not required for the target SQL query by using relevance scoring and Steiner tree problem solving.

[0049] In a preferred embodiment, step Z1 specifically includes: using a classifier modified from a table column relevance ranking classifier based on RESDSQL, training the classifier using natural language query question-SQL example pairs in a Text-to-SQL scenario, and using the classifier to give a relevance score for each table and column in each table with respect to a given natural language question x, and marking the tables and columns whose scores exceed a threshold; modeling the table column pruning task as a Steiner tree problem, solving it using a burst search algorithm, and obtaining the final pruned database outline by retaining potentially useful tables.

[0050] See Figure 2In a preferred embodiment, step Z1 marks relevant tables and columns in the database to tailor the database schema information, specifically including: receiving a database schema and a natural language query as input, where the database schema includes a set of tables, a set of columns for each table, a set of primary keys, and a set of foreign key-primary key pairs; utilizing a modified table-column relevance ranking classifier based on the RESDSQL model, trained using pairs of natural language queries and SQL examples; during the training phase, the classifier learns to predict the relevance of tables and columns in the database to a given natural language query, and uses a focal loss function to improve its ability to handle imbalanced data; during the inference phase, the classifier provides a relevance score for each table and column, and marks tables and columns with scores exceeding a set threshold as relevant to the query; and by extracting tables and columns from SQL examples corresponding to the natural language query, determining whether each table and column in the database schema appears, and using this as a label value for the predicted value. Through the above process, the classifier can identify and mark tables and columns relevant to the natural language query, providing accurate input for subsequent database schema tailoring and SQL generation.

[0051] See Figure 2 In a preferred embodiment, in step Z1, the table and column pruning stage specifically includes: converting the database schema into a graph representation, wherein the node set represents the tables in the database, the edge set represents the relationship between the tables, each edge represents the connection between the foreign key and the primary key, and each edge is assigned the same weight; modeling the table and column pruning task as a Steiner tree problem, the goal is to extract the minimum connected subgraph containing all the tables to be retained; using a burst search algorithm to solve the Steiner tree problem to identify a subset of tables and columns that are both related and interconnected; for tables with a relevance score lower than a set threshold, checking whether they have foreign key-primary key connections with nodes in the identified connected subgraph; if so, including these tables in the final set of retained tables to improve the recall rate and avoid error propagation problems; for each retained table, retaining its columns and primary keys whose scores exceed the threshold, ensuring that each table retains sufficient column information to maintain its semantic integrity; during the pruning process, removing primary and foreign key information that is not related to the retained tables and columns. After the trimming process, a concise database schema related to the target SQL query is obtained, which only contains the table and column information necessary to generate accurate SQL query statements.

[0052] Step Z2: Filter contextual examples, use the cross-encoder language model to predict the text-to-SQL similarity score, and select the examples most relevant to the query question.

[0053] In a preferred embodiment, step Z2 specifically includes: training a cross-encoder language model to predict the Text-to-SQL similarity score between a pair of questions. In the prediction phase, the trained model is applied to each Text-to-SQL example in the Text-to-SQL example set, the current natural language query, the Text-to-SQL example set, and the pruned database schema. The model is then applied to each Text-to-SQL example in the Text-to-SQL example set to obtain the k highest-scoring examples, i.e., the k Text-to-SQL examples most relevant to the natural language query.

[0054] See Figure 3 In a preferred embodiment, in step Z2, the context example screening method specifically includes: receiving a Text-to-SQL example set, the example set comprising multiple question-result SQL example pairs; given a current natural language query question and a database schema, finding a subset of several examples in the example set that are most relevant to the current question; training a cross-encoder language model, the model being used to predict Text-to-SQL similarity scores between question pairs; in a training phase, the model input includes a database schema and a pair of questions, and the output is a Text-to-SQL similarity score for the question pair, with a label value being a combination of the cosine similarity of the two questions and the structural similarity of the corresponding SQL expressions; a loss function employing a function based on the difference between the model output and the label value to optimize the model's ability to predict example similarity; in a prediction phase, using the trained model and the current natural language query question to perform a similarity score on each example in the example set; and selecting, based on the obtained similarity scores, the k examples with the highest scores, which are the examples most relevant to the current natural language query question.

[0055] Step Z3: Combining the results of steps Z1 and Z2, inject the trimmed database table and column information and the filtered context samples into the prompt word template, and use the large language model to generate SQL query statements.

[0056] In a preferred embodiment, in step Z3, a pre-trained large language model fine-tuned for the code generation task, such as Qwen-72B, is used to generate SQL query statements based on the prompt word template.

[0057] An embodiment of the present invention proposes a large language model Text-to-SQL optimization method that combines outline information clipping with context sample screening. It designs a clipping method for database outlines and a screening algorithm for context samples. It better associates user queries with domain-specific database table outlines, extracts the most relevant database table information and context samples, and injects prompt words. This improves the stimulation effect of the context learning characteristics of the large language model while improving the problem of SQL generation deviation caused by the large language model's insufficient perception of the domain-specific database data content and composition, thereby improving the accuracy of SQL generation.

[0058] The present invention significantly improves the performance and accuracy of large language models in Text-to-SQL tasks by combining the methods of database outline information clipping and context sample screening. Through the clipping algorithm, the present invention effectively reduces irrelevant information in the database outline, solves the problem of large model window length limitation caused by the large number of database tables, and at the same time increases the proportion of library table information related to the query problem in the prompt words, thereby improving the signal-to-noise ratio. In addition, the context sample screening algorithm of the present invention can select high-quality samples that are most relevant to the current query problem from a large number of samples, further improving the context learning ability of the large model. The method of the present invention not only optimizes the automatic conversion process from natural language to SQL, but also provides a quick and convenient database retrieval mechanism for non-SQL users, and provides a wider form of data access method for large language model retrieval enhancement generation and other mechanisms, thereby showing higher SQL generation accuracy and better practicality in practical applications.

[0059] The following further describes specific embodiments of the present invention and examples of algorithm implementation thereof.

[0060] A large language model Text-to-SQL optimization method that combines summary information clipping with contextual learning and screening, including:

[0061] Step Z1: In this step, a database schema pruning method is proposed to eliminate tables and columns that are not needed in the target SQL query process.

[0062] Step Z2: This step proposes a sample screening method for the Text-to-SQL task. It compares the question-example pairs of two Text-to-SQL samples and gives a similarity score, thereby selecting the sample most relevant to the current question in the sample set, solving the context sample selection problem.

[0063] Step Z3: In this step, the database involved in the problem is analyzed using the method in Z1. Based on the trimming of tables and columns, and the use of Z2's sample screening method to select several context samples related to natural language questions, the preset prompt word templates are injected into the trimmed tables and columns and context samples, and SQL is generated using the large model.

[0064] Figure 1 The figure shows the overall Text-to-SQL process of a large language model that combines outline information tailoring and context sample screening in a preferred embodiment of the present invention, which specifically includes the following steps:

[0065] Step Z1: Figure 2 As shown in the figure, the present invention proposes a database schema pruning method to eliminate unnecessary tables and columns in the target SQL query process. This method helps to shorten the input length of each example, so that the prompt word can accommodate more examples under the same large model window length limit. In addition, the method simplifies the complexity of the reasoning task by limiting the problem to a subset of the database schema. The input of this method is the database schema. and a natural language query x, where Tables representing the database schema; for each table t i , which is listed by express; Represents the primary key, Represents a foreign key-primary key pair. This method first transforms the table column relevance ranking classifier of RESDSQL to achieve the classification of database tables and columns. For the transformed classifier, its input form is:

[0066]

[0067] The classifier is trained through a large number of natural language query question-SQL sample pairs in Text-to-SQL scenarios. During the training phase, for each The classifier will use the training data for Each t i and c i,j , predict whether the table or column is related to the natural language query question x; at the same time, it is necessary to extract the corresponding table and column from the SQL sample y corresponding to the natural language query question x to identify The presence of each table and column in the prediction is used as the label value corresponding to the predicted value. The focal loss function is used in the training process to improve the model's ability to handle imbalanced data. In the inference phase, given a natural language question x, the classifier will give each table and column in each table a relevance score for the natural language question x, where the score exceeds the threshold τ pThe table is marked as For each table t i , the score exceeds τ p The columns are marked

[0068] After the tables and columns are marked by the classifier, they enter the table and column pruning stage. Unlike traditional pruning strategies, the pruning method of the present invention does not simply select the first k1 tables and the first k2 columns. Traditional strategies may bring two problems: first, it increases the complexity of the pattern by including redundant pattern items; second, due to the limited accuracy of the classifier, the selected tables may lack connectivity. These factors will cause the large language model to require additional processing to distinguish the expanded set of pattern items. In contrast, the present invention hopes to identify a subset of tables and columns that are both related and interconnected. In order to solve the above problems, the present invention models the pattern pruning task as a Steiner Tree Problem. The database model is represented as a graph G = (V, E), where V represents E represents the relationship between tables (foreign key-primary key connection). Each edge in E is assigned a weight of 1. The table that is finally retained can be simplified to the Steiner point set S, so the pruning problem can be reduced to the Steiner tree problem, whose goal is to extract all tables from the graph G. The minimum connected subgraph G'. Since the Steiner tree problem is an NP-Hard problem, the present invention adopts a burst search algorithm to solve it. In order to improve the recall rate and avoid the error propagation problem, the present invention adopts a strategy of retaining potentially useful tables. If the probability of a table is lower than the threshold τ p , but has connections with nodes in G', the table will still be included in G'. All nodes in G' are marked as reserved tables For each table t i , the score exceeds τ p The columns and their primary keys will be retained and recorded as Note τ n To maintain the minimum number of columns for table semantics, after the above trimming module is processed, only the database table and column information related to the target SQL is retained. and column Irrelevant primary key and foreign keys Will be discarded. After the above steps, the database outline after trimming is expressed as

[0069] Step Z2: Figure 3As shown in the figure, the present invention proposes a context sample screening method for Text-to-SQL tasks, which is used to compare the question-example pairs of two Text-to-SQL samples and give similarity scores, so as to select the sample most relevant to the current problem in the sample set and solve the context sample selection problem. Formally speaking, given a Text-to-SQL sample set E = {(x1, y1), (x2, y2), ..., (x |E| ,y |E| )}, that is, E is a sample pair set of question-result SQL, and given the current natural language query question x and database The context example selection problem requires finding the top k A subset E of examples that are most "related" to x sub = The key to this problem is to give a similarity scoring method sim that supports the comparison of sim(x,(x1,y1)) and sim(x,(x2,y2)), where (x1,y1) and (x2,y2) are any two examples in E. Previous methods have simplified sim(x,(x1,y1)) to sim(x,x1), which only compares the similarity between the sample question and the current question, such as cosine similarity. This method only considers the similarity of the question description, but not the database schema and the SQL structure of the corresponding question, because slight differences in the question's wording may lead to significant changes in the SQL structure. The second method is to generate a preliminary SQL y0 for the current natural language query question x in advance, and then compare the SQL structure similarity of y0 with each y1 in E, as well as the similarity between x and x1. The sum of the two is used as the similarity score of sim(x,(x1,y1)), and the most similar sample is selected based on the score ratio. Although this method takes SQL structure and keywords into account, the preliminary generated y0 may introduce inherent cognitive biases or cognitive deficiencies in the model, resulting in a decrease in the accuracy of sample selection in the subsequent process. The method proposed in this paper combines the advantages of the two methods. Specifically, a cross-encoder language model is trained. The language model is based on the llama-1.3B model to predict the Text-to-SQL similarity score between a pair of questions. In the training phase, the model input is The output z is the Text-to-SQL similarity score of (x1, x2), and the label value is lab = sim1(x1, x2) + sim2(y1, y2). Where sim1 is the cosine similarity function for natural language queries x1 and x2, and sim2 is the structural similarity function for SQL expressions y1 and y2. The loss function is Where i represents the i-th training example, z i Indicates the model output for this example. In the prediction phase, the model input is Output z is the Text-to-SQL similarity score of (x, x1). Finally, the trained model is compared with the current natural language query x and the Text-to-SQL sample set E = {(x1, y1), (x2, y2), ..., (x |E| ,y |E| )} and the pruned database schema is represented as Apply this model to each Text-to-SQL example (x1, y1) in E to obtain the k examples with the highest scores, which are the k Text-to-SQL examples most relevant to the natural language query.

[0070] Step Z3: Use the method in Z1 to solve the database involved in the problem The tables and columns are trimmed to obtain a concise database table description And use Z2's sample screening method to select a subset of context samples related to natural language questions On this basis, the preset prompt word templates are injected into the cropped tables and columns and context samples, and SQL is generated using a large model. Preferably, the large model selects a pre-trained model fine-tuned for code generation, such as Qwen-72B.

[0071] An embodiment of the present invention further provides a storage medium for storing a computer program, which at least performs the above method when executed.

[0072] An embodiment of the present invention further provides a control device, comprising a processor and a storage medium for storing a computer program; wherein the processor is configured to execute at least the method described above when executing the computer program.

[0073] An embodiment of the present invention further provides a processor, which executes a computer program and at least performs the method described above.

[0074] The storage medium can be implemented by any type of non-volatile storage device, or a combination thereof. Among them, the non-volatile memory can be a read-only memory (ROM), a programmable read-only memory (PROM), an erasable programmable read-only memory (EPROM), an electrically erasable programmable read-only memory (EEPROM), a magnetic random access memory (FRAM), a flash memory, a magnetic surface memory, an optical disc, or a compact disc read-only memory (CD-ROM); the magnetic surface memory can be a magnetic disk memory or a magnetic tape memory. The storage medium described in the embodiments of the present invention is intended to include, but is not limited to, these and any other suitable types of memory.

[0075] In the several embodiments provided by the present invention, it should be understood that the disclosed systems and methods can be implemented in other ways. The device embodiments described above are merely schematic. For example, the division of the units is merely a logical function division. In actual implementation, there may be other division methods, such as: multiple units or components can be combined, or can be integrated into another system, or some features can be ignored or not executed. In addition, the coupling, direct coupling, or communication connection between the components shown or discussed can be through some interfaces, and the indirect coupling or communication connection of the devices or units can be electrical, mechanical or other forms.

[0076] The units described above as separate components may or may not be physically separated, and the components displayed as units may or may not be physical units, that is, they may be located in one place or distributed on multiple network units; some or all of the units may be selected according to actual needs to achieve the purpose of the solution of this embodiment.

[0077] In addition, all functional units in the embodiments of the present invention may be integrated into one processing unit, or each unit may be separately used as a unit, or two or more units may be integrated into one unit; the above-mentioned integrated units may be implemented in the form of hardware or in the form of hardware plus software functional units.

[0078] Those skilled in the art will appreciate that all or part of the steps of the above-mentioned method embodiments may be implemented by hardware associated with program instructions, and the aforementioned program may be stored in a computer-readable storage medium. When the program is executed, the program executes the steps of the above-mentioned method embodiments. The aforementioned storage medium includes various media that can store program codes, such as mobile storage devices, read-only memories (ROMs), random access memories (RAMs), magnetic disks, or optical disks.

[0079] Alternatively, if the above-mentioned integrated unit of the present invention is implemented in the form of a software functional module and sold or used as an independent product, it can also be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the embodiment of the present invention, or the part that contributes to the prior art, can be embodied in the form of a software product. The computer software product is stored in a storage medium and includes a number of instructions for enabling a computer device (which can be a personal computer, server, or network device, etc.) to execute all or part of the methods described in each embodiment of the present invention. The aforementioned storage medium includes: various media that can store program codes, such as mobile storage devices, ROM, RAM, magnetic disks or optical disks.

[0080] The methods disclosed in the several method embodiments provided by the present invention can be arbitrarily combined without conflict to obtain new method embodiments.

[0081] The features disclosed in several product embodiments provided by the present invention can be arbitrarily combined without conflict to obtain new product embodiments.

[0082] The features disclosed in several method or device embodiments provided by the present invention can be arbitrarily combined without conflict to obtain new method embodiments or device embodiments.

[0083] The above is a further detailed description of the present invention in conjunction with specific preferred embodiments, and the specific implementation of the present invention should not be considered to be limited to these descriptions. Those skilled in the art will recognize that, without departing from the scope of the present invention, several equivalent substitutions or obvious variations can be made, and the performance or use of the same should be considered to fall within the scope of protection of the present invention.

Claims

1. A method for converting text to structured query language based on a large language model, characterized in that: The following steps are involved: Z1: Pruning database schema information, eliminating tables and columns not required for the target SQL query through relevance scoring and Steiner tree problem solving; In step Z1, the table column cutting stage specifically includes: Convert the database schema into a graph representation, where the node set represents the tables in the database, the edge set represents the relationship between the tables, each edge represents the connection between the foreign key and the primary key, and each edge is given the same weight; The table column pruning task is modeled as a Steiner tree problem, where the goal is to extract the minimum connected subgraph containing all the tables to be retained; A burst search algorithm is used to solve the Steiner tree problem to identify subsets of tables and columns that are both related and interconnected; For tables whose relevance scores are lower than the set threshold, we check whether they have foreign key-primary key connections with nodes in the identified connected subgraph; if so, we include these tables in the final set of retained tables to improve recall and avoid error propagation problems; For each retained table, retain the columns whose scores exceed the threshold and their primary keys, ensuring that each table retains enough column information to maintain its semantic integrity; During the pruning process, primary and foreign key information that is not relevant to the retained tables and columns is removed; Z2: Filters contextual examples, uses a cross-encoder language model to predict text-to-SQL similarity scores, and selects the examples most relevant to the query; In step Z2, the context sample screening method specifically includes: Receive a Text-to-SQL sample set, which includes multiple question-result SQL sample pairs; Given the current natural language query question and database schema, find a subset of samples that are most relevant to the current question in the sample set; Train a cross-encoder language model that predicts Text-to-SQL similarity scores between question pairs; During the training phase, the model input includes a database schema and a pair of questions, and the output is the Text-to-SQL similarity score of the question pair. The label value is a combination of the cosine similarity of the two questions and the structural similarity of the corresponding SQL expressions. The loss function uses a function based on the difference between the model output and the label value to optimize the model's ability to predict the similarity of samples; In the prediction phase, the trained model and the current natural language query are used to perform a similarity score on each example in the sample set. Based on the obtained similarity scores, select the k samples with the highest scores, which are most relevant to the current natural language query question; Z3: Injects the trimmed database table and column information and the filtered context samples into the prompt word template and uses the large language model to generate SQL query statements.

2. The method for converting text to structured query language based on a large language model according to claim 1, wherein: Step Z1 specifically includes: using the modified classifier based on RESDSQL table column relevance ranking classifier, training it through natural language query question-SQL sample pairs in the Text-to-SQL scenario, and facing the given natural language question , use the classifier to give a natural language question about each table and column in each table The table and column pruning task is modeled as a Steiner tree problem, which is solved by a burst search algorithm. The final pruned database outline is obtained by retaining potentially useful tables.

3. The method for converting text to structured query language based on a large language model according to claim 1 or 2, wherein: In step Z1, relevant tables and columns in the database are marked to tailor the database schema information, specifically including: receiving a database schema and a natural language query as input, wherein the database schema includes a set of tables, a set of columns for each table, a set of primary keys, and a set of foreign key-primary key pairs; A modified table column relevance ranking classifier is used. This classifier is based on the RESDSQL model and is trained using natural language query questions and SQL example pairs. During the training phase, the classifier learns to predict the relevance of tables and columns in the database to a given natural language query question, and uses a focal loss function to improve its ability to handle imbalanced data; In the inference phase, the classifier provides a relevance score for each table and column, and marks the tables and columns with scores exceeding a set threshold as relevant to the query; By extracting the tables and columns in the SQL sample corresponding to the natural language query question, we determine whether each table and column in the database schema appears, and use this as the label value of the predicted value.

4. The method for converting text to structured query language based on a large language model according to any one of claims 1 to 2, wherein: Step Z2 specifically includes: training a cross-encoder language model to predict the Text-to-SQL similarity score between a pair of questions. In the prediction phase, the trained model is applied to each Text-to-SQL example in the Text-to-SQL example set and the pruned database schema, and the model is applied to obtain the highest-scoring Text-to-SQL example. samples, which are the most relevant to the natural language query Text-to-SQL examples.

5. The method for converting text to structured query language based on a large language model according to any one of claims 1 to 2, wherein: In step Z3, a pre-trained large language model fine-tuned for the code generation task is used to generate SQL query statements based on the prompt word template.

6. A computer-readable storage medium storing a computer program, characterized in that: When the computer program is executed by a processor, the method for converting text to structured query language based on a large language model according to any one of claims 1 to 5 is implemented.

7. A computer program product comprising a computer program, characterized in that When the computer program is executed by a processor, the method for converting text to structured query language based on a large language model according to any one of claims 1 to 5 is implemented.

Images