Visualization method driven by SQL generation based on large model preference learning

Abstract

The invention provides an SQL generation driven visualization method based on large model preference learning. The method comprises the steps that an input natural language query text and user historical query records are acquired; generating a user portrait according to the user historical query record; analyzing the natural language query text by using a pre-trained large language model to obtain a table name of a database table related to the query intention of the user and a field name corresponding to the query object entity in the database table; generating an initial structured query statement according to the user portrait, the table name of the database table and the field name corresponding to the query object entity; performing preference optimization processing on the initial structured query statement according to a user query historical record and a user portrait to obtain a final structured query statement; and according to the user portrait, carrying out visual presentation on a query result corresponding to the final structured query statement to obtain a visual result. According to the method, the accuracy of the query result and the visual processing efficiency are effectively improved.

Description

Technical Field

[0001] The present invention belongs to the field of artificial intelligence technology, and in particular relates to a visualization method driven by SQL generation based on large model preference learning. Background Art

[0002] With the development of Large Language Models (LLMs), especially the breakthroughs in text understanding and contextual learning, researchers have begun to try to apply LLMs to Text2Vis tasks. Existing Text2Vis methods based on LLMs still have some limitations when dealing with complex data queries, especially when it is necessary to retrieve and operate data from large-scale data tables. LLMs lack sufficient data exposure during training and it is difficult to directly generate appropriate structured query languages ​​(SQL) to meet the visualization requirements of query results.

[0003] For example, directly generating SQL statements from natural language query text cannot accurately understand the semantics of complex queries, and often produces low-quality SQL queries, resulting in inaccurate query results and affecting decision quality. Secondly, in terms of data visualization, most use static chart templates, which lack flexibility. Even if some systems allow users to customize the style of charts, these adjustments are mostly manual operations and cannot automatically optimize the visualization results based on data changes or user behavior.

[0004] Therefore, it is urgent to propose a solution that meets the query result visualization needs of different users to solve the above problems. Summary of the invention

[0005] In order to at least solve one or more of the technical problems mentioned above, the present invention proposes a SQL generation-driven visualization solution based on large model preference learning in multiple aspects.

[0006] In a first aspect, an embodiment of the present invention provides a visualization method driven by SQL generation based on large model preference learning, the method comprising: obtaining an input natural language query text and a user historical query record; generating a user portrait according to the user historical query record; parsing the natural language query text using a pre-trained large language model to obtain a table name of a database table related to the user query intention and a field name corresponding to a query object entity in the database table; generating an initial structured query statement according to the user portrait, the table name of the database table and the field name corresponding to the query object entity, the initial structured query statement comprising query object entities in an initial sort and initial customized query parameters; performing preference optimization processing on the initial structured query statement according to the user query history record and the user portrait to obtain a final structured query statement, the position order of the query object entities in the final structured query statement being obtained after optimizing and adjusting the query object entities in the initial sort in the initial structured query statement according to the user query history record, and the preference customized query parameters in the final structured query statement being obtained after optimizing and adjusting the initial customized query parameters according to the user portrait; visually presenting the query results corresponding to the final structured query statement according to the user portrait to obtain a visualization result.

[0007] In some embodiments, the user portrait includes preference filtering conditions, which are preference expressions for cross-table join information, nested query information and / or data aggregation operation information. The initial structured query statement is subjected to preference optimization processing based on the user query history record and the user portrait, including: adjusting the position order and / or aggregation method of the query object entities contained in the initial structured query statement and / or deleting redundant query object entities based on the query execution results of the user query history record; optimizing the initial customized parameters contained in the initial structured query statement according to the preference filtering conditions to obtain the final structured query statement.

[0008] In some embodiments, the user portrait includes initial display configuration parameters, and the initial structured query statement is subjected to preference optimization processing based on the user query history record and the user portrait, including: inputting the initial display configuration parameters, the user query history record and the initial structured query statement obtained based on the user portrait into a pre-built preference comparison model, and at least adjusting the initial customized parameters of the initial structured query statement to obtain a final structured query statement, wherein the initial customized parameters include cross-table join information, nested query information and / or data aggregation operation information.

[0009] In some embodiments, the user portrait includes a recommended visualization type, and the query results corresponding to the final structured query statement are visualized according to the user portrait, including: executing the final structured query statement to obtain the query results corresponding to the final structured query statement; generating a visualization specification corresponding to the query result according to the recommended visualization type, the visualization specification is the result of rule definition of basic attributes of graphic elements used to present the query results according to the visualization type; generating a visualization code corresponding to the visualization specification according to the programming language rules; running the visualization code to obtain a visualization result, the visualization result is the result of visually presenting the query result according to the recommended visualization type.

[0010] In some embodiments, after visually presenting the query results corresponding to the final structured query statement according to the user portrait, the method also includes: receiving input of new display configuration parameters, the new display configuration parameters are used to adjust the visualization results; visually presenting the query results corresponding to the final structured query statement according to the new display configuration parameters to obtain new visualization results; updating the new display configuration parameters to the user portrait to obtain an updated user portrait.

[0011] In some embodiments, when the first interaction interface is a chat interaction interface, receiving new display configuration parameters as input in the first interaction interface includes: receiving new natural language text as input in the chat interaction interface, the new natural language text containing at least one keyword for adjusting the visualization results; and converting the keywords into new display configuration parameters in response to the new natural language text.

[0012] In some embodiments, when the second interactive interface is a graphical user interface, the graphical user interface includes an image display area for displaying visualization results and a parameter adjustment area for adjusting display configuration parameters, and receiving new display configuration parameters input by a user in the second interactive interface includes: receiving a selection operation input in the parameter adjustment area for an operation item related to the display configuration parameters; in response to the selection operation, updating the initial display configuration parameters according to the content of the operation item corresponding to the selection operation to obtain new display configuration parameters.

[0013] The SQL generation driven visualization method based on large model preference learning provided by the present invention comprises the following steps: obtaining an input natural language query text and a user historical query record; generating a user portrait according to the user historical query record; then parsing the natural language query text using a pre-trained large language model to obtain the table name of a database table related to the user query intention and the field name corresponding to the query object entity in the database table; and generating an initial structured query statement according to the user portrait, the table name of the database table and the field name corresponding to the query object entity, wherein the initial structured query statement comprises the query object entity in initial sorting and the initial customized query parameter; then performing preference optimization processing on the initial structured query statement according to the user query history record and the user portrait to obtain a final structured query statement, wherein the position order of the query object entity in the final structured query statement is obtained after optimizing and adjusting the query object entity in the initial sorting in the initial structured query statement according to the user query history record, and the preference customized query parameter in the final structured query statement is obtained after optimizing and adjusting the initial customized query parameter according to the user portrait. Finally, the query result corresponding to the final structured query statement is visualized according to the user portrait to obtain a visualization result. Compared with related technologies, the technical solution provided by the embodiments of the present invention accurately understands user intentions through a large language model, converts natural language query text into SQL query statements, effectively improves query efficiency and accuracy, and then dynamically optimizes the generation process of SQL query statements and visualization results through preference learning and user portraits, providing users with accurate query results and personalized chart recommendations, significantly improving the efficiency of query result visualization processing. BRIEF DESCRIPTION OF THE DRAWINGS

[0014] Other features, objects and advantages of the present invention will become more apparent from the detailed description of non-limiting embodiments made with reference to the following drawings:

[0015] Figure 1 is an exemplary schematic diagram of a visualization method 100 driven by SQL generation based on large model preference learning according to an embodiment of the present invention;

[0016] Figure 2 An exemplary flow chart showing step S104 of other embodiments of the present invention;

[0017] Figure 3 An exemplary flow chart showing step S105 of still other embodiments of the present invention;

[0018] Figure 4 A schematic diagram showing visualization results of an interactive interface according to an embodiment of the present invention is shown;

[0019] Figure 5A schematic diagram showing visualization results of an interactive interface according to another embodiment of the present invention is shown;

[0020] Figure 6 is a structural diagram of a visualization device 600 driven by SQL generation based on large model preference learning according to an embodiment of the present invention;

[0021] Figure 7 A schematic block diagram of an electronic device 700 according to an embodiment of the present invention is shown.

[0022] The same or similar reference numerals in the drawings represent the same or similar components. DETAILED DESCRIPTION

[0023] In order to better understand and explain the present invention, the present invention will be further described in detail below in conjunction with the accompanying drawings. The present invention is not limited to these specific embodiments. On the contrary, modifications or equivalent substitutions made to the present invention should all be included in the scope of the claims of the present invention.

[0024] It should be noted that numerous specific details are given in the following specific embodiments. Those skilled in the art should understand that the present invention can also be implemented without these specific details. In the multiple specific embodiments given below, the principles, structures and components well known in the art are not described in detail in order to highlight the main purpose of the present invention.

[0025] The SQL generation driven visualization method based on large model preference learning provided by the present invention can be executed by a computer device, wherein the computer device can be a terminal or a server. The terminal can be a terminal device such as a smart phone, a tablet computer, a laptop, a touch screen, a personal computer (PC), a personal digital assistant (PDA), etc., and the terminal can also include a client. The server can be an independent physical server, or a server cluster or distributed system composed of multiple physical servers, or a cloud server that provides basic cloud computing services such as cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communications, middleware services, domain name services, security services, CDN, and big data and artificial intelligence platforms.

[0026] Please refer to Figure 1 , Figure 1 FIG. 1 is an exemplary schematic diagram of a visualization method 100 driven by SQL generation based on large model preference learning according to an embodiment of the present invention. Figure 1 As shown, the method includes:

[0027] In step S101, the input natural language query text and the user's historical query records are obtained.

[0028] In the above steps, the natural language query text refers to the query request input in the form of a natural language question. The user's historical query record includes but is not limited to: query time, user ID, query statement, query result, database object, execution status, and error information. The query time is the timestamp of the query execution, which is used to record the execution time of the query. The user ID is used to distinguish the identities of different users, which can be a user ID, user name, or other attributes that uniquely identify the user. The query statement is the SQL query statement actually executed by the user, including at least SELECT and other operations. The query result is the result set returned after the query is executed. For SELECT queries, it is the data row that meets the query conditions. The database object refers to the database objects of the query, such as tables, views, stored procedures, etc. The execution status is the status of the query execution, such as success, failure, or warning. This helps to understand whether there is a problem during the query execution. The error information can provide detailed information about the cause of the failure, such as syntax errors, insufficient permissions, data constraint conflicts, etc.

[0029] In step S102, a user portrait is generated based on the user's historical query records.

[0030] In the above steps, the user query history record is input into the pre-built user portrait construction model to construct the user portrait and obtain the user portrait. The user query history record is input into the pre-built user portrait construction model to construct the user portrait and obtain the user portrait. The user portrait construction model may include but is not limited to cluster analysis models, association rule mining models, etc. After obtaining the user query history record data, the data is cleaned and sorted, duplicate, missing or erroneous data is removed, the data format is converted and uniformly processed, and then the user's personal information and behavioral characteristics (for example, query frequency, preference for icon type), etc. are extracted, and machine learning or related algorithms are used to mine the correlation between the key features of the user's personal behavior, which is used as the user's personal behavior and preference. Then, groups with similar personal behaviors and preferences are divided to obtain user portraits.

[0031] In step S103, the natural language query text is parsed using a pre-trained large language model to obtain the table name of the database table related to the user's query intention and the field name corresponding to the query object entity in the database table; based on the user portrait, the table name of the database table and the field name corresponding to the query object entity, an initial structured query statement is generated, and the initial structured query statement includes the query object entity in an initial sort and initial customized query parameters.

[0032] In the above steps, the database table related to the user's query intention refers to a database table determined in the relevant database according to the user's query intention.

[0033] The query object entity refers to the key entity determined according to the user's query intent. For example, a user enters a natural language query text in the database query interface: "Show the monthly sales data of electronic products in 2020". Keywords related to the query intent can be identified, such as "display", "2020", "electronic products", "sales data", "each month", etc. According to the user's query intent, the "sales data" of the database table related to the query intent can be determined. According to "sales data", the table name "sales_data" can be determined in the associated database. In this database table, the field names "sales_amount" and "sales_month" corresponding to the query object entities ("each month" and "sales data") can be further determined. In the input natural language query text, the intent keyword "display" can also be identified. The intent keyword is a keyword used to express the query intent. According to the natural language query text entered by the user, it can be understood that the user's query intention is to visualize the sales data of electronic products in 2020 by month.

[0034] By introducing the semantic understanding ability of the large language model, the natural language query text entered by the user can be accurately parsed and accurate SQL query statements can be automatically generated. Compared with the traditional rule template method, the SQL generation process of the present invention is more flexible and intelligent, especially when processing complex queries or cross-table queries. This improvement reduces the burden of users writing queries manually, while improving query efficiency and accuracy.

[0035] In the above steps, based on the user's historical query records (such as "Trend changes in electronic product sales data in 2019", "Monthly data comparison of electronic product sales data in the first half of this year"), it can be predicted that the user's query habit is to find the sales data of electronic products, and expects to visualize the sales data of electronic products by month. For example, it is predicted that the user's query behavior is to expect to visualize the sales data of electronic products in a certain period of time by month. Among them, the customized query parameters corresponding to its query intention may include "WHERE", "ORDER BY". Customized query parameters refer to keywords used to customize query results in SQL query statements. For example, WHERE: is used to filter the qualified part of the result set. Only rows that meet the specified conditions in the WHERE clause will be included in the result set. ORDER BY: is used to sort the result set. The ORDER BY clause can arrange the results in ascending (ASC) or descending (DESC) order according to the values ​​of one or more columns. Customized parameters include but are not limited to WHERE clauses, ORDER BY clauses, GROUP BY clauses, cross-table join information, nested query information and / or data aggregation operation information.

[0036] The customized parameters "WHERE" and "ORDER BY" can be determined based on the query object entities extracted from the natural language query text. For example, "WHERE year = 2020", "ORDER BY sales_month". In some embodiments, the initial customized parameters for generating structured query statements can be predicted by a machine learning model based on the user's historical query records. Machine learning models include but are not limited to linear regression, decision trees, etc. For example, based on the user's historical query records, it can be predicted that the query parameters "WHERE" and "ORDER BY" need to be customized. Then, the pre-trained large language model is used to generate the initial SQL statement based on the initial customized parameters "WHERE year = 2020", "ORDER BY sales_month", the database table name "sales_data" and the field names "sales_amount" and "sales_month" corresponding to the query object entities:

[0037] SELECT sa les_amount,sa les_month FROM sa les_data WHERE year=2020ORDER BY sa les_month.

[0038] In step S104, preference optimization processing is performed on the initial structured query statement according to the user query history record and the user portrait to obtain a final structured query statement.

[0039] In the above steps, the position order of the query object entities in the final structured query statement is obtained by optimizing and adjusting the query object entities in the initial structured query statement according to the user query history records, and the preference customized query parameters in the final structured query statement are obtained by optimizing and adjusting the initial customized query parameters according to the user portrait.

[0040] In some embodiments, preference optimization processing is performed on the initial structured query statement based on the user query history record and the user portrait, and preference optimization processing may be performed on the initial structured query statement using a preference learning model.

[0041] Assume that the user query history records include "trend changes in electronic product sales data in 2019", "monthly data comparison of electronic product sales data in the first half of this year", "changes in electronic product sales data in the first quarter of 2019", etc. User profiles can be generated based on the content of the user query history records, query frequency, query time and other behavioral information. For example, the user's query history records include frequent queries for various sales data and relatively small amounts of product energy consumption data. Among them, the frequent queries for various sales data include sales data of electronic products, sales data of hardware components of electronic products, or sales data of software products supporting electronic products. Relatively small amounts of product energy consumption data include the purchase quantity of various raw materials used to produce hardware components of electronic products, monthly consumption quantity, etc. The preference learning model analyzes the user query history records and predicts that the user is interested in sales data, then the priority weight value assigned to sales data is larger, and the priority weight value assigned to energy consumption data is smaller.

[0042] By summarizing user attributes, behavioral habits, preferences and other information through user portraits, the accuracy of personalized SQL query statements can be effectively improved.

[0043] In some embodiments, the final structured query statement is a query statement after optimizing and adjusting various parameters in the initial structured query statement. Assume that the initial structured query statement is:

[0044] SELECT sa les_month,sa les_amount FROM sa les_data WHERE year=2020ORDER BY sa les_month.

[0045] According to the user query history and user portrait, the initial structured query statement is optimized by preference, and the final structured query statement can be obtained as follows:

[0046] SELECT sa les_amount,sa les_month FROM sa les_data WHERE year=2020,product=elect ORDER BY sa les_month.

[0047] In some embodiments, the initial display configuration parameters and the initial structured query statement obtained according to the user portrait are input into a pre-built preference comparison model, and at least the initial structured query statement is adjusted with initial customization parameters to obtain a final structured query statement, wherein the initial customization parameters include cross-table join information, nested query information and / or data aggregation operation information. For example, a preference comparison model constructed using a direct preference optimization algorithm. After collecting data related to preferences, the data related to preferences, for example, are user feedback on query results, query execution time, result relevance scores, etc. The preference comparison model can quantify and compare different preferences. For example, a multi-criteria decision analysis (MCDA) is used to comprehensively consider multiple preferences, assign weights to each preference, and adjust the parameters in the SQL query according to the results of the preference comparison.

[0048] Based on the analysis results of the preference comparison model, adjust the parameters in the SQL query statement, including but not limited to modifying the WHERE clause of the query to filter more relevant data, using JOIN operations to combine multiple data sources, adjusting sorting and paging parameters to optimize the display of results, etc.

[0049] The initial display configuration parameters, user query history records and initial structured query statements obtained according to the user portrait are input into a pre-built preference comparison model, and at least the initial customized parameters of the initial structured query statement are adjusted, which can include adjusting the position order of the query object entity and the initial customized parameters in the initial structured query statement according to the query execution result of the user query history record to obtain new positions corresponding to the query object entity and the initial customized parameters; optimizing and adjusting the initial customized parameters in the initial structured query statement according to the initial display configuration parameters obtained from the user portrait to obtain preference customized query parameters; and generating a final structured query statement according to the new positions corresponding to the query object entity and the conditional clause and the preference customized query parameters.

[0050] For example, the final structured query statement is: SELECT sales_amount, sales_month FROM sales_data WHERE year=2020, product=elect ORDER BY sales_month. The query objects sales_amount and sales_month have changed positions compared to sales_amount and sales_month in the initial structured query statement. The conditional clause WHERE has an additional query parameter product=elect compared to the conditional clause in the initial structured query statement.

[0051] In some embodiments, the initial display configuration parameters may also include regional fields and / or product category features. Regional fields, such as region; product categories, such as product_type. Assuming that the user prefers to aggregate data by region and product category during the query process, after optimizing and adjusting the initial structured query statement, the GROUP BY conditional clause may be automatically added to the final structured query statement. For example, "GROUP BY region" and / or "GROUP BY product_type".

[0052] The present invention dynamically optimizes SQL query and visualization generation process through preference learning mechanism and user portrait, and can provide users with personalized query results and chart recommendations based on their historical query records, behavior habits and specific needs. It greatly improves user experience, enables products to meet diverse needs more efficiently, and increases user stickiness.

[0053] In step S105, the query results corresponding to the final structured query statement are visualized according to the user portrait to obtain a visualization result.

[0054] In the above steps, the user profile may include chart types related to the query results, and visualization types matching the user profile may be recommended to the user based on the user profile. For example, the user profile may be input into a pre-built visualization type recommendation model to obtain a recommended visualization type. A visualization type refers to a chart type used to visualize the query results. By using various graphic elements (such as lines, bar charts, pie charts, scatter plots, etc.), visualization charts can intuitively display the relationships and patterns between data, helping people to understand and analyze data more easily.

[0055] Initial display configuration parameters can be obtained according to the recommended visualization type, and visualization specifications can be generated according to the initial display configuration parameters. The initial display configuration parameters are parameters related to the visualization type determined according to the user portrait. For example, the initial display configuration parameters include that the chart type is a bar chart, the first axis represents time, and the second axis represents sales. The initial display configuration parameters can be determined according to the user portrait. The new display configuration parameters are obtained according to the user's modification intention in the interactive interface. Among them, the initial display configuration parameters include but are not limited to the chart type, the initial position parameter and the initial axial parameter. The initial position parameter is used to indicate the sorting position of the query object entity and the conditional clause in the structured query statement. The initial axial parameter refers to the first axial parameter and the second axial parameter defined according to the chart type. Assuming that the chart type is a line chart, the first axial is the x-axis, the first axial parameter is used to represent the time parameter, the second axial is the y-axis, and the second axial parameter is used to represent the target value corresponding to the statistical time. The initial axial parameter can be the axial specification parameter of the chart type that meets the user's habits and is pushed according to the user portrait.

[0056] In some embodiments, the visualization type recommendation model is constructed based on a model recommendation algorithm, such as collaborative filtering, finding users similar to the user to recommend visualization types, or content-based recommendation, recommending similar visualization types based on the characteristics of the user's past query results, etc. Axis parameters include, but are not limited to, axial parameters related to bar charts, axial parameters related to line charts, axial parameters related to pie charts, etc.

[0057] In some embodiments, the visualization results can also be exported or shared. According to the final generated visualization specification, the required charts or reports are displayed to the user to ensure that the results meet the user's expectations. The user can export the visualization chart to different formats (such as PDF, PNG, etc.). When the data source changes, in response to detecting that the data source has changed, the SQL query statement is regenerated and the visualization results are updated. After the data source changes, the real-time updated user portrait is detected, and in response to the updated user portrait, the visualization results are automatically adjusted, as well as the export method of the visualization results.

[0058] Assume that when the data source changes (such as uploading sales data for a new quarter), the system automatically identifies the data change and updates the visualization chart by updating the generated SQL query statement to ensure that the visualization chart provided to the user shows the latest data.

[0059] The SQL generation-driven visualization method based on large model preference learning proposed in the present invention performs preference optimization processing on structured query statements according to the user's historical query records, which greatly meets the user's personalized needs, reduces the steps of users adjusting visualization parameters multiple times to meet personalized needs, and effectively improves the efficiency of query result visualization processing.

[0060] Figure 2 FIG. 1 shows an exemplary flow chart of step S104 of some other embodiments of the present invention. It can be understood that Figure 2 The steps S2041-S2042 shown are a specific implementation of the aforementioned step S104, so the aforementioned Figure 1 The related features described can similarly apply here.

[0061] Step S2041: According to the query execution result of the user query history record, the position order and / or aggregation method of the query object entities included in the initial structured query statement are adjusted and / or redundant query object entities are deleted.

[0062] Step S2042: Optimize the initial customized parameters included in the initial structured query statement according to the preference screening condition to obtain a final structured query statement.

[0063] In the above steps, in the initial structured query statement, information with a high degree of relevance to the user profile is concentrated in the customized query parameters, and the user profile can also affect the customized parameters in the structured query statement. Customized parameters include but are not limited to cross-table join information, nested query information and / or data aggregation operation information. Cross-table join refers to merging data from two or more tables in an SQL query to facilitate simultaneous query and processing of information from different tables. In many application scenarios, data is often distributed in multiple tables. In order to obtain the required information, it is necessary to use a join operation to combine these tables. Cross-table joins can be used to implement complex query requirements and improve the efficiency of data query and processing. Nested query (Subquery) refers to embedding another query statement in an SQL query statement. Nested query can be used to execute a subquery before the main query is executed and return the result to the main query. Subqueries are usually used to generate temporary result sets to provide the required values ​​for the main query. Nested queries can appear in the SELECT, FROM, and WHERE parts of SQL query statements. Nested queries can be divided into different types according to different usage scenarios. For example, a subquery returns a single value (scalar value), which is usually used to calculate summary information or provide comparison values ​​in the WHERE clause. Nested queries can be used to meet complex query requirements and improve the flexibility of SQL queries. Aggregation is a commonly used calculation method in SQL queries to combine multiple rows of data into one or more summary results. Aggregation operations are often used with aggregate functions such as COUNT, SUM, AVG, MAX, and MIN. These functions can perform calculations on a set of values ​​and return a single result value.

[0064] When the initial customization parameters include cross-table join information, nested query information, and / or data aggregation operation information, the preference filter condition refers to the preference expression for cross-table join information, nested query information, and / or data aggregation operation information. For example, aggregation operations are often used with the GROUP BY clause to group data. When using GROUP BY, the query results are grouped by the unique values ​​of the specified column, and then the aggregation function is applied to each group. Assuming that the average salary of employees in each department is calculated, the preference expression of the data aggregation operation information is:

[0065] SELECT department_id,AVG(sa l ary)AS avg_sa l ary

[0066] FROM employees

[0067] GROUP BY department_id;

[0068] In this example, GROUP BY department_id groups the employees by department, and then uses the AVG function to calculate the average salary for each department.

[0069] Since the initial structured query statement is generated by a pre-trained large language model, its query structure is different from the user's preferences, and the query structure can be optimized according to the user's personal preference screening conditions. According to the preference screening conditions, the initial customized parameters contained in the initial structured query statement are optimized, including identifying the initial customized parameters contained in the initial structured query statement; when the initial customized parameters include cross-table join information, optimizing and adjusting according to the preference representation of the cross-table join information; when the initial customized parameters include nested query information, optimizing and adjusting according to the preference representation of the nested query information; when the initial customized parameters include data aggregation operation information, optimizing and adjusting according to the preference representation of the data aggregation operation information. Optimizing the initial customized parameters includes but is not limited to adjusting the position of the customized parameters, increasing or decreasing the customized parameters, etc.

[0070] Assume that the initial customized parameters only include "ORDER BY"; optimize the initial customized parameters according to the user profile to obtain the preferred customized query parameters, and the preferred customized parameters may include "ORDER BY" and "GROUP BY".

[0071] In some embodiments, based on the query execution results of the user query history records, the position order and / or aggregation method of the query object entities contained in the initial structured query statement are adjusted and / or redundant query object entities are deleted. Adjusting and optimizing the position order of the query object entities includes: adjusting the position of the query object entities, or deleting redundant query object entities, etc. The aggregation method can be adjusted by modifying the aggregation function and its parameters. Assuming that the total salary of employees is to be calculated instead of the average salary, the AVG function is replaced by the SUM function, and the SQL query statement obtained is:

[0072] SELECT department_id,COUNT(*)AS num_emp loyees,SUM(sa l ary)AS total_sa l aryFROM emp loyees

[0073] GROUP BY department_id;

[0074] When adjusting the aggregation method, you need to ensure that other parts of the query statement (such as SELECT, FROM, WHERE, GROUP BY, and ORDER BY) match the new aggregation method to ensure the accuracy and predictability of the query results.

[0075] The SQL generation-driven visualization method based on large model preference learning provided by the present invention can automatically optimize structured query statements according to user query history records and user portraits, thereby improving the accuracy of structured query statements and meeting the visualization requirements of structured query results.

[0076] Figure 3 FIG. 1 shows an exemplary flow chart of step S105 of some other embodiments of the present invention. It can be understood that Figure 3 The steps S3051-S3054 shown are a specific implementation of the above step S105, so the above Figure 1 The related features described can similarly apply here.

[0077] Step S3051, executing the final structured query statement to obtain a query result corresponding to the final structured query statement.

[0078] Step S3052: Generate a visualization specification corresponding to the query result according to the recommended visualization type. The visualization specification is a result of defining rules for basic attributes of graphic elements used to present the query result according to the visualization type.

[0079] In the above steps, after receiving the natural language query text, the server parses the natural language query text and constructs a user profile based on the read user query history. The user profile may include initial display configuration parameters. For example, the user is accustomed to using a blue bar chart to display sales data. The initial display configuration parameter is obtained as a blue bar chart, where the x-axis represents the time parameter and the y-axis represents the monthly sales data.

[0080] In the above steps, the visualization specification can be the result of defining rules for the basic attributes of the graphic elements used to present the query results according to the initial display configuration parameters. Assume that the initial display configuration parameters are a blue bar graph, the x-axis represents the time parameter, and the y-axis represents the monthly sales data. For example, when generating the visualization specification, according to the initial display configuration parameters, it can be defined that a blue bar graph is used, and the x-axis represents the month, and the y-axis represents the monthly sales data. When it is determined that a bar graph is used to display the sales data, a mapping relationship between the month and the first axial parameter of the bar graph and a mapping relationship between the sales data and the second axial parameter are defined. Then, the basic attributes of the graphic elements of the bar graph are set, for example, the color fill parameter of the bar graph is set to blue.

[0081] In some embodiments, the visualization specification may also include parameters such as the layout parameters of the chart, the mapping relationship of the data, the choice of color, etc. In some embodiments, the visualization specification may be dynamically adjusted according to the user preference characteristics in the user portrait. For example, after the user portrait is updated, the chart type in the user portrait is updated from a bar chart to a pie chart. When the user performs a new round of queries, the visualization specification is adjusted according to the updated user portrait. Alternatively, after the initial presentation of the visualization results, new display configuration parameters input by the user are received, and the visualization specification is adjusted according to the new display configuration parameters. For example, the new display configuration parameters are the size, color, label and other attributes of the chart, as well as binding the data in the query results to the chart.

[0082] The above automatically generates visualization specifications by recommending the most suitable data visualization charts. Users do not need to have programming or data analysis skills. The product can quickly generate intuitive charts, which effectively improves the efficiency of visualization processing of query results.

[0083] Furthermore, users can interactively adjust the visualization results in real time and update the adjustment content to the user portrait, making the data visualization process more convenient and flexible, greatly reducing the user's learning cost and operation threshold in data analysis and presentation.

[0084] Step S3053: Generate visualization code corresponding to the visualization specification according to the programming language rules.

[0085] Step S3054, running the visualization code to obtain a visualization result, where the visualization result is a result of visually presenting the query result according to the recommended visualization type.

[0086] In the above steps, the final structured query statement is executed to obtain the query result corresponding to the final structured query statement. For example, the final structured query statement is executed: SELECT sales_amount, sales_month FROM sales_data WHERE year=2020,product=e lect ORDER BY sales_month to obtain the sales data of electronic products in each month in 2020.

[0087] After obtaining the query results, a visualization specification corresponding to the query results is generated according to the recommended visualization type, and a visualization code is generated. By running the visualization code, a visualization result corresponding to the query result can be obtained. The visualization specification defines the basic properties of the graphic elements of the query results according to the recommended visualization type. After generating the preliminary visualization results, users can also make further adjustments based on the actual effect through the interactive interface, such as adjusting the coordinate axis range, changing the chart color scheme, optimizing the label text, etc., to ensure that the final visualization is both beautiful and informative.

[0088] Based on the visualization type recommended by the user portrait, personalized charts are provided to users, and visualization specifications and visualization codes are automatically generated based on the visualization type. Users do not need to have programming or data analysis capabilities, which reduces the operating cost of data visualization analysis and improves user satisfaction with the product.

[0089] In some embodiments, some interactive functions can be added, such as displaying detailed information by hovering the mouse, changing the chart type or displaying different data sets through a drop-down menu, etc., which enriches the operation types and can effectively improve user satisfaction with the product.

[0090] In some embodiments, after visually presenting the query results corresponding to the final structured query statement according to the user portrait, the method further includes: receiving input of new display configuration parameters; recording the new display configuration parameters into the user portrait to obtain an updated user portrait; visually presenting the query results corresponding to the final structured query statement according to the new display configuration parameters to obtain new visualization results.

[0091] In some embodiments, when a new display configuration parameter is received in the first interactive interface, the first interactive interface is a chat interactive interface, and the method further includes: receiving a new natural language text in the chat interactive interface, the new natural language text containing at least one keyword for adjusting the visualization type; in response to the new natural language text, converting the keyword into a new display configuration parameter; using the new display configuration parameter to update the initial display configuration parameter in the visualization specification to obtain a new visualization specification; generating a new visualization code corresponding to the new visualization specification according to the programming language rules; and running the new visualization code to obtain a new visualization result. Figure 4 As shown, in the interactive interface, A is the natural language query text entered by user, and B is the reply content from the artificial intelligence end.

[0092] For example, the user inputs a new natural language text "Change the chart type to a line chart". In response to the new natural language text input by the user, the server parses the new natural language text to obtain "Chart type" to change "Line chart", and converts "Line chart" into new display configuration parameters related to the visualization type. The new display configuration parameters can be, for example, axial parameters, that is, the first axial parameter is the month of the line chart, and the second axial parameter is the sales quantity corresponding to the month. The visualization specification is updated using the new axial parameters, and then a new visualization code is generated according to the visualization specification. Finally, the new visualization code is run to obtain a new visualization result, and the new visualization result is provided in the chat interaction interface with new feedback information.

[0093] In some embodiments, when receiving new display configuration parameters input in the second interactive interface, the second interactive interface is a graphical user interface, which includes an image display area for displaying visualization results and a parameter adjustment area for adjusting the display configuration parameters. The method includes: receiving a selection operation input in the parameter adjustment area for an operation item related to the display configuration parameters; in response to the selection operation, updating the initial display configuration parameters according to the operation item content corresponding to the selection operation to obtain new display configuration parameters; using the new display configuration parameters to update the initial display configuration parameters in the visualization specification to obtain a new visualization specification; generating a new visualization code corresponding to the new visualization specification according to the programming language rules; and running the new visualization code to obtain a new visualization result. Figure 5 As shown, in the interactive interface, user A inputs the natural language query text, B is the reply content of the artificial intelligence end, and the visualization results are provided to the user through the second interactive interface. User A can adjust the visualization results through the second interactive interface.

[0094] In some embodiments, after obtaining the new display configuration parameters, the method further includes: recording the new display configuration parameters into the user portrait data.

[0095] In some embodiments, the parameter adjustment area includes operation items corresponding to the data display range, operation items corresponding to the data display icon type, operation items corresponding to the icon element configuration parameters, and operation items corresponding to the visualization code, which is a program code used to implement the display of query results according to visualization specifications.

[0096] In some embodiments, the user usually does not directly participate in the adjustment process of the SQL query statement based on the user profile analyzed by the system. The system is more likely to "predict" and "automatically adjust" the generated charts and display content. For example, if the user's historical query records often select blue bar charts to show the trend changes of sales data, then when generating the chart, the bar chart will be automatically selected and blue will be used to meet the user's visual preferences.

[0097] When a user queries "2020 sales data", the system can set the default recommendation selection to be displayed as a line chart. After obtaining the user's historical query records, the subsequent recommended charts are automatically adjusted to blue bar charts based on the preferences in the user profile.

[0098] In some embodiments, after generating and displaying the visualization results, an interactive adjustment method (i.e., dynamic adjustment) may also be provided to further enable the user to customize and adjust the chart. The user can actively modify the generated visualization chart through the interactive interface and provide real-time feedback to the system. This is a dynamic adjustment process, and the user modifies the style, color, data display range, axis labels, etc. of the chart according to specific needs. This step is used to actively intervene in the visualization results. For example, the user can select different time intervals by dragging the timeline, or select other chart types through the menu (such as switching from a bar chart to a line chart).

[0099] After each adjustment to the visualization results, the adjusted display configuration parameters are recorded in the user portrait as user preference feedback, and the subsequent SQL queries and visualization results optimization are driven by the preference learning algorithm. For example, if a user selects a certain chart type multiple times, this type of chart will be given priority in subsequent recommendations, and even the SQL query structure will be directly optimized to speed up data return. The generated query statements and visualization specifications are adjusted according to the user's latest preferences. For example, if the user adjusts the time range of the chart to a specific interval each time, the system will automatically remember this preference, generate a matching SQL query, and adjust the time interval of subsequent charts. For example, after the user adjusts the chart type to a bar chart, the system records this preference and recommends bar charts instead of line charts in future queries, and the SQL query statements will also be automatically adjusted according to this change to match the needs of the chart (such as modifying aggregation logic, field order, etc.).

[0100] In other embodiments, the system displays the visualization results requested by the user to the user and supports the user to export or share. The charts or reports required by the user can be displayed according to the final generated visualization specifications to ensure that the results meet the user's expectations. Users can also export visualization charts to different formats (such as PDF, PNG, etc.). In some embodiments, the form of the displayed results can also be automatically adjusted by updating the user portrait in real time. For example, when the user prefers to export a PDF report, the system will automatically update the format of the exported report and adjust the data filtering conditions according to the user's preferences.

[0101] In some other embodiments, when a change in the data source is detected and the input natural language query text is received, an SQL query statement is generated according to the new data source and the displayed visualization results are updated. The user can also readjust the query and visualization specifications according to the new data source or business needs. For example, when the data source changes (such as the upload of sales data for a new quarter), the system automatically identifies the data change and updates the visualization chart through SQL query to ensure that the user can view the latest data.

[0102] After generating the visualization results, you can also interactively modify the visualization results through the interactive interface, and update the modified content to the user portrait, and then optimize the newly generated SQL query statement based on the user portrait. This can achieve dynamic adjustment of visualization, allowing the product to better adapt to changes in user needs and effectively improve the efficiency of visualization processing.

[0103] In summary, the SQL generation-driven visualization method based on large model preference learning proposed in the present invention automatically generates accurate SQL query statements by combining the intention recognition and preference learning mechanism of the large language model, and dynamically adjusts the data visualization specifications according to the user portrait, which greatly improves the accuracy of SQL query results, personalization of visualization results and convenience of interactive experience.

[0104] The SQL generation-driven visualization method based on large model preference learning proposed in the present invention can be widely used in various data-intensive fields, and is particularly suitable for products and projects such as data analysis, business intelligence (BI), big data analysis platforms, and enterprise data visualization dashboards.

[0105] Please refer to Figure 6 , Figure 6 FIG. 6 is a schematic diagram of a visualization device 600 driven by SQL generation based on large model preference learning according to an embodiment of the present invention. Figure 6 As shown, the device 600 includes:

[0106] The query text and query record acquisition module 601 is configured to acquire input natural language query text and user historical query records.

[0107] The user portrait generation module 602 is configured to generate a user portrait according to the user's historical query records.

[0108] The text parsing and statement generation module 603 is configured to parse the natural language query text using a pre-trained large language model to obtain the table name of the database table related to the user's query intention and the field name corresponding to the query object entity in the database table; based on the user portrait, the table name of the database table and the field name corresponding to the query object entity, an initial structured query statement is generated, and the initial structured query statement includes the query object entity in an initial sort and initial customized query parameters.

[0109] The query statement preference optimization module 604 is configured to perform preference optimization processing on the initial structured query statement according to the user query history record and the user portrait to obtain a final structured query statement, wherein the position order of the query object entities in the final structured query statement is obtained after optimizing and adjusting the query object entities in the initial order in the initial structured query statement according to the user query history record, and the preference customized query parameters in the final structured query statement are obtained after optimizing and adjusting the initial customized query parameters according to the user portrait;

[0110] The visualization display module 605 is configured to visualize the query results corresponding to the final structured query statement according to the user portrait to obtain a visualization result.

[0111] In some embodiments, the user portrait includes preference filtering conditions, and the query statement preference optimization module 604 is also configured to adjust the position order and / or aggregation method of the query object entities contained in the initial structured query statement and / or delete redundant query object entities according to the query execution results of the user query history record; optimize the initial customized parameters contained in the initial structured query statement according to the preference filtering conditions to obtain the final structured query statement.

[0112] In some embodiments, the user portrait includes initial display configuration parameters, and the query statement preference optimization module 604 is further configured to input the initial display configuration parameters, user query history records and initial structured query statements obtained according to the user portrait into a pre-built preference comparison model, and at least perform initial customization parameter adjustments on the initial structured query statement to obtain a final structured query statement, wherein the initial customization parameters include cross-table join information, nested query information and / or data aggregation operation information.

[0113] In some embodiments, the user portrait includes a recommended visualization type, and the visualization display module 605 is configured to execute the final structured query statement to obtain a query result corresponding to the final structured query statement; generate a visualization specification corresponding to the query result according to the recommended visualization type, and the visualization specification is the result of defining rules for basic attributes of graphic elements used to present the query results according to the visualization type; generate a visualization code corresponding to the visualization specification according to the programming language rules; run the visualization code to obtain a visualization result, and the visualization result is the result of visually presenting the query result according to the recommended visualization type.

[0114] In some embodiments, the device further includes: a parameter receiving module configured to receive input new display configuration parameters, the new display configuration parameters are used to adjust the visualization results. A visualization display module 605 is configured to visualize the query results corresponding to the final structured query statement according to the new display configuration parameters to obtain a new visualization result; and a user portrait updating module is configured to update the new display configuration parameters to the user portrait to obtain an updated user portrait.

[0115] In some embodiments, when the first interaction interface is a chat interaction interface, the parameter receiving module is configured to receive new natural language text input in the chat interaction interface, where the new natural language text contains at least one keyword for adjusting the visualization results; in response to the new natural language text, the keyword is converted into a new display configuration parameter.

[0116] In some embodiments, when the second interactive interface is a graphical user interface, the graphical user interface includes an image display area for displaying visualization results and a parameter adjustment area for adjusting display configuration parameters, and the parameter receiving module is configured to receive a selection operation input in the parameter adjustment area for an operation item related to the display configuration parameters; in response to the selection operation, the initial display configuration parameters are updated according to the content of the operation item corresponding to the selection operation to obtain new display configuration parameters.

[0117] The SQL generation-driven visualization device based on large model preference learning provided by the present invention performs preference optimization processing on the initial structured query statement through user query history records and user portraits, thereby improving the accuracy of the query results, reducing the manual modification process of the user, and improving the processing efficiency of the structured query statement. Furthermore, according to the visualization type recommended by the user portrait, the visualization result is dynamically adjusted, thereby improving the personalized recommendation and optimization effect.

[0118] Furthermore, through interactive functions, user modification information can be obtained to update the user portrait and influence the optimization results of SQL query statements driven by the preference learning algorithm. This can effectively improve the accuracy of the generated SQL query statements and continuously meet the personalized needs of visualization results.

[0119] Figure 7 FIG. 8 is a schematic block diagram of an electronic device 700 according to an embodiment of the present invention. Figure 7 As shown, the electronic device 700 may include a processor 701 and a memory 702. The memory 702 stores computer program instructions for executing a visualization method driven by SQL generation based on large model preference learning. When the computer program instructions are executed by the processor 701, the electronic device 700 executes the method according to the above combined with Figures 1 to 3 For example, in some embodiments, the electronic device 700 is used to implement a visualization method for SQL generation driven by large model preference learning, which can be referred to in the above embodiments and will not be described in detail here.

[0120] An electronic device provided by an embodiment of the present invention includes: a processor; and a memory storing computer program instructions implemented by a computer for executing a visualization method driven by SQL generation based on large model preference learning, and when the computer program instructions are executed by the processor, the electronic device executes the following Figures 1 to 3 Describe the method.

[0121] A computer-readable storage medium provided by an embodiment of the present invention includes computer program instructions implemented by a computer for executing a visualization method driven by SQL generation based on large model preference learning. When the computer program instructions are executed by a processor, the following is implemented: Figures 1 to 3 Describe the method.

[0122] It is known to those skilled in the art that the embodiments of the present invention may be implemented as a system, method or computer program product. Therefore, the present invention may be specifically implemented in the following forms, namely: complete hardware, complete software (including firmware, resident software, microcode, etc.), or a combination of hardware and software, generally referred to herein as a "circuit", "module", "unit" or "system". In addition, in some embodiments, the present invention may also be implemented in the form of a computer program product in one or more computer-readable media, which contains computer-readable program code.

[0123] Any combination of one or more computer-readable media may be used. A computer-readable medium may be a computer-readable signal medium or a computer-readable storage medium. A computer-readable storage medium may be, for example, but not limited to, an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, device or device, or any combination of the above. More specific examples (non-exhaustive examples) of computer-readable storage media may include, for example: an electrical connection with one or more wires, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the above. In this document, a computer-readable storage medium may be any tangible medium containing or storing a program that may be used by or in combination with an instruction execution system, device or device.

[0124] The program code embodied on the computer readable medium may be transmitted using any appropriate medium, including but not limited to wireless, wireline, optical fiber cable, RF, etc., or any suitable combination of the foregoing.

[0125] It should be understood that each box in the flowchart and / or block diagram and the combination of boxes in the flowchart and / or block diagram can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer or other programmable data processing device to produce a machine, and these computer program instructions are executed by a computer or other programmable data processing device to produce a device that implements the functions / operations specified in the boxes in the flowchart and / or block diagram.

[0126] Although multiple embodiments of the present invention have been shown and described herein, it will be apparent to those skilled in the art that such embodiments are provided by way of example only. Those skilled in the art may conceive of many changes, modifications, and alternatives without departing from the thought and spirit of the present invention. It should be understood that in the process of practicing the present invention, various alternatives to the embodiments of the present invention described herein may be adopted. The appended claims are intended to define the scope of protection of the present invention, and therefore cover equivalents or alternatives within the scope of these claims.

Claims

1. A SQL generation driven visualization method based on large model preference learning, characterized in that: The method includes: Obtain input natural language query text and user historical query records; Generate a user portrait based on the user's historical query records; The natural language query text is parsed using a pre-trained large language model to obtain a table name of a database table related to the user's query intention and a field name corresponding to the query object entity in the database table; an initial structured query statement is generated according to the user portrait, the table name of the database table and the field name corresponding to the query object entity, wherein the initial structured query statement includes the query object entity in an initial sort and initial customized query parameters; According to the user query history record and the user portrait, the initial structured query statement is subjected to preference optimization processing to obtain a final structured query statement, wherein the position order of the query object entities in the final structured query statement is obtained after optimizing and adjusting the query object entities in the initial order in the initial structured query statement according to the user query history record, and the preference customized query parameters in the final structured query statement are obtained after optimizing and adjusting the initial customized query parameters according to the user portrait; The query results corresponding to the final structured query statement are visualized according to the user portrait to obtain a visualization result.

2. The method according to claim 1, characterized in that The user profile includes a preference screening condition, which is a preference expression for cross-table join information, nested query information and / or data aggregation operation information. According to the user query history record and the user profile, the initial structured query statement is subjected to preference optimization processing, including: According to the query execution result of the user query history record, adjusting the position order and / or aggregation method of the query object entities included in the initial structured query statement and / or deleting redundant query object entities; The initial customized parameters included in the initial structured query statement are optimized according to the preference screening condition to obtain the final structured query statement.

3. The method according to claim 1, characterized in that The user portrait includes initial display configuration parameters, and preference optimization processing is performed on the initial structured query statement according to the user query history record and the user portrait, including: The initial display configuration parameters obtained according to the user portrait, the user query history record and the initial structured query statement are input into a pre-built preference comparison model, and at least the initial customized parameters of the initial structured query statement are adjusted to obtain a final structured query statement, wherein the initial customized parameters include cross-table join information, nested query information and / or data aggregation operation information.

4. The method according to claim 1, characterized in that: The user portrait includes a recommended visualization type, and the query result corresponding to the final structured query statement is visualized according to the user portrait, including: Executing the final structured query statement to obtain a query result corresponding to the final structured query statement; Generating a visualization specification corresponding to the query result according to the recommended visualization type, wherein the visualization specification is a result of rule definition of basic attributes of a graphic element used to present the query result according to the visualization type; Generate visualization code corresponding to the visualization specification according to programming language rules; The visualization code is run to obtain a visualization result, where the visualization result is a result of visually presenting the query result according to the recommended visualization type.

5. The method according to claim 1, characterized in that After visually presenting the query result corresponding to the final structured query statement according to the user portrait, the method further includes: receiving an input new display configuration parameter, wherein the new display configuration parameter is used to adjust the visualization result; Visually presenting the query result corresponding to the final structured query statement according to the new display configuration parameters to obtain a new visualization result; Update the new display configuration parameters to the user portrait to obtain an updated user portrait.

6. The method according to claim 5, characterized in that When the first interactive interface is a chat interactive interface, receiving input new display configuration parameters in the first interactive interface includes: Receiving new natural language text input in the chat interaction interface, wherein the new natural language text includes at least one keyword for adjusting the visualization result; In response to the new natural language text, the keywords are converted into the new presentation configuration parameters.

7. The method according to claim 5, characterized in that When the second interactive interface is a graphical user interface, the graphical user interface includes an image display area for displaying visualization results and a parameter adjustment area for adjusting display configuration parameters. Receiving new display configuration parameters input by the user in the second interactive interface includes: receiving a selection operation input in the parameter adjustment area for an operation item related to a display configuration parameter; In response to the selection operation, the initial display configuration parameters are updated according to the operation item content corresponding to the selection operation to obtain new display configuration parameters.

Images