SQL (Structured Query Language) statement generation method, device and equipment based on large model and storage medium
By obtaining and processing the similarity between database schema information and natural language problems, building prompt words and inputting large language models, the problem of low accuracy in SQL statement generation in the prior art is solved, and high accuracy and universal SQL is achieved Statement generation.
Patent Information
- Application Number
- CN202411218026.3
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-08-30
- Publication Date
- 2024-12-06
AI Technical Summary
The prior art is low in the conversion of natural language text into SQL statements, resulting in errors in generated SQL statements and relies on tedious manual work.
By obtaining the pattern information of each data table in the database, converting it into vector information to build a vector knowledge base, calculating the similarity between natural language problems and vector information, sorting in descending order, deleting the subtraction similarity, building a prompt word and inputting a large language model to generate SQL statement.
It improves the accuracy of SQL statement generation, is suitable for complex query needs, does not require tedious manual work, avoids the risk of data leakage, and has good universality.
Smart Images

Figure CN119088825A_ABST
Abstract
Description
Technical Field
[0001] The present application relates to the field of databases, and in particular to a method, device, equipment and storage medium for generating SQL statements based on a large model. Background Art
[0002] With the development of deep learning technology, the field of natural language processing has made significant progress in language understanding; at the same time, large-scale pre-trained languages have shown strong performance in various natural language processing tasks and can be applied to Text2SQL tasks. The Text2SQL task refers to the process of converting problems or requirements described in natural language into structured query language (SQL) statements.
[0003] In addition, a good database design can improve data storage efficiency and query performance, and ensure that the generated SQL statements can be executed efficiently when performing Text2SQL tasks. Currently, the methods for converting natural language text into structured query language (SQL) include: using a pre-trained language model as a basic model and applying it to the Text2SQL task by fine-tuning. However, due to the complexity of structured query language, the language model has limited understanding of this type of language, resulting in inaccurate fine-tuned models, which in turn generate incorrect SQL statements. Summary of the invention
[0004] The present application provides a method, apparatus, device and storage medium for generating SQL statements based on a large model, so as to solve the technical problem of low accuracy in converting natural language text into SQL statements.
[0005] On the one hand, the present application provides a method for generating SQL statements based on a large model, including: obtaining schema information of each data table in a database, wherein the schema information of the data table is used to describe the structure of the data table; converting the schema information of each data table to obtain first vector information corresponding to each data table, and constructing a vector knowledge base based on the first vector information; converting the natural language question input by the user to obtain second vector information; calculating the similarity between the first vector information and the second vector information corresponding to each data table, and sorting the similarity in descending order; determining the first similarity where the similarity suddenly drops from the sorting, deleting the first similarity and the similarities after the first similarity in the sorting; constructing a prompt word based on the schema information of the data table corresponding to the first vector information corresponding to the similarity retained in the sorting, and the natural language question input by the user; inputting the prompt word into the large language model to obtain an SQL statement output by the large language model.
[0006] In some examples, prompt words are constructed based on the pattern information of the data table corresponding to the first vector information corresponding to the similarity retained in the sorting, and the natural language question input by the user, including: determining the overall structure of the prompt words, the overall structure includes the work tasks of the large model, prompt instructions, pattern information of the data table and answers; defining the beginning of the work tasks of the large model, and the work tasks are to generate SQL statements; parsing the pattern information of the data table corresponding to the first vector information corresponding to the similarity retained in the sorting, and adding the parsed information and the natural language question to the prompt words, and setting prompt instructions and answers to obtain the constructed prompt words.
[0007] In some examples, the first vector information and / or the second vector information is a low-dimensional dense vector.
[0008] In some examples, it is checked whether the main information of the data table, important fields and descriptions of their meanings are clear in the schema information of the data table; if not, the schema information is optimized, and the optimization includes adding database schema information.
[0009] In some examples, a SQL statement is input into a SQL executor to obtain an execution result.
[0010] On the other hand, the present application provides a SQL statement generation device based on a large model, including: an acquisition module, used to acquire mode information of each data table in a database, wherein the mode information of the data table is used to describe the structure of the data table; a conversion module, used to convert the mode information of each data table to obtain first vector information corresponding to each data table, and construct a vector knowledge base based on the first vector information; convert the natural language question input by the user to obtain second vector information; a processing module, used to calculate the similarity between the first vector information and the second vector information corresponding to each data table, and sort the similarity in descending order; determine the first similarity where the similarity suddenly decreases from the sorting, delete the first similarity in the sorting and the similarity after the first similarity; an execution module, used to construct a prompt word based on the mode information of the data table corresponding to the first vector information corresponding to the similarity retained in the sorting, and the natural language question input by the user; input the prompt word into the large language model to obtain the SQL statement output by the large language model.
[0011] In some examples, when constructing prompt words based on the pattern information of the data table corresponding to the first vector information corresponding to the similarity retained in the sorting, and the natural language question input by the user, the execution module is specifically used to: determine the overall structure of the prompt words, the overall structure includes the work tasks of the large model, prompt instructions, pattern information of the data table and answers; define the beginning of the work tasks of the large model, and the work tasks are to generate SQL statements; parse the pattern information of the data table corresponding to the first vector information corresponding to the similarity retained in the sorting, and add the parsed information and the natural language question to the prompt words, and set the prompt instructions and answers to obtain the constructed prompt words.
[0012] In some examples, the acquisition module is also used to: check whether the description of the main information of the data table, important fields and their field meanings in the data table schema information is clear; if not, optimize the schema information, and the optimization includes adding database schema information.
[0013] On the other hand, the present application provides an electronic device, including: a processor, and a memory communicatively connected to the processor; the memory stores computer-executable instructions; the processor executes the computer-executable instructions stored in the memory to implement the above method.
[0014] On the other hand, the present application provides a computer-readable storage medium, in which computer-executable instructions are stored. When the computer-executable instructions are executed by a processor, they are used to implement the above method.
[0015] The SQL statement generation method, device, equipment and storage medium based on the large model provided by the present application first obtain the mode information of each data table in the database, then convert the mode information of each data table to obtain the first vector information corresponding to each data table, and then build a vector knowledge base based on the first vector information; convert the user's natural language question to obtain the second vector information; calculate the similarity between the first vector information and the second vector information, and arrange the similarity in descending order, determine the first similarity that suddenly decreases, delete the first similarity and the subsequent similarities; then construct a prompt word based on the data table mode information corresponding to the first vector information corresponding to the retained similarity, and the natural language question input by the user, and input the prompt word into the large language model to obtain the SQL statement output by the large language model. In this scheme, by constructing the prompt word based on the mode information corresponding to the vector information selected based on the similarity sorting, the prompt word can be used to optimize the natural language question input by the user, and generate SQL statements for the Text2SQL large model, thereby improving the accuracy of statement generation, which is suitable for complex query requirements, and does not need to rely on tedious manual work, and can be applied to various business needs. BRIEF DESCRIPTION OF THE DRAWINGS
[0016] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the present application and, together with the description, serve to explain the principles of the present application.
[0017] Figure 1 A flowchart of a method for generating SQL statements based on a large model is shown in FIG.
[0018] Figure 2 A schematic diagram of the process of generating SQL statements for a large language model of an example;
[0019] Figure 3 A structural diagram of a SQL statement generation device based on a large language model is exemplarily shown in FIG.
[0020] Figure 4 A schematic diagram of the structure of an electronic device provided in an embodiment of the present application.
[0021] The above drawings have shown clear embodiments of the present application, which will be described in more detail later. These drawings and text descriptions are not intended to limit the scope of the present application in any way, but to illustrate the concept of the present application to those skilled in the art by referring to specific embodiments. DETAILED DESCRIPTION
[0022] Exemplary embodiments will be described in detail herein, examples of which are shown in the accompanying drawings. When the following description refers to the drawings, the same numbers in different drawings represent the same or similar elements unless otherwise indicated. The implementations described in the following exemplary embodiments do not represent all implementations consistent with the present application. Instead, they are merely examples of devices and methods consistent with some aspects of the present application as detailed in the appended claims.
[0023] It should be noted that the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, stored data, displayed data, etc.) involved in this application are all information and data authorized by the user or fully authorized by all parties, and the collection, use and processing of relevant data must comply with relevant laws, regulations and standards, and provide corresponding operation entrances for users to choose to authorize or refuse.
[0024] The terms "first", "second", etc. in the specification and claims of this application and the above drawings are used to distinguish similar or similar objects or entities, and do not necessarily mean to limit a specific order or sequence, unless otherwise indicated. It should be understood that the terms used in this way can be interchangeable under appropriate circumstances, for example, they can be implemented in an order other than those given in the diagrams or descriptions of the embodiments of this application.
[0025] In addition, the terms "including" and "having" and any variations thereof are intended to cover but not exclude inclusion, for example, a product or device comprising a series of components is not necessarily limited to those components explicitly listed, but may include other components not explicitly listed or inherent to these products or devices. The term "module" as used in this application refers to any known or later developed hardware, software, firmware, artificial intelligence, fuzzy logic or combination of hardware and / or software code that can perform the functions associated with the element.
[0026] The generation of SQL statements by a large language model mainly involves three parts: language understanding and representation, model training, and generation process. Language understanding and representation include word vector representation and sentence encoding; model training is divided into large-scale data pre-training and specific task fine-tuning. Model fine-tuning is performed to gradually improve the accuracy of generated SQL statements; the generation process is divided into three parts: decoding strategy, attention mechanism, and iterative optimization. Since the generated SQL statements may not be perfect at one time, the large language model can continuously improve the generated results through iterative optimization. In short, the generation of SQL statements by a large language model is achieved by understanding and encoding natural language, using large-scale data pre-training and specific task fine-tuning, and adopting appropriate decoding strategies and attention mechanisms. This process requires powerful computing resources and a large amount of data support, and also requires continuous optimization and improvement to improve the accuracy and practicality of generation.
[0027] Text to SQL, abbreviated as Text2SQL, is the process of converting natural language text (Text) into structured query language SQL, which is a subtask in the field of natural language processing semantic analysis. The purpose is to enable ordinary users to complete complex database queries through natural language descriptions and get the desired results.
[0028] In the related technology, there are roughly two approaches to Text2SQL tasks based on large language models: one is to directly generate them using existing large model products, but this method is prone to data leakage and has low security; the other is to use data sets to fine-tune the open source large model, but since the model does not have a deep understanding of the complex language of specific fields and specific business scenarios, the fine-tuning effect will be poor, resulting in the generation of incorrect SQL statements.
[0029] The technical solution provided by this application is intended to solve the above problems of related technologies. In an embodiment of this application, firstly, the mode information of each data table in the database is obtained, wherein the mode information of the data table is used to describe the structure of the data table; the first vector information corresponding to each data table is converted according to the mode information of the table, and a vector knowledge base is constructed according to the first vector information; the natural language question input by the user is converted to obtain the second vector information; the similarity between the first vector information and the second vector information corresponding to each data table is calculated, and the similarity is sorted in descending order; the first similarity where the similarity suddenly decreases is determined from the sorting, and the first similarity and the similarity after the first similarity are deleted from the sorting; the prompt word is constructed according to the mode information of the data table corresponding to the first vector information corresponding to the similarity retained in the sorting, and the natural language question input by the user; the prompt word is input into the large language model to obtain the SQL statement output by the large language model. This solution obtains the vector information with the strongest similarity to the user's question by semantic retrieval, recall and prior filtering of the vector knowledge base, and then optimizes the natural language input based on the prompt word to generate SQL statements for the Text2SQL large model. In this way, users do not need to understand the specific information of the database, and can achieve complex query requirements by simply asking questions, which greatly reduces tedious manual work and improves the accuracy of Text2SQL, and avoids the potential data leakage risks of using ready-made large models. At the same time, the present invention can be applied to various business scenarios and has good universality.
[0030] The technical solution of the present application and how the technical solution of the present application solves the above-mentioned technical problems are described in detail below with specific embodiments. The following specific embodiments can be combined with each other, and the same or similar concepts or processes may not be repeated in some embodiments. The embodiments of the present application will be described below in conjunction with the accompanying drawings.
[0031] Embodiment 1
[0032] Figure 1 A flow chart of a method for generating SQL statements based on a large model is shown in FIG. 1 . This embodiment relates to generating SQL statements, such as Figure 1 As shown, the method includes:
[0033] S101, acquiring mode information of each data table in a database, wherein the mode information of the data table is used to describe the structure of the data table.
[0034] S102, converting the mode information of each data table to obtain first vector information corresponding to each data table, and building a vector knowledge base according to the first vector information; converting the natural language question input by the user to obtain second vector information.
[0035] S103, calculating the similarity between the first vector information and the second vector information corresponding to each data table, and arranging the similarities in descending order; determining the first similarity where the similarity suddenly decreases from the sorting, and deleting the first similarity and the similarities after the first similarity from the sorting.
[0036] S104, constructing prompt words according to the pattern information of the data table corresponding to the first vector information corresponding to the similarity retained in the sorting, and the natural language question input by the user; inputting the prompt words into the large language model to obtain the SQL statement output by the large language model.
[0037] Among them, the schema information of the data table is used to describe the structure of the data table, for example, it can be the schema information of the data table. Specifically, in the process of generating SQL statements, the vector knowledge base is used to assist natural language understanding and SQL generation. The construction of the vector knowledge base can be achieved by converting table names, column names, etc. into word vectors or using a specific encoding method to map the database structure information to the vector space; similarity usually refers to the similarity between the description of the natural language problem and the table structure, column name, data, etc. in the database.
[0038] In combination with actual scenarios, when an SQL statement is to be generated, the schema information of each data table in the database is first obtained, and then the schema information of each data table is converted to obtain the first vector information corresponding to each data table, and then a vector knowledge base is constructed based on the first vector information; the user's natural language question is converted to obtain the second vector information; the similarity between the first vector information and the second vector information is calculated, and the similarities are arranged in descending order, the first similarity that suddenly decreases is determined, and the first similarity and subsequent similarities are deleted; then, based on the data table schema information corresponding to the first vector information corresponding to the retained similarity, and the natural language question input by the user, a prompt word is constructed, and the prompt word is input into the large language model to obtain the SQL statement output by the large language model.
[0039] As an example, similarity can be calculated by vector retrieval. For example, the cosine distance between the first vector information and the second vector information can be calculated as the similarity. The smaller the distance, the more similar it is. After obtaining each similarity, sort it in descending order, determine the first similarity where the similarity suddenly drops from the descending order, delete the first similarity in the sorting and the similarities after the first similarity, and the number of remaining similarities retained is recorded as k. The data table corresponding to the first vector information corresponding to these k similarities constitutes the top k data table. Further, the pattern information of each data table in the top k data table is obtained.
[0040] Take the natural language question "How many singers do we have?" asked by the user as an example. Assuming that the most similar data table that can be retrieved through vector search is the singer table, the schema information of the singer table is as follows:
[0041] "CREATE TABLE "singer"" (——Create a singer table
[0042] "Singer__ID" int,——singer ID, primary key
[0043] "Name" text,——singer's name
[0044] "Country" text, —— singer country
[0045] "Song_Name" text,——song name
[0046] "Song_release_year" text, - the year the song was released
[0047] "Age" int,——singer's age
[0048] "Is_male" bool,——singer's gender, whether he is male
[0049] PRIMARY KEY("Singer_ID") – Set Singer_ID as the primary key
[0050] );”
[0051] Specifically, there is no limit to the means of constructing prompt words. In some examples, in S104, according to the pattern information of the data table corresponding to the first vector information corresponding to the similarity retained in the sorting, and the natural language question input by the user, constructing prompt words, which may specifically include: determining the overall structure of the prompt words, the overall structure includes the work tasks of the large model, prompt instructions, the pattern information of the data table and the answer; defining the beginning of the work task of the large model, the work task is to generate SQL statements; parsing the pattern information of the data table corresponding to the first vector information corresponding to the similarity retained in the sorting, and adding the parsed information and the natural language question to the prompt words, and setting the prompt instructions and answers to obtain the constructed prompt words.
[0052] In practical applications, it is very important to construct prompts reasonably for large models. Prompts can help large models understand the real intentions of users to generate more accurate results. Among them, the work task of the large model is to generate SQL statements. Generating SQL statements is an application task of natural language processing in a specific field (interacting with databases). It converts natural language descriptions into structured SQL query languages to obtain required information from databases. In this example, at the beginning of defining the work task of the large model, SQL statements are generated. In this example, the overall structure of the prompt is first determined, including what the large model needs to do, the natural language questions asked by users, prompt instructions, and the schema information of the data table; then the beginning of the prompt is defined. In this example, the work task of the large model is defined at the beginning of the prompt to generate SQL statements; then, according to the data table corresponding to the first vector information corresponding to the similarity retained after S103, the schema information of these data tables is parsed and added to the prompt together with the natural language questions asked by users.
[0053] As an example, the constructed prompt includes four parts: "work task", "prompt instruction", "data table pattern information", and "answer", among which "prompt instruction" can be an optional item. Among them, "work task" is the working purpose of the large language model, that is, to generate an SQL statement to answer user questions. "Prompt instruction" is prompt information for the large language model, including but not limited to returning "I don't know" if the data table pattern information cannot answer this question. "Data table pattern information" means the pattern information of the most similar data table, indicating that the large language model generates an SQL statement corresponding to the user's question based on the pattern information of these data tables. The "answer" part reiterates the question asked by the user to avoid the large language model from forgetting knowledge after receiving the long information input of "prompt instruction" and "data table pattern information". For example, the constructed prompt words are as follows:
[0054] “##Work Tasks
[0055] Generate a SQL query to answer the following question: [question]{user_question}[ / question]
[0056] ###Prompt instructions
[0057] If you cannot answer this question based on the schema information in the datasheet, return "I don't know"
[0058] ###Data table schema information
[0059] This query will be run on a database with the following schema:
[0060] {table_schema_string}
[0061] ###answer
[0062] Based on the schema information of the data table, the following is the SQL query to answer [question]{user_question}[ / question]:
[0063] [SQL]”
[0064] For another example, take the natural language question asked by the user as "How many singers do we have?", combined with the top k data table, assuming that the top k data table includes the singer table, the corresponding prompt word example is constructed as follows:
[0065] “##Work Tasks
[0066] Generate a SQL query to answer the following question: [question]How many singers do we have? [ / question]
[0067] ###Prompt instructions
[0068] If you cannot answer this question based on the schema information in the datasheet, return "I don't know"
[0069] ###Data table schema information
[0070] This query will be run on a database with the following schema:
[0071] The query will run on a database with the following schema:
[0072] CREATE TABLE "singer" (——Create a singer table
[0073] "Singer__ID" int,——singer ID, primary key
[0074] "Name" text,——singer's name
[0075] "Country" text, —— singer country
[0076] "Song_Name" text,——song name
[0077] "Song_release_year" text, - the year the song was released
[0078] "Age" int,——singer's age
[0079] "Is_male" bool,——singer's gender, whether he is male
[0080] PRIMARY KEY("Singer_ID") – Set Singer_ID as the primary key );
[0082] ###answer
[0083] Based on the schema information of the data table, the following is the SQL query to answer [question] How many singers do we have? [ / question]:
[0084] [SQL]”
[0085] In this example, constructing prompt words can better understand the natural language questions input by users, and ensure that the generated SQL statements are more accurate. In actual applications, the types of prompt words are not limited, such as prompt words related to database objects, prompt words related to operation types, prompt words related to conditions, and prompt words related to aggregation. The specific prompt words to be constructed need to comprehensively consider factors such as task objectives, user perspectives, and business domain knowledge. In actual applications, prompt words can be referred to as prompts, because prompt has become a widely accepted common term in the field of natural language processing and related technical communities. In addition, many popular natural language processing frameworks and tools use the term prompt to represent prompt words; therefore, using prompt to refer to prompt words is universal, consistent, and clear.
[0086] Specifically, based on the input prompt words, the large language model can accurately understand the natural language expression in the prompt words and convert them into correct SQL statements. Among them, the prompt words can clearly express the query intent and avoid ambiguous expressions, so as to prevent the large language model from generating ambiguity and generating incorrect SQL statements; in addition, the use of a model based on optimizing the natural language questions input by users using prompt words avoids the risk of directly using existing large models to generate SQL statements and generating data leakage.
[0087] In some examples, the first vector information and / or the second vector information is a low-dimensional dense vector. A low-dimensional dense vector refers to a vector representation with relatively low dimensions and relatively compact numerical distribution. In the process of generating SQL statements, low-dimensional dense vectors can provide guidance and constraints. Specifically, based on the matching results of natural language questions and database structures, the table name, column name, conditions, and other parts of the SQL query can be determined.
[0088] In some examples, the description of the main information of the data table, important fields and their meanings in the schema information of the data table is clear; if not, the schema information is optimized, and the optimization includes adding database schema information. Among them, the types of added database schema information include table structure information, table relationship information, index information, stored procedure and function information; specifically, the added database schema information is mainly selected according to user needs. The database schema information added during the optimization process can be manually input by the user or automatically added by the program. Complete database schema information can improve the accuracy and efficiency of generating SQL statements.
[0089] In actual applications, there are many models for implementing the Text2SQL task to generate SQL statements using large language models, among which the SQLCoder series models have the most outstanding performance. In some examples, the SQL statement is input into the SQL executor to obtain the execution result. Figure 2 The following is a schematic diagram of the process of generating SQL statements for the example large language model, as shown in Figure 2 As shown, firstly, the mode information of each data table in the database is obtained, and then the mode information of each data table is converted to obtain the first vector information corresponding to each data table, and then the vector knowledge base is constructed according to the first vector information; the user's natural language question is converted to obtain the second vector information; the similarity between the first vector information and the second vector information is calculated, and the similarity is arranged in descending order, the first similarity that suddenly decreases is determined, and the first similarity and the subsequent similarities are deleted; then, according to the data table mode information corresponding to the first vector information corresponding to the retained similarity and the natural language question input by the user, a prompt word is constructed, and the prompt word is input into the large language model to obtain the SQL statement output by the large language model, and the obtained SQL statement is input into the SQL executor to obtain the result after the SQL statement is executed. Specifically, executing the SQL statement can immediately verify the correctness of the syntax and logic and verify the integrity of the data, and can provide timely feedback to let the user quickly understand the effect of the SQL statement.
[0090] The SQL statement generation method based on the large model provided in this embodiment first obtains the mode information of each data table in the database, then converts the mode information of each data table to obtain the first vector information corresponding to each data table, and then constructs a vector knowledge base based on the first vector information; converts the user's natural language question to obtain the second vector information; calculates the similarity between the first vector information and the second vector information, and arranges the similarity in descending order, determines the first similarity that suddenly decreases, deletes the first similarity and subsequent similarities; then constructs prompt words based on the data table mode information corresponding to the first vector information corresponding to the retained similarity, and the natural language question input by the user, and inputs the prompt words into the large language model to obtain the SQL statement output by the large language model. In this solution, by constructing prompt words based on the mode information corresponding to the vector information selected based on the similarity sorting, the prompt words can be used to optimize the natural language question input by the user, and generate SQL statements for the Text2SQL large model, thereby improving the accuracy of statement generation, which is suitable for complex query requirements, and does not need to rely on tedious manual work, and can be applied to various business needs.
[0091] Embodiment 2
[0092] Figure 3 A schematic diagram of a structure of a SQL statement generation device based on a large model is shown in FIG. Figure 3 As shown, the device comprises:
[0093] The acquisition module 11 is used to acquire the mode information of each data table in the database, wherein the mode information of the data table is used to describe the structure of the data table.
[0094] The conversion module 12 is used to convert the mode information of each data table to obtain the first vector information corresponding to each data table, and build a vector knowledge base based on the first vector information; convert the natural language question input by the user to obtain the second vector information.
[0095] The processing module 13 is used to calculate the similarity between the first vector information and the second vector information corresponding to each data table, and arrange the similarities in descending order; determine the first similarity where the similarity suddenly decreases from the order, and delete the first similarity and the similarities after the first similarity in the order.
[0096] The execution module 14 is used to construct prompt words according to the pattern information of the data table corresponding to the first vector information corresponding to the similarity retained in the sorting, and the natural language question input by the user; input the prompt words into the large language model to obtain the SQL statement output by the large language model.
[0097] In some examples, the execution module 14 is also used to: determine the overall structure of the prompt word, which includes the work tasks of the large model, prompt instructions, data table pattern information and answers; define the beginning of the work task of the large model, which is to generate SQL statements; parse the data table pattern information corresponding to the first vector information corresponding to the similarity retained in the sorting, and add the parsed information and natural language questions to the prompt word, and set prompt instructions and answers to obtain the constructed prompt word.
[0098] In some examples, the acquisition module 11 is also used to: according to whether the description of the main information of the data table, important fields and their meanings in the schema information of the data table is clear; if not, the schema information is optimized, and the optimization includes adding database schema information. Among them, the added database schema information types include table structure information, table relationship information, index information, stored procedure and function information; specifically, the added database schema information is mainly selected according to user needs. The database schema information added during the optimization process can be manually input by the user or generated by the program. Complete database schema information can improve the accuracy and efficiency of generating SQL statements.
[0099] In some examples, the execution module 14 is also used to input the SQL statement into the SQL executor to obtain the execution result. The execution of the SQL statement can immediately verify the correctness of the syntax and logic and the integrity of the data, and can provide timely feedback to allow users to quickly understand the effect of the SQL statement.
[0100] It should be noted that this embodiment is a device embodiment corresponding to the aforementioned method embodiment, so the detailed content of this embodiment can refer to the relevant content of the aforementioned embodiment.
[0101] In the SQL statement generation device based on the large model provided in this embodiment, the mode information of each data table in the database is first obtained, and then the mode information of each data table is converted to obtain the first vector information corresponding to each data table, and then the vector knowledge base is constructed according to the first vector information; the user's natural language question is converted to obtain the second vector information; the similarity between the first vector information and the second vector information is calculated, and the similarity is arranged in descending order, the first similarity that suddenly decreases is determined, and the first similarity and subsequent similarities are deleted; then, according to the data table mode information corresponding to the first vector information corresponding to the retained similarity and the natural language question input by the user, a prompt word is constructed, and the prompt word is input into the large language model to obtain the SQL statement output by the large language model. In this scheme, by constructing the prompt word based on the mode information corresponding to the vector information selected by similarity sorting, the prompt word can be used to optimize the natural language question input by the user, and the SQL statement is generated for the Text2SQL large model, thereby improving the accuracy of statement generation, which is suitable for complex query requirements, and does not need to rely on tedious manual work, and can be applied to various business needs.
[0102] Embodiment 3
[0103] Figure 4 A schematic diagram of the structure of an electronic device provided in an embodiment of the present application, the electronic device comprising:
[0104] The electronic device includes a processor 291 and a memory 292; it may also include a communication interface 293 and a bus 294. The processor 291, the memory 292, and the communication interface 293 may communicate with each other through the bus 294. The communication interface 293 may be used for information transmission. The processor 291 may call the logic instructions in the memory 292 to execute the above-mentioned example method.
[0105] In addition, the logic instructions in the above-mentioned memory 292 can be implemented in the form of software functional units and can be stored in a computer-readable storage medium when sold or used as an independent product. The memory 292, as a computer-readable storage medium, can be used to store software programs, computer executable programs, such as program instructions / modules corresponding to the methods in the embodiments of the present application. The processor 291 executes functional applications and data processing by running the software programs, instructions, and modules stored in the memory 292, that is, implementing the method in the above-mentioned method example.
[0106] The memory 292 may include a program storage area and a data storage area, wherein the program storage area may store an operating system and an application required for at least one function; the data storage area may store data created according to the use of the terminal device, etc. In addition, the memory 292 may include a high-speed random access memory and may also include a non-volatile memory.
[0107] An embodiment of the present application further provides a computer-readable storage medium, in which computer-executable instructions are stored. When the computer-executable instructions are executed by a processor, they are used to implement the method in any embodiment.
[0108] An embodiment of the present application also provides a computer program product, including a computer program, which implements the method in any embodiment when the computer program is executed by a processor.
[0109] It should be noted that, for the aforementioned method embodiments, for the sake of simplicity, they are all expressed as a series of action combinations, but those skilled in the art should be aware that the present application is not limited by the described order of actions, because according to the present application, certain steps can be performed in other orders or simultaneously. Secondly, those skilled in the art should also be aware that the embodiments described in the specification are all optional embodiments, and the actions and modules involved are not necessarily required by the present application.
[0110] In an exemplary embodiment, a computer-readable storage medium storing computer-executable instructions is also provided, and the computer-executable instructions are used to implement the above method when executed by a processor. For example, the computer-readable storage medium can be a read-only memory (ROM), a random access memory (RAM), a compact disc read-only memory (CD-ROM), a magnetic tape, a floppy disk, an optical data storage device, etc.
[0111] In an exemplary embodiment, a computer program product is also provided, comprising a computer program, and the computer program implements the above method when executed by a processor.
[0112] It should be noted that, for the aforementioned method embodiments, for the sake of simplicity, they are all expressed as a series of action combinations, but those skilled in the art should be aware that the present application is not limited by the described order of actions, because according to the present application, certain steps can be performed in other orders or simultaneously. Secondly, those skilled in the art should also be aware that the embodiments described in the specification are all optional embodiments, and the actions and modules involved are not necessarily required by the present application.
[0113] In the above embodiments, the description of each embodiment has its own emphasis. For parts not described in detail in a certain embodiment, please refer to the relevant description of other embodiments. The technical features of the above embodiments can be combined arbitrarily. In order to make the description concise, all possible combinations of the technical features in the above embodiments are not described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this specification.
[0114] Those skilled in the art will readily appreciate other embodiments of the present application after considering the specification and practicing the invention disclosed herein. The present application is intended to cover any modification, use or adaptation of the present application, which follows the general principles of the present application and includes common knowledge or customary techniques in the art that are not disclosed in the present application. The specification and examples are intended to be exemplary only, and the true scope and spirit of the present application are indicated by the following claims.
[0115] It should be understood that the present application is not limited to the precise structures that have been described above and shown in the drawings, and that various modifications and changes may be made without departing from the scope thereof. The scope of the present application is limited only by the appended claims.
Claims
1. A method for generating SQL statements based on a large model, characterized in that: include: Obtaining the schema information of each data table in the database, wherein the schema information of the data table is used to describe the structure of the data table; Converting the mode information of each data table to obtain first vector information corresponding to each data table, and constructing a vector knowledge base according to the first vector information; Convert the natural language question input by the user to obtain second vector information; Calculating the similarity between the first vector information and the second vector information corresponding to each data table, and sorting the similarities in descending order; Determine a first similarity at which a sudden decrease in similarity occurs from the ranking, and delete the first similarity and similarities after the first similarity from the ranking; Constructing prompt words according to the mode information of the data table corresponding to the first vector information corresponding to the similarity retained in the sorting and the natural language question input by the user; The prompt word is input into a large language model to obtain an SQL statement output by the large language model.
2. The method according to claim 1, characterized in that: The constructing of prompt words according to the mode information of the data table corresponding to the first vector information corresponding to the similarity retained in the sorting and the natural language question input by the user includes: Determine the overall structure of the prompt word, the overall structure includes the work task of the large model, the prompt instruction, the mode information of the data table and the answer; Define the beginning of the work task of the large model, the work task is to generate SQL statements; The mode information of the data table corresponding to the first vector information corresponding to the similarity retained in the sorting is parsed, and the parsed information and the natural language question are added to the prompt word, and prompt instructions and answers are set to obtain the constructed prompt word.
3. The method according to claim 1, characterized in that include: The first vector information and / or the second vector information is a low-dimensional dense vector.
4. The method according to claim 1, characterized in that: The method further comprises: Check whether the main information of the data table, important fields and their meanings are clearly described in the schema information of the data table; If it is not clear, the schema information is optimized, and the optimization includes adding the database schema information.
5. The method according to any one of claims 1 to 4, characterized in that: include: The SQL statement is input into the SQL executor to obtain the execution result.
6. A SQL statement generation device based on a large model, characterized in that: include: An acquisition module is used to acquire the mode information of each data table in the database, wherein the mode information of the data table is used to describe the structure of the data table; A conversion module, used for converting the mode information of each data table to obtain first vector information corresponding to each data table, and constructing a vector knowledge base according to the first vector information; Convert the natural language question input by the user to obtain second vector information; A processing module, used for calculating the similarity between the first vector information and the second vector information corresponding to each data table, and sorting the similarity in descending order; Determine a first similarity at which a sudden decrease in similarity occurs from the ranking, and delete the first similarity and similarities after the first similarity from the ranking; An execution module, configured to construct a prompt word according to the mode information of the data table corresponding to the first vector information corresponding to the similarity retained in the sorting and the natural language question input by the user; The prompt word is input into a large language model to obtain an SQL statement output by the large language model.
7. The device according to claim 6, characterized in that When the execution module constructs the prompt word according to the mode information of the data table corresponding to the first vector information corresponding to the similarity retained in the sorting and the natural language question input by the user, it is specifically used to: Determine the overall structure of the prompt word, the overall structure includes the work task of the large model, the prompt instruction, the mode information of the data table and the answer; Define the beginning of the work task of the large model, the work task is to generate SQL statements; The mode information of the data table corresponding to the first vector information corresponding to the similarity retained in the sorting is parsed, and the parsed information and the natural language question are added to the prompt word, and prompt instructions and answers are set to obtain the constructed prompt word.
8. The device according to claim 6, characterized in that The acquisition module is also used for: Check whether the main information of the data table, important fields and their meanings are clearly described in the schema information of the data table; If it is not clear, the schema information is optimized, and the optimization includes adding the database schema information.
9. An electronic device, characterized in that: include: A processor, and a memory communicatively connected to the processor; The memory stores computer-executable instructions; The processor executes the computer-executable instructions stored in the memory to implement the method according to any one of claims 1 to 5.
10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores computer-executable instructions, which are used to implement the method according to any one of claims 1 to 5 when executed by a processor.
Citation Information
Patent Citations
Method and system for automatically generating sql and replying questions
CN118093634A
Structured query language statement generation method and device, equipment and storage medium
CN118363977A
Cited By
Method and device for converting natural language into SQL (Structured Query Language) statement based on large model
CN119961289A
A method and device for converting natural language into SQL statements based on a large model
CN119961289B
Data intelligent interaction enhanced query method and system based on large model
CN120162344A
SQL generation method based on large language model and storage medium
CN120950527A
Method for generating statement from natural language to SQL (Structured Query Language) based on large language model agent
CN121387930A
