Method, system, computer device and storage medium for intelligent questioning

Abstract

The application provides a smart query method, system, computer device and storage medium, the method comprises the following steps: acquiring data from a data source and constructing a data set based on preset rules and conditions; selecting corresponding data fields and records from the constructed data set based on the established business scenario to create an analysis table, and constructing a business knowledge base related to the business scenario; extracting metadata of the analysis table and the business knowledge base, establishing a first independent vector index based on the extracted metadata, and establishing a second independent vector index based on the field enumeration value in the analysis table; acquiring a user-selected business scenario and a user question under the business scenario, retrieving and generating a prompt word related to the user question based on the independent vector index; based on the user question and the prompt word related to the user question, generating an executable SQL through a large language model, and acquiring a query result based on the executable SQL.

Description

Technical Field

[0001] This invention relates to the field of computer application technology, and in particular to a method, system, computer device, and storage medium for intelligent data retrieval. Background Technology

[0002] In traditional database query scenarios, data retrieval often relies on specialized SQL skills. Users need to be proficient in SQL syntax rules and have a clear understanding of database table structures and field relationship logic to write accurate query statements. This technical barrier makes it difficult for most business personnel or non-technical staff without SQL knowledge to retrieve data independently. They can only rely on the technical team to handle the requirements, and the entire process, from requirement communication and SQL writing to result verification, is multi-step and time-consuming, severely restricting data retrieval efficiency. At the same time, with the continuous growth of data volume and increasingly flexible query requirements (such as multi-dimensional dynamic filtering and ad-hoc analysis needs), the cost of manually writing and debugging SQL has further increased, and the traditional model can no longer meet the demand for efficient data retrieval.

[0003] In recent years, breakthroughs in semantic understanding and text generation using Large Language Models (LLMs) have provided crucial technological support for addressing this pain point. Based on this, NL2SQL technology can accurately parse users' everyday language needs into SQL statements conforming to database specifications through natural language dialogue interaction, directly completing interactive queries with the database. This process eliminates the need for users to possess SQL skills, significantly lowering the data retrieval threshold and saving the step of manually writing SQL, thus significantly improving data acquisition and analysis efficiency and effectively breaking through the efficiency bottleneck of traditional database queries.

[0004] However, existing NL2SQL technology still faces multiple challenges in its implementation in real-world enterprise scenarios.

[0005] First, there are inherent flaws in word segmentation and entity matching. Enterprise-defined metrics (such as "adaptive training participation rate") are often incorrectly split into multiple independent words, leading to matching failures with the metadata dictionary. Abbreviations generated by users due to their expression habits (such as abbreviating "management service unit" to "management unit") or the superposition of domain-specific modifiers (such as "education and training settlement amount") further exacerbate the difficulty of identifying metrics and dimensions.

[0006] Secondly, the system has a weak ability to deconstruct complex intents. When faced with cross-business domain queries (such as "comparing the quarterly repurchase rate differences between beauty and apparel categories in East China"), the existing system struggles to accurately break down core elements such as indicators, dimensions, and time ranges. In particular, in multi-table association scenarios such as finance and government affairs, it cannot automatically identify the relationships between tables, leading to the failure of query command generation.

[0007] Third, the limited context window makes parsing large tables difficult. Current technologies typically input the complete database schema (including all table names, field names, field types, and all enumeration values) as context into the large language model at once. When the analysis table contains hundreds of fields, each with dozens or even hundreds of enumeration values, the text length of the full schema easily exceeds the context window limit of the large language model, causing the model to "forget" or "ignore" key field information. Even if the limit is not exceeded, the noise interference from numerous irrelevant fields and enumeration values ​​significantly reduces the model's ability to focus on user intent, causing the accuracy of the generated SQL to drop sharply as the table size increases.

[0008] Fourth, the enumeration value illusion problem is prominent. Database field values ​​often exist in the form of codes, abbreviations, or internal system identifiers (such as status code "S1" or city code "010"). These values ​​lack understandable semantics for large language models, making it easy for the model to generate values ​​that do not exist in the database when generating SQL. Since large language models cannot directly access the database for value validation, this "illusion" is often only discovered after SQL execution errors, forcing business personnel to repeatedly correct it, thus increasing communication and debugging costs.

[0009] Fifth, the probabilistic generation characteristics of large-scale models fundamentally contradict the need for accurate enterprise data. The calculation logic for the same indicator (such as "employees") varies across different business scenarios (whether it includes dispatched or rehired personnel), but existing technologies lack a unified semantic control mechanism. Large-scale models rely solely on general knowledge for inference, leading to contradictory results for the same questions. IDC research shows that over 77% of enterprises experience data interpretation biases due to inconsistent indicator definitions, and existing intelligent data query systems have not effectively addressed this core pain point. Furthermore, the randomness of model output poses a significant risk: large-scale models may generate different SQL statements due to contextual influences. For example, a query for "the number of people born in Beijing" may result in inconsistent data sources due to table name mapping discrepancies, forcing business personnel to repeatedly verify with the IT department, thus increasing communication costs. Summary of the Invention

[0010] Based on the above background, the purpose of this invention is to overcome the bottlenecks of existing technologies and provide an intelligent query solution that balances semantic parsing accuracy, result certainty, scenario adaptability, and query efficiency. Specifically, this invention adopts the following technical solution:

[0011] The first aspect of this invention provides a method for intelligent number retrieval, comprising:

[0012] Based on data source information, establish a connection with the data source, and retrieve data from the data source and construct a dataset based on preset rules and conditions;

[0013] Establish multiple business scenarios. Based on the established business scenarios, select data fields and records from the constructed dataset to create at least one analysis table for each business scenario, and build a business knowledge base related to each business scenario.

[0014] Extract the metadata of the analysis table and business knowledge base corresponding to each business scenario, establish the first independent vector index for the corresponding business scenario based on the extracted metadata, and obtain the field enumeration values ​​in the analysis table separately. Construct a second independent vector index decoupled from the first independent vector index based on the field enumeration values.

[0015] Obtain the business scenario selected by the user and the user's question under that business scenario. Based on the first independent vector index corresponding to the business scenario, obtain the knowledge base content and candidate fields, and based on the candidate fields and the second independent vector index, obtain the candidate enumeration value.

[0016] Based on the knowledge base content, candidate fields, and candidate enumeration values, generate prompt words related to the user's question;

[0017] Based on the user's question and related prompts, an executable SQL statement is generated using a large language model, and the query results are obtained based on the executable SQL statement.

[0018] Furthermore, the metadata of the analysis table includes at least the table name and enumeration values, as well as multiple or all of the following: physical field name, Chinese field name, field type, and data type; the metadata of the business knowledge base includes multiple or all of the following: business concepts, business rules, question-answer pairs, and prompt words.

[0019] Furthermore, metadata from the analysis table and business knowledge base corresponding to each business scenario is extracted. A first independent vector index for the corresponding business scenario is established based on the extracted metadata. Field enumeration values ​​from the analysis table are obtained separately. A second independent vector index, decoupled from the first independent vector index, is constructed based on the field enumeration values, including:

[0020] Locate the analysis table and business knowledge base corresponding to the target business scenario, and extract various metadata from the found analysis table and business knowledge base;

[0021] For the analysis table, a first type of vectorized text is generated based on the business scenario name and the extracted metadata of each item in the analysis table; for the business knowledge base, a second type of vectorized text is generated based on the extracted metadata of each item in the business knowledge base.

[0022] The first type of vectorized text and the second type of vectorized text are converted into vectors respectively and stored as the first independent vector index, which is used to perform semantic retrieval of analysis table metadata and business knowledge base metadata.

[0023] The field enumeration values ​​in the analysis table corresponding to the target business scenario are obtained separately, converted into vectors and stored as a second independent vector index, which is used to perform independent semantic recall of field values;

[0024] Configure the cosine similarity algorithm for the first independent vector index and the second independent vector index respectively, and enable KNN search.

[0025] Furthermore, based on the first independent vector index corresponding to the business scenario, the knowledge base content and candidate fields are obtained, and based on the candidate fields and the second independent vector index, candidate enumeration values ​​are obtained, including:

[0026] User questions are converted into question vectors. Based on these question vectors, the closest analysis table is queried through the first independent vector index corresponding to the business scenario. Knowledge base content is then obtained from the corresponding business knowledge base, which includes relevant business concepts, business rules, and question-answer pairs.

[0027] Within the scope of the retrieved analysis table, field-level retrieval is performed based on the question vector, and a preset number of candidate fields are determined by combining vector similarity and keyword matching.

[0028] For each candidate field that has been identified, a value-level search is performed on the corresponding field enumeration value in the second independent vector index, and a preset number of candidate enumeration values ​​are determined by combining vector similarity and multi-keyword matching.

[0029] Furthermore, based on user questions and related prompts, executable SQL is generated using a large language model, including:

[0030] The query is rewritten based on the user's question and historical dialogue to generate a rewritten user question.

[0031] The rewritten user question and related prompts are input into the large language model, and an executable SQL statement is generated based on the input in deep thinking mode.

[0032] Furthermore, the enumerated values ​​stored in the second independent vector index are the actual values ​​extracted from the analysis table;

[0033] Before obtaining the query results based on the executable SQL statement, the following is also included:

[0034] Verify whether the field condition values ​​in the generated executable SQL statement are in the corresponding field enumeration value set of the second independent vector index; if not, replace the mismatched values ​​with the enumeration value with the highest similarity in the second independent vector index and regenerate the SQL, or prompt the user that the verification failed.

[0035] Furthermore, based on user questions and historical conversations, query rewriting is performed to generate rewritten user questions, including:

[0036] It identifies and blocks casual conversation. If the question falls into the category of casual conversation, it directly returns a preset response; otherwise, it proceeds to the next step.

[0037] If the user's current question has no historical dialogue context, or if the current question itself is semantically complete and unrelated to the historical dialogue, then skip the rewriting; otherwise, combine the content of the historical dialogue and call the large model to perform semantic rewriting of the current question.

[0038] A second aspect of the present invention provides an intelligent query system for implementing the method described in the first aspect above, comprising:

[0039] The intelligent data analysis platform is used to establish connections with data sources, build datasets, create business scenarios, analysis tables and business knowledge bases, and provide user question entry points and SQL execution result display interfaces.

[0040] An algorithm platform, which is communicatively connected to the intelligent data analysis platform, is used for:

[0041] Extract the metadata from the analysis table and the business knowledge base, establish a first independent vector index based on the extracted metadata, and establish a second independent vector index based on the field enumeration values ​​in the analysis table;

[0042] Based on the user's selected business scenario and the user's question within that business scenario, relevant prompt words for the user's question are generated by retrieving them using the first independent vector index and the second independent vector index.

[0043] Based on user questions and related prompts, an executable SQL query is generated using a large language model, and the query results are fed back to the intelligent data analysis platform.

[0044] A third aspect of the present invention provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the method described in the first aspect above.

[0045] A fourth aspect of the present invention provides an electronic device including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the program to implement the method described in the first aspect above.

[0046] Compared with the prior art, the present invention has the following beneficial technical effects:

[0047] 1) Accurate terminology recognition and efficient parsing of large tables are achieved through a two-layer independent vector index. The metadata of the analysis table and the field enumeration values ​​are constructed as a first independent vector index and a second independent vector index, respectively. On the one hand, a hybrid strategy combining vector semantic retrieval and keyword exact matching effectively solves the problems of incorrectly splitting enterprise-defined indicators and the inability to match user abbreviations. On the other hand, during retrieval, only the matched candidate fields and candidate enumeration values ​​are input into the large language model, rather than the full metadata of the analysis table, fundamentally avoiding field omissions caused by context window overflow and significantly enhancing the system's ability to handle extremely long and large tables.

[0048] 2) Accurate decomposition of complex intents is achieved through multi-level progressive retrieval and integration with a business knowledge base. A funnel-shaped progressive retrieval strategy is adopted, moving from the table level to the field level and then to the enumeration value level, narrowing the candidate range layer by layer. Combined with semantic constraints provided by business concepts, business rules, and question-answer pairs in the business knowledge base, it can accurately parse complex queries that cross business domains and multi-dimensional combinations layer by layer, automatically identify the relationships between tables, and significantly improve the success rate of SQL generation in complex scenarios.

[0049] 3) The independent indexing of enumerated values ​​and closed-loop verification effectively reduce the illusion of a large language model. The second independent vector index stores only the actual values ​​extracted from the analysis table, and value verification is performed after SQL generation: if a non-existent field value is found, it is automatically replaced with the most similar actual enumerated value from the index, and the SQL is regenerated, or the user is notified of verification failure. This mechanism eliminates the problem of non-existent values ​​in the database generated by the large language model at its source, avoiding repeated corrections due to SQL execution errors.

[0050] 4) It can adapt to diverse business scenarios and flexible data analysis needs. Users can adjust the analysis questions and scenarios at any time according to actual needs, and the system will automatically perform corresponding data processing and display. At the same time, through the construction and application of a business knowledge base, business knowledge and data are closely integrated, making data analysis more closely aligned with actual business needs and improving the accuracy and practicality of the analysis results. Attached Figure Description

[0051] Figure 1 This is a flowchart illustrating an embodiment of the intelligent questioning method of the present invention.

[0052] Figure 2 This is an example diagram of the query results of an embodiment of the intelligent query method of the present invention. Detailed Implementation

[0053] Embodiments of the present invention will now be described in more detail with reference to the accompanying drawings. While some embodiments of the invention are shown in the drawings, it should be understood that the invention can be implemented in various forms and should not be construed as limited to the embodiments set forth herein. Rather, these embodiments are provided to provide a more thorough and complete understanding of the invention. It should be understood that the accompanying drawings and embodiments are for illustrative purposes only and are not intended to limit the scope of protection of the invention.

[0054] Example 1

[0055] See Figure 1 The first embodiment of the present invention provides a method for intelligent number retrieval, comprising the following steps:

[0056] S1. Based on the data source information, establish a connection with the data source, and retrieve data from the data source and construct a dataset based on preset rules and conditions.

[0057] In some of the illustrated examples, this step is implemented based on an intelligent analytics platform, which can establish connections with various types of data sources through corresponding interfaces and protocols. These data sources include, but are not limited to, relational databases (such as MySQL, Oracle, etc.), non-relational databases (such as MongoDB, Redis, etc.), and file systems (such as CSV, Excel files, etc.). After establishing a connection, access permissions to the data can be further obtained, laying the foundation for subsequent data processing and analysis.

[0058] In some illustrated examples, data can be extracted, transformed, and loaded (ETL) from a connected data source according to predetermined rules and conditions to construct a dataset suitable for analysis. This step also involves data cleaning, deduplication, and aggregation to ensure data quality and consistency.

[0059] For example, the preset rules and conditions include the following aspects:

[0060] Data filtering rules: Specify which tables or views to extract data from the data source and the applicable filtering conditions. For example, in a human resources analysis scenario, the rules can be configured to extract employee basic information table (hr_employee_base_info), employee onboarding and offboarding record table (hr_employment_log), employee title information table (hr_title_info), etc. from the HR database, and set the filtering conditions as "employment_status is not equal to 'deleted'" and "data update time is within the most recent synchronization cycle".

[0061] Field mapping rules: Map the original field names in the data source to uniform analysis field names. For example, map "emp_status" in the source table to "employment_status", and "dept_code" to "secondary_unit", so that the same type of field with inconsistent naming in different data sources is unified in the dataset.

[0062] Data cleaning rules: Define how to handle null values, outliers, and duplicate records. For example, fill missing "gender" field values ​​with "unknown", mark negative values ​​or outliers exceeding 150 in the "age" field as requiring manual verification, and retain the most recently updated record among multiple duplicate employee_no records.

[0063] Data conversion rules: Perform type conversion or standardization on the original data. For example, unify the date field to the "YYYY-MM-DD" format, and convert full-width characters in text enumeration values ​​to half-width characters.

[0064] Incremental update rules: Define the update strategy for the dataset. For example, you can set a full synchronization to be performed at 2:00 AM every day, or set an incremental synchronization to be performed every 30 minutes on records in the source data whose update_time field has changed within this period, to ensure that the dataset on which the analysis table is based remains consistent with the timeliness of the data source.

[0065] S2. Establish multiple business scenarios. Based on the established business scenarios, select data fields and records from the constructed dataset to create at least one analysis table for each business scenario, and construct a business knowledge base related to each business scenario.

[0066] In some of the examples shown, different business scenarios can be defined based on the company's actual business needs and analytical objectives. Examples include human resource analysis scenarios, sales analysis scenarios, financial analysis scenarios, and inventory management scenarios. Each business scenario corresponds to a specific analytical dimension and indicator system.

[0067] In some of the illustrated examples, based on established business scenarios, appropriate data fields and records are selected from the built dataset to create one or more corresponding analysis tables for each business scenario. The structure and content of the analysis tables should be designed closely around the analytical needs of the business scenario.

[0068] For example, taking the "Employee Basic Information Analysis" function in a human resources analysis scenario, the created employee basic information analysis table is as follows:

[0069] Table 1

[0070]

[0071] In some of the examples shown, a corresponding business knowledge base is built for each business scenario, and information such as table descriptions, business concepts, business rules, terminology definitions, calculation rules, common analysis questions and answers, and prompt word template examples related to the business scenario are entered into it.

[0072] For example, in the above "human resources analysis scenario", the constructed business knowledge base includes the following modules:

[0073] 1) Table description: Regarding the above analysis table 1, the table description is "This table records basic employee information, covering dimensions such as organizational structure, position, employment type, and personal basic information, and is used for personnel size statistics and structural analysis."

[0074] 2) Business Concepts: Define specialized terms used in business scenarios. For example, in a human resources analysis scenario, "female senior professional title" refers to an employee who is female and holds a senior professional title.

[0075] 3) Business Rules: Define the calculation criteria and segmentation rules for indicators. For example, for the indicator "Number of Personnel of Various Types", distinguish the following three statistical criteria: on-the-job (employment_type="on-the-job"), employment (employment_type belongs to "on-the-job", "subtotal", "dispatch", "re-employment"), and occupation (employment_type belongs to "on-the-job", "dispatch", "re-employment"); for the analysis of "Personnel Structure", predefine age group segments (35 years and below, 36 to 40 years, 41 to 45 years, 46 to 50 years, 51 to 54 years, 55 to 59 years, 60 years and above), years of service, years of service, and education level (associate degree and below, bachelor's degree, master's degree, doctoral degree), etc.

[0076] 4) Terminology definition: Time dimension is defined as follows: current period -- up to the current date; last month's end -- the end of the previous month; last year's end -- statistics at the end of the previous year.

[0077] Personnel change dimensions are defined as follows: Personnel Changes This Month - used for calculating the number of personnel changes, calculating the changes from the end of the month to the present; Personnel Changes This Year - used for calculating the number of personnel changes, calculating the changes from the end of last year to the present.

[0078] 5) Calculation Rules: For personnel changes, calculate the total based on the start and end dates and time intervals; for personnel in a specific secondary unit, summarize the count after filtering by secondary unit; the formula for calculating personnel structure percentage is:

[0079] The number of employees to be counted is calculated as count(distinct employee_no), which means deduplicating employee IDs.

[0080] The percentage of a specific job sequence / job category / job percentage = (number of people in that job sequence / job category / number of people in that job) / total number of people in the entire institute or unit × 100%.

[0081] 6) Data-driven question and answer pairing.

[0082] Example Question and Answer 1:

[0083] Question: "Number of contract employees at the headquarters of the statistical agency";

[0084] The corresponding SQL logic is: SELECT COUNT(*) FROM hr_employee_base_info WHERE secondary_unit = 'Headquarters' AND employment_type = 'Labor Contract System'.

[0085] Example Question and Answer 2:

[0086] Question: "Age distribution of each secondary unit";

[0087] The corresponding SQL logic is as follows: group by secondary_unit, calculate the age group based on the employee's date of birth, and then count the number of people in each age group.

[0088] Example Question and Answer 3:

[0089] Question: "What percentage of new employees this year are in the professional and technical fields?"

[0090] The corresponding SQL logic is to filter records with job_category_filter = 'professional and technical' and whose start date is within the current year, and calculate their proportion of the total number of new hires.

[0091] 7) Example of a prompt word template:

[0092] "When calculating the size of the workforce, it is important to distinguish the following definitions: 'On-the-job' only includes personnel with 'employment_type='on-the-job'; 'employment' includes on-the-job, subtotal, dispatched, and rehired personnel; 'occupation' includes on-the-job, dispatched, and rehired personnel. When answering user questions, the correct statistical definition should be selected based on the specific description in the user's question."

[0093] S3. Extract the metadata of the analysis table and business knowledge base corresponding to each business scenario. Based on the extracted metadata, establish the first independent vector index for the corresponding business scenario. Separately obtain the field enumeration values ​​in the analysis table and construct a second independent vector index decoupled from the first independent vector index based on the field enumeration values.

[0094] This step is one of the core steps of the present invention. Its key is to construct the analysis table metadata and field enumeration values ​​as two independent vector indexes, rather than storing them together as in the prior art.

[0095] In one illustrated example, this step specifically includes the following sub-steps:

[0096] S31. Locate the analysis table and business knowledge base corresponding to the business scenario (such as human resource analysis scenario), and extract table-level and field-level metadata in batches from the analysis table; extract business concepts, business rules, question-and-answer pairs and prompt words from the business knowledge base, convert them into key-value pair format, and filter out null values ​​and invalid entries.

[0097] S32. Generate a first type of vectorized text based on the business scenario name and the extracted metadata of the analysis table, generate a second type of vectorized text based on the extracted metadata of the business knowledge base, and convert the first type of vectorized text and the second type of vectorized text into vectors respectively and store them as the first independent vector index.

[0098] Specifically, for the analysis table, its metadata is extracted and concatenated into a text sequence using the format "Business Scenario Name + Table Name + Field Chinese Name + Field Physical Name + Field Type + Enumerated Value Summary". For the business knowledge base, the extracted metadata is concatenated into a text sequence using the format "Type Identifier: [Business Concept / Business Rule / Question-Answer Pair / Prompt Word] + Content". Both types of text sequences are uniformly encoded and input into an Embedding model (such as text2vec-large or BGE) to generate a fixed-dimensional dense vector. This vector is then associated and stored with the corresponding metadata ID, analysis table ID, business scenario ID, and field ID. An IVF_FLAT index is built based on Faiss or Milvus, using cosine similarity as the metric and KNN (K-Nearest Neighbor) as the retrieval method, with a default K value of 10. This index is used for semantic-level retrieval of analysis table metadata and business knowledge base metadata, i.e., solving the "where to search" problem.

[0099] It is important to note that when generating vectorized text here, only summary information is retained for the enumeration values, rather than a complete list of enumeration values ​​(for example, only "approximately 7 types of enumeration values" is recorded instead of listing "employed, dispatched, rehired..." one by one). This is one of the key differences between this step and existing technologies: the complete content of the enumeration values ​​will be stored and retrieved separately in a second independent vector index, rather than being mixed here.

[0100] S33. Individually retrieve the field enumeration values ​​from the analysis table corresponding to the business scenario. Iterate through all enumeration values ​​for each field, generating a text sequence in the format of "Field Chinese Name + Field Physical Name + Enumeration Value Text". After converting this sequence into a vector using the same embedding model, associate and store the enumeration value vector with the field ID, enumeration value ID, and analysis table ID. Similarly, build an IVF_FLAT index based on Faiss or Milvus, configuring cosine similarity + KNN retrieval. This index is used for independent semantic recall of field values, i.e., solving the problem of "what value to search".

[0101] Taking the `employment_type` field as an example, its enumeration values ​​"labor contract system", "labor dispatch", and "re-employment" each generate an index record, and each record is associated with the `employment_type` field ID. When the user's question mentions "dispatch personnel", the second independent vector index can recall "labor dispatch" through semantic similarity, even if the user does not use the precise terminology in the database.

[0102] As a further preferred implementation, this step also includes an index update mechanism: monitoring and analyzing changes (additions, modifications, deletions) to the table, business knowledge base, and field enumeration values, triggering incremental updates or periodic full reconstructions of the corresponding index segments to ensure consistency between the index and the source data.

[0103] The two-layer independent vector index constructed through the above steps can achieve the following two key effects:

[0104] First, context slimming. When generating prompts during subsequent searches, only the metadata and enumeration values ​​of the matched candidate fields are provided to the large language model, instead of inputting all fields and enumeration values ​​from the analysis table at once. This means that even if the analysis table contains hundreds of fields, each with dozens of enumeration values, the context received by the large language model remains within a controllable range, not exceeding its context window limit, and avoiding noise interference from a large amount of irrelevant information. This effect is particularly significant when processing extremely long or large tables.

[0105] Second, the foundation for preventing illusions. All enumeration values ​​stored in the second independent vector index come from the actual values ​​in the analysis table, providing a data foundation for value verification in subsequent steps. If the large language model generates non-existent enumeration values ​​when generating SQL, the system can detect and correct them before execution, rather than only noticing after the SQL execution throws an error.

[0106] S4. Obtain the business scenario selected by the user and the user's question under that business scenario. Based on the first independent vector index corresponding to the business scenario, obtain the knowledge base content and candidate fields, and based on the candidate fields and the second independent vector index, obtain the candidate enumeration value.

[0107] In a sample example, taking the user-selected business scenario as human resources analysis, this step specifically includes the following sub-steps:

[0108] S41. Convert user questions into question vectors. Based on these question vectors, query the closest one or more analysis tables through the first independent vector index corresponding to the human resources analysis scenario, and retrieve knowledge base content from the corresponding business knowledge base, including relevant business concepts, business rules, and question-answer pairs. If the business scenario is only bound to a single analysis table, then that table is used directly.

[0109] In this example, the retrieved analysis tables include the employee basic information analysis table 1 shown above, and may also include other related analysis tables, which are not illustrated here. The same embedding model as in step S3 can be used to convert user questions into question vectors.

[0110] S42. Within the scope of the retrieved analysis table, perform field-level retrieval based on the question vector, and determine a preset number of candidate fields by combining vector similarity and keyword matching.

[0111] Specifically, the metadata vectors of all fields in the retrieved analysis table are subjected to KNN retrieval (K=10) with the question vector, and then weighted and fused with exact keyword matching. For example, a weighted formula of "cosine similarity score × 0.7 + keyword hit score × 0.3" is used to select Top-K candidate fields from all fields in the analysis table. Keyword hit refers to the exact match between words in the Chinese or physical name of the field and words in the user's question; a hit earns extra points. This hybrid scoring method can capture fields with similar semantics but different wording (e.g., the user saying "unit" can match the physical name "secondary_unit"), while also not missing cases of exact word matching.

[0112] For example, if a user asks "to count the number of employees under labor contracts in each secondary unit", after a field-level search, the candidate fields may be: secondary_unit (secondary unit), employment_type (employment type), and employee_no (employee number), while fields irrelevant to the question, such as gender and political_status, will not be selected.

[0113] S43. For each determined candidate field, perform value-level retrieval on the corresponding field enumeration value in the second independent vector index, and determine a preset number of candidate enumeration values ​​by combining vector similarity and multi-keyword matching.

[0114] Specifically, using the candidate fields determined in the previous step as the scope, a second KNN search (K=5) is performed on the enumerated values ​​of each candidate field in the second independent vector index. Combining vector similarity and multi-keyword matching, the Top-K candidate enumerated values ​​for each candidate field are determined. The search scope in this step is strictly limited to the candidate fields, rather than blindly searching through all enumerated values, reflecting a progressive search strategy that narrows down the search layer by layer in a "funnel" manner.

[0115] Taking the candidate field employment_type as an example, if the user's question contains the keyword "contract system", the system can retrieve "labor contract system" in the second independent vector index through semantic similarity retrieval. Even if the user does not use the full term "labor contract system" stored in the database but only says "contract system", the system can still match correctly.

[0116] S5. Generate prompts related to user questions based on knowledge base content, candidate fields, and candidate enumeration values.

[0117] Specifically, the analysis table information obtained from the three-level retrieval, candidate fields (including Chinese names, physical names, and types), candidate enumeration values, and business concepts, business rules, and question-answer pairs obtained from S41 are concatenated into prompts using a structured template. The template clearly identifies the range of fields and enumeration values ​​and requires that the large language model prioritize using the field names and enumeration values ​​from the retrieval results when generating SQL.

[0118] A typical prompt structure is shown below:

[0119] [Table Information]

[0120] Table name: hr_employee_base_info

[0121] Table Description: This table records basic employee information, covering dimensions such as organizational structure, position, employment type, and basic personal information.

[0122] [Candidate fields and optional enumeration values]

[0123] secondary_unit (commonly used to filter secondary units), possible values: headquarters, branch office, subsidiary;

[0124] employment_type (commonly used to filter employment type), possible values: labor contract, labor dispatch, re-employment;

[0125] employee_no (employee number) has no limit on the number of enumerated values.

[0126] [Business Knowledge]

[0127] Definition of scope: Employment scope = employment_type='Employed'; Employment scope = employment_type belongs to [Employed, Subtotal, Dispatch, Re-employment]; Employment scope = employment_type belongs to [Employed, Dispatch, Re-employment];

[0128] Statistical method: The number of employees is counted by using COUNT(DISTINCT employee_no) to remove duplicate employee IDs.

[0129] [Q&A Reference]

[0130] Question: "Number of contract employees at the headquarters of the statistical agency";

[0131] SQL: SELECT COUNT(*) FROM hr_employee_base_info WHERE secondary_unit='Headquarters' AND employment_type='Labor Contract System'.

[0132] [User Issues]

[0133] The number of employees under labor contracts in each secondary unit was counted.

[0134] Please generate an executable SQL statement based on the information above. You must use the field names and enumeration values ​​listed in [Candidate Fields and Optional Enumeration Values].

[0135] It is important to note that the above suggestions only include three candidate fields and their enumerated values, not all fields of the analysis table (the original table has more than a dozen fields). This is a direct effect of the dual-level independent vector index in this invention: irrelevant fields and enumerated values ​​are not included in the suggestions, thus avoiding context bloat.

[0136] S6. Based on the user's question and related prompts, generate executable SQL using a large language model, and obtain query results based on the executable SQL.

[0137] In one illustrated example, this step specifically includes the following sub-steps:

[0138] S61. Query Rewriting: Rewrite based on user questions and historical conversations. The specific logic is as follows:

[0139] Casual chat identification and interception: The intent recognition based on a large model is used to determine whether it is casual chat (greeting, thanks, no query intent). If it is, a preset response is returned directly without entering the SQL generation process.

[0140] Rewriting decision: If there is no historical dialogue, or the current question is semantically complete and independent (containing explicit table / field references), then skip rewriting; otherwise, concatenate the current question with the most recent N rounds of dialogue, call the large model (such as Qwen or a finely tuned version of LLaMA instructions) to complete the context and resolve the references, and output the rewritten question.

[0141] For example, in a previous conversation, the user first asked "How many people are in the company?" and then asked "By department?" The second question was semantically incomplete. After rewriting, it was supplemented to "The distribution of the number of people in each department of the company", making it independently executable.

[0142] S62. SQL Generation: Combine the rewritten user question with the prompt words generated in step S5 into a unified input and pass it into the large language model. The model is required to enable the Chain-of-Thought deep thinking mode and perform reasoning according to the following steps: (1) Identify the required analysis table and fields; (2) Determine the filtering conditions and enumeration value matching; (3) Select the aggregation method (GROUP BY, COUNT, SUM, etc.); (4) Output the final executable SQL statement.

[0143] As a further improved implementation, the following operation (anti-illusion mechanism) is also performed before retrieving query results based on executable SQL statements:

[0144] S63. Verify whether the field condition values ​​in the generated executable SQL statement are in the corresponding field enumeration value set of the second independent vector index; if not, replace the mismatched values ​​with the enumeration values ​​with the highest similarity in the second independent vector index and regenerate the SQL, or prompt the user that the verification failed.

[0145] A specific example is as follows:

[0146] (a) Extract all parts of the generated SQL statement that involve field condition values ​​(i.e., the list of values ​​for "field name = 'value'" or "field name IN (...)" in the WHERE clause);

[0147] (b) Check each value to see if it is in the enumerated value set of the corresponding field in the second independent vector index. Since the second independent vector index stores the actual values ​​extracted from the analysis table, this check can effectively identify non-existent values ​​generated by the large language model;

[0148] (c) If a mismatched value is found, the correction process is triggered: retrieve the real enumeration value with the highest semantic similarity to the mismatched value in the second independent vector index, replace it with the real enumeration value, and then call the large language model again to generate SQL; or directly prompt the user that the verification failed and ask the user to verify the problem description.

[0149] For example, if the SQL generated by the large language model contains 'employment_type=contract worker', while the actual enumeration set of the employment_type field in the database is {labor contract system, labor dispatch, re-employment}, the verification mechanism will find that "contract worker" is not in this set, automatically replace it with "labor contract system" which has the highest similarity, and then regenerate the SQL, or prompt the user "'contract worker' does not exist, do you mean 'labor contract system'?"

[0150] S64. SQL Execution: Submits the validated SQL statement to the target database execution engine (such as the JDBC executor or Spark SQL engine), captures the query results, and returns them to the user front end.

[0151] In some examples, the query results can be presented to users in intuitive charts (such as bar charts, line charts, pie charts, etc.) and reports according to preset visualization rules and chart types, making it convenient for users to conduct data analysis and decision-making.

[0152] Example 2

[0153] See Figure 2 The second embodiment of the present invention provides an intelligent questioning system for implementing the method shown in the first embodiment above, including:

[0154] The intelligent data analysis platform is used to establish connections with data sources, build datasets, create multiple business scenarios and corresponding analysis tables and business knowledge bases, and provide user question entry and SQL execution result display interface.

[0155] The algorithm platform communicates and connects with the intelligent data analysis platform to perform the following core functions:

[0156] Two-layer index construction: Obtain metadata from the analysis table and business knowledge base from the intelligent data analysis platform, extract the metadata from the analysis table and business knowledge base corresponding to each business scenario to construct the first independent vector index, and separately obtain the field enumeration values ​​from the analysis table to construct the second independent vector index decoupled from the first independent vector index;

[0157] Index synchronization update: Monitor and analyze changes in the table, business knowledge base, and field enumeration values, and perform incremental updates or full reconstruction of the first and second independent vector indexes;

[0158] Multi-level retrieval and prompt word construction: Based on the business scenario and input question selected by the user on the intelligent data analysis platform, the candidate analysis table and knowledge base content are retrieved through the first independent vector index, candidate fields are determined through field-level retrieval, candidate enumeration values ​​are retrieved through the second independent vector index, and finally structured prompt words are generated;

[0159] SQL generation and validation execution: Based on the user's question and prompt words, the system calls the large language model to generate SQL, performs enumeration value validation on the generated SQL, submits the validated SQL to the database for execution, and feeds the query results back to the intelligent data analysis platform for display.

[0160] A third embodiment of the present invention also provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the method described in the first embodiment above.

[0161] A fourth embodiment of the present invention also provides an electronic device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the program to implement the method described in the first embodiment above.

[0162] It should be noted that the method of this embodiment can be executed by a single device, such as a computer or server. The method of this embodiment can also be applied to a distributed scenario, where multiple devices cooperate to complete the task. In such a distributed scenario, one of these devices may execute only one or more steps of the method of this embodiment, and the multiple devices will interact with each other to complete the method described.

[0163] The embodiments of this invention are intended to cover all such substitutions, modifications, and variations falling within the broad scope of the appended claims. Therefore, any omissions, modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of the embodiments of this invention should be included within the protection scope of this invention.

Claims

1. A method for intelligent number retrieval, characterized in that, include: Based on data source information, a connection is established between the intelligent data analysis platform and the data source, and data is obtained from the data source and a dataset is constructed based on preset rules and conditions; Establish multiple business scenarios. Based on the established business scenarios, select data fields and records from the constructed dataset to create at least one analysis table for each business scenario, and build a business knowledge base related to each business scenario. Extract the metadata of the analysis table and business knowledge base corresponding to each business scenario, establish the first independent vector index for the corresponding business scenario based on the extracted metadata, and obtain the field enumeration values ​​in the analysis table separately. Construct a second independent vector index decoupled from the first independent vector index based on the field enumeration values. Obtain the business scenario selected by the user and the user's question under that business scenario. Based on the first independent vector index corresponding to the business scenario, obtain the knowledge base content and candidate fields, and based on the candidate fields and the second independent vector index, obtain the candidate enumeration value. Based on the knowledge base content, candidate fields, and candidate enumeration values, generate prompt words related to the user's question; Based on the user's question and related prompts, an executable SQL statement is generated using a large language model, and the query results are obtained based on the executable SQL statement.

2. The intelligent questioning method according to claim 1, characterized in that, The metadata of the analysis table includes at least the table name and enumeration values, as well as one or more of the following: physical field name, Chinese field name, field type, and data type; the metadata of the business knowledge base includes business concepts, business rules, question-answer pairs, and prompt words.

3. The intelligent questioning method according to claim 2, characterized in that, Metadata from the analysis table and business knowledge base corresponding to each business scenario is extracted separately. A first independent vector index for the corresponding business scenario is established based on the extracted metadata. Field enumeration values ​​from the analysis table are obtained separately. A second independent vector index, decoupled from the first independent vector index, is constructed based on the field enumeration values, including: Locate the analysis table and business knowledge base corresponding to the target business scenario, and extract various metadata from the found analysis table and business knowledge base; For the analysis table, a first type of vectorized text is generated based on the business scenario name and the extracted metadata of each item in the analysis table; for the business knowledge base, a second type of vectorized text is generated based on the extracted metadata of each item in the business knowledge base. The first type of vectorized text and the second type of vectorized text are converted into vectors respectively and stored as the first independent vector index, which is used to perform semantic retrieval of analysis table metadata and business knowledge base metadata. The field enumeration values ​​in the analysis table corresponding to the target business scenario are obtained separately, converted into vectors and stored as a second independent vector index, which is used to perform independent semantic recall of field values; Configure the cosine similarity algorithm for the first independent vector index and the second independent vector index respectively, and enable KNN search.

4. The intelligent questioning method according to claim 1, characterized in that, Based on the first independent vector index corresponding to the business scenario, the knowledge base content and candidate fields are obtained, and based on the candidate fields and the second independent vector index, candidate enumeration values ​​are obtained, including: User questions are converted into question vectors. Based on these question vectors, the closest analysis table is queried through the first independent vector index corresponding to the business scenario. Knowledge base content is then obtained from the corresponding business knowledge base, which includes relevant business concepts, business rules, and question-answer pairs. Within the scope of the retrieved analysis table, field-level retrieval is performed based on the question vector, and a preset number of candidate fields are determined by combining vector similarity and keyword matching. For each candidate field that has been identified, a value-level search is performed on the corresponding field enumeration value in the second independent vector index, and a preset number of candidate enumeration values ​​are determined by combining vector similarity and multi-keyword matching.

5. The intelligent questioning method according to claim 1, characterized in that, Based on user questions and related prompts, executable SQL is generated using a large language model, including: The query is rewritten based on the user's question and historical dialogue to generate a rewritten user question. The rewritten user question and related prompts are input into the large language model, and an executable SQL statement is generated based on the input in deep thinking mode.

6. The intelligent questioning method according to claim 5, characterized in that, The enumerated values ​​stored in the second independent vector index are the actual values ​​extracted from the analysis table; Before obtaining the query results based on the executable SQL statement, the process also includes: Verify whether the field condition values ​​in the generated executable SQL statement are in the corresponding field enumeration value set of the second independent vector index; If not, the mismatched value will be replaced with the most similar enumerated value in the second independent vector index, and the SQL will be regenerated, or the user will be prompted that the verification failed.

7. The intelligent questioning method according to claim 5, characterized in that, Based on user questions and historical conversations, the query is rewritten to generate rewritten user questions, including: The system identifies and blocks casual conversation. If the question falls into the category of casual conversation, it returns a preset response directly; otherwise, it proceeds to the next step. If the user's current question has no historical dialogue context, or if the current question itself is semantically complete and unrelated to the historical dialogue, then skip the rewriting; otherwise, combine the content of the historical dialogue and call the large model to perform semantic rewriting of the current question.

8. A system for intelligent number retrieval, used to implement the method as described in any one of claims 1-7, characterized in that, include: The intelligent data analysis platform is used to establish connections with data sources, build datasets, create business scenarios, analysis tables and business knowledge bases, and provide user question entry points and SQL execution result display interfaces. An algorithm platform, which is communicatively connected to the intelligent data analysis platform, is used for: Extract the metadata from the analysis table and the business knowledge base, establish a first independent vector index based on the extracted metadata, and establish a second independent vector index based on the field enumeration values ​​in the analysis table; Based on the user's selected business scenario and the user's question within that business scenario, relevant prompt words for the user's question are generated by retrieving them using the first independent vector index and the second independent vector index. Based on user questions and related prompts, an executable SQL query is generated using a large language model, and the query results are fed back to the intelligent data analysis platform.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the program is executed by the processor, it implements the method as described in any one of claims 1-7.

10. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the program, it implements the method as described in any one of claims 1-7.

Images