An automatic calculation system for database dynamic SQL splicing

Abstract

This invention discloses a database dynamic SQL splicing automated calculation system, comprising a dynamic parameter parser, a basic indicator model library, an SQL template library, an SQL splicer, an SQL query executor, and a front-end. First, an indicator system is constructed based on the basic indicator model library for actual business operations. Users input their requirements through the front-end, and the system automatically performs SQL splicing calculations. The final results are then input to the front-end for display. The disclosed system improves the flexibility and efficiency of database queries, while enhancing the security and maintainability of the code, and realizes the automated construction and execution of SQL query statements.

Description

Technical Field

[0001] The present invention belongs to the technical field of database query optimization and automation, and in particular relates to a database dynamic SQL splicing automatic calculation system. Background Art

[0002] Structured Query Language (SQL) is a database query and programming language used to access data and query, update and manage relational database systems.

[0003] In company operations analysis and performance tracking, technical personnel often need to write SQL statements in the background to extract data based on different business logic. This creates a huge and repetitive workload for technical personnel, is prone to errors, and causes data confusion. Therefore, a more flexible and efficient dynamic SQL splicing automated calculation method is needed to automatically build and generate SQL statements based on different user needs and display the execution results. Without the intervention of technical personnel, business department personnel can obtain the required data marts according to their needs. Summary of the Invention

[0004] The purpose of the present invention is to provide a database dynamic SQL splicing automatic calculation system, which improves the flexibility and efficiency of database query and enhances the security and maintainability of the code.

[0005] The present invention provides a database dynamic SQL splicing automatic calculation system, comprising a dynamic parameter parser, a basic indicator model library, an SQL template library, an SQL splicer, an SQL query executor, and a front desk;

[0006] First, based on the basic indicator model library, an indicator system is built for actual business. Users enter their requirements through the front-end, which then feeds the user's input data into the dynamic parameter parser. The dynamic parameter parser parses the user's requirements based on the received data, the basic indicator model library, and the SQL template library, and inputs the results into the SQL query executor. The SQL query executor executes SQL queries based on the received data and the basic indicator model library, and inputs the final results into the front-end for display.

[0007] The basic indicator model library builds several basic indicator modules based on actual business needs, including financial indicator modules, operational indicator modules, human resources indicator modules, customer indicator modules, and risk management indicator modules. The module construction process adopts a unified basic indicator processing flow, including indicator system definition, basic data extraction, data cleaning, data processing and updating, and data warehouse storage.

[0008] The financial indicators in the financial indicator module are used to evaluate the company's operating conditions and profitability, including premium income statistics, profits, costs, expenses, and analytical ratio statistics;

[0009] The operational indicators in the operational indicator module are used to represent the efficiency and effectiveness of the company's daily operations, including statistics on the number of preservation projects and claims, completion time statistics, and service quality satisfaction evaluation statistics;

[0010] The human resources indicators in the human resources indicator module are used to indicate the stability and development potential of the company's workforce, including employee turnover rate, employee and salesperson training statistics, salesperson meeting and training attendance statistics, and employee and salesperson promotion statistics.

[0011] The customer indicators in the customer indicator module are used to analyze the basic situation and operating conditions of customers and their future potential. They include basic customer information statistics, customer purchase analysis, customer account statistics, customer-company contact analysis, and customer future purchasing power analysis.

[0012] The risk management indicators in the risk management indicator module are used to monitor various risks that may arise during the company's operations, including performance team authenticity, team management, sales behavior, business management, high-quality development, and market analysis;

[0013] The indicator system is defined as a description of the specific meaning of the indicator, the source of the indicator, the calculation method, the calculation formula, and the update frequency;

[0014] The basic data extraction is to extract original data from the business system, including insurance policy list, payment flow, customer basic information, and other relevant basic data;

[0015] The data cleaning is to clean the data in the data source, delete erroneous data and unnecessary data according to the indicator definition and preset rules, and convert the data according to the indicator definition;

[0016] Data processing and updating involves integrating basic data and performing processing calculations. According to the indicator definition, the corresponding indicator developer writes processing statements, integrates and calculates the corresponding indicator results from the basic data module, and updates the data according to the update frequency set when defining the indicator. The results of data processing and updating are stored in the data warehouse.

[0017] The data warehouse storage uses a unified data warehouse for all preparation models. The data warehouse will divide each indicator model into different library tables according to the category and purpose of the indicator. Each library table contains analysis objects, analysis object categories, and various indicator items; the data warehouse stores several mapping relationships, which correspond to the Chinese noun definitions of library tables, indicator fields, and operators. The dynamic parameter parser will read the actual values ​​corresponding to these noun definitions during the processing process, and convert the configuration values ​​into actual values ​​before processing.

[0018] The SQL template library is used to store predefined SQL templates, including SQL basic structures and query placeholders; the SQL template library is updated and maintained at preset time intervals;

[0019] The dynamic parameter parser defines the parameter receiving type, including the return result column type, query table type, query condition type, grouping type, post-grouping filtering type, and sorting type. It also defines placeholders based on the parameter receiving type. The converted parameter receiving type will be stored in the corresponding placeholder. The user input will be transferred to each parameter receiving type, and then the parameters will be parsed.

[0020] The parsing process of the dynamic parameter parser includes the following steps:

[0021] S1. Process the query table type. For single-table queries, directly replace the single-table query table placeholder. For multi-table join queries, generate a placeholder for each table and assign it a sort value. The sort value corresponds to the user-configured order. The sort value of the primary table is 0, which is directly set as the primary table placeholder.

[0022] The association query supports left and right associations, and is combined into a placeholder based on the association category, table name, and association fields selected by the user;

[0023] The dynamic parameter parser stores the preset suggested associated fields and creates indexes. After processing the query library table type, it sets a table alias for each table.

[0024] S2. Process the grouping type. The grouping type is optional. If the user has configured a grouping type in the requirements input at the front desk, the grouping column selected by the user is combined with the corresponding table alias prefix obtained in step S1 to form a grouping column placeholder. If the user has not configured a grouping type in the requirements input at the front desk, skip this step.

[0025] S3. Process query condition types; query conditions are divided into single query conditions and combined query conditions; the dynamic parameter parser supports operator categories and fuzzy matching query conditions; operator categories include equal, not equal, greater than, less than, greater than or equal to, less than or equal to, and interval; fuzzy matching includes similar fuzzy matching and excluding similar fuzzy matching; each single condition or combined condition sets a query condition placeholder; a single query condition combines the query field, operator, and query value into a query condition placeholder; a combined query condition is an OR combination of single query conditions, where two or more single query conditions are connected with OR to form a query condition placeholder;

[0026] S4. Process the return result column type. The return result columns are divided into direct display columns and integrated calculated columns. If step S2 is skipped, all columns are prefixed with the table alias and integrated into the return result column placeholder. If step S2 processes the grouping type, the direct display columns reuse the grouping columns from step S2, and additional integrated calculated columns are added. Sum and count functions are performed on the configured columns, and finally the direct display columns and integrated calculated columns are merged into the final return result column placeholder.

[0027] S5. Process the sort type. The sort type is optional. If the user has not configured a sort type in the requirements entered at the front desk, skip this step. If the user has configured a sort type in the requirements entered at the front desk, the sort fields and sort rules configured by the user are integrated into a single sort condition. The sort rules are divided into ascending and descending order. Then, all the single sort conditions are arranged and integrated into the final sort placeholder according to the sort order configured by the user.

[0028] The SQL splicer selects the corresponding SQL template for processing based on the placeholders passed in by the dynamic parameter parser; different placeholder categories and quantities correspond to different SQL templates, among which query library table placeholders and return result column placeholders are necessary placeholders. If there are only these two placeholders, the builder will select the simplest SQL template for processing, replace the placeholder content into the SQL template, and generate an SQL statement; for query library table placeholders, according to the order of the placeholders, first splice them together to generate a whole placeholder and then replace it; for query condition placeholders, all query conditions need to be combined with and and then replaced into the corresponding position of the SQL template; other placeholders can be directly replaced into the final SQL statement according to the results generated by the parser;

[0029] The SQL splicer includes a SQL statement detection module; the SQL statement detection module is used to detect the running efficiency of SQL statements and adjust SQL statements, specifically:

[0030] When it is detected that the number of associated tables exceeds the preset single SQL associated table threshold, the SQL splicer splits the corresponding SQL statement and generates a temporary table according to the associated order for multi-step execution;

[0031] When a table's associated fields are detected to contain a large amount of duplicate data, the SQL joiner reconstructs the query library table placeholders and replaces them with the query results of a group by query based on the associated fields. The data warehouse also searches for fields other than indicators and stores the number of values ​​in each field to identify fields with excessively repeated single values.

[0032] When it is detected that the conditional query contains only or statements, the SQL splicer splits the corresponding SQL statement into two SQL statements and then connects the two statements with union.

[0033] The SQL query executor stores the SQL statements generated and input by the SQL splicer into the execution plan list; the SQL query executor includes a cache module and an SQL optimization module; the cache module stores the top N SQL statements executed and the corresponding query results; the SQL query executor first reads the cache in the cache module, retrieves the execution plan list based on the query SQL, and if a match is found, directly reads the corresponding cache and makes adaptive adjustments based on the specific content of the SQL statement to return the final correct result set; if no match is found in the corresponding cache, a database query is performed to return the final correct result set; after each SQL statement is executed, the execution count of the corresponding execution plan list is increased by 1;

[0034] The SQL optimization module regularly checks the cache module, clears out the cache of the top N SQL statements executed, re-executes the top N SQL statements executed, and stores the cached results; the SQL optimization module runs during the database idle period; the SQL optimization module regularly analyzes the statements in the execution plan list, and for the top N SQL statements executed, analyzes the database tables and various fields in the statements. If the corresponding database tables and fields are not indexed, it will provide suggestions to the administrator, who will confirm whether to create indexes. If the administrator confirms, the SQL optimization module will automatically create indexes in the background and perform regular optimization; the SQL optimization module will also monitor indexes that have not been used for a long time and prompt the administrator to delete the indexes.

[0035] The SQL optimization module manages all indexes and regularly checks the fragmentation size of the indexes. For indexes that exceed the fragmentation threshold, the optimization engine will rebuild the index.

[0036] The front-end includes a result processing module and a demand configuration module. The demand configuration module provides customers with a basic indicator library, required indicator tables and calculation items, placeholder configuration options, and query and select configuration and grouping fields and formulas based on business needs.

[0037] The result processing module receives the result set returned by the SQL query executor and converts it into a front-end display format; the result processing module supports asynchronous query and result caching.

[0038] The present invention discloses a database dynamic SQL splicing automatic calculation system, which improves the flexibility and efficiency of database query, enhances the security and maintainability of code, and realizes the automatic construction and execution of SQL query statements. BRIEF DESCRIPTION OF THE DRAWINGS

[0039] Figure 1 Schematic diagram of the structure of the system of the present invention;

[0040] Figure 2 This is an indicator system diagram in an embodiment of the present invention;

[0041] Figure 3 This is a diagram of the indicator processing process in an embodiment of the present invention;

[0042] Figure 4 This is a data warehouse diagram in an embodiment of the present invention;

[0043] Figure 5 This is a diagram of the foreground parameter configuration in an embodiment of the present invention. DETAILED DESCRIPTION

[0044] The present invention provides a database dynamic SQL splicing automatic calculation system, the structural diagram of which is as follows Figure 1 As shown, it includes dynamic parameter parser, basic indicator model library, SQL template library, SQL splicer, SQL query executor, and front end;

[0045] First, based on the basic indicator model library, an indicator system is built for actual business. Users enter their requirements through the front-end, which then feeds the user's input data into the dynamic parameter parser. The dynamic parameter parser parses the user's requirements based on the received data, the basic indicator model library, and the SQL template library, and inputs the results into the SQL query executor. The SQL query executor executes SQL queries based on the received data and the basic indicator model library, and inputs the final results into the front-end for display.

[0046] The basic indicator model library builds several basic indicator modules based on actual business needs, including financial indicator modules, operational indicator modules, human resources indicator modules, customer indicator modules, and risk management indicator modules. The module construction process adopts a unified basic indicator processing flow, including indicator system definition, basic data extraction, data cleaning, data processing and updating, and data warehouse storage.

[0047] The financial indicators in the financial indicator module are used to evaluate the company's operating conditions and profitability, including premium income statistics, profits, costs, expenses, and analytical ratio statistics;

[0048] The operational indicators in the operational indicator module are used to represent the efficiency and effectiveness of the company's daily operations, including statistics on the number of preservation projects and claims, completion time statistics, and service quality satisfaction evaluation statistics;

[0049] The human resources indicators in the human resources indicator module are used to indicate the stability and development potential of the company's workforce, including employee turnover rate, employee and salesperson training statistics, salesperson meeting and training attendance statistics, and employee and salesperson promotion statistics.

[0050] The customer indicators in the customer indicator module are used to analyze the basic situation and operating conditions of customers and their future potential. They include basic customer information statistics, customer purchase analysis, customer account statistics, customer-company contact analysis, and customer future purchasing power analysis.

[0051] The risk management indicators in the risk management indicator module are used to monitor various risks that may arise during the company's operations, including performance team authenticity, team management, sales behavior, business management, high-quality development, and market analysis;

[0052] The indicator system is defined as a description of the specific meaning of the indicator, the source of the indicator, the calculation method, the calculation formula, and the update frequency;

[0053] The basic data extraction is to extract original data from the business system, including insurance policy list, payment flow, customer basic information, and other relevant basic data;

[0054] The data cleaning is to clean the data in the data source, delete erroneous data and unnecessary data according to the indicator definition and preset rules, and convert the data according to the indicator definition;

[0055] Data processing and updating involves integrating basic data and performing processing calculations. According to the indicator definition, the corresponding indicator developer writes processing statements, integrates and calculates the corresponding indicator results from the basic data module, and updates the data according to the update frequency set when defining the indicator. The results of data processing and updating are stored in the data warehouse.

[0056] The data warehouse storage uses a unified data warehouse for all preparation models. The data warehouse will divide each indicator model into different library tables according to the category and purpose of the indicator. Each library table contains analysis objects, analysis object categories, and various indicator items; the data warehouse stores several mapping relationships, which correspond to the Chinese noun definitions of library tables, indicator fields, and operators. The dynamic parameter parser will read the actual values ​​corresponding to these noun definitions during the processing process, and convert the configuration values ​​into actual values ​​before processing.

[0057] The SQL template library is used to store predefined SQL templates, including SQL basic structures and query placeholders; the SQL template library is updated and maintained at preset time intervals;

[0058] The dynamic parameter parser defines the parameter receiving type, including the return result column type, query table type, query condition type, grouping type, post-grouping filtering type, and sorting type. It also defines placeholders based on the parameter receiving type. The converted parameter receiving type will be stored in the corresponding placeholder. The user input will be transferred to each parameter receiving type, and then the parameters will be parsed.

[0059] The parsing process of the dynamic parameter parser includes the following steps:

[0060] S1. Process the query table type. For single-table queries, directly replace the single-table query table placeholder. For multi-table join queries, generate a placeholder for each table and assign it a sort value. The sort value corresponds to the user-configured order. The sort value of the primary table is 0, which is directly set as the primary table placeholder.

[0061] The association query supports left and right associations, and is combined into a placeholder based on the association category, table name, and association fields selected by the user;

[0062] The dynamic parameter parser stores the preset suggested associated fields and creates indexes. After processing the query library table type, it sets a table alias for each table.

[0063] S2. Process the grouping type. The grouping type is optional. If the user has configured a grouping type in the requirements input at the front desk, the grouping column selected by the user is combined with the corresponding table alias prefix obtained in step S1 to form a grouping column placeholder. If the user has not configured a grouping type in the requirements input at the front desk, skip this step.

[0064] S3. Process query condition types; query conditions are divided into single query conditions and combined query conditions; the dynamic parameter parser supports operator categories and fuzzy matching query conditions; operator categories include equal, not equal, greater than, less than, greater than or equal to, less than or equal to, and interval; fuzzy matching includes similar fuzzy matching and excluding similar fuzzy matching; each single condition or combined condition sets a query condition placeholder; a single query condition combines the query field, operator, and query value into a query condition placeholder; a combined query condition is an OR combination of single query conditions, where two or more single query conditions are connected with OR to form a query condition placeholder;

[0065] S4. Process the return result column type. The return result columns are divided into direct display columns and integrated calculated columns. If step S2 is skipped, all columns are prefixed with the table alias and integrated into the return result column placeholder. If step S2 processes the grouping type, the direct display columns reuse the grouping columns from step S2, and additional integrated calculated columns are added. Sum and count functions are performed on the configured columns, and finally the direct display columns and integrated calculated columns are merged into the final return result column placeholder.

[0066] S5. Process the sort type. The sort type is optional. If the user has not configured a sort type in the requirements entered at the front desk, skip this step. If the user has configured a sort type in the requirements entered at the front desk, the sort fields and sort rules configured by the user are integrated into a single sort condition. The sort rules are divided into ascending and descending order. Then, all the single sort conditions are arranged and integrated into the final sort placeholder according to the sort order configured by the user.

[0067] The SQL splicer selects the corresponding SQL template for processing based on the placeholders passed in by the dynamic parameter parser; different placeholder categories and quantities correspond to different SQL templates, among which query library table placeholders and return result column placeholders are necessary placeholders. If there are only these two placeholders, the builder will select the simplest SQL template for processing, replace the placeholder content into the SQL template, and generate an SQL statement; for query library table placeholders, according to the order of the placeholders, first splice them together to generate a whole placeholder and then replace it; for query condition placeholders, all query conditions need to be combined with and and then replaced into the corresponding position of the SQL template; other placeholders can be directly replaced into the final SQL statement according to the results generated by the parser;

[0068] The SQL splicer includes a SQL statement detection module; the SQL statement detection module is used to detect the running efficiency of SQL statements and adjust SQL statements, specifically:

[0069] When it is detected that the number of associated tables exceeds the preset single SQL associated table threshold, the SQL splicer splits the corresponding SQL statement and generates a temporary table according to the associated order for multi-step execution;

[0070] When a table's associated fields are detected to contain a large amount of duplicate data, the SQL joiner reconstructs the query library table placeholders and replaces them with the query results of a group by query based on the associated fields. The data warehouse also searches for fields other than indicators and stores the number of values ​​in each field to identify fields with excessively repeated single values.

[0071] When it is detected that the conditional query contains only or statements, the SQL splicer splits the corresponding SQL statement into two SQL statements and then connects the two statements with union.

[0072] The SQL query executor stores the SQL statements generated and input by the SQL splicer into the execution plan list; the SQL query executor includes a cache module and an SQL optimization module; the cache module stores the top N SQL statements executed and the corresponding query results; the SQL query executor first reads the cache in the cache module, retrieves the execution plan list based on the query SQL, and if a match is found, directly reads the corresponding cache and makes adaptive adjustments based on the specific content of the SQL statement to return the final correct result set; if no match is found in the corresponding cache, a database query is performed to return the final correct result set; after each SQL statement is executed, the execution count of the corresponding execution plan list is increased by 1;

[0073] The SQL optimization module regularly checks the cache module, clears out the cache of the top N SQL statements executed, re-executes the top N SQL statements executed, and stores the cached results; the SQL optimization module runs during the database idle period; the SQL optimization module regularly analyzes the statements in the execution plan list, and for the top N SQL statements executed, analyzes the database tables and various fields in the statements. If the corresponding database tables and fields are not indexed, it will provide suggestions to the administrator, who will confirm whether to create indexes. If the administrator confirms, the SQL optimization module will automatically create indexes in the background and perform regular optimization; the SQL optimization module will also monitor indexes that have not been used for a long time and prompt the administrator to delete the indexes.

[0074] The SQL optimization module manages all indexes and regularly checks the fragmentation size of the indexes. For indexes that exceed the fragmentation threshold, the optimization engine will rebuild the index.

[0075] The front-end includes a result processing module and a demand configuration module. The demand configuration module provides customers with a basic indicator library, required indicator tables and calculation items, placeholder configuration options, and query and select configuration and grouping fields and formulas based on business needs.

[0076] The result processing module receives the result set returned by the SQL query executor and converts it into a front-end display format; the result processing module supports asynchronous query and result caching.

[0077] The present invention will be further described below with reference to an embodiment:

[0078] Because the company operates a large number of sales incentive programs, with different methods and irregular cycles, it takes a lot of work for technicians to calculate the final rewards. The system of the present invention is used to calculate this problem and realize the distribution of sales incentive programs.

[0079] According to the calculation requirements of the business sales plan, an indicator system is built in the basic indicator library of the system, such as Figure 2 shown.

[0080] According to the definition of the indicator, the indicator is calculated and processed. The processing process is as follows: Figure 3 shown.

[0081] Finally, the processed data is stored in the data warehouse, such as Figure 4 shown.

[0082] After the data warehouse is built, the user configures it on the front end and selects the specific content of the solution, such as Figure 5 shown.

[0083] The system performs SQL splicing based on the parameters configured by the user. The calculation process is as follows:

[0084] ---Calculation results

[0085] if(@check_way=1)---achieved

[0086] begin

[0087] set asQL='insert into #tmp_result

[0088] SELECT '+@]imit id+'

[0089] FROM'+Qtab1e+'a

[0090] where'+@table+

[0091] group by'+table

[0092] Having'+table

[0093] end

[0094] The obtained results are inserted into the result table, and users can choose to view the corresponding results in the foreground.

[0095] The use of the system of the present invention for standardized calculation of sales incentive plan redemption improves work efficiency, reduces the risks of manual calculation and manual operation problems, and greatly improves the efficiency of plan preparation and redemption.

Claims

1. A database dynamic SQL splicing automatic calculation system, characterized in that: Including dynamic parameter parser, basic indicator model library, SQL template library, SQL splicer, SQL query executor, and front end; First, based on the actual business, an indicator system is built based on the basic indicator model library; users input their requirements through the front desk, and then the front desk inputs the user's input data into the dynamic parameter parser; The dynamic parameter parser parses the parameters of the user requirements based on the received data, the basic indicator model library and the SQL template library, and inputs the obtained results into the SQL query executor; The SQL query executor executes SQL queries based on the received data and the basic indicator model library, and inputs the final results to the front-end for display; The dynamic parameter parser defines the parameter receiving type, including the return result column type, query table type, query condition type, grouping type, post-grouping filtering type, and sorting type. It also defines placeholders based on the parameter receiving type. The converted parameter receiving type will be stored in the corresponding placeholder. The user input will be transferred to each parameter receiving type, and then the parameters will be parsed. The parsing process of the dynamic parameter parser includes the following steps: S1. Process the query table type. For single-table queries, directly replace the single-table query table placeholder. For multi-table join queries, generate a placeholder for each table and assign it a ranking value. The ranking value corresponds to the user-configured order. The ranking value of the primary table is 0, which is directly set as the primary table placeholder. The association query supports left and right associations, and is combined into a placeholder based on the association category, table name, and association fields selected by the user; The dynamic parameter parser stores the preset suggested associated fields and creates indexes. After processing the query library table type, it sets a table alias for each table. S2. Processing group type; group type is optional. If the user configured the group type in the front-end input requirements, the group column selected by the user is combined with the corresponding table alias prefix obtained in step S1 to form a group column placeholder. If the user did not configure the group type in the front-end input requirements, skip this step. S3. Process query condition types; query conditions are categorized into single query conditions and combined query conditions; the dynamic parameter parser supports operator categories and fuzzy matching query conditions; operator categories include equal, not equal, greater than, less than, greater than or equal to, less than or equal to, and interval; fuzzy matching includes similar fuzzy matching and excluding similar fuzzy matching; each single condition or combined condition sets a query condition placeholder; a single query condition combines the query field, operator, and query value into a query condition placeholder; a combined query condition is an OR combination of single query conditions, where two or more single query conditions are connected using OR to create a query condition placeholder; S4. Process the return result column type. The return result columns are divided into direct display columns and integrated calculated columns. If step S2 is skipped, all columns are prefixed with the table alias and consolidated into placeholder return result columns. If step S2 is grouped, the direct display columns reuse the grouped columns from step S2, and integrated calculated columns are added. Sum and count functions are performed on the configured columns, and finally, the direct display columns and integrated calculated columns are merged into the final return result column placeholder. S5. Process the sort type. The sort type is optional. If the user did not configure a sort type in the front-end requirements, skip this step. If the user configured a sort type in the front-end requirements, the user-configured sort fields and sorting rules are combined into a single sort condition. The sorting rules are divided into ascending and descending order. Then, all the single sort conditions are arranged and combined into the final sort placeholder according to the user-configured sort order.

2. The database dynamic SQL splicing automatic calculation system according to claim 1 is characterized in that: The basic indicator model library builds several basic indicator modules based on actual business needs, including financial indicator modules, operational indicator modules, human resources indicator modules, customer indicator modules, and risk management indicator modules. The module construction process adopts a unified basic indicator processing flow, including indicator system definition, basic data extraction, data cleaning, data processing and updating, and data warehouse storage. The financial indicators in the financial indicator module are used to evaluate the company's operating conditions and profitability, including premium income statistics, profits, costs, expenses, and analytical ratio statistics; The operational indicators in the operational indicator module are used to represent the efficiency and effectiveness of the company's daily operations, including statistics on the number of preservation projects and claims, completion time statistics, and service quality satisfaction evaluation statistics; The human resources indicators in the human resources indicator module are used to indicate the stability and development potential of the company's workforce, including employee turnover rate, employee and salesperson training statistics, salesperson meeting and training attendance statistics, and employee and salesperson promotion statistics. The customer indicators in the customer indicator module are used to analyze the basic situation and operating conditions of customers and their future potential. They include basic customer information statistics, customer purchase analysis, customer account statistics, customer-company contact analysis, and customer future purchasing power analysis. The risk management indicators in the risk management indicator module are used to monitor various risks that may arise during the company's operations, including performance team authenticity, team management, sales behavior, business management, high-quality development, and market analysis; The indicator system is defined as explaining the specific meaning of the indicators, the source of the indicators, the calculation method, the calculation formula, and the update frequency.

3. The database dynamic SQL splicing automatic calculation system according to claim 2 is characterized in that: The basic data extraction is to extract original data from the business system, including insurance policy list, payment flow, customer basic information, and other relevant basic data; The data cleaning is to clean the data in the data source, delete erroneous data and unnecessary data according to the indicator definition and preset rules, and convert the data according to the indicator definition; Data processing and updating involves integrating basic data and performing processing calculations. According to the indicator definition, the corresponding indicator developer writes processing statements, integrates and calculates the corresponding indicator results from the basic data module, and updates the data according to the update frequency set when defining the indicator. The results of data processing and updating are stored in the data warehouse. The data warehouse storage uses a unified data warehouse for all preparation models. The data warehouse will divide each indicator model into different library tables according to the category and purpose of the indicator. Each library table contains analysis objects, analysis object categories, and various indicator items; the data warehouse stores several mapping relationships, which correspond to the Chinese noun definitions of library tables, indicator fields, and operators. The dynamic parameter parser will read the actual values ​​corresponding to these noun definitions during the processing process, and convert the configuration values ​​into actual values ​​before processing.

4. The database dynamic SQL splicing automatic calculation system according to claim 1 is characterized in that: The SQL template library is used to store predefined SQL templates, including SQL basic structures and query placeholders; the SQL template library is updated and maintained at preset time intervals.

5. The database dynamic SQL splicing automatic calculation system according to claim 1 is characterized in that: The SQL splicer selects the corresponding SQL template for processing based on the placeholders passed in by the dynamic parameter parser; different placeholder categories and quantities correspond to different SQL templates, among which query library table placeholders and return result column placeholders are necessary placeholders. If there are only these two placeholders, the builder will select the simplest SQL template for processing, replace the placeholder content into the SQL template, and generate an SQL statement; for query library table placeholders, according to the order of the placeholders, first splice them together to generate a whole placeholder and then replace it; for query condition placeholders, all query conditions need to be combined with and and then replaced into the corresponding position of the SQL template; other placeholders can be directly replaced into the final SQL statement according to the results generated by the parser; The SQL splicer includes a SQL statement detection module; the SQL statement detection module is used to detect the running efficiency of SQL statements and adjust SQL statements, specifically: When it is detected that the number of associated tables exceeds the preset single SQL associated table threshold, the SQL splicer splits the corresponding SQL statement and generates a temporary table according to the associated order for multi-step execution; When a table's associated fields are detected to contain a large amount of duplicate data, the SQL joiner reconstructs the query library table placeholders and replaces them with the query results of a group by query based on the associated fields. The data warehouse also searches for fields other than indicators and stores the number of values ​​in each field to identify fields with excessively repeated single values. When it is detected that the conditional query contains only or statements, the SQL splicer splits the corresponding SQL statement into two SQL statements and then connects the two statements with union.

6. The database dynamic SQL splicing automatic calculation system according to claim 1 is characterized in that: The SQL query executor stores the SQL statements generated and input by the SQL splicer into the execution plan list; the SQL query executor includes a cache module and an SQL optimization module; the cache module stores the top N SQL statements executed and the corresponding query results; the SQL query executor first reads the cache in the cache module, retrieves the execution plan list based on the query SQL, and if a match is found, directly reads the corresponding cache and makes adaptive adjustments based on the specific content of the SQL statement to return the final correct result set; if no match is found in the corresponding cache, a database query is performed to return the final correct result set; after each SQL statement is executed, the execution count of the corresponding execution plan list is increased by 1; The SQL optimization module regularly checks the cache module, clears out the cache of the top N SQL statements executed, re-executes the top N SQL statements executed, and stores the cached results. The SQL optimization module runs during the database idle period. The SQL optimization module regularly analyzes the statements in the execution plan list. For the top N SQL statements executed, the module analyzes the database tables and various fields in the statements. If the corresponding database tables and fields are not indexed, the module will provide suggestions to the administrator, who will confirm whether to create indexes. If the administrator confirms, the SQL optimization module will automatically create indexes in the background and perform regular optimization. The SQL optimization module will also monitor indexes that have not been used for a long time and prompt the administrator to delete the indexes. The SQL optimization module manages all indexes and regularly checks the index fragmentation size. For indexes that exceed the fragmentation threshold, the optimization engine will rebuild the index.

7. The database dynamic SQL splicing automatic calculation system according to claim 1 is characterized in that: The front-end includes a result processing module and a demand configuration module. The demand configuration module provides customers with a basic indicator library, required indicator tables and calculation items, placeholder configuration options, and query and select configuration and grouping fields and formulas based on business needs. The result processing module receives the result set returned by the SQL query executor and converts it into a front-end display format; The result processing module supports asynchronous query and result caching.

Images