System and method for constructing multi-table dynamic summary

Abstract

The invention relates to the technical field of data processing, in particular to a system and method for constructing multi-table dynamic summarization, and provides a Web end user interface to facilitate operation of a user. The general table name input box enables a user to customize a general table name; at least one SQL statement input box is provided, a user can select a general table to be summarized and a field corresponding to the general table according to actual requirements, and summarized data content can be flexibly customized; the summarizing time setting unit allows the user to set the summarizing time of the general table, the user can flexibly arrange summarizing time nodes according to service requirements and data updating frequency, and the adaptability of the scheme is improved.

Description

Technical Field

[0001] The present invention relates to the technical field of data processing, and in particular to a system and method for constructing dynamic aggregation of multiple tables. Background Art

[0002] In the modern data-driven business environment, enterprises and organizations often need to process data from multiple different data sources and sub-tables. In order to conduct effective data analysis and decision-making, it is crucial to aggregate the data scattered in multiple sub-tables into a master table.

[0003] However, traditional multi-table aggregation methods often have problems such as complex operation, lack of flexibility and inability to update dynamically in real time. Summary of the invention

[0004] The present invention provides a system and method for constructing dynamic aggregation of multiple tables, and the technical problem to be solved is: how to allow users to flexibly arrange the time nodes of aggregation according to business needs and data update frequency, thereby improving the adaptability of the solution.

[0005] In order to achieve the above-mentioned object of the invention, the technical solution adopted by the present invention is:

[0006] On the one hand, a system for dynamically summarizing multiple tables is constructed, which is characterized by including:

[0007] A database, in which the sub-tables to be summarized are stored;

[0008] A web user interface is configured with a total table name input box and at least one SQL statement input box;

[0009] A summary time setting unit is used to set the summary time of the master table;

[0010] A general table creation unit, used for obtaining the general table name in the above input box and creating a general table with the general table name;

[0011] A data query unit, used to obtain data in the corresponding sub-table according to the above query fields;

[0012] The aggregation unit is used to dynamically aggregate the data in the above-queried sub-tables into the main table according to the set aggregation time.

[0013] Furthermore, it also includes a general table name verification module, which is used to perform regular expression matching and length checking on the input name during the process of creating the general table; if it does not meet the specifications, an error response is returned to the Web user interface.

[0014] Furthermore, it also includes an information collection module for collecting commonly used syntax options and field name information;

[0015] The completion module is used to match the pre-collected syntax options and field information during the SQL statement input process, and display possible auto-completion options below the input box.

[0016] Furthermore, it also includes a data type matching module, which is used to set the data type of the field corresponding to the main table according to the data type of the corresponding field in the sub-table.

[0017] On the other hand, the method of constructing a multi-table dynamic summary includes the following steps:

[0018] Creating a database, in which the sub-tables to be summarized are stored;

[0019] Create a web user interface, which is configured with a total table name input box and at least one SQL statement input box;

[0020] Set the summary time of the total table;

[0021] Get the name of the general table in the above input box and create a general table with the general table name; Table header creation: create a new data table with the general table name entered by the user;

[0022] Get the data in the corresponding sub-table according to the above query fields;

[0023] The data in the above-queried sub-tables are dynamically aggregated into the main table according to the set aggregation time.

[0024] Furthermore, during the process of creating the master table, the input name is matched with a regular expression and has a length check; if it does not meet the specification, an error response is returned to the Web user interface.

[0025] Furthermore, commonly used syntax options and field name information are collected;

[0026] During SQL statement input, matches are made from pre-collected syntax options and field information, and possible auto-completion options are displayed below the input box.

[0027] Furthermore, the data type of the field corresponding to the main table is set according to the data type of the corresponding field in the sub-table.

[0028] The beneficial effects of the present invention are:

[0029] A web user interface is provided to facilitate user operations; the summary table name input box allows users to customize the summary table name; there is at least one SQL statement input box, and users can select the summary table and its corresponding fields to be summarized according to actual needs, and can flexibly customize the summarized data content; the summary time setting unit allows users to set the summary time of the summary table, and users can flexibly arrange the summary time nodes according to business needs and data update frequency, which improves the adaptability of the solution. BRIEF DESCRIPTION OF THE DRAWINGS

[0030] Figure 1 It is a schematic diagram of the structure of the system of the present invention;

[0031] Figure 2 It is a schematic diagram of the process of the present invention. DETAILED DESCRIPTION

[0032] The specific implementation of the present invention is further described below in conjunction with the accompanying drawings, wherein the same components are represented by the same reference numerals.

[0033] It should be noted that the words "front", "rear", "left", "right", "up" and "down" used in the following description refer to directions in the accompanying drawings, and the words "inside" and "outside" refer to directions toward or away from the geometric center of a specific component, respectively.

[0034] In order to make the contents of the present invention more clearly understood, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the accompanying drawings in the embodiments of the present invention.

[0035] Embodiment 1

[0036] like Figure 1 , build a system for dynamic aggregation of multiple tables,

[0037] Configure a database, in which the sub-tables to be summarized are stored. The database, as the core container for storing data, carries the sub-tables to be summarized. These sub-tables may come from different business modules or data sources, and have their own unique structures and data contents. The choice of database should be determined according to actual business needs and data scale. Common database management systems such as MySQL have powerful data storage and query capabilities. When configuring a database, the following aspects need to be considered: (1) Database connection parameter settings: including database server address, port number, user name, password, etc. Ensure that the system can stably connect to the database to perform data reading and writing operations. (2) Optimization of sub-table storage structure: For a large amount of sub-table data, a reasonable storage structure can improve query performance. You can select a suitable storage engine, index strategy, etc. according to the characteristics of the data to speed up data retrieval. (3) Data backup and recovery mechanism: In order to prevent data loss, a regular data backup mechanism should be established. At the same time, in the event of data damage or loss, the data can be quickly restored to ensure the normal operation of the system.

[0038] The web user interface is configured with a summary table name input box and at least one SQL statement input box. The user can freely enter the name of the summary table in this input box to give the summarized data table a name with a clear business meaning. For example, if it is to summarize sales data, it can be named "Sales Summary Table"; if it is to count employee information, it can be named "Employee Information Summary Table" and so on. At least one SQL statement input box provides users with the function of flexibly selecting the summary table to be summarized and its corresponding fields. Users can accurately specify the range of data to be summarized by writing SQL query statements.

[0039] A summary time setting unit is used to set the summary time of the master table;

[0040] The general table creation unit is used to obtain the general table name in the above input box and create a general table with the general table name. Table header creation: Create a new data table with the general table name entered by the user. When creating the table header, the field structure of the general table should be determined according to the field selected by the user in the SQL statement input box. For example, if the user selects fields such as "Order Number", "Customer Name", and "Order Amount" in the sub-table for summary, the header of the general table should contain these fields.

[0041] A data query unit, used to obtain data in the corresponding sub-table according to the above query fields;

[0042] The aggregation unit is used to dynamically aggregate the data in the above-queried sub-tables into the main table according to the set aggregation time.

[0043] In summary, a Web user interface is provided to facilitate user operations; the summary table name input box allows users to customize the summary table name; there is at least one SQL statement input box, and users can select the summary table and its corresponding fields to be summarized according to actual needs, and can flexibly customize the summarized data content; the summary time setting unit allows users to set the summary time of the summary table, and users can flexibly arrange the summary time nodes according to business needs and data update frequency, which improves the adaptability of the solution.

[0044] Furthermore, the master table name verification module is used to perform regular expression matching and length check on the input name during the master table creation process. If it does not meet the specifications, an error response is returned to the Web user interface.

[0045] Further, the information collection module is used to collect commonly used syntax options and field name information;

[0046] The completion module is used to match the pre-collected syntax options and field information during the SQL statement input process, and display possible auto-completion options below the input box. The user can select and confirm the option by clicking the option or using the up and down keys on the keyboard.

[0047] Furthermore, the data type matching module is used to set the data type of the field corresponding to the master table with the data type of the corresponding field in the sub-table. If the field in the sub-table is an integer type, the corresponding field in the master table should also be set to an integer type; if the field in the sub-table is a date type, the corresponding field in the master table should also be set to a date type. This can avoid data errors caused by data type mismatch.

[0048] Embodiment 2

[0049] Build a system for dynamic aggregation of multiple tables.

[0050] Create a database, and store the sub-tables to be summarized in the database. The database is the core container for storing data and carries the sub-tables to be summarized. These sub-tables may come from different business modules or data sources and have their own unique structures and data contents. The choice of database should be determined according to actual business needs and data scale. Common database management systems such as MySQL have powerful data storage and query capabilities. When configuring the database, the following aspects need to be considered: (1) Database connection parameter settings: including database server address, port number, user name, password, etc. Ensure that the system can stably connect to the database to perform data reading and writing operations. (2) Optimization of sub-table storage structure: For a large amount of sub-table data, a reasonable storage structure can improve query performance. You can select a suitable storage engine, index strategy, etc. according to the characteristics of the data to speed up data retrieval. (3) Data backup and recovery mechanism: In order to prevent data loss, a regular data backup mechanism should be established. At the same time, in the event of data damage or loss, the data can be quickly restored to ensure the normal operation of the system.

[0051] Create a web user interface that is configured with a summary table name input box and at least one SQL statement input box. The user can freely enter the name of the summary table in this input box to give the summarized data table a name with a clear business meaning. For example, if it is to summarize sales data, it can be named "Sales Summary Table"; if it is to count employee information, it can be named "Employee Information Summary Table", etc. At least one SQL statement input box provides users with the function of flexibly selecting the summary table to be summarized and its corresponding fields. Users can accurately specify the range of data to be summarized by writing SQL query statements.

[0052] Set the summary time for the summary table.

[0053] Get the name of the master table in the above input box and create a master table with the master table name. Table header creation: Create a new data table with the master table name entered by the user. When creating the table header, the field structure of the master table should be determined according to the fields selected by the user in the SQL statement input box. For example, if the user selects fields such as "Order Number", "Customer Name", and "Order Amount" in the sub-table for summary, the header of the master table should include these fields.

[0054] Get the data in the corresponding sub-table according to the above query fields;

[0055] The data in the above-queried sub-tables are dynamically aggregated into the main table according to the set aggregation time.

[0056] In summary, a Web user interface is provided to facilitate user operations; the summary table name input box allows users to customize the summary table name; there is at least one SQL statement input box, and users can select the summary table and its corresponding fields to be summarized according to actual needs, and can flexibly customize the summarized data content; the summary time setting unit allows users to set the summary time of the summary table, and users can flexibly arrange the summary time nodes according to business needs and data update frequency, which improves the adaptability of the solution.

[0057] Furthermore, during the creation of the master table, the input name is subjected to regular expression matching and length checking. If it does not meet the specification, an error response is returned to the Web user interface.

[0058] Furthermore, commonly used syntax options and field name information are collected;

[0059] During the SQL statement input process, the pre-collected syntax options and field information are matched, and possible auto-completion options are displayed below the input box. The user can select and confirm the option by clicking the option or using the up and down keys on the keyboard.

[0060] Furthermore, the data type of the corresponding field in the master table is set according to the data type of the corresponding field in the sub-table. If the field in the sub-table is an integer type, the corresponding field in the master table should also be set to an integer type; if the field in the sub-table is a date type, the corresponding field in the master table should also be set to a date type. This can avoid data errors caused by data type mismatch.

[0061] A computer-readable storage medium stores a computer program, which performs the above method when executed by a processing device.

[0062] A computer program product comprises a computer program, wherein the computer program implements the above method when executed by a processing device.

[0063] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. Any modifications, equivalent substitutions and improvements made within the spirit and principles of the present invention shall be included in the protection scope of the present invention.

Claims

1. Build a system for dynamic aggregation of multiple tables, characterized by: include: A database, in which the sub-tables to be summarized are stored; A web user interface is configured with a total table name input box and at least one SQL statement input box; A summary time setting unit is used to set the summary time of the master table; A general table creation unit, used for obtaining the general table name in the above input box and creating a general table with the general table name; A data query unit, used to obtain data in the corresponding sub-table according to the above query fields; The aggregation unit is used to dynamically aggregate the data in the above-queried sub-tables into the main table according to the set aggregation time.

2. The system for constructing dynamic aggregation of multiple tables according to claim 1, characterized in that: It also includes a master table name verification module, which is used to perform regular expression matching and length checking on the input name during the master table creation process; if it does not meet the specifications, an error response is returned to the Web user interface.

3. The system for constructing dynamic aggregation of multiple tables according to claim 1, characterized in that: It also includes an information collection module for collecting commonly used syntax options and field name information; The completion module is used to match the pre-collected syntax options and field information during the SQL statement input process, and display possible auto-completion options below the input box.

4. The system for constructing dynamic aggregation of multiple tables according to claim 1, characterized in that: It also includes a data type matching module, which is used to set the data type of the field corresponding to the main table according to the data type of the corresponding field in the sub-table.

5. A method for constructing dynamic aggregation of multiple tables, characterized in that: The following steps are involved: Creating a database, in which the sub-tables to be summarized are stored; Create a web user interface, which is configured with a total table name input box and at least one SQL statement input box; Set the summary time of the total table; Get the name of the general table in the above input box and create a general table with the general table name; Table header creation: create a new data table with the general table name entered by the user; Get the data in the corresponding sub-table according to the above query fields; The data in the above-queried sub-tables are dynamically aggregated into the main table according to the set aggregation time.

6. The method for constructing dynamic aggregation of multiple tables according to claim 5, characterized in that: During the creation of the master table, the input name is matched with a regular expression and has a length check; if it does not meet the specifications, an error response is returned to the Web user interface.

7. The method for constructing dynamic aggregation of multiple tables according to claim 5, characterized in that: The following steps are also included: Collect common syntax options and field name information; During SQL statement input, matches are made from pre-collected syntax options and field information, and possible auto-completion options are displayed below the input box.

8. The method for constructing dynamic aggregation of multiple tables according to claim 5, characterized in that: The following steps are also included: Set the data type of the corresponding field in the main table according to the data type of the corresponding field in the sub-table.

9. A computer-readable storage medium having a computer program stored thereon, wherein the computer program, when executed by a processing device, performs the method according to claim 5 to claim 8.

10. A computer program product, comprising a computer program, characterized in that When the computer program is executed by a processing device, the method according to claim 5 to claim 8 is implemented.

Images