SQL (Structured Query Language) statement generation method, data analysis method and related device

Abstract

The invention discloses an SQL (Structured Query Language) statement generation method, a data analysis method and a related device, and relates to the field of data analysis, the SQL statement generation method comprises the following steps: obtaining a target demand description text of a user; according to pre-obtained data table description information, determining a table name of a data table related to the target demand description text to obtain a target table name; determining an index item related to the target demand description text from index items contained in description information of a data table corresponding to the target table name to obtain a target index item; and generating an SQL statement by utilizing the large model according to the target demand description text, the target table name and the target index item. According to the SQL statement generation method disclosed by the invention, the SQL statement can be automatically generated according to the query or analysis requirement described by the natural language, and a user does not need to have relatively strong professional skills, so that the labor cost is reduced, and meanwhile, the time consumption for obtaining the SQL statement is reduced.

Description

Technical Field

[0001] The present application relates to the field of data analysis technology, and in particular to a SQL statement generation method, a data analysis method and related devices. Background Art

[0002] In some fields with data analysis needs (such as business intelligence), it is necessary to obtain SQL statements based on the user's query or analysis needs, and then operate the database based on the SQL statements to obtain query or analysis results.

[0003] Currently, the way to obtain SQL statements is that users manually write SQL statements according to query or analysis requirements. However, manually writing SQL statements requires strong professional skills (that is, professional data analysts are required), the labor cost is high, and manually writing SQL statements is time-consuming and inefficient. Summary of the invention

[0004] In view of this, the present application provides a SQL statement generation method, a data analysis method and related devices, which are used to solve the problems of high labor cost and long time consumption caused by manual writing of SQL query statements. The technical solution is as follows:

[0005] The first aspect of the present application provides a method for generating an SQL statement, comprising:

[0006] Obtaining a target requirement description text of a user, wherein the target requirement description text is a query or analysis requirement described in natural language;

[0007] According to the pre-acquired data table description information, determine the table name of the data table related to the target demand description text, and obtain the target table name, wherein the data table description information includes the description information of each data table in the database, and the description information of any data table at least includes the metadata of the data table, and the metadata of any data table includes the table name and field information of the data table, and the field information of any data table includes the indicator items in the data table;

[0008] Determine the indicator items related to the target requirement description text from the indicator items included in the description information of the data table corresponding to the target table name, and obtain the target indicator item;

[0009] Using the big model, an SQL statement is generated according to the target requirement description text, the target table name and the target indicator item.

[0010] In a possible implementation, obtaining the user's target requirement description text includes:

[0011] Get the requirement description text entered by the user;

[0012] Using the big model, identifying the intention of the requirement description text input by the user;

[0013] If the intention of the requirement description text input by the user is a data query or analysis intention related to a specified field, the requirement description text input by the user is determined as a target requirement description text.

[0014] In a possible implementation, the SQL statement generation method further includes:

[0015] Retrieving domain knowledge related to the target requirement description text in a domain knowledge base of a specified domain;

[0016] The domain knowledge related to the target requirement description text is spliced ​​with the target requirement description text, and the spliced ​​text is used as the final target requirement description text.

[0017] In a possible implementation manner, the description information of any data table further includes: the first N rows of data in the data table, where N is an integer greater than or equal to 1.

[0018] In a possible implementation, determining the table name of the data table related to the target requirement description text according to the pre-acquired data table description information to obtain the target table name includes:

[0019] By using the large model, the description information of the data table related to the target requirement description text is filtered from the data table description information to obtain the table name of the data table related to the target requirement description text as the target table name.

[0020] In a possible implementation, filtering the description information of the data table related to the target requirement description text from the data table description information includes:

[0021] Randomly select description information of some data tables from the data table description information;

[0022] Using the large model, from the description information of the randomly selected data tables, the description information of n data tables that are most relevant to the target requirement description text is screened out, where n is an integer greater than or equal to 1;

[0023] Repeating the steps of randomly selecting description information of a portion of data tables from the description information of the data tables, and using the large model to filter out description information of n data tables that are most relevant to the target requirement description text from the description information of the randomly selected data tables, until a preset number of filtering times is reached;

[0024] By using the large model, the description information of k data tables most relevant to the target requirement description text is further screened out from the description information of all the screened data tables, where k is an integer greater than or equal to 1.

[0025] In a possible implementation, the use of the large model to screen out the description information of n data tables that are most relevant to the target requirement description text from the description information of randomly selected data tables includes:

[0026] Obtain a preconfigured first instruction prompt template, wherein the first instruction prompt template includes a data table description information slot and a requirement description information slot, and the first instruction prompt template is used to prompt the large model to filter out description information of n data tables that are most relevant to the information in the requirement description information slot from the information in the data table description information slot;

[0027] Filling the description information of the randomly selected data table into the data table description information slot, and filling the target requirement description text into the requirement description information slot, to obtain a first prompt instruction;

[0028] The first prompt instruction is input into the large model to obtain description information of n data tables output by the large model that are most relevant to the target demand description text.

[0029] In a possible implementation, determining the indicator items related to the target requirement description text from the indicator items included in the description information of the data table corresponding to the target table name to obtain the target indicator items includes:

[0030] By matching the words included in the target requirement description text with the indicator items included in the description information of the data table corresponding to the target table name, the indicator items related to the target requirement description text are determined to obtain a plurality of target indicator items;

[0031] For each target indicator item obtained, if the non-target indicator items contained in the description information of the data table where the target indicator item is located include the parent indicator item or child indicator item of the target indicator item, the parent indicator item or child indicator item of the target indicator item is determined as the target indicator item.

[0032] In a possible implementation, the use of the big model to generate an SQL statement according to the target requirement description text, the target table name, and the target indicator item includes:

[0033] Get the data table corresponding to the target table name;

[0034] A temporary table is constructed according to the data table corresponding to the target table name and the target indicator item, wherein the temporary table includes the target indicator item, the dimension items in the data table corresponding to the target table name, and the data of the target indicator item in the first N rows of data in the data table corresponding to the target table name, where N is an integer greater than or equal to 1;

[0035] Using the big model, an SQL statement is generated based on the temporary table and the target requirement description text.

[0036] In a possible implementation, the use of the large model to generate SQL statements based on the temporary table and the target requirement description text includes:

[0037] Obtain the table structure and first N rows of data of the temporary table, and obtain a preconfigured second instruction prompt template, wherein the second instruction prompt template includes a temporary table information slot and a requirement description information slot, and the second instruction prompt template is used to prompt the large model to generate an SQL statement according to the information in the temporary table information slot and the information in the requirement description information slot;

[0038] Fill the table structure and the first N rows of data of the temporary table into the temporary table information slot, and fill the target requirement description text into the requirement description information slot to obtain a second prompt instruction;

[0039] The second prompt instruction is input into the large model to obtain the SQL statement output by the large model.

[0040] A second aspect of the present application provides a data analysis method, comprising:

[0041] Generate an SQL statement by using any of the above-mentioned SQL statement generation methods;

[0042] Calling the interface connected to the database to execute the SQL statement to obtain the query or analysis result;

[0043] The query or analysis result is directly output, or the query or analysis result is processed into a chart for output.

[0044] The third aspect of the present application provides a SQL statement generation device, including: a requirement description text acquisition module, a related data table determination module, a related indicator item determination module and an SQL statement generation module;

[0045] The requirement description text acquisition module is used to acquire the user's target requirement description text, wherein the target requirement description text is a query or analysis requirement described in natural language;

[0046] The related data table determination module is used to determine the table name of the data table related to the target demand description text according to the pre-acquired data table description information, and obtain the target table name, wherein the data table description information includes the description information of each data table in the database, the description information of any data table at least includes the metadata of the data table, the metadata of any data table includes the table name and field information of the data table, and the field information of any data table includes the indicator items in the data table;

[0047] The related indicator item determination module is used to determine the indicator items related to the target requirement description text from the indicator items contained in the description information of the data table corresponding to the target table name, and obtain the target indicator items;

[0048] The SQL statement generation module is used to generate SQL statements using a large model according to the target requirement description text, the target table name and the target indicator items.

[0049] A fourth aspect of the present application provides an electronic device, comprising at least one processor and a memory connected to the processor, wherein:

[0050] The memory is used to store computer programs;

[0051] The processor is used to execute the computer program so that the electronic device can implement the steps of any one of the above-mentioned SQL statement generation methods.

[0052] In a fifth aspect, the present application provides a computer storage medium, which carries one or more computer programs. When the one or more computer programs are executed by an electronic device, the electronic device can implement the steps of any one of the above-mentioned SQL statement generation methods.

[0053] A sixth aspect of the present application provides a computer program product, comprising computer-readable instructions, which, when executed on an electronic device, enables the electronic device to implement the steps of any one of the above-described SQL statement generation methods.

[0054] By means of the above technical scheme, the SQL statement generation method provided by the present application first obtains the target demand description text of the user, and then determines the table name of the data table related to the target demand description text according to the pre-acquired data table description information, and then determines the indicator items related to the target demand description text from the indicator items contained in the description information of the data table corresponding to the target table name, obtains the target indicator items, and finally uses the large model to generate SQL statements according to the target demand description text, the target table name and the target indicator items. The SQL statement generation method provided by the present application can automatically generate SQL statements according to the query or analysis requirements described by natural language. Compared with manually writing SQL statements, users do not need to have strong professional skills (users only need to describe the query or analysis requirements through natural language), thereby reducing labor costs and greatly reducing the time consumption of obtaining SQL statements. On the basis of the SQL statement generation method provided by the present application, the present application also provides a data analysis method, which can use a large model to automatically generate SQL statements and reports, realize the full process automation from natural language input to SQL query and report generation, significantly reduce the dependence on professional skills, thereby reducing labor costs, and at the same time, greatly improve data analysis efficiency. BRIEF DESCRIPTION OF THE DRAWINGS

[0055] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are only embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on the provided drawings without paying creative work.

[0056] Figure 1 A schematic diagram of a system architecture involved in this application;

[0057] Figure 2 A schematic diagram of a hardware structure of a terminal provided in an embodiment of the present application;

[0058] Figure 3 A schematic diagram of a hardware structure of a server provided in an embodiment of the present application;

[0059] Figure 4 A flowchart of a method for generating a SQL statement provided in an embodiment of the present application;

[0060] Figure 5 A schematic diagram of using a large model to filter data table description information related to a target requirement description text from pre-acquired data table description information provided in an embodiment of the present application;

[0061] Figure 6An example of a data table (source table) corresponding to a target table name provided in an embodiment of the present application;

[0062] Figure 7 An example of a temporary table constructed according to a data table and target index items corresponding to a target table name provided in an embodiment of the present application;

[0063] Figure 8 A schematic diagram of the structure of a SQL statement generation device provided in an embodiment of the present application;

[0064] Fig. 9 A schematic diagram of the structure of a data analysis system provided in an embodiment of the present application. DETAILED DESCRIPTION

[0065] The following describes the embodiments of the present application in conjunction with the drawings in the embodiments of the present application. The terms used in the implementation method section of the present application are only used to explain the specific embodiments of the present application, and are not intended to limit the present application.

[0066] The embodiments of the present application are described below in conjunction with the accompanying drawings. Those skilled in the art will appreciate that, with the development of technology and the emergence of new scenarios, the technical solutions provided in the embodiments of the present application are also applicable to similar technical problems.

[0067] The terms "first", "second", etc. in the specification and claims of the present application and the above-mentioned drawings are used to distinguish similar objects, and need not be used to describe a specific order or sequential order. It should be understood that the terms used in this way can be interchangeable under appropriate circumstances, which is only to describe the distinction mode adopted by the objects of the same attributes when describing in the embodiments of the present application. In addition, the terms "including" and "having" and any of their variations are intended to cover non-exclusive inclusions, so that the process, method, system, product or equipment comprising a series of units need not be limited to those units, but may include other units that are not clearly listed or inherent to these processes, methods, products or equipment.

[0068] In one possible implementation, Figure 1 As shown, the system architecture involved in the present application may include a terminal 101 and a server 102, and the terminal 101 may interact with the server 102 via a network (wired network or wireless network). The server 102 may include one or more servers ( Figure 1 A server is included as an example for explanation).

[0069] Next, the product form of the above-mentioned terminal is described.

[0070] The above-mentioned terminal can be a mobile phone, a tablet computer, a wearable device, a robot, an augmented reality (AR) / virtual reality (VR) device, a laptop computer, an ultra-mobile personal computer (UMPC), a netbook, a personal digital assistant (PDA), etc., and the embodiments of the present application do not impose any restrictions on this.

[0071] Figure 2 An optional hardware structure diagram of a terminal is shown.

[0072] refer to Figure 2 As shown, the terminal may include a radio frequency unit 210, a memory 220, an input unit 230, a display unit 240, a camera 250 (optional), an audio circuit 260 (optional), a speaker 261 (optional), a microphone 262 (optional), an earphone jack 263 (optional), a processor 270, an external interface 280, a power supply 290 and other components. Those skilled in the art will appreciate that Figure 2 This is merely an example of a terminal and does not constitute a limitation on the terminal. The terminal may include more or fewer components than shown in the figure, or a combination of certain components, or different components.

[0073] The input unit 230 can be used to receive input digital or character information, and generate key signal input related to the user settings and function control of the terminal. Specifically, the input unit 230 may include a touch screen 231 (optional) and / or other input devices 232. The touch screen 231 can collect the user's touch operations on or near it (such as the user's operation on or near the touch screen using any suitable object such as fingers, joints, stylus, etc.), and drive the corresponding connection device according to a pre-set program. The touch screen can detect the user's touch action on the touch screen, convert the touch action into a touch signal and send it to the processor 270, and can receive and execute the command sent by the processor 270; the touch signal at least includes the contact point coordinate information. The touch screen 231 can provide an input interface and an output interface between the terminal and the user. In addition, the touch screen can be implemented using multiple types such as resistive, capacitive, infrared, and surface acoustic waves. In addition to the touch screen 231, the input unit 230 can also include other input devices. Specifically, other input devices 232 may include, but are not limited to, one or more of a physical keyboard, function keys (such as volume control keys, switch keys, etc.), a trackball, a mouse, a joystick, and the like.

[0074] The display unit 240 may be used to display information input by a user or information provided to a user, various menus of the terminal, an interactive interface, file display, and / or playback of any multimedia file.

[0075] The memory 220 can be used to store instructions and data. The memory 220 can mainly include an instruction storage area and a data storage area. The data storage area can store various data, such as multimedia files, texts, etc. The instruction storage area can store software units such as operating systems, applications, and instructions required for at least one function, or their subsets and extensions. It can also include a non-volatile random access memory; it provides the processor 270 with hardware, software, and data resources including management of computing and processing equipment, and supports control software and applications. It is also used for the storage of multimedia files, and the storage of running programs and applications.

[0076] The processor 270 is the control center of the terminal. It uses various interfaces and lines to connect various parts of the entire terminal. By running or executing instructions stored in the memory 220 and calling data stored in the memory 220, it executes various functions of the terminal and processes data, thereby controlling the terminal as a whole. Optionally, the processor 270 may include one or more processing units; preferably, the processor 270 may integrate an application processor and a modem processor, wherein the application processor mainly processes the operating system, user interface and application program, and the modem processor mainly processes wireless communication. It is understandable that the above-mentioned modem processor may not be integrated into the processor 270. In some embodiments, the processor and the memory may be implemented on a single chip, and in some embodiments, they may also be implemented separately on separate chips. The processor 270 may also be used to generate corresponding operation control signals, send them to corresponding components of the computing and processing device, read and process data in the software, especially read and process data and programs in the memory 220, so that each functional module therein performs corresponding functions, thereby controlling the corresponding components to act according to the requirements of the instructions.

[0077] Among them, the memory 220 can be used to store software codes related to SQL statement generation methods and data analysis methods, the processor 270 can execute the software codes in the memory 220, and can also schedule other units (such as the above-mentioned input unit 230 and display unit 240) to achieve corresponding functions.

[0078] The radio frequency unit 210 (optional) can be used for receiving and sending information or receiving and sending signals during a call, for example, after receiving the downlink information of the base station, it is sent to the processor 270 for processing; in addition, the designed uplink data is sent to the base station. Generally, the radio frequency unit 210 includes but is not limited to an antenna, at least one amplifier, a transceiver, a coupler, a low noise amplifier (LowNoiseAmplifier, LNA), a duplexer, etc. In addition, the radio frequency unit 210 can also communicate with network devices and other devices through wireless communication. The wireless communication can use any communication standard or protocol, including but not limited to Global System of Mobile communication (Global System of Mobile communication, GSM), General Packet Radio Service (General PacketRadio Service, GPRS), Code Division Multiple Access (Code Division Multiple Access, CDMA), Wideband Code Division Multiple Access (Wideband Code Division Multiple Access, WCDMA), Long Term Evolution (Long Term Evolution, LTE), email, Short Messaging Service (SMS), etc.

[0079] In the embodiment of the present application, the radio frequency unit 210 can send data to other devices, and can also receive data sent by other devices. It should be understood that the radio frequency unit 210 is optional, and can be replaced by other communication interfaces, such as a network port.

[0080] The terminal also includes a power supply 290 (such as a battery) for supplying power to various components. Preferably, the power supply can be logically connected to the processor 270 through a power management system, so that the power management system can manage charging, discharging, power consumption and other functions.

[0081] The terminal also includes an external interface 280, which may be a standard Micro USB interface or a multi-pin connector, and may be used to connect the terminal to communicate with other devices, or to connect a charger to charge the terminal.

[0082] Although not shown, the terminal may further include a flashlight, a wireless fidelity (WiFi) module, a Bluetooth module, sensors with different functions, etc., which will not be described in detail here.

[0083] Next, the product form of the above server is described.

[0084] Figure 3 A structural diagram of the above server is provided, such as Figure 3As shown, the server may include a bus 301, a processor 302, a communication interface 303, and a memory 304. The processor 302, the memory 304, and the communication interface 303 communicate with each other via the bus 301.

[0085] The bus 301 may be a peripheral component interconnect (PCI) bus or an extended industry standard architecture (EISA) bus. The bus may be divided into an address bus, a data bus, a control bus, etc. For ease of representation, Figure 3 Only one thick line is used in the diagram, but this does not mean that there is only one bus or only one type of bus.

[0086] The processor 302 may be any one or more of a central processing unit (CPU), a graphics processing unit (GPU), a microprocessor (MP), or a digital signal processor (DSP).

[0087] The memory 304 may include a volatile memory, such as a random access memory (RAM). The memory 304 may also include a non-volatile memory, such as a read-only memory (ROM), a flash memory, a hard disk drive (HDD), or a solid state drive (SSD).

[0088] The memory 304 may be used to store software codes related to the SQL statement generation method and the data analysis method. The processor 302 may call the software codes stored in the memory 304 or schedule other units to implement corresponding functions.

[0089] The processors in the above-mentioned terminals and servers (for example, processor 270 and processor 302) may be hardware circuits (such as application specific integrated circuits (ASIC), field-programmable gate arrays (FPGA), general-purpose processors, digital signal processors (DSP), microprocessors or microcontrollers, etc.), or combinations of these hardware circuits. For example, the processor may be a hardware system with an instruction execution function, such as a CPU, DSP, etc., or a hardware system without an instruction execution function, such as an ASIC, FPGA, etc., or a combination of the above-mentioned hardware systems without an instruction execution function and hardware systems with an instruction execution function.

[0090] Next, the SQL statement generation method provided by the present application is introduced through the following embodiments.

[0091] See also Figure 4 , shows a schematic diagram of a process of generating a SQL statement provided in an embodiment of the present application, the method may include:

[0092] Step S401: Obtain the user's target requirement description text.

[0093] Among them, the target demand description text is a query or analysis demand described in natural language, such as "show the monthly sales in 2023", "which category has the best sales this month and the same period last year respectively", etc.

[0094] Step S402: According to the pre-acquired data table description information, determine the table name of the data table related to the target requirement description text, and obtain the target table name.

[0095] The data table description information includes description information of each data table in the database. It should be noted that in some fields (such as business intelligence), the database will contain a large number of data tables, which are usually obtained by integrating data from multiple data sources. In order to facilitate data query or analysis, data table description information can be obtained in advance for the data tables in the database.

[0096] In one possible implementation, the descriptive information of any data table may include the metadata of the data table. The metadata of any data table may include the table name, field information, data type, etc. of the data table. The field information of any data table may include indicator items (such as electricity consumption) and dimension items (such as time, identity, city, etc.) within the data table.

[0097] In order to improve the SQL statement generation effect, in another possible implementation method, the description information of any data table may include not only the metadata of the data table, but also the first N rows of data of the data table. The first N rows of data serve as auxiliary information for SQL statement generation, where N is an integer greater than or equal to 1, such as 3.

[0098] Taking into account that not all data tables in the database are related to the target requirement description text, in order to obtain better SQL statement generation effect and improve the efficiency of SQL statement generation, this embodiment determines the description information of the data table related to the target requirement description text from the pre-obtained data table description information to obtain the table name of the data table related to the target requirement description text as the target table name.

[0099] Step S403: Determine the indicator items related to the target requirement description text from the indicator items included in the description information of the data table corresponding to the target table name, and obtain the target indicator items.

[0100] Taking into account that not all indicator items in the data table corresponding to the target table name are related to the target requirement description text, this embodiment determines the table name of the data table related to the target requirement description text, that is, the target table name, and then further determines the indicator items related to the target requirement description text from the indicator items contained in the description information of the data table corresponding to the target table name to obtain the target indicator items.

[0101] Step S404: Generate SQL statements using the big model according to the target requirement description text, target table name, and target indicator items.

[0102] After obtaining the target table name and target indicator items, this embodiment uses a large model to generate an SQL statement corresponding to the target requirement description text according to the target requirement description text, the target table name and the target indicator items.

[0103] The SQL statement generation method provided in the embodiment of the present application first obtains the user's target demand description text, then determines the table name of the data table related to the target demand description text based on the pre-acquired data table description information, then determines the indicator items related to the target demand description text from the indicator items contained in the description information of the data table corresponding to the target table name, obtains the target indicator items, and finally uses the large model to generate SQL statements based on the target demand description text, the target table name and the target indicator items. The SQL statement generation method provided in the embodiment of the present application can automatically generate SQL statements based on the query or analysis requirements described in natural language. Compared with manually writing SQL statements, it does not require users to have strong professional skills (users only need to describe the query or analysis requirements in natural language), thereby reducing labor costs and greatly reducing the time consumed in obtaining SQL statements.

[0104] In another embodiment of the present application, the specific implementation process of "step S401: obtaining the user's target demand description text" in the above embodiment is introduced.

[0105] There are many ways to obtain the user's target demand description text. This embodiment provides the following implementation methods.

[0106] The first implementation method of obtaining the user's target requirement description text is: obtaining the requirement description text input by the user (text described in natural language), and using the requirement description text input by the user as the target requirement description text.

[0107] Considering that sometimes the requirement description text input by the user may be text irrelevant to the field, text involving security issues, etc., this embodiment provides a second implementation method for obtaining the user's target requirement description text:

[0108] Step a1: Obtain the requirement description text (text described in natural language) input by the user.

[0109] Step a2: Use the large model to identify the intent of the requirement description text entered by the user.

[0110] Using the big model, the process of identifying the intent of the requirement description text input by the user can include: obtaining domain knowledge of the specified domain, and a pre-configured instruction prompt template for the intent recognition task, the instruction prompt template including a domain knowledge information slot and a requirement description information slot; filling the requirement description text input by the user into the requirement description information slot, and filling the domain knowledge of the specified domain into the domain knowledge information slot to obtain prompt instructions for the intent recognition task; inputting the prompt instructions for the intent recognition task into the big model to obtain the intent of the requirement description text input by the user.

[0111] Exemplarily, the preconfigured instruction prompt template for the intent recognition task is:

[0112] "You are a natural language understanding expert. Your task is to fully analyze the meaning of sentences and determine whether the specified sentence is a data query or analysis requirement that is strongly related to the specified field. Such sentences will be handed over to data analysts to write SQL statements that meet the requirements.

[0113] In addition, the sentence may also be classified into other categories. You need to identify whether the sentence involves security-sensitive operations. Such sentences need to be handled with caution and further confirmation with relevant security personnel is required. When the sentence is in English, you need to carefully judge the content of the sentence. It may be a requirement, or a SQL or command statement.

[0114] The following are the specific classification rules:

[0115] - If the user's question is related to the specified field and is intended for data query or analysis, it is classified as category 1.

[0116] - When users are chatting or have no specific request, it is category 3

[0117] - If the user's intention involves any security issues, including but not limited to modifying or updating data such as deleting data or tables, inserting or modifying data, or executing certain system-level commands or operations, it is classified as Category 4

[0118] - If you are not sure about the user intent or the user intent is not related to the specified field or does not fall into any of the above categories, choose category 2

[0119] After determining the user's intent, return only the numerical number of the category.

[0120] Domain knowledge of the specified domain for reference: <domain knowledge information slot>

[0121] Sentences to be classified: <requirement description information slot>

[0122] Category code: ".

[0123] The domain knowledge of the specified field and the requirement description text input by the user are filled into the above-mentioned instruction prompt template to obtain the prompt instruction for the intent recognition task, and the prompt instruction for the intent recognition task is input into the big model. The big model classifies the intent of the requirement description text input by the user and outputs the intent classification result. If the big model outputs classification 1, it means that the intention of the requirement description text input by the user is a data query or analysis intention related to the specified field.

[0124] Step a3: If the intention of the requirement description text input by the user is data query or analysis intention related to the specified field, the requirement description text input by the user is determined as the target requirement description text.

[0125] It should be noted that if the intention of the requirement description text input by the user is not a data query or analysis intention related to the specified field, the user will be prompted and guided to re-enter the requirement.

[0126] After obtaining the target requirement description text through any of the above methods, in order to improve the SQL statement generation effect, the domain knowledge related to the target requirement description text can be retrieved from the domain knowledge base of the specified field (the domain knowledge base includes professional terms of the specified field and the definition or introduction of the professional terms), and then the domain knowledge related to the target requirement description text is spliced ​​with the target requirement description text, and the spliced ​​text is used as the final target requirement description text.

[0127] Exemplarily, the demand description text input by the user is "What is the occupancy rate of Hangzhou in 2023 compared with the same period last year?", and the relevant domain knowledge retrieved from the domain knowledge base of the specified field is "The occupancy rate is the ratio of the number of open rooms to the number of rooms". The relevant domain knowledge "The occupancy rate is the ratio of the number of open rooms to the number of rooms" can be spliced ​​into the demand description text "What is the occupancy rate of Hangzhou in 2023 compared with the same period last year?", and the spliced ​​text "What is the occupancy rate of Hangzhou in 2023 compared with the same period last year? The occupancy rate is the ratio of the number of open rooms to the number of rooms" is used as the final target demand description text.

[0128] When retrieving domain knowledge related to the target requirement description text from the domain knowledge base of the specified domain, the target requirement description text may be first segmented, and then the domain knowledge related to the target requirement description text may be determined by matching the segmentation result with the domain knowledge in the domain knowledge base. It should be noted that the retrieval strategy used when retrieving domain knowledge related to the target requirement description text from the domain knowledge base of the specified domain may be, but is not limited to, BM25, similarity matching, etc.

[0129] In some fields (such as business intelligence), the number of data tables in the database is usually large. If the description information of all data tables (such as the metadata and the first N rows of data of all data tables) is passed into the large model, two problems will occur. First, when the input length is too long, the subsequent data will be truncated, resulting in the loss of context information. Second, in the long text scenario, the reasoning efficiency and effect of the large model are greatly reduced. In view of this, the present application determines the description information of the data table related to the target demand description text based on the pre-acquired data table description information (the description information of each data table in the database, which may include the metadata and the first N rows of data of each data table), so as to obtain the table name of the data table related to the target demand description text.

[0130] In another embodiment of the present application, the specific implementation process of "step S402: determining the table name of the data table related to the target requirement description text according to the pre-acquired data table description information, and obtaining the target table name" is introduced.

[0131] According to the pre-obtained data table description information, the table name of the data table related to the target requirement description text is determined. The process of obtaining the target table name may include:

[0132] Step b1: randomly select description information of some data tables from pre-acquired data table description information (description information of each data table in the database).

[0133] A preset proportion of the data table description information may be randomly selected from the data table description information. For example, the pre-acquired data table description information includes description information of 100 data tables, and 80% of the data table description information is randomly selected therefrom, that is, description information of 80 data tables is randomly selected.

[0134] Step b2: Utilize the large model to filter out the description information of n data tables that are most relevant to the target requirement description text from the description information of the randomly selected data tables.

[0135] Wherein, n is an integer greater than or equal to 1.

[0136] Specifically, the process of selecting the description information of n data tables that are most relevant to the target requirement description text from the description information of the selected data tables by using the large model may include:

[0137] Step b21: Obtain a preconfigured first instruction prompt template.

[0138] Among them, the first instruction prompt template includes a data table description information slot and a requirement description information slot. The first instruction prompt template is used to prompt the large model to filter out the description information of n data tables that are most relevant to the information in the requirement description information slot from the information in the data table description information slot.

[0139] Exemplarily, the first instruction prompt template is:

[0140] "The following are descriptions of multiple data tables and user questions. Please select the descriptions of the n data tables that are most relevant to the user's questions:

[0141] Data table description information: <data table description information slot>

[0142] Question: <requirement description information slot>"

[0143] Related data sheets: ” .

[0144] Step b22: fill the description information of the randomly selected data table into the data table description information slot in the first instruction prompt template, and fill the target requirement description text into the requirement description information slot in the first instruction prompt template to obtain the first prompt instruction.

[0145] The target requirement description text, the description information of the randomly selected data table and the preconfigured first instruction prompt template are organized into a first prompt instruction for the large model to execute.

[0146] Step b23: input the first prompt instruction into the large model to obtain the description information of n data tables output by the large model that are most relevant to the target demand description text.

[0147] The first prompt instruction is input into the large model, and the large model selects and outputs the description information of n data tables that are most relevant to the target requirement description text from the description information of multiple data tables in the first prompt instruction.

[0148] Step b3: determine whether the preset screening times m is reached. If the preset screening times m is reached, execute step b4; otherwise, execute step b1 and subsequent steps.

[0149] That is, step b1 and step b2 are repeatedly executed until the preset screening times m are reached.

[0150] like Figure 5 As shown, this embodiment completes the first round of screening by interacting with the large model m times. Through each interaction with the large model, the description information of n data tables can be screened out. After completing the first round of screening, the description information of n×m data tables can be obtained.

[0151] Step b4: Using the large model, further filter out the description information of k data tables that are most relevant to the target requirement description text from the description information of all the filtered data tables.

[0152] The specific implementation process of step b4 is similar to the specific implementation process of step b2, and will not be described in detail in this embodiment.

[0153] like Figure 5 As shown, after obtaining the description information of n×m data tables through the first round of screening, a second round of screening can be performed for the first round of screening results, that is, the description information of k data tables most relevant to the target requirement description text is screened out from the description information of n×m data tables. Wherein, k is an integer greater than or equal to 1. It should be noted that the specific values ​​of n, m, and k can be determined according to the actual scenario.

[0154] In some fields (such as business intelligence), there are a large number of indicator tables, that is, there are many indicator tables in the database, and the indicator tables contain numerous indicator items. In practical applications, the indicator items that need to be operated or screened are usually relatively few. If too many indicators are given to the large model at one time, a large number of irrelevant indicators will dilute the indicators related to the target demand description text, making it easier for the large model to ignore the indicator items that really need to be operated or screened, thereby affecting the SQL statement generation effect. In view of this, after obtaining the table name of the data table related to the target demand description text, that is, the target table name, the application further determines the indicator items related to the target demand description text from the indicator items contained in the description information of the data table corresponding to the target table name, and obtains the target indicator items.

[0155] In another embodiment of the present application, the specific implementation process of "step S403: determining the indicator items related to the target requirement description text from the indicator items contained in the description information of the data table corresponding to the target table name, and obtaining the target indicator items" is introduced.

[0156] Determine the indicator items related to the target requirement description text from the indicator items included in the description information of the data table corresponding to the target table name. The process of obtaining the target indicator items may include:

[0157] Step c1, by matching the words contained in the target demand description text with the index items contained in the description information of the data table corresponding to the target table name, determine the index items related to the target demand description text, and obtain a number of target index items.

[0158] BM25 or similarity matching methods can be used to match the words contained in the target requirement description text with the index items contained in the description information of the data table corresponding to the target table name to determine the index items related to the target requirement description text. When determining the index items related to the target requirement description text, the first N rows of data in the data table corresponding to the target table name can be used to improve the accuracy of the relevant index items.

[0159] Step c2: for each target indicator item obtained, if the non-target indicator items contained in the description information of the data table where the target indicator item is located include the parent indicator item or child indicator item of the target indicator item, the parent indicator item or child indicator item of the target indicator item is also determined as the target indicator item.

[0160] The indicator items in the data table may include indicator items with a parent-child relationship. For example, the parent indicator is "electricity consumption" and the child indicators are "electricity consumption - peak period" and "electricity - non-peak period". After the target indicator item is determined through step c1, in order to avoid missing indicator items, for any target indicator item determined through step c1, it is possible to check whether the parent indicator item or child indicator item of the target indicator item exists in the non-target indicator items included in the description information of the data table where the target indicator item is located. If the parent indicator item or child indicator item of the target indicator item exists, the parent indicator item or child indicator item of the target indicator item is also determined as the target indicator item.

[0161] Exemplarily, the metadata of a data table corresponding to a target table name includes indicator items A, B, C, D, E, F, G, H, and I. By matching the words contained in the target requirement description text with these indicator items, it is determined that B, C, and E are indicator items related to the target requirement description text, that is, B, C, and E are target indicator items. In order to avoid missing indicator items, it is further checked whether there are parent indicator items or child indicator items of B, C, and E in A, D, F, G, H, and I. Assuming that F is the parent indicator of B and H is the child indicator item of E, it is determined that F and H are also indicator items related to the target requirement description text, that is, target indicator items. The final target indicator items include B, C, E, F, and H.

[0162] It should be noted that the naming or coding of indicator items with parent-child relationship can usually reflect the parent-child relationship of the indicator items. For example, the name of the parent indicator is "electricity consumption", and the name of the child indicator is "electricity consumption-peak period". For another example, the code of the parent indicator is "AN0001", and the code of the child indicator is "AN000101". In view of this, whether an indicator item has a parent indicator and / or child indicator can be determined based on the name or code of the indicator item.

[0163] After determining the target table name (the table name of the data table related to the target requirement description text) and the target indicator items (the indicator items related to the target requirement description text in the data table related to the target requirement description text), the big model can be used to generate SQL statements based on the target requirement description text, the target table name and the target indicator items.

[0164] In another embodiment of the present application, the specific implementation process of "step S404: using the big model to generate SQL statements according to the target demand description text, target table name and target indicator items" is introduced.

[0165] In a possible implementation, the process of generating SQL statements using a large model according to a target requirement description text, a target table name, and target indicator items may include: Step d1, obtaining a data table corresponding to the target table name.

[0166] The data table corresponding to the target table name is obtained, which is the source table corresponding to the target table name.

[0167] Step d2: Build a temporary table based on the data table corresponding to the target table name and the target indicator item.

[0168] The temporary table includes the target indicator item, the dimension item in the data table corresponding to the target table name, and the data of the target indicator item in the first N rows of data in the data table corresponding to the target table name.

[0169] See also Figure 6 and Figure 7 , Figure 6An example of a data table (source table) corresponding to a target table name is shown. Figure 7 Shown according to Figure 6 An example of a temporary table constructed from the data table and target indicator items in . Figure 7 The "trading financial assets" and "derivative financial assets" in the Figure 7 The date and organization number in are dimension items. Figure 7 The data of each indicator item in is the data of the target indicator item in the first N rows of data in the data table corresponding to the target table name, that is, Figure 6 The data of "trading financial assets" and "derivative financial assets" in the first N rows of the data table are shown.

[0170] Step d3: Generate SQL statements using the large model based on the temporary table and the target requirement description text.

[0171] Specifically, using the big model, based on the temporary table and the target requirement description text, the process of generating SQL statements may include:

[0172] Step d31, obtain the table structure and the first N rows of data of the temporary table, and obtain the pre-configured second instruction prompt template.

[0173] The second instruction prompt template includes a temporary table information slot and a requirement description information slot, and the second instruction prompt template is used to prompt the large model to generate an SQL statement according to the information in the temporary table information slot and the information in the requirement description information slot.

[0174] Exemplarily, the second instruction prompt template is:

[0175] "You are a MySQL expert. For user input questions, you need to create a syntactically correct SQL statement to obtain the answer to the input question.

[0176] Unless the user specifies in the question that you want a specific number of examples, use the LIMIT clause to query at most {top_k} results in order to get the most informative data.

[0177] Be careful not to query all the columns in the table. You should query only the columns you need to answer the question. Use backticks (`) around each column name to indicate that they are delimited identifiers.

[0178] Be careful to only use the column names you see in the tables below. Do not query columns that do not exist. Also, be careful about which column belongs to which table.

[0179] If the question involves "today", be aware of using the CURDATE() function to get the current date.

[0180] Only the following tables are used: <temporary table information slot>

[0181] Question: <requirement description information slot>

[0182] SQLQuery: ".

[0183] Step d32, fill the table structure and the first N rows of data of the temporary table into the temporary table information slot in the second instruction prompt template, and fill the target requirement description text into the requirement description information slot in the second instruction prompt template to obtain the second prompt instruction.

[0184] After obtaining the table structure and the first N rows of data of the temporary table and the preconfigured second instruction prompt template, the target requirement description text, the table structure and the first N rows of data of the temporary table and the preconfigured second instruction prompt template can be organized into a second prompt instruction for the large model to execute.

[0185] Step d33: input the second prompt instruction into the large model to obtain the SQL statement output by the large model.

[0186] The second prompt instruction is input into the large model, and the large model generates and outputs an SQL statement based on the target requirement description text in the second prompt instruction, the table structure of the temporary table and the first N rows of data.

[0187] The SQL statement corresponding to the target requirement description text can be obtained through the above process.

[0188] On the basis of the SQL statement generation method provided in the above embodiment, the embodiment of the present application further provides a data analysis method, which includes: Step e1, using the SQL statement generation method provided in the above embodiment to generate SQL statements.

[0189] The generated SQL statement is the SQL statement corresponding to the user's target requirement description text.

[0190] Step e2: Call the interface connected to the database to execute SQL statements to obtain query or analysis results.

[0191] In order to query or analyze data, this application pre-uses the JDBC (Java Database Connectivity) standard interface to connect to the enterprise's database (such as MySQL, PostgreSQL, SQL Server, Oracle, etc.). Users can configure database connection information, including database URL, user name, password, etc., to complete the database connection.

[0192] After the SQL statement is generated, the JDBC interface for connecting to the database can be called to execute the SQL statement to obtain the query or analysis results.

[0193] Step e3: directly output the query or analysis results, or process the query or analysis results into charts for output.

[0194] After obtaining the query or analysis results, the query or analysis results can be directly output, or the query or analysis results can be processed into a chart and then output.

[0195] The data analysis method provided in the embodiment of the present application can use a large model to automatically generate SQL statements and reports, realizing full-process automation from natural language input to SQL query and report generation, significantly reducing dependence on professional skills, thereby reducing labor costs, and at the same time, greatly improving data analysis efficiency. For users, complex data analysis tasks can be completed without professional skills, greatly improving user experience.

[0196] The above introduces the SQL statement generation method and the data analysis method provided in the embodiments of the present application. The following will introduce the devices corresponding to the above SQL statement generation method and the data analysis method.

[0197] See also Figure 8 , Figure 8 A structural diagram of a SQL statement generating device 80 provided in an embodiment of the present application, wherein the SQL statement generating device 80 may include: a requirement description text acquiring module 801, a related data table determining module 802, a related indicator item determining module 803 and a SQL statement generating module 804.

[0198] The requirement description text acquisition module 801 is used to acquire the user's target requirement description text, where the target requirement description text is a query or analysis requirement described in natural language.

[0199] The related data table determination module 802 is used to determine the table name of the data table related to the target requirement description text according to the pre-acquired data table description information, and obtain the target table name.

[0200] Among them, the pre-obtained data table description information includes the description information of each data table in the database, the description information of any data table at least includes the metadata of the data table, the metadata of any data table includes the table name and field information of the data table, and the field information of any data table includes the indicator items in the data table.

[0201] The relevant indicator item determination module 803 is used to determine the indicator items related to the target requirement description text from the indicator items included in the description information of the data table corresponding to the target table name, and obtain the target indicator items.

[0202] The SQL statement generation module 804 is used to generate SQL statements using the big model according to the target requirement description text, target table name and target indicator items.

[0203] In a possible implementation, the requirement description text acquisition module 801 includes: a user input text acquisition module and a user intention recognition module.

[0204] The user input text acquisition module is used to obtain the requirement description text input by the user.

[0205] The user intention recognition module is used to use the big model to identify the intention of the demand description text input by the user. If the intention of the demand description text input by the user is data query or analysis intention related to the specified field, the demand description text input by the user is determined as the target demand description text.

[0206] In a possible implementation, the requirement description text acquisition module 801 includes: an information supplement module.

[0207] The information supplement module is used to retrieve the domain knowledge related to the target requirement description text in the domain knowledge base of the specified field, splice the domain knowledge related to the target requirement description text with the target requirement description text, and use the spliced ​​text as the final target requirement description text.

[0208] In a possible implementation manner, the description information of any data table further includes: the first N rows of data in the data table, where N is an integer greater than or equal to 1.

[0209] In a possible implementation, when the relevant data table determination module 802 determines the table name of the data table related to the target requirement description text according to the pre-acquired data table description information and obtains the target table name, it is specifically used to:

[0210] By using the large model, the description information of the data table related to the target requirement description text is filtered from the data table description information to obtain the table name of the data table related to the target requirement description text as the target table name.

[0211] In a possible implementation, when the relevant data table determination module 802 uses the large model to filter the description information of the data table related to the target requirement description text from the data table description information, it is specifically used to:

[0212] Randomly select description information of some data tables from the description information of the data tables;

[0213] Using the large model, from the description information of the randomly selected data tables, the description information of n data tables that are most relevant to the target requirement description text is screened out, where n is an integer greater than or equal to 1;

[0214] Repeat the steps of randomly selecting description information of a portion of data tables from the description information of the data tables, and using the large model to filter out description information of n data tables that are most relevant to the target requirement description text from the description information of the randomly selected data tables, until a preset number of filtering times is reached;

[0215] By using the large model, the description information of k data tables most relevant to the target requirement description text is further screened out from the description information of all the screened data tables, where k is an integer greater than or equal to 1.

[0216] In a possible implementation, the relevant data table determination module 802 uses the large model to filter out the description information of n data tables that are most relevant to the target requirement description text from the description information of the randomly selected data tables, including:

[0217] Obtain a preconfigured first instruction prompt template, wherein the first instruction prompt template includes a data table description information slot and a requirement description information slot, and the first instruction prompt template is used to prompt the large model to filter out description information of n data tables that are most relevant to the information in the requirement description information slot from the information in the data table description information slot;

[0218] Filling the description information of the randomly selected data table into the data table description information slot, and filling the target requirement description text into the requirement description information slot, to obtain a first prompt instruction;

[0219] The first prompt instruction is input into the large model to obtain description information of n data tables output by the large model that are most relevant to the target demand description text.

[0220] In a possible implementation, the relevant indicator item determination module 803 determines the indicator items related to the target requirement description text from the indicator items included in the description information of the data table corresponding to the target table name, and when obtaining the target indicator items, is specifically used to:

[0221] By matching the words included in the target demand description text with the indicator items included in the description information of the data table corresponding to the target table name, the indicator items related to the target demand description text are determined to obtain a plurality of target indicator items;

[0222] For each target indicator item obtained, if the non-target indicator items contained in the description information of the data table where the target indicator item is located include the parent indicator item or child indicator item of the target indicator item, the parent indicator item or child indicator item of the target indicator item is determined as the target indicator item.

[0223] In a possible implementation, when the SQL statement generation module 804 generates the SQL statement using the large model according to the target requirement description text, the target table name, and the target indicator item, it is specifically used to:

[0224] Get the data table corresponding to the target table name;

[0225] A temporary table is constructed according to the data table corresponding to the target table name and the target indicator item, wherein the temporary table includes the target indicator item, the dimension items in the data table corresponding to the target table name, and the data of the target indicator item in the first N rows of data in the data table corresponding to the target table name, where N is an integer greater than or equal to 1;

[0226] Using the big model, SQL statements are generated based on the temporary table and target requirement description text.

[0227] In a possible implementation, the SQL statement generation module 804 generates SQL statements based on the large model, the temporary table and the target requirement description text, including:

[0228] Obtain the table structure and first N rows of data of the temporary table, and obtain a preconfigured second instruction prompt template, wherein the second instruction prompt template includes a temporary table information slot and a requirement description information slot, and the second instruction prompt template is used to prompt the large model to generate an SQL statement according to the information in the temporary table information slot and the information in the requirement description information slot;

[0229] Fill the table structure and the first N rows of data of the temporary table into the temporary table information slot, and fill the target requirement description text into the requirement description information slot to obtain the second prompt instruction;

[0230] Input the second prompt instruction into the large model to obtain the SQL statement output by the large model.

[0231] The SQL statement generation device provided in the embodiment of the present application can automatically generate SQL statements according to the query or analysis requirements described in natural language. Compared with manually writing SQL statements, it does not require users to have strong professional skills (users only need to describe the query or analysis requirements in natural language), thereby reducing labor costs and greatly reducing the time spent on obtaining SQL statements.

[0232] The present application also provides a data analysis system. Fig. 9 , showing a schematic diagram of the structure of the data analysis system 90, the data analysis system 90 may include the SQL statement generating device 80 provided in the above embodiment, and may also include a SQL statement executing module 901 and an analysis result processing module 902.

[0233] The SQL statement generating device 80 is used to generate an SQL statement corresponding to the user's target requirement description text.

[0234] The SQL statement execution module 901 is used to call the interface connected to the database to execute the SQL statement generated by the SQL statement generation device 80 to obtain the query or analysis result.

[0235] The analysis result processing module 902 is used to directly output the query or analysis results, or process the query or analysis results into charts for output.

[0236] The data analysis system provided in the embodiment of the present application can use a large model to automatically generate SQL statements and reports, realizing full-process automation from natural language input to SQL query and report generation, significantly reducing dependence on professional skills, thereby reducing labor costs, and at the same time, greatly improving data analysis efficiency. For users, complex data analysis tasks can be completed without professional skills, greatly improving user experience.

[0237] An embodiment of the present application also provides an electronic device, which may include: at least one processor, at least one communication interface, at least one memory and at least one communication bus.

[0238] In the embodiment of the present application, the number of the processor, the communication interface, the memory, and the communication bus is at least one, and the processor, the communication interface, and the memory communicate with each other through the communication bus;

[0239] The processor may be a central processing unit (CPU), or an application-specific integrated circuit (ASIC), or one or more integrated circuits configured to implement the embodiments of the present application;

[0240] The memory may include a high-speed RAM memory, and may also include a non-volatile memory, such as at least one disk memory;

[0241] The memory stores a program, and the processor can call the program stored in the memory, and the program is used to implement the steps of the SQL statement generation method or data analysis method provided in the above embodiment.

[0242] An embodiment of the present application also provides a computer storage medium, which carries one or more computer programs. When the one or more computer programs are executed by an electronic device, the electronic device can implement the steps of the SQL statement generation method or data analysis method provided in the above embodiment.

[0243] An embodiment of the present application also provides a computer program product, including computer-readable instructions. When the computer-readable instructions are executed on an electronic device, the electronic device implements the steps of the SQL statement generation method or the data analysis method provided in the above embodiment.

[0244] It should also be noted that the device embodiments described above are merely schematic, wherein the units described as separate components may or may not be physically separated, and the components displayed as units may or may not be physical units, that is, they may be located in one place, or they may be distributed over multiple network units. Some or all of the modules may be selected according to actual needs to achieve the purpose of the scheme of this embodiment. In addition, in the drawings of the device embodiments provided by the present application, the connection relationship between the modules indicates that there is a communication connection between them, which may be specifically implemented as one or more communication buses or signal lines.

[0245] Through the description of the above implementation mode, the technicians in the field can clearly understand that the present application can be implemented by means of software plus necessary general hardware, and of course, it can also be implemented by special hardware including special integrated circuits, special CPUs, special memories, special components, etc. In general, all functions completed by computer programs can be easily implemented by corresponding hardware, and the specific hardware structure used to implement the same function can also be various, such as analog circuits, digital circuits or special circuits. However, for the present application, software program implementation is a better implementation mode in more cases. Based on such an understanding, the technical solution of the present application is essentially or the part that contributes to the prior art can be embodied in the form of a software product, which is stored in a readable storage medium, such as a computer floppy disk, a U disk, a mobile hard disk, a ROM, a RAM, a magnetic disk or an optical disk, etc., including a number of instructions to enable a computer device (which can be a personal computer, a training device, or a network device, etc.) to execute the methods described in each embodiment of the present application.

[0246] In the above embodiments, all or part of the embodiments may be implemented by software, hardware, firmware or any combination thereof. When implemented by software, all or part of the embodiments may be implemented in the form of a computer program product.

[0247] The computer program product includes one or more computer instructions. When the computer program instructions are loaded and executed on a computer, the process or function described in the embodiment of the present application is generated in whole or in part. The computer may be a general-purpose computer, a special-purpose computer, a computer network, or other programmable devices. The computer instructions may be stored in a computer-readable storage medium, or transmitted from one computer-readable storage medium to another computer-readable storage medium. For example, the computer instructions may be transmitted from a website site, a computer, a training device, or a data center by wired (e.g., coaxial cable, optical fiber, digital subscriber line (DSL)) or wireless (e.g., infrared, wireless, microwave, etc.) mode to another website site, computer, training device, or data center. The computer-readable storage medium may be any available medium that a computer can store or a data storage device such as a training device, a data center, etc. that includes one or more available media integrations. The available medium may be a magnetic medium, (e.g., a floppy disk, a hard disk, a tape), an optical medium (e.g., a DVD), or a semiconductor medium (e.g., a solid-state drive (SSD)), etc.

Claims

1. A method for generating a SQL statement, characterized in that: include: Obtaining a user's target requirement description text, where the target requirement description text is a query or analysis requirement described in natural language; According to the pre-acquired data table description information, determine the table name of the data table related to the target demand description text, and obtain the target table name, wherein the data table description information includes the description information of each data table in the database, and the description information of any data table at least includes the metadata of the data table, and the metadata of any data table includes the table name and field information of the data table, and the field information of any data table includes the indicator items in the data table; Determine the indicator items related to the target requirement description text from the indicator items included in the description information of the data table corresponding to the target table name, and obtain the target indicator item; Using the big model, an SQL statement is generated according to the target requirement description text, the target table name and the target indicator item.

2. The SQL statement generation method according to claim 1, characterized in that: The step of obtaining the user's target requirement description text includes: Get the requirement description text entered by the user; Using the big model, identifying the intention of the requirement description text input by the user; If the intention of the requirement description text input by the user is a data query or analysis intention related to a specified field, the requirement description text input by the user is determined as a target requirement description text.

3. The SQL statement generation method according to claim 1, characterized in that: Also includes: Retrieving domain knowledge related to the target requirement description text in a domain knowledge base of a specified domain; The domain knowledge related to the target requirement description text is spliced ​​with the target requirement description text, and the spliced ​​text is used as the final target requirement description text.

4. The SQL statement generation method according to claim 1, characterized in that: The description information of any data table also includes: the first N rows of data in the data table, where N is an integer greater than or equal to 1.

5. The SQL statement generation method according to claim 1, characterized in that: The step of determining the table name of the data table related to the target requirement description text according to the pre-acquired data table description information to obtain the target table name includes: By using the large model, the description information of the data table related to the target requirement description text is filtered from the data table description information to obtain the table name of the data table related to the target requirement description text as the target table name.

6. The SQL statement generation method according to claim 5, characterized in that: The using of the large model to filter the description information of the data table related to the target requirement description text from the data table description information includes: Randomly select description information of some data tables from the data table description information; Using the large model, from the description information of the randomly selected data tables, the description information of n data tables that are most relevant to the target requirement description text is screened out, where n is an integer greater than or equal to 1; Repeating the steps of randomly selecting description information of a portion of the data tables from the description information of the data tables, and using the large model to filter out description information of n data tables that are most relevant to the target requirement description text from the description information of the randomly selected data tables, until a preset number of filtering times is reached; By using the large model, the description information of k data tables most relevant to the target requirement description text is further screened out from the description information of all the screened data tables, where k is an integer greater than or equal to 1.

7. The SQL statement generation method according to claim 6, characterized in that: The method of using the large model to screen out the description information of n data tables that are most relevant to the target requirement description text from the description information of the randomly selected data tables includes: Obtain a preconfigured first instruction prompt template, wherein the first instruction prompt template includes a data table description information slot and a requirement description information slot, and the first instruction prompt template is used to prompt the large model to filter out description information of n data tables that are most relevant to the information in the requirement description information slot from the information in the data table description information slot; Filling the description information of the randomly selected data table into the data table description information slot, and filling the target requirement description text into the requirement description information slot, to obtain a first prompt instruction; The first prompt instruction is input into the large model to obtain description information of n data tables output by the large model that are most relevant to the target demand description text.

8. The SQL statement generation method according to claim 1, characterized in that: The step of determining the indicator items related to the target requirement description text from the indicator items contained in the description information of the data table corresponding to the target table name to obtain the target indicator items includes: By matching the words included in the target requirement description text with the indicator items included in the description information of the data table corresponding to the target table name, the indicator items related to the target requirement description text are determined to obtain a plurality of target indicator items; For each target indicator item obtained, if the non-target indicator items contained in the description information of the data table where the target indicator item is located include the parent indicator item or child indicator item of the target indicator item, the parent indicator item or child indicator item of the target indicator item is determined as the target indicator item.

9. The SQL statement generation method according to claim 1, characterized in that: The use of the large model to generate SQL statements according to the target requirement description text, the target table name and the target indicator item includes: Get the data table corresponding to the target table name; A temporary table is constructed according to the data table corresponding to the target table name and the target indicator item, wherein the temporary table includes the target indicator item, the dimension items in the data table corresponding to the target table name, and the data of the target indicator item in the first N rows of data in the data table corresponding to the target table name, where N is an integer greater than or equal to 1; Using the big model, an SQL statement is generated based on the temporary table and the target requirement description text.

10. The SQL statement generation method according to claim 9, characterized in that: The use of the large model, based on the temporary table and the target requirement description text, generates an SQL statement, including: Obtain the table structure and first N rows of data of the temporary table, and obtain a preconfigured second instruction prompt template, wherein the second instruction prompt template includes a temporary table information slot and a requirement description information slot, and the second instruction prompt template is used to prompt the large model to generate an SQL statement according to the information in the temporary table information slot and the information in the requirement description information slot; Fill the table structure and the first N rows of data of the temporary table into the temporary table information slot, and fill the target requirement description text into the requirement description information slot to obtain a second prompt instruction; The second prompt instruction is input into the large model to obtain the SQL statement output by the large model.

11. A data analysis method, characterized in that: include: Generate an SQL statement using the SQL statement generation method according to any one of claims 1 to 10; Calling the interface connected to the database to execute the SQL statement to obtain the query or analysis result; The query or analysis result is directly output, or the query or analysis result is processed into a chart for output.

12. A device for generating SQL statements, characterized in that: include: Requirement description text acquisition module, related data table determination module, related indicator item determination module and SQL statement generation module; The requirement description text acquisition module is used to acquire the user's target requirement description text, wherein the target requirement description text is a query or analysis requirement described in natural language; The related data table determination module is used to determine the table name of the data table related to the target demand description text according to the pre-acquired data table description information, and obtain the target table name, wherein the data table description information includes the description information of each data table in the database, the description information of any data table at least includes the metadata of the data table, the metadata of any data table includes the table name and field information of the data table, and the field information of any data table includes the indicator items in the data table; The related indicator item determination module is used to determine the indicator items related to the target requirement description text from the indicator items contained in the description information of the data table corresponding to the target table name, and obtain the target indicator items; The SQL statement generation module is used to generate SQL statements using a large model according to the target requirement description text, the target table name and the target indicator items.

13. An electronic device, characterized in that: The method comprises at least one processor and a memory connected to the processor, wherein: The memory is used to store computer programs; The processor is used to execute the computer program so that the electronic device can implement the steps of the SQL statement generation method as described in any one of claims 1 to 10.

14. A computer storage medium, characterized in that: The storage medium carries one or more computer programs, and when the one or more computer programs are executed by an electronic device, the electronic device can implement the steps of the SQL statement generation method as described in any one of claims 1 to 10.

15. A computer program product, characterized in that The method comprises computer-readable instructions, and when the computer-readable instructions are executed on an electronic device, the electronic device implements the steps of the SQL statement generating method as claimed in any one of claims 1 to 10.

Images