A data report generation method, device, equipment and storage medium

Abstract

The application provides a data report generation method, applied to the technical field of natural language processing. The method comprises the following steps: in response to receiving a report query instruction, obtaining a report query statement, a database and a SQL keyword; inputting the report query statement, the database and the SQL keyword into a statement conversion model to obtain a SQL statement corresponding to the report query statement, the statement conversion model comprising a semantic analysis layer, a graph coding layer and a decoding layer; querying target data matching the SQL statement in the database; and generating a target report according to the report query instruction, the SQL statement and the target data. Through the above method, the user only needs to input the report query statement, and the corresponding SQL statement can be automatically generated, thereby reducing the difficulty of data query for non-technical users, and the accuracy of SQL statement generation can be improved, thereby improving the accuracy of data query results and data reports, and facilitating data analysis by the user according to the data reports.

Description

Technical Field

[0001] This application relates to the field of natural language processing technology, and in particular to a method, apparatus, device and storage medium for generating data reports. Background Technology

[0002] Today, with the rapid development of data information technology, large amounts of data are typically stored in databases. When processing data using the internet, it is necessary to query the stored data from the database. Retrieving the desired data from the massive amounts of data in the database requires interactive operations with the database using SQL statements that computers can understand.

[0003] However, converting natural language into SQL statements that computers can recognize requires extensive technical expertise, necessitates staff training, and the accumulation of practical experience, making it relatively costly. Furthermore, manual conversion is not only inefficient but also cannot guarantee accuracy. Summary of the Invention

[0004] In view of this, embodiments of this application provide a method, apparatus, device, and storage medium for generating data reports, aiming to improve the conversion efficiency and accuracy of converting natural language into SQL statements.

[0005] In a first aspect, embodiments of this application provide a method for generating data reports, the method comprising:

[0006] In response to receiving a report query command, obtain the report query statement, database, and SQL keywords;

[0007] The report query statement, the database, and the SQL keywords are input into a statement conversion model to obtain an SQL statement corresponding to the report query statement. The statement conversion model includes a semantic parsing layer, a graph encoding layer, and a decoding layer. The semantic parsing layer is used to parse the report query statement to obtain context word vectors. The graph encoding layer is used to obtain encoding features based on the context word vectors, the database, and the SQL keywords. The decoding layer is used to obtain the SQL statement based on the encoding features.

[0008] Query the database for target data that matches the SQL statement;

[0009] Generate the target report based on the report query instruction, the SQL statement, and the target data.

[0010] Optionally, the step of converting the report query statement, the database, and the SQL keyword input statement into a model to obtain the SQL statement corresponding to the report query statement includes:

[0011] The report query statement is input into the semantic parsing layer to obtain the parsing result;

[0012] The parsing results are represented by word vectors to obtain the context word vectors output by the semantic parsing layer;

[0013] The context word vectors, the database, and the SQL keywords are input into the graph coding layer for joint encoding to obtain the encoded features output by the graph coding layer;

[0014] The encoded features are input into the decoding layer to obtain the SQL statement output by the decoding layer.

[0015] Optionally, the step of inputting the encoded features into the decoding layer to obtain the SQL statement output by the decoding layer includes:

[0016] The abstract syntax tree is generated based on the encoded features;

[0017] The abstract syntax tree is converted into the SQL statement.

[0018] Optionally, the statement transformation model further includes a graph pruning layer, which is used to reduce noise in the context word vectors and the SQL keywords using a graph pruning algorithm.

[0019] Optionally, generating the target report based on the report query instruction, the SQL statement, and the target data includes:

[0020] The report query command and the SQL statement are parsed together to obtain statistical chart display suggestions, which include displaying the target data as a bar chart, pie chart or line chart;

[0021] Based on the target data, generate the target report according to the statistical chart display suggestions.

[0022] Optionally, the method further includes:

[0023] Establish a mapping relationship between the real table names and proxy table names in the database, as well as a mapping relationship between the real field names and proxy field names in the database.

[0024] Secondly, embodiments of this application provide a data report generation apparatus, the apparatus comprising:

[0025] The acquisition module is used to respond to received report query commands and acquire the report query statement, database, and SQL keywords;

[0026] The conversion module is used to input the report query statement, the database, and the SQL keywords into a statement conversion model to obtain an SQL statement corresponding to the report query statement. The statement conversion model includes a semantic parsing layer, a graph encoding layer, and a decoding layer. The semantic parsing layer is used to parse the report query statement to obtain context word vectors. The graph encoding layer is used to obtain encoding features based on the context word vectors, the database, and the SQL keywords. The decoding layer is used to obtain the SQL statement based on the encoding features.

[0027] The query module is used to query the database for target data that matches the SQL statement;

[0028] The generation module is used to generate a target report based on the report query instruction, the SQL statement, and the target data.

[0029] Optionally, the conversion module is specifically used to input the report query statement into the semantic parsing layer to obtain the parsing result; to represent the parsing result with word vectors to obtain the context word vectors output by the semantic parsing layer; to input the context word vectors, the database, and the SQL keywords into the graph coding layer for joint encoding to obtain the encoding features output by the graph coding layer; and to input the encoding features into the decoding layer to obtain the SQL statement output by the decoding layer.

[0030] Optionally, the conversion module is specifically used to generate the abstract syntax tree based on the encoding features; and to convert the abstract syntax tree into the SQL statement.

[0031] Optionally, the statement transformation model further includes a graph pruning layer, which is used to reduce noise in the context word vectors and the SQL keywords using a graph pruning algorithm.

[0032] Optionally, the generation module is specifically used to parse the report query instruction and the SQL statement together to obtain statistical chart display suggestions, wherein the statistical chart display suggestions include displaying the target data in the form of bar charts, pie charts or line charts; and generating a target report according to the target data and the statistical chart display suggestions.

[0033] Optionally, the device further includes:

[0034] The mapping relationship establishment module is used to establish the mapping relationship between the real table name and the proxy table name of the database, as well as the mapping relationship between the real field name and the proxy field name of the database.

[0035] Thirdly, embodiments of this application provide an apparatus comprising a memory and a processor, the memory for storing instructions or code, and the processor for executing the instructions or code to cause the apparatus to perform the data report generation method described in any one of the preceding first aspects.

[0036] Fourthly, embodiments of this application provide a computer storage medium storing code, wherein when the code is executed, a device running the code implements the data report generation method described in any one of the first aspects above.

[0037] This application provides a method, apparatus, device, and storage medium for generating data reports. When executing the method, in response to receiving a report query instruction, a report query statement, database, and SQL keywords are obtained. The report query statement, database, and SQL keywords are used as input to a statement conversion model. The semantic parsing layer, graph encoding layer, and decoding layer in the statement conversion model process the report query statement, database, and SQL keywords to generate an SQL statement corresponding to the report query statement. Target data is queried from the database based on the SQL statement, and then a target report is generated based on the report query instruction, SQL statement, and target data. Through this method, users only need to input a report query statement to automatically generate the corresponding SQL statement, eliminating the need for manual statement conversion. Target data can be retrieved from the database and analyzed without manual intervention, thus improving the efficiency of statement conversion. Furthermore, the semantic parsing layer, graph encoding layer, and decoding layer in the statement conversion model jointly parse the input content, improving the accuracy of the SQL statement output by the statement conversion model, thereby improving the accuracy of data query results and data reports, facilitating data analysis by users based on the data reports. Attached Figure Description

[0038] To more clearly illustrate the technical solutions in this embodiment or the prior art, the drawings used in the description of the embodiment or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0039] Figure 1 A flowchart illustrating a method for generating a data report as provided in an embodiment of this application;

[0040] Figure 2 A schematic diagram of the statement conversion model provided in the embodiments of this application;

[0041] Figure 3 This is a schematic diagram of a data report generation device provided in an embodiment of this application. Detailed Implementation

[0042] In the era of big data, databases are increasingly used across various fields, typically accessed and queried using SQL statements. However, using SQL requires a certain level of database expertise, which can be challenging for users without relevant background knowledge. Therefore, how to perform data queries based on users' natural language queries and generate data reports has become a key issue in database usage.

[0043] To address the aforementioned technical problems, this application provides a method, apparatus, device, and storage medium for generating data reports. In response to receiving a report query instruction, the method acquires the report query statement, database, and SQL keywords. The report query statement, database, and SQL keywords are used as input to a statement conversion model. The statement conversion model processes the report query statement, database, and SQL keywords through a semantic parsing layer, a graph encoding layer, and a decoding layer to generate an SQL statement corresponding to the report query statement. Based on the SQL statement, target data is queried in the database, and then a target report is generated based on the report query instruction, SQL statement, and target data. Users only need to input the report query statement to automatically generate the corresponding SQL statement, reducing the difficulty of data querying for non-technical users. Furthermore, the statement conversion model in the above method improves the accuracy of SQL statement generation, thereby improving the accuracy of data query results and data reports, facilitating data analysis by users based on the data reports.

[0044] Obviously, the embodiments described in this application are only some embodiments, not all embodiments. All other embodiments obtained by those skilled in the art based on the embodiments in this application without inventive effort are within the scope of protection of this application.

[0045] See Figure 1 , Figure 1 A flowchart of a data report generation method provided in this application embodiment includes:

[0046] S101: In response to receiving a report query command, obtain the report query statement, database, and SQL keywords.

[0047] The report query statement is a natural language statement entered by the user, such as: query the sales volume of the Northern market in January 2022. Users can enter their query in the search bar of the query interface or speak their query via voice input. SQL keywords are used to assist in generating SQL statements and can be pre-set by staff according to actual needs. When a report query command is received, the system retrieves the user-entered report query statement, as well as the database and SQL keywords.

[0048] S102: Convert the report query statement, the database, and the SQL keyword input statement into a model to obtain the SQL statement corresponding to the report query statement.

[0049] The statement transformation model consists of a semantic parsing layer, a graph encoding layer, and a decoding layer. The semantic parsing layer parses the report query statement to obtain context word vectors; the graph encoding layer obtains encoding features based on the context word vectors, database, and SQL keywords; and the decoding layer obtains the SQL statement based on the encoding features.

[0050] After obtaining the user's input report query statement, database information, and SQL keywords, these are used as input to the statement conversion model. The model then processes this information through its semantic parsing layer, graph encoding layer, and decoding layer, converting the user's natural language input into the corresponding SQL statement.

[0051] The process of converting report query statements into SQL statements using a statement conversion model will be explained in detail below.

[0052] In one feasible implementation provided in this application, the statement transformation model further includes a graph pruning layer, which is used to denoise the context word vectors and the SQL keywords using a graph pruning algorithm.

[0053] By preprocessing context word vectors and SQL keywords using graph pruning algorithms, unimportant data can be removed, reducing computational or search complexity. By incorporating a graph pruning auxiliary task, relevant input elements can be identified, while irrelevant elements are filtered and denoised, reducing the number of parameters and computation in the statement conversion model, thus shortening conversion time and improving efficiency.

[0054] S103: Query the database for target data that matches the SQL statement.

[0055] The target data is the data the user is searching for in the database, i.e., the data the user needs. Matching target data is retrieved from the database using SQL statements.

[0056] As an example:

[0057] The SQL statement is

[0058] SELECT sum(oeder_num)

[0059] FROM di_fw.hcc

[0060] WHERE cal_number='2022-02-01'

[0061] AND hsicrm_regionname = 'Serving Small and Micro Enterprises in the Northern Market'

[0062] In this context, “di_fw.hcc” refers to the database and table name being queried, “2022-02-01” is the data corresponding to February 1, 2022 in the database time field cal_number, and “Northern Market Service Micro-enterprises” is the data corresponding to the Northern Market Service Micro-enterprises in the database region field hsicrm_regionname.

[0063] By querying the database using the SQL statement above, matching target data can be obtained.

[0064] S104: Generate a target report based on the report query instruction, the SQL statement, and the target data.

[0065] The target report includes the data queried by the user and presents the retrieved data to the user in a report format. After retrieving the target data, the report query command and the generated SQL statement are combined to generate the corresponding target report.

[0066] In one feasible implementation provided in this application, generating the target report based on the report query instruction, the SQL statement, and the target data includes:

[0067] The report query command and the SQL statement are parsed together to obtain statistical chart display suggestions, which include displaying the target data as a bar chart, pie chart or line chart;

[0068] Based on the target data, generate the target report according to the statistical chart display suggestions.

[0069] By parsing the report query commands and SQL statements, we can obtain suggestions for graphical representation of the target data. As an example, data showing trends can be displayed as a line graph, while data showing percentages can be displayed as a pie chart. Generating target reports based on these graphical representation suggestions makes it easier for users to view and analyze their target data.

[0070] This application provides a method for generating data reports. Using this method, users only need to input a report query statement to automatically generate a corresponding SQL statement. Even non-technical users can retrieve and analyze target data from the database. Furthermore, the semantic parsing layer, graph encoding layer, and decoding layer in the statement conversion model jointly parse the input content, improving the accuracy of the SQL statement output by the model. This, in turn, improves the accuracy of data query results and data reports, facilitating data analysis based on the reports.

[0071] To improve the practicality of the statement transformation model, in one feasible implementation provided in this application, the method further includes:

[0072] Establish a mapping relationship between the real table names and proxy table names in the database, as well as a mapping relationship between the real field names and proxy field names in the database.

[0073] By establishing mappings between real table names and proxy table names, as well as between real field names and proxy field names, the SQL statement can still retrieve matching target data from the database based on these mappings, even if the real table names or field names change. This eliminates the need to modify or retrain the statement transformation model, thus improving its practicality.

[0074] The following will combine Figure 2 This paper introduces the process of converting report query statements into SQL statements using a statement transformation model. Figure 2 This is a schematic diagram of the statement conversion model provided in the embodiments of this application.

[0075] In one feasible implementation provided in this application, the step of converting the report query statement, the database, and the SQL keyword input statement into a conversion model to obtain an SQL statement corresponding to the report query statement includes:

[0076] The report query statement is input into the semantic parsing layer to obtain the parsing result;

[0077] The parsing results are represented by word vectors to obtain the context word vectors output by the semantic parsing layer;

[0078] The context word vectors, the database, and the SQL keywords are input into the graph coding layer for joint encoding to obtain the encoded features output by the graph coding layer;

[0079] The encoded features are input into the decoding layer to obtain the SQL statement output by the decoding layer.

[0080] After the report query statement, database, and SQL keywords are input into the model, the report query statement is first parsed by a semantic parsing layer to obtain the parsing results. This parsing can include lexical analysis, syntactic analysis, pragmatic analysis, and contextual analysis. As an example, auxiliary tasks can be added during the parsing process to improve accuracy. After obtaining the parsing results, the semantic parsing layer represents them as word vectors to obtain context word vectors. Then, a graph encoding layer jointly encodes the context word vectors, database, and SQL keywords to obtain the corresponding encoded features. Finally, a decoding layer decodes the encoded features to obtain the SQL statement corresponding to the report query statement.

[0081] The above statement conversion model can directly generate corresponding SQL statements based on the report query statements entered by the user. Furthermore, during the conversion process, the conversion is based on the report query statements, the database, and SQL keywords, thereby improving the accuracy of SQL statement conversion.

[0082] In one feasible implementation provided in this application, the step of inputting the encoded features into the decoding layer to obtain the SQL statement output by the decoding layer includes:

[0083] The abstract syntax tree is generated based on the encoded features;

[0084] The abstract syntax tree is converted into the SQL statement.

[0085] An Abstract Syntax Tree (ABST) is a syntactic structure that represents encoded features in a tree-like format. After generating the ABST based on the encoded features, it is converted into SQL statements corresponding to report query statements. As an example, after generating the ABST, its hierarchical structure can be integrated into a Long Short-Term Memory (LSTM) artificial neural network using specific sorting neurons to represent the hierarchical structure of the ABST, and then converted into SQL statements. In this way, the encoded features can be decoded accurately and efficiently to obtain SQL statements.

[0086] The above are some specific implementations of a data report generation method provided in the embodiments of this application. Based on this, the present application also provides a corresponding apparatus. The apparatus provided in the embodiments of this application will be described below from the perspective of functional modularity.

[0087] See Figure 3 , Figure 3 This is a schematic diagram of a data report generation device provided in an embodiment of this application. The device 300 includes an acquisition module 301, a conversion module 302, a query module 303, and a generation module 304.

[0088] The acquisition module 301 is used to acquire the report query statement, database and SQL keywords in response to receiving the report query command;

[0089] The conversion module 302 is used to input the report query statement, the database, and the SQL keywords into a statement conversion model to obtain an SQL statement corresponding to the report query statement. The statement conversion model includes a semantic parsing layer, a graph encoding layer, and a decoding layer. The semantic parsing layer is used to parse the report query statement to obtain context word vectors. The graph encoding layer is used to obtain encoding features based on the context word vectors, the database, and the SQL keywords. The decoding layer is used to obtain the SQL statement based on the encoding features.

[0090] Query module 303 is used to query the database for target data that matches the SQL statement;

[0091] The generation module 304 is used to generate a target report based on the report query instruction, the SQL statement and the target data.

[0092] In one feasible implementation provided in this application, the conversion module 302 is specifically used to input the report query statement into the semantic parsing layer to obtain the parsing result; to represent the parsing result with word vectors to obtain the context word vectors output by the semantic parsing layer; to input the context word vectors, the database, and the SQL keywords into the graph coding layer for joint encoding to obtain the encoding features output by the graph coding layer; and to input the encoding features into the decoding layer to obtain the SQL statement output by the decoding layer.

[0093] In one feasible implementation provided in this application, the conversion module 302 is specifically used to generate the abstract syntax tree based on the encoding features and convert the abstract syntax tree into the SQL statement.

[0094] In one feasible implementation provided in this application, the statement transformation model further includes a graph pruning layer, which is used to denoise the context word vectors and the SQL keywords using a graph pruning algorithm.

[0095] In one feasible implementation provided in this application, the generation module 304 is specifically used to parse the report query instruction and the SQL statement together to obtain a statistical chart display suggestion. The statistical chart display suggestion includes displaying the target data in the form of a bar chart, pie chart, or line chart. Based on the target data, a target report is generated according to the statistical chart display suggestion.

[0096] In one feasible implementation provided in this application, the apparatus further includes:

[0097] The mapping relationship establishment module is used to establish the mapping relationship between the real table name and the proxy table name of the database, as well as the mapping relationship between the real field name and the proxy field name of the database.

[0098] This application provides a data report generation device. Using the above method, users only need to input a report query statement to automatically generate the corresponding SQL statement. Even non-technical users can retrieve target data from the database and perform analysis. Furthermore, the semantic parsing layer, graph encoding layer, and decoding layer in the statement conversion model jointly parse the input content, improving the accuracy of the SQL statement output by the statement conversion model, thereby improving the accuracy of data query results and data reports, and facilitating data analysis based on the data reports.

[0099] This application also provides corresponding devices and computer storage media for implementing the solutions provided in this application.

[0100] The device includes a memory and a processor. The memory is used to store instructions or code, and the processor is used to execute the instructions or code to cause the device to perform the data report generation method described in any embodiment of this application.

[0101] The computer storage medium stores code, and when the code is run, the device running the code implements the data report generation method described in any embodiment of this application.

[0102] In the embodiments of this application, the terms "first" and "second" (if they exist) are used only as name identifiers and do not represent the order of first and second.

[0103] As can be seen from the above description of the embodiments, those skilled in the art can clearly understand that all or part of the steps in the methods of the above embodiments can be implemented by means of software plus a general-purpose hardware platform. Based on this understanding, the technical solution of this application can be embodied in the form of a software product. This computer software product can be stored in a storage medium, such as a read-only memory (ROM) / RAM, magnetic disk, optical disk, etc., including several instructions to cause a computer device (which may be a personal computer, a server, or a network communication device such as a router) to execute the methods described in various embodiments or some parts of the embodiments of this application.

[0104] The various embodiments in this specification are described in a progressive manner. Similar or identical parts between embodiments can be referred to mutually. Each embodiment focuses on its differences from other embodiments. In particular, the apparatus embodiments are basically similar to the method embodiments, so the description is relatively simple; relevant parts can be referred to the descriptions in the method embodiments. Some or all of the modules can be selected to achieve the purpose of this embodiment according to actual needs. Those skilled in the art can understand and implement this without creative effort.

[0105] The above description is merely an exemplary implementation of this application and is not intended to limit the scope of protection of this application.

Claims

1. A method for generating a data report, characterized by, The method includes: In response to receiving a report query command, obtain the report query statement, database, and SQL keywords; The report query statement, the database, and the SQL keywords are input into a statement conversion model to obtain an SQL statement corresponding to the report query statement. The statement conversion model includes a semantic parsing layer, a graph encoding layer, and a decoding layer. The semantic parsing layer is used to parse the report query statement to obtain context word vectors. The graph encoding layer is used to obtain encoding features based on the context word vectors, the database, and the SQL keywords. The decoding layer is used to obtain the SQL statement based on the encoding features. Query the database for target data that matches the SQL statement; Generate the target report based on the report query instruction, the SQL statement, and the target data; The step of converting the report query statement, the database, and the SQL keyword input statement into a model to obtain the SQL statement corresponding to the report query statement includes: The report query statement is input into the semantic parsing layer to obtain the parsing result; The parsing results are represented by word vectors to obtain the context word vectors output by the semantic parsing layer; The context word vectors, the database, and the SQL keywords are input into the graph coding layer for joint encoding to obtain the encoded features output by the graph coding layer; The encoded features are input into the decoding layer to obtain the SQL statement output by the decoding layer; The step of inputting the encoded features into the decoding layer to obtain the SQL statement output by the decoding layer includes: An abstract syntax tree is generated based on the encoded features; The abstract syntax tree is converted into the SQL statement.

2. The method according to claim 1, characterized in that, The statement transformation model also includes a graph pruning layer, which is used to reduce noise in the context word vectors and the SQL keywords using a graph pruning algorithm.

3. The method according to claim 1, characterized in that, The step of generating the target report based on the report query instruction, the SQL statement, and the target data includes: The report query command and the SQL statement are parsed together to obtain statistical chart display suggestions, which include displaying the target data as a bar chart, pie chart or line chart; Based on the target data, generate the target report according to the statistical chart display suggestions.

4. The method according to claim 1, characterized in that, The method further includes: Establish a mapping relationship between the real table names and proxy table names in the database, as well as a mapping relationship between the real field names and proxy field names in the database.

5. A data report generation device, characterized in that, The device includes: The acquisition module is used to respond to received report query commands and acquire the report query statement, database, and SQL keywords; The conversion module is used to input the report query statement, the database, and the SQL keywords into a statement conversion model to obtain an SQL statement corresponding to the report query statement. The statement conversion model includes a semantic parsing layer, a graph encoding layer, and a decoding layer. The semantic parsing layer is used to parse the report query statement to obtain context word vectors. The graph encoding layer is used to obtain encoding features based on the context word vectors, the database, and the SQL keywords. The decoding layer is used to obtain the SQL statement based on the encoding features. The query module is used to query the database for target data that matches the SQL statement; The generation module is used to generate a target report based on the report query instruction, the SQL statement, and the target data; The step of converting the report query statement, the database, and the SQL keyword input statement into a model to obtain the SQL statement corresponding to the report query statement includes: The report query statement is input into the semantic parsing layer to obtain the parsing result; The parsing results are represented by word vectors to obtain the context word vectors output by the semantic parsing layer; The context word vectors, the database, and the SQL keywords are input into the graph coding layer for joint encoding to obtain the encoded features output by the graph coding layer; The encoded features are input into the decoding layer to obtain the SQL statement output by the decoding layer; The step of inputting the encoded features into the decoding layer to obtain the SQL statement output by the decoding layer includes: An abstract syntax tree is generated based on the encoded features; The abstract syntax tree is converted into the SQL statement.

6. A computer device, characterized in that, include: A memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor, when executing the computer program, implements the method for generating a data report as described in any one of claims 1-4.

7. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores instructions that, when executed on a terminal device, cause the terminal device to perform the data report generation method as described in any one of claims 1-4.

Images