Method and device for converting query text into SQL
Through semantic recognition and automated conversion technology, users' natural language query text is converted into SQL statements, which solves the problem that users find it difficult to use SQL to query databases and improves query efficiency and convenience.
Patent Information
- Application Number
- CN202510005107.3
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2025-01-02
- Publication Date
- 2025-05-02
AI Technical Summary
It is difficult for users to query database data through SQL, especially for users who lack database knowledge. Learning and understanding the SQL query language, complex table structures, and field meanings is a difficult task.
Provide a method of query text to SQL. Through the semantic recognition module, it analyzes query text, extracts indicators, filters and aggregation methods, combines the field matching module, time rewriting module and reordering module to automatically and intelligently convert query text into executable SQL statements.
It reduces the difficulty of users querying databases through SQL, improves user query efficiency, and achieves the effect that non-technical users can easily retrieve required information from the database.
Smart Images
Figure CN119917520A_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the technical field of data processing, and in particular to a method and device for converting query text into SQL. Background Art
[0002] With the development of digital transformation of enterprises and the rapid development of technologies such as big data and cloud computing, the amount of data stored by enterprises is growing at an alarming rate. Most of this data is stored in the database in a structured form, and users interact with the data through structured query language (SQL). However, for ordinary users, especially those who lack database knowledge, learning and understanding SQL query language, complex table structure, and field meaning is a difficult task.
[0003] Therefore, in the related art, there is a problem that it is difficult for users to query data in the database through SQL. Summary of the invention
[0004] The embodiment of the present invention provides a method and device for converting a query text into SQL, so as to at least solve the problem in the related art that it is difficult for a user to query data in a database through SQL.
[0005] According to one embodiment of the present invention, a method for converting query text into SQL is provided, comprising: retrieving the content of indicators, the content of filtering conditions, and the content of aggregation methods based on the query text; querying the storage content of a database based on the content of the indicators and the content of the filtering conditions; modifying the time-related vocabulary in the query text to a preset standard format based on the content of the aggregation method to match the storage content of the database; based on the context of the query text, determining the indicators and reports in the storage content that are closest to the content of the indicators and the content of the filtering conditions as recognition results; based on the recognition results, combining and executing SQL statements to retrieve the query results.
[0006] According to another embodiment of the present application, a device for converting query text to SQL is provided, including: a semantic recognition module, which is used to retrieve the content of the indicator, the content of the filtering condition, and the content of the aggregation method based on the query text; a field matching module, which is used to query the storage content of the database based on the content of the indicator and the content of the filtering condition; a time rewriting module, which is used to modify the time-related vocabulary in the query text to a preset standard format based on the content of the aggregation method to match the storage content of the database; a reordering module, which is used to determine the indicators and reports that are closest to the content of the indicator and the content of the filtering condition in the storage content as recognition results based on the context of the query text; and a SQL conversion module, which is used to combine and execute SQL statements based on the recognition results to retrieve the query results.
[0007] According to another embodiment of the present application, a computer-readable storage medium is provided, in which a computer program is stored, wherein the computer program is configured to execute the steps of any of the above method embodiments when running.
[0008] According to another embodiment of the present application, an electronic device is provided, including a memory and a processor, wherein the memory stores a computer program, and the processor is configured to run the computer program to execute the steps in any one of the above method embodiments.
[0009] According to another embodiment of the present application, a computer program product is provided, including computer instructions, which implement the steps in any of the above method embodiments when executed by a processor.
[0010] Through one of the embodiments of the present invention, by automating and intelligentizing the complex database query process, the problem of difficulty in querying database data through SQL in related technologies is solved, thereby achieving the effect of improving user query efficiency. BRIEF DESCRIPTION OF THE DRAWINGS
[0011] The drawings described herein are used to provide a further understanding of the present invention and constitute a part of this application. The exemplary embodiments of the present invention and their descriptions are used to explain the present invention and do not constitute an improper limitation of the present invention. In the drawings:
[0012] Figure 1 is a flowchart of a method for converting query text to SQL according to an embodiment of the present invention;
[0013] Figure 2 is a flow chart of a method for querying stored content in a database based on content of an indicator and content of a screening condition according to an embodiment of the present invention;
[0014] Figure 3 is a flow chart of a method using a first model, a second model, and an autoregressive large model according to an embodiment of the present invention;
[0015] Figure 4 is a flowchart of a method for calculating the loss of the first model or the second model output according to an embodiment of the present invention;
[0016] Figure 5 It is a structural block diagram of a device for converting query text to SQL according to an embodiment of the present application. DETAILED DESCRIPTION
[0017] The present invention will be described in detail below with reference to the accompanying drawings and in combination with embodiments. It should be noted that the embodiments and features in the embodiments of the present application can be combined with each other without conflict.
[0018] It should be noted that the terms "first", "second", etc. in the specification and claims of the present invention and the above-mentioned drawings are used to distinguish similar objects, and are not necessarily used to describe a specific order or sequence.
[0019] In this embodiment, a method for converting query text into SQL is provided. Figure 1 is a flowchart of a method for converting query text to SQL according to an embodiment of the present invention. Figure 1 As shown, the method includes:
[0020] Step S101, retrieve the content of the index, the content of the screening condition, and the content of the aggregation method based on the query text;
[0021] In an exemplary implementation, for example, in the case of obtaining a query text input by a customer, and the content of the query text is: "What is the performance change of Wang San of Harbin Sub-branch of Heilongjiang Branch in the first half of the year?", then the indicators, screening conditions, and aggregation methods are obtained based on the text. Among them, the content of the indicator can be: "Performance"; the content of the screening condition can be: "Time: First half of the year, Account Manager: Wang San, Institution: Harbin Sub-branch of Heilongjiang Branch"; the content of the aggregation method can be: "Time span".
[0022] Step S102, querying the storage content of the database based on the content of the indicator and the content of the screening condition;
[0023] In an exemplary implementation, the names related to the content of the indicator and the content of the screening condition are searched in the database. For example, for the performance situation to be queried, the field name stored in the real table in the database is "performance appraisal", and the name of the report where the indicator is located is obtained based on the content of the indicator, such as the "performance appraisal" indicator is in the "appraisal report". Similarly, the names of the various screening conditions in the database table are matched to facilitate the subsequent execution of SQL.
[0024] Step S103, based on the content in the aggregation mode, modifying the vocabulary related to time in the query text into a preset standard format to match the storage content of the database;
[0025] In an exemplary implementation, for example, if the time word "first half of the year" is identified in the query text, the first half of the year is converted into specific months, for example, "first half of the year" is converted into "202401, 202402, 202403, 202404, 202405, 202406". And the relevant fields of "202401, 202402, 202403, 202404, 202405, 202406" are searched in the database.
[0026] Step S104, based on the context of the query text, determine the indicators and reports that are closest to the content of the indicators and the content of the screening conditions in the stored content as the recognition results;
[0027] In an exemplary implementation, for example, in step S102, the fuzzy matching reports and indicators may include: 1. Organization assessment form: performance assessment; 2. Personal assessment form: performance assessment. In step S104, the context of the query text is identified. For example, if the query finds that there is a field "Wang San's first half performance" in the text, the selection of the personal assessment form is confirmed. Therefore, the user does not need to emphasize which report he is looking for, and the method can automatically determine the inference output for the user.
[0028] Or, if the query text is: "Wang San's assessment last month", then in step S101, the time is identified as: "last month", and in step S104, the time type is identified as: single time in combination with the context. Then, in step S103, "last month" is converted into a specific year, month, and day, for example: "August 31, 2024".
[0029] Or, if the query text is: "The punch-in data distribution of Wang San last month", then in step S101, the time is identified as: "last month", and in step S104, the time type is identified as: time period in combination with the context. Then, in step S103, "last month" is converted into a period of time, for example: "August 1, 2024 to August 31, 2024".
[0030] Step S105, based on the recognition result, combine and execute SQL statements to retrieve the query result.
[0031] In an exemplary implementation, for example, a table named employee_performance is stored in the database, which contains the employee's name, branch, sub-branch, performance score, and timestamp of the score.
[0032] The machine execution code examples of step S101 to step S105 are as follows:
[0033]
[0034] Based on the above statement, the following steps are executed:
[0035] 1. Select the employee's name, branch, sub-branch, performance score, and month of performance score.
[0036] 2. Filter out the records whose name is "Wang San", branch is "Harbin Branch", and branch is "Heilongjiang Branch" from the employee_performance table.
[0037] 3. The query time range is limited to the first half of 2024 (January to June).
[0038] 4. Sort by the month of performance rating and display the sorting results.
[0039] Through the above steps, by automating and intelligentizing the complex database query process, the problem of difficulty in querying database data through SQL in related technologies is solved, thereby achieving the effect of improving user query convenience.
[0040] In one embodiment, Figure 2 is a flow chart of a method for querying stored content in a database based on content of an indicator and content of a screening condition according to an embodiment of the present invention. Figure 2 As shown, the storage content of the database is queried based on the content of the indicator and the content of the screening condition, including:
[0041] Step S201, obtaining at least one candidate set from the stored content based on the BM25 algorithm and the Bi-Encoder neural network model;
[0042] In an exemplary implementation, in this step, the BM25 algorithm can be used to perform preliminary document retrieval and sorting on the query text. The BM25 algorithm is a probability-based retrieval model that evaluates the relevance between the document and the query by calculating the frequency of the query terms in the document and the vocabulary distribution in the document. For example, if the query text is "Performance changes of Wang San of Harbin Branch of Heilongjiang Branch in the first half of the year", the BM25 algorithm will find the most relevant document set in the database based on this query.
[0043] Next, the Bi-Encoder neural network model is used to encode these documents. The Bi-Encoder model encodes the input sequence into a fixed-length vector representation through two independent encoders, and then evaluates the relevance of the document to the query by calculating the similarity between the vectors. For example, the Bi-Encoder model encodes the query text and the documents in the database into vectors respectively, and then finds the document vector that is most similar to the query vector to form a candidate set.
[0044] Step S202, pairing the content of the indicator, the content of the screening condition and at least one candidate set into paired data points;
[0045] In an exemplary implementation, for example, after obtaining the candidate set, the indicator content and the screening condition content in the query text are paired with the documents in the candidate set to form paired data points. For example, the indicator content in the query text is "performance change", and the screening condition content is "time: first half of the year, account manager: Wang San, institution: Harbin branch of Heilongjiang branch". These contents are paired with the documents in the candidate set to form a data point pair containing the query condition and the document content.
[0046] Step S203: reorder the paired data points based on the Cross-Encoder model to output a target candidate set.
[0047] In an exemplary embodiment, for example, a Cross-Encoder model is used to reorder these paired data points. The Cross-Encoder model can encode two texts at the same time and generate a value representing the similarity of the input pair. In this step, the Cross-Encoder model considers the content of the query text and the candidate documents at the same time, generates a similarity score, and then reorders the candidate set based on the score, outputting the most relevant documents as the target candidate set.
[0048] For example, if there are two candidate documents A and B, the Cross-Encoder model calculates the similarity scores between the query text and A and B, and then sorts A and B according to these scores, selecting the most relevant document as the final query result.
[0049] Through steps S201 to S203, the documents most relevant to the query text can be automatically and intelligently retrieved and sorted from the database, thereby improving the efficiency and accuracy of the query.
[0050] In one embodiment, Figure 3 is a flow chart of a method using a first model, a second model, and an autoregressive large model according to an embodiment of the present invention. Figure 3 As shown, the method also includes:
[0051] Step S301, using the first model to retrieve the content of the index, the content of the screening condition, and the content of the aggregation method based on the query text;
[0052] In an exemplary implementation, for example, the query text input by the user is: "What is the performance change of Wang San of Harbin Sub-branch of Heilongjiang Branch in the first half of the year?" The task of the first model is to parse the query text and extract key information.
[0053] The following is an exemplary implementation process:
[0054] 1. Text preprocessing: perform preprocessing operations such as word segmentation and stop word removal on the query text.
[0055] 2. Entity recognition: Use named entity recognition (NER) technology to identify entities in the text, such as "Heilongjiang Branch", "Harbin Branch", "Wang San" and "first half of the year".
[0056] 3. Semantic recognition: Semantic recognition technology is used to determine that what the user wants to query is "performance changes", that is, the indicator content.
[0057] 4. Extraction of screening conditions: Extract the screening conditions, including "Time: First half of the year", "Account manager: Wang San" and "Institution: Harbin Branch of Heilongjiang Branch".
[0058] 5. Aggregation method identification: Identify that the user wants to aggregate performance data according to "time span".
[0059] Step S302, using the second model based on the context of the query text, determining the indicators and reports that are closest to the content of the indicators and the content of the screening conditions in the stored content as the recognition results;
[0060] In an exemplary implementation, for example, there may be multiple reports and indicators in the database, and the second model needs to determine the most appropriate report and indicator based on the content extracted in the first step.
[0061] The following is an exemplary implementation process:
[0062] 1. Semantic matching: Use semantic matching technology, such as Bi-Encoder or Cross-Encoder models, to match the extracted indicator content and screening conditions with the reports and indicators in the database.
[0063] 2. Candidate set generation: Generate a candidate set containing reports and indicators that may be related to the query text.
[0064] 3. Context analysis: Analyze the context of the query text. For example, since the query mentions the specific "Account Manager: Wang San", the model will tend to select reports related to personal performance.
[0065] 4. Final matching: Select the report and indicators most relevant to the query text from the candidate set, for example, select the "Performance Appraisal" indicator in the "Personal Appraisal Form".
[0066] Step S303: Use the autoregressive large model to adjust and train the first model and the second model.
[0067] In an exemplary embodiment, for example, in order to improve the accuracy and robustness of the model, the first model and the second model need to be continuously trained and adjusted.
[0068] The following is an exemplary implementation process:
[0069] 1. Model training: Use the annotated query text and the corresponding correct SQL statements as training data to train the autoregressive large model.
[0070] 2. Loss calculation: During the training process, the loss value between the SQL statement output by the model and the actual SQL statement is calculated.
[0071] 3. Parameter adjustment: Adjust the model parameters according to the loss value to minimize the prediction error.
[0072] 4. Model optimization: Through iterative training, the performance of the model is continuously optimized to improve its understanding of query text and the accuracy of SQL statement generation.
[0073] Through step S301 to step S303, an automated method for converting query text into SQL can be implemented to improve the efficiency and accuracy of database query.
[0074] In an exemplary embodiment, the first model and the second model can be a neural network model or a machine learning model, which can be determined based on actual conditions. The first model and the second model can be large models that are customized and developed.
[0075] In one embodiment, the first model and the second model include a multi-layer Transformer decoder module to improve the accuracy of the recognition result of the first network model or the recognition result of the second model based on the Transformer decoder module.
[0076] In an exemplary implementation, for example, the number of layers of the Transformer decoder module may be 32 layers.
[0077] The following is an exemplary implementation process:
[0078] 1. Multi-layer structure: The first model and the second model are constructed by stacking multiple Transformer decoder modules. Each module contains sub-layers such as self-attention mechanism and feedforward neural network, which can capture and process complex relationships and patterns in sequence data.
[0079] 2. Self-attention mechanism: The self-attention layer in each Transformer decoder module can capture the dependencies between any two positions in the input sequence, which is particularly important for understanding long-distance dependencies in the query text.
[0080] 3. Encoding segment information: Transformer can achieve six layers through the encoding segment or decoding end, which can capture hierarchical information and improve the model's ability to understand complex queries.
[0081] 4. Enrichment and introduction of contextual information: Through the self-attention mechanism, the model can consider other words in the input sequence while processing a target word, thereby introducing rich contextual information.
[0082] 5. Non-linear representation: A fully connected layer (MLP) is used in each Transformer decoder module to learn the non-linear representation of the query text and enhance the expressiveness of the model.
[0083] The following technical effects can be achieved by using the Transformer decoder module:
[0084] 1. Improved parallel computing capabilities: Compared with recurrent neural networks (RNNs), the Transformer model has stronger parallel computing capabilities and can significantly speed up model training and reasoning.
[0085] 2. Improved ability to capture long-distance dependencies: Through the self-attention mechanism and multi-head attention mechanism, Transformer can capture long-distance dependencies in the input sequence and improve the performance of the model when processing long texts.
[0086] 3. Improved flexibility: The design of Transformer enables it to be flexibly applied to various NLP tasks, such as machine translation, text summarization, dialogue generation, etc.
[0087] 4. Improved accuracy: The multi-layer Transformer decoder module can improve the accuracy of the model’s recognition results because it can more deeply understand and process complex language structures and semantic information.
[0088] 5. Improvement of rich contextual information: Transformer introduces rich contextual information through the self-attention mechanism, which is crucial for understanding the context of the query text and generating accurate SQL statements.
[0089] In summary, by integrating the multi-layer Transformer decoder module in the first model and the second model, the accuracy of the model's understanding of the query text and the generation of SQL statements can be effectively improved, thereby improving the performance and effect of the entire query text to SQL method.
[0090] In one embodiment, each layer of the Transformer decoder module includes:
[0091] The normalization layer RMSNorm is used to normalize the query text;
[0092] In an exemplary embodiment, the RMSNorm layer normalizes the input query text vector. For example, for the query text "Performance change of Wang San in the first half of the year at Harbin Sub-branch, Heilongjiang Branch?", it is first converted into a numerical vector form that the model can understand, and then RMSNorm is applied.
[0093] For example, if the vector representation of the query text after passing through the embedding layer is X, the calculation of RMSNorm is as follows:
[0094]
[0095] where n is the dimension of the vector X, and X i is the i-th element in the vector X. In this way, the values of each dimension are scaled to have the same scale, which helps the model better process numerical values of different scales.
[0096] When processing the query text, the RMSNorm layer normalizes the entire query vector, so that the vector representations of words such as "Heilongjiang Branch", "Harbin Sub-branch", "Wang San", and "the first half of the year" are on the same scale, facilitating the processing of subsequent layers.
[0097] The self-attention layer Self-attention is used to consider other words in the input sequence while processing a target word, where the target word and other words are included in the same input sequence;
[0098] In an exemplary embodiment, the self-attention layer allows the model to consider the information of all other words in the text when processing each word in the query text. For example, when processing the word "Wang San", the self-attention mechanism calculates the degree of association between "Wang San" and words such as "Heilongjiang Branch", "Harbin Sub-branch", and "the first half of the year".
[0099] The calculation of the self-attention layer can be expressed as:
[0100]
[0101] where Q, K, and V are the query, key, and value matrices respectively, and d k is the dimension of the key vector. This calculation generates a weight distribution for each word, indicating the correlation between other words and the current word.
[0102] When processing the query text, the self-attention layer will identify the association between "Wang San" and "Performance change situation", as well as the connection between "the first half of the year" and the time-related aggregation method, so as to better understand the intention of the query.
[0103] The fully connected layer MLP is used to learn the nonlinear representation of the query text.
[0104] In an exemplary embodiment, the MLP layer performs a nonlinear transformation on the output of the self-attention layer. For example, if the self-attention layer outputs vector representations containing words such as "Heilongjiang Branch", "Harbin Branch", "Wang San", and "First Half of the Year", the MLP layer will further process these vectors through two linear transformations and a ReLU activation function.
[0105] In one embodiment, the fully connected layer MLP can be the MLP layer of the lora fine-tuning model.
[0106] Among them, the calculation of the MLP layer can be expressed as:
[0107] MLP(X) = ReLU(XW1+b1)W2+b2;
[0108] Among them, W1 and W2 are weight matrices, b1 and b2 are bias terms, and ReLU is the activation function.
[0109] The MLP layer performs a nonlinear transformation on each word vector output by the self-attention layer, enhancing the model's ability to capture complex relationships between words such as "Heilongjiang Branch", "Harbin Sub-branch", "Wang San", and "first half of the year", providing support for generating accurate SQL statements.
[0110] Through the collaborative work of the above normalization layer RMSNorm, self-attention layer, and fully connected layer MLP, the Transformer decoder module can effectively process the query text, understand its intent and context, and thus generate accurate SQL statements.
[0111] In one embodiment, Figure 4 is a flowchart of a method for calculating the loss of the first model or the second model output according to an embodiment of the present invention. Figure 4 As shown, the method also includes:
[0112] Step S401, calculating the loss value of the first model or the second model at each moment;
[0113] Step S402, calculating an average loss value based on the loss value at each moment, and using the average loss value as the loss output by the first model or the second model.
[0114] In one embodiment, the function for calculating the loss value of the first model or the second model at each moment is:
[0115]
[0116] Among them, t is the time, y t,i is the i-th prediction result output at time t, y' t,i is the predicted probability value of the i-th prediction result output at time t, and V is a subset in the vocabulary;
[0117] The function for calculating the average loss value is:
[0118]
[0119] Where T is a continuous training period and t is a moment in T.
[0120] In an exemplary embodiment, for a function of the loss value at each moment, for example, at a certain moment t, the model predicts a SQL statement that contains keywords in the query text, such as "Heilongjiang Branch", "Harbin Branch", "Wang San", "First Half of the Year", etc. The model assigns a probability value to each predicted word, indicating the possibility of the word appearing in the predicted SQL statement. If the SQL statement predicted by the model is significantly different from the actual SQL statement, then the predicted probability value y' t,i will be lower, so the loss value L t will be higher, indicating that the model needs to be adjusted to reduce this difference.
[0121] For the average loss function, during the training process, the model generates a series of prediction SQL statements, each of which is generated based on the query text "How did Wang San of Harbin Branch of Heilongjiang Branch perform in the first half of the year?" Each prediction statement will have a corresponding loss value L t , this loss value reflects the difference between the model's prediction at that moment and the actual SQL statement. By averaging the loss values at all moments, we get the average loss value L, which provides an overall indicator of the model's performance over the entire training period. If the average loss value is high, it means that the model has problems understanding the query text and generating accurate SQL statements, and further training and optimization are needed.
[0122] By calculating the loss value and average loss value at each moment, the performance of the model can be quantified, and the model parameters can be adjusted accordingly to minimize the prediction error and improve the accuracy of the model's understanding of the query text and the generation of SQL statements. This method helps to automate and intelligently process complex database query processes and improve the convenience of user queries.
[0123] Through the above method, the embodiment of the present invention has the following advantages:
[0124] Improved automation: Users can perform database queries without SQL knowledge, and the system automatically converts query text into SQL statements.
[0125] Improved intelligence: The system understands the intent and context of the query text and intelligently matches the data in the database, reducing the cognitive burden on users when making queries.
[0126] Improved accuracy: The model uses advanced technologies such as the Transformer decoder module and autoregressive large model to improve the accuracy of query text understanding and SQL statement generation.
[0127] Improved efficiency: Through automated and intelligent processing, query efficiency is improved and query time is reduced.
[0128] In summary, the embodiments of the present invention reduce the difficulty of users querying the database through SQL through automated and intelligent methods, improve the convenience of query, and enable non-technical users to easily retrieve required information from the database.
[0129] Through the description of the above implementation methods, those skilled in the art can clearly understand that the method according to the above embodiment can be implemented by adding the necessary general hardware platform with the help of software, and of course it can also be implemented by hardware, but in many cases the former is a better implementation method. Based on this understanding, the technical solution of the present application, or the part that contributes to the prior art, can be embodied in the form of a software product, which is stored in a storage medium (such as ROM / RAM, disk, CD), including a number of instructions for a terminal device (which can be a mobile phone, computer, server, or network device, etc.) to execute the methods described in each embodiment of the present application.
[0130] In this embodiment, a device for converting query text to SQL is also provided, which is used to implement the above embodiments and preferred implementation modes, and will not be repeated here. As used below, the term "module" can implement a combination of software and / or hardware for a predetermined function. Although the device described in the following embodiments is preferably implemented in software, the implementation of hardware, or a combination of software and hardware, is also possible and conceivable.
[0131] Figure 5 is a structural block diagram of a device for converting query text to SQL according to an embodiment of the present application, such as Figure 5 As shown, the device comprises:
[0132] Semantic recognition module 51, used to retrieve the content of indicators, the content of screening conditions, and the content of aggregation methods based on the query text;
[0133] In an exemplary implementation, the module is responsible for parsing the user's natural language query text and extracting key information, including indicator content, screening conditions, and aggregation methods. For example, the user's question "What is the performance change of Wang San in the first half of the year at Harbin Sub-branch of Heilongjiang Branch?" is decomposed into performance (indicator), Wang San and the first half of the year (screening conditions), and time span (aggregation method).
[0134] In this way, users do not need to understand the specific table structure and field names of the database. They only need to describe their query requirements in natural language, and the semantic recognition module can understand the user's intentions.
[0135] A field matching module 52 is used to query the storage content of the database based on the content of the indicator and the content of the screening condition;
[0136] In an exemplary embodiment, based on the content extracted by the semantic recognition module, the field matching module searches the database for fields and reports related to the user's query. This step converts the user's natural language requirements into query conditions that the database can understand.
[0137] In this way, users do not need to know the specific field names, the field matching module will automatically match and convert this information, greatly reducing the difficulty of user operation.
[0138] A time rewriting module 53, for modifying the words related to time in the query text into a preset standard format based on the content in the aggregation mode, so as to match the storage content of the database;
[0139] In an exemplary embodiment, the module specifically processes time terms in the query text and converts them into a standard format that the database can recognize, for example, converting "first half of the year" into a specific date range of "2024-01" to "2024-06".
[0140] In this way, users can use natural language to describe time when querying without having to understand the specific storage format of the time field in the database. The time rewriting module automatically completes this conversion.
[0141] A reordering module 54 is used to determine the indicators and reports that are closest to the content of the indicators and the content of the screening conditions in the stored content as the recognition results based on the context of the query text;
[0142] In an exemplary embodiment, based on the context of the query text, the re-ranking module determines the indicators and reports that best meet the user's needs. This module uses intelligent analysis to ensure that the most appropriate result is selected even when there are multiple possible matches.
[0143] In this way, users do not need to manually filter and select the most appropriate reports or indicators, the re-ranking module automatically completes this process.
[0144] The SQL conversion module 55 is used to combine and execute SQL statements based on the recognition results to retrieve the query results.
[0145] In an exemplary implementation, the SQL conversion module automatically generates and executes SQL statements based on the recognition results provided by the previous modules to retrieve the query results.
[0146] In this way, users do not need to write any SQL code. The SQL conversion module automates the entire query process and users only need to wait for the query results.
[0147] In summary, through the collaborative work of the above five modules, the device simplifies the complex database query process into a process where users only need to enter natural language query text. Users can easily obtain the required data without having SQL knowledge or understanding the database structure, which significantly improves the convenience of querying database data through SQL. This automated and intelligent method enables non-technical users to interact with the database efficiently, solving the difficulties in traditional technologies.
[0148] It should be noted that the semantic recognition module 51 may be the first model mentioned in the method embodiment, and the reordering module 54 may be the second model mentioned in the method embodiment.
[0149] In one embodiment, the device is also used to:
[0150] Obtain at least one candidate set from the storage content based on the BM25 algorithm and the Bi-Encoder neural network model;
[0151] Pairing the content of the indicator, the content of the filter condition and at least one candidate set into pairs of data points;
[0152] Pairs of data points are reordered based on the Cross-Encoder model to output target candidate sets.
[0153] In one embodiment, the device is also used to:
[0154] Using the first model to retrieve the content of the indicator, the content of the screening condition, and the content of the aggregation method based on the query text;
[0155] Using the second model based on the context of the query text, the indicators and reports that are closest to the content of the indicator and the content of the screening condition are determined in the stored content as the recognition results;
[0156] The first model and the second model are adjusted and trained using the autoregressive large model.
[0157] In one embodiment, the first model and the second model include a multi-layer Transformer decoder module to improve the accuracy of the recognition result of the first network model or the recognition result of the second model based on the Transformer decoder module.
[0158] In one embodiment, each layer of the Transformer decoder module includes:
[0159] Normalization layer RMSNorm, used to normalize the query text;
[0160] Self-attention layer, which is used to consider other words in the input sequence while processing a target word, where the target word and other words are included in the same input sequence;
[0161] The fully connected layer MLP is used to learn the nonlinear representation of the query text.
[0162] In one embodiment, the device is also used to:
[0163] Calculate the loss value of the first model or the second model at each moment;
[0164] The average loss value is calculated based on the loss value at each moment, so as to use the average loss value as the loss output by the first model or the second model.
[0165] In one embodiment, the function for calculating the loss value of the first model or the second model at each moment is:
[0166]
[0167] Among them, t is the time, y t,i is the i-th prediction result output at time t, y' t,i is the predicted probability value of the i-th prediction result output at time t, and V is a subset in the vocabulary;
[0168] The function for calculating the average loss value is:
[0169]
[0170] Where T is a continuous training period and t is a moment in T.
[0171] It should be noted that the query text-to-SQL conversion device can be a computing device similar to a computer terminal, which is integrated into the database system, as a part of the database system, or independent of the database system. The above modules can be implemented by software or hardware. For the latter, it can be implemented in the following ways, but not limited to: the above modules are all located in the same processor; or the above modules are located in different processors in any combination.
[0172] An embodiment of the present application further provides a computer-readable storage medium, in which a computer program is stored, wherein the computer program is configured to execute the steps of any of the above method embodiments when running.
[0173] In an exemplary embodiment, the computer-readable storage medium may include, but is not limited to, various media that can store computer programs, such as a USB flash drive, a read-only memory (ROM), a random access memory (RAM), a mobile hard disk, a magnetic disk or an optical disk.
[0174] An embodiment of the present application further provides an electronic device, including a memory and a processor, wherein a computer program is stored in the memory, and the processor is configured to run the computer program to execute the steps in any one of the above method embodiments.
[0175] In an exemplary embodiment, the electronic device may further include a transmission device and an input / output device, wherein the transmission device is connected to the processor, and the input / output device is connected to the processor.
[0176] An embodiment of the present application further provides a computer program product, including a computer program, which implements the steps in any of the above method embodiments when executed by a processor.
[0177] For specific examples in this embodiment, reference may be made to the examples described in the above embodiments and exemplary implementation modes, and this embodiment will not be described in detail herein.
[0178] Obviously, those skilled in the art should understand that the above modules or steps of the present application can be implemented by a general computing device, they can be concentrated on a single computing device, or distributed on a network composed of multiple computing devices, they can be implemented by a program code executable by a computing device, so that they can be stored in a storage device and executed by the computing device, and in some cases, the steps shown or described can be executed in a different order from that herein, or they can be made into individual integrated circuit modules, or multiple modules or steps therein can be made into a single integrated circuit module for implementation. Thus, the present application is not limited to any specific combination of hardware and software.
[0179] The above description is only the preferred embodiment of the present application and is not intended to limit the present application. For those skilled in the art, the present application may have various modifications and variations. Any modification, equivalent replacement, improvement, etc. made within the principles of the present application shall be included in the protection scope of the present application.
Claims
1. A method for converting query text into structured query language SQL, characterized in that: include: Retrieve the content of indicators, filtering conditions, and aggregation methods based on the query text; Querying the storage content of the database based on the content of the indicator and the content of the screening condition; Based on the content of the aggregation method, the vocabulary related to time in the query text is modified into a preset standard format to match the storage content of the database; Based on the context of the query text, determine the indicators and reports that are closest to the content of the indicator and the content of the screening condition in the stored content as the recognition results; Based on the recognition results, SQL statements are combined and executed to retrieve query results.
2. The method according to claim 1, characterized in that Querying the storage content of the database based on the content of the indicator and the content of the screening condition includes: Acquire at least one candidate set from the stored content based on the BM25 algorithm and the Bi-Encoder neural network model; Pairing the content of the indicator, the content of the screening condition and the at least one candidate set into paired data points; The paired data points are reordered based on a Cross-Encoder model to output a target candidate set.
3. The method according to claim 1, characterized in that: Also includes: Using the first model to retrieve the content of the indicator, the content of the screening condition, and the content of the aggregation method based on the query text; Using the second model based on the context of the query text, determine the indicators and reports that are closest to the content of the indicator and the content of the screening condition in the stored content as the recognition result; The first model and the second model are adjusted and trained using an autoregressive large model.
4. The method according to claim 3, characterized in that The first model and the second model include a multi-layer Transformer decoder module to improve the accuracy of the recognition result of the first network model or the recognition result of the second model based on the Transformer decoder module.
5. The method according to claim 4, characterized in that The Transformer decoder module of each layer includes: A normalization layer RMSNorm, used for normalizing the query text; A self-attention layer, for considering other words in an input sequence while processing a target word, wherein the target word and the other words are contained in the same input sequence; The fully connected layer MLP is used to learn the non-linear representation of the query text.
6. The method according to claim 3, characterized in that Also includes: Calculate the loss value of the first model or the second model at each moment; An average loss value is calculated based on the loss value at each moment, so as to use the average loss value as the loss output by the first model or the second model.
7. The method according to claim 6, characterized in that Also includes: The function for calculating the loss value of the first model or the second model at each moment is: Among them, t is the time, y t,i is the i-th prediction result output at time t, y' t,i is the predicted probability value of the i-th prediction result output at time t, and V is a subset in the vocabulary; The function for calculating the average loss value is: Where T is a continuous training period and t is a moment in T.
8. A device for converting query text into structured query language SQL, characterized in that: include: Semantic recognition module, used to retrieve the content of indicators, screening conditions, and aggregation methods based on the query text; A field matching module, used to query the storage content of the database based on the content of the indicator and the content of the screening condition; A time rewriting module, used for modifying the vocabulary related to time in the query text into a preset standard format based on the content of the aggregation method, so as to match the storage content of the database; A reordering module, for determining, based on the context of the query text, the indicators and reports in the stored content that are closest to the content of the indicator and the content of the screening condition as the recognition result; The SQL conversion module is used to combine and execute SQL statements based on the recognition results to retrieve query results.
9. A computer-readable storage medium, characterized in that: The storage medium stores a computer program, wherein the computer program executes the method described in any one of claims 1 to 7 when executed by a processor.
10. An electronic device comprising a memory and a processor, characterized in that: A computer program is stored in the memory, and the processor is configured to run the computer program to perform the method according to any one of claims 1 to 7.
11. A computer program product, comprising a computer program, characterized in that When the computer program is executed by a processor, the method according to any one of claims 1 to 7 is implemented.