Question reply method and device based on SQL query, equipment and medium

Abstract

A question reply method, apparatus and device based on SQL query, and a medium, comprising: screening a database table meeting business requirements from a plurality of database tables according to entities corresponding to business questions identified by a knowledge graph and attributes of the entities, and retrieving the database table corresponding to the business problem from the plurality of database tables by utilizing a retrieval enhancement generation technology and a large language model, and determining a target database table based on the database table meeting the business requirement and the database table corresponding to the business problem. And determining a target SQL example pair based on the first similarity value and the second similarity value of each SQL example pair, taking the target SQL example pair, the structural information of the target database table, the entity of the business problem and the attribute of the entity as retrieval information, and generating an SQL target query statement corresponding to the retrieval information by utilizing a retrieval enhancement generation technology and a large language model. And executing the SQL target query statement to obtain a target query result and a target question reply. And the semantic accuracy of the SQL query statement and the accuracy of question reply are improved.

Description

Technical Field

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

[0002] Traditional environmental monitoring systems often face the problems of large data volumes, diverse types, and frequent updates, making them difficult to manage and analyze effectively. When users ask questions about the data in the environmental monitoring system, the questions may be ambiguous or diverse, so how to understand and convert them into specific query requirements is a challenge. When users need to ask questions based on the above environmental business knowledge, they usually use a technology that converts natural language queries into SQL statements to query the database. This technology parses the user's natural language query to generate the corresponding SQL query statement, and executes the SQL query statement to query the database. However, this method usually relies on template matching or rule-driven, has limitations when processing complex queries, and is 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 of question answers.

[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 attributes of the entity; wherein the knowledge graph is constructed by multiple entities in a business context and entity relationships between multiple entities, and each entity has multiple different attributes;

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

[0007] 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 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 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 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 structure information of the target database table, the entity corresponding to the business question and the attribute of the entity are used as retrieval information, and the SQL target query statement corresponding to the retrieval information is generated by using retrieval enhancement generation technology and a large language model;

[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] The first reference SQL example pairs and the second reference SQL example pairs are combined in pairs to obtain a plurality of SQL example pair combinations, and for each SQL example pair combination, a first similarity value of the first reference SQL example pair and a second similarity value of the second reference SQL example pair are weighted 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 combination of SQL example pairs 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 of the SQL example pairs to obtain a first similarity value for each of the SQL example pairs includes:

[0017] Compare 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, and if the query is successful, use the queried data as the target query result;

[0023] Otherwise, the corrected SQL target query statement is corrected again according to the error message received when re-querying the data to obtain the 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.

[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 the business questions raised by the user and identify the entities corresponding to the business questions and the attributes of the entities using the pre-built knowledge graph; wherein the knowledge graph is constructed by multiple entities in the business context and the entity relationships between the multiple entities, and each entity has multiple different attributes;

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

[0035] A target database table determination module, used to determine a target database table based on a 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;

[0036] An example pair acquisition module, 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 according to the reference question and the business question in each SQL example pair, respectively, to obtain a first similarity value for each SQL example pair, respectively vectorize the reference question and the business question in each SQL example pair, perform similarity calculation according to 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 attribute 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 a preset database to obtain a target query result, and use the large language model to generate a target question answer corresponding to the target query result.

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

[0041] Memory, used to store programs;

[0042] The processor is used 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, on which a program is stored, and the program can be executed by a processor to implement a question answering method.

[0044] According to the above-mentioned embodiment, a method, device, equipment and computer-readable storage medium for answering questions based on SQL queries are described. Since the entities and attributes of the entities corresponding to the business questions are identified according to 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, so as to improve the accuracy of the subsequent question responses. 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, and the target SQL example pair is determined from multiple SQL example pairs according to the first similarity value and the second similarity value, so as to achieve accurate search of the target SQL example pair, and the SQL target query statement corresponding to the retrieval information composed of the target SQL example pair is generated by using the retrieval enhancement generation technology and the large language model, so as to improve 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 response 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 of an embodiment;

[0047] Figure 3 A flowchart of an embodiment of performing similarity calculations according to a reference question and a business question in each SQL example pair to obtain a first similarity value of 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 an 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 schematic diagram of the structure of a question answering device based on SQL query according to an embodiment. DETAILED DESCRIPTION

[0052] The present invention is further described in detail below by specific embodiments in conjunction with the accompanying drawings. Wherein similar elements in different embodiments adopt associated similar element numbers. In the following embodiments, many detailed descriptions are for making the present application better understood. However, those skilled in the art can easily recognize that some features can be omitted in different situations, or can be replaced by other elements, materials, 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 part of the present application being overwhelmed by too much description, and for those skilled in the art, it is not necessary to describe these related operations in detail, and they can fully understand the related operations according to 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 can be combined in any appropriate manner to form various implementations. At the same time, the steps or actions in the method description can also be interchanged or adjusted in a manner that is obvious to those skilled in the art. Therefore, the various sequences in the specification and the drawings are only for the purpose of clearly describing a certain embodiment and are not meant to be a required sequence, unless otherwise specified that a certain sequence must be followed.

[0054] The serial numbers of the components in this document, such as "first", "second", etc., are only used to distinguish the objects described and do not have any order or technical meaning. The "connection" and "coupling" mentioned in this application, unless otherwise specified, include 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 raise business questions in natural language, which may be questions about data query or statistical analysis. The knowledge graph is constructed through multiple entities in the business context and entity relationships between multiple entities, where each entity has multiple different attributes.

[0058] In some embodiments, in the business context of the field 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. Under the administrative district, there are different attributes such as name, geographical location, administrative level, control level, number of monitoring points, and assessment indicators. Under the river, there are different attributes such as river name, basin area, maximum land width, length of river within the management scope, river length, whether it is a river entering the sea, total land area within the management scope, and basin area. Under the atmospheric monitoring point, there are different attributes such as name, code, administrative district name, longitude, latitude, supervision level, and implementation standards. Under the cross-section monitoring point, there are different attributes such as name, code, river, longitude, latitude, supervision level, and implementation standards. Under the surface water detection record, there are different attributes such as record type, time, ozone, pH value, transparency, dissolved oxygen, chemical oxygen demand, water quality category, and exceeding standard.

[0059] Please refer to Figure 2 In some embodiments, the relationship between entities includes: belonging, including, reflecting, influencing, recording, and being recorded, etc. For example, there is an entity relationship of including and belonging between the two entities of river and cross-section monitoring point, and there is an entity relationship of reflecting and influencing between the two entities of atmospheric monitoring record and city air quality. Therefore, a knowledge graph can be constructed by multiple entities and the entity relationships between multiple entities.

[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 the query question raised by the user is "what was the water quality of Bridge A last year", the preset business vocabulary can convert the query question into "what was the water quality category and comprehensive pollution index of 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 concentration of ozone sliding for 8 hours". Therefore, when the query question raised by the user is "what was the ozone average value on August 9", the preset business vocabulary can convert the query question into "what was the 90th percentile of the maximum concentration of ozone sliding for 8 hours on August 9".

[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, entities corresponding to business problems and database tables corresponding to the attributes of entities, that is, database tables that meet business requirements, are screened out from multiple database tables, and database tables corresponding to business problems are retrieved from multiple database tables using retrieval augmented generation technology (RAG) and large language models (LLM), 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 understanding 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, the entities corresponding to the business problem include air monitoring sites, and therefore, a database table corresponding to the air monitoring sites may be screened out from multiple database tables, that is, a database table containing the term “air monitoring sites”.

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

[0067] In some embodiments, the method of selecting a database table that meets business requirements from multiple database tables by using entities and their attributes, and the method of using search enhancement generation technology and a large language model to retrieve a database table corresponding to a business problem from multiple database tables both have their own limitations. Therefore, a database table duplication detection can be performed on the database table that meets business requirements and the database table that corresponds to the business problem, and the database table that meets business requirements and the database table that corresponds to the business problem are the same as the target database table. The determined target database table 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'. When the reference question is how to express 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 year ozone concentration (ug / m 3 )",ROUND(AVG(CASE WHENEXTRACT(YEAR FROMjcsj)=2023THEN o3 END)::numeric,1)AS"2023 ozone concentration (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, 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 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 may be “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: Calculate the ratio between 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 the business question in each SQL example pair are vectorized respectively, and a similarity calculation is performed based on the vectorized reference question and the business question to obtain a second similarity value for each SQL example pair. The similarity calculation can be implemented by a 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 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 weight 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 combinations 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 of the entities corresponding to the business question as retrieval information, and use retrieval enhancement generation technology and a large language model to generate a 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 attributes of the entities 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 pair, the structural information of the target database table, the entity corresponding to the business problem and the attribute of the entity, and the specific requirements for generating SQL are used as retrieval information, and the SQL target query statement corresponding to the retrieval information is generated using retrieval enhancement generation technology and a large language model. Among them, the specific requirements for generating SQL include: 1. According to the syntax rules of PostgreSQL query statements, executable, and SQL statements contain end symbols; 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. The default time sorting is ascending; 6. When a certain indicator is required to be in descending order DESC, the null value of the indicator must be removed 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 , alias reference standard: 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 a 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 re-querying the data 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 accommodate different data display requests and improve data availability and display effects.

[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 of data addition, data update and data deletion, thereby ensuring the security of the query and avoiding erroneous operation of 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 results are 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 according to 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 intuitive data display.

[0110] In some embodiments, the present application forms a structured knowledge system by constructing a knowledge graph specifically for the field of environmental monitoring, integrating business knowledge, monitoring point information, and monitoring data. The accuracy of SQL generation and the depth of semantic understanding are improved. In addition, an efficient retrieval strategy is adopted to parse user questions using the knowledge graph to improve the efficiency and accuracy of retrieval. The target SQL example pair is determined from multiple SQL example pairs based on the first similarity value and the second similarity value to ensure 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 raise business questions in natural language, which may be questions about data query or statistical analysis. The knowledge graph is constructed through multiple entities in the business context and entity relationships between multiple entities, where each entity has multiple different attributes.

[0114] In some embodiments, in the business context of the field 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. Under the administrative district, there are different attributes such as name, geographical location, administrative level, control level, number of monitoring points, and assessment indicators. Under the river, there are different attributes such as river name, basin area, maximum land width, length of river within the management scope, river length, whether it is a river entering the sea, total land area within the management scope, and basin area. Under the atmospheric monitoring point, there are different attributes such as name, code, administrative district name, longitude, latitude, supervision level, and implementation standards. Under the cross-section monitoring point, there are different attributes such as name, code, river, longitude, latitude, supervision level, and implementation standards. Under the surface water detection record, there are different attributes such as record type, time, ozone, pH value, transparency, dissolved oxygen, chemical oxygen demand, water quality category, and exceeding standard.

[0115] Please refer back to Figure 2 In some embodiments, the relationship between entities includes: belonging, including, reflecting, influencing, recording, and being recorded, etc. For example, there is an entity relationship of including and belonging between the two entities of river and cross-section monitoring point, and there is an entity relationship of reflecting and influencing between the two entities of atmospheric monitoring record and city air quality. Therefore, a knowledge graph can be constructed by multiple entities and the entity relationships between multiple entities.

[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 the query question raised by the user is "what was the water quality of Bridge A last year", the preset business vocabulary can convert the query question into "what was the water quality category and comprehensive pollution index of 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 concentration of ozone sliding for 8 hours". Therefore, when the query question raised by the user is "what was the ozone average value on August 9", the preset business vocabulary can convert the query question into "what was the 90th percentile of the maximum concentration of ozone sliding for 8 hours on August 9".

[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 their attributes, and to retrieve the database tables corresponding to business problems from multiple database tables using retrieval enhancement generation technology and a large language model.

[0120] In some embodiments, entities corresponding to business problems and database tables corresponding to the attributes of entities, that is, database tables that meet business requirements, are screened out from multiple database tables, and database tables corresponding to business problems are retrieved from multiple database tables using retrieval augmented generation technology (RAG) and large language models (LLM), 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 understanding 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, the entities corresponding to the business problem include air monitoring sites, and therefore, a database table corresponding to the air monitoring sites may be screened out from multiple database tables, that is, a database table containing the term “air monitoring sites”.

[0122] The target database table determination module 30 is used to determine the target database table based on the database table that meets the business requirements and the database table corresponding to the business problem.

[0123] In some embodiments, the method of selecting a database table that meets business requirements from multiple database tables by using entities and their attributes, and the method of using search enhancement generation technology and a large language model to retrieve a database table corresponding to a business problem from multiple database tables both have their own limitations. Therefore, a database table duplication detection can be performed on the database table that meets business requirements and the database table that corresponds to the business problem, and the database table that meets business requirements and the database table that corresponds to the business problem are the same as the target database table. The determined target database table can be one or more.

[0124] The example pair acquisition module 40 is used 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'. When the reference question is how to express 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 year ozone concentration (ug / m 3 )",ROUND(AVG(CASE WHENEXTRACT(YEAR FROMjcsj)=2023THEN o3 END)::numeric,1)AS"2023 ozone concentration (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, respectively, to obtain a first similarity value for each SQL example pair, to vectorize the reference question and the business question in each SQL example pair, and to perform similarity calculation based on the vectorized reference question and business question, to obtain a second similarity value for each SQL example pair, and to 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 calculation is performed 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 may be “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 the business question in each SQL example pair are vectorized respectively, and a similarity calculation is performed based on the vectorized reference question and the business question to obtain a second similarity value for each SQL example pair. The similarity calculation can be implemented by a 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] The SQL example pair whose first similarity value meets the preset similarity threshold is used as the first reference SQL example pair, and the SQL example pair whose second similarity value meets the preset similarity threshold is used as the second reference SQL example pair. The first reference SQL example pair and the second reference SQL example pair 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 the 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 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 combinations is selected as the target reference SQL example pair.

[0137] The query statement generation module 60 is used to use the target SQL example pair, the structural information of the target database table, and the entities and attributes of the entities corresponding to the business problem as retrieval information, and use the retrieval enhancement generation technology and the large language model to generate the SQL target query statement 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 attributes of the entities 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 pair, the structural information of the target database table, the entity corresponding to the business problem and the attribute of the entity, and the specific requirements for generating SQL are used as retrieval information, and the SQL target query statement corresponding to the retrieval information is generated using retrieval enhancement generation technology and a large language model. Among them, the specific requirements for generating SQL include: 1. According to the syntax rules of PostgreSQL query statements, executable, and SQL statements contain end symbols; 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. The default time sorting is ascending; 6. When a certain indicator is required to be in descending order DESC, the null value of the indicator must be removed 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 , alias reference standard: 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 the SQL target query statement in the preset database to start the data query process. When there is a query error in the data query process, the SQL target query statement is corrected according to the error information, business problems and large language models 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 corrected SQL target query statement is corrected again according to the error message received when re-querying the data, 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.

[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 accommodate different data display requests and improve data availability and display effects.

[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 result, it also includes determining whether the SQL target query statement contains statement content of data addition, data update and data deletion. If it does, 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 it does not, it is determined that the SQL target query statement complies with the regulations, and the result query is performed according to 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 of data addition, data update and data deletion, thereby ensuring the security of the query and avoiding erroneous operation of 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 results are 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 according to 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 intuitive data display.

[0155] In some embodiments, the present application forms a structured knowledge system by constructing a knowledge graph specifically for the field of environmental monitoring, integrating business knowledge, monitoring point information, and monitoring data. The accuracy of SQL generation and the depth of semantic understanding are improved. In addition, an efficient retrieval strategy is adopted to parse user questions using the knowledge graph to improve the efficiency and accuracy of retrieval. The target SQL example pair is determined from multiple SQL example pairs based on the first similarity value and the second similarity value to ensure the relevance and accuracy of the retrieval results.

[0156] In some embodiments, a SQL query-based question answering device 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-mentioned embodiments can be implemented by hardware or by computer programs. When all or part of the functions in the above-mentioned embodiments are implemented by computer programs, 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-mentioned 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-mentioned functions can be implemented. In addition, when all or part of the functions in the above-mentioned embodiments are implemented by computer programs, 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 can be downloaded or copied and saved in the memory of the local device, or the system of the local device is updated, and when the program in the memory is executed by the processor, all or part of the functions in the above-mentioned embodiments can be implemented.

[0158] The above specific examples are used to illustrate the present invention, which is only used to help understand the present invention and is not intended to limit the present invention. For those skilled in the art, according to the concept of the present invention, some simple deductions, modifications or substitutions can be made.

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 attributes of the entity; wherein the knowledge graph is constructed by multiple entities in a business context and entity relationships between multiple entities, and each entity has multiple different attributes; Screening out a database table that meets the business requirements from multiple database tables according to the entity and the attribute of the entity, and retrieving the database table corresponding to the business problem from the multiple database tables by using retrieval enhancement generation technology and a large language model; 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 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 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 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 structure information of the target database table, the entity corresponding to the business question and the attribute of the entity are used as retrieval information, and the SQL target query statement corresponding to the retrieval information is generated by using retrieval enhancement generation technology and a large language model; 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, characterized in that: 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 comprises: 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; The first reference SQL example pairs and the second reference SQL example pairs are combined in pairs to obtain a plurality of SQL example pair combinations, and for each SQL example pair combination, a first similarity value of the first reference SQL example pair and a second similarity value of the second reference SQL example pair are weighted 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 combination of SQL example pairs 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, characterized in that: The performing similarity calculation according to the reference question and the business question in each SQL example pair to obtain a first similarity value of each SQL example pair includes: Compare 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, characterized in that: 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, and if the query is successful, use the queried data as the target query result; Otherwise, the corrected SQL target query statement is corrected again according to the error message received when re-querying the data to obtain the 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.

5. The question answering method according to claim 1, characterized in that: 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, characterized in that: 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, characterized in that: 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 the business questions raised by the user and identify the entities corresponding to the business questions and the attributes of the entities using the pre-built knowledge graph; wherein the knowledge graph is constructed by multiple entities in the business context and the entity relationships between the multiple entities, and each entity has multiple different attributes; A database table screening module, used to screen out a database table that meets the business requirements from multiple database tables according to the entity and the attributes of the entity, and 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, used to determine a target database table based on a 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; An example pair acquisition module, 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 according to the reference question and the business question in each SQL example pair, respectively, to obtain a first similarity value for each SQL example pair, respectively vectorize the reference question and the business question in each SQL example pair, perform similarity calculation according to 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 attribute 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 a preset database to obtain a target query result, and use the large language model to generate a 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, and the program can be executed by a processor to implement the question answering method according to any one of claims 1 to 7.

Images