SQL statement generation method and device, electronic equipment and storage medium
By obtaining and constructing historical query data and table structure data, similarity matching and splicing are performed to form target propt, the problem of difficulty in injecting new business scenario knowledge in NL2SQL technology is solved, and the accuracy and efficiency of SQL statement generation is improved.
Patent Information
- Application Number
- CN202410983189.4
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-07-22
- Publication Date
- 2024-11-12
AI Technical Summary
The existing NL2SQL technology is difficult to flexibly inject new business scenario knowledge, relying on high-quality annotation data sets to consume a lot of resources and time, and it is difficult to fully cover all possible query scenarios, reducing query quality and efficiency.
By obtaining historical query data and vectorized table structure data, a historical query problem pair is constructed, and the current user query problem matches the current user query problem pair with the historical query problem pair is obtained, and the target historical query problem pair and the target table structure data are spliced into the target propt , enter a big model to generate SQL statements.
It improves the accuracy and efficiency of SQL statement generation, reduces the search space and calculation amount of the model when generating SQL statements, and improves the ability to convert natural language to SQL statements.
Smart Images

Figure CN118939681A_ABST
Abstract
Description
Technical Field
[0001] The present application relates to the field of artificial intelligence technology, and in particular to a method, device, electronic device and storage medium for generating SQL statements. Background Art
[0002] In the related technology, there is a method of NL2SQL (Natural Language to Structured Query Language, which converts natural language into SQL (Structured Query Language) statements). In the current NL2SQL technology practice, it usually relies on the inherent knowledge of the pre-trained model, and it is difficult to flexibly inject new business scenario knowledge during the model fine-tuning process. In addition, the current NL2SQL technology relies on building high-quality annotated data sets under the target business scenario to improve model performance. This method not only consumes a lot of resources and time, but also makes it difficult to fully cover all possible query scenarios, thereby reducing query quality and query efficiency.
[0003] In summary, the technical problems existing in the relevant technologies need to be improved. Summary of the invention
[0004] The embodiments of the present application aim to solve at least one of the technical problems in the related art to a certain extent. To this end, the main purpose of the embodiments of the present application is to propose a method, device, electronic device and storage medium for generating SQL statements, which can generate more accurate SQL statements that are more in line with user intentions, and at the same time improve the model's ability to convert natural language into SQL statements.
[0005] To achieve the above object, an embodiment of the present application provides a method for generating a SQL statement, the method comprising the following steps:
[0006] Acquire historical query data and vectorized table structure data, and acquire historical table structure data corresponding to the historical query data based on the historical query data and the vectorized table structure data;
[0007] Constructing a historical query question pair based on the historical query data and the historical table structure data;
[0008] Performing a first similarity match between the current user query question and the historical query question pair to obtain a target historical query question pair corresponding to the current user query question; the current user query question is a natural language text;
[0009] Performing a second similarity match between the current user query question and the vectorized table structure data to obtain target table structure data corresponding to the current user query question;
[0010] The current user query question, the target historical query question pair and the target table structure data are concatenated to obtain a target prompt;
[0011] The target prompt is input into the target big model so that the target big model outputs the target SQL statement corresponding to the current user query question.
[0012] In some embodiments, before acquiring the historical query data and the vectorized table structure data, and acquiring the historical table structure data corresponding to the historical query data based on the historical query data and the vectorized table structure data, the method further includes:
[0013] Obtain original table information in the original database;
[0014] Integrate the original table information to obtain table structure data to be processed;
[0015] Vectorization is performed on the table structure data to be processed to obtain the vectorized table structure data, and the vectorized table structure data is stored in a target database.
[0016] In some embodiments, the acquiring the historical table structure data corresponding to the historical query data based on the historical query data and the vectorized table structure data includes:
[0017] Parsing the historical SQL statements in the historical query data to obtain the table names corresponding to the historical SQL statements;
[0018] A third similarity match is performed between the table name and the vectorized table structure data in the target database to obtain the historical table structure data corresponding to the historical SQL statement.
[0019] In some embodiments, the constructing a historical query question pair based on the historical query data and the historical table structure data includes:
[0020] Constructing a historical prompt based on the historical SQL statements in the historical query data and the historical table structure data;
[0021] Input the historical prompt into the target big model, so that the target big model outputs the historical query purpose corresponding to the historical SQL statement;
[0022] Determine the historical query question corresponding to the historical SQL statement according to the historical query purpose;
[0023] The historical query question pair is constructed according to the historical SQL statement and the historical query question, and the historical query question pair is stored in the target database.
[0024] In some embodiments, performing a first similarity matching between the current user query question and the historical query question pair to obtain a target historical query question pair corresponding to the current user query question includes:
[0025] Retrieving a plurality of the historical query question pairs in the target database based on the first index, and respectively calculating a first similarity between each of the historical query question pairs in the target database and the current user query question, to obtain a first similarity ranking result;
[0026] The target historical query question pair corresponding to the current user query question is obtained based on the first similarity ranking result.
[0027] In some embodiments, performing a second similarity matching between the current user query question and the vectorized table structure data to obtain the target table structure data corresponding to the current user query question includes:
[0028] Retrieving a plurality of the vectorized table structure data in the target database based on the second index, and respectively calculating a second similarity between each of the vectorized table structure data in the target database and the current user query question, to obtain a second similarity ranking result;
[0029] The target table structure data corresponding to the current user query question is obtained based on the second similarity ranking result.
[0030] In some embodiments, inputting the target prompt into the target big model so that the target big model outputs the target SQL statement corresponding to the current user query question includes:
[0031] Inputting the target prompt into the target macro model; wherein the target prompt includes a target input sentence;
[0032] When the target input statement is input into the target macro model, the target macro model outputs the target SQL statement corresponding to the current user query question according to the target input statement.
[0033] To achieve the above object, another aspect of the embodiment of the present application provides a device for generating SQL statements, the device comprising the following modules:
[0034] A data acquisition module, used to acquire historical query data and vectorized table structure data, and acquire historical table structure data corresponding to the historical query data based on the historical query data and the vectorized table structure data;
[0035] A historical query question pair construction module, used to construct a historical query question pair based on the historical query data and the historical table structure data;
[0036] A target historical query question pair acquisition module is used to perform a first similarity match between the current user query question and the historical query question pair to obtain a target historical query question pair corresponding to the current user query question; the current user query question is a natural language text;
[0037] A target table structure data acquisition module is used to perform a second similarity match between the current user query question and the vectorized table structure data to obtain the target table structure data corresponding to the current user query question;
[0038] A target prompt construction module is used to concatenate the current user query question, the target historical query question pair and the target table structure data to obtain a target prompt;
[0039] The target SQL statement output module is used to input the target prompt into the target large model so that the target large model outputs the target SQL statement corresponding to the current user query question.
[0040] To achieve the above-mentioned purpose, another aspect of an embodiment of the present application provides an electronic device, the electronic device comprising a memory and a processor, the memory storing a computer program, and the processor implementing the above-mentioned method when executing the computer program.
[0041] To achieve the above objective, another aspect of an embodiment of the present application provides a computer-readable storage medium, wherein the computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the method described above is implemented.
[0042] The embodiments of the present application include at least the following beneficial effects: The present application provides a SQL statement generation method, device, electronic device and storage medium, the scheme obtains historical query data and vectorized table structure data, and obtains historical table structure data corresponding to the historical query data based on the historical query data and the vectorized table structure data; then constructs a historical query question pair based on the historical query data and the historical table structure data; then performs a first similarity match on the current user query question and the historical query question pair to obtain a target historical query question pair corresponding to the current user query question; the current user query question is a natural language text; performs a second similarity match on the current user query question and the vectorized table structure data to obtain the target table structure data corresponding to the current user query question; then splices the current user query question, the target historical query question pair and the target table structure data to obtain a target prompt; finally, inputs the target prompt into the target big model, so that the target big model outputs the target SQL statement corresponding to the current user query question. The embodiment of the present application constructs historical query question pairs by using historical query data and corresponding historical table structure data, so that the large model can refer to and draw on similar query scenarios and table structures in the past when generating SQL statements, that is, learn query patterns in different scenarios, so as to more accurately understand the intention of the current user query question and generate SQL statements that match it; and, use similarity matching to quickly locate the historical query question pairs and table structure data that are most relevant to the current user query question, greatly reducing the search space and calculation amount of the model when generating SQL statements, and improving query efficiency; at the same time, by constructing a target prompt containing historical query question pairs and table structure data, it can guide the large model to generate more accurate SQL statements that are more in line with user intentions, greatly improving the model's ability to convert natural language into SQL statements. BRIEF DESCRIPTION OF THE DRAWINGS
[0043] Figure 1 This is a flowchart of a method for generating a SQL statement provided by an embodiment of the present application;
[0044] Figure 2 It is a flowchart of the steps of generating vectorized table structure data provided by an embodiment of the present application;
[0045] Figure 3 It is a flowchart of the steps of generating historical table structure data provided by an embodiment of the present application;
[0046] Figure 4 yes Figure 1 The step flow chart of step S102 in FIG.
[0047] Figure 5 yes Figure 1 The step flow chart of step S103 in FIG.
[0048] Figure 6 yes Figure 1 The step flow chart of step S104 in FIG.
[0049] Figure 7 It is a schematic flow chart of a method for generating a SQL statement provided in an embodiment of the present application;
[0050] Figure 8 It is a schematic block diagram of a process for constructing a historical query question pair provided in an embodiment of the present application;
[0051] Fig. 9 It is a structural schematic diagram of a SQL statement generating device provided in an embodiment of the present application;
[0052] Fig.10 It is a schematic diagram of the hardware structure of the electronic device provided in the embodiment of the present application. DETAILED DESCRIPTION
[0053] In order to make the purpose, technical solutions and advantages of the present application clearer, the present application is further described in detail below in conjunction with the accompanying drawings and examples. It should be understood that the specific embodiments described herein are only used to explain the present application and are not intended to limit the present application. When the following description refers to the drawings, unless otherwise indicated, the same numbers in different drawings represent the same or similar elements. The embodiments described in the following exemplary embodiments do not represent all embodiments consistent with the embodiments of the present application. They are only examples of devices and methods consistent with some aspects of the embodiments of the present application as detailed in the attached claims.
[0054] It is understood that the terms "first", "second", etc. used in this application can be used to describe various concepts in this article, but unless otherwise specified, these concepts are not limited by these terms. These terms are only used to distinguish one concept from another concept. For example, without departing from the scope of the embodiment of the present application, the first information may also be referred to as the second information, and similarly, the second information may also be referred to as the first information. Depending on the context, the words "if" and "if" as used herein can be interpreted as "at the time of" or "when" or "in response to determination".
[0055] The terms "at least one", "multiple", "each", "any", etc. used in this application, at least one includes one, two or more, multiple includes two or more, each refers to each of the corresponding multiple, and any refers to any one of the multiple.
[0056] Unless otherwise defined, all technical and scientific terms used herein have the same meaning as those commonly understood by those skilled in the art to which this application belongs. The terms used herein are only for the purpose of describing the embodiments of this application and are not intended to limit this application.
[0057] Before describing the embodiments of the present application in detail, some nouns and terms involved in the embodiments of the present application are first described. The nouns and terms involved in the embodiments of the present application are subject to the following explanations.
[0058] (1) NL2SQL (Natural Language to Structured Query Language), a method for converting natural language into SQL (Structured Query Language) statements.
[0059] (2) Schema, also known as table schema, refers to the definition of the organization and structure of data in the database. The schema includes the definition of all tables in the database, the names and types of fields, and the relationships between data (such as primary key constraints and foreign key constraints).
[0060] (3) Query, i.e., query statement, corresponds to NL (Natural Language) in NL2SQL. Specifically, users can express their intentions through query, such as the need to query data, create tables, or transform data.
[0061] (4) Prompt engineering is an artificial intelligence technology that aims to improve the performance of AI by designing and improving the prompts (i.e., instructions, questions or hints given to AI models).
[0062] (5) SFT (Supervised Fine-Tuning), which is a method of further training the model through supervised learning based on a large-scale pre-trained model to make it better suited to the needs of specific tasks or fields.
[0063] (6) RLHF (Reinforcement Learning from Human): This method combines reinforcement learning techniques and human feedback to train language models so that they can more accurately understand human language and generate outputs that meet human preferences. In RLHF, the model receives human evaluation or feedback on its output and learns and optimizes based on this feedback to improve its performance.
[0064] In the related technology, there is the NL2SQL method. In the current NL2SQL technology practice, it usually relies on the inherent knowledge of the pre-trained model, and it is difficult to flexibly inject new business scenario knowledge during the model fine-tuning process. In addition, the current NL2SQL technology relies on building a high-quality annotated dataset under the target business scenario to improve model performance. This method not only consumes a lot of resources and time, but also makes it difficult to fully cover all possible query scenarios, thereby reducing query quality and query efficiency.
[0065] For example, in the current NL2SQL technical practice, the prior knowledge based on the large model itself is injected during pre-training, but it is difficult to inject new knowledge during the SFT and RLHF process, that is, it is difficult to inject new business knowledge through fine-tuning during the NL2SQL technical practice. In order to improve the NL2SQL capabilities of large models, traditional NL2SQL technology needs to build a data set of NL2SQL under the target business scenario, which requires strict data annotation to obtain high-quality data. This process is not only time-consuming and computationally intensive, but also difficult to fully cover all possible query scenarios, thereby reducing query quality and query efficiency.
[0066] In view of this, an embodiment of the present application provides a SQL statement generation method, device, electronic device and storage medium. The scheme obtains historical query data and vectorized table structure data, and obtains historical table structure data corresponding to the historical query data based on the historical query data and the vectorized table structure data; then constructs a historical query question pair based on the historical query data and the historical table structure data; then performs a first similarity match on the current user query question and the historical query question pair to obtain a target historical query question pair corresponding to the current user query question; the current user query question is a natural language text; performs a second similarity match on the current user query question and the vectorized table structure data to obtain the target table structure data corresponding to the current user query question; then splices the current user query question, the target historical query question pair and the target table structure data to obtain a target prompt; finally, inputs the target prompt into the target large model, so that the target large model outputs the target SQL statement corresponding to the current user query question. The embodiment of the present application constructs historical query question pairs by using historical query data and corresponding historical table structure data, so that the large model can refer to and draw on similar query scenarios and table structures in the past when generating SQL statements, that is, learn query patterns in different scenarios, so as to more accurately understand the intention of the current user query question and generate SQL statements that match it; and, use similarity matching to quickly locate the historical query question pairs and table structure data that are most relevant to the current user query question, greatly reducing the search space and calculation amount of the model when generating SQL statements, and improving query efficiency; at the same time, by constructing a target prompt containing historical query question pairs and table structure data, it can guide the large model to generate more accurate SQL statements that are more in line with user intentions, greatly improving the model's ability to convert natural language into SQL statements.
[0067] The SQL statement generation method provided in the embodiment of the present application relates to the field of artificial intelligence technology. The SQL statement generation method provided in the embodiment of the present application can be applied to a terminal, can also be applied to a server, and can also be software running in a terminal or a server. In some embodiments, the terminal can be a smart phone, a tablet computer, a laptop computer, a desktop computer, a smart speaker, a smart watch, and a car terminal, etc., but is not limited to this; the server side can be configured as an independent physical server, or it can be configured as a server cluster or a distributed system composed of multiple physical servers, and can also be configured to provide cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communications, middleware services, domain name services, security services, CDN (Content Delivery Network, content distribution network) and big data and artificial intelligence platforms and other basic cloud computing services. The cloud server, the server can also be a node server in the blockchain network; the software can be an application that implements the SQL statement generation method, etc., but is not limited to the above form.
[0068] The present application can be used in many general or special computer system environments or configurations. For example: personal computers, server computers, handheld devices or portable devices, tablet devices, multiprocessor systems, microprocessor-based systems, set-top boxes, programmable consumer electronic devices, network PCs (Personal Computers, personal computers), minicomputers, mainframe computers, distributed computing environments including any of the above systems or devices, etc. The present application can be described in the general context of computer executable instructions executed by a computer, such as program modules. Generally, program modules include routines, programs, objects, components, data structures, etc. that perform specific tasks or implement specific abstract data types. The present application can also be practiced in distributed computing environments, in which tasks are performed by remote processing devices connected through a communication network. In a distributed computing environment, program modules can be located in local and remote computer storage media including storage devices.
[0069] See also Figure 1 , Figure 1 is an optional step flow chart of the SQL statement generation method provided in the embodiment of the present application. Figure 1 The method may include but is not limited to steps S101 to S106.
[0070] Step S101, obtaining historical query data and vectorized table structure data, and obtaining historical table structure data corresponding to the historical query data based on the historical query data and the vectorized table structure data;
[0071] Among them, historical query data is also called historical SQL query data. Historical query data not only includes historical SQL statements, but also includes all information related to the user's past query operations. This information may include but is not limited to: actual SQL query statements, query timestamps, user context information, and query results. This data can be used to train and optimize the SQL statement generation model so that the model can better understand and generate corresponding SQL statements.
[0072] In actual NL2SQL scenarios, there is a large amount of historical query data, which contains business knowledge and fixed paradigms. In an embodiment of the present application, by using historical query data in the context learning of NL2SQL, the quality of SQL statements generated by the model can be improved.
[0073] See also Figure 2 , Figure 2 is a flowchart of steps for generating vectorized table structure data provided by an embodiment of the present application; Figure 2 As shown, in some embodiments, before step S101, a method for generating vectorized table structure data is also included, and the method may include but is not limited to steps S201 to S203:
[0074] Step S201, obtaining original table information in the original database;
[0075] The original database may be used to store original table information, and the original database may be a relational database such as MySQL, Oracle or Hive.
[0076] The original table information is the original data of the table. The table information can be called table schema or schema. In the database field, schema refers to the definition of the organization and structure of data in the database. The schema includes the definition of all tables in the database, the names and types of fields, and the relationships between data (such as primary key constraints and foreign key constraints).
[0077] Step S202, integrating the original table information to obtain table structure data to be processed;
[0078] The table structure data to be processed is the table structure data obtained after the original table information is integrated, and the table structure data to be processed is the table structure data to be subsequently vectorized and stored in the target database.
[0079] Specifically, before data integration, the original database stores the original data of the table, and the data obtained after integrating the original data of the table is the data of the table information, that is, the data after the original table information is integrated mainly stores the structural information of the table, and does not save the specific data information in the table.
[0080] Step S203, vectorizing the table structure data to be processed to obtain the vectorized table structure data, and storing the vectorized table structure data in a target database.
[0081] The vectorization process is used to convert words, sentences, and paragraphs in natural language into high-dimensional vectors in semantic space. The specific vectorization model can adopt the sentence vector model BAAI / bge-large-zh-v1.5 in NLP (Natural Language Processing).
[0082] The target database is a database that can perform vectorized retrieval and storage, such as the Elastic Search database (ES database for short).
[0083] Vectorized table structure data is data obtained after vectorization of the table structure data to be processed. Vectorized table structure data is a data structure that represents table structure information in the form of vectors or arrays. By vectorizing the table structure data to be processed to obtain vectorized table structure data, the efficiency of data processing can be improved, especially in scenarios such as database query optimization and automatic index selection.
[0084] By retaining the original data in the relational database, and extracting and vectorizing the table structure information and storing it in the target database that can be retrieved and stored in a vectorized manner, the original data and the table structure data are stored separately, so that the data structure can be quickly searched and analyzed in the target database without directly accessing the huge data set stored in the relational database. In addition, since the target database supports vectorized search, it can efficiently process complex search requests and provide fast data retrieval capabilities.
[0085] An example of the process of steps S201 to S203 shown in the embodiment of the present application may be: concatenating a table name, a table comment (table description / table explanation), a field name, and a field comment (field description / field explanation) into a string through a specific separator ";", then vectorizing the concatenated string, and storing the vectorized data in the ES database.
[0086] For example, assuming that the table name is "taxi_method", the table comment is "Taxi method", the field names are "item_id, name, price", and the field comment is "item id, name, price per kilometer", then the table name, table comment, field name, and field comment in the example can be concatenated into a string through a specific separator (such as ";"). The string can be expressed as "taxi_method; taxi method; item_id; item id; name; name; price; price per kilometer".
[0087] In a specific implementation, steps S201 to S203 shown in the embodiment of the present application integrate the original schema information of the database into table schema data, and store the table schema data in a database that can be vectorized for retrieval and storage. By directly obtaining the original table information in the original database, integrating and vectorizing it, the data parsing and conversion time in subsequent query processing can be significantly reduced. At the same time, the storage and retrieval efficiency of vectorized table structure data is much higher than the traditional method, making the entire data processing process more efficient.
[0088] See also Figure 3 , Figure 3 is a flowchart of the steps for generating historical table structure data provided by an embodiment of the present application; Figure 3 As shown, in some specific embodiments, the step of obtaining the historical table structure data corresponding to the historical query data based on the historical query data and the vectorized table structure data may include but is not limited to steps S301 to S302:
[0089] Step S301, parsing the historical SQL statements in the historical query data to obtain the table names corresponding to the historical SQL statements;
[0090] Among them, historical SQL statements refer to the records of SQL query statements executed by users on the database at a certain point in the past. By analyzing historical SQL statements, users' data access patterns can be obtained, and high-frequency queries, inefficient queries, or potential optimization points can be identified. In addition, historical SQL statements can also be used to reproduce past query scenarios to help database administrators or developers understand and solve specific problems. Among them, the table name is parsed from the historical SQL statement. The table name is the unique identifier of the table in the database and is used to distinguish different tables.
[0091] Step S302: Perform a third similarity match between the table name and the vectorized table structure data in the target database to obtain the historical table structure data corresponding to the historical SQL statement.
[0092] Among them, the third similarity matching process refers to the process of similarity matching the table name parsed from the historical SQL statement with the table in the vectorized table structure data in the target database, that is, the system searches the vectorized table structure data in the target database to see whether there is a table with the same table name. If so, the structure data of the table is directly returned as the matching result, thereby obtaining the historical table structure data corresponding to the historical SQL statement. Through the similarity matching process, the system can find the historical table structure data corresponding to the table name referenced in the historical SQL statement, providing a data basis for the subsequent construction of historical query question pairs.
[0093] The historical table structure data is the historical table structure data corresponding to the historical query data, that is, the table structure data most relevant to the historical query data. The historical table structure data can be called the relevant schema information of the historical query data. The historical table structure data is obtained from the vectorized table structure data in the target database.
[0094] In a specific implementation, the historical table structure data (the relevant schema information of the historical query data) obtained in step S302 refers to parsing the historical SQL statements in the historical query data to find the table name existing in the historical SQL statements, and then querying the table creation statement in the target database by the table name.
[0095] For example, assuming that the user's historical SQL statement is "select item_id, count(1) as cnt from taxi_method", it can be parsed that the table the user wants to query is "taxi_method", and then the schema information of the "taxi_method" table can be retrieved, and the table creation statement (create_ddl) can be obtained based on the schema information of the table "taxi_method". The example of the table creation statement (create_ddl) corresponding to the table "taxi_method" is as follows:
[0096]
[0097] In a specific implementation, steps S301 to S302 shown in the embodiment of the present application parse the historical SQL statements in the historical query data to obtain the table names corresponding to the historical SQL statements, and perform a third similarity match between the table names and the vectorized table structure data in the target database to obtain the historical table structure data corresponding to the historical SQL statements. By parsing the historical SQL statements and extracting the table names, it is possible to accurately associate them with the specific database table structure, so that the user's query intent can be understood more accurately during subsequent query processing, thereby improving the accuracy of SQL statement generation.
[0098] In step S101 shown in the embodiment of the present application, historical query data and vectorized table structure data are obtained, and historical table structure data corresponding to the historical query data is obtained based on the historical query data and the vectorized table structure data. The method of this step can learn and extract important information from historical data so as to better identify and understand the user's needs or intentions when processing the current user query. At the same time, the vectorized table structure data can provide a more efficient data processing method and improve the query speed.
[0099] Step S102, constructing a historical query question pair based on the historical query data and the historical table structure data;
[0100] Among them, the historical query question pair can be expressed as a [SQL-question] pair, which refers to a data structure that associates historical SQL query data with its corresponding natural language question description (or intent). Through the [SQL-question] pair, it is easier to analyze the user's query behavior, obtain the query pattern, and improve the database design or query optimization strategy accordingly.
[0101] See also Figure 4 , Figure 4 yes Figure 1 The step flow chart of step S102 in FIG. Figure 4 As shown, in some embodiments, step S102 may include but is not limited to steps S401 to S404:
[0102] Step S401, constructing a historical prompt based on the historical SQL statements in the historical query data and the historical table structure data;
[0103] As for prompt, it is an artificial intelligence technology that aims to improve the performance of AI by designing and improving AI prompts (that is, instructions, questions or prompts given to AI models).
[0104] Among them, the historical prompt is a prompt project constructed by historical SQL statements and historical table structure data in historical query data.
[0105] Step 402, inputting the historical prompt into the target big model, so that the target big model outputs the historical query purpose corresponding to the historical SQL statement;
[0106] The target large model refers to a large machine learning model used to process a specific task (generating SQL statements in the embodiment of the present application), such as a deep learning architecture (such as Transformer. In the database query scenario, the target large model can receive natural language queries (such as user questions) and convert the natural language queries into equivalent SQL query statements. In the embodiment of the present application, the target large model can be an encoder only architecture of Transformer, such as qwen2-72b, llama3, etc.
[0107] For historical query purposes, it is the expected result or information obtained in the historical SQL query, which is an abstract representation of the real demand or intention of the query.
[0108] Step S403, determining the historical query question corresponding to the historical SQL statement according to the historical query purpose;
[0109] For historical query problems, it refers to the problem of clearly expressing the purpose of historical SQL queries in natural language.
[0110] Step S404: construct the historical query question pair according to the historical SQL statement and the historical query question, and store the historical query question pair in the target database.
[0111] The target database not only stores historical query question pairs, but also vectorized table structure data. The index (content) of the target database is differentiated. The vectorized table structure data stores multiple table information, and the historical query question pairs store multiple question-answer pairs. Different indexes can be used for retrieval when querying different data.
[0112] Steps S401 to S404 shown in the embodiment of the present application are to construct a historical prompt based on the historical SQL statements and historical table structure data in the historical query data, input the historical prompt into the target big model, so that the target big model outputs the historical query purpose corresponding to the historical SQL statement, determines the historical query question corresponding to the historical SQL statement according to the historical query purpose, constructs a historical query question pair according to the historical SQL statement and the historical query question, and stores the historical query question pair into the target database. By combining the historical SQL statement and the historical table structure data to construct the historical prompt, a rich context information can be provided to the big model, making it easier for the model to understand the background and purpose of the historical query. At the same time, the target big model can generate the purpose of the historical query based on the input historical prompt, so as to reproduce the query intent according to the purpose of the historical query, and provide guidance for the subsequent query questions and answer generation.
[0113] In the specific implementation, firstly, the historical SQL statements in the historical query data are parsed to obtain the table names corresponding to the historical SQL statements, and then the table names are matched with the vectorized table structure data in the target database for similarity to obtain the historical table structure data corresponding to the historical SQL statements. Then, the historical prompt is constructed based on the historical SQL statements and the historical table structure data in the historical query data, and the historical prompt is input into the target large model so that the target large model outputs the historical query purpose corresponding to the historical SQL statement, determines the historical query question corresponding to the historical SQL statement according to the historical query purpose, constructs a historical query question pair according to the historical SQL statement and the historical query question, and stores the historical query question pair in the target database. This method can convert the historical query data into a structured historical query question pair, and store the historical query question pair in the target database. The historical query question pair not only contains specific SQL statements, but also associates the query intent, providing data resources for subsequent query processing and optimization.
[0114] For example, assume that the historical SQL statement in the historical query data is "```select T3.name, T2.app name, T1.number of cars from methods supported by each taxi APP as T1 join taxi APP as T2 join taxi methods as T3on methods supported by each taxi APP.appid == taxi APP.entry id and each taxi APP supports the method. Method id == taxi method. Term id ```", then the SQL parsing module can be used to parse the historical query data to obtain the three related tables of "taxi APP supported methods, taxi APP, taxi methods", and then the table creation statements of these three tables can be obtained through the table schema data related to the historical query data, and then the historical prompt project is constructed according to the historical query data and the table schema data related to the historical query data. The historical prompt project guides the large model to explain the historical SQL statements in the historical query data in the example, and obtains the purpose of the historical SQL statements. According to the purpose of the historical SQL statements, the historical SQL question is obtained as "Among the taxi methods supported by the taxi app, give each app, the taxi methods it supports and the number of cars invested in this method", so as to form a [SQL-question] pair, that is, a historical query question pair, and finally the historical query question pair is stored in the target ES database.
[0115] It should be noted that the SQL parsing module can also parse out the field names in the historical SQL statements in the historical query data. The field names are crucial for accurately understanding the query intent. When the large model infers the query intent based on the historical prompt, if the field name information can be obtained, a more accurate and specific query intent (i.e. question) can be generated. However, different application scenarios have different requirements for information. Whether field names are needed depends on the specific application scenario and the requirements for the accuracy of the query intent, the capabilities of the SQL parsing module, and the limitations of system resources.
[0116] Step S102 shown in the embodiment of the present application, by constructing a historical query question pair based on the historical query data and the historical table structure data, can associate the historical data with the corresponding table structure data to form a targeted query question pair, thereby providing effective information for subsequent similarity matching.
[0117] Step S103, performing a first similarity match between the current user query question and the historical query question pair to obtain a target historical query question pair corresponding to the current user query question; the current user query question is a natural language text;
[0118] The current user query question is a natural language text. The user query question can be called a user query or a user query statement, which corresponds to NL (Natural Language) in NL2SQL. Specifically, the user can express the user's intention through the query, such as the need to query data, create a table, and transform data.
[0119] The first similarity matching is a process of similarity matching between the current user query and the historical query pair, through which a target historical query pair corresponding to the current user query can be obtained. The target historical query pair is the historical query pair that is most relevant to the current user query.
[0120] For example, assuming that the current user query question is "Among the taxi-hailing methods supported by the taxi app, give the taxi-hailing methods supported by each app and the number of cars deployed under that method, and give the concept of that method", by performing similarity matching between the current user query question and the historical query question pairs (historical [SQL-question] pairs) in the target database, the question and corresponding SQL statement that are most relevant to the current user query question in the historical [SQL-question] pairs can be found, that is, the target historical query question pair. The target historical query question pair contains the required business information.
[0121] See also Figure 5 , Figure 5 yes Figure 1The step flow chart of step S103 in FIG. Figure 5 As shown, in some embodiments, step S103 may include but is not limited to steps S501 to S502:
[0122] Step S501, retrieving a plurality of historical query question pairs in a target database based on a first index, and respectively calculating a first similarity between each of the historical query question pairs in the target database and the current user query question, to obtain a first similarity ranking result;
[0123] The first index is an index used to retrieve multiple historical query question pairs in the target database. An index is a data structure used to quickly find data in a database management system. Different indexes are used when retrieving different data.
[0124] Among them, the first similarity is the similarity between each historical query question pair in the target database and the current user query question. By calculating the similarity between each historical query question pair in the target database and the current user query question, the first similarity ranking result can be obtained. The first similarity ranking result reflects the similarity between each historical query question pair and the current user query question. Through the first similarity ranking result, the historical query question pair most relevant to the user's current query question can be quickly located, reducing user waiting time and improving query efficiency.
[0125] Step S502: obtaining the target historical query question pair corresponding to the current user query question based on the first similarity ranking result.
[0126] In steps S501 to S502 shown in the embodiment of the present application, a plurality of historical query question pairs are retrieved in the target database based on the first index, and the first similarity between each historical query question pair in the target database and the current user query question is calculated respectively, so as to obtain the first similarity ranking result, and the target historical query question pair corresponding to the current user query question is obtained based on the first similarity ranking result. By using the first index, the historical query question pairs in the target database can be quickly retrieved, which avoids scanning the entire database and greatly improves the query efficiency. Moreover, by calculating the similarity between the historical query question pairs and the current user query question, the correlation between the two can be evaluated, which provides a basis for finding the query question pair that best matches the current user query question. By obtaining the most relevant historical query question pair corresponding to the current user query question, the accuracy of the subsequent model generating SQL statements is improved.
[0127] Step S103 shown in the embodiment of the present application can quickly find the historical query question that is most similar to the current user query question by performing a first similarity match between the current user query question and the historical query question, thereby locating the user's needs more quickly and providing the user with corresponding query results.
[0128] Step S104, performing a second similarity match between the current user query question and the vectorized table structure data to obtain target table structure data corresponding to the current user query question;
[0129] The second similarity matching is a process of similarity matching between the current user query question and the vectorized table structure data, through which the target table structure data corresponding to the current user query question can be obtained. The target table structure data is the historical table structure data most relevant to the current user query question.
[0130] See also Figure 6 , Figure 6 yes Figure 1 The step flow chart of step S104 in FIG. Figure 6 As shown, in some embodiments, step S104 may include but is not limited to steps S601 to S602:
[0131] Step 601, retrieving a plurality of the vectorized table structure data in the target database based on the second index, and respectively calculating a second similarity between each of the vectorized table structure data in the target database and the current user query question, to obtain a second similarity ranking result;
[0132] Among them, the second index is an index for retrieving multiple vectorized table structure data in the target database. The second similarity is the similarity between each vectorized table structure data in the target database and the current user query question. By calculating the similarity between each vectorized table structure data in the target database and the current user query question, a second similarity ranking result can be obtained. The second similarity ranking result reflects the similarity between each vectorized table structure data and the current user query question. Through the second similarity ranking result, the vectorized table structure data most relevant to the user's current query question can be quickly located, reducing user waiting time and improving query efficiency.
[0133] Step S602: obtaining the target table structure data corresponding to the current user query question based on the second similarity ranking result.
[0134] In steps S601 to S602 shown in the embodiment of the present application, a plurality of vectorized table structure data in the target database are retrieved based on the second index, and the second similarity between each vectorized table structure data in the target database and the current user query question is calculated respectively, so as to obtain the second similarity ranking result, and the target table structure data corresponding to the current user query question is obtained based on the second similarity ranking result. By using the second index, the vectorized table structure data in the target database can be quickly retrieved, which avoids the whole database scan and improves the query efficiency. By calculating the similarity between the vectorized table structure data and the current user query question, the correlation between the two can be evaluated, which provides a basis for finding the most matching table structure data. By automatically matching the most relevant table structure data, a fast and accurate query result can be provided for the user's current question.
[0135] In the specific implementation, for example, assuming that the current user query question is "Among the taxi-hailing methods supported by the taxi-hailing app, give the taxi-hailing methods supported by each app and the number of cars deployed under this method, and give the concept of this method", by similarity matching the current user query question with the vectorized table structure data, it can be obtained that the table similar to the current user query question is "taxi-hailing methods, methods supported by each taxi-hailing app, taxi-hailing app", etc., that is, the target table structure data, and based on these similar target table structure data, the "Create DDL" corresponding to the target table structure data can be obtained, that is, the table creation statement corresponding to the target table structure data, and the target table structure data (the relevant schema information of the current user query question) is expressed in the form of "Create DDL". The example of "Create DDL" is as follows:
[0136]
[0137]
[0138] It should be noted that the method of obtaining schema information (target table structure data) related to the current user query (current user query question) is different from the method of obtaining schema information (historical table structure data) related to historical query data mentioned above. The current user query input is a natural language (question), such as "What are the taxi methods in June?" The system needs to retrieve all schema information (vectorized table structure data) in the target database based on this question, and then filter out the schema information that is most similar to the current user query.
[0139] Step S104 shown in the embodiment of the present application can find the table structure data most relevant to the current user query question by performing a second similarity match between the current user query question and the vectorized table structure data, so that the corresponding table and field can be selected more accurately when generating SQL statements, thereby reducing SQL generation errors caused by misunderstanding of the table structure.
[0140] Step S105, concatenating the current user query question, the target historical query question pair and the target table structure data to obtain a target prompt;
[0141] The target prompt is a prompt project obtained by concatenating the current user query question, the target historical query question pair, and the target table structure data.
[0142] For example, the architecture of the target prompt is as follows:
[0143]
[0144]
[0145]
[0146] For the architecture example of the target prompt, the first part "###Task generates SQL to answer the user's question" is the input of the big model, that is, the specific task of the big model. The second part "###database schema" represents the relevant schema information of the current user's question. The third part "###question...sql..." represents the relevant historical [SQL-question] pairs of the current user's question.
[0147] Step S105 shown in the embodiment of the present application obtains a target prompt by splicing the current user query question, the target historical query question pair and the target table structure data. The target prompt not only includes the historical query data, but also incorporates the historical query question pairs and table structure information that are highly relevant to the historical queries. The fusion of multi-source information enables the large model to consider various factors more comprehensively when generating SQL, thereby improving the accuracy and applicability of the generated SQL statements.
[0148] Step S106, inputting the target prompt into the target macro model, so that the target macro model outputs the target SQL statement corresponding to the current user query question.
[0149] The target large model refers to a large machine learning model used to process a specific task (generating SQL statements in the embodiment of the present application). In the database query scenario, the target large model can receive natural language queries (such as user questions) and convert them into equivalent SQL query statements. In the embodiment of the present application, the target large model can be the encoder only architecture of Transformer. For example, qwen2-72b, llama3, etc.
[0150] Among them, the target SQL statement is the SQL query statement generated by the target large model according to the target prompt (actually the target input statement constructed by the target prompt). The SQL query statement is the model's understanding and interpretation of the user's query question. The SQL query statement can accurately retrieve the information the user needs from the database.
[0151] Exemplarily, an example of a target SQL statement returned by the target big model according to the input target prompt may be "```select T3.name, T3.price per kilometer, T2.app name, T1.number of cars from methods supported by each taxi APP as T1 join taxi APP as T2 join taxi method as T3 on methods supported by each taxi APP.appid == taxi APP.entry id and methods supported by each taxi APP.method id == taxi method.entry id```".
[0152] In some embodiments, step S106 may include: inputting a target prompt into a target big model; wherein the target prompt includes a target input statement; when the target input statement is input into the target big model, outputting a target SQL statement corresponding to the current user query question according to the target input statement through the target big model.
[0153] Among them, the target input statement is a key component in the target prompt, that is, in essence, the prompt project is first built to obtain the input statement of the target large model, and then the input statement in the target prompt is input into the target large model.
[0154] Step S106 shown in the embodiment of the present application, by inputting the target prompt into the target big model, so that the target big model outputs the target SQL statement corresponding to the current user query question, the target SQL statement can accurately reflect the user's current query intention, and realizes efficient conversion from natural language to SQL statement.
[0155] Steps S101 to S106 shown in the embodiment of the present application are performed by obtaining historical query data and vectorized table structure data, and obtaining historical table structure data corresponding to the historical query data based on the historical query data and the vectorized table structure data; then a historical query question pair is constructed based on the historical query data and the historical table structure data; then a first similarity match is performed between the current user query question and the historical query question pair to obtain a target historical query question pair corresponding to the current user query question; the current user query question is a natural language text; a second similarity match is performed between the current user query question and the vectorized table structure data to obtain the target table structure data corresponding to the current user query question; then the current user query question, the target historical query question pair and the target table structure data are concatenated to obtain a target prompt; finally, the target prompt is input into the target big model so that the target big model outputs a target SQL statement corresponding to the current user query question. The embodiment of the present application constructs historical query question pairs by using historical query data and corresponding historical table structure data, so that the large model can refer to and draw on similar query scenarios and table structures in the past when generating SQL statements, that is, learn query patterns in different scenarios, so as to more accurately understand the intention of the current user query question and generate SQL statements that match it; and, use similarity matching to quickly locate the historical query question pairs and table structure data that are most relevant to the current user query question, greatly reducing the search space and calculation amount of the model when generating SQL statements, and improving query efficiency; at the same time, by constructing a target prompt containing historical query question pairs and table structure data, it can guide the large model to generate more accurate SQL statements that are more in line with user intentions, greatly improving the model's ability to convert natural language into SQL statements.
[0156] In order to explain the principle of the technical solution of the present invention in detail, the overall process of the present invention is described below in combination with some specific embodiments. It is easy to understand that the following is an explanation of the technical principle of the present invention and cannot be regarded as a limitation of the present invention.
[0157] See also Figure 7 , Figure 7 is a flowchart of a method for generating a SQL statement provided in an embodiment of the present application; Figure 7 As shown, the overall process of generating SQL statements mainly includes seven steps, which are steps S1 to S7 below.
[0158] Step S1, integrating the original schema information in the relational database into table schema information, performing vectorization processing on the table schema information, and finally storing the vectorized table schema information in a target database that can be vectorized for retrieval and storage;
[0159] For example: first, extract the table name, table comment, field name and field comment from the original schema information, then concatenate the table name, table comment, field name and field comment into a string through a specific separator (such as ";"), then vectorize the concatenated string, and store the vectorized string data in a target database that can be vectorized for retrieval and storage.
[0160] Step S2, combines the user's historical query data and the table schema information related to the historical query data to construct a historical prompt, then inputs the historical prompt into the target big model, and the historical prompt guides the target big model to output the user's historical query questions (intentions), and finally obtains the historical [SQL-question] pairs, and stores the historical [SQL-question] pairs in the target database that can be vectorized for retrieval and storage.
[0161] Among them, step S2 can be divided into three parts. For details, please refer to Figure 8 , Figure 8 is a schematic diagram of a process for constructing a historical query question pair provided in an embodiment of the present application, such as Figure 8 As shown in Figure 1, the process of constructing a historical query question pair is as follows:
[0162] In the first part, the historical SQL statements in the user's historical query data are parsed through the SQL parsing module to obtain the table name and field name corresponding to the historical SQL statements.
[0163] The SQL parsing module is a python program that extracts table names and field names from historical SQL statements based on regular matching and rules.
[0164] In the second part, the meta-information query module of the target database searches for all schema information in the target database storing the table schema data according to the table name and field name parsed in step S1. By matching the similarity between the table name and the field name, the schema information most relevant to the user's historical query data can be obtained.
[0165] The meta-information query module is a module used to query relevant table creation statements in the target database according to the table name and field name.
[0166] In the third part, a historical prompt can be constructed based on the user's historical query data and the most relevant schema information of the user's historical query data, and then the historical prompt is input into the target big model. The target big model outputs the historical query intent corresponding to the user's historical query data based on the historical prompt. The historical query intent can be called a question. Finally, based on the user's historical query data, the most relevant schema information of the user's historical query data and the user's historical query intent, a series of [question-sql-schema] historical query question pairs can be obtained. The historical query question pairs are also called historical [SQL-question] pairs.
[0167] Step S3, searching for related questions of the user's current query data, and obtaining related historical [SQL-question] pairs of the user's current query data;
[0168] Specifically, the user's current query data is similarly matched with multiple historical query question pairs in the target database to find the question and corresponding SQL in the historical [SQL-question] pairs that are most relevant to the user's current query data, that is, to obtain the relevant historical [SQL-question] pairs of the user's current query data.
[0169] Step S4, searching the table schema information of the user's current query data to obtain the schema information related to the user's current query data.
[0170] Specifically, the user's current query data is similarly matched with multiple table schema information in the target database to find the schema information in the target database that is most relevant to the user's current query data, that is, to obtain the relevant schema information of the user's current query data.
[0171] Step S5, concatenate the user's current query data, the relevant history [SQL-question] pairs of the user's current query data obtained in step S3, and the schema information related to the user's current query data obtained in step S4 to construct a target prompt.
[0172] Step S6, input the target prompt constructed in step S5 into the target macro model.
[0173] Step S7, generating a target SQL statement according to the target prompt through the target large model.
[0174] The target prompt is used as a reference for the target large model to achieve a purpose similar to few-shot learning, and there is no strict requirement on the data format, thereby improving the effect of large models in processing NL2SQL tasks.
[0175] The method of the embodiment of the present application enhances the capability of a large natural language processing model (NL2SQL) by utilizing user historical query SQL data, without strictly annotating data or using GPU (Graphics Processing Unit) resources to fine-tune the large model. In addition, the method for generating SQL statements provided in the embodiment of the present application can adapt to different business scenarios, thereby improving the effect of large models in processing NL2SQL tasks.
[0176] It should be pointed out that this embodiment only briefly illustrates the general process of the SQL statement generation method. The detailed description of each step can refer to the relevant content in the aforementioned embodiment and will not be repeated here. It can be understood that the present invention is not limited to this.
[0177] The embodiment of the present application obtains historical query data and vectorized table structure data, and obtains historical table structure data corresponding to the historical query data based on the historical query data and the vectorized table structure data; then constructs a historical query question pair based on the historical query data and the historical table structure data; then performs a first similarity match on the current user query question and the historical query question pair to obtain a target historical query question pair corresponding to the current user query question; the current user query question is a natural language text; performs a second similarity match on the current user query question and the vectorized table structure data to obtain the target table structure data corresponding to the current user query question; then splices the current user query question, the target historical query question pair and the target table structure data to obtain a target prompt; finally, inputs the target prompt into the target big model, so that the target big model outputs a target SQL statement corresponding to the current user query question. The embodiment of the present application constructs historical query question pairs by using historical query data and corresponding historical table structure data, so that the large model can refer to and draw on similar query scenarios and table structures in the past when generating SQL statements, that is, learn query patterns in different scenarios, so as to more accurately understand the intention of the current user query question and generate SQL statements that match it; and, use similarity matching to quickly locate the historical query question pairs and table structure data that are most relevant to the current user query question, greatly reducing the search space and calculation amount of the model when generating SQL statements, and improving query efficiency; at the same time, by constructing a target prompt containing historical query question pairs and table structure data, it can guide the large model to generate more accurate SQL statements that are more in line with user intentions, greatly improving the model's ability to convert natural language into SQL statements.
[0178] The present application also provides a SQL statement generation device, which can implement the above SQL statement generation method. Fig. 9 , Fig. 9 Schematic diagram of the structure of a SQL statement generation device provided in an embodiment of the present application. Fig. 9 As shown, the device 900 includes the following modules:
[0179] The data acquisition module 901 is used to acquire historical query data and vectorized table structure data, and acquire historical table structure data corresponding to the historical query data based on the historical query data and the vectorized table structure data;
[0180] A historical query question pair construction module 902, configured to construct a historical query question pair based on the historical query data and the historical table structure data;
[0181] The target historical query question pair acquisition module 903 is used to perform a first similarity match between the current user query question and the historical query question pair to obtain a target historical query question pair corresponding to the current user query question; the current user query question is a natural language text;
[0182] The target table structure data acquisition module 904 is used to perform a second similarity matching between the current user query question and the vectorized table structure data to obtain the target table structure data corresponding to the current user query question;
[0183] A target prompt building module 905 is used to concatenate the current user query question, the target historical query question pair and the target table structure data to obtain a target prompt;
[0184] The target SQL statement output module 906 is used to input the target prompt into the target big model so that the target big model outputs the target SQL statement corresponding to the current user query question.
[0185] It can be understood that the contents of the above method embodiments are all applicable to the present device embodiments, the functions specifically implemented by the present device embodiments are the same as those of the above method embodiments, and the beneficial effects achieved are also the same as those achieved by the above method embodiments.
[0186] The embodiment of the present application also provides an electronic device, the electronic device includes a memory and a processor, the memory stores a computer program, and the processor implements the above SQL statement generation method when executing the computer program. The electronic device can be any intelligent terminal including a tablet computer, a car computer, etc.
[0187] It can be understood that the contents of the above method embodiments are all applicable to the present device embodiments, the functions specifically implemented by the present device embodiments are the same as those of the above method embodiments, and the beneficial effects achieved are also the same as those achieved by the above method embodiments.
[0188] See also Fig.10 , Fig.10 The hardware structure of an electronic device of another embodiment is illustrated, and the electronic device includes:
[0189] The processor 1001 may be implemented by a general-purpose CPU (Central Processing Unit), a microprocessor, an application-specific integrated circuit (Application Specific Integrated Circuit, ASIC for short), or one or more integrated circuits, and is used to execute relevant programs to implement the technical solutions provided in the embodiments of the present application;
[0190] The memory 1002 can be implemented in the form of a read-only memory (ROM), a static storage device, a dynamic storage device, or a random access memory (RAM). The memory 1002 can store an operating system and other application programs. When the technical solution provided in the embodiment of this specification is implemented by software or firmware, the relevant program code is stored in the memory 1002, and the processor 1001 calls and executes the SQL statement generation method of the embodiment of this application;
[0191] Input / output interface 1003, used to implement information input and output;
[0192] The communication interface 1004 is used to realize the communication interaction between the device and other devices. The communication can be realized through a wired manner (such as USB, network cable, etc.) or a wireless manner (such as mobile network, WIFI, Bluetooth, etc.);
[0193] A bus 1005 , which transmits information between various components of the device (e.g., the processor 1001 , the memory 1002 , the input / output interface 1003 , and the communication interface 1004 );
[0194] The processor 1001 , the memory 1002 , the input / output interface 1003 and the communication interface 1004 are connected to each other in communication within the device via the bus 1005 .
[0195] An embodiment of the present application further provides a computer-readable storage medium, which stores a computer program. When the computer program is executed by a processor, the above-mentioned SQL statement generation method is implemented.
[0196] It can be understood that the contents of the above method embodiments are all applicable to the present storage medium embodiments, the functions specifically implemented by the present storage medium embodiments are the same as those of the above method embodiments, and the beneficial effects achieved are also the same as those achieved by the above method embodiments.
[0197] The memory, as a non-transient computer-readable storage medium, can be used to store non-transient software programs and non-transient computer executable programs. In addition, the memory may include a high-speed random access memory, and may also include a non-transient memory, such as at least one disk storage device, a flash memory device, or other non-transient solid-state storage device. In some embodiments, the memory may optionally include a memory remotely disposed relative to the processor, and these remote memories may be connected to the processor via a network. Examples of the above-mentioned network include, but are not limited to, the Internet, an intranet, a local area network, a mobile communication network, and combinations thereof.
[0198] The SQL statement generation method, SQL statement generation device, electronic device and storage medium provided in the embodiments of the present application obtain historical query data and vectorized table structure data, and obtain historical table structure data corresponding to the historical query data based on the historical query data and the vectorized table structure data; then construct a historical query question pair based on the historical query data and the historical table structure data; then perform a first similarity match between the current user query question and the historical query question pair to obtain a target historical query question pair corresponding to the current user query question; the current user query question is a natural language text; perform a second similarity match between the current user query question and the vectorized table structure data to obtain the target table structure data corresponding to the current user query question; then splice the current user query question, the target historical query question pair and the target table structure data to obtain a target prompt; finally, input the target prompt into the target big model so that the target big model outputs the target SQL statement corresponding to the current user query question. The embodiment of the present application constructs historical query question pairs by using historical query data and corresponding historical table structure data, so that the large model can refer to and draw on similar query scenarios and table structures in the past when generating SQL statements, that is, learn query patterns in different scenarios, so as to more accurately understand the intention of the current user query question and generate SQL statements that match it; and, use similarity matching to quickly locate the historical query question pairs and table structure data that are most relevant to the current user query question, greatly reducing the search space and calculation amount of the model when generating SQL statements, and improving query efficiency; at the same time, by constructing a target prompt containing historical query question pairs and table structure data, it can guide the large model to generate more accurate SQL statements that are more in line with user intentions, greatly improving the model's ability to convert natural language into SQL statements.
[0199] The embodiments described in the embodiments of the present application are intended to more clearly illustrate the technical solutions of the embodiments of the present application and do not constitute a limitation on the technical solutions provided in the embodiments of the present application. Those skilled in the art will appreciate that with the evolution of technology and the emergence of new application scenarios, the technical solutions provided in the embodiments of the present application are also applicable to similar technical problems.
[0200] Those skilled in the art will appreciate that the technical solutions shown in the figures do not constitute a limitation on the embodiments of the present application, and may include more or fewer steps than shown in the figures, or a combination of certain steps, or different steps.
[0201] The device embodiments described above are merely illustrative, and the units described as separate components may or may not be physically separated, that is, they may be located in one place or distributed on multiple network units. Some or all of the modules may be selected according to actual needs to achieve the purpose of the solution of this embodiment.
[0202] Those skilled in the art will appreciate that all or some of the steps in the methods disclosed above, and the functional modules / units in the systems and devices may be implemented as software, firmware, hardware, or a suitable combination thereof.
[0203] The terms "first", "second", "third", "fourth", etc. (if any) in the specification of the present application and the above-mentioned drawings are used to distinguish similar objects, and are not necessarily used to describe a specific order or sequence. It should be understood that the data used in this way can be interchangeable where appropriate, so that the embodiments of the present application described herein can be implemented in an order other than those illustrated or described herein. In addition, the terms "including" and "having" and any of their variations are intended to cover non-exclusive inclusions, for example, a process, method, system, product or device comprising a series of steps or units is not necessarily limited to those steps or units clearly listed, but may include other steps or units that are not clearly listed or inherent to these processes, methods, products or devices.
[0204] It should be understood that in the present application, "at least one (item)" means one or more, and "plurality" means two or more. "And / or" is used to describe the association relationship of associated objects, indicating that three relationships may exist. For example, "A and / or B" can mean: only A exists, only B exists, and A and B exist at the same time, where A and B can be singular or plural. The character " / " generally indicates that the objects associated before and after are in an "or" relationship. "At least one of the following" or similar expressions refers to any combination of these items, including any combination of single or plural items. For example, at least one of a, b or c can mean: a, b, c, "a and b", "a and c", "b and c", or "a and b and c", where a, b, c can be single or multiple.
[0205] In the several embodiments provided in the present application, it should be understood that the disclosed devices and methods can be implemented in other ways. For example, the device embodiments described above are only schematic. For example, the division of the above 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 integrated into another system, or some features can be ignored or not executed. Another point is that the mutual coupling or direct coupling or communication connection shown or discussed can be through some interfaces, indirect coupling or communication connection of devices or units, which can be electrical, mechanical or other forms.
[0206] The units described above as separate components may or may not be physically separated, and the components shown as units may or may not be physical units, that is, they may be located in one place or distributed on multiple network units. Some or all of the units may be selected according to actual needs to achieve the purpose of the solution of this embodiment.
[0207] In addition, each functional unit in each embodiment of the present application may be integrated into one processing unit, or each unit may exist physically separately, or two or more units may be integrated into one unit. The above-mentioned integrated unit may be implemented in the form of hardware or in the form of software functional units.
[0208] If the integrated unit is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present application, or the part that contributes to the prior art, or all or part of the technical solution can be embodied in the form of a software product, and the computer software product is stored in a storage medium, including multiple instructions to enable a computer device (which can be a personal computer, server, or network device, etc.) to execute all or part of the steps of the methods of various embodiments of the present application. The aforementioned storage medium includes: U disk, mobile hard disk, read-only memory (Read-Only Memory, referred to as ROM), random access memory (Random Access Memory, referred to as RAM), disk or optical disk and other media that can store programs.
[0209] The preferred embodiments of the present invention are described above with reference to the accompanying drawings, but the scope of the rights of the present invention is not limited thereto. Any modification, equivalent substitution and improvement made by a person skilled in the art without departing from the scope and essence of the present invention should be within the scope of the rights of the present invention.
Claims
1. A method for generating a SQL statement, characterized in that: The method comprises the following steps: Acquire historical query data and vectorized table structure data, and acquire historical table structure data corresponding to the historical query data based on the historical query data and the vectorized table structure data; Constructing a historical query question pair based on the historical query data and the historical table structure data; Performing a first similarity match between the current user query question and the historical query question pair to obtain a target historical query question pair corresponding to the current user query question; the current user query question is a natural language text; Performing a second similarity match between the current user query question and the vectorized table structure data to obtain target table structure data corresponding to the current user query question; The current user query question, the target historical query question pair and the target table structure data are concatenated to obtain a target prompt; The target prompt is input into the target big model so that the target big model outputs the target SQL statement corresponding to the current user query question.
2. The method according to claim 1, characterized in that Before acquiring the historical query data and the vectorized table structure data, and acquiring the historical table structure data corresponding to the historical query data based on the historical query data and the vectorized table structure data, the method further includes: Obtain original table information in the original database; Integrate the original table information to obtain table structure data to be processed; Vectorization is performed on the table structure data to be processed to obtain the vectorized table structure data, and the vectorized table structure data is stored in a target database.
3. The method according to claim 1, characterized in that The acquiring the historical table structure data corresponding to the historical query data based on the historical query data and the vectorized table structure data includes: Parsing the historical SQL statements in the historical query data to obtain the table names corresponding to the historical SQL statements; A third similarity match is performed between the table name and the vectorized table structure data in the target database to obtain the historical table structure data corresponding to the historical SQL statement.
4. The method according to claim 1, characterized in that: The constructing of a historical query question pair based on the historical query data and the historical table structure data includes: Constructing a historical prompt based on the historical SQL statements in the historical query data and the historical table structure data; Input the historical prompt into the target big model, so that the target big model outputs the historical query purpose corresponding to the historical SQL statement; Determine the historical query question corresponding to the historical SQL statement according to the historical query purpose; The historical query question pair is constructed according to the historical SQL statement and the historical query question, and the historical query question pair is stored in the target database.
5. The method according to claim 1, characterized in that The first similarity matching of the current user query question with the historical query question pair to obtain a target historical query question pair corresponding to the current user query question includes: Retrieving a plurality of the historical query question pairs in the target database based on the first index, and respectively calculating a first similarity between each of the historical query question pairs in the target database and the current user query question, to obtain a first similarity ranking result; The target historical query question pair corresponding to the current user query question is obtained based on the first similarity ranking result.
6. The method according to claim 1, characterized in that The performing a second similarity matching between the current user query question and the vectorized table structure data to obtain the target table structure data corresponding to the current user query question includes: Retrieving a plurality of the vectorized table structure data in the target database based on the second index, and respectively calculating a second similarity between each of the vectorized table structure data in the target database and the current user query question, to obtain a second similarity ranking result; The target table structure data corresponding to the current user query question is obtained based on the second similarity ranking result.
7. The method according to claim 1, characterized in that The step of inputting the target prompt into the target macro model so that the target macro model outputs a target SQL statement corresponding to the current user query question includes: Inputting the target prompt into the target macro model; wherein the target prompt includes a target input sentence; When the target input statement is input into the target macro model, the target macro model outputs the target SQL statement corresponding to the current user query question according to the target input statement.
8. A SQL statement generating device, characterized in that: The device comprises the following modules: A data acquisition module, used to acquire historical query data and vectorized table structure data, and acquire historical table structure data corresponding to the historical query data based on the historical query data and the vectorized table structure data; A historical query question pair construction module, used to construct a historical query question pair based on the historical query data and the historical table structure data; A target historical query question pair acquisition module is used to perform a first similarity match between the current user query question and the historical query question pair to obtain a target historical query question pair corresponding to the current user query question; the current user query question is a natural language text; A target table structure data acquisition module is used to perform a second similarity match between the current user query question and the vectorized table structure data to obtain the target table structure data corresponding to the current user query question; A target prompt construction module is used to concatenate the current user query question, the target historical query question pair and the target table structure data to obtain a target prompt; The target SQL statement output module is used to input the target prompt into the target large model so that the target large model outputs the target SQL statement corresponding to the current user query question.
9. An electronic device, characterized in that: The electronic device comprises a memory and a processor, the memory stores a computer program, and the processor implements the method according to any one of claims 1 to 7 when executing the computer program.
10. A computer-readable storage medium storing a computer program, characterized in that: When the computer program is executed by a processor, the method according to any one of claims 1 to 7 is implemented.
Citation Information
Cited By
Method and device for determining data table, equipment, medium and product
CN119271805A
NL2SQL method, device and equipment based on preset industry large model and medium
CN119917524A
Query method based on retrieval enhancement generation, electronic equipment and equipment
CN119917531A
Data processing method, server, storage medium and program product
CN120144612A
SQL intelligent generation method and system for business query
CN120873005A
