A SQL intelligent generation and optimization system and method based on indicator semantic model

Abstract

This invention discloses a SQL intelligent generation and optimization system and method based on an indicator semantic model, relating to the field of data processing technology. It includes a business configuration layer for configuring basic information of indicators based on preset structured definition elements; a model assembly layer for assembling the basic information and lineage information into a hierarchical indicator semantic model according to the indicator's lineage and database connection information, and automatically adjusting the indicator semantic model structure using a dynamic semantic model optimization mechanism; and a model parsing and execution layer for parsing the indicator semantic model, generating corresponding structured query language fragments, and concatenating them to obtain a complete structured query language. The model parsing engine intelligently decomposes the semantic elements of the structured indicator semantic model, dynamically generating standardized SQL code blocks, improving query performance and reducing computational redundancy.

Description

Technical Field

[0001] This invention relates to the field of data processing technology, and in particular to an intelligent SQL generation system and method based on an indicator semantic model. Background Technology

[0002] The statements in this section are merely background information relating to this disclosure and do not necessarily constitute prior art.

[0003] In the fields of data analytics and business intelligence, metrics are measures that quantify business status. Traditional metric development suffers from the following problems: each metric requires manually written SQL, resulting in a large amount of redundant code and low reusability; multiple metric queries are prone to generating redundant SQL, leading to excessive database load; business logic is tightly coupled with SQL code, requiring SQL re-debugging when modifying metrics; business personnel cannot directly participate in metric definition, relying on developers to translate requirements; and security is insufficient, lacking fine-grained row and column-level access control for metric data access, posing a risk of sensitive data leakage.

[0004] Furthermore, existing solutions such as visual SQL generators only address basic syntax generation and lack the ability to semantically parse and optimize complex indicator logic. Summary of the Invention

[0005] To overcome the shortcomings of the prior art, this invention provides a SQL intelligent generation and optimization system and method based on an indicator semantic model. The system intelligently decomposes the semantic elements of the structured indicator semantic model through a model parsing engine, dynamically generates standardized SQL code blocks, improves query performance and reduces computational redundancy.

[0006] To achieve the above objectives, one or more embodiments of the present invention provide the following technical solutions:

[0007] In a first aspect, the present invention provides an intelligent SQL generation and optimization system based on an indicator semantic model, comprising:

[0008] The business configuration layer is used to configure basic information of indicators based on preset structured definition elements;

[0009] The model assembly layer is used to assemble the basic information and lineage information into a hierarchical indicator semantic model based on the lineage relationship of the indicators and database connection information, and to automatically adjust the indicator semantic model structure using a semantic model dynamic optimization mechanism.

[0010] The model parsing and execution layer is used to parse the indicator semantic model, generate corresponding structured query language fragments, and concatenate them to obtain a complete structured query language.

[0011] A further technical solution is that the preset structured definition elements include the indicator source dataset, filtering conditions, dimensions, and metrics.

[0012] A further technical solution involves filtering the basic information of the configured indicators using row-level and column-level permission policies. The row-level permission policy controls data querying within the permission range, while the column-level permission policy controls field display within the permission range.

[0013] A further technical solution is that the dynamic optimization mechanism of the semantic model automatically adjusts the model structure and query logic based on real-time changes in data during the assembly of the indicator semantic model.

[0014] Further technical solutions also utilize machine learning models, combined with historical query logs, to automatically identify high-frequency query patterns. For multi-table joins involved in high-frequency query patterns, the relevant datasets are pre-widened and pre-aggregated.

[0015] A further technical solution involves optimizing the detection of the homogeneity of indicators when querying multiple indicators. If multiple indicators are detected to originate from the same star schema, the query conditions are automatically merged to reduce the number of table joins and subqueries.

[0016] A further technical solution involves using an object-oriented tree structure to traverse the model during the semantic model parsing process, gradually generating corresponding SQL fragments, and finally concatenating them into a complete SQL query statement.

[0017] Secondly, this invention provides a method for intelligent SQL generation and optimization based on an indicator semantic model, including:

[0018] Basic information based on preset structured definition of element configuration indicators;

[0019] Based on the kinship of the indicators and database connection information, the basic information and kinship information are assembled into a hierarchical indicator semantic model, and the structure of the indicator semantic model is automatically adjusted by a dynamic optimization mechanism of the semantic model.

[0020] The semantic model of the indicator is parsed to generate corresponding structured query language fragments, which are then concatenated to obtain a complete structured query language.

[0021] Thirdly, the present invention provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the steps of the SQL intelligent generation and optimization method based on an indicator semantic model as described in the second aspect.

[0022] Fourthly, the present invention provides a computer device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the program to implement the steps of the SQL intelligent generation and optimization method based on an indicator semantic model as described in the second aspect.

[0023] The above one or more technical solutions have the following beneficial effects:

[0024] This invention parses the basic information of user-defined metric configurations and transforms it into a structured metric semantic model. A model parsing engine intelligently decomposes semantic elements, dynamically generating standardized SQL code blocks. After syntax optimization, these blocks are combined into complete executable statements, ultimately executing precise queries on the target database. Leveraging metric lineage tracing technology, it automatically associates dependent datasets and implements semantic model optimization strategies in cross-fact table join scenarios. This intelligently derives the optimal table join path, significantly reducing the complexity of multi-table join operations and achieving an exponential improvement in query performance.

[0025] This invention utilizes a semantic model dynamic optimization mechanism to perceive changes in data sources in real time, automatically adjust aggregation logic and model structure, and reduce redundant calculations. Furthermore, based on lineage analysis, it intelligently merges queries for similar indicators, combining multiple table scans into a single scan, reducing temporary table creation and join operations, and minimizing redundant database calculations. It also incorporates machine learning to predict high-frequency query patterns, improving response speed. Through these measures, the system's query performance and efficiency are significantly improved.

[0026] This invention dynamically embeds row-level and column-level permission policies during the SQL generation stage, eliminating the need for secondary filtering at the application layer and achieving field-level data isolation. Dynamic row- and column-level permission control ensures the isolation of sensitive data, intercepts more than 99% of SQL injection attacks, and enhances security.

[0027] This invention greatly improves the system's compatibility with heterogeneous databases by automatically adjusting SQL statements to adapt to the syntax and features of different databases. Attached Figure Description

[0028] The accompanying drawings, which form part of this invention, are used to provide a further understanding of the invention. The illustrative embodiments of the invention and their descriptions are used to explain the invention and do not constitute an improper limitation of the invention.

[0029] Figure 1 This is a framework diagram of an SQL intelligent generation and optimization system based on an indicator semantic model according to an embodiment of the present invention. Detailed Implementation

[0030] It should be noted that the following detailed descriptions are exemplary and intended to provide further illustration of the invention. Unless otherwise specified, all technical and scientific terms used herein have the same meaning as commonly understood by one of ordinary skill in the art to which this invention pertains.

[0031] It should be noted that the terminology used herein is for the purpose of describing particular embodiments only and is not intended to limit the scope of exemplary embodiments according to the invention. As used herein, the singular form is intended to include the plural form as well, unless the context clearly indicates otherwise. Furthermore, it should be understood that when the terms "comprising" and / or "including" are used in this specification, they indicate the presence of features, steps, operations, devices, components, and / or combinations thereof.

[0032] Where there is no conflict, the embodiments and features in the embodiments of the present invention can be combined with each other.

[0033] Terminology Explanation:

[0034] Metric semantic model: A structured descriptive model that defines the standardized calculation logic, data sources, and relationships of metrics from a business perspective.

[0035] Model parsing engine: The core module that dynamically generates SQL statements from the results of semantic model parsing, supporting syntax optimization and logical merging.

[0036] Datasets: Datasets are created through database tables, and the relationships between datasets are constructed to form a data model. The data model is an important foundation for the relationship between indicators and dimensions in the indicator platform.

[0037] Combined query merging: An optimization strategy that intelligently analyzes multiple indicator query conditions and merges operations on the same dataset to improve execution efficiency.

[0038] Example 1

[0039] like Figure 1 As shown in the figure, this embodiment discloses an intelligent SQL generation and optimization system based on an indicator semantic model. The method includes the following steps:

[0040] The business configuration layer is used to configure basic information of indicators based on preset structured definition elements;

[0041] The model assembly layer is used to assemble the basic information and lineage information into a hierarchical indicator semantic model based on the lineage relationship of the indicators and database connection information, and to automatically adjust the indicator semantic model structure using a semantic model dynamic optimization mechanism.

[0042] The model parsing and execution layer is used to parse the indicator semantic model, generate corresponding structured query language fragments, and concatenate them to obtain a complete structured query language.

[0043] In this embodiment, the system adopts a front-end and back-end separation architecture. The front-end is built on the Vue.js framework to construct the interactive interface, while the back-end service uses Java to implement the core business logic, combined with JavaScript to enhance dynamic data processing capabilities. The system includes a business configuration layer, a model assembly layer, and a model parsing and execution layer. On this basis, the system introduces innovative modules such as dynamic row and column permission control, a semantic model dynamic optimization engine, an SQL security verification module, and an SQL adaptive execution plan engine, forming a complete indicator calculation and optimization system.

[0044] (1) Business configuration layer

[0045] Users configure basic information about metrics (such as the source dataset, filtering conditions, dimensions, and measures) through the front-end page and save it as structured data. During the configuration process, the system supports dynamically binding row-level permission policies (such as department filtering) and column-level permission policies (such as field sensitivity), ensuring that permission filtering conditions are automatically injected into the generated SQL statements before execution to protect data security and avoid data compliance risks. In addition, the system is compatible with the syntax of mainstream databases (MySQL, Oracle, Doris, etc.), improving the universality and scalability of metric configuration.

[0046] Furthermore, row-level (data) permission policies control data queries within the scope of permissions. For example, each user can only view the data they submitted and cannot view the data of other users. Column-level permission policies control the display of fields within the scope of permissions. For example, fields related to private information such as personal mobile phone numbers and home addresses are not viewable by ordinary users. Based on the RBAC-based permission management system, the metric query SQL will dynamically concatenate permission-related fields. When a user performs a data query, the system automatically parses the user's role information and performs dynamic matching.

[0047] Atomic metric configuration consists of a combination of dimensions and measures, with its basic elements all derived from a data source-driven dataset model. The dataset model is a relational model of datasets; a dataset is a logical abstraction of database tables, and building the model is a virtual representation of multi-table joins. The system provides end-to-end autonomous configuration capabilities, supporting the entire process from data source access to the definition of common dimensions. Users can freely select correlation factors to configure metric combinations, that is, based on basic metrics (atomic metrics), they can combine them with expressions to form combined metrics, achieving a flexible and scalable metric management system.

[0048] To ensure heterogeneous database compatibility, at the business configuration layer, the system automatically adjusts the semantic model assembly rules based on the data source type of the metrics. That is, the model parsing engine has a built-in assembly template. After identifying the data source type, it automatically assembles SQL according to the corresponding rule template and syntax rule library to ensure that the generated SQL statements conform to the standard specifications of the corresponding database.

[0049] (2) Model assembly layer

[0050] Based on the indicator identifier, the system queries the indicator lineage and database connection information. The system then assembles the configuration and lineage information into a hierarchical indicator semantic model using an outer query + subquery structure. During this process, the system dynamically optimizes the semantic model to adapt to changes in the data source (such as the addition of new fields or a surge in data volume), automatically adjusting the model structure. For example, when a new field is detected, the system binds it to the relevant indicator; when the data volume surges, the system optimizes the aggregation logic to reduce computational overhead.

[0051] Furthermore, the dynamic optimization mechanism refers to the system's ability to automatically adjust the model structure and query logic based on real-time changes in metadata (such as added fields or changes in data volume) during the assembly of the indicator semantic model, thereby ensuring the accuracy and efficiency of queries. Specifically, this includes:

[0052] Field adaptability: When new fields appear in the data source, the system will automatically detect them and bind them to relevant metrics without manual intervention, ensuring that the metric model is always synchronized with the data source.

[0053] Query structure adjustment: Based on changes in the data table structure, the system will dynamically adjust the SQL query method (such as the nesting relationship between outer queries and subqueries) to adapt to the new data structure.

[0054] Performance optimization: When a surge in data volume or a decrease in query performance is detected, the system will automatically optimize the query logic (such as adjusting the aggregation method, adding indexes, splitting large tables, etc.) to improve query efficiency and reduce resource consumption.

[0055] Furthermore, the aggregation logic is optimized to reduce computational overhead, specifically as follows:

[0056] (1) Analyze the aggregation method; analyze the current aggregation logic (such as SUM, AVG, COUNT, etc.) and determine whether there are inefficient operations such as full table scans and duplicate calculations.

[0057] (2) Select optimization strategies; Based on the analysis results, the system automatically selects appropriate optimization strategies, including but not limited to: Introducing partitioned aggregation: Partitioning the large table by time, region, etc., first aggregating within the partition, and then summarizing the results to reduce the amount of calculation per operation; Pre-aggregating intermediate tables: Pre-calculating and storing commonly used aggregation results in an intermediate table, which can be directly read during queries to avoid redundant calculations; Adding indexes: Adding appropriate indexes to the aggregation fields to accelerate aggregation operations; Dynamically adjusting the SQL structure: For example, changing the original nested subqueries to WITH statements (common table expressions) to improve SQL execution efficiency.

[0058] (3) Automatic adjustment and verification. The system automatically generates and replaces the optimized SQL or model structure, and compares the query performance before and after optimization to ensure that the optimization is effective.

[0059] Furthermore, the system analyzes historical query logs using machine learning to predict high-frequency query patterns (such as "quarterly sales revenue"), pre-broadening and pre-aggregating relevant datasets to optimize the model structure and significantly reduce latency for the first query. For multi-metric query scenarios, the system optimizes homogeneity detection through cross-metric merging. If multiple metrics are identified as originating from the same star schema, query conditions are automatically merged, reducing table joins and subqueries, thereby improving execution efficiency. Specifically, the system analyzes historical query logs, continuously collecting and organizing user query logs, including the SQL text of each query, the tables and fields involved, aggregation methods, query times, and other information. Through structured processing of these logs, the system can extract feature vectors for each query. Subsequently, the system uses machine learning models such as cluster analysis (e.g., the K-Means algorithm) to analyze these feature vectors and automatically identify frequently occurring query patterns. For example, the system can detect that queries like "quarterly sales revenue" or "monthly active users" appear repeatedly in historical logs. The input to the machine learning model is structured query feature data, and the output is a structural description of several high-frequency query patterns. For high-frequency queries involving multiple table joins, the system pre-widens the data in the relevant tables. This is achieved by generating a wide table through pre-join operations, consolidating commonly used fields into a single table, thus reducing the need for multi-table joins in subsequent queries. For high-frequency aggregation operations, the system pre-calculates the aggregation results and stores them in intermediate tables or materialized views. Users can directly read these pre-aggregated results during queries without real-time calculations, significantly reducing latency for the first query. Furthermore, the system automatically creates or optimizes indexes for fields involved in high-frequency queries and can cache high-frequency query results, further improving query response speed.

[0060] The indicator lineage fully records the traceability map of data assets, forming a complete mapping chain of "data source → dataset → parent indicator → current indicator". The mapping relationship between the data source and the dataset ensures the reliability of the original data, while the transformation process from dataset to indicator ensures the accuracy of the calculation logic. The dual verification mechanism builds a traceable and auditable data indicator system.

[0061] The system is equipped with an intelligent semantic model dynamic optimization engine that automatically activates the lineage analysis module when executing indicator queries. By intelligently identifying multi-indicator scenarios in datasets with the same source, the system reconstructs the assembly paradigm of the semantic model, replacing the traditional multi-level nested architecture with a flattened modeling approach. This optimization strategy effectively reduces table join operations and subquery nesting levels during SQL parsing, improving query response speed by over 30% while maintaining the integrity of business logic. Specifically, the optimization rules of the intelligent semantic model dynamic optimization engine are as follows: For multi-indicator query scenarios, the system automatically analyzes the lineage relationship of the multiple indicators queried by the user, determining whether these indicators all originate from the same fact table and its related dimension tables. If multiple indicators are detected to belong to the same star schema, the system automatically merges the query conditions for these indicators, generating a unified SQL query. In this way, what originally required multiple independent table joins and subqueries can be completed through a single joint query of the fact table and dimension tables, greatly reducing the number of table joins and subqueries and significantly improving query execution efficiency.

[0062] Furthermore, the star schema is a common data warehouse modeling approach, consisting of a central fact table and multiple dimension tables, with a structure resembling a star. Specifically, the fact table stores metric data for business events (such as sales revenue, order count, etc.), typically containing foreign keys linking to the dimension tables; the dimension tables store descriptive information (such as time, region, product, etc.), used for grouping and filtering the fact table data.

[0063] Example: Three metrics from the same dataset need to be queried simultaneously, each with different query conditions. After optimization, the number of database scans was reduced from 3 to 1, temporary table creation operations were reduced by 2, and query conditions were isolated using CASE statements. This significantly reduced database I / O operations while preserving the original business semantics.

[0064] Furthermore, the system will reconstruct the semantic model assembly paradigm, adopting a flattened modeling approach instead of the traditional multi-level nested architecture. Specifically, during the semantic model assembly process, the system will no longer use traditional multi-level nested subqueries or complex SQL structures with layered relationships. Instead, it will directly join and aggregate all related data tables and fields within the same query level. In other words, the system will complete the metric data that originally required multiple nested subqueries in a single SQL statement through a single Join and aggregation operation. This significantly reduces the number of table joins and subquery nesting levels in the SQL statement, lowering the database's parsing and execution burden.

[0065] (3) Model parsing and execution layer

[0066] The model parsing engine is the core component that enables automatic conversion from semantic models to SQL. After users configure their query requirements through natural language or visual methods, the system maps these requirements to an internal semantic model. The model parsing engine is responsible for parsing and traversing this semantic model, converting its various components (such as query fields, filtering conditions, grouping conditions, sorting rules, etc.) into corresponding SQL fragments, and finally concatenating them into a complete SQL query statement.

[0067] During the parsing process, the model parsing engine typically uses an object-oriented tree structure to organize and manage the semantic model. Each semantic model is parsed into a structure or object containing multiple nodes, with each node representing a query element (such as fields, conditions, groupings, etc.). The model parsing engine recursively traverses these nodes, progressively generating SQL fragments such as SELECT, WHERE, GROUP BY, and ORDER BY.

[0068] Meanwhile, the model parsing engine, in conjunction with the system's dynamic row and column permission control mechanism, dynamically adjusts the generated SQL based on the current user's role and permissions. For example, it automatically adds conditions such as WHERE region='sales_department' for different users, or excludes sensitive fields from the SELECT statement (such as SELECT name, department FROM employee) to achieve fine-grained security control of data access. The specific implementation steps are as follows:

[0069] (1) Model structuring

[0070] The system first parses the user's query into an internal semantic model object. This object typically contains attributes such as a list of fields (SELECT), filtering conditions (WHERE), grouping conditions (GROUPBY), and sorting conditions (ORDER BY), with each attribute containing specific fields or condition nodes.

[0071] (2) Traverse each part of the model

[0072] The system uses a recursive approach to iterate through each part of the model object sequentially. For example, it iterates through the SELECT field list, processing each field that needs to be queried; it iterates through the WHERE conditions, processing each filtering rule; and it iterates through the GROUP BY and ORDER BY conditions, collecting the grouping and sorting fields.

[0073] (3) Generate SQL fragments

[0074] When traversing each part, the system generates the corresponding SQL fragment based on the node type.

[0075] (4) Dynamic access control

[0076] During the iteration process, the system dynamically adjusts the SQL fragments based on the user's role and permission information. For example, for users with row-level permission control, a condition such as region='sales_department' is automatically added to the WHERE clause.

[0077] (5) Construct the complete SQL statement

[0078] The system concatenates the generated SQL fragments according to the SQL syntax order to form a complete SQL query statement.

[0079] Based on this, the system dynamically adjusts the SQL execution path through the SQL adaptive execution plan engine. The SQL adaptive execution plan engine's processing specifically includes: real-time monitoring of the database's CPU, memory, I / O and other resource load status; prioritizing index scans to reduce the overhead of full table scans; optimizing the connection order and query path by combining database metadata; dynamically adjusting the SQL execution strategy to adapt to the current resource status; and further improving resource utilization and reducing execution latency through the above processing.

[0080] After generating the SQL statement, the system performs dual verification through the SQL security verification module: first, it uses regular expression matching to detect dangerous operations in the SQL (such as DROP, DELETE, etc.) to prevent malicious operations; at the same time, it uses AST syntax tree analysis to parse the SQL structure, mark high-risk operations (such as UNION injection attacks), and automatically generate a secure version of the SQL statement to avoid potential threats.

[0081] Seamless integration of permission rules is achieved based on an indicator semantic model, dynamically embedding data filtering conditions during the SQL generation stage through a model parsing engine. A typical application scenario is: when a user binds a "department-level" row-level data permission control policy to the "sales data" indicator, the system will automatically append the corresponding conditional expression during SQL compilation.

[0082] WHERE department_id = CURRENT_USER_DEPARTMENT() .

[0083] Seamless integration of permission rules is achieved based on an indicator semantic model. Specifically, the permission rules for each user or user group (such as accessible organizations, departments, regions, data levels, etc.) are first stored in a structured manner in the permission management module. These permission rules are associated with the indicator semantic model, forming a user-permission-indicator mapping relationship. When a user initiates a query request, the system automatically retrieves the corresponding permission rules based on their identity information (such as role, department, etc.). Subsequently, the model parsing engine dynamically transforms these permission rules into specific data filtering conditions during the parsing of the indicator semantic model and the generation of SQL statements. For example, if a user is only allowed to access data in the "East China" region, the system will automatically generate and insert a filter condition such as WHEREregion = 'East China'. The specific implementation process is as follows:

[0084] (1) Extraction permission rules

[0085] Once a user request arrives, the system queries the corresponding permission rules (such as accessible fields, row-level data ranges, etc.) based on the user's identity.

[0086] (2) Parsing the semantic model

[0087] The system parses the user's query requirements into an indicator semantic model, clarifying the fields, tables, aggregation methods, etc. that need to be queried.

[0088] (3) Generate permission conditions

[0089] The model parsing engine automatically generates corresponding SQL filtering conditions based on the extracted permission rules. For example, row permissions will generate `region = 'East China'`, and column permissions will remove sensitive fields from the SELECT list.

[0090] (4) Dynamically embedding SQL fragments

[0091] During the SQL generation phase, the system dynamically embeds the above permission conditions as WHERE clauses or SELECT field filters into the final SQL statement. Row permissions: add permission conditions to the WHERE clause; Column permissions: remove fields without permissions from the SELECT clause.

[0092] (5) SQL statement generation and validation

[0093] Generate complete SQL statements with permission filtering and perform legality and security checks through the SQL security verification module.

[0094] At the model parsing and execution layer, optimization strategies can be dynamically adjusted according to the characteristics of heterogeneous databases. For example, in high-concurrency scenarios, some databases may prefer to use temporary tables to cache intermediate results, while other databases may directly use in-memory computing to improve speed.

[0095] Example 2

[0096] This embodiment discloses a method for intelligent SQL generation and optimization based on an indicator semantic model, including:

[0097] Basic information based on preset structured definition of element configuration indicators;

[0098] Based on the kinship of the indicators and database connection information, the basic information and kinship information are assembled into a hierarchical indicator semantic model, and the structure of the indicator semantic model is automatically adjusted by a dynamic optimization mechanism of the semantic model.

[0099] The semantic model of the indicator is parsed to generate corresponding structured query language fragments, which are then concatenated to obtain a complete structured query language.

[0100] Example 3

[0101] The purpose of this embodiment is to provide a computing device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the program to implement the steps of the method of Embodiment 2.

[0102] Example 4

[0103] The purpose of this embodiment is to provide a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, performs the steps of the method of Embodiment 2.

[0104] The steps and methods involved in the apparatuses of Embodiments 3 and 4 above correspond to those in Embodiment 1. For specific implementation details, please refer to the relevant description section of Embodiment 1. The term "computer-readable storage medium" should be understood as a single medium or multiple media including one or more instruction sets; it should also be understood as including any medium capable of storing, encoding, or carrying an instruction set for execution by a processor and enabling the processor to perform any of the methods in this invention.

[0105] Those skilled in the art will understand that the modules or steps of the present invention described above can be implemented using general-purpose computer devices. Optionally, they can be implemented using computer-executable program code, thereby allowing them to be stored in a storage device for execution by a computer device, or they can be fabricated as separate integrated circuit modules, or multiple modules or steps can be fabricated as a single integrated circuit module. The present invention is not limited to any particular combination of hardware and software.

[0106] The above description is merely a preferred embodiment of the present invention and is not intended to limit the invention. Various modifications and variations can be made to the present invention by those skilled in the art. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of the present invention should be included within the scope of protection of the present invention.

[0107] While the specific embodiments of the present invention have been described above in conjunction with the accompanying drawings, this is not intended to limit the scope of protection of the present invention. Those skilled in the art should understand that various modifications or variations that can be made by those skilled in the art without creative effort based on the technical solutions of the present invention are still within the scope of protection of the present invention.

Claims

1. A SQL intelligent generation and optimization system based on an indicator semantic model, characterized in that, include: The business configuration layer is used to configure basic information of indicators based on preset structured definition elements; The model assembly layer is used to assemble the basic information and lineage information into a hierarchical indicator semantic model based on the lineage relationship of the indicators and database connection information, and to automatically adjust the indicator semantic model structure using a semantic model dynamic optimization mechanism. The model parsing and execution layer is used to parse the indicator semantic model, generate corresponding structured query language fragments and splice them together to obtain a complete structured query language. During the semantic model parsing process, an object-oriented tree structure is used to traverse the model, gradually generating corresponding SQL fragments, which are then assembled into a complete SQL query statement.

2. The SQL intelligent generation and optimization system based on an indicator semantic model as described in claim 1, characterized in that, The preset structured definition elements include the indicator source dataset, filtering conditions, dimensions, and metrics.

3. The SQL intelligent generation and optimization system based on an indicator semantic model as described in claim 1, characterized in that, When configuring basic information for metrics, the basic information is filtered using row-level and column-level permission policies. The row-level permission policy controls data querying within the permission scope, and the column-level permission policy controls field display within the permission scope.

4. The SQL intelligent generation and optimization system based on an indicator semantic model as described in claim 1, characterized in that, The dynamic optimization mechanism of the semantic model is to automatically adjust the model structure and query logic based on real-time changes in data during the assembly of the indicator semantic model.

5. The SQL intelligent generation and optimization system based on an indicator semantic model as described in claim 1, characterized in that, Furthermore, by using machine learning models and combining historical query logs, high-frequency query patterns are automatically identified. For multi-table joins involved in high-frequency query patterns, the relevant datasets are pre-widened and pre-aggregated.

6. The SQL intelligent generation and optimization system based on an indicator semantic model as described in claim 1, characterized in that, When querying multiple indicators, the system optimizes the detection of the homogeneity of indicators by merging across indicators. If multiple indicators are detected to originate from the same star schema, the query conditions are automatically merged to reduce the number of table joins and subqueries.

7. A method for intelligent SQL generation and optimization based on an indicator semantic model, characterized in that, include: Basic information based on preset structured definition of element configuration indicators; Based on the kinship of the indicators and database connection information, the basic information and kinship information are assembled into a hierarchical indicator semantic model, and the structure of the indicator semantic model is automatically adjusted by a dynamic optimization mechanism of the semantic model. The semantic model of the indicator is parsed to generate corresponding structured query language fragments, which are then concatenated to obtain a complete structured query language. During the semantic model parsing process, an object-oriented tree structure is used to traverse the model, gradually generating corresponding SQL fragments, which are then assembled into a complete SQL query statement.

8. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the program is executed by the processor, it implements the steps in the SQL intelligent generation and optimization method based on the indicator semantic model as described in claim 7.

9. A computer device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the program, it implements the steps in the SQL intelligent generation and optimization method based on the indicator semantic model as described in claim 7.

Images