Configuration management method for statistical data

Abstract

The invention discloses a configuration management method for statistical data, comprising: making field agreements for various business tables; establishing a statistical configuration management system, automatically pulling out the opened business table and the fields in the business table after the factory data personnel log in, creating a statistical type, creating a statistical table, splicing SQL, dynamically querying the corresponding business table through JDBC, redundantly adding the query result and various required fields into the created statistical table; generating SQL for querying and updating data for the statistical table, and storing them in the list query SQL and data update SQL fields of the data table of the statistical type; parsing the sub-interface address parameter transmitted from the page through the interface address, then finding the corresponding statistical type, and then reading the SQL of the query list, and then dynamically executing the SQL through JDBC to complete the query of statistical data; the invention improves the response speed.

Description

Technical Field

[0001] The present invention relates to the field of intelligent manufacturing technology, and in particular to a configuration management method for statistical data. Background Art

[0002] In the era of Industry 4.0, data is becoming increasingly important. Almost every production management system has its own data dashboard and data report. These statistical data, as a digital reflection of the production situation, provide an analytical basis for the better operation of the factory. In general MES systems, there is basically a special module: the statistical module. All the data queried on all pages under this module are statistical data. These data are either directly queried from the business table, or have a special statistical table, or are queried from multiple tables. There is also a special module in the MES system: the dashboard. The data on the dashboard and the data source of the statistical module are similar. The data found may need to be aggregated in real time before it is finally presented. Conventional statistical modules are basically integrated into ordinary business functions as a small module among the many MES modules. Statistics do not involve business, but only perform various screening, classification, grouping, aggregation operations, and final queries on the data generated by the business; but statistics are very important and are the most intuitive feedback on the production and operation process. Statistics coupled with ordinary business usually require the backend to perform multi-table joint query immediately after receiving the statistical interface. The query process may also involve grouping, and many operations may be required after the query. There may be some performance issues. In addition, each statistical requirement may require special development, which means that statistics will be iterated continuously.

[0003] In addition, traditional statistical methods may not be able to adapt to some scenarios: 1. The factory wants to maintain the statistics itself, but the source code cannot be opened to the factory; 2. Statistics cannot be coupled with business (for example, unified interception and open permissions for statistics, but business cannot open permissions); 3. After the separation of statistics and business, the development team has limited manpower resources and can only meet business development. Summary of the invention

[0004] In order to solve the problems existing in the prior art, the purpose of the present invention is to provide a configuration management method for statistical data, which improves the response speed.

[0005] To achieve the above object, the technical solution adopted by the present invention is: a configuration management method for statistical data, comprising the following steps:

[0006] Step 1: Make field agreements for various business tables;

[0007] Step 2: Establish a statistical configuration management system. When the factory data personnel log in, the business table and the fields in the business table are automatically pulled out, and a statistical type is created, including filling in the name and purpose of the statistics, selecting the corresponding business type classification and menu, filling in the interface address and button permissions of the statistics, selecting the data table used for statistics, configuring the fields to be displayed on the statistical page to be presented, and configuring the formulas of the fields to be calculated;

[0008] Step 3. After creating the statistical type, create a statistical table. According to the configuration result of the statistical type in step 2, assemble the SQL, dynamically query the corresponding business table through JDBC, and redundantly add the query results and various required fields into the created statistical table.

[0009] Step 4: Generate the query and data update SQL for the statistical table, and store them in the list query SQL and data update SQL fields of the data table of the statistical type; the query SQL supports all filtering queries, sorting, and paging for all fields of the statistical table;

[0010] Step 5: Resolve the sub-interface address parameter transmitted from the page through the interface address, find the corresponding statistical type, read the SQL in the query list, and then dynamically execute the SQL through JDBC to complete the query of statistical data.

[0011] As a further improvement of the present invention, in step 1, the business table includes a basic business table and a production business table. The basic business table is a limited data type, including a data table of equipment, lines, personnel, and various basic data. After reaching a certain number, it basically stops growing or fluctuates slightly; the production business table is a business table that continuously generates data, including a data table of work orders, warehouse entry orders, station transit data, and output data.

[0012] As a further improvement of the present invention, the fields that the basic business table must hold include creation time, modification time, creator and modifier; the fields that the production business table must hold include creation time, creator and time dimension index, and the time dimension index includes year, month, day, hour and week.

[0013] As a further improvement of the present invention, step 3 also includes: configuring the frequency of data refresh, executing data update SQL through a scheduled task to refresh the data.

[0014] As a further improvement of the present invention, step 3 also includes: configuring rules for destroying the statistical table and rules for cleaning up the statistical table data.

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

[0016] The present invention allows the statistical module to be independent of the business module and maintained by a dedicated person within the factory. It is a non-code maintenance, which reduces the learning cost and frees the business developers from the work of maintaining statistical requirements and allows them to focus more on the business itself. At the same time, the statistical data can be dynamically and automatically generated and updated throughout the process, and the simplest single-table query can be performed directly during the query, which improves the response speed. BRIEF DESCRIPTION OF THE DRAWINGS

[0017] Figure 1 This is a schematic diagram of a statistical configuration system according to Embodiment 2 of the present invention;

[0018] Figure 2 This is a schematic diagram of creating statistical business data details according to Embodiment 2 of the present invention. DETAILED DESCRIPTION

[0019] The embodiments of the present invention are described in detail below with reference to the accompanying drawings.

[0020] Example 1

[0021] A configuration management method for statistical data, comprising:

[0022] First, we need to agree on the fields for various business tables. Here, we divide various business tables into two categories: basic business tables and production business tables. Basic business tables refer to limited data types, such as data tables for equipment, lines, personnel, and various basic data. After reaching a certain number, they basically stop growing or fluctuate slightly. Production business tables refer to tables that continuously generate data, such as work orders, warehouse entry orders, station data, production data, etc. These data will be continuously produced as the factory runs, and they will be recorded one by one. For basic business tables, we agree on the fields that must be held: creation time, modification time, creator, and modifier. For production business tables, we agree on the fields that must be held: creation time, creator, year, month, day, hour, and week. The reason why year, month, day, hour, and week are separated is that it is convenient to establish indexes for each time dimension when the subsequent statistical tables are grouped and aggregated from here.

[0023] Secondly, a separate statistical configuration management system is established. The main functions of this system are as follows: 1. After the factory data personnel log in, the business table and the fields in the business table are automatically pulled out. The factory personnel can create a statistical type on the "Add Statistical Type" page, including filling in the name and purpose of the statistic, selecting the corresponding business type classification, the menu to which it belongs, filling in the interface address of the statistic, button permissions, selecting the data table used for the statistic, configuring the fields to be displayed on the statistical page to be presented, configuring the formula of the fields to be calculated, etc. 2. After creating a statistical type, the system will automatically create a statistical table in the statistical microservice (which can be stored in a relational database such as MySQL or a non-relational database such as Mongo), splice sql according to the results of the configuration in step 1, dynamically query the corresponding data table through jdbc, and redundantly add the query results and various required fields into the created statistical table. Then generate the query and data update sql for the list, and store them in the "list query sql" and "data update sql" fields of the data table of the statistical type. The query sql supports all filtering queries and sorting of all fields in the statistical table, as well as paging. In addition, you can also configure the frequency of data refresh, and use scheduled tasks to execute data update SQL to refresh the data (of course, manual refresh is also supported). You can also configure the rules for destroying statistical tables and statistical table data cleanup rules, such as: data older than one year will be automatically destroyed through scheduled tasks, and data tables that have not been queried for three months will be put into the list to be deleted, and then the table will be deleted later.

[0024] Finally, a separate statistics microservice is set up to handle query business. In the statistics microservice, there is only one interface address, which will parse the sub-interface address parameters transmitted from the page (that is, the interface address of each statistic configured in step 1 of the statistics configuration management system), and then find the corresponding statistics type, and then read the SQL of the query list, and then dynamically execute the SQL through JDBC to complete the query of statistical data.

[0025] The above entire process separates the production of statistical data into the statistical configuration management system and is triggered when the statistical type is created. One statistical type corresponds to one statistical type data, which is stored in the statistical type maintenance data table; the data to be counted by the statistical type is queried and calculated from the business table to generate a new statistical table, which is stored in the data table of the statistical microservice. At the same time, the SQL for query is generated and stored in the "List Query" field in the statistical type table in string format, so that the SQL can be dynamically read and executed when the page or kanban interface is called. As a result, statistical-related businesses no longer require iterative maintenance by developers, and are left to factory personnel to manage and configure. The entire statistical configuration system is equivalent to a statistical data table factory, which is responsible for the maintenance of the statistical table's life cycle (creation, query, data update, destruction, etc.).

[0026] Example 2

[0027] like Figure 1 As shown, this embodiment is based on the microservice and data system of spring boot+jdbc+mysql+mongo, so mysql and mongo databases are taken as examples. Statistical configuration types are stored in the statistical type configuration table. One statistical configuration type corresponds to a statistical business data table. When the required fields and calculation formulas of the required business table are selected on the page, a SQL statement will be spliced ​​through jdbc to query the corresponding fields from the business table to form a result set, which will be inserted into the statistical type configuration table. At the same time, a statistical data table is dynamically created through jdbc (or a mongo collection is created by the system and stored in mongo), and the previous result set is inserted into the statistical business data table (ie, data insert). All data queried in the statistical presentation place are queried from the statistical business data table of the system.

[0028] like Figure 2As shown, in the business system, there are two tables: the line body table and the line body production table. Among them, the line body table belongs to the basic data type table (the data will no longer change after reaching a certain level), and the line body production table belongs to the production data table (the data will continue to increase). The data stored in the line body production table is the data collected by the collector and archived by hour. For example, the output at 2021-09-17 14:00:00 is 200, and the output at 2021-09-17 15:00:00 is 300, then this corresponds to two data. If there is no output in a certain hour, there will be no corresponding data. There is a statistical requirement to count the hourly production efficiency of a certain line body in the last 24 hours, and make a line chart to present it in the dashboard. According to the above requirements, the factory staff creates a statistical type of "statistics of the hourly production efficiency of a line in the last 24 hours" in the statistical configuration system, and then selects the corresponding field of the corresponding business data table to be queried as the presentation field (the field of the statistical table that is finally dynamically generated); at the same time, you can customize the new field (such as percent in the schematic diagram), configure whether the field needs a formula, the specific formula, the field in the business table involved in the formula calculation, etc. These configurations will eventually be parsed into a string for splicing SQL so that JDBC can fill in data after generating the statistical table. After creating the presentation field, a statistical table is dynamically generated. The next step is to fill the table with data. Since "hourly in the last 24 hours" is a continuous process on an hourly basis, the "line production table" in the business table may not have data in some hours, so it is necessary to create a temporary 24-hour hourly table, which is used as the main table for query, and link the two business tables to ensure that there is no missing hourly data within 24 hours. The statistical system also includes the function of creating a continuous time temporary table, which is specially used for queries in this case. Here, the statistical system temporarily creates an "hour table" based on the three configurations of "Is it continuous time: yes", "Continuous time dimension: hours", and "Continuous time interval: 2021-09-1615::00:00-2021-09-1715:00:00". 24 data are generated in the table, each of which is in the format of "yyyy-MM-dd HH:00:00" and arranged in ascending order by time. Then use the hour table as the main table, left-join the two business tables, query the fields to be displayed (line name lineName), the fields to be involved in calculating production efficiency (hourly output, theoretical minute output mOutput), and some additional time dimension fields (year, month, day, and time), as well as a percent value obtained by production efficiency calculation, query a result set, and store it in the statistical table. In this way, the creation and initialization of the statistical table of "statistical production efficiency of a line body per hour in the last 24 hours" is completed.Finally, the statistics module will also generate a SQL string for list query and store it in the "query list" field of the statistics type table, so that the SQL can be found when querying and dynamically executed by JDBC to query the table. At this time, in this dynamically created statistics table, each line may have the production data of the last 24 hours, so the overall data may be n*24 (n represents the number of lines). When you want to query the production efficiency of a certain line in the last 24 hours in the dashboard, you only need to add the lineCode filter parameter to find the production efficiency data of the last 24 hours for the line (rather than all lines).

[0029] The above-mentioned embodiments only express the specific implementation of the present invention, and the description thereof is relatively specific and detailed, but it cannot be understood as limiting the scope of the present invention. It should be pointed out that, for ordinary technicians in this field, several variations and improvements can be made without departing from the concept of the present invention, which all belong to the protection scope of the present invention.

Claims

1. A configuration management method for statistical data, characterized in that: The following steps are involved: Step 1: Make field agreements for various business tables; In step 1, the business table includes a basic business table and a production business table. The basic business table is of limited data type, including data tables of equipment, lines, personnel, and various basic data. After reaching a certain number, it basically stops growing or fluctuates slightly; the production business table is a business table that continuously generates data, including data tables of work orders, warehouse entry orders, station-passing data, and output data; the fields that must be held in the basic business table include creation time, modification time, creator, and modifier; the fields that must be held in the production business table include creation time, creator, and time dimension index, and the time dimension index includes year, month, day, hour, and week; Step 2: Establish a statistical configuration management system. When the factory data personnel log in, the business table and the fields in the business table are automatically pulled out, and a statistical type is created, including filling in the name and purpose of the statistics, selecting the corresponding business type classification and menu, filling in the interface address and button permissions of the statistics, selecting the data table used for statistics, configuring the fields to be displayed on the statistical page to be presented, and configuring the formulas of the fields to be calculated; Step 3. After creating the statistical type, create a statistical table. According to the configuration result of the statistical type in step 2, splice the SQL, dynamically query the corresponding business table through JDBC, and redundantly add the query results and various required fields into the created statistical table; Step 4: Generate the query and data update SQL for the statistical table, and store them in the list query SQL and data update SQL fields of the data table of the statistical type; The query SQL supports all filtering queries, sorting, and paging of all fields in the statistics table. Step 5: Resolve the sub-interface address parameter transmitted from the page through the interface address, find the corresponding statistical type, read the SQL in the query list, and then dynamically execute the SQL through JDBC to complete the query of statistical data.

2. The configuration management method of statistical data according to claim 1, characterized in that: Step 3 also includes: configuring the frequency of data refresh, executing data update SQL through scheduled tasks to refresh data.

3. The configuration management method of statistical data according to claim 2, characterized in that: Step 3 also includes: configuring statistical table destruction rules and statistical table data cleanup rules.

Images