Method for converting medical congruent variable text into SQL (Structured Query Language) based on large model and fine tuning technology

Abstract

The invention discloses a large model and fine tuning technology-based medical confusable variable text-to-SQL (structured query language) conversion method, which is characterized by comprising the following steps of: constructing and processing a confusable variable text; a Prompt engineering step is carried out; a model training step; generating an SQL (Structured Query Language) query; and integrating the system. Based on the NLP (Natural Language Processing) technology, the SQL query statement for the medical database can be automatically generated according to the data analysis requirement of a doctor. By using Prompt engineering, efficient parameter fine tuning (PEFT) and fine tuning (Fine-Tuning) technologies, data analysis requirements of doctors can be accurately understood, and then complex SQL query statements are accurately generated, so that the working efficiency and the data processing capability of the doctors are improved.

Description

Technical Field

[0001] The invention relates to a method for converting medical easily confusable variable text into SQL, and belongs to the field of large-model medical applications. Background Art

[0002] With the rapid growth of medical data, doctors and researchers have an increasing demand for data analysis. Traditional data analysis methods usually require professional database knowledge and programming skills, but most doctors do not have these skills, which limits doctors' effective exploration of medical data. Summary of the invention

[0003] The purpose of the present invention is to transform doctors' medical data analysis requirements into SQL query statements through natural language processing (NLP) technology based on large models and fine-tuning technology, thereby simplifying the data analysis process.

[0004] In order to achieve the above object, the technical solution of the present invention is to disclose a method for converting medical confusable variable text to SQL based on a large model and fine-tuning technology, which is characterized by comprising:

[0005] The steps to construct and process confusable variable text include:

[0006] Defining a variable pool further includes: compiling a detailed variable list, including all variables that may appear in the query; listing variable values ​​or ranges;

[0007] Designing the sample structure further includes: designing the basic structure of the sample; designing a natural language description to clearly explain the purpose of the query, the variables involved and any special computing requirements; designing the corresponding SQL query statement;

[0008] Generate basic samples;

[0009] On the basis of the basic sample, variable complexity, conceptual complexity and computational complexity are gradually added;

[0010] Combined with actual application scenarios, simulated complex query requests;

[0011] Balanced sample distribution;

[0012] Verify sample quality;

[0013] Label samples;

[0014] Construct a validation set;

[0015] Continuously update the sample library;

[0016] The steps of the Prompt project include:

[0017] Multi-table association recognition: Add support for multi-table association in prompt design. By clearly indicating the tables that need to be associated and their relationships in the instruction part, the model can understand the connection between data.

[0018] Variable mapping prompt: Add variable mapping information to the prompt, that is, indicate which words in the natural language correspond to which fields or tables in the database;

[0019] Condition filtering guidance: Design special prompts to guide the model to correctly generate query statements containing WHERE clauses, especially when there are multiple condition filters;

[0020] Aggregate function prompts: Provides specific prompt templates for queries involving aggregate functions including SUM, COUNT, and AVG;

[0021] Sorting and grouping prompts: Design prompts that can guide the model to generate query statements containing ORDER BY and GROUP BY clauses;

[0022] Time series analysis prompt: Provides a special prompt design for queries involving time series data;

[0023] Error handling prompts: Design prompts that can help the model identify and correct common SQL syntax errors;

[0024] Complex query examples: Add complex query examples to the prompt, including multi-table joins, nested queries, etc., to enhance the model's ability to handle complex queries;

[0025] User intent parsing prompts: Design specific prompts to help the model better understand the user's query intent, especially when the requirement description is vague;

[0026] Result display format tips: Provide tips on how to format query results to meet the display needs of different users;

[0027] The steps of model training include:

[0028] Data collection and preprocessing: Collect a large amount of medical-related natural language descriptions and corresponding SQL query statements, clean the data, and remove irrelevant information;

[0029] Construct variable pool: Construct a detailed variable list based on the characteristics of the medical field, including diagnosis codes and diagnosis names;

[0030] Design prompt templates: Based on the design principles of the above prompt project steps, create a series of prompt templates that can cover various query types;

[0031] Generate training data: Generate a large number of training samples using the designed prompt template to ensure the diversity and representativeness of the samples;

[0032] Initial model selection: Select a pre-trained large language model as the base model;

[0033] Implement efficient parameter fine-tuning: Add an adapter layer to the selected base model and fine-tune the adapter layer using the training data generated in the previous step to improve the model's understanding and generation capabilities for specific tasks;

[0034] Evaluate the effect of PEFT: Evaluate the effect of PEFT through a small-scale validation set and adjust the design or training parameters of the adapter layer based on the results;

[0035] Fine-tune: In addition to PEFT, continue to fine-tune the entire model and train the entire model using the same training data to achieve better results;

[0036] Continuous iterative optimization: Based on the performance of the validation set, the prompt template, training data, and training strategy are continuously adjusted until the model performance is stable and meets the requirements;

[0037] User feedback loop: deploy a preliminary version of the system to collect user feedback, especially on whether the generated SQL queries are accurate and efficient, and how friendly the user interface is, so as to further optimize the model and system design;

[0038] The steps of SQL query generation include:

[0039] Input parsing: Receive text data of doctors’ diabetes data analysis needs and parse query intent;

[0040] Query generation: Use the trained large language model to generate the corresponding SQL query statement;

[0041] Query verification: perform syntax and logic verification on the generated SQL query to ensure the correctness and validity of the query;

[0042] The steps of system integration include:

[0043] User interface: Provide a user-friendly interface through which doctors can input text of relevant disease data analysis requirements;

[0044] Backend service: The backend service is responsible for receiving user input data, calling the large language model to generate SQL query statements, and returning the results to the user;

[0045] Database connection: The system connects to the relevant disease database, executes the generated SQL query and returns the query results.

[0046] Preferably, generating the basic samples further comprises: randomly selecting a variable combination from a variable pool to construct a simple query request; and writing a corresponding SQL statement for each basic sample.

[0047] Preferably, the balanced sample distribution further includes: ensuring sample diversity; covering all important variables, ensuring that all important variables and calculation methods are included in the sample; avoiding skewness, and ensuring that the frequencies of different types of problems are balanced.

[0048] Preferably, the verification of sample quality further includes: reviewing the generated samples by domain experts or personnel familiar with the business to ensure that they conform to the actual situation and are accurately expressed; and performing preliminary testing on the samples to ensure that the generated SQL statements can be executed correctly.

[0049] Preferably, the labeling process of implementing the labeling sample includes: labeling the natural language description of each sample; labeling the SQL statement corresponding to each sample; multiple labelers: introducing multiple labelers to reduce subjective bias.

[0050] Preferably, the validation set is constructed by extracting the validation set, including: random extraction, randomly extracting a part of the labeled samples as the validation set; reflecting diversity, ensuring that the validation set can reflect the diversity in the real world.

[0051] Preferably, the sample library is continuously updated through updating and optimization, including: regular updates: as new data is added and business needs change, the sample library is regularly updated and expanded; user feedback: through user feedback and model performance, the sample quality and model performance are continuously optimized.

[0052] The present invention is based on natural language processing (NLP) technology and can automatically generate SQL query statements for medical databases according to the doctor's data analysis needs. The present invention uses prompt engineering, parameter efficient fine tuning (PEFT) and fine-tuning technology to accurately understand the doctor's data analysis needs and then accurately generate complex SQL query statements, thereby improving the doctor's work efficiency and data processing capabilities. BRIEF DESCRIPTION OF THE DRAWINGS

[0053] Figure 1 A flow chart of the present invention is illustrated. DETAILED DESCRIPTION

[0054] The present invention will be further described below in conjunction with specific embodiments. It should be understood that these embodiments are only intended to illustrate the present invention and are not intended to limit the scope of the present invention. In addition, it should be understood that after reading the content taught by the present invention, those skilled in the art may make various changes or modifications to the present invention, and these equivalent forms fall within the scope limited by the appended claims of the application equally.

[0055] like Figure 1 As shown, a method for converting medical confusable variable text to SQL based on a large model and fine-tuning technology disclosed in an embodiment of the present invention includes:

[0056] Steps to construct and process confusable variable text;

[0057] Prompt project steps;

[0058] Steps of model training;

[0059] Steps for SQL query generation;

[0060] Steps for system integration.

[0061] 1) Steps to construct and process confusing variable text

[0062] (1) Define the variable pool:

[0063] Prepare a variable list: Prepare a detailed variable list, including all variables that may appear in the query, such as diagnosis code, diagnosis name, ID number, card number, hospital name, examination name, drug name, etc.

[0064] List variable values ​​or ranges: for example: 1) diagnosis codes E11.2, I21.1, G56.1, etc.; 2) diagnosis name: neuropathy, retinopathy, nephropathy, etc.; 3) ID number: 123456789012345678 or 123456789012345; 4) examination name: triglycerides, glycosylated hemoglobin, random blood glucose, etc.; 5) drug name: XN00000000000, etc.

[0065] (2) Design the sample structure, including:

[0066] Basic structure of the sample;

[0067] Natural language description: clearly state the purpose of the query, the variables involved, and any special computational requirements;

[0068] SQL statement: the corresponding SQL query statement.

[0069] (3) Generate basic samples:

[0070] Randomly select variable combinations from the variable pool and construct simple query requests, such as "query all records of diabetic patients" and "count all the people who have had their glycated hemoglobin checked";

[0071] For each basic sample, write the corresponding SQL statement.

[0072] (4) Increased complexity:

[0073] On the basis of the basic sample, variable complexity, concept complexity and computational complexity are gradually added. For example, for the basic sample of "primary diagnosis is diabetes", it can be expanded to "the medical records of patients with primary diagnosis of diabetes and secondary diagnosis of hypertension in Hospital A".

[0074] (5) Simulate real scenarios:

[0075] Combined with actual application scenarios, complex query requests are simulated, such as "According to the patient's card number, obtain his or her last five visit records in a certain hospital, and calculate the blood sugar management rate using the sliding average method", "Classify by year and ICD10 code, and count the number of people with the main diagnosis".

[0076] (6) Balanced sample distribution:

[0077] Ensure sample diversity;

[0078] Cover all important variables: Ensure that all important variables and calculation methods are included in the sample;

[0079] Avoid skew: Make sure different types of questions appear evenly.

[0080] (7) Verify sample quality:

[0081] The generated samples are reviewed by domain experts or people familiar with the business to ensure that they are realistic and accurately represented;

[0082] Perform preliminary tests on the sample to ensure that the generated SQL statements can be executed correctly.

[0083] (8) Annotated samples:

[0084] The annotation process includes:

[0085] Natural language description: natural language description of each sample;

[0086] SQL statement: the SQL statement corresponding to each sample;

[0087] Multiple annotators: Introduce multiple annotators to reduce subjective bias.

[0088] (9) Constructing a validation set

[0089] Extract the validation set, including:

[0090] Random sampling: randomly select a part of the labeled samples as the validation set;

[0091] Reflect diversity: Make sure your validation set reflects the diversity found in the real world.

[0092] (10) Continuously update the sample library

[0093] Updates and improvements, including:

[0094] Regular updates: As new data is added and business needs change, the sample library is regularly updated and expanded;

[0095] User feedback: Continuously optimize sample quality and model performance through user feedback and model performance.

[0096] Through the above steps, we can systematically construct and process samples of easily confounded variables, ensure that the generated samples cover all important variables and calculation methods, and improve the accuracy and generalization ability of the model.

[0097] 2) Prompt Project

[0098] In order to enable the system to generate SQL query statements more efficiently, especially when dealing with complex queries such as multi-table associations, the design of the prompt project is crucial.

[0099] The following are specific improvement measures:

[0100] 1. Multi-table association recognition: Add support for multi-table association in the prompt design, and help the model understand the connection between data by clearly indicating the tables that need to be associated and their relationships in the instruction part.

[0101] 2. Variable mapping prompt: Add variable mapping information in the prompt, that is, indicate which words in the natural language correspond to which fields or tables in the database.

[0102] 3. Condition filtering guidance: Design special prompts to guide the model to correctly generate query statements containing WHERE clauses, especially when there are multiple condition filters.

[0103] 4. Aggregate function prompt: Provides specific prompt templates for queries involving aggregate functions such as SUM, COUNT, AVG, etc.

[0104] 5. Sorting and grouping prompts: Design prompts that can guide the model to generate query statements containing ORDER BY and GROUP BY clauses.

[0105] 6. Time series analysis prompt: Provides a special prompt design for queries involving time series data (such as statistics by date).

[0106] 7. Error handling prompts: Design prompts that can help the model identify and correct common SQL syntax errors.

[0107] 8. Complex query examples: Add complex query examples to Prompt, including multi-table joins, nested queries, etc., to enhance the model's ability to handle complex queries.

[0108] 9. User intent parsing prompts: Design specific prompts to help the model better understand the user's query intent, especially when the requirement description is vague.

[0109] 10. Result display format tips: Provide tips on how to format query results to meet the display needs of different users.

[0110] 3) Model training

[0111] Combined with the improvements in the Prompt project, the following is the training process of the medical confusable variable text to SQL conversion system based on large models and fine-tuning technology:

[0112] 1. Data collection and preprocessing: Collect a large amount of medical-related natural language descriptions and corresponding SQL query statements, clean the data, and remove irrelevant information.

[0113] 2. Build a variable pool: Based on the characteristics of the medical field, build a detailed variable list, including diagnosis codes, diagnosis names, etc.

[0114] 3. Design Prompt templates: Based on the design principles of the above Prompt project, create a series of Prompt templates that can cover various query types.

[0115] 4. Generate training data: Use the designed prompt template to generate a large number of training samples to ensure the diversity and representativeness of the samples.

[0116] 5. Initial model selection: Select a pre-trained large language model as the base model, such as Qwen-2.5Coder.

[0117] 6. Implement Parameter Efficient Fine-tuning (PEFT): Add an adapter layer on top of the selected base model and fine-tune the adapter layer using the training data generated in the previous step, focusing on improving the model’s understanding and generation capabilities for specific tasks.

[0118] 7. Evaluate the effect of PEFT: Evaluate the effect of PEFT through a small-scale validation set and adjust the design or training parameters of the adapter layer based on the results.

[0119] 8. Fine-Tuning: In addition to PEFT, continue to fine-tune the entire model and train the entire model using the same training data to achieve better results.

[0120] 9. Continuous iterative optimization: Based on the performance of the validation set, continuously adjust the prompt template, training data, and training strategy until the model performance is stable and meets the requirements.

[0121] 10. User feedback loop: Deploy a preliminary version of the system to collect user feedback, especially on whether the generated SQL queries are accurate and efficient, and the user interface is friendly, so as to further optimize the model and system design.

[0122] 4) SQL query generation

[0123] SQL query generation is the core function of the system, which mainly includes the following steps:

[0124] (1) Input parsing: Receive text data of doctors’ diabetes data analysis requirements and parse query intent.

[0125] (2) Query generation: Use the trained large language model to generate the corresponding SQL query statement.

[0126] (3) Query verification: Perform syntax and logic verification on the generated SQL query to ensure the correctness and validity of the query.

[0127] 5) System Integration

[0128] System integration brings together individual components to form a complete solution, including

[0129] 1. User Interface: Provide a user-friendly interface through which doctors can enter text regarding diabetes data analysis requirements.

[0130] 2. Backend service: The backend service is responsible for receiving user input data, calling the large language model to generate SQL query statements, and returning the results to the user.

[0131] 3. Database connection: The system connects to the diabetes database, executes the generated SQL query and returns the query results.

Claims

1. A method for converting medical confusable variable text to SQL based on large models and fine-tuning technology, characterized in that: include: The steps to construct and process confusable variable text include: Defining a variable pool further includes: compiling a detailed variable list, including all variables that may appear in the query; listing variable values ​​or ranges; Designing the sample structure further includes: designing the basic structure of the sample; designing a natural language description to clearly explain the purpose of the query, the variables involved and any special computing requirements; designing the corresponding SQL query statement; Generate basic samples; On the basis of the basic sample, variable complexity, conceptual complexity and computational complexity are gradually added; Combined with actual application scenarios, simulated complex query requests; Balanced sample distribution; Verify sample quality; Label samples; Construct a validation set; Continuously update the sample library; The steps of the Prompt project include: Multi-table association recognition: Add support for multi-table association in prompt design. By clearly indicating the tables that need to be associated and their relationships in the instruction part, the model can understand the connection between data. Variable mapping prompt: Add variable mapping information to the prompt, that is, indicate which words in the natural language correspond to which fields or tables in the database; Condition filtering guidance: Design special prompts to guide the model to correctly generate query statements containing WHERE clauses, especially when there are multiple condition filters; Aggregate function prompts: Provides specific prompt templates for queries involving aggregate functions including SUM, COUNT, and AVG; Sorting and grouping prompts: Design prompts that can guide the model to generate query statements containing ORDER BY and GROUP BY clauses; Time series analysis prompt: Provides a special prompt design for queries involving time series data; Error handling prompts: Design prompts that can help the model identify and correct common SQL syntax errors; Complex query examples: Add complex query examples to the prompt, including multi-table joins, nested queries, etc., to enhance the model's ability to handle complex queries; User intent parsing prompts: Design specific prompts to help the model better understand the user's query intent, especially when the requirement description is vague; Result display format tips: Provide tips on how to format query results to meet the display needs of different users; The steps of model training include: Data collection and preprocessing: Collect a large amount of medical-related natural language descriptions and corresponding SQL query statements, clean the data, and remove irrelevant information; Construct variable pool: Construct a detailed variable list based on the characteristics of the medical field, including diagnosis codes and diagnosis names; Design prompt templates: Based on the design principles of the above prompt project steps, create a series of prompt templates that can cover various query types; Generate training data: Generate a large number of training samples using the designed prompt template to ensure the diversity and representativeness of the samples; Initial model selection: Select a pre-trained large language model as the base model; Implement efficient parameter fine-tuning: Add an adapter layer to the selected base model and fine-tune the adapter layer using the training data generated in the previous step to improve the model's understanding and generation capabilities for specific tasks; Evaluate the effect of PEFT: Evaluate the effect of PEFT through a small-scale validation set and adjust the design or training parameters of the adapter layer based on the results; Fine-tune: In addition to PEFT, continue to fine-tune the entire model and train the entire model using the same training data to achieve better results; Continuous iterative optimization: Based on the performance of the validation set, the prompt template, training data, and training strategy are continuously adjusted until the model performance is stable and meets the requirements; User feedback loop: deploy a preliminary version of the system to collect user feedback, especially on whether the generated SQL queries are accurate and efficient, and how friendly the user interface is, so as to further optimize the model and system design; The steps of SQL query generation include: Input parsing: Receive text data of doctors’ diabetes data analysis needs and parse query intent; Query generation: Use the trained large language model to generate the corresponding SQL query statement; Query verification: perform syntax and logic verification on the generated SQL query to ensure the correctness and validity of the query; The steps of system integration include: User interface: Provide a user-friendly interface through which doctors can input text of relevant disease data analysis requirements; Backend service: The backend service is responsible for receiving user input data, calling the large language model to generate SQL query statements, and returning the results to the user; Database connection: The system connects to the relevant disease database, executes the generated SQL query and returns the query results.

2. The method for converting medical confusable variable text to SQL based on large model and fine-tuning technology as claimed in claim 1, characterized in that: The generating of the basic samples further includes: randomly selecting a variable combination from a variable pool to construct a simple query request; and writing a corresponding SQL statement for each basic sample.

3. The method for converting medical confusable variable text to SQL based on large model and fine-tuning technology as claimed in claim 1, characterized in that: The balanced sample distribution further includes: ensuring sample diversity; covering all important variables and ensuring that all important variables and calculation methods are included in the sample; avoiding skewness and ensuring that the frequency of occurrence of different types of problems is balanced.

4. The method for converting medical confusable variable text to SQL based on large model and fine-tuning technology as claimed in claim 1, characterized in that: The verification of sample quality further includes: reviewing the generated samples by domain experts or personnel familiar with the business to ensure that they are consistent with the actual situation and accurately expressed; and performing preliminary testing on the samples to ensure that the generated SQL statements can be executed correctly.

5. The method for converting medical confusable variable text to SQL based on large model and fine-tuning technology as claimed in claim 1, characterized in that: The labeling process of implementing the labeling sample includes: labeling the natural language description of each sample; labeling the SQL statement corresponding to each sample; multiple labelers: introducing multiple labelers to reduce subjective bias.

6. The method for converting medical confusable variable text to SQL based on large model and fine-tuning technology as claimed in claim 1, characterized in that: The validation set is constructed by extracting the validation set, including: random extraction, randomly extracting a part of the labeled samples as the validation set; reflecting diversity, ensuring that the validation set can reflect the diversity in the real world.

7. The method for converting medical confusable variable text to SQL based on large model and fine-tuning technology as claimed in claim 1, characterized in that: The sample library is continuously updated through updating and optimization, including: Regular updates: As new data is added and business needs change, the sample library is regularly updated and expanded; User feedback: Continuously optimize sample quality and model performance through user feedback and model performance.

Images