Statistical report development and data checking method based on dynamic SQL (Structured Query Language)

Abstract

The invention discloses a statistical report development and data check method based on a dynamic SQL, which is realized based on linkage processing logic of a dynamic SQL construction mechanism, a result table and a check detail table, and comprises the following steps: initializing and recording a log; adding SELECT query, JOINS table association and WHERE conditions according to required parameters, and constructing a basic range table tmpbasic comprising all potential personnel; a result table tmpresult meeting all screening is constructed and hit; a check detail intermediate table fbasic is inserted; the hit state FLAG is updated, and a final result table fresult is output; according to the invention, more efficient, reusable and expandable data statistics and check can be realized.

Description

Technical Field

[0001] This invention relates to the fields of data processing and information technology, and in particular to a method for developing statistical reports and verifying data based on dynamic SQL. Background Technology

[0002] In current information system and data statistics platform development practices, data verification and indicator display functions are typically implemented using a "front-end-led" or "hybrid front-end and back-end processing" approach. That is, the front-end is not only responsible for data display but also for some or all of the data cleaning, comparison, and anomaly marking logic. This approach has the following technical drawbacks: ① Bulky front-end code and high maintenance costs: Frequent changes in verification rules or expansion of indicators require repeated modifications to the front-end code, leading to long version iteration cycles and a large workload for testing and regression analysis; ② Poor rule reusability: It is difficult to share unified verification logic between different pages or modules, easily causing inconsistencies in rules and confusion in data definitions; ③ Limited scalability: Adding new statistical dimensions or verification conditions often requires refactoring the front-end architecture, making it impossible to achieve "configuration-as-it-is" adaptive capabilities; ④ Performance bottlenecks: A large amount of data comparison is performed on the front-end, easily causing page lag and response delays, especially on mobile devices or low-performance devices.

[0003] Chinese patent CN110688321A, "A Data Verification System and Method Based on Front-end and Back-end Separation," proposes to move some verification logic to the back end, but still relies on front-end configuration of verification rules and does not achieve standardized encapsulation at the database layer. Mainstream BI tools (such as Power BI and Tableau) support back-end data modeling, but their verification logic mostly relies on preset models or scripts, lacking a universal design for the "dynamic details + standardized verification" linkage architecture. Open-source frameworks such as Apache Superset or Metabase focus on visualization and querying, and do not adequately support the "business scenario-oriented automatic verification + lightweight front-end display" mode.

[0004] None of the aforementioned existing technologies have effectively solved the core technical problem of "standardizing and sinking the verification logic to the database, only dynamically displaying it on the front end, and achieving front-end and back-end linkage and adaptive expansion". Summary of the Invention

[0005] To address the problems existing in the prior art, the purpose of this invention is to provide a method for developing statistical reports and verifying data based on dynamic SQL. This invention can achieve more efficient, reusable, and easily scalable data statistics and verification.

[0006] To achieve the above objectives, the technical solution adopted by this invention is: a method for developing statistical reports and verifying data based on dynamic SQL, implemented based on a dynamic SQL construction mechanism and the linkage processing logic of the result table and the verification detail table, specifically including the following steps:

[0007] Step 1: Initialize and log;

[0008] Step 2: Based on the required parameters, add SELECT queries, JOINS table joins, and WHERE conditions to construct a basic scope table tmp_basic that includes all potential personnel;

[0009] Step 3: Append SELECT queries, JOINS table joins, and WHERE conditions one by one according to the non-required parameters to build a result table tmp_result that matches all the filters, and dynamically concatenate the JSON key-value pair string match_details to record the "actual value" of all the filtered fields.

[0010] Step 4: Insert the verification details intermediate table f_basic into the basic scope table tmp_basic and the JSON key-value pair string match_details;

[0011] Step 5: Update the hit status FLAG and output the final result table f_result.

[0012] As a further improvement of the present invention, step 1 specifically includes: recording call parameters, deleting old logs, and inserting parameter records.

[0013] As a further improvement of the present invention, step 2 specifically includes the following steps:

[0014] Step 2.1: Initialize variables, including all fields of the personnel base table (sp_sql_select) and the personnel base table joins (sp_sql_joins and sp_sql_where);

[0015] Step 2.2: Iterate through all parameters involving joins with other tables. If a parameter is not null, then: ① Concatenate the join table joins. If it is a required parameter, use an inner join; if it is a non-required parameter, use a left join. Ensure that the construction of the base table tmp_basic covers the range of personnel with required parameters, and append it to sp_sql_joins; ② Concatenate the SELECT query and append the actual values ​​of the parameter fields to sp_sql_select; ③ Concatenate the WHERE condition and append the judgment logic of the required filter conditions to sp_sql_where. Non-required parameters do not need to be appended to ensure that the construction of the base table tmp_basic covers the range of personnel with required parameters.

[0016] Step 2.3: Construct the complete SQL: sp_sql_tmp_basic, to generate the basic scope table tmp_basic, which includes the basic personnel pool.

[0017] As a further improvement of the present invention, step 3 specifically includes the following steps:

[0018] Step 3.1: Reset the constructed sp_sql_select and sp_sql_joins for reuse or continue using sp_sql_where with the added required conditions;

[0019] Step 3.2: If the parameter is not empty, then: ① Concatenate the WHERE condition and append it to sp_sql_where; ② Concatenate match_details, which includes the condition name and field expression. match_details is a dynamic JSON key-value pair concatenation string, which is the core of the verification details and records the actual value of the field corresponding to each filter condition for post-event verification.

[0020] Step 3.3: Construct the complete SQL: sp_sql_tmp_result, generating the result table tmp_result which includes the final matched personnel. At this point, match_details has recorded the actual value expressions of all filter fields, which will be used to generate the verification JSON later.

[0021] As a further improvement of the present invention, step 4 is specifically as follows:

[0022] If match_details is not empty, use the jsonb_build_object function to insert the dynamic_values ​​field of the intermediate real table to store the actual values ​​of each filter field for the corresponding personnel; execute the insertion, and each record will include the field values ​​that were matched.

[0023] This invention implements standardized verification logic at the database level, while the front end displays dynamic verification details, thereby constructing a lightweight data verification architecture that is interconnected between the front and back ends and can adaptively expand. It is particularly suitable for scenarios such as enterprise-level BI reports, human resource indicator analysis, and business personnel screening, providing a general statistical report solution that enables parameterized condition combinations, standardized data processing workflows, and automated generation of traceable verification details.

[0024] The beneficial effects of this invention are:

[0025] 1. Reduced redundant development work: The front-end no longer needs to write separate verification logic for each report; it only needs to display the results returned by the database, significantly reducing the development workload. The front-end only needs basic display capabilities, while complex logic is implemented by database engineers / analysts using SQL. Enterprises can use more junior front-end developers + business analysts, reducing the overall human resource costs of the team.

[0026] 2. Verification results are traceable: Each data entry in the intermediate verification details table comes with a "hits flag" and "original filter conditions (JSON record)", allowing users to view why each data entry was selected or excluded, reducing communication costs.

[0027] 3. Flexible response to changing needs: When adding or modifying filter conditions, only the dynamic SQL and annotation logic need to be adjusted. There is no need to change the front-end code or restructure the table structure, resulting in a faster response time.

[0028] 4. Facilitates testing and verification: Through the "condition value JSON recording" mechanism, all filtering logic is traced, meeting audit compliance requirements (such as SOX, GDPR, and financial regulation).

[0029] 5. High versatility, can be quickly replicated to multiple scenarios such as finance, operations, risk control, auditing, and regulatory reporting. The solution is not bound to specific businesses and only relies on the "basic table + dynamic conditions + hit tag" paradigm.

[0030] In summary, this invention is not a localized optimization, but rather a fundamental reconstruction of the development paradigm for statistical reports and data verification at the data production mechanism level. Its technical effect is not merely "icing on the cake," but a radical solution to the fundamental flaws of traditional solutions, such as "bloated front-end, scattered rules, difficulty in expansion, and lack of traceability." This architecture is not limited to any particular database type or business type; its "dynamic conditional filtering + detailed verification traceability" architecture is highly suitable for complex assessment, qualification screening, and rule engine scenarios, possessing high reusability and auditability. Attached Figure Description

[0031] Figure 1 This is an architecture diagram of an embodiment of the present invention. Detailed Implementation

[0032] The embodiments of the present invention will now be described in detail with reference to the accompanying drawings.

[0033] Example

[0034] like Figure 1 As shown, a method for developing statistical reports and verifying data based on dynamic SQL is presented. The function architecture design focuses on the dynamic SQL construction mechanism and the linkage processing logic between the result table and the verification detail table. The following section uses personnel assessment as an example to detail the specific implementation:

[0035] I. Overall Architecture Goals: Implemented in 3 tables, rwx_id_no as the instance ID, uniquely representing a single function call: Parameter log record → f_cal_index, intermediate table for generating verification details → f_basic (containing the actual values ​​of the hit fields for each record, in JSON format), and final output result table → f_result (containing only the hit personnel, used for business consumption).

[0036] Two key temporary tables are used: tmp_basic (all possible list ranges) for generating the basic range table, and tmp_result (list ranges that meet all filtering conditions) for generating the hit results table.

[0037] II. Core Variable Design (Dynamic SQL Concatenation Skeleton), including: 1. sp_sql_tmp_basic ← CREATE TEMPTABLE tmp_basic AS [sp_sql_select+sp_sql_joins+sp_sql_where]; 2. sp_sql_tmp_result ← CREATE TEMP TABLE tmp_result AS [sp_sql_select + sp_sql_joins + sp_sql_where]; 3. sp_sql_select ← Dynamic SELECT field list (including calculated fields and aliases); 4. sp_sql_joins ← Dynamic JOIN clause (LEFT JOIN temporary tables or dimension tables as needed); 5. sp_sql_where ← Dynamic WHERE condition (initially "WHERE 1=1", gradually appended); 6. match_details ← Dynamic JSON key-value pair concatenation string, format: 'key1' / expr1 / 'key2' / expr2 / ...; Explanation: match_details The core of the verification details is to record "the actual value of each field corresponding to each filter condition" for later verification.

[0038] Specifically, the following steps are included:

[0039] STEP 0: Initialization & Log Recording

[0040] Record the call parameters → Delete the old log for f_cal_index → ​​Insert the f_cal_index parameter record;

[0041] STEP 1: Based on the required parameters (such as organization, etc.), add SELECT queries, JOINS table joins, and WHERE conditions to build the basic scope table tmp_basic (all potential personnel);

[0042] Initialize variables: sp_sql_select = 'SELECT all fields of the personnel base table', sp_sql_joins = 'FROM' + 'personnel base table joins', sp_sql_where = 'WHERE 1=1';

[0043] Iterate through all parameters involving other tables (the parameter element is not in the personnel base table, such as attendance rate in the attendance table): IF parameter not null: 1. Concatenate JOIN table joins; if the parameter is required, use an inner join; if the parameter is optional, use a left join to ensure the construction of the base table tmp_basic covers the personnel range of the required parameter → append to sp_sql_joins; 2. Concatenate SELECT query → append the actual values ​​of the parameter fields to sp_sql_select; 3. Concatenate WHERE conditions → append the judgment logic of the required filter conditions to sp_sql_where, and do not need to append judgments for optional parameters to ensure the construction of the base table tmp_basic covers the personnel range of the required parameter.

[0044] Construct the complete SQL: sp_sql_tmp_basic='CREATE TEMP TABLE tmp_basic AS '+ sp_sql_select + sp_sql_joins + sp_sql_where; Execute → Generate tmp_basic (basic personnel pool).

[0045] STEP 2: Construct the hit result table tmp_result (satisfying all filters):

[0046] Reuse sp_sql_select and sp_sql_joins (already built in STEP1) to reset or continue using sp_sql_where (required conditions have been added on top of STEP1);

[0047] Iterate through all non-required filter parameters (such as date of joining the company, job level, gender, attendance rate, etc.): If the IF parameter is not empty: 1. Concatenate the WHERE condition → append to sp_sql_where; 2. Concatenate match_details → 'condition name' / field expression / ; Example: 'date of joining the company' / to_char(agent_registerdate,'YYYY-MM-DD') / ;

[0048] Construct the complete SQL: `sp_sql_tmp_result=CREATE TEMP TABLE tmp_result AS + sp_sql_select + sp_sql_joins + sp_sql_where`; Execute → generate `tmp_result` (the final matched personnel). At this point, `match_details` has recorded the "actual value expressions" of all filter fields, which will be used to generate the verification JSON later.

[0049] STEP 3: Insert the check detail intermediate table f_basic: IF match_details is empty → set to 'none / none / ' ELSE → remove the trailing ' / ' and replace ' / ' with ',' → prepare for use in jsonb_build_object;

[0050] INSERT INTO f_basic (..., dynamic_values);

[0051] SELECT ..., jsonb_build_object(match_details replaced string);

[0052] FROM tmp_basic;

[0053] Insert all basic personnel (regardless of whether they are matched), and store the actual values ​​(JSON) of each personnel's filter fields in the dynamic_values ​​field; execute the insert, the core value is: each record carries "the field values ​​that were matched", which makes it easy to check "why it was not matched" or "the basis for the match" afterward.

[0054] STEP 4: Update the hit status FLAG: UPDATE f_basic SET flag = '1'; WHERE employee_number IN (SELECT employee_number FROM tmp_result), marking which people were ultimately hit.

[0055] STEP 5: Output the final result table f_result: DELETE FROM f_result WHERE rwd_idx_id = instance ID; INSERT INTO f_result (..., index_value); SELECT ..., flag (i.e., whether it hit); FROM f_basic; WHERE flag = '1'; The final output only contains the hits.

[0056] STEP 6: Function return: RETURN QUERY SELECT..., flag; FROM f_result; WHERE rwd_idx_id = instance ID.

[0057] Summary: The core elements of dynamic processing and verification details are summarized in the table below:

[0058]

[0059] The embodiments described above are merely illustrative of specific implementations of the present invention, and while the descriptions are detailed, they should not be construed as limiting the scope of the present invention. It should be noted that those skilled in the art can make various modifications and improvements without departing from the concept of the present invention, and these modifications and improvements all fall within the scope of protection of the present invention.

Claims

1. A method for developing statistical reports and verifying data based on dynamic SQL, characterized in that, The implementation is based on a dynamic SQL construction mechanism and the linked processing logic between the result table and the verification detail table, specifically including the following steps: Step 1: Initialize and log; Step 2: Based on the required parameters, add SELECT queries, JOINS table joins, and WHERE conditions to construct a basic scope table tmp_basic that includes all potential personnel; Step 3: Append SELECT queries, JOINS table joins, and WHERE conditions one by one according to the non-required parameters to build a result table tmp_result that matches all the filters, and dynamically concatenate the JSON key-value pair string match_details to record the "actual value" of all the filtered fields. Step 4: Insert the verification details intermediate table f_basic into the basic scope table tmp_basic and the JSON key-value pair string match_details; Step 5: Update the hit status FLAG and output the final result table f_result.

2. The method for developing and verifying statistical reports based on dynamic SQL according to claim 1, characterized in that, Step 1 specifically includes: recording the call parameters, deleting old logs, and inserting parameter records.

3. The method for developing and verifying statistical reports based on dynamic SQL according to claim 1, characterized in that, Step 2 specifically includes the following steps: Step 2.1: Initialize variables, including all fields of the personnel base table (sp_sql_select) and the personnel base table joins (sp_sql_joins and sp_sql_where); Step 2.2: Iterate through all parameters involving joins with other tables. If a parameter is not null, then: ① Concatenate the join table joins. If it is a required parameter, use an inner join; if it is a non-required parameter, use a left join. Ensure that the construction of the base table tmp_basic covers the range of personnel with required parameters, and append it to sp_sql_joins; ② Concatenate the SELECT query and append the actual values ​​of the parameter fields to sp_sql_select; ③ Concatenate the WHERE condition and append the judgment logic of the required filter conditions to sp_sql_where. Non-required parameters do not need to be appended to ensure that the construction of the base table tmp_basic covers the range of personnel with required parameters. Step 2.3: Construct the complete SQL: sp_sql_tmp_basic, to generate the basic scope table tmp_basic, which includes the basic personnel pool.

4. The method for developing and verifying statistical reports based on dynamic SQL according to claim 3, characterized in that, Step 3 specifically includes the following steps: Step 3.1: Reset the constructed sp_sql_select and sp_sql_joins for reuse or continue using sp_sql_where with the added required conditions; Step 3.2: If the parameter is not empty, then: ① Concatenate the WHERE condition and append it to sp_sql_where; ② Concatenate match_details, which includes the condition name and field expression. match_details is a dynamic JSON key-value pair concatenation string, which is the core of the verification details and records the actual value of the field corresponding to each filter condition for post-event verification. Step 3.3: Construct the complete SQL: sp_sql_tmp_result, generating the result table tmp_result which includes the final matched personnel. At this point, match_details has recorded the actual value expressions of all filter fields, which will be used to generate the verification JSON later.

5. The method for developing and verifying statistical reports based on dynamic SQL according to claim 4, characterized in that, Step 4 is as follows: If match_details is not empty, use the jsonb_build_object function to insert the dynamic_values ​​field of the intermediate real table to store the actual values ​​of each filter field for the corresponding personnel; execute the insertion, and each record will include the field values ​​that were matched.

Images