Query optimization method and device used under Text-to-SQL (Structured Query Language) task, storage medium and electronic equipment

Abstract

The embodiment of the invention provides a query optimization method and device used under a Text-to-SQL task, a storage medium and electronic equipment, and relates to the technical field of query optimization. The method comprises the steps that a query optimization data set is created; designing an SQL generator model according to an encoder-decoder architecture, and generating initial SQL query data based on the SQL generator model and the query optimization data set; generating training data used for querying the rewriting task based on the initial SQL query data; training a query rewriting model by utilizing the training data; performing joint training based on the SQL generator model and the query rewriting model to obtain a query optimization model; and performing query optimization based on the query optimization model. According to the technical scheme, the accuracy and efficiency of query optimization are greatly improved.

Description

Technical Field

[0001] This application relates to the field of query optimization technology. Specifically, it relates to a query optimization method, device, storage medium, and electronic device for Text-to-SQL tasks. Background Art

[0002] The execution efficiency in a Text-to-SQL system refers to the speed and resource efficiency of executing the generated SQL queries in a database system. Despite substantial progress in improving generation accuracy, execution efficiency has received significantly less attention. Traditional query optimization techniques, such as cost-based optimization (Selinger et al., 1979) and rule-based optimization (Graefe, 1995), are tailored for predefined SQL queries, which limits their applicability to the dynamic and often complex queries generated by text-to-SQL systems. The challenge stems from the fact that text-to-SQL systems dynamically generate SQL queries with variable structures and complexities, rendering traditional optimization techniques ineffective.

[0003] To meet the growing demand for Text-to-SQL query optimization, some studies have proposed hybrid optimization methods that combine learning models with traditional query optimization techniques. Although this approach is promising, it is still limited to a narrow set of query structures and cannot fully account for the diversity of SQL queries generated by text-to-SQL systems.

[0004] Currently, the existing Text-to-SQL query optimization techniques have the following defects: The challenge of optimizing query execution for dynamic Text-to-SQL systems is further complicated by the need for real-time response and high scalability.

[0005] Existing evaluation benchmarks for Text-to-SQL systems, such as Spider (Yu et al., 2018), mainly focus on the accuracy of SQL generation. These datasets provide valuable resources for evaluating the semantic correctness of the generated SQL queries, but they cannot fully measure execution performance. Although there have been attempts to evaluate execution efficiency, such as through runtime testing of the generated SQL queries, these methods lack standardization and cannot provide a consistent framework for measuring and comparing the execution efficiency of different systems.

[0006] A significant defect in the existing literature is the lack of a dedicated benchmark to evaluate the execution efficiency of Text-to-SQL models. Most current evaluation frameworks do not consider execution performance, which limits the understanding of the scalability and performance of these systems in real database environments. Summary of the Invention

[0007] Embodiments of the present application provide a query optimization method, device, storage medium, and electronic device for Text-to-SQL tasks to solve the problems existing in the prior art and make up for the defects in the prior art.

[0008] Other features and advantages of the present application will become apparent through the following detailed description or be learned in part through the practice of the present application.

[0009] According to a first aspect of an embodiment of the present application, there is provided a query optimization method for Text-to-SQL tasks, including: Create a query optimization dataset; Design an SQL generator model according to the encoder-decoder architecture, and generate initial SQL query data based on the SQL generator model and the query optimization dataset; Generate training data for query rewriting tasks based on the initial SQL query data; Train a query rewriting model using the training data; Perform joint training based on the SQL generator model and the query rewriting model to obtain a query optimization model; Perform query optimization based on the query optimization model.

[0010] In some embodiments of the present application, based on the foregoing solution, the creating of the query optimization dataset includes: Create a query optimization dataset according to the Spider dataset, where the query optimization dataset includes: natural language questions, database schemas, SQL statements, optimal SQL statements, and estimated cardinalities; And rewrite the SQL statements in the query optimization dataset according to the query rewriting method, and supplement the data in the query optimization dataset by means of algorithm annotation.

[0011] In some embodiments of the present application, based on the foregoing solution, the generating of the initial SQL query data based on the SQL generator model and the query optimization dataset includes: Use the encoder in the SQL generator model to generate a latent representation from the natural language question and the database schema; Generate initial SQL query data based on the latent representation using the decoder in the SQL generator model.

[0012] In some embodiments of the present application, based on the foregoing solution, the generating of the training data for query rewriting tasks based on the initial SQL query data includes: Generate candidate query data with different execution times by controlling and modifying the initial SQL query data; The candidate query data is divided into a good query queue and a bad query queue according to the execution time length, and used as the training data.

[0013] In some embodiments of the present application, based on the foregoing solution, the loss function of the SQL generator model is defined as follows: ; (1) Where represents the loss function of the SQL generator model, represents the token sequence generated before time t, represents the parameters of the SQL generator model, represents the natural language question, represents the database schema, represents the token sequence generated at time t, and T represents the number of all time moments.

[0014] In some embodiments of the present application, based on the foregoing solution, the loss function of the query rewriting model is defined as follows: ; (2) Where represents the loss function of the query rewriting model, represents the execution time, represents the candidate query data with a relatively short execution time in the good and bad query queues, represents the candidate query data with a relatively long execution time in the good and bad query queues, is an interval parameter, represents the candidate query data, represents the total amount of candidate query data.

[0015] In some embodiments of the present application, based on the foregoing solution, during the joint training process, the loss function of the query optimization model is determined based on the loss function of the SQL generator model and the loss function of the query rewriting model, and the total optimization objective is determined according to the loss function of the query optimization model; Among them, the loss function of the query optimization model is as follows: ; (3) Where represents the loss function of the query optimization model, represents the hyperparameter.

[0016] According to the second aspect of the embodiments of the present application, there is provided a query optimization device for the Text-to-SQL task, including: A creation unit, configured to create a query optimization data set; A design unit for designing an SQL generator model according to the encoder-decoder architecture; A first generation unit for generating initial SQL query data based on the SQL generator model and the query optimization data set; A second generation unit for generating training data for use in query rewriting tasks based on the initial SQL query data; A first training unit for training a query rewriting model using the training data; A second training unit for jointly training based on the SQL generator model and the query rewriting model to obtain a query optimization model; A query optimization unit for query optimization based on the query optimization model.

[0017] According to the third aspect of the embodiments of the present application, there is provided a computer-readable storage medium storing computer instructions, which when run on a computer, cause the computer to execute the method described in the first aspect.

[0018] According to the fourth aspect of the embodiments of the present application, there is provided an electronic device including: a memory and a processor; The memory for storing computer instructions; The processor for calling the computer instructions stored in the memory, causing the electronic device to execute the method described in the first aspect.

[0019] The technical solution of the present application first obtains an SQL generator model and a query rewriting model through training, then obtains a query optimization model through jointly training the SQL generator model and the query rewriting model, and finally uses the query optimization model for query optimization. Tests show that the technical solution of the present application greatly improves the accuracy and efficiency of query optimization.

[0020] It should be understood that the above general description and the following detailed description are only exemplary and explanatory, and cannot limit the present application. BRIEF DESCRIPTION OF THE DRAWINGS

[0021] The drawings herein are incorporated into the specification and constitute a part of this specification, showing embodiments consistent with the present application and used together with the specification to explain the principles of the present application. Obviously, the drawings in the following description are only some embodiments of the present application, and those of ordinary skill in the art can obtain other drawings based on these drawings without creative efforts. In the drawings: Figure 1 Shows a schematic flowchart of a query optimization method for a Text-to-SQL task according to an embodiment of the present application; Figure 2 The block diagram of a query optimization device for the Text-to-SQL task according to an embodiment of the present application is shown; Figure 3 The block diagram of an electronic device according to an embodiment of the present application is shown; Figure 4 The schematic structural diagram of a computer system of an electronic device suitable for implementing the embodiments of the present application is shown; Figure 5 The sample diagram of a query optimization data set for the Text-to-SQL task according to an embodiment of the present application is shown; Figure 6 The schematic diagram of the effect of a query optimization method for the Text-to-SQL task according to an embodiment of the present application is shown. Detailed implementation manners

[0022] Example embodiments will now be described more fully with reference to the accompanying drawings. However, the example embodiments can be implemented in various forms and should not be construed as limited to the examples set forth herein; rather, these embodiments are provided so that this application will be more complete and comprehensive, and will fully convey the concept of the example embodiments to those skilled in the art.

[0023] In addition, the described features, structures, or characteristics can be combined in any suitable manner in one or more embodiments. In the following description, numerous specific details are provided to give a thorough understanding of the embodiments of the present application. However, those skilled in the art will realize that the technical solutions of the present application can be practiced without one or more of the specific details, or other methods, components, devices, steps, etc. can be adopted. In other cases, well-known methods, devices, implementations, or operations are not shown or described in detail to avoid obscuring aspects of the present application.

[0024] The block diagrams shown in the accompanying drawings are merely functional entities and do not necessarily correspond to physically independent entities. That is, these functional entities can be implemented in software form, or in one or more hardware modules or integrated circuits, or in different networks and / or processor devices and / or microcontroller devices.

[0025] The flowcharts shown in the accompanying drawings are merely illustrative and do not necessarily include all the content and operations / steps, nor do they necessarily have to be executed in the described order. For example, some operations / steps can be decomposed, while some operations / steps can be combined or partially combined, so the actual execution order may change according to the actual situation.

[0026] It should be noted that the terms "first", "second", etc. in the specification, claims and the above-mentioned drawings of this application are used to distinguish similar objects, and do not necessarily describe a specific order or sequence. It should be understood that the objects so used can be interchanged under appropriate circumstances, so that the embodiments of the present application described herein can be implemented in an order other than those illustrated or described.

[0027] To make the objectives, technical solutions and advantages of the present invention clearer, the technical solutions in the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings in the embodiments of the present invention. Apparently, the described embodiments are only some of the embodiments of the present invention, rather than all of the embodiments. All other embodiments obtained by those of ordinary skill in the art based on the embodiments of the present invention without creative efforts shall fall within the scope of protection of the present invention.

[0028] The following will describe in detail some embodiments of the present application with reference to the accompanying drawings. Without conflict, the following embodiments and the features in the embodiments can be combined with each other.

[0029] See Figure 1 , which shows a schematic flow diagram of a query optimization method for the Text-to-SQL task according to an embodiment of the present application.

[0030] As Figure 1 shown, a query optimization method for the Text-to-SQL task is presented, specifically including steps S100 to S700.

[0031] Refer to Figure 1 , step S100, create a query optimization dataset.

[0032] In some feasible embodiments, based on the foregoing solution, step S100 includes: Create a query optimization dataset according to the Spider dataset. The query optimization dataset includes: natural language questions, database schemas, SQL statements, optimal SQL statements, and estimated cardinalities; And rewrite the SQL statements in the query optimization dataset according to the query rewriting method, and supplement the data in the query optimization dataset by means of algorithm annotation.

[0033] Exemplarily, the query optimization dataset can be represented as the following tuple:

[0034] Among them, is the dataset, is the natural language question, is the database schema, is an SQL statement, is the optimal SQL statement, is the estimated cardinality; The completed query optimization dataset sample is as Figure 5 shown.

[0035] Figure 5 What is presented is the completed query optimization dataset sample, which is organized in the form of a JSON array, and each object in the array corresponds to a query optimization instance. It contains the db_id to identify the database name, such as real_estate_properties in the example; the question expresses the query requirement in natural language, like asking for the property names of houses or apartments with more than 1 room; the syn_question may be a synonymous expression of the question; the spider_query is the SQL statement written based on the question, using the UNION operator to filter relevant property names from the Properties table; the optimized_query should show the optimized SQL, which is the same as the spider_query in this example; the query is another form of SQL expression, which is also the same as the spider_query in this example. The entire dataset sample provides a structured data basis for query optimization research.

[0036] It can be understood that the purpose of rewriting the SQL statements in the query optimization dataset according to the query rewriting method is to optimize the data cardinality to achieve the optimal execution efficiency.

[0037] It can be understood that the purpose of supplementing the data in the query optimization dataset by means of algorithm annotation is to make the data reach the optimal execution efficiency.

[0038] It should be noted that in this embodiment, the specific process of supplementing the data in the query optimization dataset by means of algorithm annotation is as follows: First, use x to generate a question-SQL statement pair; then, use the y algorithm to perform query rewriting on the generated SQL statement.

[0039] It can be understood that the existing query optimization datasets only generate SQL statements and do not consider the execution efficiency of the generated SQL. In this embodiment, an innovative query optimization dataset is proposed, which adds the optimal SQL and the estimated cardinality on the basis of the existing dataset.

[0040] Continue to refer to Figure 1 , step S200, design a SQL generator model according to the encoder-decoder architecture.

[0041] It can be understood that the SQL generator model generated in this step can convert natural language into SQL language.

[0042] Exemplarily, the SQL generator model is as follows: ; Among them, represents the parameters of the generator, represents the token sequence generated before time t, represents the token sequence generated at time t, represents the number of all time instants.

[0043] In some feasible embodiments, based on the foregoing solution, the loss function of the SQL generator model is defined as follows: ; Among them, represents the loss function of the SQL generator model, represents the token sequence generated before time t, represents the parameters of the SQL generator model, represents the natural language question, represents the database schema, represents the token sequence generated at time t.

[0044] It can be understood that this loss ensures that the generated SQL is syntactically valid and semantically aligned with the input query.

[0045] Continue to refer to Figure 1 , step S300, generate initial SQL query data based on the SQL generator model and the query optimization dataset.

[0046] In some feasible embodiments, based on the foregoing solution, the step S300 includes: Use the encoder in the SQL generator model to generate a latent representation from the natural language question and the database schema; Generate initial SQL query data based on the latent representation using the decoder in the SQL generator model.

[0047] Continue to refer to Figure 1 , step S400, generate training data for use in the query rewriting task based on the initial SQL query data.

[0048] In some feasible embodiments, based on the foregoing solution, generating the training data for use in the query rewriting task based on the initial SQL query data includes: Generate candidate query data with different execution times by controlling and modifying the initial SQL query data; The candidate query data are divided into good and bad query queues according to the execution time length, and used as the training data.

[0049] Exemplarily, for the generated initial SQL query data , a set of candidate query data with different execution times is generated by controlling the modification ; then the candidate query data with shorter execution time is denoted as , and the candidate query data with longer execution time is denoted as , thus creating a set of good and bad query pairs .

[0050] In addition, during this process, a transformation function is also defined , which generates a perturbed query from the original query : ; where represents the perturbed query, represents the original query, represents the perturbation parameter. The difference in execution time is:

[0051] where represents the perturbed query, represents the original query, if , then the perturbed query is considered more efficient and regarded as a positive example; otherwise, it is regarded as a negative example.

[0052] Continue to refer to Figure 1 , step S500, and train the query rewriting model using the training data.

[0053] It can be understood that the role of the query rewriting model is to optimize the initial SQL query data to improve its execution efficiency.

[0054] It should be noted that the query rewriting model will learn a transformation function , which can map the initial SQL query data to the optimized version: ; where represents the optimized query, represents the original query, is the learnable parameter of the query rewriting model, represents the query optimization transformation function. Its goal is to minimize the execution time of the optimized query , while maintaining semantic equivalence with the original query.

[0055] In some feasible embodiments, based on the foregoing solution, the loss function of the query rewriting model is defined as follows: ; Wherein, represents the loss function of the query rewriting model, represents the execution time, represents the candidate query data with a relatively short execution time in the good and bad query team, represents the candidate query data with a relatively long execution time in the good and bad query team, is an interval parameter, > 0, which is used to enforce that there is at least a delta gap in the execution time between the optimized query and the non-optimized query. This loss encourages the query rewriting model to generate queries with significantly reduced execution time; represents the candidate query data, represents the total amount of candidate query data.

[0056] Continuing to refer to Figure 1 , step S600, based on the SQL generator model and the query rewriting model, joint training is performed to obtain a query optimization model.

[0057] In some feasible embodiments, based on the foregoing solution, during the joint training process, based on the loss function of the SQL generator model and the loss function of the query rewriting model, the loss function of the query optimization model is determined, and the total optimization target is determined according to the loss function of the query optimization model; Wherein, the loss function of the query optimization model is as follows: ; Wherein, represents the loss function of the query optimization model, represents a hyperparameter.

[0058] Hyperparameter is used to balance the trade-off between the semantic accuracy of SQL generation and the improvement in execution efficiency brought by query rewriting.

[0059] In this embodiment, the total optimization target of the query optimization model is as follows:

[0060] Wherein, regarding the gradient calculation is: ; Because the ranking loss does not depend on .

[0061] Similarly, the query rewriting model parameter The gradient of is: ; For each query pair, ; where is the indicator function.

[0062] Continuing to refer to Figure 1 , step S700, query optimization is performed based on the query optimization model.

[0063] Next, it is theoretically proven that the performance and execution efficiency of the query optimization model provided by this application meet the condition of being simultaneously optimal.

[0064] Suppose the SQL generator model and the query rewrite model satisfy the following conditions: (i) The encoder-decoder model used in the SQL generator is trained to ensure semantic equivalence between the generated SQL and the natural language query; (ii) The transformation function adopted by the query rewrite model preserves the semantics of the input SQL query; (iii) The execution time function is Lipschitz continuous; (iv) The interval parameter is reasonably set. Then, minimizing the total loss: ; ensures that the optimized query satisfies: ; while maintaining semantic equivalence, that is, .

[0065] where, represents the optimized query and represents the original query.

[0066] In addition, under the standard assumptions of gradient optimization, the proposed framework will converge to the local minimum, thus providing a principled guarantee to ensure the improvement of query execution efficiency without compromising semantic correctness.

[0067] Proof: The proof comes from the construction of the ranking loss which explicitly penalizes any candidate rewrite that fails to reduce the execution time by at least . Since the SQL generator optimizes semantic correctness through and the query rewrite function is restricted to preserving semantics, therefore, any reduction in the total loss will necessarily result in . Then, the standard gradient descent convergence result shows that, on the premise of the Lipschitz continuity of and the differentiability of the loss component, the optimization will converge to a local minimum where these properties are maintained. Proven.

[0068] The query optimization method using the technical solution of this application has the effects as Figure 6 shown. Figure 6 It focuses on showing the effects of the query optimization method using the technical solution of this application, presented through the comparison of specific problems, the original SQL, and the optimized SQL. For example, for the problem of querying singer information and sorting by age, the optimized SQL adds a LIMIT 100 to limit the number of result rows to improve performance; for the problem of querying the countries of origin of singers over 20 years old, the optimization method removes the DISTINCT keyword to reduce performance overhead; for the problem of querying the names of stadiums that have not held concerts, the NOT IN subquery is replaced with NOT EXISTS because in some database systems, NOT EXISTS has better performance when the subquery result set is large. These comparisons intuitively reflect the role of the optimization method in different query scenarios.

[0069] In summary, this application has the following advantages: (1) New problem definition: Officially defines the Text-to-SQL query optimization problem, emphasizing its unique challenges, differences from the classic Text-to-SQL task, and relationship with traditional database optimization.

[0070] (2) New dataset: Presents the query optimization dataset Spider-CE, which is a new dataset tailored for evaluating the execution efficiency of Text-to-SQL models. The query optimization dataset Spider-CE enhances the annotations and performance metrics on the basis of the widely used Spider dataset, and is specifically used to measure query efficiency.

[0071] (3) New framework: Proposes a novel multi-task learning framework that jointly optimizes SQL generation and query execution. The framework includes an SQL generator and a query rewriting model. Extensive experiments on five benchmark tests show that the framework has achieved state-of-the-art performance in terms of both accuracy and efficiency.

[0072] The following introduces the device embodiments of this application, which can be used to execute a query optimization method for the Text-to-SQL task in the above embodiments of this application. For the details not disclosed in the device embodiments of this application, please refer to the method embodiments of this application above.

[0073] Refer to Figure 2As shown, a query optimization device 200 according to an embodiment of the present application for the Text-to-SQL task includes: A creation unit 201 for creating a query optimization data set; A design unit 202 for designing an SQL generator model according to the encoder-decoder architecture; A first generation unit 203 for generating initial SQL query data based on the SQL generator model and the query optimization data set; A second generation unit 204 for generating training data for query rewriting tasks based on the initial SQL query data; A first training unit 205 for training a query rewriting model using the training data; A second training unit 206 for jointly training based on the SQL generator model and the query rewriting model to obtain a query optimization model; A query optimization unit 207 for query optimization based on the query optimization model.

[0074] As Figure 3 shown, an embodiment of the present application also provides an electronic device 300, including a memory 310, a processor 320, and a computer program 311 stored on the memory 310 and executable on the processor. When the processor 320 executes the computer program 311, the steps of the above-mentioned query optimization method for the Text-to-SQL task are implemented.

[0075] Since the electronic device introduced in this embodiment is the device used to implement a query optimization device for the Text-to-SQL task in an embodiment of the present application, based on the method introduced in the embodiment of the present application, those skilled in the art can understand the specific implementation manners and various variations of the electronic device in this embodiment. Therefore, the specific implementation of how this electronic device implements the method in the embodiment of the present application will not be described in detail here. As long as it is the device used by those skilled in the art to implement the method in the embodiment of the present application, it falls within the scope of protection of the present application.

[0076] In the specific implementation process, when the computer program 311 is executed by the processor, it can implement any implementation manner in the corresponding embodiment of the first aspect.

[0077] Figure 4 Shows a schematic structural diagram of a computer system of an electronic device suitable for implementing an embodiment of the present application.

[0078] It should be noted that Figure 4 the shown computer system 400 of the electronic device is only an example and should not bring any limitations to the functions and usage scopes of the embodiments of the present application.

[0079] As Figure 4 shown, computer system 400 includes a central processing unit (CPU) 401, which can perform various appropriate actions and processes according to a program stored in a read-only memory (ROM) 402 or a program loaded from a storage section 408 into a random access memory (RAM) 403, such as executing the methods described in the above embodiments. In the RAM 403, various programs and data required for system operations are also stored. The CPU 401, ROM 402, and RAM 403 are connected to each other via a bus 404. An input / output (I / O) interface 405 is also connected to the bus 404.

[0080] The following components are connected to the I / O interface 405: an input section 406 including a keyboard, a mouse, etc.; an output section 407 including, for example, a cathode ray tube (CRT), a liquid crystal display (LCD), etc. and a speaker, etc.; a storage section 408 including a hard disk, etc.; and a communication section 409 including a network interface card such as a LAN (Local Area Network) card, a modem, etc. The communication section 409 performs communication processing via a network such as the Internet. A drive 410 is also connected to the I / O interface 405 as needed. A removable medium 411, such as a magnetic disk, an optical disk, a magneto-optical disk, a semiconductor memory, etc., is installed on the drive 410 as needed so that a computer program read from it can be installed into the storage section 408 as needed.

[0081] Specifically, according to an embodiment of the present application, the process described above with reference to the flowchart can be implemented as a computer software program. For example, an embodiment of the present application includes a computer program product, which includes a computer program carried on a computer-readable medium, and the computer program includes program code for executing the method shown in the flowchart. In such an embodiment, the computer program can be downloaded and installed from a network via the communication section 409, and / or installed from the removable medium 411. When the computer program is executed by a central processing unit (CPU) 401, various functions defined in the system of the present application are executed.

[0082] It should be noted that the computer-readable medium shown in the embodiments of the present application can be a computer-readable signal medium, a computer-readable storage medium, or any combination of the two. The computer-readable storage medium can be, for example, but not limited to, an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any combination of the above. More specific examples of the computer-readable storage medium can include, but are not limited to: an electrical connection with one or more wires, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM), a flash memory, an optical fiber, a portable compact disc read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the above. In the present application, the computer-readable storage medium can be any tangible medium that contains or stores a program, and this program can be used by or in combination with an instruction execution system, apparatus, or device. In the present application, the computer-readable signal medium can include a data signal propagated in a baseband or as part of a carrier wave, which carries the computer-readable program code. Such a propagated data signal can take various forms, including but not limited to electromagnetic signals, optical signals, or any suitable combination of the above. The computer-readable signal medium can also be any computer-readable medium other than the computer-readable storage medium, and this computer-readable medium can send, propagate, or transmit a program for use by or in combination with an instruction execution system, apparatus, or device. The program code contained on the computer-readable medium can be transmitted by any appropriate medium, including but not limited to: wireless, wired, etc., or any suitable combination of the above.

[0083] The flowcharts and block diagrams in the accompanying drawings illustrate the possible architectures, functions, and operations of systems, methods, and computer program products according to various embodiments of the present application. Among them, each block in the flowchart or block diagram can represent a module, a program segment, or a part of the code, and the above module, program segment, or part of the code contains one or more executable instructions for implementing the specified logical function. It should also be noted that in some alternative implementations, the functions marked in the blocks may occur in a different order than that marked in the accompanying drawings. For example, two consecutive blocks shown may actually be executed substantially in parallel, and they may sometimes be executed in the reverse order, depending on the functions involved. It should also be noted that each block in the block diagram or flowchart, and the combination of blocks in the block diagram or flowchart, can be implemented by a dedicated hardware-based system for performing the specified functions or operations, or can be implemented by a combination of dedicated hardware and computer instructions.

[0084] The units involved in the embodiments of the present application can be implemented in software or in hardware. The described units can also be provided in a processor. In some cases, the names of these units do not constitute a limitation on the units themselves.

[0085] As another aspect, the present application also provides a computer program product or a computer program. The computer program product or the computer program includes computer instructions stored in a computer-readable storage medium. The processor of the computer device reads the computer instructions from the computer-readable storage medium, and the processor executes the computer instructions, so that the computer device executes a query optimization method for the Text-to-SQL task described in the above embodiments.

[0086] As another aspect, the present application also provides a computer-readable medium, which may be included in the electronic device described in the above embodiments; or may exist separately without being assembled into the electronic device. The above computer-readable medium carries one or more programs. When the one or more programs are executed by an electronic device, the electronic device implements a query optimization method for the Text-to-SQL task described in the above embodiments.

[0087] It should be noted that although several modules or units of a device for action execution are mentioned in the above detailed description, such a division is not mandatory. In fact, according to the embodiments of the present application, the features and functions of two or more of the above-mentioned modules or units can be embodied in one module or unit. Conversely, the features and functions of one module or unit described above can be further divided and embodied by multiple modules or units.

[0088] Through the description of the above embodiments, those skilled in the art can easily understand that the example embodiments described herein can be implemented in software or in a manner combining software with necessary hardware. Therefore, the technical solutions according to the embodiments of the present application can be embodied in the form of a software product, which can be stored in a non-volatile storage medium (such as a CD-ROM, a USB flash drive, a mobile hard disk, etc.) or on a network, including several instructions to enable a computing device (such as a personal computer, a server, a touch terminal, or a network device, etc.) to execute the method according to the embodiments of the present application.

[0089] Those skilled in the art will readily conceive of other embodiments of the present application after considering the specification and practicing the embodiments disclosed herein. The present application is intended to cover any variations, uses, or adaptations of the present application, which follow the general principles of the present application and include the common general knowledge or conventional technical means in the technical field not disclosed in the present application. It should be understood that the present application is not limited to the exact structures described above and shown in the drawings, and various modifications and changes can be made without departing from its scope. The scope of the present application is only limited by the appended claims.

Claims

1. A query optimization method for the Text-to-SQL task, characterized in that, Including: Create a query optimization dataset; Design an SQL generator model according to the encoder-decoder architecture, and generate initial SQL query data based on the SQL generator model and the query optimization dataset; Generate training data for query rewriting tasks based on the initial SQL query data; Train a query rewriting model using the training data; Conduct joint training based on the SQL generator model and the query rewriting model to obtain a query optimization model; Perform query optimization based on the query optimization model.

2. The method according to claim 1, wherein The creation of the query optimization dataset includes: Create a query optimization dataset according to the Spider dataset, where the query optimization dataset includes: natural language questions, database schemas, SQL statements, optimal SQL statements, and estimated cardinalities; And rewrite the SQL statements in the query optimization dataset according to the query rewriting method, and supplement the data in the query optimization dataset by means of algorithm annotation.

3. The method according to claim 2, characterized in that, The generation of initial SQL query data based on the SQL generator model and the query optimization dataset includes: Use the encoder in the SQL generator model to generate a latent representation from the natural language question and the database schema; Generate initial SQL query data based on the latent representation using the decoder in the SQL generator model.

4. The method according to claim 3, characterized in that, The generation of training data for query rewriting tasks based on the initial SQL query data includes: Generate candidate query data with different execution times by controlling and modifying the initial SQL query data; Classify the candidate query data into good and bad query teams according to the execution time length as the training data.

5. The method according to claim 1, wherein The loss function of the SQL generator model is defined as follows: ；（1） Among them, represents the loss function of the SQL generator model, represents the token sequence generated before time t, represents the parameters of the SQL generator model, represents the natural language question, represents the database schema, represents the token sequence generated at time t, and T represents the number of all time instances.

6. The method according to claim 5, wherein The loss function of the query rewriting model is defined as follows: ；（2） Among them, represents the loss function of the query rewrite model, represents the execution time, represents the candidate query data with relatively short execution time in the good and bad query queue, represents the candidate query data with relatively long execution time in the good and bad query queue, is the interval parameter, represents the candidate query data, represents the total amount of the candidate query data.

7. The method according to claim 6, characterized in that, During the joint training process, determine the loss function of the query optimization model based on the loss function of the SQL generator model and the loss function of the query rewriting model, and determine the total optimization objective according to the loss function of the query optimization model; Among them, the loss function of the query optimization model is as follows: ；（3） Among them, represents the loss function of the query optimization model, represents hyperparameters.

8. A query optimization device for the Text-to-SQL task, characterized in that, Including: A creation unit for creating a query optimization dataset; A design unit for designing an SQL generator model according to the encoder-decoder architecture; A first generation unit for generating initial SQL query data based on the SQL generator model and the query optimization dataset; A second generation unit for generating training data for query rewriting tasks based on the initial SQL query data; A first training unit for training a query rewriting model using the training data; A second training unit for conducting joint training based on the SQL generator model and the query rewriting model to obtain a query optimization model; A query optimization unit for performing query optimization based on the query optimization model.

9. A computer-readable storage medium, characterized in that, The computer instructions are stored in the storage medium, and when the computer instructions run on the computer, the computer executes the method according to any one of claims 1-7.

10. An electronic device, characterized in that, Including: A memory and a processor; The memory is used to store computer instructions; The processor is configured to call the computer instructions stored in the memory, so that the electronic device executes the method according to any one of claims 1-7.

Images