Automatic retrieval query system based on SQL (Structured Query Language) statement conversion
By building a business knowledge base and automatically generating SQL statements using the knowledge retrieval module, the problem of writing SQL queries requires a lot of manual participation, and the accuracy and convenience of the query are improved.
Patent Information
- Application Number
- CN202411469855.9
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-10-21
- Publication Date
- 2025-02-07
AI Technical Summary
In the prior art, writing SQL queries requires a lot of manual participation, which is time-consuming and requires high technical knowledge and experience of operators, resulting in low automation and low efficiency.
By building a business knowledge base and business dictionary library, using knowledge retrieval modules and built-in SQL statement templates, SQL statements are automatically generated to reduce manual participation.
It improves the accuracy of retrieval, lowers the threshold for users to use databases, makes database queries more intuitive and convenient, and provides a more efficient and convenient information retrieval experience.
Smart Images
Figure CN119396853A_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the technical field of data query, and in particular to an automatic retrieval query system based on SQL statement conversion. Background Art
[0002] In the field of information technology, the importance of information retrieval is self-evident. It is the basis of data analysis and decision support. SQL (Structured Query Language), as the standard language of relational databases, provides a powerful means of data retrieval. Through SQL statements, users can accurately query, update, and manage data in the database. The writing of SQL statements requires an in-depth understanding of the database structure, and requires the writer to have certain technical knowledge and experience to ensure the accuracy and performance of the statements. However, this SQL-based retrieval method has limitations when processing unstructured data and natural language queries, and for non-technical users, the threshold is high and it is not easy to understand and use.
[0003] A major problem in the prior art is that the process of writing SQL queries requires a lot of manual participation, which is not only time-consuming, but also requires a high level of technical knowledge from the operator. When business personnel conduct data analysis, they usually need to first clarify the specific requirements of the data they need to query, including the fields to be queried, the conditions to be set, and the possible aggregation calculation methods. Then, data engineers or data analysts need to write corresponding SQL statements based on these requirements. This process takes a lot of time and energy, and requires a high level of technical knowledge and experience from the operator, otherwise the written SQL statements may be inappropriate, which in turn affects subsequent data queries.
[0004] In view of this, a system for automatic retrieval query based on SQL statement conversion is needed. Summary of the invention
[0005] In view of the prior art, writing SQL queries during data analysis requires a lot of manual participation. This process is time-consuming and has high requirements on the technical level and experience of the operators, resulting in low automation and low efficiency. The present invention provides an automatic retrieval query system based on SQL statement conversion, which can automatically generate SQL statements by building a business knowledge base and a business dictionary, thereby reducing the need for manual participation. It generates SQL statements based on the data in the first database through the knowledge retrieval module and the built-in SQL statement template. This method not only improves the accuracy of retrieval, but also greatly reduces the threshold for users to use the database, making database queries more intuitive and convenient. In this way, the automated search intent recognition method can effectively make up for the shortcomings of traditional SQL retrieval methods and provide users with a more efficient and convenient information retrieval experience. The specific technical solution is as follows:
[0006] A system for automatic retrieval and query based on SQL statement conversion, comprising:
[0007] Data acquisition module, used for collecting raw data;
[0008] A first database, connected to the data acquisition module, for storing a data dictionary and a data structure table, wherein the data dictionary is for storing professional terms and synonyms and abbreviations of the professional terms;
[0009] A knowledge retrieval module is connected to the first database and generates SQL statements based on the data in the first database. The knowledge retrieval module includes an SQL statement template module, and the SQL statement template module has several SQL statement generation templates built in.
[0010] An SQL retrieval module, which is connected to the knowledge retrieval module to obtain the SQL statement output by the knowledge retrieval module, and is also connected to a second database to perform a search in the second database based on the SQL statement, thereby obtaining a search result and transmitting it to the terminal;
[0011] The second database is used to store data for retrieval by SQL statements;
[0012] The terminal is connected with the data acquisition module and the SQL retrieval module, and is used to transmit data to the data acquisition module and receive the retrieval results output by the SQL retrieval module.
[0013] Preferably, the data collection module collects all documents, reports, and manuals related to power grid business, and the specific contents collected include the operation process of the power grid, the maintenance manual of the power equipment, and the analysis report of the power market.
[0014] Preferably, the construction of the data structure table first requires defining the data storage structure, and then defining the data storage rules, wherein the data storage rules include mandatory items and non-mandatory items in the data storage structure, and finally, based on the collected original data and the defined data storage rules, the corresponding part of the original data is stored in the corresponding storage structure.
[0015] Preferably, the storage structure includes: entity, relationship, time, result, id, type, task, previous task node of the current task, and next task of the current task.
[0016] Preferably, the identification and retrieval module also includes a segmentation module and a statement generation module, which segments the original data collected by the data collection module. The segmentation processing module segments the collected data. The statement generation module extracts the segmented data segmented by the segmentation module, and retrieves the data structure contained in each segmented data according to keywords, and then stores the retrieved keywords in the corresponding data structure in the first database to form a data item. After the data item is generated, the SQL statement template module selects the corresponding SQL statement generation template according to the type of data structure included in the data item, and then generates the corresponding SQL statement.
[0017] Preferably, the segmentation method of the segmentation processing module includes segmentation using a period, a semicolon, a question mark or a carriage return character, and the segmentation method is used alone or in combination during segmentation.
[0018] Preferably, the construction process of the second database is as follows:
[0019] Determine the table structure, fields, data types, keys, and relationships between tables;
[0020] Use a database management system (DBMS) to create databases and tables, specifically: define the name and other initial parameters of the database, create tables according to the design pattern, and define column data types, keys, and constraints;
[0021] Insert data into the database;
[0022] Create indexes on certain columns in the database.
[0023] Preferably, the data is inserted into the database in the following ways: manually inputting, batch importing using CSV files, or interacting with an existing database by building an interface.
[0024] A computer-readable storage medium includes a stored program, wherein when the program is executed, the device where the computer-readable storage medium is located is controlled to execute the automatic retrieval query system based on SQL statement conversion as described above.
[0025] A processor is used to run a program, wherein when the program is run, the automatic retrieval query system based on SQL statement conversion as described above is executed.
[0026] Compared with the prior art, the present invention has the following beneficial effects:
[0027] The present invention automatically generates SQL statements by constructing a business knowledge base and a business dictionary, which greatly reduces the threshold for users to use the database. The convenience of this integrated system lies in that it can understand and convert natural language queries, so that non-technical users can also easily perform database queries. It generates SQL statements based on the data in the first database through the knowledge retrieval module and the built-in SQL statement template. This method not only improves the accuracy of retrieval, but also greatly reduces the threshold for users to use the database, making database queries more intuitive and convenient. In this way, the automated search intent recognition method can effectively make up for the shortcomings of traditional SQL retrieval methods and provide users with a more efficient and convenient information retrieval experience. In addition, the advantages of this integrated system are also reflected in its ability to provide a friendly user interface, support rich query syntax, automatic completion, error correction and other functions, and visually display retrieval results to enhance user experience. BRIEF DESCRIPTION OF THE DRAWINGS
[0028] In order to more clearly illustrate the specific embodiments of the present invention or the technical solutions in the prior art, the following is a brief introduction to the drawings required for the specific embodiments or the prior art description. In all the drawings, similar elements or parts are generally identified by similar reference numerals. In the drawings, the elements or parts are not necessarily drawn according to the actual scale.
[0029] Figure 1 It is the system principle diagram of the present invention;
[0030] Figure 2 A flow chart of the method for constructing the second database of the present invention. DETAILED DESCRIPTION
[0031] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.
[0032] It should be understood that when used in this specification and the appended claims, the terms "include" and "comprises" indicate the presence of described features, integers, steps, operations, elements and / or components, but do not exclude the presence or addition of one or more other features, integers, steps, operations, elements, components and / or combinations thereof.
[0033] It should also be understood that the terms used in the present specification are only for the purpose of describing specific embodiments and are not intended to limit the present invention. As used in the present specification and the appended claims, unless the context clearly indicates otherwise, the singular forms "a", "an" and "the" are intended to include plural forms.
[0034] It should be further understood that the term "and / or" used in the present description and the appended claims refers to any and all possible combinations of one or more of the associated listed items, and includes these combinations.
[0035] In one embodiment of the present invention, a system for automatic retrieval query based on SQL statement conversion is provided. Figure 1 As shown, including:
[0036] The data acquisition module is used to collect raw data. The data acquisition module collects and organizes all documents, reports, manuals and other materials related to the power grid business. These materials may include the operation process of the power grid, maintenance manuals of power equipment, analysis reports on the power market, etc.
[0037] The first database is used to store a data dictionary and a data structure table. The data dictionary is used to store professional terms and synonyms and abbreviations of professional terms. Professional terms and synonyms and abbreviations of professional terms are given by business personnel or industry experts. They are reference tools for expert opinions and can be set according to specific scenarios. The construction of the data structure table first requires the definition of the data storage structure, and then the definition of the data storage rules. The data storage rules include mandatory items and non-mandatory items in the data storage structure. Finally, based on the collected original data and the defined data storage rules, the corresponding parts of the original data are stored in the corresponding storage structure. The storage structure includes: entity, relationship, time, result, id, type, task, previous task node of the current task, next task of the current task, etc. In the collected data, a column of data stored according to the data storage structure is a data item. (That is, if the data storage structure is arranged in a table, one row of the table is a data item).
[0038] The knowledge retrieval module includes an SQL statement template module, wherein the SQL statement template module has several SQL statement generation templates built in, wherein the SQL statement generation template includes a fixed template part and an active template part, wherein the fixed template part is an immutable SQL command statement, and the active template part is a changeable SQL statement, specifically a data structure type in a storage structure, including entity, relationship, time, result, id, type, task, previous task node of the current task, next task of the current task, etc. The knowledge retrieval module is connected to the data acquisition module, and the knowledge retrieval module also includes a segmentation module and a statement generation module, which performs segmentation processing on the original data collected by the data acquisition module. The segmentation processing described here can be simply segmented by a period or semicolon, a question mark or a carriage return, etc. The specific segmentation method is used flexibly according to the actual operation, and can be used alone or in combination. The statement generation module extracts the segmented data divided by the segmentation module, and searches for entities, relationships, time, results, ids, types, tasks, previous task nodes of the current task, next tasks of the current task, etc. contained in each segmented data according to keywords, and then stores the retrieved keywords in the corresponding data structure in the first database to generate data items. That is, each segment of data output by the segmentation module corresponds to a data item. After the data items are generated, the SQL statement template module selects the corresponding SQL statement generation template according to the type of data structure included in the data item, and then generates the corresponding SQL statement.
[0039] The SQL retrieval module is connected to the knowledge retrieval module to obtain the SQL statement output by the knowledge retrieval module, and the SQL retrieval module is also connected to the second database to search the second database based on the SQL statement, and then obtain the search results and transmit them to the terminal.
[0040] The second database is used to store data for retrieval by SQL statements.
[0041] like Figure 2 As shown, the construction process of the second database is as follows:
[0042] 1. Determine the table structure, fields (columns), data types, keys (primary keys, foreign keys), and relationships between tables. Create an ER diagram to represent entities (tables) and the relationships between them;
[0043] Here are some examples of table structures:
[0044] User table (user ID, name, address, meter number, etc.)
[0045] Electricity usage data table (record ID, user ID, date, time, electricity consumption, etc.)
[0046] Billing data table (record ID, user ID, billing cycle, fee, etc.)
[0047] 2. Use a database management system (DBMS) such as MySQL, PostgreSQL, Oracle, or SQL Server to create a database and table. The main process is: define the name of the database and other initial parameters, create a table according to the design pattern, and define the data type, key, and constraints of the column (such as primary key, foreign key, uniqueness, non-null, etc.)
[0048] 3. Insert the data into the database. This can be done by manual entry, bulk import (such as using a CSV file), or through the interaction of the application with the database.
[0049] 4. Create indexes on certain columns of the database, especially those that are frequently used for searching and sorting.
[0050] Next, a specific example will be used to illustrate the process of implementing SQL query by cooperating with the above modules.
[0051] Suppose the user wants to know which region had the highest electricity consumption last month. To do this, the system generates an SQL query statement that aims to query the total electricity consumption grouped by region within a specific time period, sort by electricity consumption in descending order, and finally select the region with the highest electricity consumption.
[0052] If the user asks “Which region had the highest electricity consumption last month?”, the knowledge retrieval module performs the following steps:
[0053] Understand the query: Identify key information, such as "last month" refers to the time range, "which region" refers to the geographical location, and "highest electricity consumption" refers to the maximum value of electricity consumption.
[0054] Retrieve database schema: Find the table (e.g., "Electricity Usage Data Table") and columns (e.g., "Region", "Electricity Usage", "Date") that contain relevant data.
[0055] Retrieve data: Retrieve electricity consumption data for each region in the last month from the database.
[0056] Output search results: Output data and metadata related to the query. This information will be used to generate SQL statements.
[0057] The output of the knowledge retrieval module is not the final SQL statement, but the data and information used to construct the SQL statement. The final SQL statement is generated by other modules of the system (such as the SQL generation module) based on the context information provided by the knowledge retrieval module.
[0058] For example, a knowledge retrieval module might output the following information:
[0059] Related table name: electricity_usage_data
[0060] Relevant column names: region,electricity_usage,date
[0061] Data from last month: electricity consumption and date information by region
[0062] The SQL generation module then uses this information to build and output the final SQL query statement, as shown below:
[0063]
[0064] The meaning of this SQL statement is:
[0065] SELECT region, SUM(electricity_usage) AS total_usage: Select the region column, calculate the sum of electricity_usage for each region, and name this sum total_usage.
[0066] FROM electricity_usage_data: specifies that the data table to be queried is electricity_usage_data.
[0067] WHERE date BETWEEN 'last month's start date' AND 'last month's end date': Filters out records whose dates are between the start and end of last month.
[0068] GROUP BY region: Group the results by the region column, so that the total electricity consumption of each region can be obtained.
[0069] ORDER BY total_usage DESC: Sort the results by the total_usage column in descending order, so that the areas with the highest electricity usage are ranked first.
[0070] LIMIT 1: Limit the results to return only one record, that is, the area with the highest electricity consumption.
[0071] When this SQL statement is sent to the database for execution, the database will return the query results that meet the conditions. In this example, the query results may be as follows:
[0072] region total_usage Eastern District 50 million kWh ... ...
[0073] Here, the "Eastern District" is the area with the highest electricity consumption, with a total electricity consumption of 50 million kWh last month. This result will then be returned to the user, and the user can directly see the information they asked for.
[0074] In summary, the present invention automatically generates SQL statements by constructing a business knowledge base and a business dictionary, which greatly reduces the threshold for users to use the database. The convenience of this integrated system lies in that it can understand and convert natural language queries, so that non-technical users can also easily perform database queries. It generates SQL statements based on the data in the first database through the knowledge retrieval module and the built-in SQL statement template. This method not only improves the accuracy of retrieval, but also greatly reduces the threshold for users to use the database, making database queries more intuitive and convenient. In this way, the automated search intent recognition method can effectively make up for the shortcomings of traditional SQL retrieval methods and provide users with a more efficient and convenient information retrieval experience. In addition, the advantages of this integrated system are also reflected in its ability to provide a friendly user interface, support rich query syntax, automatic completion, error correction and other functions, and visually display retrieval results to enhance user experience.
[0075] Those of ordinary skill in the art will appreciate that the units of each example described in conjunction with the embodiments disclosed herein can be implemented in electronic hardware, computer software, or a combination of the two. In order to clearly illustrate the interchangeability of hardware and software, the composition of each example has been generally described in terms of function in the above description. Whether these functions are performed in hardware or software depends on the specific application and design constraints of the technical solution. Professional and technical personnel can use different methods to implement the described functions for each specific application, but such implementation should not be considered to be beyond the scope of the present invention.
[0076] In the embodiments provided by the present invention, it should be understood that the division of units is only a logical function division, and there may be other division methods in actual implementation, for example, multiple units can be combined into one unit, one unit can be split into multiple units, or some features can be ignored, etc.
[0077] In addition, each functional unit in each embodiment of the present invention may be integrated into one processing unit, or each unit may exist physically separately, or two or more units may be integrated into one unit. The above-mentioned integrated unit may be implemented in the form of hardware or in the form of software functional units.
[0078] If the integrated unit is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present invention, in essence, or the part that contributes to the prior art, or all or part of the technical solution can be embodied in the form of a software product, which is stored in a storage medium and includes several instructions for a computer device (which can be a personal computer, a server or a network device, etc.) to perform all or part of the steps of the method described in each embodiment of the present invention. The aforementioned storage medium includes: U disk, read-only memory (ROM, Read-0nlyMemory), random access memory (RAM, RandomAccessMemory), mobile hard disk, magnetic disk or optical disk, etc., which can store program code.
[0079] 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 by equivalents. These modifications or replacements do not make the essence of the corresponding technical solutions deviate from the scope of the technical solutions of the embodiments of the present invention, and they should all be included in the scope of the claims and specification of the present invention.
Claims
1. A system for automatic retrieval and query based on SQL statement conversion, characterized in that: include: Data acquisition module, used for collecting raw data; A first database, connected to the data acquisition module, is used to store a data dictionary and a data structure table, wherein the data dictionary is used to store professional terms and synonyms and abbreviations of the professional terms; A knowledge retrieval module is connected to the first database and generates SQL statements based on the data in the first database. The knowledge retrieval module includes an SQL statement template module, and the SQL statement template module has several SQL statement generation templates built in. An SQL retrieval module, which is connected to the knowledge retrieval module to obtain the SQL statement output by the knowledge retrieval module, and is also connected to a second database to perform a search in the second database based on the SQL statement, thereby obtaining a search result and transmitting it to the terminal; The second database is used to store data for retrieval by SQL statements; The terminal is connected with the data acquisition module and the SQL retrieval module, and is used to transmit data to the data acquisition module and receive the retrieval results output by the SQL retrieval module.
2. The automatic search and query system based on SQL statement conversion according to claim 1 is characterized in that: The data collection module collects all documents, reports, and manuals related to the power grid business. The specific contents collected include the operation process of the power grid, the maintenance manual of the power equipment, and the analysis report of the power market.
3. The automatic search and query system based on SQL statement conversion according to claim 1 is characterized in that: The construction of the data structure table first requires defining the data storage structure, and then defining the data storage rules. The data storage rules include mandatory items and non-mandatory items in the data storage structure. Finally, based on the collected original data and the defined data storage rules, the corresponding part of the original data is stored in the corresponding storage structure.
4. The automatic search and query system based on SQL statement conversion according to claim 3 is characterized in that: The storage structure includes: entity, relationship, time, result, id, type, task, previous task node of the current task, and next task of the current task.
5. The automatic search and query system based on SQL statement conversion according to claim 1 is characterized in that: The identification retrieval module also includes a segmentation module and a statement generation module, which segments the original data collected by the data collection module. The segmentation processing module segments the collected data. The statement generation module extracts the segmented data segmented by the segmentation module, and retrieves the data structure contained in each segmented data according to keywords, and then stores the retrieved keywords in the corresponding data structure in the first database to form a data item. After the data item is generated, the SQL statement template module selects the corresponding SQL statement generation template according to the type of data structure included in the data item, and then generates the corresponding SQL statement.
6. The automatic search and query system based on SQL statement conversion according to claim 5 is characterized in that: The segmentation method of the segmentation processing module includes segmentation using a period, a semicolon, a question mark or a carriage return character. The segmentation method is used alone or in combination during segmentation.
7. The automatic search and query system based on SQL statement conversion according to claim 1 is characterized in that: The construction process of the second database is as follows: Determine the table structure, fields, data types, keys, and relationships between tables; Use a database management system (DBMS) to create databases and tables, specifically: define the name and other initial parameters of the database, create tables according to the design pattern, and define column data types, keys, and constraints; Insert data into the database; Create indexes on certain columns in the database.
8. The automatic search and query system based on SQL statement conversion according to claim 7 is characterized in that: Data can be inserted into a database by manual entry, batch import using CSV files, or by interacting with an existing database by building an interface.
9. A computer-readable storage medium, characterized in that: The computer-readable storage medium includes a stored program, wherein when the program is executed, the device where the computer-readable storage medium is located is controlled to execute the system for automatic retrieval query based on SQL statement conversion as described in any one of claims 1 to 8.
10. A processor, characterized in that: The processor is used to run a program, wherein the program, when running, executes the system for automatic retrieval query based on SQL statement conversion as described in any one of claims 1 to 8.