Method and system for evaluating accuracy of NL2SQL in dialogue analysis system

Abstract

The invention discloses an NL2SQL accuracy evaluation method and system in a conversational analysis system, belongs to the technical field of natural language processing and software evaluation, and aims to solve the technical problem of how to realize automatic evaluation of NL2SQL conversion accuracy in the conversational analysis system. According to the technical scheme, the method comprises the steps of obtaining a url path and login information of a tested system and an evaluation data set file needing to serve as a standard answer; circularly inputting questions in the standard answers into an API interface corresponding to the tested system, and carrying out standardization processing on returned contents; and comparing and evaluating the return value of the tested system with the standard answer, and outputting an evaluation result. The system comprises a base layer, a processing layer and an application layer, the base layer is developed by adopting Python, the application layer adopts a UI component, and the processing layer comprises an input module, a loading module, a data acquisition module, a data processing module, an evaluation module and a result output module.

Description

Technical Field

[0001] The present invention relates to the technical field of natural language processing and software evaluation, and in particular to an NL2SQL accuracy evaluation method and system in a conversational analysis system. Background Art

[0002] With the development of big data and artificial intelligence technology, more and more companies and individuals hope to obtain the required information through natural language query databases. Conversational analysis combines artificial intelligence technology with business intelligence (BI), analyzes and translates through natural language analysis engines, introduces large language model technology, deeply identifies user intentions, helps users more easily obtain data insights and opinions, and achieves zero threshold for data analysis. In the conversational analysis system, NL2SQL technology aims to convert natural language queries into SQL query statements, and then convert natural language into charts in BI analysis for display. Therefore, the accuracy of NL2SQL conversion directly affects the accuracy and efficiency of the data displayed in the conversational analysis system. At the same time, the disclosure of this data is also of great significance to product marketing and competitive product benchmarking. However, in the process of how to effectively evaluate its accuracy, the following issues need to be solved:

[0003] (1) Effectively evaluating the accuracy of NL2SQL requires a large amount of sample data, and manual evaluation would incur huge manpower costs.

[0004] (2) The efficiency of manual evaluation of NL2SQL accuracy is very low and cannot meet the R&D department's requirements for the timeliness of accuracy data.

[0005] Therefore, how to realize the automatic evaluation of NL2SQL conversion accuracy in conversational analysis systems is a technical problem that needs to be solved urgently. Summary of the invention

[0006] The technical task of the present invention is to provide a method and system for evaluating NL2SQL accuracy in a conversational analysis system to solve the problem of how to automatically evaluate the accuracy of NL2SQL conversion in a conversational analysis system.

[0007] The technical task of the present invention is achieved in the following way: a method for evaluating the accuracy of NL2SQL in a conversational analysis system, the method is specifically as follows:

[0008] Get the URL path and login information of the system under test and the evaluation data set file required as the standard answer;

[0009] Input the questions in the standard answers into the API interface corresponding to the system under test in a loop, and standardize the returned content;

[0010] Compare and evaluate the return value of the system under test with the standard answer, and output the evaluation results.

[0011] As a preferred method, the URL path and login information of the system under test and the evaluation data set file required as the standard answer are obtained as follows:

[0012] Get the API URL, username and password entered by the user, and the selected information of the standard answer JSON file to initialize the evaluation environment information;

[0013] Select and load the JSON file corresponding to the standard answer evaluation data set, and extract the question field value in the JSON file.

[0014] As a preferred method, the questions in the standard answers are input into the API interface corresponding to the system under test in a loop, and the returned content is standardized as follows:

[0015] Call the NL2SQL API interface of the system under test according to the API URL and authentication information provided by the user, and input the question field value in the obtained standard data set in a loop to obtain the JSON data converted into SQL query statements returned by the conversational analysis system;

[0016] The JSON data of the SQL query statement is standardized to obtain the standardized SQL query statement, so that the format of the data of the system under test is consistent with the format of the standard answer; wherein, in the process of standardizing the JSON data of the SQL query statement, the aggregate field is de-aggregated to avoid the format of the data of the system under test being inconsistent with the standard answer, which affects the accuracy of the evaluation.

[0017] Preferably, the return value of the system under test is compared with the standard answer and the evaluation results are output as follows:

[0018] Compare the standardized SQL query statements with the answers to the selected standard test data set to evaluate the accuracy of the NL2SQL conversion of the tested system and obtain the evaluation results. When evaluating the accuracy of the NL2SQL conversion of the tested system, a full comparison method is used, that is, the selected fields, database tables, query conditions and aggregation methods in the SQL query statements returned by the tested system are completely consistent, and the system is considered to have passed the test, otherwise it is marked as failed.

[0019] The evaluation results are output in the form of an Excel file, and the accuracy of converting all questions in this round of testing into SQL query statements is displayed; the content of the Excel file includes Chinese questions, standard answers, evaluation system answers, and whether the test is passed.

[0020] An NL2SQL accuracy evaluation system in a conversational analysis system, the system comprising a base layer, a processing layer and an application layer, the base layer is developed using Python, the application layer uses UI components, the processing layer comprises an input module, a loading module, a data acquisition module, a data processing module, an evaluation module and a result output module;

[0021] The input module is used to receive the API URL, username and password, and the selection of the standard answer JSON file entered by the user to initialize the evaluation environment information;

[0022] The loading module is used to enable users to select and load the JSON file corresponding to the standard answer evaluation data set, and extract the question field value in the JSON file;

[0023] The data acquisition module is used to call the NL2SQL API interface of the system under test according to the API URL and authentication information provided by the user, and cyclically input the question field value in the standard data set obtained in the loading module, and obtain the JSON data converted into SQL query statements returned by the conversational analysis system;

[0024] The data processing module is used to perform standardization processing on the acquired data of the system under test so that the format of the data of the system under test is consistent with the standard answer;

[0025] The evaluation module is used to compare the standardized SQL query statements with the answers of the selected standard evaluation data set to evaluate the accuracy of the NL2SQL transformation of the system under test;

[0026] The result output module is used to output the evaluation results in the form of an Excel file and display the accuracy of converting all questions in this round of testing into SQL query statements.

[0027] Preferably, during the process of standardizing the JSON data of the SQL query statement, the aggregate fields are de-aggregated to avoid inconsistency between the format of the data of the system under test and the standard answer, which affects the accuracy of the evaluation.

[0028] Preferably, when evaluating the accuracy of the NL2SQL conversion of the system under test, a full comparison method is used, that is, the selected fields, database tables, query conditions and aggregation methods in the SQL query statement returned by the system under test are completely consistent and are considered passed, otherwise they are marked as failed.

[0029] Preferably, the content of the Excel file includes Chinese questions, standard answers, assessment system answers, and whether or not the test was passed.

[0030] An electronic device comprising: a memory and at least one processor;

[0031] Wherein, the memory stores a computer program;

[0032] The at least one processor executes the computer program stored in the memory, so that the at least one processor executes the NL2SQL accuracy evaluation method in the conversational analysis system as described above.

[0033] A computer-readable storage medium stores a computer program, and the computer program can be executed by a processor to implement the NL2SQL accuracy evaluation method in the conversational analysis system as described above.

[0034] The NL2SQL accuracy evaluation method and system in the conversational analysis system of the present invention have the following advantages:

[0035] (I) This paper mainly uses the authoritative Chinese NL2SQL evaluation dataset in the industry to evaluate the accuracy of natural language to SQL conversion of the conversational analysis (ChatBI) system. Weighing the product characteristics, this time we choose to use the first Chinese NL2SQL Challenge dataset, and its datasets are all in json file format;

[0036] (ii) The present invention mainly comprises six components: input module, loading module, data acquisition module, data processing module, evaluation module and result output module. The user only needs to copy the evaluation program to the local computer, input relevant configuration information and select the standard data set file used for evaluation to carry out accuracy evaluation;

[0037] (III) The present invention realizes automated evaluation: the accuracy of NL2SQL is evaluated through an automated process, which improves the evaluation efficiency and effectively reduces the labor cost when evaluating large amounts of data;

[0038] (iv) The present invention is user-friendly: it provides an intuitive GUI that enables users to easily configure evaluation parameters and view results;

[0039] (V) The present invention is customizable: it supports users to customize the standard answer JSON file, which increases the flexibility of the system;

[0040] (VI) The present invention improves the accuracy of NL2SQL in the conversational analysis system: the accuracy of the evaluation results is ensured through standardization processing;

[0041] (VII) The present invention can automatically evaluate the accuracy of NL2SQL conversion and support users to interactively configure parameters and analyze results during the evaluation process, filling the gap of being unable to efficiently evaluate the accuracy of NL2SQL in a conversational analysis system, and providing effective assistance for product improvement and benefit enhancement. BRIEF DESCRIPTION OF THE DRAWINGS

[0042] The present invention is further described below in conjunction with the accompanying drawings.

[0043] Attached Figure 1 This is a flowchart of the NL2SQL accuracy evaluation method in the conversational analysis system;

[0044] Attached Figure 2 This is the structural diagram of the NL2SQL accuracy evaluation system in the conversational analysis system;

[0045] Attached Figure 3 This is a screenshot of the interface of the NL2SQL accuracy evaluation system in the conversational analysis system. DETAILED DESCRIPTION

[0046] The NL2SQL accuracy evaluation method and system in the conversational analysis system of the present invention are described in detail below with reference to the accompanying drawings and specific embodiments of the specification.

[0047] Embodiment 1:

[0048] As attached Figure 1 As shown, this embodiment provides a method for evaluating the accuracy of NL2SQL in a conversational analysis system, and the method is specifically as follows:

[0049] S1. Obtain the URL path and login information of the system under test and the evaluation data set file required as the standard answer;

[0050] S2. Input the questions in the standard answers into the API interface corresponding to the system under test in a loop, and standardize the returned content;

[0051] S3. Compare and evaluate the return value of the system under test with the standard answer, and output the evaluation result.

[0052] The specific steps of obtaining the URL path and login information of the system under test and the evaluation data set file required as the standard answer in step S1 of this embodiment are as follows:

[0053] S101, obtaining the API URL, user name and password, and selected information of the standard answer JSON file input by the user, and initializing the evaluation environment information;

[0054] S102. Select and load the JSON file corresponding to the standard answer evaluation data set, and extract the question field value in the JSON file for input into the conversational analysis system under test in the next step.

[0055] In step S2 of this embodiment, the questions in the standard answers are inputted cyclically into the API interface corresponding to the system under test, and the returned content is subjected to standardization processing as follows:

[0056] S201, calling the NL2SQL API interface of the system under test according to the API URL and authentication information provided by the user, and cyclically inputting the question field value in the obtained standard data set to obtain the JSON data converted into SQL query statements returned by the conversational analysis system;

[0057] S202. Standardize the JSON data of the SQL query statement to obtain the standardized SQL query statement, so that the format of the tested system data is consistent with the format of the standard answer; it should be noted that because the tested system is a business intelligence analysis system, the converted SQL statement generally contains aggregation of measurement fields. Therefore, in the process of standardizing the JSON data of the SQL query statement, the aggregated field is de-aggregated to avoid inconsistency between the format of the tested system data and the standard answer, which affects the evaluation accuracy.

[0058] In step S3 of this embodiment, the return value of the system under test is compared and evaluated with the standard answer, and the evaluation result is output as follows:

[0059] S301, comparing the standardized SQL query statement with the answer of the selected standard evaluation data set, evaluating the accuracy of the NL2SQL conversion of the tested system, and then obtaining the evaluation result; wherein, when evaluating the accuracy of the NL2SQL conversion of the tested system, a full comparison method is used, that is, the selected fields, database tables, query conditions and aggregation methods in the SQL query statement returned by the tested system are completely consistent, and it is considered to be passed, otherwise it is marked as failed;

[0060] S302, output the evaluation results in the form of an excel file, and display the accuracy of converting all questions in this round of testing into SQL query statements; wherein the content of the excel file includes Chinese questions, standard answers, evaluation system answers, and whether the test is passed.

[0061] Embodiment 2:

[0062] As attached Figure 2 As shown, this embodiment provides an NL2SQL accuracy evaluation system in a conversational analysis system, which includes a base layer, a processing layer and an application layer. The base layer is developed using Python, the application layer uses UI components, and the processing layer includes an input module, a loading module, a data acquisition module, a data processing module, an evaluation module and a result output module;

[0063] The input module is used to receive the API URL, username and password, and the selection of the standard answer JSON file entered by the user to initialize the evaluation environment information;

[0064] The loading module is used to enable users to select and load the JSON file corresponding to the standard answer evaluation data set, and extract the question field value in the JSON file;

[0065] The data acquisition module is used to call the NL2SQL API interface of the system under test according to the API URL and authentication information provided by the user, and cyclically input the question field value in the standard data set obtained in the loading module, and obtain the JSON data converted into SQL query statements returned by the conversational analysis system;

[0066] The data processing module is used to perform standardization processing on the acquired data of the system under test so that the format of the data of the system under test is consistent with the standard answer;

[0067] The evaluation module is used to compare the standardized SQL query statements with the answers of the selected standard evaluation data set to evaluate the accuracy of the NL2SQL transformation of the system under test;

[0068] The result output module is used to output the evaluation results in the form of an Excel file and display the accuracy of converting all questions in this round of testing into SQL query statements.

[0069] In this embodiment, during the process of standardizing the JSON data of the SQL query statement, the aggregated fields are de-aggregated to avoid the format of the tested system data being inconsistent with the standard answer, which affects the accuracy of the evaluation.

[0070] In this embodiment, when evaluating the accuracy of NL2SQL conversion of the tested system, a full comparison method is used, that is, the selected fields, database tables, query conditions and aggregation methods in the SQL query statement returned by the tested system are completely consistent and it is considered to have passed, otherwise it is marked as failed.

[0071] In this embodiment, the content of the excel file includes Chinese questions, standard answers, evaluation system answers, and whether or not the answer was passed.

[0072] As attached Figure 3 As shown, an embodiment of evaluating the accuracy of NL2SQL in a conversational analysis system is as follows:

[0073] (1) Preconditions:

[0074] ① Choose a standard evaluation dataset as the standard answer, such as the first Chinese NL2SQL Challenge dataset;

[0075] ② Ensure that the system under test is a conversational analysis system developed by a software company.

[0076] (2) Evaluation process:

[0077] ①Double-click to run the tool, enter the URL of the system under test and log in;

[0078] ②Choose to load the standard answer file in JSON format;

[0079] ③ Click the "Start Evaluation" button, the system will automatically call the API interface of the system under test and input the questions in the standard answers one by one;

[0080] ④The system obtains the SQL query results returned by the system under test and performs standardized processing;

[0081] ⑤ Compare the standardized SQL query results with the standard answers and record the evaluation results of each query;

[0082] ⑥After the evaluation is completed, the system generates an evaluation report and saves it to the specified path.

[0083] (3) Evaluation results:

[0084] The evaluation report is output in the form of an Excel file, containing the following contents:

[0085] Chinese language problem;

[0086] Standard answer;

[0087] Evaluation system answers;

[0088] Passed or not;

[0089] The accuracy (precision and recall) of converting all questions into SQL in this round of testing.

[0090] Embodiment 3:

[0091] This embodiment also provides an electronic device, including: a memory and a processor;

[0092] Wherein, the memory stores computer-executable instructions;

[0093] The processor executes the computer-executable instructions stored in the memory, so that the processor executes the NL2SQL accuracy evaluation method in the conversational analysis system in any embodiment of the present invention.

[0094] The processor may be a central processing unit (CPU), or other general-purpose processors, digital signal processors (DSP), application-specific integrated circuits (ASIC), field-programmable gate arrays (FPGA) or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. The processor may be a microprocessor or any conventional processor, etc.

[0095] The memory can be used to store computer programs and / or modules. The processor realizes various functions of the electronic device by running or executing the computer programs and / or modules stored in the memory, and calling the data stored in the memory. The memory can mainly include a program storage area and a data storage area, wherein the program storage area can store an operating system, at least one application required for a function, etc.; the data storage area can store data created according to the use of the terminal, etc. In addition, the memory can also include a high-speed random access memory, and can also include a non-volatile memory, such as a hard disk, a memory, a plug-in hard disk, a smart memory card (SMC), a secure digital (SD) card, a flash memory card, at least one disk storage period, a flash memory device, or other volatile solid-state storage devices.

[0096] Embodiment 4:

[0097] This embodiment also provides a computer-readable storage medium, in which a plurality of instructions are stored, and the instructions are loaded by a processor, so that the processor executes the NL2SQL accuracy evaluation method in the conversational analysis system in any embodiment of the present invention. Specifically, a system or device equipped with a storage medium can be provided, on which a software program code that implements the functions of any of the above embodiments is stored, and a computer (or CPU or MPU) of the system or device reads and executes the program code stored in the storage medium.

[0098] In this case, the program code itself read from the storage medium can realize the function of any one of the above-mentioned embodiments, and thus the program code and the storage medium storing the program code constitute a part of the present invention.

[0099] The storage medium embodiments for providing the program code include a floppy disk, a hard disk, a magneto-optical disk, an optical disk (such as CD-ROM, CD-R, CD-RW, DVD-ROM, DVD-RYM, DVD-RW, DVD+RW), a magnetic tape, a non-volatile memory card, and a ROM. Alternatively, the program code can be downloaded from a server computer via a communication network.

[0100] In addition, it should be clear that the functions of any of the above embodiments can be implemented not only by executing the program code read by the computer, but also by enabling an operating system operating on the computer to complete part or all of the actual operations based on instructions from the program code.

[0101] In addition, it can be understood that the program code read from the storage medium is written to a memory provided in an expansion board inserted into the computer or written to a memory provided in an expansion unit connected to the computer, and then based on the instructions of the program code, a CPU installed on the expansion board or the expansion unit is enabled to perform part or all of the actual operations, thereby realizing the functions of any of the above-mentioned embodiments.

[0102] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit it. Although the present invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or replace some or all of the technical features therein with equivalents. However, these modifications or replacements do not cause the essence of the corresponding technical solutions to deviate from the scope of the technical solutions of the embodiments of the present invention.

Claims

1. A method for evaluating the accuracy of NL2SQL in a conversational analysis system, characterized in that: The method is as follows: Get the URL path and login information of the system under test and the evaluation data set file required as the standard answer; Input the questions in the standard answers into the API interface corresponding to the system under test in a loop, and standardize the returned content; Compare and evaluate the return value of the system under test with the standard answer, and output the evaluation results.

2. The NL2SQL accuracy evaluation method in the conversational analysis system according to claim 1 is characterized in that: Get the URL path and login information of the system under test and the evaluation data set file required as the standard answer as follows: Obtain the API URL, username and password entered by the user, and the selected information of the standard answer JSON file to initialize the evaluation environment information; Select and load the JSON file corresponding to the standard answer evaluation dataset, and extract the question field value in the JSON file.

3. The NL2SQL accuracy evaluation method in the conversational analysis system according to claim 1 is characterized in that: The questions in the standard answers are input into the API interface corresponding to the system under test in a loop, and the returned content is standardized as follows: Call the NL2SQL API interface of the system under test according to the API URL and authentication information provided by the user, and input the question field value in the obtained standard data set in a loop to obtain the JSON data converted into SQL query statements returned by the conversational analysis system; The JSON data of the SQL query statement is standardized to obtain the standardized SQL query statement, so that the format of the data of the system under test is consistent with the format of the standard answer; wherein, in the process of standardizing the JSON data of the SQL query statement, the aggregate field is de-aggregated.

4. The NL2SQL accuracy evaluation method in a conversational analysis system according to any one of claims 1 to 3, characterized in that: The return value of the system under test is compared with the standard answer, and the evaluation results are output as follows: Compare the standardized SQL query statements with the answers to the selected standard test data set to evaluate the accuracy of the NL2SQL conversion of the tested system and obtain the evaluation results. When evaluating the accuracy of the NL2SQL conversion of the tested system, a full comparison method is used, that is, the selected fields, database tables, query conditions and aggregation methods in the SQL query statements returned by the tested system are completely consistent, and the system is considered to have passed the test, otherwise it is marked as failed. The evaluation results are output in the form of an Excel file, and the accuracy of converting all questions in this round of testing into SQL query statements is displayed; the content of the Excel file includes Chinese questions, standard answers, evaluation system answers, and whether the test is passed.

5. A NL2SQL accuracy evaluation system in a conversational analysis system, characterized in that: The system includes a base layer, a processing layer and an application layer. The base layer is developed using Python, the application layer uses UI components, and the processing layer includes an input module, a loading module, a data acquisition module, a data processing module, an evaluation module and a result output module. The input module is used to receive the API URL, username and password, and the selection of the standard answer JSON file entered by the user to initialize the evaluation environment information; The loading module is used to enable users to select and load the JSON file corresponding to the standard answer evaluation data set, and extract the question field value in the JSON file; The data acquisition module is used to call the NL2SQL API interface of the system under test according to the API URL and authentication information provided by the user, and cyclically input the question field value in the standard data set obtained in the loading module, and obtain the JSON data converted into SQL query statements returned by the conversational analysis system; The data processing module is used to perform standardization processing on the acquired data of the system under test so that the format of the data of the system under test is consistent with the standard answer; The evaluation module is used to compare the standardized SQL query statements with the answers of the selected standard evaluation data set to evaluate the accuracy of the NL2SQL transformation of the system under test; The result output module is used to output the evaluation results in the form of an Excel file and display the accuracy of converting all questions in this round of testing into SQL query statements.

6. The NL2SQL accuracy evaluation system in the conversational analysis system according to claim 5, characterized in that: During the standardization process of the JSON data of the SQL query statement, the aggregated fields are de-aggregated.

7. The NL2SQL accuracy evaluation system in the conversational analysis system according to claim 5, characterized in that: When evaluating the accuracy of the NL2SQL conversion of the system under test, a full comparison method is used, that is, the selected fields, database tables, query conditions and aggregation methods in the SQL query statement returned by the system under test are completely consistent, and it is considered passed, otherwise it is marked as failed.

8. The NL2SQL accuracy evaluation system in the conversational analysis system according to any one of claims 5 to 7, characterized in that: The contents of the Excel file include Chinese questions, standard answers, assessment system answers, and whether or not the test was passed.

9. An electronic device, characterized in that: include: memory and at least one processor; Wherein, the memory stores a computer program; The at least one processor executes the computer program stored in the memory, so that the at least one processor executes the NL2SQL accuracy evaluation method in the conversational analysis system according to any one of claims 1 to 4.

10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a computer program, which can be executed by a processor to implement the NL2SQL accuracy evaluation method in a conversational analysis system as described in any one of claims 1 to 4.

Images