SQL (Structured Query Language) statement generation method and device, electronic equipment, medium and program product

Abstract

The invention discloses an SQL (Structured Query Language) statement generation method and device, electronic equipment, a medium and a program product, and relates to the technical field of databases, the SQL statement generation method comprises the following steps: updating each first named entity in an original question text into a corresponding second named entity in a preset database to obtain a replacement question text; according to a preset table selection model and a column selection model, performing matching in a preset database to obtain a table name and a column name; inputting the table name, the column name and the replacement problem text into an SQL generation model to obtain a first SQL statement which does not carry a query value; inputting the first SQL statement and the replacement question text into a text generation model, and determining a second SQL statement carrying a query value; and correspondingly replacing the corresponding first named entity in the original problem text with the second named entity in the second SQL statement to obtain the target SQL statement, and by splitting the SQL statement generation task, the flexibility and automation level of statement generation are improved.

Description

Technical Field

[0001] This application relates to the field of database technology, and in particular to SQL statement generation methods, apparatus, electronic devices, computer-readable storage media, and computer program products. Background Technology

[0002] Currently, scenarios involving user information management and service optimization, billing and cost control, precision marketing, and customer data analysis by telecom operators typically involve a large amount of data management and query operations. However, users may not all be professional database administrators or developers, making it relatively difficult for them to write complex SQL (Structured Query Language) statements. Therefore, a solution is needed to convert user-inputted Chinese natural language into corresponding SQL statements. Current SQL statement conversion solutions mainly include pipeline methods and deep learning methods. Pipeline methods rely on a general description of natural language queries, thus failing to handle complex and varied natural language descriptions. Deep learning methods, due to their end-to-end architecture, have complex models, resulting in lower accuracy and relatively weaker practicality in real-world applications.

[0003] The above content is only used to help understand the technical solution of this application and does not represent an admission that the above content is prior art. Summary of the Invention

[0004] The main purpose of this application is to provide a method, apparatus, electronic device, computer-readable storage medium, and computer program product for generating SQL statements, aiming to solve the technical problem of poor practicality of current SQL statement conversion methods.

[0005] To achieve the above objectives, this application proposes an SQL statement generation method, which includes: Update each first named entity in the original question text with the corresponding second named entity in the preset database to obtain the replaced question text; Based on the preset table selection model and column selection model, match the table name and column name corresponding to the replacement question text in the preset database; Input the table name, the column name, and the replacement question text into a preset SQL generation model to obtain a first SQL statement, wherein the first SQL statement does not carry a query value; Input the first SQL statement and the replacement question text into a preset text generation model to determine the second SQL statement, wherein the second SQL statement carries a query value; Replace the corresponding first named entities in the original question text with the second named entities in the second SQL statement to obtain the target SQL statement.

[0006] In one embodiment, the step of updating each first named entity in the original question text with the corresponding second named entity in a preset database to obtain the replaced question text includes: The first named entities in the original question text are identified by a preset named entity model, wherein the types of the first named entities include at least table name, column name and organization name; Match the second named entities corresponding to each of the first named entities in the preset database; The first named entity in the original question text is replaced with the corresponding second named entity to obtain the replaced question text.

[0007] In one embodiment, the step of matching the table name and column name corresponding to the replacement question text in a preset database according to a preset table selection model and column selection model includes: The replacement question text is input into a pre-trained table selection model, which matches the replacement question text with tables in a preset database to obtain the table name corresponding to the replacement question text. The replacement question text and the table name are input into a pre-trained column selection model. The column selection model matches the replacement question text with the column corresponding to the table name in a preset database to obtain the column name corresponding to the replacement question text.

[0008] In one embodiment, the step of inputting the first SQL statement and the replacement question text into a preset text generation model to determine the second SQL statement includes: Input the first SQL statement and the replacement question text into a preset text generation model, and output at least one query value through the text generation model; The query values ​​are filled into the first SQL statement to obtain the second SQL statement.

[0009] In one embodiment, before the step of matching the table name and column name corresponding to the replacement question text in a preset database according to a preset table selection model and column selection model, the method further includes: Obtain the question text data and the corresponding SQL statement data to construct a first training set, wherein the first training set includes at least the question text, table name, and real label; Based on the first training set, the pre-trained BERT (Bidirectional Encoder Representations from Transformers) model is fine-tuned to obtain a table selection model, which is used to determine whether the input question text data includes the input table name. Obtain the question text data and the corresponding SQL statement data to construct a second training set, wherein the second training set includes at least the question text, table name, column name and column type; Based on the second training set, the preset BERT pre-trained model is fine-tuned to obtain a column selection model. The column selection model is used to mark the column names in the question text according to the question text data, the table name corresponding to the question text data, and the column name and column type corresponding to the table name.

[0010] In one embodiment, the question text data is enhanced question text data, and the SQL statement generation method further includes: Collect the original question text data; Randomly replace the database values ​​in the original question text data to obtain enhanced question text data; and / or, The column names existing in the preset database in the original question text data are updated to obtain enhanced question text data, wherein the updating method is to add, delete, or replace; and / or, Generate text with the same semantic scope as the original question text data to obtain enhanced question text data; The enhanced question text data is used to construct the first training set and the second training set.

[0011] Furthermore, to achieve the above objectives, this application also proposes an SQL statement generation apparatus, which includes: The entity replacement module is used to update each first named entity in the original problem text with the corresponding second named entity in the preset database to obtain the replaced problem text. The table and column selection module is used to match the table name and column name corresponding to the replacement question text in a preset database according to the preset table selection model and column selection model. The statement generation module is used to input the table name, the column name and the replacement question text into a preset SQL generation model to obtain a first SQL statement, wherein the first SQL statement does not carry a query value; The value filling module is used to input the first SQL statement and the replacement question text into a preset text generation model to determine the second SQL statement, wherein the second SQL statement carries a query value; The entity restoration module is used to replace the corresponding first named entities in the original question text with each second named entity in the second SQL statement to obtain the target SQL statement.

[0012] In addition, to achieve the above objectives, this application also proposes an electronic device, the device comprising: a memory, a processor, and a computer program stored in the memory and executable on the processor, the computer program being configured to implement the steps of the SQL statement generation method as described above.

[0013] In addition, to achieve the above objectives, this application also proposes a storage medium, which is a computer-readable storage medium, on which a computer program is stored, and which, when executed by a processor, implements the steps of the SQL statement generation method described above.

[0014] In addition, to achieve the above objectives, this application also provides a computer program product, which includes a computer program that, when executed by a processor, implements the steps of the SQL statement generation method described above.

[0015] The SQL statement generation method in this application breaks down the SQL statement generation task. First, each first named entity in the original question text is updated to its corresponding second named entity in a preset database; this step corresponds to the text replacement subtask. Then, based on preset table selection and column selection models, the table name and column name corresponding to the replacement question text are matched in the preset database, completing the table and column matching subtask. Next, the table name, column name, and replacement question text are input into a preset SQL generation model to obtain the first SQL statement, completing the statement generation subtask. Then, the first SQL statement and the replacement question text are input into a preset text generation model to determine the second SQL statement, completing the value filling subtask. Finally, the text replacement subtask is performed, replacing the corresponding first named entities in the original question text with the second named entities in the second SQL statement. As described above, this application adopts different processing methods according to the characteristics of each subtask. The model includes a preset SQL generation model and a text generation model. Compared to traditional pipeline methods, the method in this application does not rely on templates and manual design, avoiding inflexibility and poor model portability. Furthermore, in terms of the model, each subtask is transformed into a standard natural language processing deep learning model. This data-driven approach significantly reduces the labor costs of rule writing and feature design, improves the flexibility and automation of each subtask, and successfully eliminates the process of building a model from scratch, further saving time and resources required for model training. Specifically, this application first generates an SQL statement without values, and then combines the replacement of the question text with a text generation model to form a complete SQL statement. To achieve this goal, an SQL statement generation model and a text generation model are introduced to handle these two stages separately. After the model outputs the SQL statement, the named entities are finally replaced with the named entities from the question, thereby improving the accuracy of the output target SQL statement. The SQL statement generation method of this application, through the aforementioned task decomposition, effectively reduces the overall model complexity, improves trainability and the ease of parameter adjustment, and can adapt to various application scenarios, demonstrating strong practicality. Attached Figure Description

[0016] The accompanying drawings, which are incorporated in and form part of this specification, illustrate embodiments consistent with this application and, together with the description, serve to explain the principles of this application.

[0017] To more clearly illustrate the technical solutions in the embodiments of this application or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, for those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0018] Figure 1This is a flowchart illustrating an embodiment of the SQL statement generation method of this application. Figure 2 This is a full-process example diagram of a feasible SQL statement generation method in the embodiments of this application; Figure 3 This is a schematic diagram of a feasible SQL statement generation system functional module in an embodiment of this application; Figure 4 This is a schematic diagram of the structure of the SQL statement generation device in the embodiments of this application; Figure 5 This is a schematic diagram of the device structure of the hardware operating environment involved in the SQL statement generation method in this application embodiment.

[0019] The purpose, features, and advantages of this application will be further explained in conjunction with the embodiments and with reference to the accompanying drawings. Detailed Implementation

[0020] It should be understood that the specific embodiments described herein are merely illustrative of the technical solutions of this application and are not intended to limit this application.

[0021] To better understand the technical solution of this application, a detailed description will be provided below in conjunction with the accompanying drawings and specific implementation methods.

[0022] The executing entity in this embodiment can be a computing service device with data processing, network communication, and program execution functions, such as a tablet computer, personal computer, mobile phone, server, etc., or an electronic device or control device capable of performing the above functions. The following description uses a server as the executing entity to illustrate this embodiment and the subsequent embodiments.

[0023] This application provides a method for generating SQL statements, referring to... Figure 1 , Figure 1 This is a flowchart illustrating the first embodiment of the SQL statement generation method of this application. The SQL statement generation method includes: Step S10: Update each first named entity in the original problem text to the corresponding second named entity in the preset database to obtain the replaced problem text; The first named entity is the named entity in the original question text, and the second named entity is the named entity that exists in the preset database. The types of the named entities include at least table name, column name and organization name.

[0024] Since the original question text is user-input natural language text, it may contain irregularities. To avoid the influence of these irregular named entities during SQL statement generation and to reduce the risk of spelling errors and entity omissions, the first named entity is replaced with the standardized second named entity in the preset database (i.e., the SQL database) to obtain the replaced question text with standardized named entities. Finally, the original question text and the replaced question text are stored in the results storage module, which stores data such as the input and output of each stage of the sub-model and the final task result for future retrieval.

[0025] For example, if the original question text includes the first named entity "Zhang3", while the preset database includes the second named entity "Zhang San", then the first named entity is non-standard. To avoid non-standard named entities affecting the generation of SQL statements, "Zhang3" in the original question text needs to be replaced with "Zhang San". It should be noted that if the original question text contains a first named entity that is identical to the second named entity in the database, then that first named entity does not need to be replaced.

[0026] Step S20: Based on the preset table selection model and column selection model, match and replace the table name and column name corresponding to the problem text in the preset database; The preset table selection model and column selection model can be models obtained by fine-tuning the BERT pre-trained model using a pre-built training dataset. These models can determine the table name and column name that need to be selected when performing an SQL query to replace the question text.

[0027] Specifically, the replacement question text is input into the table selection model and the column selection model respectively. The table selection model and the column selection model process the text and match it with the corresponding tables and columns in the database. Then, the text is stored in the result storage module.

[0028] To maintain flexibility, as the latest technologies evolve, older table and column selection models can be replaced at any time based on the latest ones, thereby reducing the labor costs of rule writing and feature design and improving the flexibility and automation of SQL statement generation.

[0029] Step S30: Input the table name, column name and replacement question text into the preset SQL generation model to obtain the first SQL statement, wherein the first SQL statement does not carry query values; The query value must include at least one of the following: query range, query entity, and query type. After obtaining the table name and column names, these are directly combined with the replacement question text and input into a pre-trained SQL generation model. This model generates a standard SQL statement without query values ​​based on the table and column names. An SQL statement without query values ​​includes the table and column names but does not contain specific query parameters; it is essentially a framework for an SQL statement, excluding query ranges (such as time ranges), query entities, query types, and other query values.

[0030] Since the named entities in the replacement problem text are all second named entities that exist in the database, the SQL generation model, which is pre-built using training data from the database, can effectively select tables and columns based on the named entities in the replacement problem text, thereby improving the accuracy of the generated SQL statements and the overall accuracy and reliability.

[0031] Step S40: Input the first SQL statement and the replacement question text into the preset text generation model to determine the second SQL statement, wherein the second SQL statement carries the query value; After obtaining the first SQL statement without query values, further query value filling is required. Specifically, the first SQL statement is combined with the previously obtained replacement question text and input into a pre-trained text generation model for value filling. That is, the positions in the first SQL statement where query values ​​can be filled are filled with query values ​​extracted from the replacement question text. Then, the data is integrated to obtain a standard SQL query statement.

[0032] Since the named entities in the replacement problem text are all second named entities that exist in the database, the text generation model built in advance using the training data in the database can effectively extract the second named entities in the replacement problem text and obtain effective filling values, thereby improving the reliability and accuracy of the system.

[0033] Step S50: Replace the corresponding first named entities in the original question text with the second named entities in the second SQL statement to obtain the target SQL statement.

[0034] Through the aforementioned steps S10 to S40, a standard statement suitable for SQL data querying is obtained. However, the named entities in this statement are determined by replacing the question text, which does not match the first named entity in the original question text input by the user. To improve the user experience and ensure that the final output target SQL statement corresponds to the original question text input by the user, the second named entity in the generated second SQL statement is used to replace the first named entity in the original question text. This makes the target SQL statement more closely match the original question text input by the user, thus improving the user experience.

[0035] Furthermore, in a feasible embodiment, the step of updating each first named entity in the original question text with the corresponding second named entity in a preset database to obtain the replaced question text includes: Step S11: Identify each first named entity in the original question text through a preset named entity model, wherein the type of the first named entity includes at least table name, column name and organization name; Step S12: Match the second named entities corresponding to each first named entity in the preset database; Step S13: Replace the corresponding second named entity in the first named entity in the original question text to obtain the replaced question text.

[0036] To ensure that the named entities input into the model are consistent with the named entities in the database, this embodiment of the application scans the original question text using a pre-trained named entity model, identifies the first named entity, replaces the second named entity in the database with the identified first named entity, and finally stores both the original question text and the replaced question text in the result storage module.

[0037] The named entity recognition model is responsible for identifying named entities in the original text. This model is a pre-trained deep learning model, which can be any mature network model, such as a CNN (Convolutional Neural Network) or a large language model. It locates and classifies entities from the input text, such as table names, column names, and organization names, and then replaces them with representations of second named entities existing in a database. Specifically, the named entity recognition model associates the identified first named entities with the second named entities in the database; that is, the identified first named entity will replace the corresponding representation of the second named entity in the database.

[0038] Thus, the replaced question text is then passed to the subsequent model. The subsequent model now uses accurate named entity information from the database when processing the question, improving its sensitivity to entities. The model then generates SQL statements according to the normal generation process. Because accurate named entity information is used in the input, the generated SQL statements are more precise. Finally, the generated SQL statements may contain entity representations from the database. To restore the original question format, the named entity model can intervene again, replacing the database representations (i.e., the second named entity) in the SQL statement back with the original named entities (i.e., the first named entity).

[0039] In one feasible embodiment, the step of matching and replacing the table name and column name corresponding to the problem text in a preset database according to a preset table selection model and column selection model may include: Step S21: Input the replacement question text into the pre-trained table selection model. The table selection model matches the replacement question text with tables in the preset database to obtain the table name corresponding to the replacement question text. Step S22: Input the replacement question text and table name into the pre-trained column selection model. The column selection model matches the replacement question text with the column corresponding to the table name in the preset database to obtain the column name corresponding to the replacement question text.

[0040] The table selection and column selection models are pre-trained based on the BERT model. Using BERT for table and column selection captures deeper semantic information, supports parallel computation, and significantly improves processing speed. As a pre-trained model, BERT requires only minor fine-tuning to achieve good results, and its exposure to a large amount of text data during pre-training also enhances its generalization ability. Simply input the replacement question text into the table and column selection models, and they will output the corresponding table and column names.

[0041] In this embodiment, the SQL statement generation is implemented using the T5 (text-to-text) algorithm. As a pre-trained model based on Transformer (a natural language processing architecture), T5 has strong generation capabilities and does not rely on complex syntax tree grammar rules. It is more flexible and versatile. As an end-to-end solution, T5 simplifies the design and implementation process of the system from input natural language text to output SQL query statements and reduces the need for manual intervention.

[0042] Furthermore, prior to the step of matching and replacing the table name and column name corresponding to the question text in a preset database based on a preset table selection model and column selection model, the method may further include: Step A10: Obtain the question text data and the corresponding SQL statement data to construct the first training set, wherein the first training set includes at least the question text, table name and real label; Step A20: Based on the first training set, fine-tune the preset BERT pre-trained model to obtain the table selection model, wherein the table selection model is used to determine whether the input question text data includes the input table name; First, a first training set for training the table selection model can be constructed based on the collected question text data. Given BERT's robustness to text length and its superior performance in text classification and sequence labeling tasks, this embodiment uses a pre-trained BERT model and fine-tunes it to establish the table selection sub-model. After selecting BERT as the pre-trained model, its hyperparameters (including the number of training epochs, learning rate, batch size, etc.) are configured. Subsequent fine-tuning involves adding a classifier layer after loading the pre-trained BERT model, providing the first training set to BERT, calculating the loss function, and using backpropagation to calculate the gradient of the loss function with respect to the model parameters. The Adam (Adaptive Moment Estimation) optimizer is used to update the model parameters. After multiple iterations, iteration stops when the learning rate reaches a threshold (e.g., 0.01).

[0043] Specifically, in constructing the first training set, the table selection task involves determining whether the question mentions a table selected from the database. Specifically, a question and a table name are input into the database, causing the table selection model to output a yes or no answer. The label is 1 if the question mentions the table, and 0 otherwise. The data format is then constructed accordingly: {Input: Question + Table Name, Label: 0 or 1}. Based on the existing question SQL pair dataset, a dataset for training the table selection model is constructed using the above data format.

[0044] Step A30: Obtain the question text data and the corresponding SQL statement data to construct a second training set, wherein the second training set includes at least the question text, table name, column name, and column type; Step A40: Based on the second training set, fine-tune the preset BERT pre-trained model to obtain the column selection model. The column selection model is used to mark the column names in the question text according to the question text data, the table name corresponding to the question text data, and the column name and column type corresponding to the table name.

[0045] There is a fixed order between steps A10 and A20, and a fixed order between steps A30 and A40. Steps A10 to A20 can be executed simultaneously with steps A30 to A40, without any restrictions.

[0046] A second training set is constructed for training the column selection model. In this embodiment, a BERT pre-trained model plus fine-tuning method can be used to construct the column selection sub-model. After selecting BERT as the pre-trained model, its hyperparameters (including the number of training epochs, learning rate, batch size, etc.) are configured. The subsequent fine-tuning involves adding a classifier layer after loading the pre-trained BERT model, providing the training data to BERT, calculating the loss function and using the backpropagation algorithm to calculate the gradient of the loss function with respect to the model parameters, updating the model parameters using the Adam optimizer, and stopping the iteration when the learning rate reaches a threshold (e.g., 0.01) after multiple iterations.

[0047] Specifically, in constructing the second training set, the column selection task requires the model to label the columns mentioned in the question. Therefore, the input for column selection includes the question, the table name mentioned in the question, and the names and types of all columns in the table (column type information is included to enrich the model input). The model output is information about whether the target column is hit. The output sequence is represented as 'o', where 'a' represents the sequence length, which is the same length as the input sequence and corresponds to it. Column separators are used to label column click information. When a column is hit, its column separator position is labeled BC; if it is not hit, it is labeled BN; all other positions are labeled O. Then, the input data format is constructed accordingly: {Input: Question + Table Name + Column 1 Name + Column 1 Type + ... + Column n Name + Column n Type}. Based on the existing question-SQL pair dataset, a second dataset for a column selection model is constructed using the above data format.

[0048] For example, the dataset constructed based on data from operator scenarios, due to the large amount of data involving personal privacy, further employs anonymization processing in this embodiment, processing the dataset based on real tables. Finally, the dataset is divided into training set samples and test set samples in a 9:1 ratio to ensure that the model training and evaluation process is sufficiently representative.

[0049] For example, in the table selection and column selection subtasks, the BERT model can be selected, which contains 12 encoders, 12 attention heads, and 768 input dimensions. In the SQL statement generation and value imputation subtasks, the T5 model is selected, which contains 12 encoders and decoders, 12 attention heads, and 768 input dimensions. The corresponding datasets for each submodel are input into the submodel for model building, and fine-tuning is used to improve the model's accuracy. BERT fine-tuning involves adding a classifier layer after loading the pre-trained BERT model, providing training data to the pre-trained BERT model, calculating the loss function, using backpropagation to calculate the loss function with respect to the model parameters, and updating the model parameters using the Adam optimizer. Iteration stops when the learning rate reaches a threshold (e.g., 0.01). T5's fine-tuning method differs from BERT. T5 adds a task declaration prefix before the input data to specify the task the model should perform, thus enabling it to handle various NLP tasks without changing the model structure. Training data with a task declaration prefix is ​​fed into a pre-trained T5 model. The loss function is computed, and backpropagation is used to calculate the loss function with respect to the model parameters. The model parameters are then updated using the Adam optimizer, and iteration stops when the learning rate reaches a threshold (e.g., 0.01). This selection and configuration aims to fully leverage the strong performance of BERT and T5 across different subtasks to ensure accurate modeling of the operator's scenario.

[0050] In one feasible embodiment, before step S30, the SQL generation model needs to be trained first. First, a SQL generation dataset for training is constructed. For the SQL statement generation model, the input is expected to be a question, table name, and column names, and the model should output an SQL statement without query values. During the generation process, specific table names and column names are replaced with corresponding identifiers to generate SQL statements without query values. The constructed data format is: {Input: "Question + Table Separator + Corresponding Subscript Identifier + Table 1 + Column Separator + Column 1 Name + ... + Column Separator + Column n Name + ... + Table Separator + ..."}, and the output is: {SQL: SQL statement without values}. Based on the existing question-SQL pair dataset, a dataset for training the SQL statement generation model is constructed using the above data format.

[0051] Furthermore, to simplify the learning and training of the SQL generation model, a replication mechanism can be introduced. This involves directly selecting the required fields from the input question text data for generation, and using a targeted search method to avoid generating invalid SQL queries. At this stage, given the limitations of BERT in text generation tasks, T5 can be chosen as the pre-trained model for the SQL generation subtask, as it is a general-purpose Chinese natural language pre-training model with only slightly fewer parameters than BERT. After selecting T5 as the pre-trained model, the hyperparameters of the T5 model, including the learning rate and batch size, are configured according to the task requirements. Then, the pre-trained T5 model is loaded. T5's fine-tuning method differs from BERT's; T5 adds a task declaration prefix to the input data to specify the task the model should perform, thus enabling it to handle various NLP tasks without changing the model structure. The training data with the task declaration prefix is ​​input into the pre-trained T5 model. The loss function is calculated, and the gradient of the loss function with respect to the model parameters is calculated using the backpropagation algorithm. The Adam optimizer is used to update the model parameters. After multiple iterations, iteration stops when the learning rate reaches a threshold (e.g., 0.01). In this step, the SQL generation model generates SQL query statements without values ​​and stores them in the results storage module. This step significantly reduces the labor costs of rule writing and feature design, and improves flexibility and automation.

[0052] In one feasible embodiment, the step of inputting the first SQL statement and the replacement question text into a preset text generation model to determine the second SQL statement may include: Step S41: Input the first SQL statement and the replacement question text into the preset text generation model, and output at least one query value through the text generation model; Step S42: Fill each query value into the first SQL statement to obtain the second SQL statement.

[0053] In this embodiment, the first SQL query statement generated in the previous step is combined with the replacement question text, which is then input into a pre-trained text generation model for value filling. The corresponding query value is output and then filled into the first SQL statement to obtain the standard second SQL query statement.

[0054] It should be noted that a value imputation dataset is constructed before training the text generation model. Specifically, in the value imputation model, the input data format is constructed as follows: {Input: Question [SEP] select v1_ from v2_ where v3_, Values: word1, word2, word3}. The question and the SQL statement are separated by a special delimiter [SEP]. Then, based on the existing question-SQL pair dataset and the SQL statement generated in the previous step, a dataset is constructed for the value imputation model using the above data format. The training of this model can also refer to the training process of the aforementioned SQL generation model, using loss functions, Adam optimizers, multiple iterations, etc., which will not be elaborated here.

[0055] To more clearly explain the method for generating the above SQL statements, the following section will refer to the appendix. Figure 2 The specific implementation steps of this method are described below.

[0056] First, the pipeline structure receives the original question text input by the user. After the original question text ("How many users in Channel X have cancelled their broadband subscriptions this month") is input, the named entity recognition module replaces the named entities in the original question text with entities existing in the database, resulting in the replaced question text ("How many users in Channel X Group have cancelled their broadband subscriptions this month?"). Next, the corresponding table name, field name, SQL statement without values ​​(first SQL statement), and standard SQL query statement (second SQL statement) are determined through table selection model, column selection model, SQL generation model, and value filling model (i.e., text generation model), respectively. To introduce more input information into the model to facilitate the generation of SQL statements, this embodiment divides the SQL generation task into two stages: first, generating SQL statements without values, and then generating values ​​to fill the corresponding positions to form a complete standard SQL query statement. To achieve this goal, an SQL (no value) generation model and a value filling model are introduced to handle these two stages respectively. After the model outputs the SQL statement, the named entity recognition model replaces the named entities with those in the original question text to obtain the target SQL statement (select count(user_id)from broadband subscription details-month table where billing period=202303 and jt_id='X channel'), thereby improving the accuracy of the overall model.

[0057] In one feasible embodiment, the question text data is augmented question text data. Before constructing the training sets corresponding to the table selection model and the column selection model, the SQL statement generation method may further include: Step B10: Collect the original question text data; Step B20: Randomly replace the database values ​​in the original question text data to obtain enhanced question text data; and / or, Step B30 involves updating the column names in the original question text data that exist in the preset database to obtain enhanced question text data. The update method includes adding, deleting, or replacing; and / or... Step B40: Generate text with the same semantic scope as the original question text data to obtain enhanced question text data; The augmented question text data is used to construct the first and second training sets.

[0058] Understandably, due to the personal information and trade secrets involved in the original question text data, obtaining a large number of operator text-SQL pairs is difficult. Therefore, the existing question-SQL pairs are limited datasets manually accumulated by internal developers. To improve model performance, this application's embodiments employ three optional data augmentation methods: First, replacing keywords in the original question text by replacing database values ​​present in the question text; second, adding, deleting, or replacing column names in the text by replacing existing database column names in the input to increase data diversity; and third, introducing SimBERT (a semantic similarity model based on BERT) for similar text generation: inputting the question text into SimBERT to generate multiple texts semantically similar to the original question, thereby expanding the dataset and achieving data augmentation.

[0059] The aforementioned three data augmentation methods were used to improve model performance: random keyword replacement in the question text, random addition, deletion, or replacement of column names in the text, and the introduction of similar text through simBERT. These methods made the model more robust to different questions and expressions, better adapting to diverse inputs and improving its generalization ability, thus providing more reliable support for practical applications. Furthermore, the limitations of model performance caused by limited data were successfully overcome, significantly improving the model's accuracy.

[0060] Based on the foregoing embodiments, the SQL statement generation method of this application can be applied to a feasible SQL statement generation system, the functional modules of which are as follows: Figure 3As shown in the diagram. The dataset construction module includes the construction of a table selection dataset (i.e., the first training set, used to train the table selection model), a column selection dataset (i.e., the second training set, used to train the column selection model), an SQL generation dataset (used to train the SQL generation model), and a value imputation dataset (used to train the text generation model). The data augmentation module enriches the limited dataset accumulated manually by internal developers, making the trained model more generalizable and its performance more stable. The named entity recognition module identifies and replaces named entities in the question text using the named entity recognition model, including replacing named entities in the original question text and replacing them back in the second SQL statement output by the named entity recognition module to obtain the target SQL statement. The text conversion module desensitizes privacy-related data in the dataset and divides the dataset into training and test sets. The result storage module stores the results of each model (table selection model, column selection model, SQL generation model, text generation model) and the named entity recognition module, including the original question text, the replaced question text, table names, column names, the first SQL statement, the second SQL statement, and the target SQL statement.

[0061] In practical applications, the SQL statement generation system, which combines the aforementioned pre-trained models, can receive user-input text and sequentially generate corresponding standard SQL query statements through table selection, column selection, SQL statement generation, and value filling tasks. In carrier application scenarios, especially with small datasets, the accuracy of the SQL statements generated in this embodiment can reach up to 95.6%. By using data augmentation methods to enhance the dataset, the accuracy of the task was significantly improved by 12.5%. Furthermore, in carrier application scenarios, the SQL statement generation system of this embodiment has been successfully applied to solve complex query requests, further improving automation and accuracy. This means that when addressing the needs of the carrier industry, this system not only provides high-precision SQL query statement generation but also effectively improves system performance through data augmentation and other means, providing strong support for solving practical business problems.

[0062] It should be noted that the above examples are only for understanding this application and do not constitute a limitation on the SQL statement generation method of this application. Any simple transformations based on this technical concept are within the protection scope of this application.

[0063] This application also provides an SQL statement generation apparatus, specifically, referring to... Figure 4 The SQL statement generation device includes at least: The entity replacement module 10 is used to update each first named entity in the original problem text with the corresponding second named entity in the preset database to obtain the replaced problem text. The table and column selection module 20 is used to match the table name and column name corresponding to the replacement question text in a preset database according to the preset table selection model and column selection model. The statement generation module 30 is used to input the table name, the column name and the replacement question text into a preset SQL generation model to obtain a first SQL statement, wherein the first SQL statement does not carry a query value; The value filling module 40 is used to input the first SQL statement and the replacement question text into a preset text generation model to determine the second SQL statement, wherein the second SQL statement carries a query value; The entity restoration module 50 is used to replace the corresponding first named entities in the original question text with each second named entity in the second SQL statement to obtain the target SQL statement.

[0064] In one embodiment, the entity replacement module 10 is further configured to: The first named entities in the original question text are identified by a preset named entity model, wherein the types of the first named entities include at least table name, column name and organization name; Match the second named entities corresponding to each of the first named entities in the preset database; The first named entity in the original question text is replaced with the corresponding second named entity to obtain the replaced question text.

[0065] In one embodiment, the table column selection module 20 is further configured to: The replacement question text is input into a pre-trained table selection model, which matches the replacement question text with tables in a preset database to obtain the table name corresponding to the replacement question text. The replacement question text and the table name are input into a pre-trained column selection model. The column selection model matches the replacement question text with the column corresponding to the table name in a preset database to obtain the column name corresponding to the replacement question text.

[0066] In one embodiment, the value filling module 40 is further configured to: Input the first SQL statement and the replacement question text into a preset text generation model, and output at least one query value through the text generation model; The query values ​​are filled into the first SQL statement to obtain the second SQL statement.

[0067] In one embodiment, the SQL statement generation apparatus further includes a model training module, which is used for: Obtain the question text data and the corresponding SQL statement data to construct a first training set, wherein the first training set includes at least the question text, table name, and real label; Based on the first training set, the preset BERT pre-trained model is fine-tuned to obtain the table selection model, wherein the table selection model is used to determine whether the input question text data includes the input table name. Obtain the question text data and the corresponding SQL statement data to construct a second training set, wherein the second training set includes at least the question text, table name, column name and column type; Based on the second training set, the preset BERT pre-trained model is fine-tuned to obtain a column selection model. The column selection model is used to mark the column names in the question text according to the question text data, the table name corresponding to the question text data, and the column name and column type corresponding to the table name.

[0068] In one embodiment, the question text data is augmented question text data, and the model training module is further used for: Collect the original question text data; Randomly replace the database values ​​in the original question text data to obtain enhanced question text data; and / or, The column names existing in the preset database in the original question text data are updated to obtain enhanced question text data, wherein the updating method is to add, delete, or replace; and / or, Generate text with the same semantic scope as the original question text data to obtain enhanced question text data; The enhanced question text data is used to construct the first training set and the second training set.

[0069] The SQL statement generation apparatus provided in this application, employing the SQL statement generation method described in the above embodiments, can solve the technical problem of poor practicality in current SQL statement conversion methods. Compared with the prior art, the beneficial effects of the SQL statement generation apparatus provided in this application are the same as those of the SQL statement generation method provided in the above embodiments, and other technical features in this SQL statement generation apparatus are the same as those disclosed in the previous embodiment method, and will not be repeated here.

[0070] This application provides an electronic device, which includes: at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor, the instructions being executed by the at least one processor to enable the at least one processor to execute the SQL statement generation method in the above embodiments.

[0071] The following is for reference. Figure 5 The diagram illustrates a structural schematic of an electronic device suitable for implementing embodiments of this application. The electronic devices in these embodiments may include, but are not limited to, mobile terminals such as mobile phones, laptops, digital broadcast receivers, PDAs (Personal Digital Assistants), PADs (Portable Application Descriptions), PMPs (Portable Media Players), in-vehicle terminals (e.g., in-vehicle navigation terminals), and fixed terminals such as digital TVs and desktop computers. Figure 5 The electronic device shown is merely an example and should not impose any limitation on the functionality and scope of use of the embodiments of this application.

[0072] like Figure 5 As shown, the electronic device may include a processing unit 1001 (e.g., a central processing unit, a graphics processing unit, etc.), which can perform various appropriate actions and processes according to a program stored in a read-only memory (ROM) 1002 or a program loaded from a storage device 1003 into a random access memory (RAM) 1004. The RAM 1004 also stores various programs and data required for the operation of the electronic device. The processing unit 1001, ROM 1002, and RAM 1004 are interconnected via a bus 1005. An input / output (I / O) interface 1006 is also connected to the bus. Typically, the following systems can be connected to the I / O interface 1006: input devices 1007 including, for example, a touchscreen, touchpad, keyboard, mouse, image sensor, microphone, accelerometer, gyroscope, etc.; output devices 1008 including, for example, a liquid crystal display (LCD), speaker, vibrator, etc.; storage devices 1003 including, for example, magnetic tape, hard disk, etc.; and communication devices 1009. Communication device 1009 allows electronic devices to communicate wirelessly or wiredly with other devices to exchange data. While electronic devices with various systems are shown in the figures, it should be understood that implementation or possession of all the systems shown is not required. More or fewer systems may be implemented alternatively.

[0073] Specifically, according to the embodiments disclosed in this application, the processes described above with reference to the flowcharts can be implemented as computer software programs. For example, embodiments disclosed in this application include a computer program product comprising a computer program carried on a computer-readable medium, the computer program containing program code for performing the methods shown in the flowcharts. In such embodiments, the computer program can be downloaded and installed from a network via a communication device, or installed from storage device 1003, or installed from ROM 1002. When the computer program is executed by processing device 1001, it performs the functions defined in the methods of the embodiments disclosed in this application.

[0074] The electronic device provided in this application, employing the SQL statement generation method described in the above embodiments, can solve the technical problem of poor practicality in current SQL statement conversion methods. Compared with the prior art, the beneficial effects of the electronic device provided in this application are the same as those of the SQL statement generation method provided in the above embodiments, and other technical features of this electronic device are the same as those disclosed in the previous embodiment method, and will not be repeated here.

[0075] It should be understood that the various parts disclosed in this application can be implemented using hardware, software, firmware, or a combination thereof. In the description of the above embodiments, specific features, structures, materials, or characteristics can be combined in any suitable manner in one or more embodiments or examples.

[0076] The above description is merely a specific embodiment of this application, but the scope of protection of this application is not limited thereto. Any variations or substitutions that can be easily conceived by those skilled in the art within the scope of the technology disclosed in this application should be included within the scope of protection of this application. Therefore, the scope of protection of this application should be determined by the scope of the claims.

[0077] This application provides a computer-readable storage medium having computer-readable program instructions (i.e., a computer program) stored thereon, the computer-readable program instructions being used to execute the SQL statement generation method in the above embodiments.

[0078] The computer-readable storage medium provided in this application may be, for example, a USB flash drive, but is not limited to, electrical, magnetic, optical, electromagnetic, infrared, or semiconductor systems, devices, or any combination thereof. More specific examples of computer-readable storage media may include, but are not limited to: electrical connections having one or more wires, portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fiber, portable compact disk read-only memory (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination thereof. In this embodiment, the 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, system, or device. The program code contained on the computer-readable storage medium may be transmitted using any suitable medium, including but not limited to: wires, optical cables, RF (Radio Frequency), etc., or any suitable combination thereof.

[0079] The aforementioned computer-readable storage medium may be included in an electronic device or may exist independently without being assembled into an electronic device.

[0080] The aforementioned computer-readable storage medium carries one or more programs. When the aforementioned one or more programs are executed by an electronic device, the electronic device causes the following: the electronic device updates each first named entity in the original question text with the corresponding second named entity in a preset database to obtain a replacement question text; the electronic device matches the table name and column name corresponding to the replacement question text in the preset database according to a preset table selection model and a preset column selection model; the electronic device inputs the table name, the column name, and the replacement question text into a preset SQL generation model to obtain a first SQL statement, wherein the first SQL statement does not carry a query value; the electronic device inputs the first SQL statement and the replacement question text into a preset text generation model to determine a second SQL statement, wherein the second SQL statement carries a query value; and the electronic device replaces the corresponding first named entity in the original question text with each second named entity in the second SQL statement to obtain a target SQL statement.

[0081] Computer program code for performing the operations of this application can be written in one or more programming languages ​​or a combination thereof, including object-oriented programming languages ​​such as Java, Smalltalk, and C++, and conventional procedural programming languages ​​such as the "C" language or similar programming languages. The program code can be executed entirely on the user's computer, partially on the user's computer, as a standalone software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In cases involving remote computers, the remote computer can be connected to the user's computer via any type of network—including a Local Area Network (LAN) or a Wide Area Network (WAN)—or can be connected to an external computer (e.g., via the Internet using an Internet service provider).

[0082] The flowcharts and block diagrams in the accompanying drawings illustrate the architecture, functionality, and operation of possible implementations of systems, methods, and computer program products according to various embodiments of this application. In this regard, each block in a flowchart or block diagram may represent a module, segment, or portion of code containing one or more executable instructions for implementing a specified logical function. It should also be noted that in some alternative implementations, the functions indicated in the blocks may occur in a different order than those indicated in the drawings. For example, two consecutively indicated blocks may actually be executed substantially in parallel, and they may sometimes be executed in reverse order, depending on the functions involved. It should also be noted that each block in the block diagrams and / or flowcharts, and combinations of blocks in the block diagrams and / or flowcharts, can be implemented using a dedicated hardware-based system that performs the specified function or operation, or using a combination of dedicated hardware and computer instructions.

[0083] The modules described in the embodiments of this application can be implemented in software or hardware. The names of the modules do not necessarily limit the functionality of the unit itself.

[0084] The readable storage medium provided in this application is a computer-readable storage medium that stores computer-readable program instructions (i.e., a computer program) for executing the above-described SQL statement generation method, which can solve the technical problem of poor practicality in current SQL statement conversion methods. Compared with the prior art, the beneficial effects of the computer-readable storage medium provided in this application are the same as the beneficial effects of the SQL statement generation method provided in the above embodiments, and will not be repeated here.

[0085] This application also provides a computer program product, including a computer program that, when executed by a processor, implements the steps of the SQL statement generation method described above.

[0086] The computer program product provided in this application can solve the technical problem of poor practicality in current SQL statement conversion methods. Compared with the prior art, the beneficial effects of the computer program product provided in this application are the same as those of the SQL statement generation method provided in the above embodiments, and will not be repeated here.

[0087] The above description is only a part of the embodiments of this application and does not limit the patent scope of this application. All equivalent structural transformations made under the technical concept of this application and using the contents of the specification and drawings of this application, or direct / indirect applications in other related technical fields, are included in the patent protection scope of this application.

Claims

1. A method for generating SQL statements, characterized in that, The SQL statement generation method includes: Update each first named entity in the original question text with the corresponding second named entity in the preset database to obtain the replaced question text; Based on the preset table selection model and column selection model, match the table name and column name corresponding to the replacement question text in the preset database; Input the table name, the column name, and the replacement question text into a preset SQL generation model to obtain a first SQL statement, wherein the first SQL statement does not carry a query value; Input the first SQL statement and the replacement question text into a preset text generation model to determine the second SQL statement, wherein the second SQL statement carries a query value; Replace the corresponding first named entities in the original question text with the second named entities in the second SQL statement to obtain the target SQL statement.

2. The SQL statement generation method as described in claim 1, characterized in that, The step of updating each first named entity in the original question text with the corresponding second named entity in the preset database to obtain the replaced question text includes: The first named entities in the original question text are identified by a preset named entity model, wherein the types of the first named entities include at least table name, column name and organization name; Match the second named entities corresponding to each of the first named entities in the preset database; The first named entity in the original question text is replaced with the corresponding second named entity to obtain the replaced question text.

3. The SQL statement generation method as described in claim 1, characterized in that, The step of matching the table name and column name corresponding to the replacement question text in a preset database according to a preset table selection model and column selection model includes: The replacement question text is input into a pre-trained table selection model, which matches the replacement question text with tables in a preset database to obtain the table name corresponding to the replacement question text. The replacement question text and the table name are input into a pre-trained column selection model. The column selection model matches the replacement question text with the column corresponding to the table name in a preset database to obtain the column name corresponding to the replacement question text.

4. The SQL statement generation method as described in claim 1, characterized in that, The step of inputting the first SQL statement and the replacement question text into a preset text generation model to determine the second SQL statement includes: Input the first SQL statement and the replacement question text into a preset text generation model, and output at least one query value through the text generation model; The query values ​​are filled into the first SQL statement to obtain the second SQL statement.

5. The SQL statement generation method as described in claim 1, characterized in that, Before the step of matching the table name and column name corresponding to the replacement question text in a preset database according to a preset table selection model and column selection model, the method further includes: Obtain the question text data and the corresponding SQL statement data to construct a first training set, wherein the first training set includes at least the question text, table name, and real label; Based on the first training set, the preset BERT pre-trained model is fine-tuned to obtain the table selection model, wherein the table selection model is used to determine whether the input question text data includes the input table name. Obtain the question text data and the corresponding SQL statement data to construct a second training set, wherein the second training set includes at least the question text, table name, column name and column type; Based on the second training set, the preset BERT pre-trained model is fine-tuned to obtain a column selection model. The column selection model is used to mark the column names in the question text according to the question text data, the table name corresponding to the question text data, and the column name and column type corresponding to the table name.

6. The SQL statement generation method as described in claim 5, characterized in that, The question text data is enhanced question text data, and the SQL statement generation method further includes: Collect the original question text data; Randomly replace the database values ​​in the original question text data to obtain enhanced question text data; and / or, The column names existing in the preset database in the original question text data are updated to obtain enhanced question text data, wherein the updating method is to add, delete, or replace; and / or, Generate text with the same semantic scope as the original question text data to obtain enhanced question text data; The enhanced question text data is used to construct the first training set and the second training set.

7. An SQL statement generation device, characterized in that, The SQL statement generation device includes: The entity replacement module is used to update each first named entity in the original problem text with the corresponding second named entity in the preset database to obtain the replaced problem text. The table and column selection module is used to match the table name and column name corresponding to the replacement question text in a preset database according to the preset table selection model and column selection model. The statement generation module is used to input the table name, the column name and the replacement question text into a preset SQL generation model to obtain a first SQL statement, wherein the first SQL statement does not carry a query value; The value filling module is used to input the first SQL statement and the replacement question text into a preset text generation model to determine the second SQL statement, wherein the second SQL statement carries a query value; The entity restoration module is used to replace the corresponding first named entities in the original question text with each second named entity in the second SQL statement to obtain the target SQL statement.

8. An electronic device, characterized in that, The device includes: a memory, a processor, and a computer program stored in the memory and executable on the processor, the computer program being configured to implement the steps of the SQL statement generation method as described in any one of claims 1 to 6.

9. A storage medium, characterized in that, The storage medium is a computer-readable storage medium, and a computer program is stored on the storage medium. When the computer program is executed by a processor, it implements the steps of the SQL statement generation method as described in any one of claims 1 to 6.

10. A computer program product, characterized in that, The computer program product includes a computer program that, when executed by a processor, implements the steps of the SQL statement generation method as described in any one of claims 1 to 6.

Images