Structured Query Language Generation Method, Apparatus, Electronic Device, and Storage Medium

Abstract

The present invention provides a method, apparatus, electronic device, and storage medium for generating a structured query language, belonging to the technical field of data processing. The method includes: based on a query problem, determining a target table and corresponding header information, where the query problem is a natural language query problem and the target table is a table related to the query problem; based on the query problem, retrieving the encoding information of target fields in the target table to obtain relevant encoding information, where the relevant encoding information is the encoding information related to the query problem in the encoding information of the target fields, the target fields are encoding fields related to the query problem, and the encoding fields are fields that store data in an encoded form; based on the header information and the relevant encoding information, using a large language model to generate a structured query language. By retrieving relevant encoding information and then inputting the encoding information into the large model together, the present invention can directly generate an SQL statement containing the encoding information, improving the SQL generation efficiency and reducing the error rate.

Description

Technical Field

[0001] The present invention relates to the technical field of data processing, and in particular, to a method, apparatus, electronic device, and storage medium for generating a Structured Query Language. Background Art

[0002] Intelligent data query aims to help users find data conveniently and quickly. The core module of an intelligent data query system is the Text-to-SQL module, whose function is to use an algorithm model to convert natural language into corresponding SQL (Structured Query Language) statements. In this way, users do not need to have professional SQL knowledge or manually write complex SQL statements, and can directly query the underlying database, thus greatly reducing the threshold of data query.

[0003] Currently, after the conventional Text-to-SQL method process generates an SQL statement using an algorithm model, it is necessary to manually map the encoded fields in the generated SQL statement to convert the Chinese semantics into specific digital codes, so as to finally generate an SQL query statement that can be directly executed by the database. This way of manually completing the mapping process not only has low efficiency, but also involves many mapping rules, with a large workload and is prone to errors.

[0004] Therefore, there is an urgent need for a method for generating a Structured Query Language to solve the problems of low efficiency and high error rate caused by manually completing field mapping in the prior art. Summary of the Invention

[0005] The present invention provides a method, apparatus, electronic device, and storage medium for generating a Structured Query Language to solve the defects of low efficiency and high error rate in manually completing field mapping in the prior art.

[0006] The present invention provides a method for generating a Structured Query Language, including the following steps:

[0007] Based on the query problem, determine the target table and the header information corresponding to the target table, where the query problem is a natural language query problem, and the target table is a table related to the query problem;

[0008] Based on the query problem, retrieve the encoding information of the target field in the target table to obtain relevant encoding information, where the relevant encoding information is the encoding information related to the query problem in the encoding information of the target field, the target field is an encoding field related to the query problem, and the encoding field is a field that stores data in an encoded form;

[0009] Based on the header information and the relevant encoding information, use a pre-trained large language model to generate a Structured Query Language.

[0010] A method for generating a structured query language provided by the present invention, wherein the header information includes coding identification information, and the coding identification information is used to identify whether each field in the target table is a coding field. Before determining the target table and the header information corresponding to the target table based on the query problem, it further includes:

[0011] For any coding field in the target table, store the coding information of the coding field into the vector library corresponding to the coding field, and the coding information of the coding field is the character - coding correspondence relationship of the coding field.

[0012] A method for generating a structured query language provided by the present invention, wherein retrieving the coding information of the target field in the target table based on the query problem to obtain relevant coding information includes:

[0013] Traverse each field in the target table. If the current field is a coding field, retrieve the vector library corresponding to the current field to obtain the coding information related to the query problem as the relevant coding information.

[0014] A method for generating a structured query language provided by the present invention, wherein retrieving the vector library corresponding to the current field to obtain the coding information related to the query problem as the relevant coding information includes:

[0015] Based on a preset scoring criterion, score the coding information in the vector library corresponding to the current field to obtain the correlation scores of each coding information, and the correlation scores of each coding information reflect the correlation between each coding information and the query problem;

[0016] Take the N coding information with the largest correlation scores as the relevant coding information, where N is a positive integer.

[0017] A method for generating a structured query language provided by the present invention, wherein the header information includes the table name, table annotation and table field information of the target table.

[0018] A method for generating a structured query language provided by the present invention, wherein using a pre - trained large - language model to generate a structured query language based on the header information and the relevant coding information includes:

[0019] Update the header information based on the relevant coding information;

[0020] Based on the query problem and the updated header information, use a preset prompt to generate a query statement;

[0021] Input the query statement into a pre-trained large language model to obtain the structured query language output by the large language model.

[0022] The present invention also provides a structured query language generation device, including the following modules:

[0023] A table recall module, configured to: based on the query question, determine the target table and the header information corresponding to the target table, where the query question is a natural language query question, and the target table is a table related to the query question;

[0024] An encoding recall module, configured to: based on the query question, retrieve the encoding information of the target fields in the target table to obtain relevant encoding information, where the relevant encoding information is the encoding information related to the query question in the encoding information of the target fields, the target fields are encoding fields related to the query question, and the encoding fields are fields that store data in an encoded form;

[0025] A statement generation module, configured to: based on the header information and the relevant encoding information, use a pre-trained large language model to generate a structured query language.

[0026] The present invention also provides an electronic device, including a memory, a processor, and a computer program stored on the memory and executable on the processor, where when the processor executes the computer program, it implements the structured query language generation method as described in any one of the above.

[0027] The present invention also provides a non-transitory computer-readable storage medium, on which a computer program is stored, and when the computer program is executed by a processor, it implements the structured query language generation method as described in any one of the above.

[0028] The present invention also provides a computer program product, including a computer program, and when the computer program is executed by a processor, it implements the structured query language generation method as described in any one of the above.

[0029] The structured query language generation method, device, electronic device, and storage medium provided by the present invention determine a target table and the header information corresponding to the target table based on a query problem, where the query problem is a natural language query problem, and the target table is a table related to the query problem; retrieve the coding information of the target fields in the target table based on the query problem to obtain relevant coding information, where the relevant coding information is the coding information related to the query problem in the coding information of the target fields, the target fields are coding fields related to the query problem, and the coding fields are fields that store data in a coded form; and generate a structured query language using a pre-trained large language model based on the header information and the relevant coding information. After retrieving the target table related to the query problem, the present invention further retrieves the coding information related to the query problem and inputs the coding information and the header information of the target table into the pre-trained large language model, which allows the large language model to directly generate a standard SQL statement containing the coding information based on the context information, which is concise and efficient, and there is no need to perform post-processing operations of coding mapping on the generated SQL manually, reducing the error rate of SQL statement generation. BRIEF DESCRIPTION OF THE DRAWINGS

[0030] In order to more clearly illustrate the technical solutions in the present invention or the prior art, the following will briefly introduce the drawings required for use in the description of the embodiments or the prior art. Obviously, the drawings in the following description are some embodiments of the present invention. For those of ordinary skill in the art, other drawings can be obtained based on these drawings without creative efforts.

[0031] Figure 1 is a flowchart of the structured query language generation method provided by the present invention;

[0032] Figure 2 is the coding information of the exemplary target fields provided by the present invention;

[0033] Figure 3 is a schematic structural diagram of the structured query language generation device provided by the present invention.

[0034] Figure 4 is a schematic structural diagram of the electronic device provided by the present invention. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0035] To make the objectives, technical solutions, and advantages of the present invention clearer, the following will clearly and completely describe the technical solutions in the present invention with reference to the accompanying drawings in the present invention. Obviously, the described embodiments are some, but not all, of the embodiments of the present invention. All other embodiments obtained by those of ordinary skill in the art without creative efforts based on the embodiments of the present invention fall within the protection scope of the present invention.

[0036] It should be noted that in the description of the embodiments of the present invention, the terms "include", "comprise" or any other variants thereof are intended to cover non-exclusive inclusion, so that a process, method, article or device including a series of elements not only includes those elements but also includes other elements not explicitly listed, or also includes elements inherent to such a process, method, article or device. Without further limitation, an element defined by the statement "including one..." does not exclude the existence of additional identical elements in the process, method, article or device including the said element. The orientation or positional relationship indicated by terms such as "upper", "lower", etc. is based on the orientation or positional relationship shown in the drawings, and is only for the convenience of describing the present invention and simplifying the description, rather than indicating or implying that the device or element referred to must have a specific orientation, be constructed and operated in a specific orientation, and thus cannot be construed as a limitation of the present invention. Unless otherwise clearly specified and defined, the terms "mounted", "connected" and "coupled" should be understood in a broad sense. For example, it can be a fixed connection, a detachable connection or an integral connection; it can be a mechanical connection or an electrical connection; it can be directly connected or indirectly connected through an intermediate medium, and it can be the communication inside two elements. For those of ordinary skill in the art, the specific meanings of the above terms in the present invention can be understood according to specific circumstances.

[0037] The terms "first", "second", etc. in the present invention are used to distinguish similar objects, rather than to describe a specific order or sequence. It should be understood that the data used in this way can be interchanged under appropriate circumstances, so that the embodiments of the present invention can be implemented in an order other than those illustrated or described herein, and the objects distinguished by "first", "second", etc. are usually of the same category, and do not limit the number of objects. For example, the first object can be one or multiple. In addition, "and / or" means at least one of the connected objects, and the character " / ", generally represents an "or" relationship between the associated objects before and after.

[0038] Next, in combination with Figures 1 - 4 describe the structured query language generation method, device, electronic device and storage medium provided by the embodiments of the present invention.

[0039] Figure 1 is a schematic flowchart of the structured query language generation method provided by the present invention. As Figure 1 shown, the method includes the following:

[0040] S110, based on the query problem, determine the target table and the header information corresponding to the target table;

[0041] S120, based on the query problem, retrieve the encoding information of the target field in the target table to obtain relevant encoding information;

[0042] S130. Based on the header information and relevant coding information, use a pre-trained large language model to generate a structured query language.

[0043] It should be noted that the execution entity of the structured query language generation method provided in the embodiments of the present invention can be a server or a computer device, such as a mobile phone, a tablet computer, a notebook computer, a handheld computer, a vehicle-mounted electronic device, a wearable device, an ultra-mobile personal computer (UMPC), a netbook, or a personal digital assistant (PDA), etc.

[0044] For ease of understanding, the following embodiments use the "User Owing Fee Detail Table" as an example to illustrate the structured query language generation method provided by the present invention.

[0045] The information of the User Owing Fee Detail Table nl2sql_dw_zw_awe_det_day is as follows:

[0046] CREATE TABLE `nl2sql_dw_zw_awe_det_day` (

[0047] `company_code` text COMMENT 'Power Supply Company Unit',

[0048] `cust_no` text COMMENT 'Customer Number',

[0049] `CUST_NAME` varchar(500) DEFAULT NULL COMMENT 'Customer Name',

[0050] `ec_addr` text COMMENT 'Address',

[0051] `cust_cls` text COMMENT 'Customer Type',

[0052] `User Type` varchar(30) DEFAULT NULL COMMENT 'Customer Type',

[0053] `rcvbl_ym` text COMMENT 'Receivable Year and Month',

[0054] `ec_qty` text COMMENT 'Electric Quantity',

[0055] `t_bal` text COMMENT 'Account balance',

[0056] `rcvbl_amt` text COMMENT 'Receivable electricity charge',

[0057] `rcvd_amt` text COMMENT 'Received electricity charge',

[0058] `arer_bal` text COMMENT 'Arrears',

[0059] `rcvbl_lqd_damg` text COMMENT 'Receivable liquidated damages',

[0060] `rcvd_lqd_damg` text COMMENT 'Received liquidated damages',

[0061] `arer_lqd_damg` text COMMENT 'Arrears liquidated damages',

[0062] `mobile` text COMMENT 'Contact phone number',

[0063] `cur_stop_rcvr_stat` text COMMENT 'Current status',

[0064] `Current user status` varchar(30) DEFAULT NULL COMMENT 'Current user status',

[0065] `to_date` date DEFAULT NULL COMMENT 'Statistical date',

[0066] `city_name` text COMMENT 'Name of the affiliated city company',

[0067] `county_name` text COMMENT 'Name of the affiliated county company',

[0068] `station_name` text COMMENT 'Name of the affiliated power supply station',

[0069] `mgt_org_name` text COMMENT 'Name of the management unit'

[0070] ) ENGINE=InnoDB DEFAULT CHARSET=utf8。

[0071] As described above, the first half of each sentence, `XXXX`, is the field name, and the second half, text COMMENT 'XXXXX', is the explanation corresponding to that field. For example, the field company_code represents the code of the power supply company.

[0072] Figure 2 It is the encoding information of the exemplary target field provided by the present invention. As Figure 2 shown, the exemplary encoding information of the field company_code includes the names of each power supply company and their corresponding codes.

[0073] In the specific implementation process, in the database, the data stored in the field company_code is dim_value, that is, the code of the corresponding power supply company.

[0074] In the prior art, when the user asks: "Query the actual received electricity charges of Power Supply Company B in May 2023", and asks the Text-to-SQL algorithm model, the algorithm model may generate the reply SQL: "select rcvd_amt as actual received electricity charges

[0075] from nl2sql_dw_zw_awe_det_day

[0076] where company_code = ‘Power Supply Company B’ and rcvbl_ym = ‘202305’";

[0077] That is, query the actual received electricity charges "rcvd_amt" of "Power Supply Company B" in the receivable year and month "rcvbl_ym = 202305" from the target table "nl2sql_dw_zw_awe_det_day";

[0078] As Figure 2 shown, the unit code corresponding to "Power Supply Company B" is 11112. Therefore, when actually executing the query task, it is necessary to convert some Chinese semantics in the SQL generated by the algorithm model into specific digital encodings, and the condition in the SQL should be "company_code = ‘11112’", rather than "company_code = ‘Power Supply Company B’";

[0079] Therefore, the final standard SQL is: "select rcvd_amt as actual received electricity charges

[0080] from nl2sql_dw_zw_awe_det_day

[0081] where company_code = '11112' and rcvbl_ym = '202305'".

[0082] In S110, the query question is a natural language query question, and the target table is a table related to the query question. Specifically, the query question is a question raised by the user that needs to be queried. For example, "Query the actual electricity charges of Power Supply Company B in May 2023" raised by the user is a query question. According to the query question, the most relevant table, that is, the target table, is retrieved and returned in the database. In response to the above query question, the detailed table of users in arrears (nl2sql_dw_zw_awe_det_day) and related header information are retrieved and returned, wherein the header information includes relevant information of the table, such as the table name, the field names contained in the table, the data types of each field, comments on the table, and other information.

[0083] It should be noted that the criteria for judging the relevance of a table to a query question and the method used to retrieve the most relevant table are not limited here.

[0084] In S120, the relevant coding information is coding information related to the query question in the coding information of the target field, the target field is a coding field related to the query question, and the coding field is a field that stores data in a coded form.

[0085] Still taking "query the actual electricity charges of power supply company B in May 2023" as an example, the target field is the company_code field in the user details table (nl2sql_dw_zw_awe_det_day), such as Figure 2 As shown, the coding information of the company_code field includes multiple pieces of coding information, and the coding information "B Power Supply Company: 11112" related to the query question is retrieved from the multiple pieces of coding information, which is the relevant coding information.

[0086] In some embodiments, the coding fields related to the query question are first determined, and then the coding information most related to the query question in each coding field related to the query question is further retrieved as the related coding information.

[0087] In other embodiments, each field of the target table is traversed, and a search is performed for each coding field to obtain coding information related to the query question in each coding field, and these coding information are combined as relevant coding information.

[0088] The structured query language generation method provided by the embodiments of the present invention determines a target table and the header information corresponding to the target table based on a query question, where the query question is a natural language query question, and the target table is a table related to the query question; retrieves the coding information of target fields in the target table based on the query question to obtain relevant coding information, where the relevant coding information is the coding information related to the query question in the coding information of the target fields, the target fields are coding fields related to the query question, and the coding fields are fields that store data in a coded form; and generates a structured query language using a pre-trained large language model based on the header information and the relevant coding information. After retrieving the target table related to the query question, the present invention further retrieves the coding information related to the query question, and inputs the coding information and the header information of the target table into the pre-trained large language model, which allows the large language model to directly generate a standard SQL statement containing coding information based on the context information, which is concise and efficient, and there is no need to manually perform post-processing operations on the generated SQL for coding mapping, reducing the error rate of SQL statement generation.

[0089] In an alternative embodiment, the header information includes coding identification information, which is used to identify whether each field in the target table is a coding field. Before determining the target table and the header information corresponding to the target table based on the query question, it further includes:

[0090] For any coding field in the target table, store the coding information of the coding field into the vector library corresponding to the coding field, where the coding information of the coding field is the character-coding correspondence relationship of the coding field.

[0091] Specifically, the coding identification information has two values, which can be numbers, strings, or a combination of numbers and strings. One value indicates that the field is a coding field, and the other value indicates that the field is not a coding field. For example, the coding identification information is is_code, which has two values, 1 and 0. If the is_code of a certain field is 1, it means that the field is a coding field; if the is_code of a certain field is 0, it means that the field is a non-coding field.

[0092] In the embodiments of the present invention, for each coding field, store its Chinese name and digital code into the vector library corresponding to the coding field. The name of the vector library is named after the name of the coding field, and each piece of data in the vector library represents a piece of coding information. For example, the name of the vector library corresponding to the field company_code is also company_code, and this vector library contains multiple pieces of coding information, such as:

[0093] Electric Power Company of Province A: 11111,

[0094] Power Supply Company B: 11112,

[0095] Power Supply Company C: 11113,

[0096] Power Supply Company D: 11114,

[0097] Power Supply Company E: 11115,

[0098] Power Supply Company F: 11116,

[0099] Power Supply Company G: 11117,

[0100] Power Supply Company H: 11118.

[0101] It should be noted that, in the embodiments of the present invention, by way of example, in each piece of coding information, the text and the coding number are connected by a colon ":". In the specific implementation process, the storage format of the coding information can be set according to actual usage requirements, for example, connected by a hyphen "-" or a separator "|".

[0102] The structured query language generation method provided by the embodiments of the present invention differentiates coding fields from non-coding fields through coding identification information, facilitating the quick identification of coding fields; stores the coding information of each coding field in its corresponding vector library, realizing the unified management of coding information and providing a reliable data basis for subsequent retrieval of coding information.

[0103] In an optional embodiment, retrieving the coding information of the target field in the target table based on the query problem to obtain relevant coding information includes:

[0104] Traverse each field in the target table. If the current field is a coding field, retrieve the vector library corresponding to the current field to obtain the coding information related to the query problem as the relevant coding information.

[0105] Specifically, according to the query problem, traverse each field in the target table. In this example, that is, traverse each field in the user detail table nl2sql_dw_zw_awe_det_day. If the coding identification information is_code = 0, it means the current field is a non-coding field, and continue to process the next field; if the coding identification information is_code = 1, it means the current field is a coding field. At this time, retrieve the vector library corresponding to the field name to obtain the coding information most relevant to the query problem, which is the relevant coding information. It can be understood that some coding fields are not relevant to the query problem, and for these coding fields, no coding information related to the query problem can be retrieved when retrieving the vector library.

[0106] Preferably, only for the target field, retrieve its corresponding vector library and return the coding information most relevant to the query problem.

[0107] For the structured query language generation method provided by the embodiments of the present invention, for each coding field, only retrieve the coding information related to the query problem from its corresponding vector library, reducing the data calculation amount and improving the data query efficiency.

[0108] In an alternative embodiment, retrieving the vector library corresponding to the current field to obtain coding information related to the query problem as the relevant coding information includes:

[0109] Based on a preset scoring criterion, score the coding information in the vector library corresponding to the current field to obtain the relevance scores of the respective coding information, and the relevance scores of the respective coding information reflect the relevance of the respective coding information to the query problem;

[0110] Take the N coding information with the largest relevance scores as the relevant coding information, where N is a positive integer.

[0111] In the embodiments of the present invention, query and return the N coding information most relevant to the query problem from the vector library, where N is a positive integer, and the specific value can be set according to actual usage requirements. For example, for the query problem "Please query the details of the overdue users in City B in the past 3 months", if N = 3, the top 3 coding information that may be recalled is:

[0112] B Power Supply Company: 11112,

[0113] Electric Power Company of Province A: 11111,

[0114] C Power Supply Company: 11113.

[0115] The structured query language generation method provided by the embodiments of the present invention recalls multiple coding information most relevant from the vector library, providing sufficient data basis for subsequent SQL statement generation, avoiding errors in SQL statement generation caused by the most relevant coding information obtained by calculation not being the coding information ultimately needed, and improving the fault tolerance rate of SQL statement generation.

[0116] In an alternative embodiment, retrieving the vector library corresponding to the current field to obtain coding information related to the query problem as the relevant coding information includes:

[0117] Based on a preset scoring criterion, score the coding information in the vector library corresponding to the current field to obtain the relevance scores of the respective coding information, and the relevance scores of the respective coding information reflect the relevance of the respective coding information to the query problem;

[0118] Use the encoded information with a correlation score greater than a preset threshold as the relevant encoded information.

[0119] In an alternative embodiment, the header information includes the table name, table annotation, and table field information of the target table.

[0120] Here, the table name is the naming name of the table. Still taking the user overdue details table as an example, the table name is "nl2sql_dw_zw_awe_det_day"; the table annotation is the annotation information of the table, such as "Details table of electricity bill overdue users, used to record relevant details of overdue users, counted daily"; the table field information is the relevant information of the fields in the table, including information such as "`company_code` text COMMENT 'Power supply unit', `cust_no` text COMMENT 'Customer number'" as described above.

[0121] In an alternative embodiment, generating a structured query language using a pre-trained large language model based on the header information and the relevant encoded information includes:

[0122] Update the header information based on the relevant encoded information;

[0123] Based on the query question and the updated header information, use a preset prompt to generate a query statement;

[0124] Input the query statement into the pre-trained large language model to obtain the structured query language output by the large language model.

[0125] In the embodiment of the present invention, updating the header information according to the relevant encoded information means assembling the retrieved relevant encoded information into the corresponding fields of the header information to form new header information. For example:

[0126] The retrieved relevant encoded information is: B Power Supply Company: 11112, A Provincial Electric Power Company: 11111, C Power Supply Company: 11113. Assemble this relevant encoded information into the company_code field to form the following header information: "Table name: nl2sql_dw_zw_awe_det_day

[0127] Table annotation: Details table of electricity bill overdue users, used to record relevant details of overdue users, counted daily

[0128] Table fields are as follows:

[0129] `company_code` text COMMENT "Power supply unit, enumerated values such as:

[0130] - B Power Supply Company: 11112

[0131] - Electric Power Company of Province A: 11111

[0132] - Power Supply Company C: 11113 ”,

[0133] `cust_no` text COMMENT 'Customer number',

[0134] `CUST_NAME` varchar(500) DEFAULT NULL COMMENT 'Customer name',

[0135] `ec_addr` text COMMENT 'Address',

[0136] `cust_cls` text COMMENT 'Customer type',

[0137] `Customer type` varchar(30) DEFAULT NULL COMMENT 'Customer type',

[0138] `rcvbl_ym` text COMMENT 'Receivable year and month',

[0139] `ec_qty` text COMMENT 'Electricity quantity',

[0140] `t_bal` text COMMENT 'Account balance',

[0141] `rcvbl_amt` text COMMENT 'Receivable electricity charge',

[0142] `rcvd_amt` text COMMENT 'Received electricity charge',

[0143] `arer_bal` text COMMENT 'Arrears',

[0144] `rcvbl_lqd_damg` text COMMENT 'Receivable liquidated damages',

[0145] `rcvd_lqd_damg` text COMMENT 'Received liquidated damages',

[0146] `arer_lqd_damg` text COMMENT 'Arrears liquidated damages',

[0147] `mobile` text COMMENT 'Contact phone number',

[0148] `cur_stop_rcvr_stat` text COMMENT 'Current status',

[0149] `Current user status` varchar(30) DEFAULT NULL COMMENT 'Current user status',

[0150] `to_date` date DEFAULT NULL COMMENT 'Statistical date',

[0151] `city_name` text COMMENT 'Name of the affiliated city company',

[0152] `county_name` text COMMENT 'Name of the affiliated county company',

[0153] `station_name` text COMMENT 'Name of the affiliated power supply station',

[0154] `mgt_org_name` text COMMENT 'Name of the management unit'.

[0155] In the embodiment of the present invention, the query question and the header information are recombined according to the preset prompt template to generate a complete query statement, and the complete context is input to the pre-trained large language model. The large language model generates a corresponding SQL statement based on the query question and the header information for querying the database. For example, the following query statement is generated:

[0156] As a text-to-sql model, it realizes generating accurate sql statements based on the provided table information and user questions, and supports complex nested queries and advanced mysql functions.

[0157] Table name: nl2sql_dw_zw_awe_det_day

[0158] Table comment: Detail table of electricity fee overdue users, used to record the relevant detailed information of overdue users, and statistically by day

[0159] The table fields are as follows:

[0160] `company_code` text COMMENT "Power supply unit, enumerated values such as:

[0161] - B Power Supply Company: 11112

[0162] - A Provincial Electric Power Company: 11111

[0163] - C Power Supply Company: 11113 ”,

[0164] `cust_no` text COMMENT 'Customer number',

[0165] `CUST_NAME` varchar(500) DEFAULT NULL COMMENT 'Customer name',

[0166] `ec_addr` text COMMENT 'Address',

[0167] `cust_cls` text COMMENT 'Customer type',

[0168] `User type` varchar(30) DEFAULT NULL COMMENT 'Customer type',

[0169] `rcvbl_ym` text COMMENT 'Receivable year and month',

[0170] `ec_qty` text COMMENT 'Electricity quantity',

[0171] `t_bal` text COMMENT 'Account balance',

[0172] `rcvbl_amt` text COMMENT 'Receivable electricity charge',

[0173] `rcvd_amt` text COMMENT 'Received electricity charge',

[0174] `arer_bal` text COMMENT 'Arrears',

[0175] `rcvbl_lqd_damg` text COMMENT 'Receivable liquidated damages',

[0176] `rcvd_lqd_damg` text COMMENT 'Received liquidated damages',

[0177] `arer_lqd_damg` text COMMENT 'Arrears liquidated damages',

[0178] `mobile` text COMMENT 'Contact phone number',

[0179] `cur_stop_rcvr_stat` text COMMENT 'Current status',

[0180] `Current user status` varchar(30) DEFAULT NULL COMMENT 'Current user status',

[0181] `to_date` date DEFAULT NULL COMMENT 'Statistics date',

[0182] `city_name` text COMMENT 'Name of the affiliated city company',

[0183] `county_name` text COMMENT 'Name of the affiliated county company',

[0184] `station_name` text COMMENT 'Name of the affiliated power supply station',

[0185] `mgt_org_name` text COMMENT 'Name of the management unit'"arer_bal" text COMMENT "Amount of overdue fees (yuan)"

[0186] User's question: Query the actual electricity charges of Power Supply Company B in May 2023.

[0187] The structured query language generation method provided by the embodiment of the present invention updates the retrieved relevant coding information to the header information, and assembles the query question and the updated header information into a preset input prompt, so as to accurately control the large language model to generate the expected output.

[0188] In summary, in the prior art, in the process of converting Chinese semantics into specific digital codes, corresponding mapping relationships are required. If manually constructed through key character matching, due to the large number of codes, the workload may be very large, and exact matching of strings is required. Once there are minor changes in the field condition values involved in the user's question, the codes of the key strings cannot be matched. For example, for the mapping relationship between the codes constructed through a dictionary and Chinese, when the user asks a question, the query question may have various changes. For example, for "B Power Supply Company", the user may ask "B Electric Power Company", "B City Power Supply Company", etc. Even if there are minor differences from the key values in the dictionary, the conversion cannot be successful, lacking flexibility and usability. In addition, there may be a very large number of codes for some fields (such as thousands of codes). If all the codes are written into the context and handed over to the large language model for generation, it will lead to a decrease in the accuracy of the generated SQL and too low generation efficiency. The structured query language generation method provided by the present invention recalls and assembles relevant coding information into the context in the pre-processing link of the large language model generating SQL, so that the large language model can directly generate an SQL statement containing coding information in one step, which is concise and efficient, and there is no need to consider complex post-processing mapping logic.

[0189] The structured query language generation device provided by the embodiments of the present invention will be described below. The structured query language generation device described below can be correspondingly referred to the structured query language generation method described above.

[0190] Figure 3 is a schematic structural diagram of the structured query language generation device provided by the present invention, as Figure 3 shown. The structured query language generation device may include, but is not limited to;

[0191] A table recall module 310, configured to: based on a query question, determine a target table and header information corresponding to the target table, where the query question is a natural language query question, and the target table is a table related to the query question;

[0192] A code recall module 320, configured to: based on the query question, retrieve coding information of a target field in the target table to obtain relevant coding information, where the relevant coding information is coding information related to the query question in the coding information of the target field, the target field is a coding field related to the query question, and the coding field is a field storing data in a coding form;

[0193] A statement generation module 330, configured to: based on the header information and the relevant coding information, use a pre-trained large language model to generate a structured query language.

[0194] It should be noted that the structured query language generation device provided in the embodiments of the present invention can execute the structured query language generation method described in any of the above embodiments during specific operation, and details thereof are not described in this embodiment.

[0195] Figure 4 An example of the entity structure diagram of an electronic device is shown as Figure 4 shown. The electronic device may include: a processor 410, a communication interface 420, a memory 430, and a communication bus 440. Among them, the processor 410, the communication interface 420, and the memory 430 complete communication with each other through the communication bus 440. The processor 410 can call the logical instructions in the memory 430 to execute the structured query language generation method, which includes: determining a target table and the header information corresponding to the target table based on a query problem, where the query problem is a natural language query problem, and the target table is a table related to the query problem;

[0196] retrieving the encoding information of a target field in the target table based on the query problem to obtain relevant encoding information, where the relevant encoding information is the encoding information related to the query problem in the encoding information of the target field, the target field is an encoding field related to the query problem, and the encoding field is a field that stores data in an encoded form;

[0197] generating a structured query language using a pre-trained large language model based on the header information and the relevant encoding information.

[0198] In addition, when the logical instructions in the above-mentioned memory 430 are implemented in the form of software functional units and sold or used as an independent product, they can be stored in a computer-readable storage medium. Based on such an understanding, the technical solution of the present invention, in essence, or the part that contributes to the prior art, or a part of this technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions for causing a computer device (which may be a personal computer, a server, or a network device, etc.) to execute all or part of the steps of the methods described in various embodiments of the present invention. The aforementioned storage medium includes: USB flash drives, mobile hard disks, read-only memories (ROM, Read-Only Memory), random access memories (RAM, Random Access Memory), magnetic disks, or optical discs, etc., which can store program codes.

[0199] On the other hand, the present invention also provides a computer program product, which includes a computer program that can be stored on a non-transitory computer-readable storage medium. When the computer program is executed by a processor, the computer can execute the structured query language generation method provided by the above-mentioned various methods. The method includes: based on a query problem, determining a target table and the header information corresponding to the target table, where the query problem is a natural language query problem, and the target table is a table related to the query problem;

[0200] Based on the query problem, retrieving the coding information of the target field in the target table to obtain relevant coding information, where the relevant coding information is the coding information in the coding information of the target field that is related to the query problem, the target field is a coding field related to the query problem, and the coding field is a field that stores data in a coded form;

[0201] Based on the header information and the relevant coding information, using a pre-trained large language model to generate a structured query language.

[0202] In another aspect, the present invention also provides a non-transitory computer-readable storage medium, on which a computer program is stored. When the computer program is executed by a processor, it realizes the structured query language generation method provided by the above-mentioned various methods. The method includes: based on a query problem, determining a target table and the header information corresponding to the target table, where the query problem is a natural language query problem, and the target table is a table related to the query problem;

[0203] Based on the query problem, retrieving the coding information of the target field in the target table to obtain relevant coding information, where the relevant coding information is the coding information in the coding information of the target field that is related to the query problem, the target field is a coding field related to the query problem, and the coding field is a field that stores data in a coded form;

[0204] Based on the header information and the relevant coding information, using a pre-trained large language model to generate a structured query language.

[0205] The device embodiments described above are merely illustrative. The units described as separate components may or may not be physically separated. The components shown as units may or may not be physical units, that is, they may be located in one place, or may be distributed to multiple network units. Some or all of the modules can be selected according to actual needs to achieve the purpose of the solution of this embodiment. Those of ordinary skill in the art can understand and implement it without creative labor.

[0206] Through the description of the above embodiments, those skilled in the art can clearly understand that each embodiment can be implemented by means of software plus a necessary general hardware platform, and of course, it can also be implemented by hardware. Based on such an understanding, the above technical solution, in essence, or the part that contributes to the prior art can be embodied in the form of a software product. This computer software product can be stored in a computer-readable storage medium, such as ROM / RAM, magnetic disk, optical disk, etc., and includes several instructions to enable a computer device (which can be a personal computer, a server, or a network device, etc.) to execute the methods described in each embodiment or some parts of the embodiments.

[0207] 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 them; although the present invention has been described in detail with reference to the foregoing embodiments, those of ordinary skill in the art should understand that they can still modify the technical solutions recorded in the foregoing embodiments, or perform equivalent replacements for some of the technical features; and these modifications or replacements do not make the essence of the corresponding technical solutions deviate from the spirit and scope of the technical solutions of each embodiment of the present invention.

Claims

1. A structured query language generation method, characterized in that: include: Determine a target table and table header information corresponding to the target table based on a query question, wherein the query question is a natural language query question, and the target table is a table related to the query question; Based on the query question, the coding information of the target field in the target table is retrieved to obtain relevant coding information, wherein the relevant coding information is coding information related to the query question in the coding information of the target field, the target field is a coding field related to the query question, and the coding field is a field storing data in a coded form; Based on the header information and the relevant encoding information, generate a structured query language using a pre-trained large language model; The header information includes coding identification information, and the coding identification information is used to identify whether each field in the target table is a coding field. Before determining the target table and the header information corresponding to the target table based on the query question, it also includes: For any coding field in the target table, the coding information of the coding field is stored in the vector library corresponding to the coding field, wherein the coding information of the coding field is the text-coding comparison relationship of the coding field; The step of retrieving the encoding information of the target field in the target table based on the query question to obtain relevant encoding information includes: Traversing each field in the target table, if the current field is a coding field, searching a vector library corresponding to the current field to obtain coding information related to the query question as the related coding information; The table header information includes the table name, table comments and table field information of the target table; The generating structured query language based on the header information and the related encoding information using a pre-trained large language model includes: Based on the relevant coding information, updating the header information; Based on the query question and the updated header information, a query statement is generated using a preset prompt word; The query statement is input into a pre-trained large language model to obtain a structured query language output by the large language model.

2. The structured query language generation method according to claim 1, characterized in that: The retrieving the vector library corresponding to the current field to obtain the encoding information related to the query question as the related encoding information includes: Based on a preset scoring standard, scoring the coded information in the vector library corresponding to the current field to obtain a relevance score of each coded information, where the relevance score of each coded information reflects the relevance of each coded information to the query question; The N pieces of coding information with the largest correlation scores are used as the relevant coding information, where N is a positive integer.

3. A structured query language generation device, characterized in that: The method for generating a structured query language according to claim 1 comprises: A table recall module is used to: determine a target table and table header information corresponding to the target table based on a query question, wherein the query question is a natural language query question, and the target table is a table related to the query question; A coding recall module is used to: retrieve the coding information of the target field in the target table based on the query question to obtain relevant coding information, wherein the relevant coding information is coding information related to the query question in the coding information of the target field, the target field is a coding field related to the query question, and the coding field is a field storing data in a coded form; The statement generation module is used to generate a structured query language based on the header information and the related encoding information using a pre-trained large language model.

4. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein: When the processor executes the computer program, the structured query language generation method according to any one of claims 1 to 2 is implemented.

5. A non-transitory computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the structured query language generation method according to any one of claims 1 to 2 is implemented.

6. A computer program product comprising a computer program, characterized in that When the computer program is executed by a processor, the structured query language generation method according to any one of claims 1 to 2 is implemented.

Images