Database query statement generation method and device, electronic equipment and storage medium

Abstract

This application relates to a method, apparatus, electronic device, and storage medium for generating database query statements. It converts natural language questions into question vectors, retrieves candidate options related to the question vectors from a pre-built multi-source vector index, and determines a comprehensive relevance score between each candidate option and the question vector. Based on the comprehensive relevance score and the complexity of the natural language question, it selects target candidate options from the candidate options and assembles the target candidate options with the natural language question into target prompt words. It then calls a large language model to generate database query statements based on the target prompt words. This allows users to directly submit query requests in everyday natural language; the comprehensive relevance score makes the search results more closely match the actual query logic, significantly improving the quality of the selected candidate options; and it filters target candidate options as needed from the candidate options based on complexity, reducing the processing cost of low-value candidate options and adapting to various query needs.

Description

Technical Field

[0001] This application relates to the field of computer technology, and in particular to a method, apparatus, electronic device and storage medium for generating database query statements. Background Technology

[0002] With the widespread application of data warehouses and data lakes in enterprises, business users' demand for self-service data queries is increasing. Existing technologies can generate corresponding SQL statements through large language models.

[0003] However, existing solutions mostly use static context injection, which is limited by the context window mechanism of large language models; and considering the single type of context leads to insufficient retrieval accuracy, poor quality of generated context, and lack of flexible processing mechanism for prompt word construction, resulting in low accuracy of generated SQL and inability to adapt to complex application environments. Summary of the Invention

[0004] This application provides a database query statement generation method, apparatus, electronic device, and storage medium to solve the technical problem in the prior art that the accuracy of relevance retrieval is insufficient, and it is unable to flexibly provide high-quality contextual information for large language models, resulting in low accuracy of the generated database query statements.

[0005] Firstly, this application provides a method for generating database query statements, the method comprising: Receive natural language questions input by the user and convert the natural language questions into question vectors; Retrieve candidate options related to the question vector from a pre-built multi-source vector index library, wherein the multi-source vector index library stores vectorized representations from various different types of contextual information sources; Determine the overall relevance score between each candidate and the question vector; Based on the comprehensive relevance score and the complexity of the natural language question, target candidate words are selected from the candidate words, and the target candidate words are assembled with the natural language question to form target prompt words; The large language model is invoked to generate a database query statement based on the target prompt words.

[0006] In one possible implementation, determining a comprehensive relevance score between each candidate and the question vector includes: For each of the candidates, perform the following processing: Determine the semantic similarity between the candidate options and the question vector; Determine the structural similarity between the candidate options and the question vector; The semantic similarity and the structural similarity are weighted and fused to obtain a comprehensive relevance score between the candidate options and the question vector.

[0007] In one possible implementation, determining the structural similarity between the candidate and the question vector includes: Extract the structural features of the natural language problem; wherein the structural features include at least one of the following features: tables involved, aggregation operations, grouping dimensions, connection relationships, and filtering conditions; Extract the structural features of the candidates; The degree of matching between the structural features of the candidate options and the structural features of the natural language question is determined as the structural similarity between the candidate options and the question vector.

[0008] In one possible implementation, the complexity of the natural language problem is determined in the following way: The complexity of the natural language problem is evaluated from multiple dimensions; wherein, the multiple dimensions include at least one of the following dimensions: table join complexity, query operation complexity, and data size complexity; The complexity of the natural language problem is obtained by weighted fusion of the complexity from multiple dimensions.

[0009] In one possible implementation, selecting a target candidate from the candidates based on the comprehensive relevance score and the complexity of the natural language problem includes: The candidates are ranked according to the comprehensive relevance score; The number of candidate options of each type is allocated according to the complexity of the natural language problem; wherein the number of candidate options is positively correlated with the complexity. According to the quantity quota, select the corresponding quantity and type of target candidates from the sorted candidates; wherein the total number of selected target candidates does not exceed the preset context window limit.

[0010] In one possible implementation, assembling the target candidate with the natural language question into a target prompt word includes: Extract the table name and / or field name from the target candidate and compare it with the table structure data in the multi-source vector index library to obtain the conflict detection result; In the event of a conflict, the mapping relationship between the table name and / or field name and the table structure data is determined, and the conflict detection result and the mapping relationship are combined with the target candidate and the natural language question in the form of prompt text to form a target prompt word; In the absence of detected conflicts, the target candidates are directly assembled with the natural language question to form target prompt words.

[0011] In one possible implementation, the method further includes: Execute the database query statement, obtain the execution result of the database query statement, and obtain the user's confirmation information based on the execution result; If the database query statement is executed successfully and the user confirms that it is correct, the natural language question is associated with the database query statement and stored in the positive sample database, and the multi-source vector index database is updated. If the database query fails to execute and the user confirms the error, the natural language question is associated with the database query and stored in the negative sample library for adjusting the retrieval parameters.

[0012] Secondly, this application provides a database query statement generation apparatus, the apparatus comprising: The question receiving module is used to receive natural language questions input by the user and convert the natural language questions into question vectors; The candidate retrieval module is used to retrieve candidate options related to the question vector from a pre-built multi-source vector index library, wherein the multi-source vector index library stores vectorized representations from various different types of context information sources; The comprehensive relevance score determination module is used to determine the comprehensive relevance score between each candidate option and the question vector; The target prompt word assembly module is used to select target candidates from the candidate options based on the comprehensive relevance score and the complexity of the natural language question, and assemble the target candidates with the natural language question into target prompt words; The database query statement generation module is used to call the large language model and generate database query statements based on the target prompt words.

[0013] In one possible implementation, the comprehensive relevance score determination module includes: For each of the candidates, perform the following processing: A semantic similarity determination unit is used to determine the semantic similarity between the candidate options and the question vector; A structural similarity determination unit is used to determine the structural similarity between the candidate options and the question vector; The comprehensive relevance score determination unit is used to perform weighted fusion of the semantic similarity and the structural similarity to obtain a comprehensive relevance score between the candidate and the question vector.

[0014] In one possible implementation, the structural similarity determination unit is specifically used for: Extract the structural features of the natural language problem; wherein the structural features include at least one of the following features: tables involved, aggregation operations, grouping dimensions, connection relationships, and filtering conditions; Extract the structural features of the candidates; The degree of matching between the structural features of the candidate options and the structural features of the natural language question is determined as the structural similarity between the candidate options and the question vector.

[0015] In one possible implementation, the complexity of the natural language problem is determined in the following way: The complexity of the natural language problem is evaluated from multiple dimensions; wherein, the multiple dimensions include at least one of the following dimensions: table join complexity, query operation complexity, and data size complexity; The complexity of the natural language problem is obtained by weighted fusion of the complexity from multiple dimensions.

[0016] In one possible implementation, the target prompt word assembly module is specifically used for: The candidates are ranked according to the comprehensive relevance score; The number of candidate options of each type is allocated according to the complexity of the natural language problem; wherein the number of candidate options is positively correlated with the complexity. According to the quantity quota, select the corresponding quantity and type of target candidates from the sorted candidates; wherein the total number of selected target candidates does not exceed the preset context window limit.

[0017] In one possible implementation, the target prompt word assembly module is further configured to: Extract the table name and / or field name from the target candidate and compare it with the table structure data in the multi-source vector index library to obtain the conflict detection result; In the event of a conflict, the mapping relationship between the table name and / or field name and the table structure data is determined, and the conflict detection result and the mapping relationship are combined with the target candidate and the natural language question in the form of prompt text to form a target prompt word; In the absence of detected conflicts, the target candidates are directly assembled with the natural language question to form target prompt words.

[0018] In one possible implementation, the device further includes: The database query statement execution module is used to execute the database query statement, obtain the execution result of the database query statement, and obtain user confirmation information based on the execution result. The multi-source vector index update module is used to associate the natural language question with the database query statement and store it in the positive sample database, and update the multi-source vector index database, when the database query statement is executed successfully and the user confirms that it is correct. The retrieval parameter adjustment module is used to associate the natural language question with the database query statement and store it in a negative sample library when the database query statement fails to execute and the user confirms the error, so as to adjust the retrieval parameters.

[0019] Thirdly, this application provides an electronic device, including: a processor and a memory, wherein the processor is configured to execute a database query statement generation program stored in the memory to implement the database query statement generation method described in any one of the first aspects.

[0020] Fourthly, this application provides a storage medium storing one or more programs that can be executed by one or more processors to implement the database query statement generation method described in any one aspect.

[0021] Compared with the prior art, the technical solution provided in this application has the following advantages: The method provided in this application converts natural language questions into question vectors, retrieves candidate options related to the question vectors from a pre-built multi-source vector index library, and determines the comprehensive relevance score between each candidate option and the question vector. Based on the comprehensive relevance score and the complexity of the natural language question, target candidate options are selected from the candidate options, and the target candidate options are assembled with the natural language question into target prompt words. A large language model is called to generate a database query statement based on the target prompt words. This enables users to directly express query needs in everyday natural language without needing to master SQL syntax to complete independent data queries. At the same time, it overcomes the shortcomings of single semantic retrieval, making the retrieval results more consistent with the actual query logic based on the comprehensive relevance score, which can significantly improve the quality of the selected candidate options. In addition, based on the complexity of the natural language question, target candidate options are selected from the candidate options as needed, reducing the processing cost of low-value candidate options and adapting to different industries and different query needs. Attached Figure Description

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

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

[0024] One or more embodiments are illustrated by way of example with reference numerals in the accompanying drawings. These illustrations do not constitute a limitation on the embodiments. Elements with the same reference numerals in the drawings are denoted as similar elements. Unless otherwise stated, the figures in the drawings are not to be limited by scale.

[0025] Figure 1 A flowchart illustrating an embodiment of a database query statement generation method provided in this application; Figure 2 A flowchart illustrating an embodiment of another database query statement generation method provided in this application; Figure 3 A flowchart illustrating another embodiment of a database query statement generation method provided in this application; Figure 4 A structural block diagram of a database query statement generation device provided in this application; Figure 5 This is a schematic diagram of the structure of an electronic device provided in an embodiment of this application. Detailed Implementation

[0026] To make the objectives, technical solutions, and advantages of the embodiments of this application clearer, the technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, not all embodiments. Based on the embodiments of this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.

[0027] The following disclosure provides numerous different embodiments or examples for implementing various structures of this application. To simplify the disclosure, specific examples of components and arrangements are described below. These are merely examples and are not intended to limit the scope of this application. Furthermore, reference numerals and / or letters may be repeated in different examples. Such repetition is for simplification and clarity and does not in itself indicate a relationship between the various embodiments and / or arrangements discussed.

[0028] To address the technical problem of insufficient accuracy in existing relevance retrieval technologies, which prevents the provision of high-quality contextual information for large language models and results in low accuracy of generated database query statements, this application provides a database query statement generation method, apparatus, electronic device, and storage medium. This enables users to directly submit query requests using everyday natural language, allowing them to complete independent data queries without needing to master SQL syntax. Furthermore, it overcomes the limitations of single semantic retrieval by using a comprehensive relevance score to make the search results more closely match the actual query logic, significantly improving the quality of the selected candidate options. In addition, it dynamically allocates different context types on demand based on complexity, reducing the processing cost of low-value candidate options and allowing for flexible adjustment of weight configurations according to business scenarios, adapting to different industries and query needs.

[0029] Figure 1 A flowchart illustrating an embodiment of a database query statement generation method provided in this application includes the following steps: Step 101: Receive the natural language question input by the user and convert the natural language question into a question vector.

[0030] Natural language problems can refer to database query requests described by users in the form of everyday language, such as "querying product sales revenue for each quarter of 2023" or "statistics on the number of customers in each region".

[0031] Question vectors can refer to the vectorized representation of natural language questions; that is, the semantic information of natural language questions is quantified into vector form for subsequent similarity retrieval.

[0032] In one embodiment, the natural language question text submitted by the user is obtained through a system interface, API interface, or other means. The natural language question text is preprocessed; the processed natural language question is encoded into a vector form to obtain a question vector; finally, the generated question vector is passed to the corresponding retrieval module for subsequent matching of relevant candidate options based on the question vector.

[0033] For example, a user inputs a natural language question: "Query the average monthly sales revenue of product A in East China from the first quarter to the fourth quarter of 2023." After preprocessing the user's input, we obtain: "Query the average monthly sales revenue of product A in East China for each quarter of 2023." The preprocessed text is then converted into a question vector with specific dimensions. For example, this vector could contain semantic information such as "time dimension (quarters of 2023), region dimension (East China), product dimension (product A), and indicator dimension (average monthly sales revenue)."

[0034] The above embodiments break down technical barriers, allowing business users without technical backgrounds to ask questions directly in natural language without needing to master SQL syntax, thus lowering the threshold for data querying. Simultaneously, converting text questions into computable vectors provides a foundation for subsequent cross-type and cross-data source similarity matching, avoiding the limitations of traditional keyword retrieval; furthermore, the semantic representation in vector form supports fast cosine similarity calculation, adapting to the efficient retrieval needs of large-scale indexes.

[0035] Step 102: Retrieve candidate options related to the question vector from a pre-built multi-source vector index library, which stores vectorized representations from various types of contextual information sources.

[0036] A multi-source vector index can refer to a collection that stores vectorized representations of various heterogeneous context information. It contains multiple independent sub-indexes, each corresponding to a type of context information source, and supports multi-path parallel retrieval.

[0037] Specifically, the multi-source vector index library provided in this application embodiment includes at least: a data table structure vector index library, used to store vectors related to database table structure, including vectorized results of database definition statements such as table definitions, field definitions, constraint relationships, and index information; a document vector index library, used to store vectors related to business documents, including vectorized results of unstructured text such as business descriptions, data dictionaries, and process documents after being segmented into paragraphs; and an SQL vector index library, used to store vectors of historical SQL query statements, including vectorized results of SQL text and corresponding natural language descriptions (if any), and associated structural feature metadata of the SQL text.

[0038] Candidates can refer to contextual information related to the semantics or structure of the question vector retrieved from a multi-source vector index, including at least fragments of data table structure definitions, fragments of business documents, and historical SQL statements.

[0039] Vectorization refers to converting various types of contextual information into vector form, which has the same dimension as the question vector, making it easier to calculate similarity.

[0040] In one embodiment, based on the question vector, search results matching the question vector are retrieved from the data table structure vector index library, document vector index library, and SQL vector index library in a pre-built multi-source vector index library, and the search results are deduplicated to obtain the final candidate options.

[0041] For example, the question vector is input into three sub-vector indexes: data table structure, document, and SQL. Similarity retrieval is performed in parallel, and each sub-index returns several preliminary matching results. All preliminary results from the three approaches are collected, duplicates are removed, and candidate items are formed.

[0042] The multi-source vector index library described in this embodiment covers at least three types of heterogeneous information, including database table structures, business documents, and historical SQL data. This complementary structure avoids contextual gaps caused by single information types, providing comprehensive and high-quality contextual information for the generation of subsequent database query statements (hereinafter referred to as SQL statements for convenience). Furthermore, multi-path parallel retrieval ensures matching of relevant information from different dimensions; for example, database table structures provide field relationships, documents provide business rules, and SQL provides query patterns, significantly reducing the risk of missed detections. This multi-source, sharded index structure supports independent updates and maintenance, adapting to the dynamic evolution of enterprise data warehouses and various complex application scenarios.

[0043] Step 103: Determine the overall relevance score between each candidate option and the question vector.

[0044] The comprehensive relevance score is a comprehensive indicator that measures the target database query statement generated by each candidate option pair. It provides an objective quantitative basis for selecting the final target candidate option from multiple candidates. The final comprehensive relevance score is obtained by evaluating the candidate options from multiple dimensions. This solves the problem that single-dimensional evaluation methods (such as only focusing on the semantic dimension) cannot comprehensively measure the contextual value of each candidate option. It ensures that the contextual information input into the large language model can maximize the accuracy and relevance of the generated database query statement.

[0045] In one embodiment, the following processing is performed for each candidate: determining the semantic similarity between the candidate and the question vector; determining the structural similarity between the candidate and the question vector; and weighting and fusing the semantic similarity and structural similarity to obtain a comprehensive relevance score between the candidate and the question vector.

[0046] The aforementioned comprehensive relevance score takes into account both the semantic and structural similarity between the candidate options and the question vector. The quantitative score obtained through weighted fusion is used to measure the auxiliary value of the candidate options in generating the target database query statement.

[0047] Semantic similarity is used to score the degree of matching based on text semantics, reflecting the relevance of candidate options and question vectors at the semantic level (such as the semantic similarity between "sales" and "revenue").

[0048] Structural similarity is scored based on the degree of matching of query structural features, reflecting the relevance of candidate options and question vectors at the query pattern level (e.g., both contain structures such as "group statistics, time filtering, and region filtering").

[0049] Specifically, the structural similarity between candidate options and question vectors can be determined as follows: extract the structural features of the natural language question; wherein, the structural features include at least one of the following features: tables involved, aggregation operations, grouping dimensions, connection relationships, and filtering conditions; extract the structural features of candidate options; and determine the degree of matching between the structural features of candidate options and the structural features of the natural language question as the structural similarity between candidate options and question vectors.

[0050] For example, let's take a historical SQL statement candidate retrieved in the above steps as an example for explanation. First, calculate the semantic similarity between the historical SQL statement and the question vector. The calculation shows that the historical SQL statement and the question vector "2023 quarter, East China region, Product A, average monthly sales" are highly semantically related, with only differences in time, region, and product, resulting in a semantic similarity of 0.82. Then, extract the structural features of the question and the historical SQL statement, calculating a structural similarity of 0.91. At this point, assuming the structural similarity adjustment parameter is 0.4 and the semantic similarity dynamic adjustment parameter is 0.6, the comprehensive relevance score is determined to be 0.6 × 0.82 + 0.4 × 0.91 = 0.856.

[0051] The above embodiments combine semantic and structural similarity evaluation to avoid semantically similar but structurally mismatched queries (e.g., queries requiring grouped statistics but recalling ungrouped SQL statements) or structurally similar but semantically unrelated queries. Simultaneously, structural similarity calculation covers the core elements of the query (tables, aggregations, groupings, filtering, etc.), ensuring that the recalled candidates provide targeted structural references for generating complex SQL statements; it can adapt to complex query scenarios and overcome the technical shortcomings of existing technologies that rely solely on semantic similarity retrieval, resulting in low retrieval accuracy.

[0052] Step 104: Based on the comprehensive relevance score and the complexity of the natural language question, select the target candidate from the candidate options and assemble the target candidate with the natural language question into target prompt words.

[0053] Target candidates can refer to the candidates selected to generate the most valuable context information for the database query statement. The selection criteria include comprehensive relevance score, quota limit corresponding to problem complexity, and context type balance.

[0054] The complexity of natural language processing problems can be a quantitative indicator that measures the difficulty of a user's query needs. It is evaluated based on dimensions such as table joins, query operations, and data scale, and is used to dynamically allocate context quotas.

[0055] Target prompts can refer to the complete input text assembled from target candidates and natural language questions, used to guide large language models to generate SQL statements that meet the requirements.

[0056] Specifically, the complexity of the aforementioned natural language problem is determined as follows: the complexity of the natural language problem is evaluated from multiple dimensions; wherein, the multiple dimensions include at least one of the following dimensions: table join complexity, query operation complexity, and data scale complexity; the complexity of the multiple dimensions is weighted and fused to obtain the complexity of the natural language problem.

[0057] In one embodiment, semantic information corresponding to a natural language question is identified. Based on the semantic information and a multi-source vector index, corresponding table association information, query operation information, and data scale information are determined. The corresponding table association complexity, query operation complexity, and data scale complexity are determined based on this information, and the complexity of each type of complexity is weighted and summed to obtain the complexity corresponding to the natural language question. The number quotas of three types of candidate options are dynamically allocated based on the complexity. Target candidate options are determined from the candidate options according to the number quotas. Then, target prompt words are assembled from the target candidate options and the natural language question.

[0058] Specifically, table join complexity rules can be preset, such as: single table complexity is 1, multi-table simple join complexity is 2, and multi-table complex join / self-join complexity is 3; query operation complexity rules can be preset, such as: simple query complexity is 1, and complexity including a single-level subquery is 2; data size complexity can be preset, such as: less than 100,000 data entries in the table has a complexity of 1, data entries between 100,000 and 1 million have a complexity of 2, and data entries greater than 1 million have a complexity of 3. This is merely an example, and the embodiments of this application do not impose limitations.

[0059] In addition, the quantity quota can be dynamically determined based on the complexity of the natural language problem; the weight coefficient of the multi-dimensional complexity of the natural language problem can be dynamically determined based on the actual situation of the current natural language problem or the user's personalized settings; for example, if the user attaches great importance to the table association responsibility of the natural language problem, the weight coefficient corresponding to this complexity can be set to 0.8 or even higher.

[0060] For example, based on the natural language question (average monthly sales of product A in East China in each quarter of 2023) and a multi-source vector index, the table join complexity, query operation complexity, and data size complexity corresponding to this question are determined to be 2, resulting in a total complexity of 2, which falls under medium complexity. Based on this medium complexity, quotas are allocated to three categories of candidate options: 4 candidate data table structures, 2 candidate business documents, and 5 candidate historical SQL statements. From the selected candidates, the 4 candidate data table structures (table definitions, field joins), 2 candidate documents (monthly sales calculation rules, East China region scope), and 5 candidate historical SQL statements (historical quarterly statistical SQL) with the highest comprehensive relevance scores are selected according to the quotas. The final target candidates and the natural language question are then assembled into prompt words.

[0061] The above embodiments reduce unnecessary information consumption in the context window and lower model inference time by dynamically adjusting the number of three types of candidate options. This also prevents irrelevant information from interfering with the large model's judgment of core needs, maximizing context window utilization. Furthermore, it adapts to the differentiated needs of different query scenarios. Based on queries with specific business rules, the number of different types of candidate options can be dynamically set, avoiding a one-size-fits-all fixed selection (e.g., selecting 10 candidate options regardless of query complexity). This reduces the cost of retrieving, sorting, and assembling low-value candidate options, improving overall system processing efficiency, especially significantly reducing server load in high-concurrency scenarios. In addition, dynamic quotas can indirectly match business scenario characteristics through complexity assessment (e.g., complex calculations in financial analysis require more documents and SQL quotas), ensuring that context support aligns with actual business needs and improving SQL generation adaptability across different scenarios.

[0062] Step 105: Call the large language model to generate a database query statement based on the target prompt words.

[0063] In one embodiment, the structured target prompts, which are assembled from the above steps and contain high-value contextual information and user input natural language questions, are fully input into the large language model interface. Based on the table structure of the prompts, business rules, and reference SQL statements, the model understands the user's query intent and automatically generates grammatically correct and logically sound database query statements (SQL).

[0064] For example, the target prompt words are input into a large language model, which outputs a series of text and SQL statements. The explanatory text and formatting symbols are filtered from the model output, and only the standard SQL that can be executed directly is retained, that is, the useful SQL statements are purified. The SQL statements are then subjected to basic validation: the extracted SQL is syntax validated, and after confirming that the table names, fields, joins and aggregation logic are correct, the final usable data query statements are output.

[0065] The method provided in this application converts a natural language question into a question vector, retrieves candidate options related to the question vector from a pre-built multi-source vector index, and determines a comprehensive relevance score between each candidate option and the question vector. Based on the comprehensive relevance score and the complexity of the natural language question, a target candidate option is selected from the candidate options, and the target candidate option is assembled with the natural language question into a target prompt word. A large language model is invoked to generate a database query statement based on the target prompt word. This method enables users to directly express their query needs in everyday natural language, completing independent data queries without needing to master SQL syntax. Simultaneously, it overcomes the limitations of single semantic retrieval, making the search results more closely aligned with actual query logic based on the comprehensive relevance score, significantly improving the quality of the selected candidate options. Furthermore, by selecting target candidate options on demand based on the complexity of the natural language question, the processing cost of low-value candidate options is reduced, adapting to different industries and different query needs.

[0066] Figure 2 A flowchart illustrating an embodiment of another database query statement generation method provided in this application is shown below. Figure 1 Based on the illustrated process, this section mainly describes how to select target candidate words from the pool of candidates based on the comprehensive relevance score and the complexity of the natural language question, and how to assemble the target candidate words with the natural language question to form target prompt words, including the following steps: Step 201: Receive the natural language question input by the user and convert the natural language question into a question vector.

[0067] Step 202: Retrieve candidate options related to the question vector from a pre-built multi-source vector index library, wherein the multi-source vector index library stores vectorized representations from various different types of contextual information sources.

[0068] Step 203: Determine the overall relevance score between each candidate option and the question vector.

[0069] For steps 201-203 above, please refer to the above. Figure 1 Detailed description of the relevant embodiments; Step 204: Sort the candidate options according to the comprehensive relevance score; and allocate the quantity quota of each type of candidate option according to the complexity of the natural language problem; wherein, the quantity quota is positively correlated with the complexity.

[0070] The positive correlation between quantity quotas and complexity can be understood as follows: the more complex the query, the more context options are allocated; the simpler the query, the fewer are allocated. For example, if the current complexity is medium, the quantity quotas for each type of candidate option are the baseline value; if the current complexity is high, the quantity quotas for each type of candidate option are increased compared to the baseline value; and if the current complexity is low, the quantity quotas for each type of candidate option are decreased compared to the baseline value.

[0071] In addition, the weight coefficients of different types of candidate options in the total amount of context information can be dynamically adjusted according to actual query needs or user's personalized settings. By dynamically adjusting the weight coefficients, the model can pay more attention to the candidate options with higher weights, thereby making the generated context information more accurate.

[0072] In one embodiment, all candidate options obtained in the above steps are sorted from highest to lowest based on their comprehensive relevance score. A quota is then allocated to each of the three context types according to the complexity level of the natural language problem (low / medium / high). Higher complexity results in a larger quota; lower complexity results in a smaller quota.

[0073] For example, suppose a user inputs a natural language question: "Query the average monthly sales of product A in East China for each quarter of 2023." This question has a medium complexity, and the allocated quotas for each type of candidate option are as follows: 4 candidate options for data table structure, 2 candidate options for business documents, and 5 candidate options for historical SQL. If the complexity is high, the quotas for each type of candidate option will automatically increase; if the complexity is low, the quotas for each type of candidate option will automatically decrease.

[0074] This implementation adjusts the quotas of three candidate types—data table structure, business documents, and historical SQL—synchronously according to their complexity. This ensures that the complementary relationship between different types of information is not broken (e.g., all three types of information are added synchronously during complex queries, guaranteeing the integrity of the table structure while also taking into account the reference value of business rules and query patterns). It avoids excessive loss or redundancy of any type of information, providing comprehensive and balanced contextual support for the large model. Furthermore, it supports personalized adjustment of the adjustment coefficients for different types of candidate options to meet the query needs of different users and different query scenarios.

[0075] Step 205: Select target candidates of the corresponding quantity and type from the sorted candidates according to the quantity quota; wherein the total number of selected target candidates does not exceed the preset context window limit.

[0076] The target candidate can refer to the most valuable context selected from the high-scoring candidates by type and quota.

[0077] The context window limit refers to the maximum amount of contextual information that a large model can receive; exceeding this limit can lead to overflow or increased costs.

[0078] The corresponding quantity and type can be understood as the quantity quota allocated strictly according to the above steps, and the highest score is selected from the three candidate categories respectively.

[0079] In one embodiment, candidates are grouped by type (data table structure / business document / historical SQL), and within each group, data is taken from high to low scores; the number of data taken is strictly equal to the quota to ensure that the total number does not exceed the upper limit of the context window.

[0080] For example, select the top 4 highest-scoring data table structure candidates sorted by overall relevance score. Select the top 2 highest-scoring business document candidates. Select the top 5 highest-scoring historical SQL candidates. This results in 4 + 2 + 5 = 11 target candidates, with the total number not exceeding the window limit.

[0081] The above embodiments ensure that the total number of selected target candidates does not exceed the limit by limiting the preset context window, avoiding overflow of large model processing and increased response latency due to excessive context information. At the same time, it also reduces unnecessary token consumption, lowers model call costs, and balances generation efficiency and usage costs.

[0082] Step 206: Extract the table names and / or field names from the target candidates and compare them with the table structure data in the multi-source vector index library to obtain the conflict detection results.

[0083] Step 207: If a conflict is detected, determine the mapping relationship between the table name and / or field name and the table structure data, and assemble the conflict detection result and mapping relationship into target prompt words in the form of prompt text, along with target candidates and natural language questions.

[0084] Step 208: If no conflict is detected, directly assemble the target candidate with the natural language question into a target prompt word.

[0085] The following is a unified explanation of steps 206-208 above: Table name / field name extraction refers to extracting the table names and field names used from target candidates (especially historical SQL). Latest table structure data refers to the most up-to-date database table structure in the multi-source vector index library.

[0086] Conflict checking can be understood as checking whether the table / field names in the candidate tables are consistent with the latest structure. Conflict detection results include consistency / inconsistency (renaming, deletion, addition, type change).

[0087] Mapping relationships can refer to the rules governing the correspondence between old and new names (the mapping relationship between old and new tables regarding table names, field names, etc.). Conflict warning text can refer to informing the large model, in natural language, which names have changed and which new names should be used.

[0088] In one embodiment, table names and field names are extracted from target candidates (historical SQL, old documents). The latest actual table structure is obtained from the data table structure vector index. Each table name and field name is matched and validated. Based on the matching and validation results, a conflict result is output: no conflict / conflict exists.

[0089] Furthermore, if a conflict exists—that is, if a difference is detected between the table information in the target candidate and the latest actual table structure—a mapping is established between the old and new tables for the conflicting items: old table / field name → latest table / field name. A prompt text is then generated based on the conflict situation, explicitly informing the main model to use the latest structure. Finally, the target candidate, conflict prompt, mapping relationship, and user question are assembled into a structured prompt word as the target prompt word.

[0090] If no conflict exists, meaning the table information in the target candidate matches the latest actual table structure, then all table names and field names are confirmed to be consistent with the latest structure. The target candidate is then directly concatenated with the user-input natural language question to generate clean, standard target prompts.

[0091] For example, suppose the historical SQL statements in the target candidates use the old table name: `old_sales`; while the actual table name in the latest historical database query vector index is: `sales_table`; a table name conflict is found through comparison; then a mapping between the old and new names corresponding to the conflict is generated and established: old table / field name → latest table / field name. A prompt text is generated, such as: "The table name `old_sales` in the historical SQL has been renamed to `sales_table`, please use the latest name." Finally, the target candidates, the generated conflict prompt, the mapping relationship, and the natural language question input by the user are assembled into a structured target prompt word.

[0092] The above embodiments automatically identify conflicts such as renaming, deletion, and field changes by comparing the table names and field names in the candidate tables with the latest table structure. This prevents the large language model from generating invalid SQL using outdated or incorrect table / field information, reducing syntax errors and execution failures at the source. Simultaneously, it can automatically generate mapping rules between old and new names when conflicts occur, eliminating the need for manual maintenance and correction of historical context, reducing system maintenance costs, and improving automation and robustness. Furthermore, by adding conflict descriptions and correct mapping relationships as prompts, the large language model clearly understands the changes in historical context, forcing the use of the latest and correct table structure information, significantly improving the compliance and usability of generated SQL.

[0093] Step 209: Call the large language model to generate a database query statement based on the target prompt words.

[0094] For step 208 above, please refer to the above. Figure 1 Detailed description of the relevant embodiments; Through the above Figure 2 The description of the illustrated embodiment supports dynamically adjusting the weight coefficients of different types of candidate options based on actual query scenarios or user settings. Quotas for the three types of candidate options are adjusted synchronously according to complexity; the amount of information of each type is increased synchronously for complex queries and reduced synchronously for simple queries, accurately filtering out high-value contextual information and ensuring the quality of generated SQL statements. Simultaneously, by limiting the preset context window, the total number of target candidate options is strictly controlled to avoid overflow and response delays in large model processing due to excessive information volume, ensuring a stable and reliable generation process. Furthermore, it can automatically handle structural conflicts, adapting to the dynamic data environment of enterprises. When a conflict occurs, it automatically establishes a mapping relationship between old and new names, eliminating the need for manual maintenance of historical context and reducing system maintenance costs. At the same time, it generates clear conflict warning text, guiding the large model to force the use of the latest table structure, further ensuring SQL compliance and availability.

[0095] Figure 3 A flowchart illustrating another embodiment of the database query statement generation method provided in this application is shown below. Figure 1 Based on the illustrated process, this section mainly describes how to dynamically adjust and optimize the retrieval parameters for contextual information according to the execution of the generated database query statement, including the following steps: Step 301: Receive the natural language question input by the user and convert the natural language question into a question vector.

[0096] Step 302: Retrieve candidate options related to the question vector from a pre-built multi-source vector index library, wherein the multi-source vector index library stores vectorized representations from various different types of contextual information sources.

[0097] Step 303: Determine the overall relevance score between each candidate option and the question vector.

[0098] Step 304: Based on the comprehensive relevance score and the complexity of the natural language question, select the target candidate from the candidate options and assemble the target candidate with the natural language question into target prompt words.

[0099] Step 305: Call the large language model to generate a database query statement based on the target prompt words.

[0100] For steps 301-305 above, please refer to the above. Figure 1 Detailed description of the relevant embodiments; Step 306: Execute the database query statement, obtain the execution result of the database query statement, and obtain the user's confirmation information based on the execution result.

[0101] In one embodiment, the valid database query statement generated in the above steps is submitted to the target database for execution, and the query results (tables, values, statistics, etc.) returned by the database are received. The results are displayed to the user, who then judges whether the results match their query intent, and the user's confirmation / negation feedback is collected to form confirmation information.

[0102] For example, suppose the target SQL statement is executed to query the quarterly sales revenue of product A in East China in 2023. The database returns 4 records, including the quarterly and monthly average sales revenue. After the user reviews and confirms, the result is correct and meets the requirements; the system receives confirmation information: execution was successful and the user confirmed it was correct.

[0103] Step 307: If the database query statement is executed successfully and the user confirms that it is correct, associate the natural language question with the database query statement and store it in the positive sample database, and update the multi-source vector index database.

[0104] A positive sample library can refer to a knowledge base used to store high-quality, correct, and usable pairing data of "natural language questions and SQL statements." Association storage can be understood as binding user questions with correct SQL statements for storage, forming reusable standard samples.

[0105] Updating the multi-source vector index library can vectorize newly added correct SQL statements and add them to the SQL vector index library, thereby improving the quality of subsequent searches.

[0106] In one embodiment, the SQL execution is successful and the user confirms the result is correct. The natural language question and its corresponding SQL are stored as a set of positive samples in the positive sample database. The SQL is then vectorized. The vectors are updated in the SQL vector index database to expand retrieval resources; this completes sample accumulation and index self-evolution.

[0107] For example, suppose a user's question is: "Query the average monthly sales of product A in East China for each quarter of 2023." The generated SQL is executed successfully, the user confirms its correctness, and the system stores it in the positive sample database: {Natural Language Question, Correct SQL Statement}; and the SQL is vectorized and added to the SQL vector index library. Subsequent similar questions can directly retrieve this high-quality SQL as a reference.

[0108] Step 308: If the database query fails to execute and the user confirms the error, the natural language question is associated with the database query and stored in the negative sample library for use in adjusting the retrieval parameters.

[0109] A negative sample library can refer to a knowledge base that stores paired data of "problem and error SQL" that are incorrect, unavailable, or fail to execute.

[0110] Search parameters can include core system parameters such as similarity thresholds, weighting coefficients, quota strategies, and structural matching rules. Adjusting search parameters can be understood as using negative samples to analyze the causes of errors and optimize search, scoring, and suggestion word assembly strategies.

[0111] In one embodiment, if SQL execution fails or the user confirms an incorrect result, the natural language problem is associated with the erroneous SQL and stored in a negative sample database. These negative samples are then categorized and analyzed for errors such as syntax errors, structural errors, incorrect table names, and semantic errors. Based on the error type, parameters such as retrieval weights, similarity thresholds, and scoring strategies are automatically / semi-automatically adjusted to prevent the system from generating the same type of erroneous SQL again.

[0112] For example, suppose the SQL generated uses the old table name old_sales, the execution fails and the user confirms the error; the system stores the negative sample database: {Problem, Error SQL, Error Reason: Table Name Does Not Exist}; and adjusts the retrieval parameters: increases the weight of table structure conflict detection, and strengthens the verification of the mapping between the old and new names.

[0113] Through the above Figure 3 The embodiments described herein demonstrate how the system achieves self-optimization through execution results and user feedback, forming a closed-loop learning mechanism. A positive sample library is continuously expanded to include high-quality SQL queries, making the system increasingly accurate with use. The vector index library is updated to enable self-evolution of retrieval capabilities, adapting to business changes. Simultaneously, a negative sample library is constructed to pinpoint the root causes of errors, automatically adjusting retrieval parameters, continuously reducing the error rate, and improving system robustness and long-term availability, fully adapting to the dynamic, complex, and constantly changing data environment of enterprise-level systems.

[0114] Figure 4 A structural block diagram of a database query statement generation device provided in this application, the device comprising: Question receiving module 41 is used to receive natural language questions input by the user and convert the natural language questions into question vectors; The candidate retrieval module 42 is used to retrieve candidate options related to the question vector from a pre-built multi-source vector index library, wherein the multi-source vector index library stores vectorized representations from various different types of context information sources; The comprehensive relevance score determination module 43 is used to determine the comprehensive relevance score between each candidate option and the question vector; The target prompt word assembly module 44 is used to select target candidates from the candidates based on the comprehensive relevance score and the complexity of the natural language question, and assemble the target candidates with the natural language question into target prompt words; The database query statement generation module 45 is used to call the large language model and generate a database query statement based on the target prompt words.

[0115] In one possible implementation, the comprehensive relevance score determination module 43 includes: For each of the candidates, perform the following processing: A semantic similarity determination unit is used to determine the semantic similarity between the candidate options and the question vector; A structural similarity determination unit is used to determine the structural similarity between the candidate options and the question vector; The comprehensive relevance score determination unit is used to perform weighted fusion of the semantic similarity and the structural similarity to obtain a comprehensive relevance score between the candidate and the question vector.

[0116] In one possible implementation, the structural similarity determination unit is specifically used for: Extract the structural features of the natural language problem; wherein the structural features include at least one of the following features: tables involved, aggregation operations, grouping dimensions, connection relationships, and filtering conditions; Extract the structural features of the candidates; The degree of matching between the structural features of the candidate options and the structural features of the natural language question is determined as the structural similarity between the candidate options and the question vector.

[0117] In one possible implementation, the complexity of the natural language problem is determined in the following way: The complexity of the natural language problem is evaluated from multiple dimensions; wherein, the multiple dimensions include at least one of the following dimensions: table join complexity, query operation complexity, and data size complexity; The complexity of the natural language problem is obtained by weighted fusion of the complexity from multiple dimensions.

[0118] In one possible implementation, the target prompt word assembly module 44 is specifically used for: The candidates are ranked according to the comprehensive relevance score; The number of candidate options of each type is allocated according to the complexity of the natural language problem; wherein the number of candidate options is positively correlated with the complexity. According to the quantity quota, select the corresponding quantity and type of target candidates from the sorted candidates; wherein the total number of selected target candidates does not exceed the preset context window limit.

[0119] In one possible implementation, the target prompt word assembly module 44 is further configured to: Extract the table name and / or field name from the target candidate and compare it with the table structure data in the multi-source vector index library to obtain the conflict detection result; In the event of a conflict, the mapping relationship between the table name and / or field name and the table structure data is determined, and the conflict detection result and the mapping relationship are combined with the target candidate and the natural language question in the form of prompt text to form a target prompt word; In the absence of detected conflicts, the target candidates are directly assembled with the natural language question to form target prompt words.

[0120] In one possible implementation, the device further includes: The database query statement execution module is used to execute the database query statement, obtain the execution result of the database query statement, and obtain user confirmation information based on the execution result. The multi-source vector index update module is used to associate the natural language question with the database query statement and store it in the positive sample database, and update the multi-source vector index database, when the database query statement is executed successfully and the user confirms that it is correct. The retrieval parameter adjustment module is used to associate the natural language question with the database query statement and store it in a negative sample library when the database query statement fails to execute and the user confirms the error, so as to adjust the retrieval parameters.

[0121] like Figure 5 As shown in the figure, this application provides an electronic device, including a processor 111, a communication interface 112, a memory 113, and a communication bus 114, wherein the processor 111, the communication interface 112, and the memory 113 communicate with each other through the communication bus 114. Memory 113 is used to store computer programs; In one embodiment of this application, when the processor 111 executes the program stored in the memory 113, it implements the database query statement generation method provided in any of the foregoing method embodiments, including: Receive natural language questions input by the user and convert the natural language questions into question vectors; Retrieve candidate options related to the question vector from a pre-built multi-source vector index library, wherein the multi-source vector index library stores vectorized representations from various different types of contextual information sources; Determine the overall relevance score between each candidate and the question vector; Based on the comprehensive relevance score and the complexity of the natural language question, target candidate words are selected from the candidate words, and the target candidate words are assembled with the natural language question to form target prompt words; The large language model is invoked to generate a database query statement based on the target prompt words.

[0122] This application also provides a computer-readable storage medium storing a computer program thereon, which, when executed by a processor, implements the steps of the database query statement generation method provided in any of the foregoing method embodiments.

[0123] The device embodiments described above are merely illustrative. The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the modules can be selected to achieve the purpose of this embodiment according to actual needs.

[0124] Through the above description of the embodiments, those skilled in the art can clearly understand that each embodiment can be implemented using software plus a general-purpose hardware platform, or of course, using hardware. Based on this understanding, the above technical solutions, in essence or the parts that contribute to the related technology, can be embodied in the form of a software product. This computer software product can be stored in a computer-readable storage medium, such as ROM / RAM, magnetic disk, optical disk, etc., and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute the methods described in the various embodiments or some parts of the embodiments.

[0125] It should be understood that the terminology used herein is for the purpose of describing particular exemplary embodiments only and is not intended to be limiting. Unless the context clearly indicates otherwise, the singular forms “a,” “an,” and “described” as used herein may also mean including the plural forms. The terms “comprising,” “including,” “containing,” and “having” are inclusive and therefore indicate the presence of the stated features, steps, operations, elements, and / or components, but do not exclude the presence or addition of one or more other features, steps, operations, elements, components, and / or combinations thereof. The method steps, processes, and operations described herein are not construed as requiring them to be performed in a particular order described or illustrated unless the order of performance is explicitly indicated. It should also be understood that additional or alternative steps may be used.

[0126] The above description is merely a specific embodiment of this application, enabling those skilled in the art to understand or implement this application. Various modifications to these embodiments will be readily apparent to those skilled in the art, and the general principles defined herein may be implemented in other embodiments without departing from the spirit or scope of this application. Therefore, this application is not to be limited to the embodiments shown herein, but is to be accorded the widest scope consistent with the principles and novel features claimed herein.

Claims

1. A method for generating database query statements, characterized in that, The method includes: Receive natural language questions input by the user and convert the natural language questions into question vectors; Retrieve candidate options related to the question vector from a pre-built multi-source vector index library, wherein the multi-source vector index library stores vectorized representations from various different types of contextual information sources; Determine the overall relevance score between each candidate and the question vector; Based on the comprehensive relevance score and the complexity of the natural language question, target candidate words are selected from the candidate words, and the target candidate words are assembled with the natural language question to form target prompt words; The large language model is invoked to generate a database query statement based on the target prompt words.

2. The method according to claim 1, characterized in that, Determine the overall relevance score between each candidate and the question vector, including: For each of the candidates, perform the following processing: Determine the semantic similarity between the candidate options and the question vector; Determine the structural similarity between the candidate options and the question vector; The semantic similarity and the structural similarity are weighted and fused to obtain a comprehensive relevance score between the candidate options and the question vector.

3. The method according to claim 2, characterized in that, Determining the structural similarity between the candidate options and the question vector includes: Extract the structural features of the natural language problem; wherein the structural features include at least one of the following features: tables involved, aggregation operations, grouping dimensions, connection relationships, and filtering conditions; Extract the structural features of the candidates; The degree of matching between the structural features of the candidate options and the structural features of the natural language question is determined as the structural similarity between the candidate options and the question vector.

4. The method according to claim 1, characterized in that, The complexity of the natural language problem is determined in the following way: The complexity of the natural language problem is evaluated from multiple dimensions; wherein, the multiple dimensions include at least one of the following dimensions: table join complexity, query operation complexity, and data size complexity; The complexity of the natural language problem is obtained by weighted fusion of the complexity from multiple dimensions.

5. The method according to claim 1, characterized in that, Based on the comprehensive relevance score and the complexity of the natural language problem, target candidates are selected from the candidate options, including: The candidates are ranked according to the comprehensive relevance score; The number of candidate options of each type is allocated according to the complexity of the natural language problem; wherein the number of candidate options is positively correlated with the complexity. According to the quantity quota, select the corresponding quantity and type of target candidates from the sorted candidates; wherein the total number of selected target candidates does not exceed the preset context window limit.

6. The method according to claim 1, characterized in that, Assemble the target candidates and the natural language question into target prompt words, including: Extract the table name and / or field name from the target candidate and compare it with the table structure data in the multi-source vector index library to obtain the conflict detection result; In the event of a conflict, the mapping relationship between the table name and / or field name and the table structure data is determined, and the conflict detection result and the mapping relationship are combined with the target candidate and the natural language question in the form of prompt text to form a target prompt word; In the absence of detected conflicts, the target candidates are directly assembled with the natural language question to form target prompt words.

7. The method according to claim 1, characterized in that, The method further includes: Execute the database query statement, obtain the execution result of the database query statement, and obtain the user's confirmation information based on the execution result; If the database query statement is executed successfully and the user confirms that it is correct, the natural language question is associated with the database query statement and stored in the positive sample database, and the multi-source vector index database is updated. If the database query fails to execute and the user confirms the error, the natural language question is associated with the database query and stored in the negative sample library for adjusting the retrieval parameters.

8. A database query statement generation device, characterized in that, The device includes: The question receiving module is used to receive natural language questions input by the user and convert the natural language questions into question vectors; The candidate retrieval module is used to retrieve candidate options related to the question vector from a pre-built multi-source vector index library, wherein the multi-source vector index library stores vectorized representations from various different types of context information sources; The comprehensive relevance score determination module is used to determine the comprehensive relevance score between each candidate option and the question vector; The target prompt word assembly module is used to select target candidates from the candidate options based on the comprehensive relevance score and the complexity of the natural language question, and assemble the target candidates with the natural language question into target prompt words; The database query statement generation module is used to call the large language model and generate database query statements based on the target prompt words.

9. An electronic device, characterized in that, include: A processor and a memory, the processor being configured to execute a database query statement generation program stored in the memory to implement the database query statement generation method according to any one of claims 1-7.

10. A storage medium, characterized in that, The storage medium stores one or more programs, which can be executed by one or more processors to implement the database query statement generation method according to any one of claims 1-7.

Images