Text-to-structured query language conversion method based on large language model

Abstract

A text-to-structured query language conversion method based on a large language model comprises the steps that database outline information is cut, and tables and columns needed by query of a non-target structured query language SQL are eliminated through correlation scoring and Steiner tree problem solving; screening context samples, predicting a similarity score from a text to a structured query language Text-to-SQL by using a cross encoder language model, and selecting a sample most related to a query problem; and injecting the cut database table and column information and the screened context sample into a cue word template, and generating an SQL query statement by using a large language model. The method can be used for optimizing the automatic conversion process from the natural language to the SQL and improving the conversion accuracy, and is used for providing a quick and convenient database retrieval mechanism for a non-SQL user and providing applications such as a data access mode in a wider form for mechanisms such as big language model retrieval enhancement generation RAG and the like.

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 conversion) technology aims to convert data query requirements (text) described in natural language into structured data language (Structured Query Language, SQL) used for relational database retrieval. It has been widely used in application scenarios such as database intelligent query, automated business reporting, intelligent question and answer, and personalized data analysis in the field of natural language processing, and has important significance. In recent years, the mainstream method in this field is to use large language models (LLM, hereinafter referred to as large models) to fine-tune the model for Text2SQL tasks, and use the model fine-tuning results for natural language semantic understanding and SQL generation. However, when deploying actual Text2SQL applications, due to the complexity of the actual business domain content, large language models usually cannot fully understand the meaning and composition of the data content such as tables and columns of the actual database, and therefore perform poorly in actual production applications. Therefore, it is very important to effectively introduce the problem-related information such as the database table information and the column information in the table used in the actual production application into the generation process of the large model. The database table description information introduced into the generation process of the large model is collectively referred to as the outline information, namely Schema Information, and the process of introducing the outline information is called Schema Linking or outline linking; at the same time, due to the in-context learning (ICL) characteristics of the large language model, adding appropriate SQL generation sample references to the model prompt word part during the model SQL generation process will help to effectively stimulate the SQL generation performance of the model. Therefore, it is also necessary to consider integrating the context learning process into the large model SQL generation process. The context learning process of the large language model is mainly divided into two categories: zero-shot learning and few-shot learning. Zero-shot learning focuses on adding guiding descriptions to the prompt words to guide the output of the model using the instruction-following characteristics of the large language model to enhance the intelligence of model generation and reduce deviations; few-shot learning focuses on providing several exemplary input-output pairs in a specific format in the prompt words for the large language model as a reference in the generation process.As for pattern linking, the difficulty lies in the huge number of database tables involved in real problems. Therefore, if all database tables and their column description information are incorporated into the large model prompt words, the prompt word length will exceed the large model window length (ContextLength) limit and affect the generation. At the same time, the presence of a large amount of database table and column description information irrelevant to the problem in the prompt words will reduce the prompt word signal-to-noise ratio and affect the output quality of the large model. Therefore, it is necessary to screen and streamline the existing database table and column information for each data retrieval problem; as for context learning, due to the limitation of the large model window length, it is impossible to put all the huge number of samples into the prompt words. Therefore, how to select Text-to-SQL examples that are more relevant to the problem and of higher quality (the examples are usually in the form of Text-to-SQL question-answer pairs) will become another key to 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 for understanding the background of the present application, and therefore may include information that does not constitute prior art known to ordinary technicians in the field. Summary of the invention

[0004] The main purpose of the present invention is to overcome the defects existing 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 to structured query language based on a large language model comprises the following steps:

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

[0008] 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;

[0009] Z3: 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.

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

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

[0012] receiving as input a database schema and a natural language query, wherein the database schema includes a set of tables, a set of columns per 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. The classifier is based on the RESDSQL model and is trained using natural language query questions and SQL sample 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 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, 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 with relevance scores below the set threshold, check whether they have foreign key-primary key connections with nodes in the identified connected subgraph; if so, 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 the pruning process, primary and foreign key information that is not relevant to the retained tables and columns is removed.

[0024] Furthermore, step Z2 specifically includes: training a cross-encoder language model for predicting the Text-to-SQL similarity score between a pair of questions. In the prediction stage, the trained model, the current natural language query, the Text-to-SQL sample set and the pruned database outline are applied to each Text-to-SQL sample in the Text-to-SQL sample set to obtain the k samples with the highest scores, which are the k Text-to-SQL samples most relevant to the natural language query.

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

[0026] receiving a Text-to-SQL sample set, the sample set comprising a plurality of question-result SQL sample pairs;

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

[0028] Train a cross-encoder language model, which is used to predict Text-to-SQL similarity scores between question pairs;

[0029] In the training phase, the model input includes the database outline 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 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, wherein when the computer program is executed by a processor, the method for converting text to structured query language based on a large language model is implemented.

[0035] A computer program product comprises a computer program, wherein when the computer program is executed by a processor, the method for converting text to 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 text-to-structured query language conversion method based on a large language model, which 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 form of data access method for large language model retrieval enhanced generation (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, designs a tailoring scheme for the database outline, designs a screening algorithm for context samples, better associates user queries with the domain-specific database table outline, 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 deviation in the generated SQL due to insufficient perception of the domain-specific database data content and composition by the large language model.

[0039] Compared with the prior art, the main advantages of the present invention are as follows: in view of the problem that a large number of database tables will make it difficult to accommodate the length of the large model window, and the traditional database table clipping method will lead to the loss of result database table information, the present invention proposes a database table clipping method based on foreign keys as table semantic connections, thereby removing the parts of the database table information that are not related to natural language queries and enhancing the database table signal-to-noise ratio; in view of the problem that too many context samples will make it difficult to accommodate the length of the large model window, and the traditional context sample screening method is not compatible with the Text-to-SQL feature, a screening algorithm for Text-to-SQL context samples is proposed, thereby selecting Text-to-SQL samples that are more relevant to the problem and of higher quality, thereby improving the stimulation effect of the context learning feature of the large language model while improving the problem that the large language model generates SQL deviations due to insufficient perception of the data content and composition of the domain-specific database, thereby improving the accuracy of SQL generation, and can be used to provide a quick and convenient database retrieval mechanism for non-SQL users, and provide a wider form of data access method 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 It 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 It is a flow chart of a database schema pruning method according to a preferred embodiment of the present invention;

[0043] Figure 3 It 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 and application of the present invention.

[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. is a collection of columns, is a collection of cell values, each table t i By a column c i The set is composed of each column ci By a cell value v i The collection consists of Represents the primary key, Represents a foreign key-primary key pair. Database schema for That is, by 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 a 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, and 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 wording in the prompt word is to avoid the prompt word length reaching the window limit of the large model, and to enhance the outline The proportion of the part related to question x in the prompt word / signal-to-noise ratio needs to be based on the database outline An outline clipping algorithm is proposed to remove the parts of the outline that are not related to the question x; in order to better serve the context learning characteristics of the large model, while reducing the length of the context learning sample in the prompt word, its problem relevance is improved, thereby improving the context learning performance of the large model, and it is necessary to design a screening algorithm for the context sample ε. In response to the above needs, the present invention proposes a large language model Text-to-SQL optimization method that combines outline information clipping and context sample screening, designs a clipping scheme for the database outline, designs a screening algorithm for context samples, 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 large language model generating SQL deviation due to insufficient perception of domain-specific database data content and composition, improving the accuracy of SQL generation, and can be used to provide a quick 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 also 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 the tables and columns that are not required for the target SQL query by relevance scoring and Steiner tree problem solving.

[0049] In a preferred embodiment, step Z1 specifically includes: using a classifier modified by a table column relevance ranking classifier based on RESDSQL, training through natural language query question-SQL sample pairs in a Text-to-SQL scenario, facing a given natural language question x, using the classifier to give a relevance score for each table and each 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 through a strategy of retaining potentially useful tables.

[0050] See also Figure 2In a preferred embodiment, in step Z1, marking relevant tables and columns in the database to tailor the database outline information specifically includes: 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; using a modified table-column relevance ranking classifier, which is based on the RESDSQL model and trained by natural language query questions and SQL sample pairs; in 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 the processing ability of unbalanced data; in the inference phase, the classifier provides a relevance score for each table and column, and marks the tables and columns whose scores exceed a set threshold as being relevant to the query; by extracting the tables and columns in the SQL sample corresponding to the natural language query question, determining whether each table and column in the database schema appears, and using this as the label value of the predicted value. Through the above process, the classifier can identify and mark the tables and columns related to the natural language query question, and provide accurate input for subsequent database outline tailoring and SQL generation.

[0051] See also Figure 2 In a preferred embodiment, in step Z1, the table and column pruning stage specifically includes: converting the database model 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, check whether they have a foreign key-primary key connection with the nodes in the identified connected subgraph; if so, include these tables in the final set of retained tables to improve the recall rate and avoid error propagation problems; for each retained table, retain its columns and their primary keys whose scores exceed the threshold to ensure that each table retains sufficient column information to maintain its semantic integrity; during the pruning process, remove 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 for generating accurate SQL query statements.

[0052] Step Z2: Filter context samples, use the cross-encoder language model to predict the text-to-SQL similarity score, and select the samples 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 stage, the trained model, the current natural language query, the Text-to-SQL sample set and the pruned database outline are applied to each Text-to-SQL sample in the Text-to-SQL sample set to obtain the k samples with the highest scores, which are the k Text-to-SQL samples most relevant to the natural language query.

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

[0055] Step Z3: Combine 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] The embodiment of the present invention proposes a large language model Text-to-SQL optimization method that combines outline information clipping with context sample screening, designs a clipping method for database outlines, designs a screening algorithm for context samples, better associates user queries with domain-specific database table outlines, extracts the most relevant database table information and context samples and injects prompt words, thereby improving the stimulation effect of the context learning characteristics of the large language model while improving the problem of deviation in the generated SQL due to insufficient perception of the domain-specific database data content and composition by the large language model, 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 database outline information clipping and context sample screening methods. 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 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 combining outline information clipping and context learning screening, comprising:

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

[0062] Step Z2: In this step, a sample screening method for the Text-to-SQL task is proposed to compare the question-example pairs of two Text-to-SQL samples and give similarity scores, so as to select the samples most relevant to the current problem in the sample set and solve 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 big model.

[0064] Figure 1 The figure shows the overall flow of Text-to-SQL of a large language model combining 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 FIG. 1 , the present invention proposes a database schema pruning method for eliminating unnecessary tables and columns in the target SQL query process. The 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 the 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 correlation 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 this 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 Whether each table and column in the model appears 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 unbalanced data. In the inference stage, facing a given natural language question x, the classifier will give a relevance score for each table and column in each table to 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 labeled

[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 large language models 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 it is connected to the 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, only the database table and column information related to the target SQL is retained after being processed by the above trimming module. and column Irrelevant primary key and foreign keys After the above steps, the pruned database outline 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 set of sample pairs 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 The 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 samples in E. The previous method is to simplify sim(x,(x1,y1)) to sim(x,x1), that is, only compare the similarity between the sample problem and the current problem, such as cosine similarity. This method only considers the similarity of the problem description, but does not consider the database outline and the SQL structure of the corresponding problem, because slight differences in the expression of the problem may bring about huge changes in the SQL structure; the second method is to generate a preliminary SQLy0 for the current natural language query problem x in advance, and then compare the SQL structure similarity of y0 with each y1 in E and the similarity of x with x1, and add the two as the similarity score of sim(x,(x1,y1)), and then select the most similar sample based on the score ratio. Although this method takes into account the SQL structure and keywords, the initially 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 invention combines the advantages of the two methods. Specifically, a cross-encoder language model is trained. The language model is based on llama-1.3B and is used 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 represents the model output for this example. In the prediction phase, the model input is The output z is the Text-to-SQL similarity score of (x, x1). Finally, the trained model is used to compare 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 the big model is used to generate SQL. Preferably, the big 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, wherein the processor executes a computer program and at least executes 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 ferromagnetic random access memory (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 disk memory or a 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 only schematic. For example, the division of the units is only a logical function division. There may be other division methods in actual implementation, 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 present 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] A person skilled in the art can understand that: all or part of the steps of implementing the above method embodiment can be completed by hardware related to program instructions, and the aforementioned program can be stored in a computer-readable storage medium. When the program is executed, it executes the steps of the above method embodiment; and the aforementioned storage medium includes: mobile storage devices, read-only memories (ROM, Read-Only Memory), random access memories (RAM, Random Access Memory), disks or optical disks, etc. Various media that can store program codes.

[0079] Alternatively, if the above-mentioned integrated unit of the present invention is implemented in the form of a software function 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 can be essentially or partly reflected in the form of a software product that contributes to the prior art. The computer software product is stored in a storage medium and includes several instructions for a computer device (which can be a personal computer, a server, or a 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 contents are further detailed descriptions of the present invention in combination with specific preferred embodiments, and it cannot be determined that the specific implementation of the present invention is limited to these descriptions. For those skilled in the art of the present invention, several equivalent substitutions or obvious variations can be made without departing from the concept of the present invention, and the performance or use is the same, which should be regarded as belonging to the protection scope 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: trimming database schema information, eliminating tables and columns not required for the target SQL query through relevance scoring and Steiner tree problem solving; 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; Z3: 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.

2. The method for converting text to structured query language based on a large language model as claimed in claim 1, characterized in that: Step Z1 specifically includes: using a classifier modified by a table column relevance ranking classifier based on RESDSQL, training through natural language query question-SQL sample pairs in a Text-to-SQL scenario, facing a given natural language question x, using the classifier to give a relevance score for each table and each column in each table with respect to the natural language question x, marking the tables and columns whose scores exceed a threshold; modeling the table column pruning task as a Steiner tree problem, solving it with a burst search algorithm, and obtaining the final pruned database outline by retaining a strategy for potentially useful tables.

3. The method for converting text to structured query language based on a large language model as claimed in claim 1 or 2, characterized in that: In step Z1, relevant tables and columns in the database are marked to tailor database schema information, specifically including: receiving as input a database schema and a natural language query, wherein the database schema includes a set of tables, a set of columns per table, a set of primary keys, and a set of foreign key-primary key pairs; A modified table column relevance ranking classifier is used. The classifier is based on the RESDSQL model and is trained using natural language query questions and SQL sample 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 3, characterized in that: 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, 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 with relevance scores below the set threshold, check whether they have foreign key-primary key connections with nodes in the identified connected subgraph; if so, 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.

5. The method for converting text to structured query language based on a large language model according to any one of claims 1 to 4, characterized in that: 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 stage, the trained model, the current natural language query, the Text-to-SQL sample set and the pruned database outline are applied to each Text-to-SQL sample in the Text-to-SQL sample set to obtain the k samples with the highest scores, which are the k Text-to-SQL samples most relevant to the natural language query.

6. The method for converting text to structured query language based on a large language model according to any one of claims 1 to 5, characterized in that: In step Z2, the context sample screening method specifically includes: receiving a Text-to-SQL sample set, the sample set comprising a plurality of question-result SQL sample pairs; Given the current natural language query question and database outline, find a subset of samples that are most relevant to the current question in the sample set; Train a cross-encoder language model, which is used to predict Text-to-SQL similarity scores between question pairs; In the training phase, the model input includes the database outline 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, k samples with the highest scores are selected, which are most relevant to the current natural language query question.

7. The method for converting text to structured query language based on a large language model according to any one of claims 1 to 6, characterized in that: 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.

8. 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 7 is implemented.

9. 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 7 is implemented.

Images