Business data query method and device

Abstract

The invention discloses a business data query method and device, and relates to the technical field of big data, and the method comprises the steps: executing a full task, which needs to summarize business data in a business detail table and then insert the summarized business data into an intermediate summary table; the incremental task is executed according to the execution moment of the incremental task, and the incremental task needs to summarize unsummarized business data in the business detail table and then insert the summarized business data into the intermediate summary table; after a service data query request initiated by a user is received, the service data query request is analyzed, and a first time interval and a second time interval are obtained; querying service data of the first time interval from the service detail list; querying summarized data of a second time interval from the intermediate summarized table and / or the business detail table; and aggregating the queried service data and the queried summarized data, and feeding back the aggregated data to the user. The method can improve the real-time summary query efficiency of hundreds of millions of data volume services.

Description

Technical Field

[0001] The present invention relates to the technical field of database processing, and particularly to a method and device for querying business data. Background Art

[0002] This section aims to provide background or context for the embodiments of the present invention stated in the claims. The description herein is not admitted to be prior art merely by virtue of being included in this section.

[0003] With the rapid development of technology, users on various projects have increasingly higher requirements for the efficiency of complex query business scenarios in asset operations, and at the same time require ensuring the accuracy and real-time nature of aggregated query data. Therefore, data query in complex scenarios, especially real-time aggregated query based on hundreds of millions of data volumes after multi-dimensional grouping statistics, is a thorny problem faced by current databases. Summary of the Invention

[0004] Embodiments of the present invention provide a method for querying business data to improve the efficiency of real-time aggregated query for business data volumes of hundreds of millions. The method includes:

[0005] Execute a full-volume task, which needs to aggregate the business data in the business detail table according to a preset data aggregation rule and insert it into an intermediate aggregation table. The intermediate aggregation table includes multiple aggregation fields, and the aggregation fields are generated according to the preset data aggregation rule;

[0006] Execute the incremental task according to the execution time of the incremental task. The incremental task needs to aggregate the unaggregated business data in the business detail table and insert it into the intermediate aggregation table;

[0007] After receiving a business data query request initiated by a 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 completion time of the most recent incremental task to the most recent query time, and the second time interval is from the farthest query time to the completion time of the most recent incremental task;

[0008] Query the business data in the first time interval from the business detail table;

[0009] Query the aggregated data in the second time interval from the intermediate aggregation table and / or the business detail table;

[0010] Aggregate the queried business data and the queried aggregated data, and feedback the aggregated data to the user.

[0011] Embodiments of the present invention further provide a device for querying business data to improve the efficiency of real-time aggregated query for business data volumes of hundreds of millions. The device includes:

[0012] A full - volume task execution module, which is used to execute full - volume tasks. The full - volume tasks need to summarize the business data in the business detail table according to a preset data summarization rule and then insert it into the intermediate summary table. The intermediate summary table includes multiple summary fields, and the summary fields are generated according to the preset data summarization rule;

[0013] An incremental task execution module, which is used to execute the incremental task according to the execution time of the incremental task. The incremental task needs to summarize the un - summarized business data in the business detail table and then insert it into the intermediate summary table;

[0014] A request decomposition module, which is used to parse the business data query request after receiving the business data query request initiated by the user, and 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 farthest query time to the completion time of the most recent incremental task;

[0015] A first query module, which is used to query the business data in the first time interval from the business detail table;

[0016] A second query module, which is used to query the summary data in the second time interval from the intermediate summary table and / or the business detail table;

[0017] A data aggregation module, which is used to aggregate the queried business data and the queried summary data and feedback the aggregated data to the user.

[0018] An embodiment of the present invention also provides a computer device, including a memory, a processor, and a computer program stored on the memory and executable on the processor. When the processor executes the computer program, the above - mentioned business data query method is implemented.

[0019] An embodiment of the present invention also provides a computer - readable storage medium. The computer - readable storage medium stores a computer program. When the computer program is executed by a processor, the above - mentioned business data query method is implemented.

[0020] An embodiment of the present invention also provides a computer program product. The computer program product includes a computer program. When the computer program is executed by a processor, the above - mentioned business data query method is implemented.

[0021] In an embodiment of the present invention, a full - volume task is executed. The full - volume task needs to summarize the business data in the business detail table according to a preset data summarization rule and then insert it into the intermediate summary table. The intermediate summary table includes multiple summary fields, and the summary fields are generated according to the preset data summarization rule. At the execution time of the incremental task, the incremental task is executed. The incremental task needs to summarize the un - summarized business data in the business detail table and then 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 completion time of the most recent incremental task to the most recent query time, and the second time interval is from the farthest query time to the completion time of the most recent incremental task. The business data in the first time interval is queried from the business detail table. The summary data in the second time interval is queried from the intermediate summary table and / or the business detail table. The queried business data and the queried summary data are aggregated, and the aggregated data is fed back to the user. Through the above steps, the full - volume task and the incremental task insert the business data into the intermediate summary table, so that when the user queries, for the query requirement of the first time interval, only the huge business detail table needs to be queried; for the query requirement of the second time interval, the summary data can be directly queried from the intermediate summary table summarized according to the preset data summarization rule, greatly improving the efficiency of business data query. BRIEF DESCRIPTION OF THE DRAWINGS

[0022] In order to more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the following will briefly introduce the drawings required for use in the description of the embodiments or the prior art. Obviously, the following - described drawings are only some embodiments of the present invention. For those of ordinary skill in the art, other drawings can be obtained based on these drawings without creative efforts. In the drawings:

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

[0024] Figure 2 It is a flowchart of executing a full - volume task in an embodiment of the present invention;

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

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

[0027] Figure 5 It is a flowchart of querying summary data in an embodiment of the present invention;

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

[0029] Figure 7 It is a schematic diagram of the service data query device in the embodiment of the present invention;

[0030] Figure 8 It is a schematic diagram of a computer device provided in the embodiment of the present invention. Detailed implementation manners

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

[0032] In the technical solutions of this application, the acquisition, storage, use, processing, etc. of data all comply with the relevant regulations of national laws and regulations.

[0033] The term "and / or" in this article only describes an association relationship, indicating that there can be three relationships. For example, A and / or B can represent: A exists alone, A and B exist simultaneously, and B exists alone. In addition, the term "at least one" in this article means any one of multiple or any combination of at least two of multiple. For example, including at least one of A, B, and C can represent any one or more elements selected from the set composed of A, B, and C.

[0034] In the description of this specification, the terms "include", "comprise", "have", "contain", etc. are all open-ended terms, that is, they are meant to include but not limited to. The descriptions referring to terms such as "an embodiment", "a specific embodiment", "some embodiments", "for example", etc. mean that the specific features, structures or characteristics described in connection with the embodiment or example are included in at least one embodiment or example of this application. In this specification, the schematic descriptions of the above terms do not necessarily refer to the same embodiment or example. Moreover, the specific features, structures or characteristics described can be combined in a suitable manner in any one or more embodiments or examples. The step sequences involved in each embodiment are used to schematically illustrate the implementation of this application, and the step sequences are not limited and can be adjusted appropriately as needed.

[0035] The inventor found that when querying data, the data in the business details table can be regularly stored in the intermediate summary table according to the summary dimension, and the result can be obtained by directly querying the intermediate summary table instead of directly querying the data in the business details table. Since the intermediate summary table groups and summarizes fields such as amount and quantity in the actual business details table according to certain rules, the amount of data is much less than that in the actual table, and the performance of querying this preprocessed intermediate summary table is much faster than that of the actual business details table. However, this interactive solution for scheduled tasks will cause the execution efficiency of the scheduled task of regularly summarizing business data in the business details table to the intermediate summary table to be very slow every day. And for users, the real-time performance cannot be guaranteed, and there will be a one-day delay.

[0036] Therefore, it is necessary to improve the execution efficiency of the scheduled task and the real-time performance of business data, which is a real-time query solution.

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

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

[0039] Step 102: Execute the incremental task according to the execution time of the incremental task. The incremental task needs to summarize the un-summarized business data 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 completion time of the most recent incremental task to the most recent query time, and the second time interval is from the farthest query time to the completion time of the most recent incremental task;

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

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

[0043] Step 106: Aggregate the queried business data and the queried summary data, and feedback the aggregated data to the user.

[0044] In the embodiments of the present invention, full-volume tasks and incremental tasks insert business data into an intermediate summary table, so that when a user queries, for the query requirement in the first time interval, only the huge business detail table needs to be queried; for the query requirement in the second time interval, the summary data can be directly queried from the intermediate summary table summarized according to the preset data summarization rules, greatly improving the efficiency of business data query.

[0045] In the embodiments of the present invention, the business detail table represents the life cycle of each business order in each time line. The same business order may have multiple versions, and the value and amount of each version may be inconsistent.

[0046] The fields included in the business detail table are unique identifier, business version number, business status, specific business fields, business start accounting date and business end accounting date, amount and quantity. Among them, the business status includes new, updated and logically deleted. The specific business fields include multiple ones, which are related to the specific business and will not be elaborated here. The business start accounting date and the business end accounting date will not have an intersection, and between the versions of multiple businesses, this date is always continuous, in the form of a date zipper, with the head and tail corresponding.

[0047] Taking the asset system as an example, an asset may have multiple historical versions, indicating the number of historical states and value of the card in each life cycle. Historical data is recorded in this way. 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 example, for a vehicle asset, the quantity is always 1, and the value is the actual value of the vehicle. Here, the quantity and value actually generally refer to the numerical field statistical indicators in each system, which can be any indicator, such as the number of orders, the number of goods, the number of commodity prices, scores, etc. on a certain shopping website, for aggregation use, and can be used to calculate the average value, maximum value, sum, count entries, etc.

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

[0049] The business start accounting date of business B is January 1, 2020, and the value field value is 10,000;

[0050] The business end accounting date of business B is January 1, 2021;

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

[0052] In the user query scenario:

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

[0054] If any day between January 1, 2020 and January 1, 2021 is queried, the statistical value of the value field is 20,000;

[0055] If any day between January 1, 2021 and January 3, 2021 is queried, the statistical value of the value field is 10,000;

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

[0057] The following takes the case where in the actual query scenario of the user, the start date (the farthest query time) is selected as February 1, 2020 and the end date (the nearest query time) is selected as January 10, 2021 as an example to further illustrate how to perform grouped calculation and statistics. Among them, the meaning of the start is how the historical status and historical value quantity of all business tables are at the moment of the start date, and the end refers to how the end moment status and value quantity of all business tables are at the moment of the end date. By dynamically displaying the increased and decreased value situations while the span between the selected start and end by the user is different.

[0058] For example: the start date is February 1, 2020, the end date is January 10, 2021, the statistical value of the value field at the start is 10,000 + 10,000 = 20,000, the statistical increase of this value field in this period is: 10299.96; the statistical decrease of the value field in this period is: 10,000, and the statistical value of this value field at the end is: 20299.96.

[0059] For all the above query data, when the user sends a request, a specific summation statement needs to be executed in the business detail table to obtain the summary data of the specified field statistics. When the data volume reaches hundreds of millions, there is a bottleneck in the execution efficiency of the summation statement executed by this part of the business detail table, so the following intermediate summary table needs to be introduced.

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

[0061] When subsequent queries are needed, it is only necessary to determine that the required query date is within the time zipper interval of the farthest query time (start date) and the nearest query time (end date) of the summary field statistics, and then the value / quantity of that day can be intuitively known.

[0062] The increase and decrease of quantity and value mainly depend on which data's farthest query time is within the user-selected date range, and add up this part of the increased and decreased data. Then the increase and decrease situation can be obtained. For example:

[0063] The start date is February 1, 2020, and the end date is January 10, 2021;

[0064] At the moment of February 1, 2020, in which section of the time zipper in the intermediate summary table can it be determined that February 1, 2020 is greater than the start date of the first piece of data, January 1, 2020, and less than the end date of the first piece, January 1, 2021? Therefore, the value corresponding to the start date can directly take the value total of the first piece of data, which is 20,000, without having to add 10,000 + 10,000 as before.

[0065] An increase in value means counting the business growth and change situation during the start and end periods. In the range from February 1, 2020 to January 10, 2021, the calculation method for the increase in value is to determine which data has a start date within the date range selected by the user. Here, it can be seen that the second piece with a start date of January 1, 2021 and the third piece with a start date of January 3, 2021 are within this time range. Therefore, the increase in value for this period should be the sum of the value increases of the two, which is 0 + 10299.96.

[0066] The calculation method for a decrease in value is the same as that for an increase in value. The difference is that the addition of the value increase amounts is replaced by the addition of the value decrease amounts.

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

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

[0069] (2) Source of incremental planned tasks:

[0070] (a) The result of summarizing newly added data;

[0071] (b) The negated result of summarizing deleted data. This part of the data is used to subtract the data that has already been summarized.

[0072] (c) The result of modified data. This part of the data is used to supplement the data that has already been summarized after modification.

[0073] As mentioned above, when storing existing business data, it is a scheduled task, but the execution efficiency is too low. To ensure the execution efficiency of the scheduled task, the embodiments of the present invention split the scheduled task into two parts: a full - volume task and an incremental task. The full - volume task is used to reset the data in the intermediate summary table or calibrate and initialize the data in the intermediate summary table. The incremental task is used to ensure the iterative update of the data in the intermediate summary table.

[0074] The following details each step.

[0075] Step 101: Execute the full - volume task. The full - volume task needs to summarize the business data in the business detail table according to the preset data summarization rule and then insert it into the intermediate summary table.

[0076] The full - volume task integrates the data to be queried and stores the statistical results of each unit and each field into the intermediate summary table, so it is called the full - volume task. Since the intermediate summary table is the result of unified calculation of each business data, it will summarize all the business data in the original business detail table according to the preset data summarization rule. Therefore, the data volume is much less than that of the original business detail table. At the same time, when inserting into the intermediate summary table, a batch number will be assigned to this batch of data to record the time of this data insertion, which is convenient for the incremental task to filter out the new data between the execution of this full - volume task and the execution of the next incremental task. Therefore, directly querying the intermediate table to obtain the result is sufficient without querying for redundant complex calculations.

[0077] Since this full - volume task is executed on the business data of the entire business detail table, its execution efficiency is relatively slow. Therefore, it is considered to be used as reset data or initialization data, and the execution frequency is controlled.

[0078] Figure 2 This is the flowchart for executing the full - volume task in the embodiments of the present invention. In one embodiment, executing the full - volume 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 detail table according to the preset data summarization rule for the business data of each unit, and then obtain the summary data and insert it into the intermediate summary table.

[0081] Step 203, assign a batch number to the summary data inserted into the intermediate summary table and record the time stamp corresponding to the batch number.

[0082] The full - volume task is the starting action and is a planned task that needs to be executed initially / periodically for the reset / calibration and initialization of the intermediate summary table. Clearing the intermediate summary table is because there may be data that has become invalid or has been changed in the intermediate summary table. If we want to calibrate and correct this part of the data, it will increase the calculation amount. Here, all the data in the intermediate table is directly cleared and re - counted.

[0083] It should be noted that the preset data summarization rule is a summarization rule generated based on the historical data required by the customer's query. If the user's request can be any request dimension, for asynchronous tasks, the user's request is unpredictable when the asynchronous timed task is executed. Therefore, it is also unpredictable according to which dimension to summarize. Two solutions are proposed here to handle this requirement. One is to preset all possible dimensions in advance and count the summary fields of multiple dimensions in an intermediate summary table. This method is flexible and can support multi-dimensional summarization. The disadvantage is that the data in the summary table may be very detailed, and it is impossible to directly locate the summary result based on a single piece of data. If the selected dimension is not the most detailed dimension, it is usually necessary to perform summary calculations on the data because it is more detailed. The other is to use multiple intermediate summary tables, each of which is a different dimension, but it is impossible to achieve multi-dimensional combined summarization. The advantage is that the efficiency is higher and it is convenient to maintain. The disadvantage is that it lacks flexibility and only supports summarization within the dimension range of a few commonly preset summary fields.

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

[0085] (1) Reduce the data of the original business table to the extreme, and try to find out the data characteristics and actually model and store them.

[0086] (2) Usability. Try to improve the intuitive usability of the storage structure, and try to locate the summary data status through a single piece of data.

[0087] (3) Scalability. Try to reserve the expansion and maintenance interfaces, and preset multi-dimensional and typical filtering condition fields.

[0088] It should be noted that the preset data summarization rule is the basis and perspective for analyzing data specified by the user, which belongs to the typical characteristics of the data, and specific grouping statistical calculations are performed according to these characteristic fields. For the multi-dimensional statistics specified by the user, the summarization rule here is to preset the dimensions specified by the user and calculate in advance the dimensions of the usage scenarios with higher occurrence frequencies. Due to the unpredictability of the user dimensions during the execution of the scheduled task, it is impossible to predict all user usage scenarios for presetting. Therefore, the summarization rules here are all fixed rules. Of course, these summarization rules in the embodiments of the present invention can be freely extended.

[0089] In the embodiments of the present invention, it is necessary to summarize the business data in the business detail table and insert it into the intermediate summary table, control the process of extracting and inserting business data through a full-volume task, and ensure the correctness of the summary data in the intermediate summary table. At the same time, a simple page is also designed to control the execution frequency of the full-volume scheduled task, so as to regularly maintain the data cleanliness of 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 unaggregated business data in the business detail table and then insert it into the intermediate summary table.

[0091] Figure 3 This is the flowchart for executing the incremental task in the embodiments of the present invention. In one embodiment, executing the incremental task according to the execution time of the incremental task includes:

[0092] Step 301: Extract the changed data with marked status from the business detail table from the completion time of the last full-volume task or the completion time of the incremental task to the current time. The changed data are new data, modified data, and deleted data.

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

[0094] Step 303: Mark the batch number for the summary data inserted into the intermediate summary table, and record the time stamp corresponding to the batch number.

[0095] Specifically, the incremental task is a daily planned task, and the execution frequency needs to be set according to requirements, which is used for the daily maintenance and update of the intermediate summary table. The incremental task ensures the maintainability and continuity of the data, and at the same time solves the efficiency problem of executing the planned task, and has a certain degree of fault tolerance.

[0096] The incremental task will query all the data that are newly added, modified, and deleted during the period between the time points of the last executed task (regardless of full-volume task and incremental task) and the execution of this incremental task in the business detail table, and summarize and insert it into the intermediate summary table again according to the original data summarization rules.

[0097] Since this incremental planned task only queries the data that are newly added, deleted, and modified during the execution process from the last planned task, a large batch of stock data can be filtered, and the data volume will be much less. Therefore, the incremental task is executed relatively fast. Therefore, this planned task is considered as the planned task started for daily use to ensure the iterative update of the data in the intermediate summary table.

[0098] In fact, according to the storage structure design of the intermediate summary table, adding, modifying, and deleting should all modify the business data in the intermediate summary table. However, in reality, adding, modifying, and deleting do not directly modify the intermediate summary table, but insert new summary data into the intermediate summary table. Different designs can be made according to the specific storage structure of the intermediate summary table. For example, in the asset business, since the status of historical assets needs to be retained, an asset may store multiple versions, and each version's life cycle is distinguished by the version number and date. These dates are in the form of a date zipper, and the start time and end time of each version do not overlap. To ensure the execution efficiency of incremental tasks, adding, modifying, and deleting all insert summary data here because the insertion, modification, and deletion of a simple business data may affect the summary results of many pieces of data, which is basically equivalent to the rearrangement of 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, the insertion method will also cause the data to show an increasing trend, so the full-volume task needs to be executed regularly to calibrate the data in the standard and merge the modified and deleted data to the specified time version of the specified dimension.

[0099] The above solutions for incremental tasks and full-volume tasks only ensure that the data in the intermediate summary table is not affected and that the business data generated in the business detail table every day can be synchronized to the intermediate summary table at the same time every day. However, it is not real-time synchronization after all. After the incremental task is executed and the user adds or modifies business data, when the user clicks the query on the front-end page before the next incremental task is executed, since the business data newly added by the user has not been synchronized to the intermediate summary table, the business data cannot be retrieved from the intermediate summary table. At this time, a data real-time 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 the flowchart of business data changes in the embodiment of the present invention. In one embodiment, the method further includes:

[0101] Step 401, after receiving a new request initiated by the user, add the business data in the new request to the business detail table, mark the status as new, and add a timestamp;

[0102] Step 402, after receiving an update request initiated by the user, modify the business data in the update request in the business detail 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 status of the business data in the deletion request as logically deleted in the business detail 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 detail table and insert the offset data of the business data corresponding to the deletion request into the intermediate summary table.

[0104] Step 103: After receiving a business data query request initiated by a 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 completion time of the most recent incremental task to the most recent query time, and the second time interval is from the farthest query time to the completion time of the most recent incremental task.

[0105] In one embodiment, querying the business data in the first time interval from the business detail table includes: querying the business data in the first time interval from the business detail table using a first thread.

[0106] Querying the summary data in the second time interval from the intermediate summary table and / or the business detail table includes: querying the summary data in the second time interval from the intermediate summary table and / or the business detail table using a second thread.

[0107] Aggregating the queried business data and the queried summary data and feeding back the aggregated data to the user includes: aggregating the queried business data and the queried summary data using the main thread and feeding back the aggregated data to the user.

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

[0109] Step 501, if the marked status of the summary data in the second time interval queried from the intermediate summary table is logically deleted, query the logically deleted business data in the business detail table, perform a reverse summary on the queried business data, and output the summary data after obtaining it.

[0110] Step 502, if the summary data in the second time interval queried from the intermediate summary table is offset data, or the marked status is new, or the marked status is updated, or the marked status is empty, directly output the queried summary data.

[0111] Figure 6 This is the flowchart of data aggregation in an embodiment of the present invention. In one embodiment, aggregating the queried business data and the queried summary data includes:

[0112] Step 601: After calculating the summary field values for the queried business data according to the preset data summarization rule, new summary data is obtained.

[0113] Step 602: Add the new summary data and the queried summary data corresponding to each other according to the summary fields to obtain aggregated data.

[0114] For example, a full - volume planned task was executed at 2 o'clock on November 30th. The user performed add, delete, and modify operations at 10 o'clock on November 30th, and an incremental task was automatically executed at 0 o'clock on December 1st, and add, delete, and modify operations were performed at 12 o'clock on December 1st. The user clicked the query function at 13 o'clock on the same day and initiated a business data query request.

[0115] First, determine that the first time interval is from December 1st to 13 o'clock on the same day, and the second time interval is from November 30th to December 1st.

[0116] Secondly, use the first thread to query the business data in the first time interval from the business detail table. This data includes the following three parts:

[0117] (1) Data added by the user from 0 o'clock on December 1st (the time of the last incremental task initiation) to 13 o'clock on the same day (the time when the user clicks the query);

[0118] (2) Data deleted by the user from 0 o'clock on December 1st to 13 o'clock on the same day;

[0119] (3) Data modified by the user from 0 o'clock on December 1st to 13 o'clock on the same day.

[0120] These three parts of data are all data summarized according to the specified fields, but these three parts of data will not be entered into the intermediate summary table. They are just business data queried in real - time from the specific business detail table and will be temporarily stored in memory. This part of the business data will be released after the query request ends.

[0121] Then, use the second thread to query the summary data in the second time interval from the intermediate summary table and / or the business detail table. If the marked status of the summary data in the second time interval queried from the intermediate summary table is logically deleted, all the logically deleted business data in the first time interval is retrieved from the business data table. Summarize and reverse this part of the business data according to the preset summarization rule and output it as the summary data. If the summary data in the second time interval queried from the intermediate summary table is offset data, or the marked status is new, or the marked status is updated, directly output the queried summary data, that is, the summary data formed by all the added, physically deleted, and modified business data in the second time interval. This part of the data is the summary of all data before the moment of the last execution of the planned task. This part of the summary data has the following two sources:

[0122] (1) Full - volume task: The aggregated data of all business data before 2:00 on November 30th.

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

[0124] Newly added data from 2:00 on November 30th to 0:00 on December 1st;

[0125] Deleted data from 2:00 on November 30th to 0:00 on December 1st;

[0126] Modified data from 2:00 on November 30th to 0:00 on December 1st.

[0127] The main thread waits for the two sub - threads to finish execution. According to the preset data aggregation rules, after calculating the aggregated field values for the queried business data, new aggregated data is obtained; according to the aggregated fields, the new aggregated data and the queried aggregated data are added correspondingly to obtain the aggregated data.

[0128] In the embodiments of the present invention, deletion includes physical deletion and logical deletion.

[0129] 1. Logical deletion

[0130] Logical deletion is widely used in the asset business function scenario. Almost all businesses involving deletion are logical deletions, aiming to retain the previous operation records and support business rollback operations. The specific approach is that when actually performing the deletion business operation, the business data in the deletion request is marked as logically deleted in the business detail 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 the data enumeration values are 1 and 2, where 1 represents logical deletion and 2 represents not logically deleted.

[0131] If the marked status of the aggregated data in the second time interval queried from the intermediate summary table is logically deleted, then all the logically deleted business data within the first time interval is retrieved from the business data table. The following will illustrate with a specific scenario.

[0132] Scenario: A user adds a piece of business data in the business detail table on November 30th. An incremental task is automatically executed on December 1st. At the same time, the user executes a deletion business at 12:00 noon on December 1st and queries the data at 13:00.

[0133] For the above scenario, the incremental task on December 1 will obviously include the business data newly added on November 30 and insert it into the intermediate summary table. When the user actually queries, he needs to query two parts of data at this time. Therefore, it is necessary to form the first time interval and the second time interval, obtain the business data of the first time interval in the business details table, and count the summary data according to the specified field, reverse the summary data (offset the new data counted in the summary table), and obtain the summary data of the intermediate summary table in the second time interval. Keep the two parts of the summary data in the memory at the same time, merge the two parts of the data with the same specified fields, and sum the data with the same dimension. The result is the summary data for the day.

[0134] 2. Physical deletion

[0135] Physical deletion Since it is impossible to detect from the specific business data table which data is deleted between the time when the user initiates the query request and the time when the most recent scheduled task is executed, it is necessary to fully interfere with the specific business, that is, to perform subsequent interference processing after the business data is deleted. The steps are as follows:

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

[0137] The listener obtains business data from the parameters and directly inserts a negative value of the summary field of the corresponding dimension into the intermediate summary table to offset the positive value of the historical status that has been counted in the exchange business details table.

[0138] If the above steps are not taken, the user will still query the results before the business data is deleted, and this deleted data cannot be traced back.

[0139] The present invention also provides a business data query device, as described in the following embodiments. Since the principle of solving the problem by the device is similar to that of the business data query method, the implementation of the device can refer to the implementation of the business data query method, and the repeated parts will not be repeated.

[0140] like Figure 7 The schematic diagram of the service data query device in an embodiment of the present invention includes:

[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 detail table according to the preset data summary rule and insert it into the intermediate summary table. The intermediate summary table includes multiple summary fields, and the summary fields are generated according to the preset data summary rule;

[0142] The incremental task execution module 702 is configured to execute the incremental task according to the execution time of the incremental task, and the incremental task is required to summarize the unaggregated business data in the business detail table and then insert it into the intermediate summary table;

[0143] The request decomposition module 703 is configured to parse the business data query request to obtain a first time interval and a second time interval after receiving the business data query request initiated by the user. 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 farthest query time to the completion time of the most recent incremental task;

[0144] The first query module 704 is configured to query the business data of the first time interval from the business detail table;

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

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

[0147] In one embodiment, the full-volume task execution module is configured to:

[0148] Empty the intermediate summary table;

[0149] Concurrently calculate the summary field values of the business data in the business detail table according to the preset data aggregation rules for the business data of each unit, obtain the summary data, and insert it into the intermediate summary table;

[0150] Mark the summary data inserted into the intermediate summary table with a batch number and record the time stamp corresponding to the batch number.

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

[0152] Extract the marked status change data from the business detail table from the completion time of the previous full-volume task or the completion time of the incremental task to the current time. The change data is new data, modified data, and deleted data;

[0153] Concurrently calculate the summary field values of the change data according to the preset data aggregation rules for the change data of each unit, obtain the summary data, and insert it into the intermediate summary table;

[0154] Mark the summary data inserted into the intermediate summary table with a batch number and record the time stamp corresponding to the batch number.

[0155] In one embodiment, the device further includes a business data change module, configured to:

[0156] After receiving a 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.

[0157] After receiving an 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.

[0158] After receiving a delete request initiated by the user, if the delete request is a logical delete, mark the status of the business data in the delete request as logically deleted in the business details table and add a timestamp; if the delete request is a physical delete, delete the business data corresponding to the delete request in the business details table and insert the offset data of the business data corresponding to the delete request into the intermediate summary table.

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

[0160] If the marked status of the summary data in the second time interval queried from the intermediate summary table is logically deleted, query the logically deleted business data in the business details table, perform a summary negation on the queried business data, and output the summary data after obtaining it.

[0161] If the summary data queried from the intermediate summary table is offset data, or the marked status is new, or the marked status is updated, or the marked status is empty, directly output the queried summary data.

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

[0163] According to the preset data summary rule, calculate the summary field value for the queried business data to obtain new summary data.

[0164] According to the summary field, add the new summary data and the queried summary data correspondingly to obtain aggregated data.

[0165] In one embodiment, the first query module is used for: querying the business data in the first time interval from the business details table using the first thread.

[0166] The second query module is used for: querying the summary data in the second time interval from the intermediate summary table and / or the business details table using the second thread.

[0167] The data aggregation module is used for: aggregating the queried business data and the queried summary data using the main thread, and feeding back the aggregated data to the user.

[0168] An embodiment of the present invention further provides a computer program product, where the computer program product includes a computer program, and when the computer program is executed by a processor, the above-mentioned business data query method is implemented.

[0169] An embodiment of the present invention further provides a computer-readable storage medium storing a computer program, which, when executed by a processor, implements the above-mentioned business data query method.

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

[0171] In an embodiment of the present invention, a full-volume task is executed. The full-volume task needs to summarize the business data in the business detail table according to a preset data summarization rule and then insert it into an intermediate summary table. The intermediate summary table includes multiple summary fields, and the summary fields are generated according to the preset data summarization rule. At the execution time of the incremental task, the incremental task is executed. The incremental task needs to summarize the un-summarized business data in the business detail table and then 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 completion time of the most recent incremental task to the most recent query time, and the second time interval is from the farthest query time to the completion time of the most recent incremental task. The business data in the first time interval is queried from the business detail table. The summary data in the second time interval is queried from the intermediate summary table and / or the business detail table. The queried business data and the queried summary data are aggregated, and the aggregated data is fed back to the user. Through the above steps, the full-volume task and the incremental task insert the business data into the intermediate summary table, so that when the user queries, only the huge business detail table needs to be queried for the query requirement of the first time interval. For the query requirement of the second time interval, the summary data can be directly queried from the intermediate summary table summarized according to the preset data summarization rule, which greatly improves the efficiency of business data query.

[0172] Those skilled in the art should understand that the embodiments of the present invention can be provided as a method, a system, or a computer program product. Therefore, the present invention can adopt the form of a complete hardware embodiment, a complete software embodiment, or an embodiment combining software and hardware aspects. Moreover, the present invention can adopt the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk memories, CD-ROMs, optical memories, etc.) containing computer-usable program codes.

[0173] The present invention is described with reference to the flowcharts and / or block diagrams of methods, apparatuses (systems), and computer program products according to embodiments of the present invention. It should be understood that each flow and / or block in the flowchart and / or block diagram, and the combination of flows and / or blocks in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to the processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing devices to generate a machine, such that the instructions executed by the processor of the computer or other programmable data processing devices generate means for implementing the functions specified in one flow Figure 1 one flow or multiple flows and / or blocks Figure 1 or means for implementing the functions specified in one block or multiple blocks.

[0174] These computer program instructions can also be stored in a computer-readable memory that can direct a computer or other programmable data processing device to work in a specific manner, such that the instructions stored in the computer-readable memory generate a manufactured article including instruction means that implement the functions specified in one flow Figure 1 one flow or multiple flows and / or blocks Figure 1 or means for implementing the functions specified in one block or multiple blocks.

[0175] These computer program instructions can also be loaded onto a computer or other programmable data processing device, such that a series of operation steps are executed on the computer or other programmable device to generate a computer-implemented process, and thus the instructions executed on the computer or other programmable device provide steps for implementing the functions specified in one flow Figure 1 one flow or multiple flows and / or blocks Figure 1 or means for implementing the functions specified in one block or multiple blocks.

[0176] The specific embodiments described above further elaborate on the objectives, technical solutions, and beneficial effects of the present invention. It should be understood that the above are only specific embodiments of the present invention and are not used to limit the protection scope of the present invention. Any modifications, equivalent replacements, improvements, etc. made within the spirit and principles of the present invention shall be included in the protection scope of the present invention.

Claims

1. A method for querying service data, characterized in that, Including: Execute a full - volume task. The full - volume task needs to summarize the business data in the business detail table according to a preset data summarization rule and insert it into the intermediate summary table. The intermediate summary table includes multiple summary fields, and the summary fields are generated according to the preset data summarization rule; Execute the incremental task according to the execution time of the incremental task. The incremental task needs to summarize the un - summarized business data in the business detail table and insert it into the intermediate summary table; After receiving a business data query request initiated by a 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 completion time of the most recent incremental task to the most recent query time, and the second time interval is from the farthest query time to the completion time of the most recent incremental task; Query the business data in the first time interval from the business detail table; Query the summary data in the second time interval from the intermediate summary table and / or the business detail table; Aggregate the queried business data and the queried summary data, and feedback the aggregated data to the user.

2. The service data query method according to claim 1, characterized in that, Executing the full - volume task includes: Empty the intermediate summary table; Concurrently, for the business data of each unit, calculate the summary field values for the business data in the business detail table according to the preset data summarization rule to obtain summary data, and insert it into the intermediate summary table; Mark the batch number for the summary data inserted into the intermediate summary table and record the time stamp corresponding to the batch number.

3. The service data query method according to claim 1, wherein, Executing the incremental task according to the execution time of the incremental task includes: Extract the marked - status changed data (the changed data includes new data, modified data, and deleted data) from the business detail table from the completion time of the previous full - volume task or the completion time of the incremental task to the current time; Concurrently, for the changed data of each unit, calculate the summary field values for the changed data according to the preset data summarization rule to obtain summary data, and insert it into the intermediate summary table; Mark the batch number for the summary data inserted into the intermediate summary table and record the time stamp corresponding to the batch number.

4. The service data query method according to claim 1, wherein, It also includes: After receiving a new request initiated by a user, add the business data in the new request to the business detail table, mark the status as new, and add a time stamp; After receiving an update request initiated by a user, modify the business data in the update request in the business detail table, mark the status as updated, and add a time stamp; After receiving a delete request initiated by a user, if the delete request is a logical deletion, mark the status of the business data in the delete request as logically deleted in the business detail table and add a time stamp; If the delete request is a physical deletion, delete the business data corresponding to the delete request in the business detail table and insert the offset data of the business data corresponding to the delete request into the intermediate summary table.

5. The service data query method according to claim 1, wherein Querying the summary data in the second time interval from the intermediate summary table and / or the business detail table includes: If the marked status of the summary data in the second time interval queried from the intermediate summary table is logically deleted, query the logically deleted business data in the business detail table, summarize and take the opposite of the queried business data to obtain the summary data and then output it; If the summary data for the second time interval queried in the intermediate summary table is write-off data, or the marked status is new, or the marked status is updated, or the marked status is empty, directly output the queried summary data.

6. The service data query method according to claim 1, characterized in that, Aggregate the queried business data and the queried summary data, including: After calculating the summary field values for the queried business data according to the preset data summarization rules, obtain new summary data; Add the new summary data and the queried summary data corresponding to each other according to the summary fields to obtain aggregated data.

7. The service data query method according to claim 1, characterized in that, Query the business data for the first time interval from the business detail table, including: query the business data for the first time interval from the business detail table using the first thread; Query the summary data for the second time interval from the intermediate summary table and / or the business detail table, including: query the summary data for the second time interval from the intermediate summary table and / or the business detail table using the second thread; Aggregate the queried business data and the queried summary data, and feedback the aggregated data to the user, including: aggregate the queried business data and the queried summary data using the main thread and feedback the aggregated data to the user.

8. A service data query device, characterized in that, Including: A full-volume task execution module for executing a full-volume task, where the full-volume task needs to summarize the business data in the business detail table according to the preset data summarization rules and insert it into the intermediate summary table. The intermediate summary table includes multiple summary fields, and the summary fields are generated according to the preset data summarization rules; An incremental task execution module for executing the incremental task according to the execution time of the incremental task. The incremental task needs to summarize the un-summarized business data in the business detail table and insert it into the intermediate summary table; A request decomposition module for parsing the business data query request to obtain a first time interval and a second time interval after receiving the business data query request initiated by the user. 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 farthest query time to the completion time of the most recent incremental task; A first query module for querying the business data for the first time interval from the business detail table; A second query module for querying the summary data for the second time interval from the intermediate summary table and / or the business detail table; A data aggregation module for aggregating the queried business data and the queried summary data and feedbacking the aggregated data to the user.

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

10. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores a computer program, and when the computer program is executed by the processor, it implements the method according to any one of claims 1 to 7.

11. A computer program product, characterized in that, The computer program product includes a computer program, and when the computer program is executed by the processor, it implements the method according to any one of claims 1 to 7.

Images