A medical text to SQL method based on retrieval enhancement and hypothesis driving

Abstract

The present application relates to a kind of medical text conversion SQL method based on retrieval enhancement and hypothesis driving, belong to computer database interface technology and natural language processing technical field.This method first utilizes retrieval enhancement generation mechanism to dissolve clinical rhetoric, relies on " generation hypothesis " of large model intelligent reasoning base, drives large model to generate " virtual query " with perfect word source naming based on user query and prior knowledge reasoning generation, in accordance with SQL logic skeleton.Finally, this kind of ideal state intermediate relay medium is thrown to the enhanced physical pooling library consisting of " macro-narrative space " and " micro-analysis space " with complex underlying space, and the semantic similarity soft matching and graph search completion are carried out.The present application effectively breaks through the gap between user rough intention and database, can effectively dissolve industry terminology barrier, effectively solves the pain point that clinical doctor oral question and underlying standardization system cannot match, realizes high efficiency, cost reduction.

Description

Technical Field

[0001] This invention relates to an improved method for intelligent query systems of large-scale medical relational databases, specifically to a text-to-SQL parsing method executed by the processor of the medical database intelligent query system, based on medical terminology retrieval enhancement and hypothesis structure-driven dual-space retrieval, belonging to the fields of computer database interface technology and natural language processing technology. Background Technology

[0002] With the leap in computing power of large language models, Text-to-SQL applications have demonstrated enormous potential for handling complex natural language database question answering. However, when this technology is applied to real-world, ultra-large-scale healthcare databases with thousands of obscure tables and physical columns, it faces severe challenges. The core issue is that there is a deep semantic gap between the vague, colloquial, and medically abbreviated natural business query intents of clinicians and the highly standardized (e.g., requiring compliance with ICD disease codes and LOINC test codes), heavily abbreviated, and rigidly formatted physical schemas in the underlying database.

[0003] To address the aforementioned issues and achieve seamless integration between the two technologies, engineers have proposed various solutions, but all have limitations. Existing text-to-SQL technologies suffer from three main technical shortcomings in ultra-large-scale medical database scenarios:

[0004] 1. Insufficient accuracy in heterogeneous semantic matching. Conventional dense vector retrieval models have limited ability to represent cross-morphological semantics, and cannot establish semantic mapping between clinical colloquialisms, slang, and ICD / LOINC standard codes, as well as abbreviated physical column names. Furthermore, natural language queries typically do not carry word structures with specific underlying standard codes. Conventional dense vector retrieval methods cannot effectively match "clinical terms" with "standard underlying columns / enumeration values," resulting in low recall and precision in database pattern retrieval, and insufficient accuracy in hitting core columns.

[0005] 2. There is a trade-off between long context resource consumption and inference performance. Ultra-large-scale databases contain thousands of tables and tens of thousands of physical fields. Directly cramming these massive physical columns and table structures into a large language model context system consumes a significant amount of context window resources, leading to a surge in inference computational costs and a substantial increase in response latency. Simultaneously, long contexts can cause attention decay, directly reducing the accuracy of the model's generated results.

[0006] 3. Lack of hard constraints on the topological validity of generated results. Pure generative methods rely solely on the model's implicit learning of table relationships, lacking explicit constraints on the actual database topology. This often results in topological errors in the generated SQL statements, such as illegal table joins and broken foreign key paths, making them unexecutable in the database system and leading to a low success rate.

[0007] In summary, there is an urgent need for a new technical processing method for intelligent query systems of medical databases. This method should enable text-to-SQL parsing based on enhanced medical terminology retrieval and hypothesis-driven dual-space retrieval through the collaborative execution of a processor, database, offline preprocessing module, online retrieval module, and SQL generation module. Summary of the Invention

[0008] The purpose of this invention is to address the technical shortcomings of existing intelligent query systems for medical databases, such as difficulty in aligning medical terminology, rapid overload of lengthy and worthless contexts, and the extreme difficulty in overcoming semantic gaps with conventional vector matching. This invention creatively proposes a medical text-to-SQL method based on retrieval enhancement and hypothesis-driven approaches, executed by the intelligent query system for medical databases, which can balance high industry terminology comprehension, high recall, and low latency.

[0009] The innovations of this invention include: Unlike existing one-time discriminative matching methods, this method first uses a medical database intelligent query system processor to invoke a retrieval enhancement generation mechanism to standardize and semantically align clinical colloquial terms. Then, the processor invokes a large language model to generate a logical-level virtual query intermediate representation based on user queries, prior graph structures, and terminological knowledge. This virtual query intermediate representation includes a top-level primary and secondary structure, semantic fields, and an SQL logical skeleton. Finally, the system maps this virtual query intermediate representation to an enhanced physical pooling library composed of a "macro-level representation space" and a "micro-level analysis space," achieving physical pattern matching with the underlying database through semantic similarity calculation and graph search completion.

[0010] The working process of this method is as follows:

[0011] The processing chain is established through a phased decoupling process executed by the intelligent medical database query system. First, the offline preprocessing module constructs a dual-path offline base library: one path processor calls a large language model and combines it with sampled data to generate enhanced semantic descriptions for each native database column; the other path processor introduces medical standard coding specifications to construct a medical ontology terminology knowledge vector library covering clinical colloquialisms and underlying enumeration mappings. Subsequently, when the online retrieval module receives a natural language query, it triggers a hybrid RAG (Retrieval Augmented Generation) retrieval mechanism, aligning colloquial professional terms into standard medical explanations as prior context. Based on this, the processor calls the large language model and, based on the user query, terminology dictionary, and graph association definitions, generates a logical-level virtual query intermediate representation.

[0012] The system performs dual-space verification and topology completion. At the macro level, the online retrieval module calculates vector similarity between virtual column information and real semantic profiles to locate candidate entity objects. At the micro level, the online retrieval module matches atomic sub-problems obtained from natural language query decomposition with a synthetic question library to extract high-granularity candidate columns. To reduce the risk of isolated extraction of unrelated SQL tables due to local feature comparisons in this stage, the graph search module performs optimal solution path graph search based on the Steiner tree algorithm, calculating and inserting join tables and foreign key columns that connect isolated table blocks along the global foreign key system, ensuring that the generated candidate pattern skeleton can be executed in the database connection environment and has graph connectivity and reachability. In addition, the system uses a source feature set with hit reason traceability tags, input into the large language model for screening decisions based on a preset algorithm. The large language model aims to retain necessary topological components and remove low-frequency irrelevant noise to perform compression and noise reduction.

[0013] Finally, the extracted core content is handed over to the SQL generation module, where the processor synthesizes predictive SQL under preset constraint instructions.

[0014] Beneficial effects

[0015] This invention has the following significant advantages over the prior art:

[0016] 1. This invention generates a virtual query intermediate representation that is dynamically deduced by the processor and maps it to the physical schema of the database, thereby reducing the semantic gap between the user's natural language intent and the database structure;

[0017] 2. This invention can effectively break down industry terminology barriers. This method introduces a medical ontology-based RAG mechanism, effectively addressing the industry pain point of mismatch between clinicians' colloquial questioning and underlying standardized systems (such as the ICD / LOINC system).

[0018] Experiments have shown that this invention can stably maintain an extremely high hit rate for high-level real core columns. At the same time, by using multiple source tracing and pruning techniques, it can effectively reduce contextual interference noise by about 50%. By eliminating the need for complex iterative interaction and polling by multiple agents, it can significantly reduce the system's computing power and energy consumption, achieving efficient cost reduction and speed improvement. Attached Figure Description

[0019] Figure 1 This is an architectural diagram of the method of the present invention.

[0020] Figure 2 This is an overall flowchart of the method of the present invention. Specific implementation methods

[0021] The present invention will now be described in further detail with reference to the accompanying drawings and embodiments.

[0022] like Figure 1 As shown, a medical text-to-SQL method based on retrieval enhancement and hypothesis-driven approaches is applied to a medical database intelligent query system that includes a processor, memory, database, offline preprocessing module, online retrieval module, graph search module, and SQL generation module. The method includes the following steps:

[0023] Step 1: Construct an offline enhanced semantic space and inverted index using the offline preprocessing module of the medical database intelligent query system.

[0024] Step 2: The online retrieval module generates a hypothetical structure-driven virtual query intermediate representation.

[0025] Step 3: The online retrieval module and the graph search module perform dual-space semantic retrieval and graph-guided completion.

[0026] Step 4: The processor performs source-aware pattern pruning.

[0027] Step 5: The SQL generation module builds an enhanced context to guide the large language model in generating SQL.

[0028] In this embodiment, all the above steps are executed automatically by the system. Specifically, the database stores the target medical relational database and its table structure, fields, and foreign key relationships; the offline preprocessing module generates semantic profiles, synthesizes question sets, entity inverted indexes, and a medical ontology terminology knowledge vector library; the online retrieval module performs terminology RAG retrieval, dual-space vector retrieval, and candidate set recall; the graph search module performs connected subgraph completion based on the database foreign key graph; and the SQL generation module generates and corrects SQL statements under multi-dimensional structural constraints.

[0029] The steps are described in detail below with reference to specific embodiments. Figure 2 As shown.

[0030] Step 1: The offline preprocessing module builds an offline enhanced semantic space and inverted index.

[0031] Before the system goes online, the offline preprocessing module first processes the target database offline to build an enhanced semantic space.

[0032] This process is executed only once. The specific steps are as follows:

[0033] Step 1.1: The offline preprocessing module generates a semantic profile.

[0034] For each physical column in the database Sample several non-empty data values ​​from this column.

[0035] Subsequently, the processor invokes the large language model, inputting the table name, column name, and sampled data, to generate a natural language semantic description of that column. .

[0036] For example, for the column name "diag_icd", the model-generated description might be "Stores the ICD-10 standard clinical diagnosis code of the patient at discharge".

[0037] This step compensates for the semantic loss caused by the abbreviation of physical column names.

[0038] Step 1.2: The offline preprocessing module generates the synthetic problem set.

[0039] Generative natural language semantic description This again guides the large model to generate a set of typical synthesis problems for this column. . It covers common operation types in SQL queries. For example: "Find records with diagnosis code I21", "Count the total number of patients with a specific disease", "Sort by admission date", etc.

[0040] This step establishes a benchmark for subsequent micro-intent matching.

[0041] Step 1.3: The offline preprocessing module builds the entity inverted index.

[0042] Iterate through all columns of text types such as VARCHAR and TEXT in the database and extract the most frequent literal values. Build an inverted index , making That is, a target value is recorded. In which columns of which tables does it appear? Among them, Indicates that the target value is included. The physical table, Indicates the target value The column it belongs to.

[0043] This step provides support for subsequent hard matching of entities.

[0044] Step 1.4: The offline preprocessing module constructs a medical ontology terminology knowledge vector library.

[0045] Collect medical industry standard coding systems such as ICD-10 and LOINC, as well as departmental slang. Construct ternary profiles, for example: [slang: "triple positive" -> standard semantics: "Hepatitis B surface antigen, E antigen and core antibody are all positive" -> factual enumeration value: "HBsAg(+), HBeAg(+), HBcAb(+)"] etc.

[0046] These slices are vectorized and encoded for storage, forming a terminology RAG retrieval base.

[0047] After completing the above steps, input all semantic profiles and synthetic question sets into the encoder to generate corresponding vectors and build a vector retrieval index for use in the online phase.

[0048] Step 2: The online retrieval module generates a hypothetical structure-driven virtual query intermediate representation.

[0049] When a user enters a natural language query, the intelligent medical database query system first enters the virtual query intermediate representation generation stage. .

[0050] In this embodiment, the user query is: "Statistics on the total number of patients with acute myocardial infarction (AMI) and dyslipidemia admitted to the outpatient department last year." The system first extracts the entities "AMI" and "dyslipidemia" and performs a mixed search in the medical ontology terminology database to recall prior knowledge: "AMI" corresponds to the standard diagnostic ICD-10 code I21.9; "dyslipidemia" corresponds to the test item LDL-C with a result >3.4 mmol / L.

[0051] Then, the system obtains the global graph prior of the database. This prior includes table names, table descriptions, and foreign key relationships between tables, but hides all physical column details to avoid exceeding the large language model context window length limit.

[0052] Subsequently, the processor combines the prior knowledge retrieved by RAG with the database structure, and calls the large language model to perform divide-and-conquer reasoning, which includes two stages:

[0053] Decomposition Phase: The large language model first breaks down the global query into atomic sub-problems. For example:

[0054] "Identify outpatient records from last year."

[0055] "Screen patients diagnosed with AMI (ICD: I21.9)".

[0056] "Records with LDL-C greater than 3.4 in the screening test indicators."

[0057] "Count the total number of patients who meet the above conditions and remove duplicates."

[0058] Synthesis Phase: Based on the atomic subproblem and global graph prior, the large language model generates a virtual query intermediate representation in the form of DummySQL, and generates semantic virtual column names, for example:

[0059] SELECT COUNT(DISTINCT Patient Name) FROM Diagnosis Table, JOIN Test Table, JOIN Visit Table, WHERE Diagnosis Code = 'I21.9', Test Indicator = 'LDL-C' AND, Indicator Value > 3.4, Visit Year = 2023.

[0060] This DummySQL does not directly correspond to actual physical columns, but is a logical-level query skeleton generated by the processor. It provides the system with two key anchors: a macroscopic description of virtual columns and a microscopic set of atomic subproblems.

[0061] Step 3: The online retrieval module and the graph search module perform dual-space semantic retrieval and graph-guided completion.

[0062] Based on the anchor points provided by DummySQL, the online retrieval module performs dual-space retrieval, mapping the intermediate representation of the virtual query to the real physical database schema.

[0063] Description space matching: Extract dummy column descriptions from the DummySQL. For each dummy column... Calculate the embedding vector it describes. Then, look up in the vector index and calculate. semantic profile vectors for each physical column cosine similarity :

[0064]

[0065] in, Indicates the actual physical column candidates to be matched; This represents the set of virtual columns extracted by DummySQL; Indicates the target table to which the current virtual column belongs or is constrained.

[0066] The system selects the top similarity values ​​according to a preset similarity threshold τ (e.g., 0.75). Each column (such as the first 5) forms a macroscopic candidate set. In this embodiment, "diagnostic code" matches the physical column "diag_icd_code", and "inspection index" matches "lab_item_name".

[0067] Problem space matching: Extracting the set of subproblems derived from the decomposition Examples include "counting the number of patients in each department." The embedding vectors of these atomic subproblems are calculated. Then, find the composite problem set of the physical column. Calculate the maximum matching score between the two question sets. :

[0068]

[0069] in, This represents the set of atomic subproblems obtained by decomposing a natural language query. Representing the composition problem The corresponding embedding vector. Similarly, select the first few vectors exceeding the threshold. Each column (such as the first 5) forms a micro-candidate set.

[0070] This step captures fine-grained analytical intent such as "aggregate calculations," recalling columns that might have been missed in the macro-level description.

[0071] Finally, the two candidate sets are merged to obtain preliminary candidate columns. At this point, the candidate columns are distributed across tables such as the "Diagnosis Table" and the "Test Details Table". The database schema is modeled as a graph, and an approximate solution using the Steiner tree algorithm (such as the Dreyfus-Wagner approximate Steiner tree algorithm) is used to obtain the minimum connected subgraph connecting these discrete tables. Since the "Diagnosis Table" and the "Test Details Table" need to be connected through the "Medical Record Table", the system automatically adds the "Medical Record Table" and its corresponding foreign key columns to the candidate set, thereby ensuring full connectivity of the candidate schema and resolving the topological blind spot problem.

[0072] At this point, the intermediate candidate set was obtained.

[0073] Step 4: The processor performs source-aware pattern pruning.

[0074] Because dual-space retrieval employs a high-recall strategy, the intermediate candidate set contains some noisy columns. Therefore, the processor needs to perform source-aware pruning.

[0075] First, the system generates source labels for each column in the intermediate candidate set.

[0076] The processor then inputs these labeled patterns and the original query into the large language model, and uses preset instructions to filter out noisy columns that are irrelevant to the query based on the source information of the columns, while retaining all necessary columns and join keys.

[0077] After the large language model performs inference based on the original query, fields retrieved solely by weak matching are eliminated, while relevant fields are retained, resulting in a compact subset of the final pattern. This subset is much smaller than the original database, making it suitable for the context window of the large language model.

[0078] Step 5: The SQL generation module builds an enhanced context to guide the large language model in generating SQL.

[0079] Specifically, four types of constraints are injected:

[0080] Physical constraints: A subset of the final pattern, containing semantic profiles.

[0081] Fact constraints: The values ​​that may appear in the query and their corresponding fields.

[0082] Logical constraints: This query may require grouping, aggregation, or descending sorting.

[0083] Technical terminology constraints: Provides standard alignment information recalled from the medical ontology by the RAG mechanism.

[0084] The SQL generation module calls the large language model to generate the final SQL statement based on the above information.

[0085] If an error occurs during execution, the system will automatically send the database execution error information back to the large language model for iterative correction.

[0086] Furthermore, the present invention also provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the medical text-to-SQL method based on retrieval enhancement and hypothesis-driven approach described in the present invention.

Claims

1. A medical text-to-SQL method based on retrieval enhancement and hypothesis-driven approaches, applied to an intelligent medical database query system including a processor, memory, database, offline preprocessing module, online retrieval module, graph search module, and SQL generation module, characterized in that, First, the processor invokes the retrieval enhancement generation mechanism to standardize and semantically align clinical colloquial terms. Then, the processor invokes the large language model to generate a logical-level virtual query intermediate representation based on user queries, prior graph structures, and terminology knowledge. This virtual query intermediate representation includes a top-level primary and secondary structure, semantic fields, and an SQL logical skeleton. Finally, the system maps this virtual query intermediate representation to an enhanced physical pooling library composed of a "macro-level representation space" and a "micro-level analysis space." Through semantic similarity calculation and graph search completion, it achieves physical pattern matching with the underlying database. Includes the following steps: Step 1: Construct an offline enhanced semantic space and inverted index using the offline preprocessing module of the medical database intelligent query system; The offline preprocessing module first processes the target database offline to construct an enhanced semantic space; this process is executed only once. Step 2: The online retrieval module generates a hypothetical structure-driven virtual query intermediate representation; When a user enters a natural language query, the system first enters the virtual query intermediate representation generation stage. ; Then, the system obtains the global graph prior of the database. This prior includes table names, table descriptions, and foreign key relationships between tables, but hides all physical column details; Subsequently, the processor combines prior knowledge and database structure to call the large language model to perform divide-and-conquer reasoning; Step 3: The online retrieval module and the graph search module perform dual-space semantic retrieval and graph-guided completion; Step 4: The processor performs source-aware pattern pruning; First, the system generates source labels for each column in the intermediate candidate set; Subsequently, the processor inputs these labeled patterns and the original query into the large language model, and through preset instructions, it filters out noisy columns that are irrelevant to the query based on the source information of the columns, while retaining all necessary columns and join keys; After the large language model performs inference in conjunction with the original query, it removes fields that are recalled by weak matching only, while retaining relevant fields, and finally obtains a compact subset of the final pattern. Step 5: The SQL generation module constructs an enhanced context to guide the large language model in generating SQL; If an error occurs during execution, the system will automatically send the database execution error information back to the large language model for iterative correction.

2. The medical text-to-SQL method based on retrieval enhancement and hypothesis-driven approach as described in claim 1, characterized in that, Step 1 includes: Step 1.1: The offline preprocessing module generates a semantic profile; For each physical column in the database Sample several non-empty data values ​​from this column; Subsequently, the processor invokes the large language model, inputting the table name, column name, and sampled data, to generate a natural language semantic description of that column. ; Step 1.2: The offline preprocessing module generates the synthetic problem set; Generative natural language semantic description The processor then calls the large language model again to generate a set of typical composition questions for the column, covering common operation types in SQL queries; Step 1.3: The offline preprocessing module constructs the entity inverted index; Iterate through all text-type columns in the database and extract the most frequent literal values. Build an inverted index , making That is, a target value is recorded. In which columns of which tables does it appear; among them, Indicates that the target value is included. The physical table, Indicates the target value The column it belongs to; Step 1.4: The offline preprocessing module constructs a medical ontology terminology knowledge vector library; Collect standard coding systems and departmental slang in the medical industry to construct triplet slices; vectorize and encode these slices for storage to form a terminology RAG retrieval base. After completing the above steps, input all semantic profiles and synthetic question sets into the encoder to generate corresponding vectors and establish a vector retrieval index.

3. The medical text-to-SQL method based on retrieval enhancement and hypothesis-driven approach as described in claim 1, characterized in that, In step 2, the processor invokes the large language model to perform divide-and-conquer reasoning, which includes two stages: Decomposition phase: The large language model first decomposes the global query into atomic sub-problems; Synthesis phase: Based on atomic subproblems and global graph priors, the large language model generates a virtual query intermediate representation in the form of DummySQL and generates semantic virtual column names; this DummySQL is a logical-level query skeleton generated by the processor, providing the system with two key anchors: macroscopic virtual column descriptions and microscopic atomic subproblem sets.

4. The medical text-to-SQL method based on retrieval enhancement and hypothesis-driven approach as described in claim 1, characterized in that, In step 3, based on the anchor points provided by DummySQL, the online retrieval module performs dual-space retrieval, mapping the virtual query intermediate representation to the real database physical schema; Description space matching: Extract dummy column descriptions from the DummySQL; for each dummy column... Calculate the embedding vector it describes. ; Then, look up and calculate in the vector index. semantic profile vectors for each physical column cosine similarity : in, Indicates the actual physical column candidates to be matched; This represents the set of virtual columns extracted by DummySQL; Indicates the target table to which the current virtual column belongs or is constrained; The system selects the previous similarity threshold τ according to the preset similarity threshold. Each column forms a macro-level candidate set; Problem space matching: Extracting the set of subproblems derived from the decomposition ; Calculate the embedding vector of the atomic subproblem Then, find the composite problem set of the physical column. Calculate the maximum matching score between the two question sets. : in, This represents the set of atomic subproblems obtained by decomposing a natural language query. Representing the composition problem The corresponding embedding vector; similarly, select the first one exceeding the threshold. Each column forms a micro-candidate set.

5. The medical text-to-SQL method based on retrieval enhancement and hypothesis-driven approach as described in claim 1, characterized in that, In step 5, four types of constraints are injected, including: Physical constraints: A subset of the final patterns, containing semantic profiles; Fact constraints: the values ​​that may appear in the query and their corresponding fields; Logical constraints: This query may require grouping, aggregation, or descending sorting. Technical terminology constraints: Provide standard alignment information recalled from the medical ontology by the RAG mechanism; The SQL generation module calls the large language model to generate the final SQL statement based on the above information.

6. A computer-readable storage medium, characterized in that, It stores a computer program that, when executed by a processor, implements the medical text-to-SQL method based on retrieval enhancement and hypothesis-driven approaches described in this invention.

Images