SQL intelligent generation and optimization system and method based on index semantic model

Abstract

The invention discloses an intelligent SQL generation and optimization system and method based on an index semantic model, and relates to the technical field of data processing, and the system comprises a business configuration layer which is used for configuring basic information of indexes based on preset structured definition elements; the model assembly layer is used for assembling the basic information and the blood relationship information into a layered index semantic model according to the blood relationship of the indexes and database connection information, and automatically adjusting the structure of the index semantic model by adopting a semantic model dynamic optimization mechanism; and the model analysis and execution layer is used for analyzing the index semantic model, generating corresponding structured query language segments and splicing the structured query language segments to obtain a complete structured query language. Semantic elements of the structured index semantic model are intelligently disassembled through the model analysis engine, standardized SQL code blocks are dynamically generated, the query performance is improved, and calculation redundancy is reduced.

Description

Technical Field

[0001] The present invention relates to the field of data processing technology, and in particular to a system and method for intelligently generating SQL based on an indicator semantic model. Background Art

[0002] The statements in this section merely provide background information related to the present disclosure and do not necessarily constitute prior art.

[0003] In the field of data analysis and business intelligence, indicators are quantitative measures of business status. Traditional indicator development suffers from the following issues: Each indicator requires manual SQL coding, resulting in a large amount of redundant code and low reusability; multiple indicator combination queries easily generate redundant SQL, leading to excessive database load; business logic is tightly coupled with SQL code, requiring SQL re-debugging when modifying indicators; business personnel cannot directly participate in indicator definition, relying on developers to translate requirements; and security is insufficient, with a lack of fine-grained row and column-level permission control for indicator data access, posing the risk of sensitive data leakage.

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

[0005] To overcome the deficiencies of the above-mentioned prior art, the present invention provides a SQL intelligent generation and optimization system and method based on an indicator semantic model. The model parsing engine intelligently decomposes the semantic elements of the structured indicator semantic model, 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: In a first aspect, the present invention provides an SQL intelligent generation and optimization system based on an indicator semantic model, comprising: The business configuration layer is used to configure the basic information of indicators based on preset structured definition elements; The model assembly layer is used to assemble the basic information and the lineage information into a hierarchical indicator semantic model according to the indicator's lineage relationship and database connection information, and automatically adjust the indicator semantic model structure using a dynamic optimization mechanism of the semantic model; The model parsing and execution layer is used to parse the indicator semantic model, generate corresponding structured query language fragments and splice them to obtain a complete structured query language.

[0007] In a further technical solution, the preset structured definition elements include indicator source data sets, screening conditions, dimensions and metrics.

[0008] A further technical solution is to filter the basic information through row-level permission strategy and column-level permission strategy when configuring the basic information of the indicator. The row-level permission strategy controls the data query within the permission range, and the column-level permission strategy controls the field display within the permission range.

[0009] As a further technical solution, the dynamic optimization mechanism of the semantic model is to automatically adjust the model structure and query logic according to the real-time changes of the data during the process of assembling the indicator semantic model.

[0010] A further technical solution is to automatically identify high-frequency query patterns through machine learning models combined with historical query logs, and pre-widen and pre-aggregate relevant data sets for multi-table associations involved in high-frequency query patterns.

[0011] A further technical solution is to optimize the homology of detection indicators through cross-indicator merging when querying multiple indicators. If it is detected that multiple indicators come from the same star model, the query conditions are automatically merged to reduce the number of table connections and subqueries.

[0012] A further technical solution is to traverse the model in an object-oriented tree structure during the parsing of the indicator semantic model, gradually generate corresponding SQL fragments, and finally splice them into a complete SQL query statement.

[0013] In a second aspect, the present invention provides a method for intelligently generating and optimizing SQL statements based on an indicator semantic model, comprising: Basic information of configuration indicators based on preset structured definition elements; According to the indicator's lineage relationship and database connection information, the basic information and lineage information are assembled into a hierarchical indicator semantic model, and the indicator semantic model structure is automatically adjusted using a dynamic optimization mechanism of the semantic model; The indicator semantic model is parsed to generate corresponding structured query language fragments and spliced ​​together to obtain a complete structured query language.

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

[0015] In a fourth aspect, the present invention provides a computer device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the program, the steps of the method for intelligent SQL generation and optimization based on an indicator semantic model as described in the second aspect are implemented.

[0016] One or more of the above technical solutions have the following beneficial effects: The present invention parses the basic information of user-defined indicators and converts them into a structured indicator semantic model. The model parsing engine intelligently decomposes semantic elements, dynamically generates standardized SQL code blocks, and combines them into complete executable statements after syntax optimization, ultimately executing precise queries on the target database. Relying on indicator lineage tracking technology to automatically associate dependent data sets, the semantic model optimization strategy is implemented in scenarios involving cross-fact table associations, and the optimal table association path is intelligently derived, significantly reducing the complexity of multi-table join operations, thereby achieving an exponential improvement in query performance.

[0017] The present invention uses a dynamic optimization mechanism of semantic models to perceive changes in data sources in real time, automatically adjust aggregation logic and model structure, and reduce redundant calculations; and based on lineage analysis, intelligently merges homologous indicator queries, merges multiple table scans into a single scan, reduces temporary table creation and connection operations, and reduces redundant calculations in the database; and combines machine learning to predict high-frequency query patterns to improve response speed; the above content significantly improves the system query performance and efficiency.

[0018] The present invention dynamically embeds row-level and column-level permission policies during the SQL generation phase, 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.

[0019] The present invention automatically adjusts SQL statements to adapt to the syntax and characteristics of different databases, which greatly improves the compatibility of the system with heterogeneous databases. BRIEF DESCRIPTION OF THE DRAWINGS

[0020] The accompanying drawings, which constitute a part of the present invention, are used to provide a further understanding of the present invention. The exemplary embodiments of the present invention and their descriptions are used to explain the present invention and do not constitute improper limitations on the present invention.

[0021] Figure 1 This is a framework diagram of an SQL intelligent generation and optimization system based on an indicator semantic model in an embodiment of the present invention. DETAILED DESCRIPTION

[0022] It should be noted that the following detailed descriptions are exemplary and intended to provide further explanation of the present invention. Unless otherwise specified, all technical and scientific terms used herein have the same meaning as commonly understood by those skilled in the art to which the present invention belongs.

[0023] It should be noted that the terms used herein are only for describing specific embodiments and are not intended to limit the exemplary embodiments according to the present invention. As used herein, unless the context clearly indicates otherwise, the singular form is intended to include the plural form. In addition, it should be understood that when the terms "comprise" and / or "include" are used in this specification, they indicate the presence of features, steps, operations, devices, components and / or combinations thereof.

[0024] In the absence of conflict, the embodiments of the present invention and the features thereof may be combined with each other.

[0025] Explanation of terms: Indicator semantic model: A structured description model that defines the standardized calculation logic, data sources, and association relationships of indicators from a business perspective.

[0026] Model parsing engine: a core module that dynamically generates SQL statements from semantic model parsing results, supporting syntax optimization and logic merging.

[0027] Dataset: Create a dataset through a database table, build dataset associations, and form a data model. The data model is an important basis for associating indicators and dimensions in the indicator platform.

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

[0029] Example 1 like Figure 1 As shown, this embodiment discloses a SQL intelligent generation and optimization system based on an indicator semantic model, the method comprising the following steps: The business configuration layer is used to configure the basic information of indicators based on preset structured definition elements; The model assembly layer is used to assemble the basic information and the lineage information into a hierarchical indicator semantic model according to the indicator's lineage relationship and database connection information, and automatically adjust the indicator semantic model structure using a dynamic optimization mechanism of the semantic model; The model parsing and execution layer is used to parse the indicator semantic model, generate corresponding structured query language fragments and splice them to obtain a complete structured query language.

[0030] In this embodiment, the system adopts a separate front-end and back-end architecture. The front-end uses the Vue.js framework to build an interactive interface, while the back-end services implement core business logic in Java, incorporating JavaScript to enhance dynamic data processing capabilities. The system comprises a business configuration layer, a model assembly layer, and a model parsing and execution layer. On this foundation, the system incorporates innovative modules such as dynamic row and column permission control, a semantic model dynamic optimization engine, a SQL security verification module, and a SQL adaptive execution plan engine, forming a complete indicator calculation and optimization system.

[0031] (1) Business configuration layer Users configure basic indicator information (such as the indicator's source dataset, filter conditions, dimensions, and metrics) through the front-end interface and save it as structured data. During configuration, the system supports dynamic binding of row-level permission policies (such as department filtering) and column-level permission policies (such as field sensitivity), ensuring that generated SQL statements are automatically injected with permission filters before execution, thereby ensuring data security and mitigating data compliance risks. Furthermore, the system is compatible with the syntax of major databases (MySQL, Oracle, Doris, etc.), enhancing the versatility and scalability of indicator configuration.

[0032] Furthermore, row-level (data) permission policies control data queries within the permission scope. For example, each user can only view the data submitted by themselves and cannot view the data of other users. Column-level permission policies control the display of fields within the permission scope. 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 permission management system, indicator query SQL will dynamically splice the fields related to permission management. When users perform data queries, the user's role information is automatically parsed and dynamically matched.

[0033] Atomic indicator configuration consists of a combination of dimensions and metrics, with its foundational elements derived from the data source-driven dataset model. The dataset model is the relational modeling of a dataset, which is a logical abstraction of a database table. Building a model is a virtual representation of multi-table connections. The system provides full-link autonomous configuration capabilities, supporting the entire process from data source access to public dimension definition. Users can freely select correlation factors to configure indicator combinations. This means combining basic indicators (atomic indicators) with expressions to form composite indicators, enabling a flexible and scalable indicator management system.

[0034] To ensure heterogeneous compatibility of databases, at the business configuration layer, the system automatically adjusts the semantic model assembly rules based on the data source type of the indicator source. 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 grammar rule library to ensure that the generated SQL statements comply with the standard specifications of the corresponding database.

[0035] (2) Model assembly layer Based on indicator identifiers, the system queries indicator lineage relationships and database connection information. The system then assembles configuration and lineage information into a hierarchical indicator semantic model using an outer query + subquery structure. During this process, the system utilizes a dynamic semantic model optimization mechanism to adapt to changes in the data source (such as new fields or a surge in data volume) in real time, automatically adjusting the model structure. For example, when a new field is detected, the system binds it to the relevant indicator; when data volume surges, the system optimizes aggregation logic to reduce computational overhead.

[0036] Furthermore, the dynamic optimization mechanism means that when assembling the indicator semantic model, the system can automatically adjust the model structure and query logic according to the real-time changes in metadata (such as new fields, changes in data volume, etc.) to ensure the accuracy and efficiency of the query. Specifically, it includes: Field adaptability: When new fields appear in the data source, the system automatically detects and binds these fields to related indicators without manual intervention, ensuring that the indicator model is always synchronized with the data source.

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

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

[0039] Furthermore, the aggregation logic is optimized to reduce computational overhead, specifically: (1) Analyze the aggregation method; analyze the current aggregation logic (such as SUM, AVG, COUNT, etc.) to determine whether there are inefficient operations such as full table scans and repeated calculations.

[0040] (2) Select an optimization strategy; based on the analysis results, the system automatically selects an appropriate optimization strategy, including but not limited to: Introducing partition aggregation: partitioning large tables by time, region, and other dimensions, first aggregating within the partitions, and then summarizing the results to reduce the amount of single calculations; Pre-aggregating intermediate tables: Calculating commonly used aggregation results in advance and storing them in intermediate tables, which can be read directly during queries to avoid repeated calculations; Adding indexes: Adding appropriate indexes to aggregate fields to speed up aggregation operations; Dynamically adjusting SQL structures: For example, changing the original nested subqueries to WITH statements (common table expressions) to improve SQL execution efficiency.

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

[0042] In addition, the system uses machine learning to analyze historical query logs, predicting high-frequency query patterns (such as "quarterly sales") and pre-broadening and pre-aggregating related datasets to optimize the model structure and significantly reduce initial query latency. For multi-metric queries, the system uses cross-metric merging optimization to detect homology. If multiple metrics are identified as originating from the same star schema, the query conditions are automatically merged, reducing the number of table joins and subqueries, thereby improving execution efficiency. Specifically, the system analyzes historical query logs, continuously collecting and organizing user historical query logs, including the SQL text of each query, the tables and fields involved, the aggregation method, and the query time. By structured processing these logs, the system extracts feature vectors for each query. The system then uses machine learning models such as cluster analysis (such as the K-Means algorithm) to analyze these feature vectors and automatically identify frequently occurring query patterns. For example, the system can detect recurring queries such as "quarterly sales" or "monthly active users" in historical logs. The machine learning model takes structured query feature data as input and outputs structural descriptions of several high-frequency query patterns. For multi-table associations involved in high-frequency queries, the system will widen the data of related tables in advance, that is, generate wide tables through pre-join operations, integrate commonly used fields into one table, and reduce multi-table join operations in subsequent queries; for high-frequency aggregation operations, the system will calculate the aggregation results in advance and store them in intermediate tables or materialized views. Users can directly read these pre-aggregated results when querying without real-time calculation, thereby significantly reducing the latency of the first query; in addition, the system will automatically create or optimize indexes for fields involved in high-frequency queries, and can cache high-frequency query results to further improve query response speed.

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

[0044] The system is equipped with an intelligent semantic model dynamic optimization engine, which automatically activates the lineage analysis module when executing indicator queries. By intelligently identifying multi-indicator scenarios with homogenous datasets, the system restructures the semantic model assembly paradigm, adopting flat modeling instead of the traditional multi-level nested architecture. This optimization strategy effectively reduces table joins 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 relationships of the multiple indicators queried by the user to determine 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 to generate a unified SQL query. This allows operations that would otherwise require multiple independent table joins and subqueries to be completed through a single joint query of the fact table and dimension tables, significantly reducing the number of table joins and subqueries and significantly improving query execution efficiency.

[0045] Furthermore, the star schema is a common data warehouse modeling approach, consisting of a central fact table and multiple dimension tables, similar in structure to a star. Specifically, the fact table stores metrics for business events (such as sales and order counts), typically including foreign keys to the dimension tables. The dimension tables store descriptive information (such as time, region, and product) and are used to group and filter fact table data.

[0046] For 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 three to one, temporary table creation operations were reduced by two, and query conditions were isolated using CASE statements. This significantly reduced database I / O operations while preserving the original business semantics.

[0047] Furthermore, the system will restructure the semantic model assembly paradigm, adopting flat modeling instead of the traditional multi-level nested architecture. Specifically, during the semantic model assembly process, the system will no longer use the traditional multi-level nested subqueries or complex SQL structures with layered associations. Instead, all relevant data tables and fields will be directly associated and aggregated within the same query level. Specifically, the system will complete the metric data that originally required multiple nested subqueries through a single join and aggregation operation in a single SQL statement. This can significantly reduce the number of table joins and the number of nested subqueries in the SQL statement, reducing the database's parsing and execution burden.

[0048] (3) Model parsing and execution layer The model parsing engine is the core component that automatically converts semantic models into SQL. After users configure query requirements using natural language or visualization, the system maps these requirements into an internal semantic model. The model parsing engine is responsible for parsing and traversing this semantic model, converting its components (such as query fields, filter conditions, grouping conditions, sorting rules, etc.) into corresponding SQL fragments, and ultimately assembling them into complete SQL query statements.

[0049] During the parsing process, the model parsing engine typically uses an object-oriented tree structure to organize and manage semantic models. Each semantic model is parsed into a structure or object containing multiple nodes, each representing a query element (such as a field, condition, or group). The model parsing engine recursively traverses these nodes, gradually generating SQL fragments such as SELECT, WHERE, GROUP BY, and ORDER BY.

[0050] At the same time, the model parsing engine combines the system's dynamic row and column permission control mechanism to dynamically adjust SQL generation 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 in SELECT statements (such as SELECT name, department FROM employee) to achieve fine-grained security control of data access. The specific implementation steps are as follows: (1) Model structuring The system first parses the user's query into an internal semantic model object. This object typically contains attributes such as a field list (SELECT), filter conditions (WHERE), grouping conditions (GROUP BY), and sorting conditions (ORDER BY). Each attribute contains specific fields or condition nodes.

[0051] (2) Traverse each part of the model The system uses a recursive approach to traverse each part of the model object in sequence. For example, it traverses the SELECT field list, processing each field to be queried in turn; traverses the WHERE conditions, processing each filter rule; traverses the GROUP BY and ORDER BY conditions, collecting grouping and sorting fields.

[0052] (3) Generate SQL fragments When traversing each part, the system generates corresponding SQL fragments based on the node type.

[0053] (4) Dynamic permission control During the traversal process, the system dynamically adjusts the SQL snippet based on the user's role and permission information. For example, for users with row permission control, a condition such as region='sales_department' is automatically added to the WHERE clause.

[0054] (5) Concatenate complete SQL statements The system splices the SQL fragments generated by each part according to the SQL syntax order to form a complete SQL query statement.

[0055] On this basis, the system dynamically adjusts the SQL execution path through the SQL adaptive execution plan engine. The SQL adaptive execution plan engine specifically monitors the database CPU, memory, I / O and other resource load status in real time; prioritizes index scans to reduce the overhead of full table scans; combines database metadata to optimize the connection order and query path; dynamically adjusts the SQL execution strategy to adapt to the current resource status; and further improves resource utilization and reduces execution latency through the above processing.

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

[0057] Based on the indicator semantic model, permission rules are seamlessly integrated, and data filtering conditions are dynamically embedded in the SQL generation phase through the model parsing engine. A typical application scenario is: when a user binds the "department dimension" row-level data permission control policy to the "sales data" indicator, the system will automatically append the corresponding conditional expression during the SQL compilation process: WHERE department_id = CURRENT_USER_DEPARTMENT() .

[0058] Based on the index semantic model, seamless integration of permission rules is achieved. Specifically, first, the permission rules of each user or user group (such as accessible organizations, departments, regions, data levels, etc.) are stored in the permission management module in a structured manner. These permission rules are associated with the index semantic model to form a user-permission-index mapping relationship. When a user发起 a query request, the system will automatically retrieve its corresponding permission rules based on the user's identity information (such as role, department, etc.). Subsequently, during the process of the model parsing engine parsing the index semantic model and generating SQL statements, these permission rules are dynamically converted into specific data filtering conditions. For example, if a user is only allowed to access data in the "East China region", the system will automatically generate and insert a filtering condition such as WHERE region = 'East China'. The specific implementation process is as follows: (1) Extract permission rules After the user request arrives, the system queries the corresponding permission rules (such as accessible fields, row-level data ranges, etc.) according to the user's identity.

[0059] (2) Parse the semantic model The system parses the user's query requirements into an index semantic model to clarify the fields, tables, aggregation methods, etc. to be queried.

[0060] (3) Generate permission conditions 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 exclude sensitive fields from the SELECT list.

[0061] (4) Dynamically embed SQL fragments During the SQL generation stage, the system takes the above permission conditions as WHERE clauses or SELECT field filters and dynamically embeds them into the final SQL statement. Row permissions: Append permission conditions in the WHERE clause; Column permissions: Remove unauthorized fields in the SELECT clause.

[0062] (5) SQL statement generation and verification Generate a complete SQL statement with permission filtering and perform legality and security checks through the SQL security verification module.

[0063] In the model parsing and execution layer, the optimization strategy can be dynamically adjusted according to the characteristics of heterogeneous databases. For example, in a high-concurrency scenario, for some databases, it may be more inclined to use temporary tables to cache intermediate results, while for other databases, it may directly adopt in-memory computing to improve speed.

[0064] Example 2 This embodiment discloses a method for intelligently generating and optimizing SQL statements based on an indicator semantic model, including: Basic information of configuration indicators based on preset structured definition elements; According to the indicator's lineage relationship and database connection information, the basic information and lineage information are assembled into a hierarchical indicator semantic model, and the indicator semantic model structure is automatically adjusted using a dynamic optimization mechanism of the semantic model; The indicator semantic model is parsed to generate corresponding structured query language fragments and spliced ​​together to obtain a complete structured query language.

[0065] Example 3 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 implements the steps of the method of the second embodiment when executing the program.

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

[0067] The steps involved in the apparatuses of Examples 3 and 4 above correspond to those of Method Example 1. For detailed implementation, please refer to the relevant description of Example 1. The term "computer-readable storage medium" should be understood to mean a single medium or multiple media containing one or more instruction sets; it should also be understood to include any medium capable of storing, encoding, or carrying an instruction set for execution by a processor and causing the processor to perform any of the methods of the present invention.

[0068] Those skilled in the art will appreciate that the modules or steps of the present invention described above can be implemented using a general-purpose computer device. Alternatively, they can be implemented using program code executable by a computing device, which can then be stored in a storage device and executed by the computing device. Alternatively, they can be fabricated into separate integrated circuit modules, or multiple modules or steps can be fabricated into a single integrated circuit module for implementation. The present invention is not limited to any specific combination of hardware and software.

[0069] The foregoing description is merely a preferred embodiment of the present invention and is not intended to limit the present invention. Those skilled in the art will readily appreciate that various modifications and variations of the present invention are possible. Any modifications, equivalent substitutions, or improvements made within the spirit and principles of the present invention are intended to be within the scope of protection of the present invention.

[0070] Although the above describes the specific embodiments of the present invention in conjunction with the accompanying drawings, it 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 on the basis of the technical solution of the present invention without any creative work are still within the scope of protection of the present invention.

Claims

1. An SQL intelligent generation and optimization system based on an indicator semantic model, characterized in that: include: The business configuration layer is used to configure the basic information of indicators based on preset structured definition elements; The model assembly layer is used to assemble the basic information and the lineage information into a hierarchical indicator semantic model according to the indicator's lineage relationship and database connection information, and automatically adjust the indicator semantic model structure using a dynamic optimization mechanism of the semantic model; The model parsing and execution layer is used to parse the indicator semantic model, generate corresponding structured query language fragments and splice them to obtain a complete structured query language.

2. The SQL intelligent generation and optimization system based on the indicator semantic model according to claim 1, characterized in that: The preset structured definition elements include indicator source data sets, screening conditions, dimensions and metrics.

3. The SQL intelligent generation and optimization system based on the indicator semantic model according to claim 1, characterized in that: When configuring the basic information of an indicator, the basic information is filtered through row-level permission policies and column-level permission policies. The row-level permission policies control data queries within the permission range, and the column-level permission policies control field display within the permission range.

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

5. The SQL intelligent generation and optimization system based on the indicator semantic model according to claim 1, characterized in that: We also use machine learning models combined with historical query logs to automatically identify high-frequency query patterns. For multi-table associations involved in high-frequency query patterns, we pre-widen and pre-aggregate relevant data sets.

6. The SQL intelligent generation and optimization system based on the indicator semantic model according to claim 1, characterized in that: When querying multiple indicators, the homology of the indicators is detected through cross-indicator merging optimization. If it is detected that multiple indicators come from the same star model, the query conditions are automatically merged to reduce the number of table joins and subqueries.

7. The SQL intelligent generation and optimization system based on the indicator semantic model according to claim 1, characterized in that: During the parsing process of the indicator semantic model, the model is traversed in an object-oriented tree structure, and the corresponding SQL fragments are gradually generated and finally spliced ​​into a complete SQL query statement.

8. A method for intelligent SQL generation and optimization based on an indicator semantic model, characterized in that: include: Basic information of configuration indicators based on preset structured definition elements; According to the indicator's lineage relationship and database connection information, the basic information and lineage information are assembled into a hierarchical indicator semantic model, and the indicator semantic model structure is automatically adjusted using a dynamic optimization mechanism of the semantic model; The indicator semantic model is parsed to generate corresponding structured query language fragments and spliced ​​together to obtain a complete structured query language.

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

10. A computer device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein: When the processor executes the program, the steps of the SQL intelligent generation and optimization method based on the indicator semantic model as described in claim 8 are implemented.

Images