A query optimization method, device, storage medium and electronic device for Text-to-SQL tasks

Abstract

Embodiments of the present application provide a query optimization method, apparatus, storage medium, and electronic device for Text-to-SQL tasks, relating to the field of query optimization technology. The method comprises: creating a query optimization dataset; designing an SQL generator model based on an encoder-decoder architecture, and generating initial SQL query data based on the SQL generator model and the query optimization dataset; generating training data for query rewriting tasks based on the initial SQL query data; training a query rewriting model using the training data; jointly training 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. The technical solution of the present application significantly improves the accuracy and efficiency of query optimization.

Description

Technical Field

[0001] The present application relates to the technical field of query optimization, and in particular to a query optimization method, device, storage medium, and electronic device for Text-to-SQL tasks. Background Art

[0002] Execution efficiency in text-to-SQL systems refers to the speed and resource efficiency of executing 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 arises from the fact that text-to-SQL systems dynamically generate SQL queries with variable structure and complexity, which renders traditional optimization techniques ineffective.

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

[0004] Currently, existing Text-to-SQL query optimization technologies have the following drawbacks:

[0005] 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.

[0006] Existing evaluation benchmarks for text-to-SQL systems, such as Spider (Yu et al. 2018), primarily focus on SQL generation accuracy. These datasets provide a valuable resource for evaluating the semantic correctness of generated SQL queries, but they do not adequately measure execution performance. While some have attempted to assess execution efficiency, such as through runtime testing of generated SQL queries, these methods lack standardization and fail to provide a consistent framework for measuring and comparing the execution efficiency of different systems.

[0007] A significant shortcoming in the existing literature is the lack of dedicated benchmarks to evaluate the execution efficiency of text-to-SQL models. Most current evaluation frameworks do not consider execution performance, which limits our understanding of the scalability and performance of these systems in real-world database environments. Summary of the Invention

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

[0009] Other features and advantages of the present application will become apparent from the following detailed description, or may be learned in part by practice of the present application.

[0010] According to a first aspect of an embodiment of the present application, a query optimization method for a Text-to-SQL task is provided, comprising:

[0011] Create query optimization dataset;

[0012] 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 dataset;

[0013] Generate training data for query rewriting tasks based on initial SQL query data;

[0014] Training a query rewriting model using the training data;

[0015] Perform joint training based on the SQL generator model and the query rewriting model to obtain a query optimization model;

[0016] Query optimization is performed based on the query optimization model.

[0017] In some embodiments of the present application, based on the aforementioned solution, the step of creating a query optimization dataset includes:

[0018] Creating a query optimization dataset based on the Spider dataset, wherein the query optimization dataset includes: natural language questions, database schemas, SQL statements, optimal SQL statements, and estimated cardinality;

[0019] Furthermore, the SQL statements in the query optimization data set are rewritten according to a query rewriting method, and the data in the query optimization data set are supplemented by using an algorithm annotation method.

[0020] In some embodiments of the present application, based on the aforementioned solution, generating initial SQL query data based on the SQL generator model and the query optimization dataset includes:

[0021] Generate latent representations from natural language questions and database schemas using an encoder in the SQL generator model;

[0022] Initial SQL query data is generated using a decoder in the SQL generator model based on the latent representation.

[0023] In some embodiments of the present application, based on the aforementioned solution, generating training data for the query rewriting task based on the initial SQL query data includes:

[0024] Generating candidate query data with different execution times by controlling and modifying the initial SQL query data;

[0025] The candidate query data are divided into good and bad query teams according to the execution time, and used as the training data.

[0026] In some embodiments of the present application, based on the aforementioned solution, the loss function of the SQL generator model is defined as follows:

[0027] ; (1)

[0028] in, represents the loss function of the SQL generator model, represents the tag sequence generated before time t, Represents the parameters of the SQL generator model, Representing natural language questions, Represents the database schema, represents the tag sequence generated at time t, and T represents the number of all moments.

[0029] In some embodiments of the present application, based on the aforementioned solution, the loss function of the query rewriting model is defined as follows:

[0030] ; (2)

[0031] in, represents the loss function of the query rewriting model, Indicates the execution time, Indicates the candidate query data with relatively short execution time in the good and bad query teams. Indicates the candidate query data with relatively long execution time in the good and bad query teams. is the interval parameter, Represents candidate query data, Indicates the total amount of candidate query data.

[0032] In some embodiments of the present application, based on the aforementioned 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 overall optimization objective is determined according to the loss function of the query optimization model;

[0033] Among them, the loss function of the query optimization model is as follows:

[0034] ; (3)

[0035] in, represents the loss function of the query optimization model, represents a hyperparameter.

[0036] According to a second aspect of an embodiment of the present application, a query optimization device for a Text-to-SQL task is provided, comprising:

[0037] A creation unit for creating a query optimization dataset;

[0038] A design unit, used to design an SQL generator model based on the encoder-decoder architecture;

[0039] A first generating unit, configured to generate initial SQL query data based on the SQL generator model and the query optimization dataset;

[0040] A second generating unit, configured to generate training data for a query rewriting task based on the initial SQL query data;

[0041] A first training unit, configured to train a query rewriting model using the training data;

[0042] A second training unit is configured to perform joint training based on the SQL generator model and the query rewriting model to obtain a query optimization model;

[0043] A query optimization unit is used to perform query optimization based on the query optimization model.

[0044] According to a third aspect of an embodiment of the present application, a computer-readable storage medium is provided, wherein the storage medium stores computer instructions. When the computer instructions are executed on a computer, the computer executes the method according to the first aspect.

[0045] According to a fourth aspect of the embodiments of the present application, there is provided an electronic device, including: a memory and a processor;

[0046] The memory is used to store computer instructions;

[0047] The processor is configured to call the computer instructions stored in the memory so that the electronic device executes the method according to the first aspect.

[0048] 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 by jointly training the SQL generator model and the query rewriting model, and finally uses the query optimization model to perform query optimization. Tests have shown that the technical solution of the present application greatly improves the accuracy and efficiency of query optimization.

[0049] It should be understood that the foregoing general description and the following detailed description are exemplary and explanatory only and are not restrictive of the present application. BRIEF DESCRIPTION OF THE DRAWINGS

[0050] The accompanying drawings are incorporated into and constitute a part of the specification, illustrating embodiments consistent with the present application and, together with the specification, explaining the principles of the present application. Obviously, the drawings described below are only some embodiments of the present application, and those skilled in the art can derive other drawings based on these drawings without inventive effort. In the drawings:

[0051] Figure 1 A flowchart of a query optimization method for a Text-to-SQL task according to an embodiment of the present application is shown;

[0052] Figure 2 A block diagram of a query optimization device for a Text-to-SQL task according to an embodiment of the present application is shown;

[0053] Figure 3 A block diagram of an electronic device according to an embodiment of the present application is shown;

[0054] Figure 4 Shows a schematic diagram of the structure of a computer system suitable for implementing an electronic device of an embodiment of the present application;

[0055] Figure 5 Shows a sample diagram of a query optimization dataset for a Text-to-SQL task according to one embodiment of the present application;

[0056] Figure 6 A schematic diagram of the effect of a query optimization method for a Text-to-SQL task according to an embodiment of the present application is shown. DETAILED DESCRIPTION

[0057] Example embodiments will now be described more fully with reference to the accompanying drawings. However, example embodiments can be implemented in many 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 thorough and complete and will fully convey the concepts of the example embodiments to those skilled in the art.

[0058] In addition, described feature, structure or characteristic can be combined in one or more embodiments in any suitable manner.In the following description, many specific details are provided so as to provide a full understanding of the embodiments of the present application. However, it will be appreciated by those skilled in the art that the technical scheme of the present application can be put into practice without one or more of the specific details, or other methods, components, devices, steps etc. can be adopted. In other cases, known methods, devices, implementations or operations are not shown or described in detail to avoid blurring the various aspects of the application.

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

[0060] The flowcharts shown in the accompanying drawings are for illustrative purposes only and do not necessarily include all contents and operations / steps, nor must they be executed in the order described. For example, some operations / steps may be decomposed, while others may be combined or partially combined. Therefore, the actual execution order may vary depending on the actual situation.

[0061] It should be noted that the terms "first," "second," and the like in the specification and claims of this application and the accompanying drawings are used to distinguish similar objects and are not necessarily used to describe a specific order or precedence. It should be understood that the terms used in this manner are interchangeable where appropriate, such that the embodiments of the present application described herein can be implemented in an order other than that illustrated or described.

[0062] To make the objectives, technical solutions, and advantages of the present invention more clear, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the accompanying drawings in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without making creative efforts shall fall within the scope of protection of the present invention.

[0063] The following will describe some embodiments of the present application in detail with reference to the accompanying drawings. In the absence of conflict, the following embodiments and features therein may be combined with each other.

[0064] See also Figure 1 , shows a flow chart of a query optimization method for a Text-to-SQL task according to an embodiment of the present application.

[0065] like Figure 1As shown, a query optimization method for a Text-to-SQL task is shown, which specifically includes steps S100 to S700.

[0066] refer to Figure 1 , step S100, creating a query optimization data set.

[0067] In some feasible embodiments, based on the above solution, step S100 includes:

[0068] Creating a query optimization dataset based on the Spider dataset, wherein the query optimization dataset includes: natural language questions, database schemas, SQL statements, optimal SQL statements, and estimated cardinality;

[0069] Furthermore, the SQL statements in the query optimization data set are rewritten according to a query rewriting method, and the data in the query optimization data set are supplemented by using an algorithm annotation method.

[0070] For example, the query optimization dataset can be represented as the following tuple:

[0071]

[0072] in, For the dataset, For natural language problems, For the database schema, For SQL statements, For the optimal SQL statement, To estimate the cardinality, a sample of the query optimization dataset is prepared as follows: Figure 5 shown.

[0073] Figure 5 This dataset presents a sample query optimization dataset, organized as a JSON array, with each object corresponding to a query optimization instance. The dataset includes a db_id identifying the database name, such as real_estate_properties in this example; a question expressing the query in natural language, such as asking for the name of a house or apartment with more than one room; a syn_question that may be a synonym for the question; a spider_query representing the SQL statement based on the question, using the UNION operator to select relevant property names from the Properties table; an optimized_query representing the optimized SQL statement, which in this example is the same as the spider_query; and a query representing another SQL statement, which in this example is also the same as the spider_query. This dataset provides a structured data foundation for query optimization research.

[0074] It is understandable that the purpose of rewriting the SQL statements in the query optimization data set according to the query rewriting method is to optimize the data cardinality to achieve optimal execution efficiency.

[0075] It is understandable that the purpose of supplementing the data in the query optimization dataset by using algorithm annotation is to achieve optimal execution efficiency of the data.

[0076] It should be noted that, in this embodiment, the specific process of supplementing the data in the query optimization dataset by using algorithm annotation is as follows:

[0077] First, use x to generate a problem-SQL statement team; then, use y algorithm to perform query rewriting on the generated SQL statements.

[0078] It is understandable that the existing query optimization dataset only generates SQL statements without considering the execution efficiency of the generated SQL. This embodiment innovatively proposes a query optimization dataset that adds optimal SQL and estimated cardinality to the existing dataset.

[0079] Continue to refer Figure 1 , step S200, designing an SQL generator model according to the encoder-decoder architecture.

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

[0081] For example, the SQL generator model is as follows:

[0082] ;

[0083] in, Represents the parameters of the generator, represents the tag sequence generated before time t, represents the tag sequence generated at time t, Represents the number of all moments.

[0084] In some feasible embodiments, based on the above solution, the loss function of the SQL generator model is defined as follows:

[0085] ;

[0086] in, represents the loss function of the SQL generator model, represents the tag sequence generated before time t, Represents the parameters of the SQL generator model, Representing natural language questions, Represents the database schema, represents the token sequence generated at time t.

[0087] Understandably, this loss ensures that the generated SQL is syntactically valid and semantically aligned with the input query.

[0088] Continue to refer Figure 1 , step S300, generating initial SQL query data based on the SQL generator model and the query optimization data set.

[0089] In some feasible embodiments, based on the above solution, step S300 includes:

[0090] Generate latent representations from natural language questions and database schemas using an encoder in the SQL generator model;

[0091] Initial SQL query data is generated using a decoder in the SQL generator model based on the latent representation.

[0092] Continue to refer Figure 1 , step S400, generating training data for query rewriting tasks based on initial SQL query data.

[0093] In some feasible embodiments, based on the above solution, generating training data for the query rewriting task based on the initial SQL query data includes:

[0094] Generating candidate query data with different execution times by controlling and modifying the initial SQL query data;

[0095] The candidate query data are divided into good and bad query teams according to the execution time, and used as the training data.

[0096] For example, for the generated initial SQL query data , by controlling the modification to generate a set of candidate query data with different execution times ; Then record the candidate query data with shorter execution time as , the candidate query data with longer execution time is recorded as , thus creating a set of good and bad query pairs .

[0097] In addition, a conversion function is defined in this process , which generates a perturbed query from the original query :

[0098] ;

[0099] in, represents a perturbation query, represents the original query, represents the perturbation parameter. The difference in execution time is:

[0100]

[0101] in, represents a perturbation query, Represents the original query, if , then the perturbation query is more efficient and treats it as a positive example; otherwise, it is treated as a negative example.

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

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

[0104] It should be noted that the query rewrite model will learn a transformation function , which maps the initial SQL query data to the optimized version:

[0105] ;

[0106] in, Represents the optimized query, represents the original query, are learnable parameters of the query rewriting model, Represents a query optimization transformation function. Its goal is to minimize the execution time of the optimized query. , while maintaining semantic equivalence with the original query.

[0107] In some feasible embodiments, based on the above solution, the loss function of the query rewriting model is defined as follows:

[0108] ;

[0109] in, represents the loss function of the query rewriting model, Indicates the execution time, Indicates the candidate query data with relatively short execution time in the good and bad query teams. Indicates the candidate query data with relatively long execution time in the good and bad query teams. is the interval parameter, > 0, which is used to enforce that the execution time of optimized queries must differ by at least delta from that of non-optimized queries. This penalty encourages the query rewrite model to generate queries with significantly lower execution times. Represents candidate query data, Indicates the total amount of candidate query data.

[0110] Continue to refer Figure 1 , step S600, performing joint training based on the SQL generator model and the query rewriting model to obtain a query optimization model.

[0111] In some feasible embodiments, based on the aforementioned 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 overall optimization objective is determined according to the loss function of the query optimization model;

[0112] Among them, the loss function of the query optimization model is as follows:

[0113] ;

[0114] in, represents the loss function of the query optimization model, represents a hyperparameter.

[0115] Hyperparameters Used to balance the semantic accuracy of SQL generation and the execution efficiency improvement brought by query rewriting.

[0116] In this embodiment, the overall optimization goal of the query optimization model is as follows:

[0117]

[0118] Among them, about The gradient of is calculated as:

[0119] ;

[0120] Because the ranking loss does not depend on .

[0121] Likewise, query rewrite model parameters The gradient of is:

[0122] ;

[0123] For each query pair,

[0124] ;

[0125] in is the indicator function.

[0126] Continue to refer Figure 1 , step S700, performing query optimization based on the query optimization model.

[0127] The following theoretically proves that the performance and execution efficiency of the query optimization model provided by this application meet the conditions of simultaneous optimization.

[0128] Assume that the SQL generator model and query rewriting model meet the following conditions: (i) the encoder-decoder model used in the SQL generator is trained to ensure semantic equivalence between the generated SQL and natural language queries; (ii) the transformation function used by the query rewriting model is Maintain the semantics of the input SQL query; (iii) execution time function is Lipschitz continuous; (iv) interval parameter Reasonable settings. Then, minimize the total loss:

[0129] ;

[0130] Ensure optimized queries satisfy:

[0131] ;

[0132] While maintaining semantic equivalence, i.e.,

[0133] .

[0134] in, represents the optimized query, and represents the original query.

[0135] Furthermore, under the standard assumption of gradient optimization, the proposed framework converges to This provides a principled guarantee for improving query execution efficiency without compromising semantic correctness.

[0136] prove:

[0137] Proof comes from ranking loss The loss explicitly penalizes any failure to reduce the execution time by at least Candidate rewrites of Since the SQL generator passes Optimize semantic correctness and query rewrite function is constrained to keep the semantics unchanged, so the total loss Any reduction in Then, the standard gradient descent convergence result shows that Under the premise of Lipschitz continuity and differentiability of the loss component, the optimization converges to a local minimum where these properties hold. Proof.

[0138] The query optimization method using the technical solution of this application has the following effects: Figure 6 shown. Figure 6 The paper focuses on demonstrating the effectiveness of the query optimization method using the technical solution of this application, presenting it through a comparison of specific questions, original SQL, and optimized SQL. For example, for querying singer information and sorting by age, the optimized SQL adds LIMIT 100 to limit the number of result rows to improve performance; for querying the country of origin of singers over 20 years old, the optimization method removes the DISTINCT keyword to reduce performance overhead; for querying the names of stadiums that have not hosted concerts, the NOT IN subquery is replaced with NOT EXISTS, because in some database systems, NOT EXISTS performs better when the subquery result set is large. These comparisons intuitively demonstrate the effectiveness of the optimization method in different query scenarios.

[0139] In summary, this application has the following advantages:

[0140] (1) New Problem Definition: We formally define the Text-to-SQL query optimization problem, emphasizing its unique challenges, its differences from classic Text-to-SQL tasks, and its relationship to traditional database optimization.

[0141] (2) New Dataset: We propose the Query Optimization Dataset Spider-CE, a new dataset tailored for evaluating the execution efficiency of Text-to-SQL models. Based on the widely used Spider dataset, the Query Optimization Dataset Spider-CE enhances annotations and performance metrics specifically for measuring query efficiency.

[0142] (3) Novel Framework: We propose a novel multi-task learning framework to jointly optimize SQL generation and query execution. The framework consists of a SQL generator and a query rewriting model. Extensive experiments on five benchmarks show that the framework achieves state-of-the-art performance in both accuracy and efficiency.

[0143] The following describes an embodiment of the device of the present application, which can be used to execute a query optimization method for a Text-to-SQL task in the above embodiment of the present application. For details not disclosed in the embodiment of the device of the present application, please refer to the embodiment of the method of the above application.

[0144] Reference Figure 2 As shown, according to one embodiment of the present application, a query optimization device 200 for a Text-to-SQL task includes:

[0145] A creation unit 201 is used to create a query optimization data set;

[0146] A design unit 202 is used to design an SQL generator model according to the encoder-decoder architecture;

[0147] A first generating unit 203 is configured to generate initial SQL query data based on the SQL generator model and the query optimization dataset;

[0148] A second generating unit 204 is configured to generate training data for the query rewriting task based on the initial SQL query data;

[0149] A first training unit 205 is configured to train a query rewriting model using the training data;

[0150] A second training unit 206 is configured to perform joint training based on the SQL generator model and the query rewriting model to obtain a query optimization model;

[0151] The query optimization unit 207 is configured to perform query optimization based on the query optimization model.

[0152] like Figure 3 As 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 in 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 Text-to-SQL tasks are implemented.

[0153] Since the electronic device introduced in this embodiment is a device used to implement a query optimization device for Text-to-SQL tasks in the embodiment of this application, based on the method introduced in the embodiment of this application, technical personnel in this field can understand the specific implementation of the electronic device of this embodiment and its various variations. Therefore, how the electronic device implements the method in the embodiment of this application is no longer introduced in detail here. As long as the equipment used by technical personnel in this field to implement the method in the embodiment of this application falls within the scope of protection of this application.

[0154] During the specific implementation process, when the computer program 311 is executed by the processor, any implementation method of the embodiments corresponding to the first aspect can be implemented.

[0155] Figure 4 A schematic diagram of the structure of a computer system suitable for implementing an electronic device according to an embodiment of the present application is shown.

[0156] It should be noted that Figure 4The computer system 400 of the electronic device shown is only an example and should not bring any limitation to the functions and scope of use of the embodiments of the present application.

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

[0158] The following components are connected to the I / O interface 405: an input section 406 including a keyboard, mouse, and the like; an output section 407 including devices such as a cathode ray tube (CRT), a liquid crystal display (LCD), and speakers; a storage section 408 including devices such as a hard disk; and a communication section 409 including a network interface card such as a LAN (Local Area Network) card or a modem. 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. Removable media 411, such as a magnetic disk, an optical disk, a magneto-optical disk, or a semiconductor memory, is installed in the drive 410 as needed, so that computer programs read from the media can be installed in the storage section 408 as needed.

[0159] In particular, according to embodiments of the present application, the processes described above with reference to the flowcharts can be implemented as computer software programs. For example, embodiments of the present application include a computer program product comprising a computer program carried on a computer-readable medium, the computer program containing program code for executing the methods illustrated in the flowcharts. In such embodiments, the computer program can be downloaded and installed from a network via the communication section 409 and / or installed from removable media 411. When executed by the central processing unit (CPU) 401, the computer program performs the various functions defined in the system of the present application.

[0160] It should be noted that the computer-readable medium described in the embodiments of this application may be a computer-readable signal medium or a computer-readable storage medium, or any combination thereof. A computer-readable storage medium may be, for example, but not limited to, an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any combination thereof. More specific examples of computer-readable storage media may include, but are not limited to, an electrical connection having one or more conductors, a portable computer disk, a hard disk, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM), flash memory, optical fiber, a portable compact disc read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination thereof. In this application, a computer-readable storage medium may be any tangible medium containing or storing a program that can be used by or in conjunction with an instruction execution system, apparatus, or device. In this application, a computer-readable signal medium may include a data signal transmitted in baseband or as part of a carrier wave, which carries computer-readable program code. Such propagated data signals may take various forms, including but not limited to electromagnetic signals, optical signals, or any suitable combination thereof. A computer-readable signal medium may also be any computer-readable medium other than a computer-readable storage medium that can transmit, propagate, or transport a program for use by or in connection with an instruction execution system, apparatus, or device. Program code embodied on a computer-readable medium may be transmitted using any suitable medium, including but not limited to wireless, wired, or any suitable combination thereof.

[0161] The flowcharts and block diagrams in the accompanying drawings illustrate the possible implementation architecture, functions and operations of the systems, methods and computer program products according to various embodiments of the present application. Among them, each box in the flowchart or block diagram can represent a module, program segment, or part of the code, and the above-mentioned 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 box can also occur in an order different from that marked in the accompanying drawings. For example, two boxes represented in succession can actually be executed substantially in parallel, and they can sometimes be executed in the opposite order, depending on the functions involved. It should also be noted that each box in the block diagram or flowchart, and the combination of boxes in the block diagram or flowchart, can be implemented with a dedicated hardware-based system that performs the specified function or operation, or can be implemented with a combination of dedicated hardware and computer instructions.

[0162] The units involved in the embodiments described in this application may be implemented by software or hardware, and the units described may also be set in a processor. In some cases, the names of these units do not constitute limitations on the units themselves.

[0163] As another aspect, the present application further provides a computer program product or computer program, which includes computer instructions stored in a computer-readable storage medium. A processor of a computer device reads the computer instructions from the computer-readable storage medium and executes the computer instructions, causing the computer device to perform the query optimization method for a Text-to-SQL task described in the above embodiment.

[0164] 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 independently and not incorporated into the electronic device. The computer-readable medium carries one or more programs, which, when executed by the electronic device, enable the electronic device to implement the query optimization method for a Text-to-SQL task described in the above embodiments.

[0165] It should be noted that, although several modules or units of the device for action execution are mentioned in the above detailed description, this division is not mandatory. In fact, according to the embodiment of the application, the features and functions of two or more modules or units described above can be concretized in one module or unit. On the contrary, the features and functions of one module or unit described above can be further divided into multiple modules or units to be concretized.

[0166] Through the description of the above embodiments, it is easy for those skilled in the art to understand that the example embodiments described herein can be implemented by software or by combining software with necessary hardware. Therefore, the technical solution 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 (which can be a CD-ROM, a USB flash drive, a mobile hard disk, etc.) or on a network, and includes a number of instructions to enable a computing device (which can be 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.

[0167] Those skilled in the art will readily appreciate other embodiments of the present application after considering the specification and practicing the embodiments disclosed herein. This application is intended to cover any variations, uses, or adaptations of the present application that follow the general principles of the present application and include common knowledge or customary techniques in the art that are not disclosed in this application. It should be understood that this application is not limited to the precise structures described above and shown in the accompanying drawings, and various modifications and changes can be made without departing from the scope thereof. The scope of this application is limited only by the appended claims.

Claims

1. A query optimization method for a Text-to-SQL task, characterized in that: include: Creating a query optimization dataset, wherein the query optimization dataset includes: a natural language question, a database schema, an SQL statement, an optimal SQL statement, and an estimated cardinality; 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 dataset; Generate training data for query rewriting tasks based on initial SQL query data; Training 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; Performing query optimization based on the query optimization model; The step of generating training data for the query rewriting task based on the initial SQL query data includes: Generating candidate query data with different execution times by controlling and modifying the initial SQL query data; Dividing the candidate query data into good and bad query teams according to the execution time as the training data; Among them, the query rewriting model learns a transformation function , the conversion function You can map the initial SQL query data to the optimized version: ； in, Represents the optimized query, represents the original query, are learnable parameters of the query rewriting model, Represents a transformation function for query optimization.

2. The method according to claim 1, characterized in that The step of creating a query optimization data set includes: Create a query optimization dataset based on the Spider dataset; Furthermore, the SQL statements in the query optimization data set are rewritten according to a query rewriting method, and the data in the query optimization data set are supplemented by using an algorithm annotation method.

3. The method according to claim 2, characterized in that Generating initial SQL query data based on the SQL generator model and the query optimization data set includes: Generate latent representations from natural language questions and database schemas using an encoder in the SQL generator model; Initial SQL query data is generated using a decoder in the SQL generator model based on the latent representation.

4. The method according to claim 1, wherein The loss function of the SQL generator model is defined as follows: ；（1） in, represents the loss function of the SQL generator model, represents the tag sequence generated before time t, Represents the parameters of the SQL generator model, Representing natural language questions, Represents the database schema, represents the tag sequence generated at time t, and T represents the number of all moments.

5. The method according to claim 4, characterized in that The loss function of the query rewriting model is defined as follows: ；（2） in, represents the loss function of the query rewriting model, Indicates the execution time, Indicates the candidate query data with relatively short execution time in the good and bad query teams. Indicates the candidate query data with relatively long execution time in the good and bad query teams. is the interval parameter, Represents candidate query data, Indicates the total amount of candidate query data.

6. The method according to claim 5, characterized in that During the joint training process, determining a loss function of a query optimization model based on the loss function of the SQL generator model and the loss function of the query rewriting model, and determining an overall 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） in, represents the query optimization model loss function, represents a hyperparameter.

7. A query optimization device for a Text-to-SQL task, applied to the method according to any one of claims 1 to 6, characterized in that: include: A creation unit for creating a query optimization dataset; A design unit, used to design an SQL generator model based on the encoder-decoder architecture; A first generating unit, configured to generate initial SQL query data based on the SQL generator model and the query optimization dataset; A second generating unit, configured to generate training data for a query rewriting task based on the initial SQL query data; A first training unit, configured to train a query rewriting model using the training data; A second training unit is configured to perform joint training based on the SQL generator model and the query rewriting model to obtain a query optimization model; A query optimization unit is used to perform query optimization based on the query optimization model.

8. A computer-readable storage medium, characterized in that The storage medium stores computer instructions, which, when executed on a computer, enable the computer to execute the method according to any one of claims 1 to 6.

9. An electronic device, characterized in that: include: memory and 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 to 6.

Images