A method and terminal for querying statistical data

Abstract

The present invention discloses a query method and terminal for statistical data. After receiving a statistical task in an intermediate layer, when the statistical task turns on a flag for automatically creating a data set, the query statement of the statistical task is automatically obtained, data is captured from a database based on the query statement for statistics, and the statistical data is written into a new database table, thereby pre-grouping data based on the conditions required by the statistical task. When the statistical data in the new database table reaches a preset number, the query table of the query statement is rewritten into a new database table for querying, so that the statistical data in the new table can be directly used when querying data, and no additional changes and library table design are required, thereby improving the query efficiency of the statistical data.

Description

Technical Field

[0001] The present invention relates to the technical field of data query, and in particular to a query method and terminal for statistical data. Background Art

[0002] Currently, many systems have data statistics functions. The background program queries the data that meets the conditions from certain basic tables, then groups and merges the data according to certain business rules, and finally returns it to the client through the interface for display by the client.

[0003] However, the current data statistics method requires separate development for various data statistics, including the design of statistical database tables, the writing of statistical SQL, and the reading and writing of data, which will bring a large workload. Summary of the invention

[0004] The technical problem to be solved by the present invention is to provide a statistical data query method and terminal, which can improve the efficiency of data query.

[0005] In order to solve the above technical problems, the technical solution adopted by the present invention is:

[0006] A statistical data query method, implemented by an intermediate layer, includes the following steps:

[0007] Receive a statistical task, and if the statistical task contains a flag for enabling automatic creation of a data set, automatically obtain a query statement for the statistical task, fetch data from a database based on the query statement for statistics, and write the statistical data into a new database table;

[0008] When the statistical data in the new database table reaches a preset number, the query table of the query statement is rewritten into the new database table and a query is performed.

[0009] In order to solve the above technical problems, another technical solution adopted by the present invention is:

[0010] A statistical data query terminal, including a database and an intermediate layer:

[0011] The middle layer is used to receive a statistical task, and if the statistical task contains a flag for enabling automatic creation of a data set, automatically obtain a query statement for the statistical task, fetch data from the database based on the query statement, perform statistics, and write the statistical data into a new database table;

[0012] When the statistical data in the new database table reaches a preset number, the query table of the query statement is rewritten into the new database table and a query is performed.

[0013] The beneficial effect of the present invention is that: in the middle layer, after receiving the statistical task, when the statistical task turns on the flag of automatically creating a data set, the query statement of the statistical task is automatically obtained, data is captured from the database based on the query statement for statistics, and the statistical data is written into a new database table, so that data is pre-grouped based on the conditions required by the statistical task. When the statistical data in the new database table reaches a preset number, the query table of the query statement is rewritten into a new database table for query, so that the statistical data in the new table can be directly used when querying data, and no additional changes and library table design are required. The new database table can be directly used for search, which improves the query efficiency of statistical data. BRIEF DESCRIPTION OF THE DRAWINGS

[0014] Figure 1 A flowchart of a method for querying statistical data according to an embodiment of the present invention;

[0015] Figure 2 The figure is a schematic diagram of a statistical data query terminal according to an embodiment of the present invention. DETAILED DESCRIPTION

[0016] In order to explain the technical content, achieved objectives and effects of the present invention in detail, the following is an explanation in combination with the implementation modes and the accompanying drawings.

[0017] Please refer to Figure 1 The embodiment of the present invention provides a statistical data query method, which is implemented by the middle layer and includes the steps of:

[0018] Receive a statistical task, and if the statistical task contains a flag for enabling automatic creation of a data set, automatically obtain a query statement for the statistical task, fetch data from a database based on the query statement for statistics, and write the statistical data into a new database table;

[0019] When the statistical data in the new database table reaches a preset number, the query table of the query statement is rewritten into the new database table and a query is performed.

[0020] From the above description, it can be seen that the beneficial effect of the present invention is that: in the middle layer, after receiving the statistical task, when the statistical task turns on the flag of automatically creating a data set, the query statement of the statistical task is automatically obtained, data is captured from the database based on the query statement for statistics, and the statistical data is written into a new database table, thereby pre-grouping the data based on the conditions required by the statistical task. When the statistical data in the new database table reaches a preset number, the query table of the query statement is rewritten into a new database table for query, so that the statistical data in the new table can be directly used when querying data, and no additional changes and library table design are required. The new database table can be directly used for search, which improves the query efficiency of statistical data.

[0021] Furthermore, the query statement for automatically obtaining the statistical task includes:

[0022] Obtaining statistical conditions for the statistical task;

[0023] A query statement of the statistical task is generated according to the statistical condition, and the query statement contains a grouping condition.

[0024] From the above description, it can be seen that the corresponding query statement is generated according to the statistical conditions in the statistical task, and both the statistical conditions and the query statement contain grouping conditions, so the query statement can be automatically obtained without additional coding.

[0025] Further, based on the query statement, fetching data from the database for statistics and writing the statistics data into a new database table includes:

[0026] Create a scheduled task and a new database table structure according to the grouping conditions in the query statement;

[0027] The scheduled task is used to periodically fetch data from the database and perform data statistics, and the statistical data obtained is written into a new database table.

[0028] From the above description, it can be seen that a scheduled task and a new library table are created according to the grouping conditions of the query statement, so that database data can be captured regularly through the scheduled task for data statistics, and the obtained statistical data is written into the new library table, so as to automatically create a new database and facilitate subsequent statistical data queries.

[0029] Further, rewriting the query table of the query statement into the new database table and querying includes:

[0030] Modify the table name queried in the query statement to the table name of the new database table;

[0031] The corresponding statistical data is obtained from the new database table according to the query scope in the query statement.

[0032] From the above description, it can be seen that when the statistical data in the new table reaches the preset number, it can meet the needs of business queries. At this time, the table name queried in the query statement is changed to the table name of the new database table, so that data is queried in the new table. At this time, users do not need to group data when querying statistical data, but can directly search for statistical data in the new table.

[0033] Furthermore, if the statistical task does not include a flag for enabling automatic creation of a data set, data query is performed directly in the database.

[0034] It can be seen from the above description that, by combining the cases of enabling the flag for automatically creating a data set and disabling the flag for automatically creating a data set, a suitable data query method can be selected according to actual needs.

[0035] Please refer to Figure 2 Another embodiment of the present invention provides a statistical data query terminal, including a database and an intermediate layer:

[0036] The middle layer is used to receive statistical tasks. If the statistical task contains a flag to enable automatic creation of data sets, the query statement of the statistical task is automatically obtained, data is captured from the database based on the query statement for statistics, and the statistical data is written into a new database table. When the statistical data in the new database table reaches a preset number, the query table of the query statement is rewritten into the new database table and a query is performed.

[0037] From the above description, it can be seen that in the middle layer, after receiving the statistical task, when the statistical task turns on the flag of automatically creating a data set, the query statement of the statistical task is automatically obtained, and data is captured from the database based on the query statement for statistics, and the statistical data is written into a new database table, so as to pre-group the data based on the conditions required by the statistical task. When the statistical data in the new database table reaches a preset number, the query table of the query statement is rewritten into a new database table for query, so that the statistical data in the new table can be directly used when querying data, and no additional changes and library table design are required. The new database table can be directly used for search, which improves the query efficiency of statistical data.

[0038] Furthermore, the query statement for automatically obtaining the statistical task includes:

[0039] Obtaining statistical conditions for the statistical task;

[0040] A query statement of the statistical task is generated according to the statistical condition, and the query statement contains a grouping condition.

[0041] From the above description, it can be seen that the corresponding query statement is generated according to the statistical conditions in the statistical task, and both the statistical conditions and the query statement contain grouping conditions, so the query statement can be automatically obtained without additional coding.

[0042] Further, based on the query statement, fetching data from the database for statistics and writing the statistics data into a new database table includes:

[0043] Create a scheduled task and a new database table structure according to the grouping conditions in the query statement;

[0044] The scheduled task is used to periodically fetch data from the database and perform data statistics, and the statistical data obtained is written into a new database table.

[0045] From the above description, it can be seen that a scheduled task and a new library table are created according to the grouping conditions of the query statement, so that database data can be captured regularly through the scheduled task for data statistics, and the obtained statistical data is written into the new library table, so as to automatically create a new database and facilitate subsequent statistical data queries.

[0046] Further, rewriting the query table of the query statement into the new database table and querying includes:

[0047] Modify the table name queried in the query statement to the table name of the new database table;

[0048] The corresponding statistical data is obtained from the new database table according to the query scope in the query statement.

[0049] From the above description, it can be seen that when the statistical data in the new table reaches the preset number, it can meet the needs of business queries. At this time, the table name queried in the query statement is changed to the table name of the new database table, so that data is queried in the new table. At this time, users do not need to group data when querying statistical data, but can directly search for statistical data in the new table.

[0050] Furthermore, if the statistical task does not include a flag for enabling automatic creation of a data set, data query is performed directly in the database.

[0051] It can be seen from the above description that, by combining the cases of enabling the flag for automatically creating a data set and disabling the flag for automatically creating a data set, a suitable data query method can be selected according to actual needs.

[0052] The statistical data query method and terminal of the present invention are suitable for improving the query efficiency of statistical data in data query projects, and are described below through specific implementation methods:

[0053] Embodiment 1

[0054] Please refer to Figure 1 , a statistical data query method is implemented by an intermediate layer, wherein the intermediate layer can be integrated and accessed by a server, including the steps of:

[0055] S1. Receive a statistical task. If the statistical task contains a flag for enabling automatic creation of a data set, automatically obtain a query statement for the statistical task, fetch data from a database based on the query statement, perform statistics, and write the statistical data into a new database table.

[0056] In this embodiment, when SQL statements are frequent, a flag for automatically creating a data set can be enabled. The following is an example of querying the number of times a user logs in every day:

[0057] S11. Obtain statistical conditions of the statistical task, and generate a query statement of the statistical task according to the statistical conditions, wherein the query statement includes a grouping condition.

[0058] Specifically, when the flag for starting automatic creation of data sets is turned on in the statistical task, the statistical module automatically obtains the query SQL statement of the statistical task. Since the statistical conditions of the statistical task include data grouping conditions, in this embodiment, the SQL statement will contain the data format of group by userId, dataformat (day).

[0059] S12. According to the grouping conditions in the query statement, a scheduled task and a new database table structure are created. The scheduled task periodically captures data from the database and performs data statistics, and the statistical data obtained is written into the new database table.

[0060] Specifically, the statistics module automatically creates a scheduled task and a database table structure according to the grouping conditions in the query statement. The scheduled task grabs data from the basic database on a daily basis and performs data statistics, and writes the statistical data into a new database table.

[0061] S2. When the statistical data in the new database table reaches a preset number, the query table of the query statement is rewritten into the new database table and a query is performed.

[0062] Wherein step S2 specifically comprises:

[0063] The table name queried in the query statement is modified to the table name of the new database table, and corresponding statistical data is obtained from the new database table according to the query range in the query statement.

[0064] Specifically, when the statistical data in the new database table reaches the total amount of statistical data, the query statement is automatically rewritten, and the table name queried in the query statement is changed to the table name of the new database table, so that when subsequent scheduled tasks continue to use the query statement to query data, the data will be directly queried and returned from the new database table.

[0065] In this embodiment, since there is already a scheduled task to additionally count the number of user logins per day in a new table, when the query SQL reaches the middle layer again, if the group by is followed by a daily query statement, the group by condition is removed and the new table is queried directly according to the query time range. Because the new table already contains the statistical results of each person per day, the result data can be queried directly without grouping again.

[0066] Furthermore, when the statistical task has subtasks, data aggregation is performed on the new database table according to the statistical conditions of the subtasks to obtain a sub-database table.

[0067] Specifically, if you need to query the number of weekly logins of users on this basis, the data that has been counted by day in the previous text will be automatically aggregated again and a new database table will be created to provide subsequent query operations.

[0068] S3. If the statistical task does not include a flag for enabling automatic creation of a data set, data query is performed directly in the database.

[0069] If the preset flag is not turned on during the query operation, the existing statistical data query method is still used to query data from the specific library table and perform statistical processing.

[0070] Furthermore, when data is written, it can also be directly transmitted to the specific library table for data writing.

[0071] Therefore, compared with the prior art of counting user login data within a certain time range, this embodiment can automatically create data sets according to different SQL statements for the proxy middle layer to directly query statistical data. In this way, some simple statistical requirements can be achieved to a certain extent without the need for additional coding and library table design, thereby improving work efficiency.

[0072] Embodiment 2

[0073] Please refer to Figure 2 A statistical data query terminal includes a database and an intermediate layer, and the intermediate layer implements each step of a statistical data query method of embodiment one.

[0074] In summary, the present invention provides a statistical data query method and terminal, which performs data query through the middle layer. When a data query is to be performed, if it is marked as querying data from an automatically created data set, the statistical data is automatically obtained in advance according to the statistical needs and stored in a new table, and the statistical data in the new table can be directly queried later. Specifically: after receiving the statistical task, when the statistical task turns on the flag of automatically creating a data set, the query statement of the statistical task is automatically obtained, and data is periodically captured from the database for statistics based on the grouping conditions in the query statement, and the statistical data is written into a new database table, thereby pre-grouping the data based on the conditions required by the statistical task. When the statistical data in the new database table reaches a preset number, the query table of the query statement is rewritten into a new database table for query. Therefore, when querying data, the statistical data in the new table can be directly used, and no additional changes and library table design are required. The new database table can be directly used for search, which improves the query efficiency of the statistical data.

[0075] The above descriptions are merely embodiments of the present invention and are not intended to limit the patent scope of the present invention. Any equivalent transformations made using the contents of the present invention's specification and drawings, or directly or indirectly applied in related technical fields, are also included in the patent protection scope of the present invention.

Claims

1. A statistical data query method, implemented by the middle layer, characterized in that: Includes steps: Receive a statistical task, and if the statistical task contains a flag for enabling automatic creation of a data set, automatically obtain a query statement for the statistical task, fetch data from a database based on the query statement for statistics, and write the statistical data into a new database table; When the statistical data in the new database table reaches a preset number, rewriting the query table of the query statement into the new database table and querying it; If the statistical task does not include a flag for enabling automatic creation of a data set, data query is performed directly in the database.

2. A statistical data query method according to claim 1, characterized in that: The query statement for automatically obtaining the statistical task includes: Obtaining statistical conditions for the statistical task; A query statement of the statistical task is generated according to the statistical condition, and the query statement contains a grouping condition.

3. A statistical data query method according to claim 2, characterized in that: Based on the query statement, the data is captured from the database for statistics and the statistics are written into a new database table, including: Create a scheduled task and a new database table structure according to the grouping conditions in the query statement; The scheduled task is used to periodically fetch data from the database and perform data statistics, and the statistical data obtained is written into a new database table.

4. A statistical data query method according to claim 1, characterized in that: Rewriting the query table of the query statement into the new database table and querying it includes: Modify the table name queried in the query statement to the table name of the new database table; The corresponding statistical data is obtained from the new database table according to the query scope in the query statement.

5. A statistical data query terminal, characterized in that: Including database and middle tier: The middle layer is used to receive a statistical task, and if the statistical task contains a flag for enabling automatic creation of a data set, automatically obtain a query statement for the statistical task, fetch data from a database based on the query statement for statistics, and write the statistical data into a new database table; when the statistical data in the new database table reaches a preset number, rewrite the query table of the query statement into the new database table and perform a query; If the statistical task does not include a flag for enabling automatic creation of a data set, data query is performed directly in the database.

6. A statistical data query terminal according to claim 5, characterized in that: The query statement for automatically obtaining the statistical task includes: Obtaining statistical conditions for the statistical task; A query statement of the statistical task is generated according to the statistical condition, and the query statement contains a grouping condition.

7. A statistical data query terminal according to claim 6, characterized in that: Based on the query statement, the data is captured from the database for statistics and the statistics are written into a new database table, including: Create a scheduled task and a new database table structure according to the grouping conditions in the query statement; The scheduled task is used to periodically fetch data from the database and perform data statistics, and the statistical data obtained is written into a new database table.

8. A statistical data query terminal according to claim 5, characterized in that: Rewriting the query table of the query statement into the new database table and querying it includes: Modify the table name queried in the query statement to the table name of the new database table; The corresponding statistical data is obtained from the new database table according to the query scope in the query statement.

Images