Business data query method and device

Abstract

The application discloses a kind of service data query method and device, it is related to big data technical field, the method comprises: executing full amount task, full amount task needs to insert intermediate summary table after the service data in service detail table is summarized;According to the execution time of incremental task, incremental task is executed, incremental task needs to insert intermediate summary table after the service data not summarized in service detail table is summarized;After receiving the service data query request initiated by user, parse service data query request, obtain first time interval and second time interval;From service detail table, the service data of first time interval is inquired;From intermediate summary table and / or service detail table, the summary data of second time interval is inquired;The service data found and the summary data found are aggregated, and aggregated data is fed back to user.The application can improve the efficiency of real-time summary query of service of 100 million data.

Description

Technical Field

[0001] This invention relates to the field of database processing technology, and in particular to a method and apparatus for querying business data. Background Technology

[0002] This section is intended to provide background or context for the embodiments of the invention set forth in the claims. The description herein is not an admission that it is prior art simply because it is included in this section.

[0003] With the rapid development of technology, users in various projects are increasingly demanding higher efficiency in complex query scenarios within asset operations, while simultaneously requiring guarantees of the accuracy and real-time performance of aggregated query data. Therefore, data querying in complex scenarios, especially real-time aggregated queries based on hundreds of millions of data points after multi-dimensional grouping and statistics, is currently a challenging problem for databases. Summary of the Invention

[0004] This invention provides a business data query method to improve the efficiency of real-time summary query of business data volumes of hundreds of millions. The method includes:

[0005] Execute a full task. The full task requires summarizing the business data in the business details table and inserting it into an intermediate summary table according to a preset data summarization rule. The intermediate summary table includes multiple summary fields, which are generated according to the preset data summarization rule.

[0006] The incremental task is executed according to the execution time of the incremental task. The incremental task needs to summarize the business data that has not been summarized in the business details table and insert it into the intermediate summary table.

[0007] After receiving a business data query request initiated by a user, the business data query request is parsed to obtain a first time interval and a second time interval. The first time interval is from the time when the most recent incremental task was completed to the time when the most recent query was made, and the second time interval is from the time when the most distant query was made to the time when the most recent incremental task was completed.

[0008] Query the business data for the first time period from the business details table;

[0009] Query the summary data for the second time interval from the intermediate summary table and / or business detail table;

[0010] The system aggregates the retrieved business data and the retrieved summary data, and then returns the aggregated data to the user.

[0011] This invention also provides a business data query device to improve the efficiency of real-time summary query of business data volumes of hundreds of millions. The device includes:

[0012] The full task execution module is used to execute full tasks. The full tasks need to summarize the business data in the business details table and insert it into the intermediate summary table according to the preset data summary rules. The intermediate summary table includes multiple summary fields, which are generated according to the preset data summary rules.

[0013] The incremental task execution module is used to execute the incremental task according to the execution time of the incremental task. The incremental task needs to summarize the business data that has not been summarized in the business details table and insert it into the intermediate summary table.

[0014] The request decomposition module is used to parse the business data query request after receiving the request initiated by the user, and obtain a first time interval and a second time interval. The first time interval is from the time when the most recent incremental task was completed to the time when the most recent query was made, and the second time interval is from the time when the most distant query was made to the time when the most recent incremental task was completed.

[0015] The first query module is used to query business data for the first time interval from the business details table;

[0016] The second query module is used to query the summary data of the second time interval from the intermediate summary table and / or business detail table;

[0017] The data aggregation module is used to aggregate the retrieved business data and the retrieved summary data, and then return the aggregated data to the user.

[0018] This invention also provides a computer device, including a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the computer program, it implements the above-described business data query method.

[0019] This invention also provides a computer-readable storage medium storing a computer program that, when executed by a processor, implements the aforementioned business data query method.

[0020] This invention also provides a computer program product, which includes a computer program that, when executed by a processor, implements the above-described business data query method.

[0021] In this embodiment of the invention, a full task is executed. This full task requires summarizing business data from the business detail table according to preset data summarization rules and inserting it into an intermediate summary table. The intermediate summary table includes multiple summary fields generated according to the preset data summarization rules. Incremental tasks are executed according to their execution times. These incremental tasks require summarizing unsummarized business data from the business detail table and inserting it into the intermediate summary table. Upon receiving a user-initiated business data query request, the request is parsed to obtain a first time interval and a second time interval. The first time interval is from the completion time of the most recent incremental task to the most recent query time, and the second time interval is from the furthest query time to the completion time of the most recent incremental task. Business data for the first time interval is queried from the business detail table. Summarized data for the second time interval is queried from the intermediate summary table and / or the business detail table. The queried business data and the queried summarized data are aggregated, and the aggregated data is fed back to the user. Through the above steps, the full and incremental tasks insert business data into the intermediate summary table. This allows users to query only the massive business detail table for the first time interval, and directly query the summary data in the intermediate summary table according to the preset data aggregation rules for the second time interval, greatly improving the efficiency of business data query. Attached Figure Description

[0022] To more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort. In the drawings:

[0023] Figure 1 This is a flowchart of a business data query method provided in an embodiment of the present invention;

[0024] Figure 2 This is a flowchart illustrating the execution of the full task in an embodiment of the present invention;

[0025] Figure 3 This is a flowchart of the incremental task execution in an embodiment of the present invention;

[0026] Figure 4 This is a flowchart illustrating the business data change process in an embodiment of the present invention;

[0027] Figure 5 This is a flowchart of the data query process in an embodiment of the present invention;

[0028] Figure 6 This is a flowchart of data aggregation in an embodiment of the present invention;

[0029] Figure 7 This is a schematic diagram of the business data query device in an embodiment of the present invention;

[0030] Figure 8 This is a schematic diagram of a computer device provided in an embodiment of the present invention. Detailed Implementation

[0031] To make the objectives, technical solutions, and advantages of the embodiments of the present invention clearer, the embodiments of the present invention will be further described in detail below with reference to the accompanying drawings. Here, the illustrative embodiments of the present invention and their descriptions are used to explain the present invention, but are not intended to limit the present invention.

[0032] The acquisition, storage, use, and processing of data in this application all comply with the relevant provisions of national laws and regulations.

[0033] In this document, the term "and / or" merely describes a relationship, indicating that three relationships can exist. For example, A and / or B can represent three cases: A alone, A and B simultaneously, and B alone. Furthermore, the term "at least one" in this document means any combination of at least two of any one or more elements. For example, including at least one of A, B, and C can mean including any one or more elements selected from the set consisting of A, B, and C.

[0034] In the description of this specification, the terms "comprising," "including," "having," and "containing" are open-ended terms, meaning that they include but are not limited to. The terms "an embodiment," "a specific embodiment," "some embodiments," and "for example," etc., refer to specific features, structures, or characteristics described in connection with that embodiment or example that are included in at least one embodiment or example of this application. In this specification, the illustrative expressions of the above terms do not necessarily refer to the same embodiment or example. Furthermore, the specific features, structures, or characteristics described can be combined in any suitable manner in one or more embodiments or examples. The order of steps involved in the various embodiments is used to illustrate the implementation of this application, and the order of steps is not limited and can be adjusted appropriately as needed.

[0035] The inventors discovered that during data queries, data from the business detail table can be periodically stored in an intermediate summary table according to the summary dimensions. This allows for direct querying of the intermediate summary table instead of directly querying the business detail table. Because the intermediate summary table groups and summarizes data from the actual business detail table according to certain rules, such as amount and quantity, the data volume is significantly smaller than that in the actual table. Querying this pre-processed intermediate summary table is also much faster than querying the actual business detail table. However, this scheduled task interaction scheme results in very slow execution of the daily scheduled task of summarizing business data from the business detail table to the intermediate summary table. Furthermore, this solution cannot guarantee real-time performance for users, resulting in a one-day delay.

[0036] Therefore, improving the execution efficiency of scheduled tasks and ensuring the real-time nature of business data are essential for real-time query solutions.

[0037] Figure 1 This is a flowchart of a business data query method in an embodiment of the present invention. The business data query method provided in this embodiment of the present invention can ensure the real-time performance of data while ensuring the execution efficiency of scheduled tasks. The method includes:

[0038] Step 101: Execute the full task. The full task needs to summarize the business data in the business details table and insert it into the intermediate summary table according to the preset data summary rules. The intermediate summary table includes multiple summary fields, which are generated according to the preset data summary rules.

[0039] Step 102: Execute the incremental task according to the execution time of the incremental task. The incremental task needs to summarize the business data that has not been summarized in the business details table and insert it into the intermediate summary table.

[0040] Step 103: After receiving the business data query request initiated by the user, parse the business data query request to obtain a first time interval and a second time interval. The first time interval is from the time when the most recent incremental task was completed to the time when the most recent query was made, and the second time interval is from the time when the most distant query was made to the time when the most recent incremental task was completed.

[0041] Step 104: Query the business data for the first time interval from the business details table;

[0042] Step 105: Query the summary data for the second time interval from the intermediate summary table and / or business details table;

[0043] Step 106: Aggregate the retrieved business data and the retrieved summary data, and then return the aggregated data to the user.

[0044] In this embodiment of the invention, the full task and incremental task insert business data into an intermediate summary table, so that when a user queries, they only need to query the massive business detail table for the query requirements of the first time interval; for the query requirements of the second time interval, they can directly query the summary data in the intermediate summary table that is summarized according to the preset data summary rules, which greatly improves the efficiency of business data query.

[0045] In this embodiment of the invention, the business details table represents the lifecycle of each business order in each timeline. The same business order may have multiple versions, and the value and amount of each version may be different.

[0046] The business details table includes fields such as a unique identifier, business version number, business status, specific business fields, business start date and end date, amount, and quantity. Business status includes add, update, and logical delete. Specific business fields are numerous and related to specific business processes, and will not be elaborated upon here. The business start date and end date will not overlap, and these dates are always consecutive across multiple business versions, forming a date chain where the first and last dates correspond.

[0047] Taking an asset system as an example, an asset may have multiple historical versions, marking the number and value of the card's historical state within each lifecycle. This is used to record historical data. For example, for a housing asset, the quantity represents the area of ​​the house, and the value represents the actual value of the house. For a vehicle asset, the quantity is always 1, and the value is the actual value of the vehicle. Here, quantity and value actually refer to numerical statistical indicators in various systems. They can be any indicator, such as the number of orders, the number of products, the number of product prices, scores, etc., on a shopping website, for aggregation purposes, such as calculating averages, maximum values, sums, and calculating entries.

[0048] For example, the accounting start date for business A is January 1, 2020, and the value of a value field in the specific business field is 10000;

[0049] Business B's accounting start date is January 1, 2020, and the value field value is 10000;

[0050] The closing accounting date for Transaction B is January 1, 2021;

[0051] Business B was re-entered on January 3, 2021, and the value field value increased by 299.96;

[0052] In user query scenarios:

[0053] If you query the value field before January 1, 2020, the statistical value is 0;

[0054] If you query any day between January 1, 2020 and January 1, 2021, the statistical value of the value field is 20000;

[0055] If you query any day between January 1, 2021 and January 3, 2021, the statistical value of the value field is 10000;

[0056] If you query any day after January 3, 2021, the statistical value of the value field is 20299.96.

[0057] The following example, using a real-world user query scenario where the beginning date (farthest query time) is February 1, 2020, and the end date (most recent query time) is January 10, 2021, further illustrates how grouping and statistical calculations are performed. Here, "beginning date" refers to the historical state and historical value of all business tables at the beginning date, while "end date" refers to the state and value of all business tables at the end date. The changes in value should be dynamically displayed, even with the user-selected beginning and end date spans.

[0058] For example: the beginning date is February 1, 2020, and the end date is January 10, 2021. The statistical value of the beginning value field is 10000 + 10000 = 20000. The increase in this value field during the period is 10299.96. The decrease in this value field during the period is 10000. The statistical value of this value field at the end of the period is 20299.96.

[0059] For all the above queries, when a user makes a request, a specific summation statement needs to be executed in the business details table to obtain the summary data of the specified fields. When the data volume is hundreds of millions, the execution efficiency of the summation statement executed in this part of the business details table has a bottleneck. Therefore, the intermediate summary table below needs to be introduced.

[0060] In this embodiment of the invention, the summary fields of the intermediate summary table are determined according to preset data summary rules, which can be obtained by statistical analysis of user needs. For example, the summary fields include total quantity field, quantity increase field, quantity decrease field, total value field, value increase field, and value decrease field.

[0061] When a query is needed later, you only need to determine if the desired query date falls within the time zipper range of the furthest query time (beginning date) and the most recent query time (end date) in the summary field statistics to intuitively know the value / quantity of that day.

[0062] The increase or decrease in quantity and value is mainly determined by which data's furthest query time falls within the user-selected date range, and these data increases or decreases are summed. This gives the increase or decrease details, for example:

[0063] The period begins on February 1, 2020, and ends on January 10, 2021.

[0064] Given an initial date of February 1, 2020, within which segment of the time zipper in the intermediate summary table can we determine that February 1, 2020 is greater than the initial date of the first data entry (2020-01-01) and less than the final date of the first data entry (2021-01-01)? Therefore, the value corresponding to the initial date can be directly taken as the sum of the values ​​of the first data entry, which is 20000, instead of the original 10000+10000.

[0065] Value increase refers to the changes in business growth during the period from the beginning to the end of the period. In the range of 2020-02-01 to 2021-01-10, the method for calculating value increase is to determine which data's beginning date falls within the user's selected date range. Here we can see that the second data, 2021-01-01, and the third data, 2021-01-03, fall within this time range. Therefore, the value increase for this period should be the sum of the value increases of the two, which is 0 + 10299.96.

[0066] The difference between calculating value decrease and value increase is that instead of adding up the value increases, we add up the value decreases.

[0067] The data sources in the intermediate summary table mainly include the following:

[0068] (1) Source of full planned tasks: The result of summarizing all business data tables.

[0069] (2) Sources of incremental planning tasks:

[0070] (a) Results after summarizing the newly added data;

[0071] (b) Delete the inverted result after data aggregation. This part of the data is used to reverse the data that has already been aggregated.

[0072] (c) Modify data results. This part of the data is used to supplement the data that has already been summarized.

[0073] As mentioned earlier, existing business data storage is performed as a scheduled task, but its execution efficiency is too low. To ensure the execution efficiency of scheduled tasks, this embodiment of the invention splits scheduled tasks into two parts: a full task and an incremental task. The full task is used to reset or calibrate and initialize the intermediate summary table data. The incremental task is used to ensure the iterative update of the intermediate summary table data.

[0074] Each step will be explained in detail below.

[0075] Step 101: Execute the full task. The full task requires summarizing the business data in the business details table and inserting it into the intermediate summary table according to the preset data summarization rules.

[0076] The full task integrates the data to be queried, storing the statistical results of each unit and field in an intermediate summary table, hence the name "full task." Since the intermediate summary table is the result of unified calculations of various business data, it summarizes all business data from the original business detail table according to preset data aggregation rules, resulting in a significantly smaller data volume compared to the original business detail table. Simultaneously, when data is inserted into the intermediate summary table, a batch number is assigned, recording the insertion time. This facilitates the next incremental task in filtering out newly added data between the execution of the full task and the next incremental task. Therefore, the results can be retrieved directly from the intermediate table, eliminating the need for complex calculations.

[0077] Since this full task involves executing business data from the entire business details table, its execution efficiency is relatively slow. Therefore, this plan is considered to be used for resetting or initializing data, and the execution frequency will be controlled.

[0078] Figure 2 This is a flowchart illustrating the execution of a full task in an embodiment of the present invention. In one embodiment, executing a full task includes:

[0079] Step 201: Clear the intermediate summary table;

[0080] Step 202: Concurrently, calculate the summary field values ​​of the business data in the business details table according to the preset data summary rules for each unit's business data to obtain the summary data, and insert it into the intermediate summary table.

[0081] Step 203: Add batch numbers to the summary data inserted into the intermediate summary table and record the timestamp corresponding to the batch numbers.

[0082] The full task is the initial action, a planned task that needs to be executed at the beginning of the program / periodically, used to reset / calibrate and initialize the intermediate summary table. The intermediate summary table is cleared because it may contain obsolete or changed data. Calibrating and correcting this data would increase the computational load; therefore, all data in the intermediate table is cleared and recalculated.

[0083] It should be noted that the preset data aggregation rules are generated based on historical data from customer query requirements. If a user's request can be of any dimension, for asynchronous tasks, the user's request is unpredictable when executing asynchronous scheduled tasks, and therefore the aggregation dimensions are also unpredictable. Two solutions are proposed to handle this requirement. First, pre-set all possible dimensions and aggregate multiple dimensions in a single intermediate summary table. This method is flexible and supports multi-dimensional aggregation. The disadvantage is that the data in the summary table may be very granular, making it impossible to directly locate the aggregation result based on a single data point. If the most granular dimension is not selected, further aggregation calculations are usually required due to the increased granularity. Second, use multiple intermediate summary tables, each with different dimensions. However, this method cannot aggregate multiple combined dimensions. The advantage is higher efficiency and easier maintenance. The disadvantage is a lack of flexibility, only supporting aggregation within a few commonly used preset aggregation fields.

[0084] The storage structure of the intermediate summary table can be designed according to user needs. The design principles for the storage structure are as follows:

[0085] (1) Reduce the original business table data to the minimum, find out the data characteristics as much as possible, and actually model and store them.

[0086] (2) Ease of use: Try to improve the intuitiveness and ease of use of the storage structure, and try to locate the status of the summary data through a single data entry.

[0087] (3) Scalability: retain as many expansion and maintenance ports as possible, and pre-set multi-dimensional and typical filtering condition fields.

[0088] It should be noted that the preset data aggregation rules are the basis and perspective for analyzing the data specified by the user, representing typical characteristics of the data, and specific grouping and statistical calculations are performed based on these characteristic fields. For user-specified multi-dimensional statistics, the aggregation rules here are preset user-specified dimensions, with frequently occurring usage scenarios being pre-calculated. Due to the unpredictability of user dimensions during the execution of planned tasks, it is impossible to predict all user usage scenarios for pre-calculation; therefore, the aggregation rules here are all fixed rules. Of course, these aggregation rules in this embodiment of the invention can be freely extended.

[0089] In this embodiment of the invention, business data from the business details table needs to be summarized and inserted into an intermediate summary table. The process of extracting and inserting business data is controlled by a full task, and the accuracy of the summarized data in the intermediate summary table is ensured. At the same time, a simplified page control is designed to control the frequency of the full task execution, thereby regularly maintaining the cleanliness of the data in the intermediate summary table.

[0090] Step 102: Execute the incremental task according to the execution time of the incremental task. The incremental task needs to summarize the business data that has not been summarized in the business details table and insert it into the intermediate summary table.

[0091] Figure 3 This is a flowchart illustrating the execution of incremental tasks in an embodiment of the present invention. In one embodiment, the incremental tasks are executed according to their execution times, including:

[0092] Step 301: Extract the marked change data from the business details table up to the time between the last full task completion time or the incremental task completion time and the current time. The change data includes newly added data, modified data, and deleted data.

[0093] Step 302: Concurrently, calculate the summary field values ​​for the change data of each unit according to the preset data summary rules, obtain the summary data, and insert it into the intermediate summary table.

[0094] Step 303: Add batch numbers to the summary data inserted into the intermediate summary table and record the timestamp corresponding to the batch numbers.

[0095] Specifically, incremental tasks are daily scheduled tasks, and their execution frequency needs to be set according to requirements. They are used to support the daily maintenance and updates of intermediate summary tables. Incremental tasks ensure data maintainability and continuity, while also solving the efficiency problem of executing scheduled tasks and providing a certain degree of fault tolerance.

[0096] Incremental tasks will retrieve all data added, modified, and deleted during the time period between the last executed task (regardless of whether it is a full task or an incremental task) and the execution of the current incremental task from the business details table, and then re-aggregate and insert it into the intermediate summary table according to the original data aggregation rules.

[0097] Since this incremental task only queries data added, deleted, or modified since the last scheduled task execution, it filters out large batches of existing data, resulting in a significantly smaller data volume. Therefore, the incremental task executes relatively quickly. This task is considered for daily use to ensure iterative updates of data in the intermediate summary table.

[0098] In practice, according to the storage structure design of the intermediate summary table, adding, modifying, and deleting data should all modify the business data in the intermediate summary table. However, in reality, adding, modifying, and deleting data do not directly modify the intermediate summary table; instead, they insert new summary data into it. Different designs can be implemented based on the specific storage structure of the intermediate summary table. For example, in asset management, because the historical asset status needs to be retained, an asset may have multiple versions, with each version's lifecycle distinguished by version number and date. These dates are in the form of date chains, and the start and end times of each version do not overlap. To ensure the execution efficiency of incremental tasks, adding, modifying, and deleting data here involves inserting summary data. This is because the insertion, modification, or deletion of a simple business data entry can affect the summary results of many data entries, essentially equivalent to rearranging all data in a single dimension. To avoid rearrangement, directly inserting summary data instead of modifying the original business data is an optimized choice. Of course, this insertion method will also lead to an increasing trend in data, so this full task needs to be executed periodically to calibrate the data in the benchmark and to merge modified and deleted data into the specified time version of the specified dimension.

[0099] The above incremental and full task solutions only guarantee that the data accuracy of the intermediate summary table is not affected, and that business data generated in the daily business detail table can be synchronized to the intermediate summary table at the same time every day. However, this is not real-time synchronization. If a user adds or modifies business data after the incremental task is executed, and the next incremental task has not yet been executed, the user will not be able to retrieve the business data from the intermediate summary table when clicking to query on the front-end page because the newly added business data has not been synchronized to the intermediate summary table. At this point, a real-time data solution needs to be considered. The following explains how to solve the real-time problem from three aspects: data addition, deletion, and modification.

[0100] Figure 4 This is a flowchart illustrating the business data change process in one embodiment of the present invention. In one embodiment, the method further includes:

[0101] Step 401: After receiving the new request initiated by the user, add the business data in the new request to the business details table, mark the status as new, and add a timestamp.

[0102] Step 402: After receiving the update request initiated by the user, modify the business data in the update request in the business details table, mark the status as updated, and add a timestamp;

[0103] Step 403: After receiving a deletion request initiated by a user, if the deletion request is a logical deletion, mark the business data in the deletion request as logical deletion in the business details table and add a timestamp; if the deletion request is a physical deletion, delete the business data corresponding to the deletion request in the business details table and insert the reversal data of the business data corresponding to the deletion request into the intermediate summary table.

[0104] Step 103: After receiving the business data query request initiated by the user, parse the business data query request to obtain a first time interval and a second time interval. The first time interval is from the time when the most recent incremental task was completed to the time when the most recent query was made, and the second time interval is from the time when the most distant query was made to the time when the most recent incremental task was completed.

[0105] In one embodiment, querying business data for a first time interval from a business detail table includes: using a first thread to query business data for a first time interval from the business detail table;

[0106] Querying summary data for the second time interval from the intermediate summary table and / or business detail table, including: using a second thread to query summary data for the second time interval from the intermediate summary table and / or business detail table;

[0107] Aggregate the queried business data and the queried summary data, and then return the aggregated data to the user. This includes using the main thread to aggregate the queried business data and the queried summary data, and then returning the aggregated data to the user.

[0108] Figure 5 This is a flowchart illustrating the summary data query process in one embodiment of the present invention. The process of querying summary data for a second time interval from an intermediate summary table and / or a business detail table includes:

[0109] Step 501: If the status of the summary data for the second time interval retrieved from the intermediate summary table is logically deleted, query the logically deleted business data in the business details table, summarize and invert the retrieved business data, and output the summary data.

[0110] Step 502: If the summary data for the second time interval retrieved from the intermediate summary table is reversal data, or is marked as newly added, or is marked as updated, or is marked as empty, directly output the retrieved summary data.

[0111] Figure 6 This is a flowchart of data aggregation in an embodiment of the present invention. In one embodiment, the aggregated business data and the aggregated summary data are performed, including:

[0112] Step 601: Calculate the summary field values ​​for the retrieved business data according to the preset data summary rules to obtain new summary data;

[0113] Step 602: Add the new summary data to the retrieved summary data according to the summary fields to obtain the aggregated data.

[0114] For example, a full planned task was executed at 2:00 AM on November 30th. The user performed add, delete, and modify operations at 10:00 AM on November 30th, and an incremental task was automatically executed at 0:00 AM on December 1st. The user then performed add, delete, and modify operations again at 12:00 PM on December 1st. At 1:00 PM on the same day, the user clicked the query function to initiate a business data query request.

[0115] First, the first time interval is determined to be from December 1st to 13:00 on that day, and the second time interval is from November 30th to December 1st.

[0116] Secondly, using the first thread, we query the business data for the first time interval from the business details table. This data includes the following three parts:

[0117] (1) New data added by the user from 0:00 on December 1st (the time the user last initiated the incremental task) to 13:00 on the same day (the time the user clicked to query);

[0118] (2) The user deleted data between 0:00 and 13:00 on December 1st;

[0119] (3) The user modified the data from 0:00 to 13:00 on December 1st.

[0120] These three parts of data are all data that have been statistically summarized according to the specified fields. However, these three parts of data will not be included in the intermediate summary table. They are business data that is queried in real time from the specific business details table and will be temporarily stored in memory. This part of the business data will be released after the query request is completed.

[0121] Next, a second thread is used to query the summary data for the second time interval from the intermediate summary table and / or the business detail table. If the summary data for the second time interval retrieved from the intermediate summary table is marked as logically deleted, then all logically deleted business data within the first time interval is retrieved from the business data table. This part of the business data is summarized and inverted according to the preset summary rules, and output as the summary data. If the summary data for the second time interval retrieved from the intermediate summary table is reversal data, or marked as added, or marked as updated, the retrieved summary data is directly output, that is, the summary data formed by all added, physically deleted, and modified business data within the second time interval. This part of the data is a summary of all data before the last execution of the planned task. This part of the summary data includes the following two sources:

[0122] (1) Full task: Summary data of all business data before 2:00 on November 30.

[0123] (2) Incremental task: The following three parts of data are retrieved from the intermediate summary table. The query logic is the same as the three parts of data in the above steps, except that this part of the data is the data that actually falls into the intermediate summary table.

[0124] New data added between 2:00 AM on November 30th and 12:00 AM on December 1st;

[0125] Data will be deleted from 2:00 AM on November 30th to 12:00 AM on December 1st.

[0126] Data will be modified from 2:00 AM on November 30th to 12:00 AM on December 1st.

[0127] The main thread waits for both threads to finish executing, calculates the summary field values ​​for the queried business data according to the preset data summary rules, and obtains new summary data. Then, according to the summary fields, the new summary data is added to the queried summary data to obtain aggregated data.

[0128] In this embodiment of the invention, deletion includes physical deletion and logical deletion.

[0129] 1. Logical deletion

[0130] Logical deletion is widely used in asset business scenarios. Almost all business operations involving deletion are logical deletions, intended to retain previous operation records and support business rollback operations. Specifically, when actually executing a deletion operation, the business data in the deletion request is marked as logically deleted in the business details table, and a timestamp is added. Generally, a separate field can be designed, such as the IS_DELETED field 1. This field exists in almost all business record tables, and its data enumeration values ​​are 1 and 2, where 1 represents logical deletion and 2 represents no logical deletion.

[0131] If the summary data for the second time interval retrieved from the intermediate summary table is marked as logically deleted, then all logically deleted business data within the first time interval is retrieved from the business data table. A specific scenario will be provided below.

[0132] Scenario: On November 30th, a user adds a new business data entry to the business details table. The incremental task is automatically executed on December 1st. At the same time, the user deletes the business data at 12:00 noon on December 1st and queries the data at 1:00 PM.

[0133] For the above scenario, the incremental task on December 1st will obviously include the new business data added on November 30th and insert it into the intermediate summary table. When users actually query, they need to query two parts of data. Therefore, it is necessary to form a first time interval and a second time interval, obtain the business data of the first time interval in the business details table, and summarize the data according to the specified fields. The summary data is then inverted (reducing the new data counted in the summary table), and the summary data of the intermediate summary table in the second time interval is obtained. Both parts of the summary data are kept in memory at the same time. Data with the same specified fields in the two parts are merged, and data with the same dimensions are summed. The result is the summary data for that day.

[0134] 2. Physical deletion

[0135] Physical deletion cannot detect from the specific business data table which data was deleted between the time the user initiated the query request and the period of the most recent scheduled task execution. Therefore, it requires comprehensive intervention at the specific business level. That is, after the business data is deleted, subsequent intervention processing is performed. The steps are explained as follows:

[0136] When a user clicks the delete action, the backend will publish an asynchronous event to notify the listener that a business has been physically deleted, and at the same time pass the business data as a parameter to the listener.

[0137] The listener retrieves business data from the parameters and directly inserts the inverse value of the corresponding dimension's summary field into the intermediate summary table to offset the positive values ​​of the historical status already counted in the remittance business details table.

[0138] Without the above steps, users will still be querying the results before the business data was deleted, and this deleted data cannot be traced.

[0139] This invention also provides a business data query device, as described in the following embodiments. Since the principle by which this device solves the problem is similar to that of the business data query method, the implementation of this device can refer to the implementation of the business data query method; repeated details will not be elaborated further.

[0140] like Figure 7 This is a schematic diagram of a business data query device in an embodiment of the present invention, comprising:

[0141] The full task execution module 701 is used to execute the full task. The full task needs to summarize the business data in the business details table and insert it into the intermediate summary table according to the preset data summary rules. The intermediate summary table includes multiple summary fields, which are generated according to the preset data summary rules.

[0142] The incremental task execution module 702 is used to execute the incremental task according to the execution time of the incremental task. The incremental task needs to summarize the business data that has not been summarized in the business details table and insert it into the intermediate summary table.

[0143] The request decomposition module 703 is used to parse the business data query request initiated by the user after receiving the request, and obtain a first time interval and a second time interval. The first time interval is from the time when the most recent incremental task was completed to the time when the most recent query was made, and the second time interval is from the time when the most distant query was made to the time when the most recent incremental task was completed.

[0144] The first query module 704 is used to query business data for the first time interval from the business details table;

[0145] The second query module 705 is used to query the summary data of the second time interval from the intermediate summary table and / or business detail table;

[0146] The data aggregation module 706 is used to aggregate the queried business data and the queried summary data, and then feed the aggregated data back to the user.

[0147] In one embodiment, the full task execution module is used for:

[0148] Clear the intermediate summary table;

[0149] Concurrently, the business data of each unit is processed according to the preset data aggregation rules. The aggregation field values ​​of the business data in the business details table are calculated to obtain the aggregation data, which is then inserted into the intermediate aggregation table.

[0150] Add batch numbers to the summary data inserted into the intermediate summary table and record the timestamp corresponding to the batch numbers.

[0151] In one embodiment, the incremental task execution module is used to:

[0152] From the business details table, extract the changed data with marked status from the last full task completion time or incremental task completion time to the current time. The changed data includes newly added data, modified data, and deleted data.

[0153] Concurrently, the change data of each unit is processed according to the preset data aggregation rules. The values ​​of the aggregation fields are calculated for the change data to obtain the aggregated data, which is then inserted into the intermediate summary table.

[0154] Add batch numbers to the summary data inserted into the intermediate summary table and record the timestamp corresponding to the batch numbers.

[0155] In one embodiment, the apparatus further includes a business data change module, used for:

[0156] After receiving a new request from a user, add the business data from the new request to the business details table, mark the status as new, and add a timestamp.

[0157] After receiving an update request from a user, modify the business data in the update request in the business details table, mark the status as updated, and add a timestamp;

[0158] Upon receiving a deletion request initiated by a user, if the deletion request is a logical deletion, the business data in the deletion request is marked as logically deleted in the business details table, and a timestamp is added; if the deletion request is a physical deletion, the business data corresponding to the deletion request is deleted in the business details table, and the reversal data of the business data corresponding to the deletion request is inserted into the intermediate summary table.

[0159] In one embodiment, the second query module is used for:

[0160] If the summary data for the second time interval retrieved from the intermediate summary table is marked as logically deleted, query the logically deleted business data in the business details table, summarize and invert the retrieved business data, and output the summary data.

[0161] If the summary data for the second time interval retrieved from the intermediate summary table is reversal data, or is marked as newly added, or is marked as updated, or is marked as empty, the retrieved summary data will be output directly.

[0162] In one embodiment, the data aggregation module is used for:

[0163] According to the preset data aggregation rules, the aggregated field values ​​are calculated on the queried business data to obtain new aggregated data;

[0164] Based on the summary fields, the new summary data is added to the corresponding summary data retrieved from the query to obtain aggregated data.

[0165] In one embodiment, the first query module is used to: use a first thread to query business data for a first time interval from the business details table;

[0166] The second query module is used to: use a second thread to query the summary data of the second time interval from the intermediate summary table and / or the business detail table;

[0167] The data aggregation module is used to aggregate the queried business data and the queried summary data using the main thread, and then return the aggregated data to the user.

[0168] This invention also provides a computer program product, which includes a computer program that, when executed by a processor, implements the above-described business data query method.

[0169] This invention also provides a computer-readable storage medium storing a computer program that, when executed by a processor, implements the aforementioned business data query method.

[0170] Based on the aforementioned inventive concept, such as Figure 8 As shown, the present invention also proposes a computer device 800, including a memory 810, a processor 820, and a computer program 830 stored in the memory 810 and executable on the processor 820. When the processor 820 executes the computer program 830, it implements the aforementioned business data query method.

[0171] In this embodiment of the invention, a full task is executed. This full task requires summarizing business data from the business detail table according to preset data summarization rules and inserting it into an intermediate summary table. The intermediate summary table includes multiple summary fields generated according to the preset data summarization rules. Incremental tasks are executed according to their execution times. These incremental tasks require summarizing unsummarized business data from the business detail table and inserting it into the intermediate summary table. Upon receiving a user-initiated business data query request, the request is parsed to obtain a first time interval and a second time interval. The first time interval is from the completion time of the most recent incremental task to the most recent query time, and the second time interval is from the furthest query time to the completion time of the most recent incremental task. Business data for the first time interval is queried from the business detail table. Summarized data for the second time interval is queried from the intermediate summary table and / or the business detail table. The queried business data and the queried summarized data are aggregated, and the aggregated data is fed back to the user. Through the above steps, the full and incremental tasks insert business data into the intermediate summary table. This allows users to query only the massive business detail table for the first time interval, and directly query the summary data in the intermediate summary table according to the preset data aggregation rules for the second time interval, greatly improving the efficiency of business data query.

[0172] Those skilled in the art will understand that embodiments of the present invention can be provided as methods, systems, or computer program products. Therefore, the present invention can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, the present invention can take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0173] This invention is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the invention. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart illustrations and / or block diagrams. Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0174] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0175] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

[0176] The specific embodiments described above further illustrate the purpose, technical solution, and beneficial effects of the present invention. It should be understood that the above descriptions are merely specific embodiments of the present invention and are not intended to limit the scope of protection of the present invention. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of the present invention should be included within the scope of protection of the present invention.

Claims

1. A business data query method, characterized in that, include: Execute a full task. The full task requires summarizing the business data in the business details table and inserting it into an intermediate summary table according to a preset data summarization rule. The intermediate summary table includes multiple summary fields, which are generated according to the preset data summarization rule. The incremental task is executed according to the execution time of the incremental task. The incremental task needs to summarize the business data that has not been summarized in the business details table and insert it into the intermediate summary table. After receiving a business data query request initiated by a user, the business data query request is parsed to obtain a first time interval and a second time interval. The first time interval is from the time when the most recent incremental task was completed to the time when the most recent query was made, and the second time interval is from the time when the most distant query was made to the time when the most recent incremental task was completed. The first thread queries the business data for the first time interval from the business details table. A second thread is used to query the summary data for the second time interval from the intermediate summary table and / or the business detail table; The main thread aggregates the retrieved business data and the retrieved summary data, and then returns the aggregated data to the user. Retrieve summary data for the second time interval from the intermediate summary table and / or business detail table, including: If the summary data for the second time interval retrieved from the intermediate summary table is marked as logically deleted, query the logically deleted business data in the business details table, summarize and invert the retrieved business data, and output the summary data. If the summary data for the second time interval retrieved from the intermediate summary table is reversal data, or is marked as newly added, or is marked as updated, or is marked as empty, the retrieved summary data will be output directly. Logical deletion retains previous operation records and supports business rollback operations.

2. The business data query method as described in claim 1, characterized in that, Execute the full task, including: Clear the intermediate summary table; Concurrently, the business data of each unit is processed according to the preset data aggregation rules. The aggregation field values ​​of the business data in the business details table are calculated to obtain the aggregation data, which is then inserted into the intermediate aggregation table. Add batch numbers to the summary data inserted into the intermediate summary table and record the timestamp corresponding to the batch numbers.

3. The business data query method as described in claim 1, characterized in that, The incremental task is executed according to its execution time, including: From the business details table, extract the changed data with marked status from the last full task completion time or incremental task completion time to the current time. The changed data includes newly added data, modified data, and deleted data. Concurrently, the change data of each unit is processed according to the preset data aggregation rules. The values ​​of the aggregation fields are calculated for the change data to obtain the aggregated data, which is then inserted into the intermediate summary table. Add batch numbers to the summary data inserted into the intermediate summary table and record the timestamp corresponding to the batch numbers.

4. The business data query method as described in claim 1, characterized in that, Also includes: After receiving a new request from a user, add the business data from the new request to the business details table, mark the status as new, and add a timestamp. After receiving an update request from a user, modify the business data in the update request in the business details table, mark the status as updated, and add a timestamp; Upon receiving a deletion request initiated by a user, if the deletion request is a logical deletion, the business data in the deletion request is marked as logically deleted in the business details table, and a timestamp is added. If the deletion request is a physical deletion, delete the business data corresponding to the deletion request in the business details table, and insert the reduction data of the business data corresponding to the deletion request into the intermediate summary table.

5. The business data query method as described in claim 1, characterized in that, Aggregate the retrieved business data and the retrieved summary data, including: According to the preset data aggregation rules, the aggregated field values ​​are calculated on the queried business data to obtain new aggregated data; Based on the summary fields, the new summary data is added to the corresponding summary data retrieved from the query to obtain aggregated data.

6. A business data query device, characterized in that, include: The full task execution module is used to execute full tasks. The full tasks need to summarize the business data in the business details table and insert it into the intermediate summary table according to the preset data summary rules. The intermediate summary table includes multiple summary fields, which are generated according to the preset data summary rules. The incremental task execution module is used to execute the incremental task according to the execution time of the incremental task. The incremental task needs to summarize the business data that has not been summarized in the business details table and insert it into the intermediate summary table. The request decomposition module is used to parse the business data query request after receiving the request initiated by the user, and obtain a first time interval and a second time interval. The first time interval is from the time when the most recent incremental task was completed to the time when the most recent query was made, and the second time interval is from the time when the most distant query was made to the time when the most recent incremental task was completed. The first query module is used to query business data for the first time interval from the business details table using the first thread. The second query module is used to query the summary data of the second time interval from the intermediate summary table and / or business detail table using the second thread; The data aggregation module is used to aggregate the queried business data and the queried summary data using the main thread, and then feed the aggregated data back to the user. The second query module is used to: if the flag status of the summary data for the second time interval retrieved from the intermediate summary table is logically deleted, query the logically deleted business data in the business details table, summarize and invert the retrieved business data, and output the summary data. If the summary data for the second time interval retrieved from the intermediate summary table is reversal data, or is marked as newly added, or is marked as updated, or is marked as empty, the retrieved summary data will be output directly. Logical deletion retains previous operation records and supports business rollback operations.

7. A computer device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the computer program, it implements the method of any one of claims 1 to 5.

8. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores a computer program that, when executed by a processor, implements the method of any one of claims 1 to 5.

9. A computer program product, characterized in that, The computer program product includes a computer program that, when executed by a processor, implements the method of any one of claims 1 to 5.

Images