A method, device, equipment and medium for answering questions based on SQL query

Abstract

A method, apparatus, device, and medium for answering questions based on SQL queries, including screening database tables that meet business requirements from multiple database tables based on entities and attributes corresponding to business questions identified through a knowledge graph, using retrieval enhancement generation technology and a large language model to retrieve database tables corresponding to business questions from multiple database tables, and determining a target database table based on the database tables that meet business requirements and the database tables corresponding to the business questions. A target SQL example pair is determined based on the first and second similarity values ​​of each SQL example pair, and the target SQL example pair, structural information of the target database table, and entities and attributes of the business questions are used as retrieval information. Retrieval enhancement generation technology and a large language model are used to generate a SQL target query statement corresponding to the retrieval information, and the SQL target query statement is executed to obtain a target query result and a target question answer. This improves the semantic accuracy of SQL query statements and the accuracy of question answers.

Description

Technical Field

[0001] The present invention relates to the field of artificial intelligence technology, and in particular to a method, device, equipment and medium for answering questions based on SQL queries. Background Art

[0002] Traditional environmental monitoring systems often face challenges with large data volumes, diverse data types, and frequent updates, making them difficult to manage and analyze effectively. When users ask questions about data in environmental monitoring systems, these questions may be ambiguous or diverse, making it challenging to understand and convert them into specific query requirements. When users need to ask questions based on this environmental business knowledge, database queries are typically implemented using a technology that converts natural language queries into SQL statements. This technology parses the user's natural language query to generate the corresponding SQL query statement, which is then executed to query the database. However, this method typically relies on template matching or rule-driven processing, which has limitations when processing complex queries and makes it difficult to achieve semantically accurate queries. Summary of the Invention

[0003] The main technical problem solved by the present invention is that the semantic accuracy of SQL query statements is low, which leads to low accuracy in question responses.

[0004] According to the first aspect, an embodiment provides a question answering method based on SQL query, comprising:

[0005] Obtaining a business question raised by a user, and using a pre-built knowledge graph to identify entities corresponding to the business question and the attributes of the entities; wherein the knowledge graph is constructed by multiple entities in the business context and multiple entity relationships between the entities, each entity having multiple different attributes;

[0006] Screening out a database table that meets the business requirements from multiple database tables based on the entity and its attributes, and retrieving the database table corresponding to the business problem from the multiple database tables using retrieval enhancement generation technology and a large language model;

[0007] Determining a target database table based on the database table that meets the business requirements and the database table corresponding to the business problem; wherein at least one target database table is included;

[0008] Acquire multiple SQL example pairs, wherein the SQL example pairs include reference questions and SQL statements corresponding to the reference questions;

[0009] performing similarity calculations based on the reference question and the business question in each SQL example pair to obtain a first similarity value for each SQL example pair, performing vectorization processing on the reference question and the business question in each SQL example pair, performing similarity calculations based on the vectorized reference question and the business question to obtain a second similarity value for each SQL example pair, and determining a target SQL example pair from the multiple SQL example pairs based on the first similarity value and the second similarity value;

[0010] The target SQL example pair, the structural information of the target database table, the entity corresponding to the business problem, and the attributes of the entity are used as retrieval information, and the retrieval enhancement generation technology and a large language model are used to generate the SQL target query statement corresponding to the retrieval information;

[0011] The SQL target query statement is executed in a preset database to obtain a target query result, and the large language model is used to generate a target question response corresponding to the target query result.

[0012] In some embodiments, determining a target SQL example pair from the plurality of SQL example pairs based on the first similarity value and the second similarity value includes:

[0013] Taking the SQL example pair whose first similarity value meets the preset similarity threshold as a first reference SQL example pair, and taking the SQL example pair whose second similarity value meets the preset similarity threshold as a second reference SQL example pair;

[0014] Combining the first reference SQL example pairs with the second reference SQL example pairs in pairs to obtain multiple SQL example pair combinations, and for each SQL example pair combination, performing a weighted calculation on the first similarity value of the first reference SQL example pair and the second similarity value of the second reference SQL example pair according to a preset weight to obtain a final weighted similarity value;

[0015] The first reference SQL example pair or the second reference SQL example pair in the SQL example pair combination whose final weighted similarity value meets the reference requirement is used as a target reference SQL example pair.

[0016] In some embodiments, performing similarity calculations based on the reference question and the business question in each SQL example pair to obtain a first similarity value for each SQL example pair includes:

[0017] Comparing each word in the reference question in each SQL example pair with each word in the business question to obtain a comparison result; the comparison result includes the number of identical words;

[0018] A ratio calculation is performed based on the comparison result and the question length of the reference question to obtain a first similarity value for each of the SQL example pairs.

[0019] In some embodiments, executing the SQL target query statement in a preset database to obtain a target query result includes:

[0020] Execute the SQL target query statement in the preset database to start the data query process;

[0021] When a query error occurs in the data query process, the SQL target query statement is corrected according to the acquired error information, the business problem, and the large language model to obtain a corrected SQL target query statement;

[0022] Re-query the data using the modified SQL target query statement. If the query is successful, the queried data is used as the target query result.

[0023] Otherwise, the corrected SQL target query statement is revised again according to the error message received when re-querying the data to obtain the revised SQL target query statement, and the query result obtained by executing the revised SQL target query statement is used as the target query result.

[0024] In some embodiments, before using the large language model to generate a target question answer corresponding to the target query result, the question answering method further includes:

[0025] The target query result is subjected to different display processing according to the data display request sent by the user, wherein the display processing includes retaining the target query result to a preset number of decimal places and adding a preset serial number to the target query result.

[0026] In some embodiments, before executing the SQL target query statement in the preset database to obtain the target query result, the question answering method further includes:

[0027] Determine whether the SQL target query statement contains statement content for data addition, data update, and data deletion;

[0028] If included, it is determined that the SQL target query statement does not comply with the regulations and a warning message is issued to the user;

[0029] If not included, it is determined that the SQL target query statement complies with the regulations, and a result query is performed according to the SQL target query statement.

[0030] In some embodiments, before obtaining the business question raised by the user, the question answering method further includes:

[0031] A query question raised by a user is obtained, and different word segments in the query question are replaced based on a preset business vocabulary to generate a business question raised by the user.

[0032] According to the second aspect, an embodiment provides a question answering device based on SQL query, comprising:

[0033] An entity recognition module is used to obtain a business question raised by a user and identify the entities corresponding to the business question and the attributes of the entities using a pre-built knowledge graph; wherein the knowledge graph is constructed by multiple entities in the business context and the entity relationships between the entities, each entity having multiple different attributes;

[0034] A database table screening module, configured to screen out a database table that meets the business requirements from multiple database tables based on the entity and its attributes, and to retrieve the database table corresponding to the business problem from the multiple database tables using retrieval enhancement generation technology and a large language model;

[0035] A target database table determination module is configured to determine a target database table based on the database table that meets the business requirements and the database table corresponding to the business problem; wherein the target database table includes at least one target database table;

[0036] An example pair acquisition module is used to acquire multiple SQL example pairs, wherein the SQL example pairs include reference questions and SQL statements corresponding to the reference questions;

[0037] a similarity calculation module, configured to perform similarity calculation based on the reference question and the business question in each SQL example pair, respectively, to obtain a first similarity value for each SQL example pair; perform vectorization processing on the reference question and the business question in each SQL example pair, perform similarity calculation based on the vectorized reference question and the business question, to obtain a second similarity value for each SQL example pair; and determine a target SQL example pair from the multiple SQL example pairs based on the first similarity value and the second similarity value;

[0038] A query statement generation module is used to use the target SQL example pair, the structural information of the target database table, the entity corresponding to the business question, and the attributes of the entity as retrieval information, and use retrieval enhancement generation technology and a large language model to generate an SQL target query statement corresponding to the retrieval information;

[0039] The question answering module is used to execute the SQL target query statement in the preset database to obtain the target query result, and use the large language model to generate the target question answer corresponding to the target query result.

[0040] According to a third aspect, an embodiment provides a question answering device based on SQL query, comprising:

[0041] Memory, used to store programs;

[0042] The processor is configured to implement the question answering method by executing the program stored in the memory.

[0043] According to a fourth aspect, an embodiment provides a computer-readable storage medium having a program stored thereon, wherein the program can be executed by a processor to implement a question answering method.

[0044] According to the above-mentioned embodiment, a method, apparatus, device and computer-readable storage medium for answering questions based on SQL queries are provided. Since the entities and attributes of the entities corresponding to the business questions are identified based on the knowledge graph to determine the key information of the business questions and the requirements of the query, the target database table is determined by combining the retrieval enhancement generation technology and the large language model to improve the accuracy of the subsequent question answers. The first similarity value between each SQL example pair and the business question and the second similarity value between each SQL example pair and the business question are calculated respectively, and the similarity between the SQL example pair and the business question is calculated from different dimensions. The target SQL example pair is determined from multiple SQL example pairs based on the first similarity value and the second similarity value to achieve accurate search of the target SQL example pair. The SQL target query statement corresponding to the retrieval information composed of the target SQL example pair is generated using the retrieval enhancement generation technology and the large language model, thereby improving the efficiency of subsequent queries. Therefore, executing the SQL target query statement in the preset database can obtain accurate target query results, and using the large language model to generate the target question answer corresponding to the target query result, an effective and accurate question-answering process is achieved. BRIEF DESCRIPTION OF THE DRAWINGS

[0045] Figure 1 This is a flowchart of a question answering method based on SQL query according to an embodiment of the present application;

[0046] Figure 2 A structural diagram of a knowledge graph according to an embodiment;

[0047] Figure 3 A flowchart of an embodiment for performing similarity calculation based on a reference question and a business question in each SQL example pair to obtain a first similarity value for each SQL example pair;

[0048] Figure 4 A flowchart of determining a target SQL example pair from a plurality of SQL example pairs based on a first similarity value and a second similarity value in one embodiment;

[0049] Figure 5 A flowchart of executing an SQL target query statement in a preset database to obtain a target query result in an embodiment;

[0050] Figure 6 A flowchart of a method for executing an SQL target query statement in a preset database to obtain a target query result in an embodiment;

[0051] Figure 7 A structural diagram of a question answering device based on SQL query according to an embodiment. DETAILED DESCRIPTION

[0052] The present invention will be further described in detail below by means of specific embodiments in conjunction with the accompanying drawings. Similar elements in different embodiments are numbered with associated similar elements. In the following embodiments, many detailed descriptions are provided to enable the present application to be better understood. However, those skilled in the art will readily appreciate that some of the features may be omitted in different circumstances, or may be replaced by other elements, materials, or methods. In some cases, some operations related to the present application are not shown or described in the specification. This is to avoid the core portion of the present application being overwhelmed by excessive descriptions, and for those skilled in the art, it is not necessary to describe these related operations in detail. They will fully understand the related operations based on the description in the specification and the general technical knowledge in the art.

[0053] In addition, the features, operations, or characteristics described in the specification may be combined in any appropriate manner to form various embodiments. Furthermore, the steps or actions in the method description may be reordered or adjusted in a manner readily apparent to those skilled in the art. Therefore, the various sequences in the specification and drawings are provided solely for the purpose of clearly describing a particular embodiment and are not intended to be mandatory, unless otherwise specified.

[0054] The serial numbers assigned to components herein, such as "first," "second," etc., are used solely to distinguish the objects being described and do not convey any sequential or technical meaning. References to "connection" and "coupling" herein, unless otherwise specified, include both direct and indirect connections (couplings).

[0055] Please refer to Figure 1 In an embodiment of the present invention, a question answering method based on SQL query is provided, including steps S10 to S70, which are described in detail below.

[0056] Step S10: Obtain the business question raised by the user, and use the pre-built knowledge graph to identify the entities and entity attributes corresponding to the business question.

[0057] In some embodiments, users ask business questions in natural language, which may involve data query or statistical analysis. The knowledge graph is constructed by multiple entities and entity relationships between entities in the business context, where each entity has multiple different attributes.

[0058] In some embodiments, in the business context of environmental monitoring, multiple entities include rivers, cross-section monitoring points, municipal surface water quality, municipal environmental quality, municipal air quality, municipal surface water quality, atmospheric monitoring points, atmospheric monitoring records, surface water monitoring records, and administrative districts. Administrative districts have different attributes such as name, geographic location, administrative level, control level, number of monitoring points, and assessment indicators. Rivers have different attributes such as river name, basin area, maximum land width, length of river within the management area, river length, whether it is a river flowing into the sea, total land area within the management area, and basin area. Atmospheric monitoring points have different attributes such as name, code, administrative district name, longitude, latitude, regulatory level, and implementation standards. Cross-section monitoring points have different attributes such as name, code, river to which they belong, longitude, latitude, regulatory level, and implementation standards. Surface water detection records have different attributes such as record type, time, ozone, pH value, transparency, dissolved oxygen, chemical oxygen demand, water quality category, and exceedance.

[0059] Please refer to Figure 2 In some embodiments, the relationships between entities include: belonging, including, reflecting, influencing, recording, and being recorded. For example, the entities "River" and "Section Monitoring Point" have entity relationships of including and belonging, and the entities "Atmosphere Monitoring Record" and "City Air Quality" have entity relationships of reflecting and influencing. Therefore, a knowledge graph can be constructed by constructing multiple entities and the entity relationships between them.

[0060] In some embodiments, before obtaining the business question raised by the user, the method further includes: obtaining the query question raised by the user, and replacing different word segments in the query question based on a preset business vocabulary to generate the business question raised by the user.

[0061] In some embodiments, the preset business vocabulary contains business terms corresponding to different colloquial words. Therefore, based on the preset business vocabulary, the colloquial words in different word segments in the query questions are replaced with business terms, so that the generated business questions are more professional and facilitate more accurate understanding of user needs.

[0062] In some embodiments, the business term corresponding to the colloquial term "water quality" in the preset business vocabulary is "water quality category and comprehensive pollution index." Therefore, when a user asks a query such as "What was the water quality at Bridge A last year?", the preset business vocabulary can convert the query into "What was the water quality category and comprehensive pollution index at Bridge A last year?". The business term corresponding to the colloquial term "ozone average value" in the preset business vocabulary is "90th percentile of the maximum ozone concentration over an 8-hour sliding period." Therefore, when a user asks a query such as "What was the average ozone value on August 9th?", the preset business vocabulary can convert the query into "What was the 90th percentile of the maximum ozone concentration over an 8-hour sliding period on August 9th."

[0063] Step S20: Filter out database tables that meet business requirements from multiple database tables based on entities and their attributes, and use retrieval enhancement generation technology and a large language model to retrieve database tables corresponding to business problems from multiple database tables.

[0064] In some embodiments, database tables corresponding to entities and attributes of entities corresponding to business problems, that is, database tables that meet business requirements, are screened from multiple database tables, and retrieval augmented generation technology (RAG) and large language models (LLM) are used to retrieve database tables corresponding to business problems from multiple database tables, thereby narrowing the scope of required database tables. Since retrieval augmented generation technology can enhance the accuracy of the generation process by integrating retrieval capabilities, and large language models can improve the generation and comprehension capabilities of the model through a large amount of text training, the use of retrieval augmented generation technology and large language models can more accurately retrieve the required database tables, thereby improving the efficiency of information retrieval and enhancing the accuracy of semantic understanding.

[0065] In some embodiments, entities corresponding to business problems include atmospheric monitoring sites. Therefore, database tables corresponding to atmospheric monitoring sites can be screened out from multiple database tables, that is, database tables containing the term atmospheric monitoring sites.

[0066] Step S30: determining a target database table based on the database tables that meet the business requirements and the database tables corresponding to the business problems.

[0067] In some embodiments, the method of selecting database tables that meet business requirements from multiple database tables by using entities and their attributes, as well as the method of using search-enhanced generation technology and large-scale language models to retrieve database tables corresponding to business problems from multiple database tables, both have their own limitations. Therefore, database table duplication can be checked between database tables that meet business requirements and database tables corresponding to business problems, and database tables that are identical in both database tables that meet business requirements and database tables corresponding to business problems can be used as target database tables. The determined target database tables can be one or more.

[0068] Step S40: Acquire multiple SQL example pairs.

[0069] In some embodiments, the SQL example pair includes a reference question and an SQL statement corresponding to the reference question. When the reference question is what are the number of good days, good rate, and excellent rate in Shenzhen as of May 10, 24, the SQL statement corresponding to the reference question is SELECT yltsAS"good days",yllAS"good rate",dylAS"excellent rate"FROM t_llm_hjkq_city_year_cumulation_szWHERE pjkssj='2024-01-01'andpjjssj='2024-05-10'. The reference question is how to represent the line graph of the daily average ozone value in August 2024 compared with 23 years. The corresponding SQL statement is SELECTTO_CHAR(jcsj,'MM-DD')AS"Month Day",ROUND(AVG(CASE WHEN EXTRACT(YEAR FROMjcsj)=2024THEN o3 END)::numeric,1)AS"2024 Ozone Concentration (ug / m 3 )",ROUND(AVG(CASE WHENEXTRACT(YEAR FROMjcsj)=2023THEN o3 END)::numeric,1)AS"Ozone concentration in 2023 (ug / m 3 )"FROM t_llm_hjkq_city_day_szWHERE EXTRACT(MONTH FROMjcsj)=8AND EXTRACT(YEARFROMjcsj)IN(2023,2024)

[0070] GROUP BYTO_CHAR(jcsj,'MM-DD')ORDER BY "month day" ASC.

[0071] Step S50: Perform similarity calculation based on the reference question and the business question in each SQL example pair to obtain a first similarity value for each SQL example pair, perform vectorization processing on the reference question and the business question in each SQL example pair, perform similarity calculation based on the vectorized reference question and business question to obtain a second similarity value for each SQL example pair, and determine a target SQL example pair from the multiple SQL example pairs based on the first similarity value and the second similarity value.

[0072] Please refer to Figure 3 In some embodiments, step S50 performs similarity calculation based on the reference question and the business question in each SQL example pair to obtain a first similarity value for each SQL example pair, including steps S51 to S52, which are described in detail below.

[0073] Step S51: Compare each word in the reference question in each SQL example pair with each word in the business question to obtain a comparison result.

[0074] In some embodiments, the reference question in each SQL example pair may be "What was the water quality category and comprehensive pollution index of Bridge A last year?", and the business question is "What was the water quality and comprehensive index of Bridge A last year?". By comparing each word in the reference question in each SQL example pair with each word in the business question, since the comparison result contains the same number of words, the comparison result is 14.

[0075] Step S52: performing a ratio calculation based on the comparison result and the question length of the reference question to obtain a first similarity value for each SQL example pair.

[0076] In some embodiments, a ratio calculation is performed based on the comparison result 14 and the question length 18 of the reference question to obtain a first similarity value of 0.78 for each SQL example pair.

[0077] In some embodiments, the reference question and business question in each SQL example pair are vectorized respectively, and similarity calculation is performed based on the vectorized reference question and business question to obtain a second similarity value for each SQL example pair. The similarity calculation can be implemented using the cosine similarity calculation formula.

[0078] Please refer to Figure 4 In some embodiments, step S50 determines a target SQL example pair from a plurality of SQL example pairs based on the first similarity value and the second similarity value, including steps S53 to S55, which are described in detail below.

[0079] Step S53: taking the SQL example pair whose first similarity value meets the preset similarity threshold as the first reference SQL example pair, and taking the SQL example pair whose second similarity value meets the preset similarity threshold as the second reference SQL example pair.

[0080] In some embodiments, the preset similarity threshold may be 0.5.

[0081] Step S54: Combine the first reference SQL example pairs and the second reference SQL example pairs in pairs to obtain multiple SQL example pair combinations. For each SQL example pair combination, perform a weighted calculation on the first similarity value of the first reference SQL example pair and the second similarity value of the second reference SQL example pair according to a preset weight to obtain a final weighted similarity value.

[0082] In some embodiments, the preset weights may be that the weight of the first reference SQL example pair is 0.7, and the weight of the second reference SQL example pair is 0.3.

[0083] Step S55: taking the first reference SQL example pair or the second reference SQL example pair in the SQL example pair combination whose final weighted similarity value meets the reference requirement as the target reference SQL example pair.

[0084] In some embodiments, each SQL example pair combination is sorted according to the final weighted similarity value, and the first reference SQL example pair or the second reference SQL example pair in the first four SQL example pair combinations in the sorted SQL example pair combination is selected as the target reference SQL example pair.

[0085] Step S60: Use the target SQL example pair, the structural information of the target database table, and the entities and attributes corresponding to the business problem as retrieval information, and use retrieval enhancement generation technology and a large language model to generate the SQL target query statement corresponding to the retrieval information.

[0086] In some embodiments, the SQL target query statement corresponding to the retrieval information generated using retrieval enhancement generation technology and a large language model is the SQL target query statement corresponding to the business question raised by the user. Since the retrieval information includes target SQL example pairs, structural information of the target database table, and entities and entity attributes corresponding to the business question, the obtained SQL target query statement is more semantically accurate.

[0087] In some embodiments, the specific requirements for generating SQL can also be added to the retrieval information, that is, the target SQL example pairs, the structural information of the target database table, the entities corresponding to the business problem and the attributes of the entities, and the specific requirements for generating SQL are used as retrieval information, and the retrieval enhancement generation technology and the large language model are used to generate the SQL target query statement corresponding to the retrieval information. Among them, the specific requirements for generating SQL include: 1. According to the grammatical rules of PostgreSQL query statements, executable, and containing end symbols in SQL statements; 2. Only one data table is selected to generate the SQL statement for the query; 3. The query results are set with Chinese aliases. If the Chinese alias involves the pollutant concentration value, the unit needs to be added; 4. The default year, month, and day information refers to "today's date"; 5. Time sorting defaults to ascending order; 6. When a certain indicator is required to be in descending order DESC, the null value of the indicator must be eliminated first, for example, "DESC NULLS LAST"; 7. When calculating the average AVG, it is required to use ROUND and numeric to retain one valid digit, for example,

[0088] "ROUND(AVG(co)::numeric,1), 8. SELECT requires that there can be at most one field containing time information.

[0089] In some embodiments, when the pollutant is a gas, the unit of CO is mg / m 3 , SO2, NO2, PM10, PM2.5, O3 units are ug / m 3 , the other reference standard is: AS "sulfur dioxide concentration (ug / m 3 )".

[0090] Step S70: Execute the SQL target query statement in the preset database to obtain the target query result, and use the large language model to generate the target question response corresponding to the target query result.

[0091] Please refer to Figure 5 In some embodiments, step S70 executes the SQL target query statement in the preset database to obtain the target query result, including steps S71 to S75, which are described in detail below.

[0092] Step S71: Execute the SQL target query statement in the preset database to start the data query process.

[0093] Step S72: When a query error occurs in the data query process, the SQL target query statement is corrected according to the acquired error information, business problem and large language model to obtain a corrected SQL target query statement.

[0094] In some embodiments, error messages can generally be divided into two categories: format errors and syntax errors. Syntax errors often include incorrect functions, incorrect field types, and inconsistent query statement versions.

[0095] Step S73: Use the corrected SQL target query statement to re-query the data and determine whether the query is successful.

[0096] Step S74: If the query is successful, the queried data is used as the target query result.

[0097] Step S75: Otherwise, the corrected SQL target query statement is corrected again according to the error message received when the data query is re-performed to obtain a corrected SQL target query statement, and the query result obtained by executing the corrected SQL target query statement is used as the target query result.

[0098] In some embodiments, the stability and reliability of the query are improved by performing statement correction and re-correction on the SQL target query statement.

[0099] In some embodiments, before using a large language model to generate a target question answer corresponding to a target query result, the method further includes:

[0100] The target query results are displayed differently according to the data display request sent by the user.

[0101] In some embodiments, the display processing includes retaining the target query results to a preset number of decimal places and adding a preset serial number to the target query results to adapt to different data display requests and improve the availability and display effect of the data.

[0102] Please refer to Figure 6 In some embodiments, before step S70 executes the SQL target query statement in the preset database to obtain the target query result, it also includes steps S75 to S77, which are described in detail below.

[0103] Step S75: Determine whether the SQL target query statement contains statement content for data addition, data update, and data deletion.

[0104] Step S76: If included, it is determined that the SQL target query statement does not comply with the regulations, and a warning message is issued to the user.

[0105] Step S77: If not included, determine whether the SQL target query statement complies with the regulations, and perform result query according to the SQL target query statement.

[0106] In some embodiments, whether the SQL target query statement complies with the specification is determined by determining whether the SQL target query statement contains statement content for data addition, data update, and data deletion, thereby ensuring the security of the query and avoiding erroneous operations on data.

[0107] In some embodiments, when a target query result is obtained, a large language model is used to analyze the target query result to determine whether further data analysis and visualization of the target query result is required. If not, the target query result is used as a response to the target question. If necessary, data analysis is performed based on the target query result or visualization is performed based on the target query result to obtain a response to the target question.

[0108] In some embodiments, when the target query result is the daily average ozone value in August 2024 and the average ozone value in August 2023, but the actual user's question is how to represent the line graph of the daily average ozone value in August 2024 compared with 23 years, then it is necessary to visualize the daily average ozone value in August 2024 and the average ozone value in August 2023 to generate the corresponding line graph.

[0109] In some embodiments, when visualization is required, Python program code is generated based on the data structure and field type in the target query result, and the Python program code is used for data processing and analysis chart generation. Executing the Python program in a sandbox environment can ensure the security of code execution. If an error occurs during the execution of the Python program, the system will obtain the error message and allow the large language model to further improve the code. Generate Echart code. If the Python program is executed successfully, the system will process the query result data and generate Echart code for generating interactive data visualization charts. The system displays the analysis chart generated by the Echart code to provide users with an intuitive data display.

[0110] In some embodiments, this application constructs a knowledge graph specifically for the field of environmental monitoring, integrating business knowledge, monitoring point information, and monitoring data to form a structured knowledge system. This improves the accuracy of SQL generation and the depth of semantic understanding. Furthermore, it adopts an efficient retrieval strategy, utilizing the knowledge graph to parse user questions, improving retrieval efficiency and accuracy. Furthermore, it determines a target SQL example pair from multiple SQL example pairs based on a first similarity value and a second similarity value, ensuring the relevance and accuracy of the retrieval results.

[0111] Please refer to Figure 7The present application implements a question answering device based on SQL query, including an entity recognition module 10, a database table screening module 20, a target database table determination module 30, an example pair acquisition module 40, a similarity calculation module 50, a query statement generation module 60 and a question answering module 70, which are described in detail below.

[0112] The entity recognition module 10 is used to obtain the business questions raised by the user and use the pre-built knowledge graph to identify the entities corresponding to the business questions and the attributes of the entities.

[0113] In some embodiments, users ask business questions in natural language, which may involve data query or statistical analysis. The knowledge graph is constructed by multiple entities and entity relationships between entities in the business context, where each entity has multiple different attributes.

[0114] In some embodiments, in the business context of environmental monitoring, multiple entities include rivers, cross-section monitoring points, municipal surface water quality, municipal environmental quality, municipal air quality, municipal surface water quality, atmospheric monitoring points, atmospheric monitoring records, surface water monitoring records, and administrative districts. Administrative districts have different attributes such as name, geographic location, administrative level, control level, number of monitoring points, and assessment indicators. Rivers have different attributes such as river name, basin area, maximum land width, length of river within the management area, river length, whether it is a river flowing into the sea, total land area within the management area, and basin area. Atmospheric monitoring points have different attributes such as name, code, administrative district name, longitude, latitude, regulatory level, and implementation standards. Cross-section monitoring points have different attributes such as name, code, river to which they belong, longitude, latitude, regulatory level, and implementation standards. Surface water detection records have different attributes such as record type, time, ozone, pH value, transparency, dissolved oxygen, chemical oxygen demand, water quality category, and exceedance.

[0115] Please refer back to Figure 2 In some embodiments, the relationships between entities include: belonging, including, reflecting, influencing, recording, and being recorded. For example, the entities "River" and "Section Monitoring Point" have entity relationships of including and belonging, and the entities "Atmosphere Monitoring Record" and "City Air Quality" have entity relationships of reflecting and influencing. Therefore, a knowledge graph can be constructed by constructing multiple entities and the entity relationships between them.

[0116] In some embodiments, before obtaining the business question raised by the user, the method further includes: obtaining the query question raised by the user, and replacing different word segments in the query question based on a preset business vocabulary to generate the business question raised by the user.

[0117] In some embodiments, the preset business vocabulary contains business terms corresponding to different colloquial words. Therefore, based on the preset business vocabulary, the colloquial words in different word segments in the query questions are replaced with business terms, so that the generated business questions are more professional and facilitate more accurate understanding of user needs.

[0118] In some embodiments, the business term corresponding to the colloquial term "water quality" in the preset business vocabulary is "water quality category and comprehensive pollution index." Therefore, when a user asks a query such as "What was the water quality at Bridge A last year?", the preset business vocabulary can convert the query into "What was the water quality category and comprehensive pollution index at Bridge A last year?". The business term corresponding to the colloquial term "ozone average value" in the preset business vocabulary is "90th percentile of the maximum ozone concentration over an 8-hour sliding period." Therefore, when a user asks a query such as "What was the average ozone value on August 9th?", the preset business vocabulary can convert the query into "What was the 90th percentile of the maximum ozone concentration over an 8-hour sliding period on August 9th."

[0119] The database table screening module 20 is used to screen out database tables that meet business requirements from multiple database tables based on entities and entity attributes, and use retrieval enhancement generation technology and large language models to retrieve the database table corresponding to the business problem from multiple database tables.

[0120] In some embodiments, database tables corresponding to entities and attributes of entities corresponding to business problems, that is, database tables that meet business requirements, are screened from multiple database tables, and retrieval augmented generation technology (RAG) and large language models (LLM) are used to retrieve database tables corresponding to business problems from multiple database tables, thereby narrowing the scope of required database tables. Since retrieval augmented generation technology can enhance the accuracy of the generation process by integrating retrieval capabilities, and large language models can improve the generation and comprehension capabilities of the model through a large amount of text training, the use of retrieval augmented generation technology and large language models can more accurately retrieve the required database tables, thereby improving the efficiency of information retrieval and enhancing the accuracy of semantic understanding.

[0121] In some embodiments, entities corresponding to business problems include atmospheric monitoring sites. Therefore, database tables corresponding to atmospheric monitoring sites can be screened out from multiple database tables, that is, database tables containing the term atmospheric monitoring sites.

[0122] The target database table determination module 30 is configured to determine the target database table based on the database tables that meet the business requirements and the database tables corresponding to the business problems.

[0123] In some embodiments, the method of selecting database tables that meet business requirements from multiple database tables by using entities and their attributes, as well as the method of using search-enhanced generation technology and large-scale language models to retrieve database tables corresponding to business problems from multiple database tables, both have their own limitations. Therefore, database table duplication can be checked between database tables that meet business requirements and database tables corresponding to business problems, and database tables that are identical in both database tables that meet business requirements and database tables corresponding to business problems can be used as target database tables. The determined target database tables can be one or more.

[0124] The example pair acquisition module 40 is configured to acquire multiple SQL example pairs.

[0125] In some embodiments, the SQL example pair includes a reference question and an SQL statement corresponding to the reference question. When the reference question is what are the number of good days, good rate, and excellent rate in Shenzhen as of May 10, 24, the SQL statement corresponding to the reference question is SELECT yltsAS"good days",yllAS"good rate",dylAS"excellent rate"FROM t_llm_hjkq_city_year_cumulation_szWHERE pjkssj='2024-01-01'andpjjssj='2024-05-10'. The reference question is how to represent the line graph of the daily average ozone value in August 2024 compared with 23 years. The corresponding SQL statement is SELECTTO_CHAR(jcsj,'MM-DD')AS"Month Day",ROUND(AVG(CASE WHEN EXTRACT(YEAR FROMjcsj)=2024THEN o3 END)::numeric,1)AS"2024 Ozone Concentration (ug / m 3 )",ROUND(AVG(CASE WHENEXTRACT(YEAR FROMjcsj)=2023THEN o3 END)::numeric,1)AS"Ozone concentration in 2023 (ug / m 3 )"FROM t_llm_hjkq_city_day_szWHERE EXTRACT(MONTH FROMjcsj)=8AND EXTRACT(YEARFROMjcsj)IN(2023,2024)

[0126] GROUP BYTO_CHAR(jcsj,'MM-DD')ORDER BY "month day" ASC.

[0127] The similarity calculation module 50 is used to perform similarity calculation based on the reference question and the business question in each SQL example pair, obtain a first similarity value for each SQL example pair, vectorize the reference question and the business question in each SQL example pair, perform similarity calculation based on the vectorized reference question and business question, obtain a second similarity value for each SQL example pair, and determine a target SQL example pair from multiple SQL example pairs based on the first similarity value and the second similarity value.

[0128] Please refer back to Figure 3 In some embodiments, similarity calculation is performed based on the reference question and the business question in each SQL example pair to obtain a first similarity value for each SQL example pair, which is described in detail below.

[0129] Each word in the reference question in each SQL example pair is compared with each word in the business question to obtain a comparison result. A ratio is calculated based on the comparison result and the question length of the reference question to obtain a first similarity value for each SQL example pair.

[0130] In some embodiments, the reference question in each SQL example pair may be "What was the water quality category and comprehensive pollution index of Bridge A last year?", and the business question is "What was the water quality and comprehensive index of Bridge A last year?". By comparing each word in the reference question in each SQL example pair with each word in the business question, since the comparison result contains the same number of words, the comparison result is 14.

[0131] In some embodiments, a ratio calculation is performed based on the comparison result 14 and the question length 18 of the reference question to obtain a first similarity value of 0.78 for each SQL example pair.

[0132] In some embodiments, the reference question and business question in each SQL example pair are vectorized respectively, and similarity calculation is performed based on the vectorized reference question and business question to obtain a second similarity value for each SQL example pair. The similarity calculation can be implemented using the cosine similarity calculation formula.

[0133] Please refer back to Figure 4 In some embodiments, a target SQL example pair is determined from a plurality of SQL example pairs based on a first similarity value and a second similarity value, as described in detail below.

[0134] SQL example pairs whose first similarity values ​​meet a preset similarity threshold are used as first reference SQL example pairs, and SQL example pairs whose second similarity values ​​meet the preset similarity threshold are used as second reference SQL example pairs. The first reference SQL example pairs and the second reference SQL example pairs are combined in pairs to obtain multiple SQL example pair combinations. For each SQL example pair combination, the first similarity value of the first reference SQL example pair and the second similarity value of the second reference SQL example pair are weighted according to preset weights to obtain a final weighted similarity value. The first reference SQL example pair or the second reference SQL example pair in the SQL example pair combination whose final weighted similarity value meets the reference requirement is used as the target reference SQL example pair.

[0135] In some embodiments, the preset similarity threshold may be 0.5. The preset weight may be 0.7 for the first reference SQL example pair and 0.3 for the second reference SQL example pair.

[0136] In some embodiments, each SQL example pair combination is sorted according to the final weighted similarity value, and the first reference SQL example pair or the second reference SQL example pair in the first four SQL example pair combinations in the sorted SQL example pair combination is selected as the target reference SQL example pair.

[0137] The query statement generation module 60 is used to use the target SQL example pairs, the structural information of the target database table, and the entities and attributes corresponding to the business problems as retrieval information, and use retrieval enhancement generation technology and a large language model to generate SQL target query statements corresponding to the retrieval information.

[0138] In some embodiments, the SQL target query statement corresponding to the retrieval information generated using retrieval enhancement generation technology and a large language model is the SQL target query statement corresponding to the business question raised by the user. Since the retrieval information includes target SQL example pairs, structural information of the target database table, and entities and entity attributes corresponding to the business question, the obtained SQL target query statement is more semantically accurate.

[0139] In some embodiments, the specific requirements for generating SQL can also be added to the retrieval information, that is, the target SQL example pairs, the structural information of the target database table, the entities corresponding to the business problem and the attributes of the entities, and the specific requirements for generating SQL are used as retrieval information, and the retrieval enhancement generation technology and the large language model are used to generate the SQL target query statement corresponding to the retrieval information. Among them, the specific requirements for generating SQL include: 1. According to the grammatical rules of PostgreSQL query statements, executable, and containing end symbols in SQL statements; 2. Only one data table is selected to generate the SQL statement for the query; 3. The query results are set with Chinese aliases. If the Chinese alias involves the pollutant concentration value, the unit needs to be added; 4. The default year, month, and day information refers to "today's date"; 5. Time sorting defaults to ascending order; 6. When a certain indicator is required to be in descending order DESC, the null value of the indicator must be eliminated first, for example, "DESC NULLS LAST"; 7. When calculating the average AVG, it is required to use ROUND and numeric to retain one valid digit, for example,

[0140] "ROUND(AVG(co)::numeric,1), 8. SELECT requires that there can be at most one field containing time information.

[0141] In some embodiments, when the pollutant is a gas, the unit of CO is mg / m 3 , SO2, NO2, PM10, PM2.5, O3 units are ug / m 3 , the other reference standard is: AS "sulfur dioxide concentration (ug / m 3 )".

[0142] The question answering module 70 is used to execute the SQL target query statement in the preset database to obtain the target query result, and use the large language model to generate the target question answer corresponding to the target query result.

[0143] Please refer back to Figure 5 In some embodiments, an SQL target query statement is executed in a preset database to obtain a target query result, which is described in detail below.

[0144] Execute a SQL target query statement in the preset database to start the data query process. If a query error occurs during the data query process, the SQL target query statement is corrected based on the error information, business problem, and large-scale language model to obtain a corrected SQL target query statement. Use the corrected SQL target query statement to re-query the data and determine whether the query is successful.

[0145] If the query is successful, the queried data is used as the target query result. Otherwise, the revised SQL target query statement is further revised based on the error message received when re-querying the data, resulting in a further revised SQL target query statement. The query result obtained by executing the further revised SQL target query statement is used as the target query result.

[0146] In some embodiments, error messages can generally be divided into two categories: format errors and syntax errors. Syntax errors often include incorrect functions, incorrect field types, and inconsistent query statement versions.

[0147] In some embodiments, the stability and reliability of the query are improved by performing statement correction and re-correction on the SQL target query statement.

[0148] In some embodiments, before using a large language model to generate a target question answer corresponding to a target query result, the method further includes: performing different display processing on the target query result according to a data display request sent by a user.

[0149] In some embodiments, the display processing includes retaining the target query results to a preset number of decimal places and adding a preset serial number to the target query results to adapt to different data display requests and improve the availability and display effect of the data.

[0150] Please refer back to Figure 6 In some embodiments, before executing the SQL target query statement in the preset database to obtain the target query results, the process further includes determining whether the SQL target query statement contains statements for data addition, data update, and data deletion. If so, the SQL target query statement is determined to be non-compliant with regulations, and a warning message is issued to the user. If not, the SQL target query statement is determined to be compliant with regulations, and a result query is performed based on the SQL target query statement.

[0151] In some embodiments, whether the SQL target query statement complies with the specification is determined by determining whether the SQL target query statement contains statement content for data addition, data update, and data deletion, thereby ensuring the security of the query and avoiding erroneous operations on data.

[0152] In some embodiments, when a target query result is obtained, a large language model is used to analyze the target query result to determine whether further data analysis and visualization of the target query result is required. If not, the target query result is used as a response to the target question. If necessary, data analysis is performed based on the target query result or visualization is performed based on the target query result to obtain a response to the target question.

[0153] In some embodiments, when the target query result is the daily average ozone value in August 2024 and the average ozone value in August 2023, but the actual user's question is how to represent the line graph of the daily average ozone value in August 2024 compared with 23 years, then it is necessary to visualize the daily average ozone value in August 2024 and the average ozone value in August 2023 to generate the corresponding line graph.

[0154] In some embodiments, when visualization is required, Python program code is generated based on the data structure and field type in the target query result, and the Python program code is used for data processing and analysis chart generation. Executing the Python program in a sandbox environment can ensure the security of code execution. If an error occurs during the execution of the Python program, the system will obtain the error message and allow the large language model to further improve the code. Generate Echart code. If the Python program is executed successfully, the system will process the query result data and generate Echart code for generating interactive data visualization charts. The system displays the analysis chart generated by the Echart code to provide users with an intuitive data display.

[0155] In some embodiments, this application constructs a knowledge graph specifically for the field of environmental monitoring, integrating business knowledge, monitoring point information, and monitoring data to form a structured knowledge system. This improves the accuracy of SQL generation and the depth of semantic understanding. Furthermore, it adopts an efficient retrieval strategy, utilizing the knowledge graph to parse user questions, improving retrieval efficiency and accuracy. Furthermore, it determines a target SQL example pair from multiple SQL example pairs based on a first similarity value and a second similarity value, ensuring the relevance and accuracy of the retrieval results.

[0156] In some embodiments, a question answering device based on SQL query includes: a memory for storing a program; and a processor for implementing a question answering method by executing the program stored in the memory.

[0157] Those skilled in the art will appreciate that all or part of the functions of the various methods in the above embodiments can be implemented by hardware or by computer program. When all or part of the functions in the above embodiments are implemented by computer program, the program can be stored in a computer-readable storage medium, and the storage medium can include: read-only memory, random access memory, disk, optical disk, hard disk, etc., and the program is executed by a computer to implement the above functions. For example, the program is stored in the memory of the device, and when the program in the memory is executed by the processor, all or part of the above functions can be implemented. In addition, when all or part of the functions in the above embodiments are implemented by computer program, the program can also be stored in a storage medium such as a server, another computer, disk, optical disk, flash disk or mobile hard disk, and saved in the memory of the local device by downloading or copying, or the system of the local device is updated. When the program in the memory is executed by the processor, all or part of the functions in the above embodiments can be implemented.

[0158] The above examples are used to illustrate the present invention, which are only used to help understand the present invention and are not intended to limit the present invention. Those skilled in the art can make several simple deductions, modifications or substitutions based on the concept of the present invention.

Claims

1. A question answering method based on SQL query, characterized in that: include: Obtaining a business question raised by a user, and using a pre-built knowledge graph to identify entities corresponding to the business question and the attributes of the entities; wherein the knowledge graph is constructed by multiple entities in the business context and multiple entity relationships between the entities, each entity having multiple different attributes; Screening out a database table that meets the business requirements from multiple database tables based on the entity and its attributes, and retrieving the database table corresponding to the business problem from the multiple database tables using retrieval enhancement generation technology and a large language model; Determining a target database table based on the database table that meets the business requirements and the database table corresponding to the business problem; wherein at least one target database table is included; Acquire multiple SQL example pairs, wherein the SQL example pairs include reference questions and SQL statements corresponding to the reference questions; performing similarity calculations based on the reference question and the business question in each SQL example pair to obtain a first similarity value for each SQL example pair, performing vectorization processing on the reference question and the business question in each SQL example pair, performing similarity calculations based on the vectorized reference question and the business question to obtain a second similarity value for each SQL example pair, and determining a target SQL example pair from the multiple SQL example pairs based on the first similarity value and the second similarity value; The target SQL example pair, the structural information of the target database table, the entity corresponding to the business problem, and the attributes of the entity are used as retrieval information, and the retrieval enhancement generation technology and a large language model are used to generate the SQL target query statement corresponding to the retrieval information; The SQL target query statement is executed in a preset database to obtain a target query result, and the large language model is used to generate a target question response corresponding to the target query result.

2. The question answering method according to claim 1, wherein: The determining a target SQL example pair from the plurality of SQL example pairs based on the first similarity value and the second similarity value includes: Taking the SQL example pair whose first similarity value meets the preset similarity threshold as a first reference SQL example pair, and taking the SQL example pair whose second similarity value meets the preset similarity threshold as a second reference SQL example pair; Combining the first reference SQL example pairs with the second reference SQL example pairs in pairs to obtain multiple SQL example pair combinations, and for each SQL example pair combination, performing a weighted calculation on the first similarity value of the first reference SQL example pair and the second similarity value of the second reference SQL example pair according to a preset weight to obtain a final weighted similarity value; The first reference SQL example pair or the second reference SQL example pair in the SQL example pair combination whose final weighted similarity value meets the reference requirement is used as a target reference SQL example pair.

3. The question answering method according to claim 1, wherein: The performing similarity calculation based on the reference question and the business question in each SQL example pair to obtain a first similarity value for each SQL example pair includes: Comparing each word in the reference question in each SQL example pair with each word in the business question to obtain a comparison result; the comparison result includes the number of identical words; A ratio calculation is performed based on the comparison result and the question length of the reference question to obtain a first similarity value for each of the SQL example pairs.

4. The question answering method according to claim 1, wherein: The executing the SQL target query statement in the preset database to obtain the target query result includes: Execute the SQL target query statement in the preset database to start the data query process; When a query error occurs in the data query process, the SQL target query statement is corrected according to the acquired error information, the business problem, and the large language model to obtain a corrected SQL target query statement; Re-query the data using the modified SQL target query statement. If the query is successful, the queried data is used as the target query result. Otherwise, the corrected SQL target query statement is revised again according to the error message received when re-querying the data to obtain the revised SQL target query statement, and the query result obtained by executing the revised SQL target query statement is used as the target query result.

5. The question answering method according to claim 1, wherein: Before using the large language model to generate a target question answer corresponding to the target query result, the question answering method further includes: The target query result is subjected to different display processing according to the data display request sent by the user, wherein the display processing includes retaining the target query result to a preset number of decimal places and adding a preset serial number to the target query result.

6. The question answering method according to claim 1, wherein: Before executing the SQL target query statement in the preset database to obtain the target query result, the question answering method further includes: Determine whether the SQL target query statement contains statement content for data addition, data update, and data deletion; If included, it is determined that the SQL target query statement does not comply with the regulations and a warning message is issued to the user; If not included, it is determined that the SQL target query statement complies with the regulations, and a result query is performed according to the SQL target query statement.

7. The question answering method according to claim 1, wherein: Before obtaining the business question raised by the user, the question answering method further includes: A query question raised by a user is obtained, and different word segments in the query question are replaced based on a preset business vocabulary to generate a business question raised by the user.

8. A question answering device based on SQL query, characterized in that: include: An entity recognition module is used to obtain a business question raised by a user and identify the entities corresponding to the business question and the attributes of the entities using a pre-built knowledge graph; wherein the knowledge graph is constructed by multiple entities in the business context and the entity relationships between the entities, each entity having multiple different attributes; A database table screening module, configured to screen out a database table that meets the business requirements from multiple database tables based on the entity and its attributes, and to retrieve the database table corresponding to the business problem from the multiple database tables using retrieval enhancement generation technology and a large language model; A target database table determination module is configured to determine a target database table based on the database table that meets the business requirements and the database table corresponding to the business problem; wherein the target database table includes at least one target database table; An example pair acquisition module is used to acquire multiple SQL example pairs, wherein the SQL example pairs include reference questions and SQL statements corresponding to the reference questions; a similarity calculation module, configured to perform similarity calculation based on the reference question and the business question in each SQL example pair, respectively, to obtain a first similarity value for each SQL example pair; perform vectorization processing on the reference question and the business question in each SQL example pair, perform similarity calculation based on the vectorized reference question and the business question, to obtain a second similarity value for each SQL example pair; and determine a target SQL example pair from the multiple SQL example pairs based on the first similarity value and the second similarity value; A query statement generation module is used to use the target SQL example pair, the structural information of the target database table, the entity corresponding to the business question, and the attributes of the entity as retrieval information, and use retrieval enhancement generation technology and a large language model to generate an SQL target query statement corresponding to the retrieval information; The question answering module is used to execute the SQL target query statement in the preset database to obtain the target query result, and use the large language model to generate the target question answer corresponding to the target query result.

9. A question answering device based on SQL query, characterized in that: include: Memory, used to store programs; A processor, configured to implement the question answering method according to any one of claims 1 to 7 by executing the program stored in the memory.

10. A computer-readable storage medium, characterized in that The computer-readable storage medium stores a program, which can be executed by a processor to implement the question answering method according to any one of claims 1 to 7.

Images