Defect database question and answer method using TEXT2SQL large language model

Abstract

The invention provides a defect database question and answer method using a TEXT2SQL large language model, and the method comprises the steps: analyzing a natural language question through the TEXT2SQL large language model, and generating a table and a column related to the natural language question; based on the related tables and columns, designing a plurality of prompt modes to generate a plurality of candidate SQL queries; executing and comparing results and speeds of all the candidate SQL queries, and screening out a target SQL query from the candidate SQL queries; and performing question and answer on a defect database by utilizing the target SQL query. By adopting the defect database question and answer method of the TEXT2SQL large language model and combining the technical steps of table linking, column linking, multi-SQL generation, filtering and selection and the like, the query accuracy and diversity are effectively improved. Related information can be accurately extracted from a complex database structure, efficient and explainable database questions and answers are achieved, and the query efficiency and accuracy of defect data are remarkably improved.

Description

Technical Field

[0001] The present invention relates to the field of artificial intelligence technology, and particularly to a method for defect database question answering using a TEXT2SQL large language model. Background Art

[0002] With the rapid development of information technology, databases, as the core of data storage, are increasingly widely used in fields such as enterprise operations, scientific research, and social management. However, in the actual operation process, due to the complexity and diversity of data, how to efficiently and accurately obtain the required information from the database has become a challenge. Although the traditional SQL query language is powerful, for non-professionals, the learning and usage costs are relatively high, which limits the effective utilization of the database. Therefore, combining natural language processing technology with database query to achieve the conversion from natural language to SQL (Natural Language to SQL, abbreviated as TEXT2SQL) has become a current research hotspot.

[0003] However, the existing technologies still have the following deficiencies when dealing with the specific scenario of defect data question answering:

[0004] Domain Adaptability: Most existing models perform well in general domains, but in scenarios such as defect data question answering with strong professionalism and dense terms, the accuracy and robustness need to be improved.

[0005] Context Understanding: In defect data question answering, queries often involve multi-round conversations and complex logical reasoning, and the existing technologies are still insufficient in understanding context and long-term dependencies.

[0006] Query Efficiency: Facing large-scale defect databases, how to quickly locate and extract relevant information is a major challenge for existing technologies.

[0007] Personalized Customization: The defect database structures and query requirements of different industries and organizations vary greatly, and the existing technologies lack effective means for personalized customization and optimization.

[0008] In response to the above problems, the present invention proposes a method for defect database question answering based on a TEXT2SQL large language model, aiming to achieve efficient and accurate query of defect data through the integration of deep learning technology and professional domain knowledge, while having a good user interaction experience and high flexibility.

[0009] In response to the above problems, the present invention proposes a method for defect database question answering using a TEXT2SQL large language model, aiming to achieve efficient and accurate query of defect data through the integration of deep learning technology and professional domain knowledge, while having a good user interaction experience and high flexibility. Summary of the Invention

[0010] In the first aspect of the present disclosure, a method for defect database question answering using a TEXT2SQL large language model is provided. The method includes:

[0011] Analyze a natural language question through a TEXT2SQL large language model to generate tables and columns related to the natural language question;

[0012] Based on the related tables and columns, design multiple prompting methods to generate multiple candidate SQL queries;

[0013] Execute and compare the results and speeds of all candidate SQL queries, and screen out the target SQL query from them;

[0014] Use the target SQL query to perform question answering on the defect database.

[0015] In combination with the first aspect, the analyzing the natural language question includes:

[0016] Parse the natural language question to identify target words and target phrases;

[0017] Map the identified target words and target phrases to tables and columns in the database;

[0018] Generate a JSON format response for the tables and columns mapped to the database, including the reasons for selecting each table and column.

[0019] In combination with the first aspect, the designing multiple prompting methods to generate multiple candidate SQL queries includes:

[0020] Generate different types of SQL prompting templates according to the natural language question;

[0021] Embed an example of the database structure similar to the natural language question in the prompting template;

[0022] Generate multiple candidate SQL queries according to the example of the database structure and sort them.

[0023] In combination with the first aspect, the steps of executing and comparing candidate SQL queries include:

[0024] Execute each candidate SQL query and record the execution result and execution time;

[0025] Exclude candidate SQL queries with failed queries or execution times exceeding the preset time;

[0026] Compare the matching degree of the execution result with the expected answer and screen out the target SQL query with a matching value greater than the preset matching value.

[0027] In combination with the first aspect, the screening of target SQL queries greater than a preset matching value includes:

[0028] Calculate the confidence level of the execution result using the following formula:

[0029]

[0030] where conf(s) represents the confidence level of the execution result, N represents the number of SQL queries, s i represents the i-th SQL statement, s j represents the j-th SQL statement, E(s i ) represents the execution result of executing the i-th SQL statement, E(s j ) represents the execution result of executing the j-th SQL statement;

[0031] Screen out the target SQL queries whose confidence level of the execution result is greater than the preset confidence level.

[0032] In the second aspect of the present disclosure, an electronic device is provided, including:

[0033] One or more processors;

[0034] A storage unit for storing one or more programs, which, when executed by the one or more processors, enable the one or more processors to implement any one of the above-mentioned defect database Q&A methods based on the TEXT2SQL large language model.

[0035] In the third aspect of the present disclosure, a computer-readable storage medium is provided, on which a computer program is stored, characterized in that when the computer program is executed by a processor, it can implement any one of the above-mentioned defect database Q&A methods based on the TEXT2SQL large language model.

[0036] Beneficial effects: A defect database Q&A method based on the TEXT2SQL large language model of the present disclosure effectively improves the accuracy and diversity of queries by adopting the defect database Q&A method of the TEXT2SQL large language model and combining technical steps such as table linking, column linking, multi-SQL generation, filtering, and selection. Specifically, through in-depth analysis of natural language questions, the model can accurately identify the involved tables and columns, generate multiple candidate SQL queries, and then screen out the optimal query by comparing the query results and execution speeds. Finally, this method can accurately extract relevant information in a complex database structure, realizing efficient and interpretable database Q&A, and significantly improving the query efficiency and accuracy of defect data. Description of the Drawings

[0037] Figure 1Schematic flowchart of a defect database question - answering method based on the TEXT2SQL large - language model according to an embodiment of the present disclosure;

[0038] Figure 2 Schematic structural diagram of an electronic device according to an embodiment of the present disclosure. Detailed implementation manners

[0039] Here, exemplary embodiments will be described in detail, and their examples are shown in the drawings. When the following description refers to the drawings, unless otherwise indicated, the same numbers in different drawings represent the same or similar elements. The implementation manners described in the following exemplary embodiments do not represent all implementation manners consistent with the embodiments of the present disclosure.

[0040] The terms used in the embodiments of the present disclosure are only for the purpose of describing specific embodiments, and are not intended to limit the embodiments of the present disclosure. The singular forms "a", "the", and "said" used in the embodiments of the present disclosure and the appended claims are also intended to include the plural forms unless the context clearly indicates otherwise. It should also be understood that the term "and / or" as used herein refers to and includes any or all possible combinations of one or more of the associated listed items.

[0041] It should be understood that although the terms first, second, third, etc. may be used in the embodiments of the present disclosure to describe various information, such information should not be limited to these terms. These terms are only used to distinguish the same type of information from each other. For example, without departing from the scope of the embodiments of the present disclosure, the first information may also be referred to as the second information, and similarly, the second information may also be referred to as the first information. Depending on the context, the word "if" as used herein may be interpreted as "when" or "while" or "in response to determining".

[0042] As Figure 1 shown, it is a schematic flowchart of a defect database question - answering method based on the TEXT2SQL large - language model according to an embodiment of the present disclosure. It includes the following steps:

[0043] S101: Analyze the natural - language question through the TEXT2SQL large - language model to generate tables and columns related to the natural - language question;

[0044] S102: Based on the related tables and columns, design multiple prompting methods to generate multiple candidate SQL queries;

[0045] S103: Execute and compare the results and speeds of all candidate SQL queries, and screen out the target SQL query from them;

[0046] S104: Use the target SQL query to conduct question - answering on the defect database.

[0047] Specifically:

[0048] S101: Analyze the natural language question through the TEXT2SQL large language model to generate tables and columns related to the natural language question.

[0049] The analysis of the natural language question includes:

[0050] Parse the natural language question to identify target words and target phrases;

[0051] Map the identified target words and target phrases to tables and columns in the database;

[0052] Generate a JSON - formatted response with the tables and columns mapped to the database, including the reasons for selecting each table and column.

[0053] Specifically, input processing: Receive a natural language question, such as "Query the total amount of all orders this year".

[0054] Text parsing: Use the TEXT2SQL large language model for natural language parsing, understand the meaning of the question and identify key elements, such as tables, columns, time range, etc. For example, "orders" in the question points to a database table related to orders, and "total amount" in the question points to the amount field.

[0055] Table and column linking:

[0056] Through the inference ability of the model, identify which tables are related to the question, such as the orders table (orders) and the customers table (customers).

[0057] Determine the required columns, such as the amount field (orders.amount) and the date field (orders.order_date) in the orders table.

[0058] Output: Return the required tables and columns, and provide the reasons for the selection of each table.

[0059] Use JSON format for output to help subsequent steps understand the tables and columns selected by the model.

[0060] S102: Based on the related tables and columns, design multiple prompting methods to generate multiple candidate SQL queries.

[0061] The design of multiple prompting methods to generate multiple candidate SQL queries includes:

[0062] Generate different types of SQL prompt templates according to the natural language question;

[0063] Embed examples of database structures similar to the natural language question in the prompt templates;

[0064] Generate multiple candidate SQL queries based on the described database structure example and sort them.

[0065] Specifically, generate prompts: Based on the identified table and column information, design multiple different prompt methods (e.g., based on question similarity, based on masked question similarity) to generate multiple candidate SQL queries for the model.

[0066] Include the database table structure, column names, and sample data (such as sample data in CSV format) in the prompt to help the model better understand the question.

[0067] For example, for the question "Query the total amount of all orders this year", the following prompts may be designed:

[0068] Multiple query generation: The model generates multiple candidate SQL queries according to different prompt methods. Each query may contain different SQL structures or use different query methods.

[0069] S103: Execute and compare the results and speeds of all candidate SQL queries, and filter out the target SQL query from them.

[0070] The steps of executing and comparing candidate SQL queries include:

[0071] Execute each of the candidate SQL queries and record the execution results and execution times;

[0072] Exclude candidate SQL queries that fail to execute or whose execution time exceeds the preset time;

[0073] Compare the matching degree of the execution results with the expected answers and filter out the target SQL queries with a matching value greater than the preset value.

[0074] Specifically, execute candidate queries: Submit all the generated SQL queries to the database for execution and record the execution results and execution times of each query.

[0075] For each candidate query, check whether it can be successfully executed and ensure that its syntax is correct and it can logically return the expected results correctly.

[0076] Filter queries: Group the queries with the same execution results and retain the query with the fastest execution speed in each group.

[0077] If an error or timeout occurs during query execution, exclude it.

[0078] For example, if the execution results of two queries are the same, but one query has a longer execution time, then select the query with the shorter execution time.

[0079] Confidence score: Sort the remaining queries according to the accuracy and execution speed of their execution results, and calculate the confidence scores.

[0080] If the confidence of a certain query is lower than the predetermined threshold, it is eliminated.

[0081] Calculate the confidence of the execution result using the following formula:

[0082]

[0083] where conf(s) represents the confidence of the execution result, N represents the number of SQL queries, s i represents the i-th SQL statement, s j represents the j-th SQL statement, E(s i ) represents the execution result of executing the i-th SQL statement, E(s j ) represents the execution result of executing the j-th SQL statement.

[0084] Output the target query: Finally, determine the most suitable target SQL query through screening and sorting.

[0085] S104: Use the target SQL query to perform question and answer on the defect database

[0086] Execute the target SQL query: Use the selected target SQL query to perform an actual query on the defect database.

[0087] The query result will be returned to the user as the question and answer result of the defect database.

[0088] Output the result: Output the query result to the user in an easy-to-understand form to solve their natural language problem.

[0089] Optionally, according to the query result, subsequent feedback and improvement suggestions can be provided. For example, if the user has questions about certain data or results, the query can be further refined, and SQL can be regenerated and executed.

[0090] Through this method, the database question and answer system can be made more flexible and accurate. Especially in complex query scenarios, it can effectively handle diverse query requirements.

[0091] Exemplarily, assume there is a power generation equipment defect elimination database, which contains the following tables:

[0092] Equipment table (equipment): Equipment ID, equipment name, equipment type, installation date, etc.

[0093] Defect table (defect): Defect ID, affiliated equipment ID, defect description, discovery date, severity, etc.

[0094] Maintenance table: Maintenance ID, defect ID, maintenance personnel, maintenance date, maintenance result, etc.

[0095] Part table: Part ID, part name, specification model, supplier, etc.

[0096] Given a natural language query: "Which parts were replaced due to serious defects in 2022?"

[0097] First, perform table linking. The large language model gives the following JSON format response:

[0098] {"reason": "The query involves defect severity and part replacement, so the defect table and maintenance table are required. Part information is in the part table and is also needed.",

[0099] "tables": ["defect", "maintenance", "part"]

[0100] }

[0101] Next, perform column linking. The large language model identifies the specific columns required for the query:

[0102] defect.defect_id, defect.severity, maintenance.defect_id, maintenance.maintenance_date, part.part_id, part.part_name

[0103] Then, perform multi - SQL generation:

[0104] Based on the problem similarity strategy, select the following two examples from the training set as few - shots:

[0105] Example 1: "Which key parts were replaced in 2021?"

[0106] SQL: SELECT p.part_name

[0107] FROM maintenance m

[0108] JOIN part p ON m.part_id = p.part_id

[0109] WHERE m.maintenance_date BETWEEN '2021 - 01 - 01' AND '2021 - 12 - 31' AND p.critical = 1

[0110] Example 2: "Which was the maintenance with the largest amount involved in the repairs caused by high-risk defects?"

[0111] SQL: SELECT m.maintenance_id, m.cost

[0112] FROM maintenance m

[0113] JOIN defect d ON m.defect_id = d.defect_id

[0114] WHERE d.severity = 'high risk'

[0115] ORDER BY m.cost DESC

[0116] LIMIT 1

[0117] Then construct a prompt asking the LLM to generate the SQL and explain its logic:

[0118] Prompt: Generate an SQL query for the given question and explain the query logic based on the following database schema, sample table content, and similar question examples.

[0119] Database schema: {Information about tables and columns}

[0120] Sample table content: {Fragment of table data in CSV format}

[0121] Similar question examples:

[0122] {Example 1 question and SQL}

[0123] {Example 2 question and SQL}

[0124] Question: Which parts were replaced due to severe defects in 2022?

[0125] SQL and explanation generated by the large language model:

[0126] SQL: SELECT DISTINCT p.part_name

[0127] FROM defect d

[0128] JOIN maintenance m ON d.defect_id = m.defect_id

[0129] JOIN part p ON m.part_id = p.part_id

[0130] WHERE d.severity IN ('Severe', 'High Risk')

[0131] AND m.maintenance_date BETWEEN '2022-01-01' AND '2022-12-31'

[0132] Among them, first filter out the defects with severity of "Severe" or "High Risk" through the defect table, then find the maintenance records for these defects in 2022 through the association with the maintenance table, and finally obtain the names of the corresponding replaced parts through the part table. The DISTINCT keyword ensures that there are no duplicate part names.

[0133] Then perform filtering and selection:

[0134] Assume that in addition to the SQL in Example 2 in the previous step, the large language model also generated the following two candidate queries:

[0135] Candidate SQL-1:

[0136] SELECT p.part_name

[0137] FROM part p

[0138] WHERE p.part_id IN (

[0139] SELECT m.part_id

[0140] FROM maintenance m

[0141] WHERE m.defect_id IN (

[0142] SELECT d.defect_id

[0143] FROM defect d

[0144] WHERE d.severity IN ('Severe', 'High Risk') )

[0146] AND m.maintenance_date BETWEEN '2022-01-01' AND '2022-12-31' )

[0148] Candidate SQL-2:

[0149] SELECT p.part_name

[0150] FROM defect d, maintenance m, part p

[0151] WHERE d.severity IN ('Severe', 'High Risk')

[0152] AND d.defect_id = m.defect_id

[0153] AND m.part_id = p.part_id

[0154] AND m.maintenance_date BETWEEN '2022-01-01' AND '2022-12-31'

[0155] First, execute these three candidate queries. Assume that they can all return results normally and the execution time is within an acceptable range. However, upon comparison, it is found that although the writing methods of candidate SQL-2 and SQL are different, their returned results are the same, while the execution speed of candidate SQL-1 is slightly slower, so it is eliminated in the first round of screening.

[0156] Next, utilize the multi-selection ability of the large language model to give a new prompt:

[0157] Prompt: For the question "Which parts were replaced due to severe defects in 2022?", the following two SQL queries can both return the correct results. Please select the query you think is the best and explain the reason.

[0158] A. {SQL of Embodiment 2}

[0159] B. {Candidate SQL-2}

[0160] Response from the large language model:

[0161] Select A.

[0162] Although the results returned by the two queries are the same, query A uses the standard JOIN syntax and specifies the join conditions between tables through the ON clause, with clearer semantics and stronger readability. While query B uses the old-fashioned implicit join syntax, which is prone to confusion. In addition, the DISTINCT keyword is used in query A to remove duplicates, while query B does not. If there are duplicate items in the result set, query B cannot filter them out. In summary, query A is a better choice.

[0163] A defect database question-answering method based on the TEXT2SQL large language model of the present disclosure effectively improves the accuracy and diversity of queries by adopting the defect database question-answering method of the TEXT2SQL large language model and combining technical steps such as table linking, column linking, multi-SQL generation, filtering, and selection. Specifically, through in-depth analysis of natural language questions, the model can accurately identify the involved tables and columns, generate multiple candidate SQL queries, and then screen out the optimal query by comparing the query results and execution speeds. Finally, this method can accurately extract relevant information in a complex database structure, realizing efficient and interpretable database question-answering, and significantly improving the query efficiency and accuracy of defect data.

[0164] The electronic device 200 can be a desktop computer, a notebook, a palm computer, a cloud server, or other electronic devices. The electronic device 200 may include, but is not limited to, a processor 201 and a memory 202. Those skilled in the art can understand that Figure 2 merely examples of the electronic device 200 do not constitute a limitation on the electronic device 200, and may include more or fewer components than shown in the figure, or combine certain components, or different components. For example, the electronic device may also include input / output devices, network access devices, buses, etc.

[0165] The processor 201 may be a central processing unit (CPU), or other general-purpose processors, digital signal processors (DSPs), application specific integrated circuits (ASICs), field programmable gate arrays (FPGAs), or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. The general-purpose processor may be a microprocessor or the processor may also be any conventional processor, etc.

[0166] The memory 202 can be an internal storage unit of the electronic device 200. For example, it can be the hard disk or memory of the electronic device 200. The memory 202 can also be an external storage device of the electronic device 200. For example, it can be a plug-in hard disk equipped on the electronic device 200, a Smart Media Card (SMC), a Secure Digital (SD) card, a Flash Card, etc. Further, the memory 202 can also include both the internal storage unit of the electronic device 200 and the external storage device. The memory 202 is used to store the computer program 203 and other programs and data required by the electronic device. The memory 202 can also be used to temporarily store the data that has been output or will be output.

[0167] In the embodiments provided in the present disclosure, it should be understood that the disclosed apparatus / electronic device and method can be implemented in other ways. For example, the above-described apparatus / electronic device embodiments are merely illustrative. For example, the division of modules or units is only a logical function division. In actual implementation, there may be other division methods. Multiple units or components can be combined or integrated into another system, or some features can be ignored or not executed. Another point is that the shown or discussed coupling or direct coupling or communication connection to each other can be through some interfaces. The indirect coupling or communication connection of the apparatus or unit can be in an electrical, mechanical or other form.

[0168] In addition, in each embodiment of the present disclosure, each functional unit can be integrated in a processing unit, or each unit can exist physically alone, or two or more units can be integrated in one unit. The above-mentioned integrated unit can be implemented in the form of hardware or in the form of a software functional unit.

[0169] The above embodiments are only used to illustrate the technical solutions of the present disclosure, and are not intended to limit them; although the present disclosure has been described in detail with reference to the foregoing embodiments, those of ordinary skill in the art should understand that they can still modify the technical solutions recorded in the foregoing embodiments, or perform equivalent replacements for some of the technical features; and these modifications or replacements do not make the essence of the corresponding technical solutions deviate from the spirit and scope of the technical solutions of the embodiments of the present disclosure, and should all be included in the protection scope of the present disclosure.

Claims

1. A defect database question-answering method using a TEXT2SQL large language model, characterized in that: The method comprises: Analyze natural language questions through the TEXT2SQL large language model and generate tables and columns related to the natural language questions; Based on the related tables and columns, design multiple prompting methods to generate multiple candidate SQL queries; Execute and compare the results and speeds of all candidate SQL queries to select the target SQL query; The target SQL query is used to perform question and answer on the defect database.

2. The method according to claim 1, characterized in that The natural language analysis problem includes: Parse natural language questions to identify target words and target phrases; Mapping the identified target words and target phrases to tables and columns in a database; The mapping is performed to tables and columns in the database and a JSON response is generated, including the reasons for selecting each table and column.

3. The method according to claim 1, characterized in that The design of multiple prompting methods to generate multiple candidate SQL queries includes: Generate different types of SQL prompt templates based on natural language questions; embedding a database structure example similar to the natural language question in the prompt template; A plurality of candidate SQL queries are generated according to the database structure example and ranked.

4. The method according to claim 1, characterized in that: The steps of executing and comparing candidate SQL queries include: Execute each of the candidate SQL queries and record the execution result and execution time; Eliminate candidate SQL queries whose query fails or whose execution time exceeds a preset time; The matching degree of the execution result and the expected answer is compared and the target SQL query with a matching value greater than a preset value is screened out.

5. The method according to claim 4, characterized in that The target SQL query that is greater than the preset matching value is screened out includes: The confidence level of the execution result is calculated using the following formula: Wherein, conf(s) represents the confidence of the execution result, N represents the number of SQL queries, and s i Indicates the i-th SQL statement, s j represents the jth SQL statement, E(s i ) represents the execution result of the i-th SQL statement, E(s j ) represents the execution result of the jth SQL statement; Target SQL queries whose execution result confidence is greater than a preset confidence are screened out.

6. An electronic device, characterized in that: include: one or more processors; A storage unit, used to store one or more programs, which, when executed by the one or more processors, enable the one or more processors to implement the defect database question and answer method based on the TEXT2SQL large language model according to any one of claims 1 to 5.

7. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, it can implement the defect database question and answer method based on the TEXT2SQL large language model according to any one of claims 1 to 5.

Images