Method and apparatus for processing intermittent data, and electronic device

Abstract

The application relates to a method and device for processing intermittent data and an electronic device, wherein the method comprises the following steps: extracting the latest valid record of each classification in each time unit within a preset time range from an original data table to obtain a valid record set, wherein the original data table is used for recording intermittent data generated by a storage business system and arranged in time sequence, and each classification refers to the minimum dimension combination of the determined state attribution in the intermittent data; obtaining the latest historical state record of each classification before the starting time of the preset time range from the original data table to obtain a boundary state record set; iteratively processing each unit time within the preset time range in time sequence, updating the state value of the corresponding classification in the boundary state record set based on the valid record set, and performing aggregate calculation on the updated state value of the classification to obtain an aggregate result of the current time; and outputting the aggregate result in a target format.

Description

Technical Field

[0001] This application relates to the field of data processing technology, and in particular to a method, apparatus, and electronic device for processing intermittent data. Background Technology

[0002] Existing Structured Query Language (SQL) aggregation methods, when processing intermittent data, implicitly discard the historical state of data for days with no data available. This leads to a discrepancy between the aggregation results and the actual business requirements (presenting the latest state of each category each day). Current technologies employ data completion methods, which use scheduled tasks to forcibly initialize the latest records of each category with virtual records for that day to fill in data gaps. However, this method writes a large number of redundant virtual records, causing the data volume to grow linearly and severely consuming storage resources. Summary of the Invention

[0003] This application provides a method, apparatus, and electronic device for processing intermittent data, in order to solve the problem in the prior art where the latest records of each category are forcibly initialized to the virtual records of the day to fill data gaps through timed tasks, resulting in the need to write a large number of redundant virtual records and causing serious consumption of storage resources.

[0004] In a first aspect, this application provides a method for processing intermittent data, comprising: extracting the latest valid record for each category within a preset time range from an original data table to obtain a valid record set, wherein the original data table is used to record and store intermittent data generated by a business system and arranged in chronological order, and each category refers to the smallest dimensional combination in the intermittent data that determines the state affiliation; obtaining the latest historical state record for each category before the start time of the preset time range from the original data table to obtain a boundary state record set; iterating through each unit time within the preset time range in chronological order to update the state values ​​of the corresponding categories in the boundary state record set based on the valid record set, and performing aggregation calculations on the updated category state values ​​to obtain the aggregation result for the current time; and outputting the aggregation result in a target format.

[0005] Optionally, extract the latest valid record for each time unit within a preset time range for each category from the original data table to obtain the corresponding valid record set, including: grouping the intermittent data within the preset time range in the original data table according to the combination of category dimensions and time order; extracting the latest valid record for each category dimension combination from the grouping, and constructing the valid record set based on the latest valid record of all categories.

[0006] Optionally, obtaining the latest historical state record for each category before the start time of the preset time range from the original data table to obtain the corresponding boundary state record set includes: grouping the intermittent data within a preset duration before the start time of the preset time range in the original data table according to the dimensional combination of the category and the time order; if there is a historical record corresponding to the dimensional combination of the target category in the group, extracting the latest historical record from the historical record; wherein, the target category is any category of all categories in the valid record set; if there is no historical record corresponding to the dimensional combination of the target category in the group, determining the default value of the target category as the latest historical record of the target category; and constructing the boundary state record set based on the latest historical records of all the target categories.

[0007] Optionally, iterating through each unit of time within the preset time range in chronological order to update the state values ​​of the corresponding categories in the boundary state record set based on the valid record set includes: constructing a state container in memory and loading the records in the boundary state record set into the state container as the initial state; wherein the state container is a single key-value pair mapping structure, and the number of keys is equal to the number of all categories within the preset time range; iterating through each unit of time within the time range in chronological order, if there is an update record for any category in the valid record set at the current time, then the update record is used to update the state value of the corresponding category in the state container; otherwise, the state value of the corresponding category remains unchanged.

[0008] Optionally, the aggregation calculation of the updated classification state values ​​to obtain the aggregation result at the current time includes: performing aggregation calculation for each unit of time during the time-sequential iteration process until the aggregation calculation of the last unit of time in the preset time range is completed, so as to obtain the aggregation result.

[0009] Optionally, performing aggregation calculations for each unit of time includes: maintaining a global aggregation variable, wherein the initial value of the global aggregation variable is equal to the aggregation result of all category state values ​​in the boundary state record set; within the current unit of time, for each category in the valid record set with updated records, calculating the change in its state value relative to the original state value in the state container, and accumulating the change in the global aggregation variable; determining the global aggregation variable after accumulation as the aggregation result for the current unit of time, and updating the state value of the corresponding category in the state container.

[0010] Optionally, outputting the aggregation result in a target format includes: converting the aggregation result into a structured data format, wherein the structured data format is a key-value pair sequence or JSON format; and sending the structured data format to the front end to drive the visualization chart component to display it in the form of a time series chart.

[0011] Secondly, this application provides an apparatus for processing intermittent data, comprising: a first processing module, configured to extract the latest valid record for each category within a preset time range from an original data table to obtain a valid record set, wherein the original data table is used to record and store intermittent data generated by a business system and arranged in chronological order, and each category refers to the smallest dimensional combination in the intermittent data that determines the state affiliation; a second processing module, configured to obtain the latest historical state record for each category before the start time of the preset time range from the original data table to obtain a boundary state record set; and a third processing module, configured to iterate through each unit time within the preset time range in chronological order, update the state value of the corresponding category in the boundary state record set based on the valid record set, and perform aggregation calculation on the updated state value of the category to obtain the aggregation result for the current time. The fourth processing module is used to output the aggregation result in the target format.

[0012] Thirdly, this application provides an electronic device, comprising: at least one communication interface; at least one bus connected to the at least one communication interface; at least one processor connected to the at least one bus; and at least one memory connected to the at least one bus, wherein the processor is configured to perform the method for processing intermittent data as described in the first aspect of this application.

[0013] Fourthly, this application also provides a computer storage medium storing computer-executable instructions for performing the method for processing intermittent data described in the first aspect of this application.

[0014] Compared with the prior art, the technical solution provided in this application has the following advantages: The method provided in this application first extracts the latest valid record of each category within a preset time range from the original data table to obtain a valid record set. Then, it obtains the latest historical state record of each category before the start time of the preset time range from the original data table to obtain a boundary state record set. Next, it iterates through each unit of time within the preset time range in chronological order to update the state values ​​of the corresponding categories in the boundary state record set based on the valid record set. Finally, it aggregates the updated state values ​​of the categories to obtain the aggregation result for the current time. Finally, it outputs the aggregation result in the target format. It can be seen that, through the method of this application, the storage overhead only comes from the updated data in the original data table, greatly reducing the data expansion rate. In addition, the aggregation result is calculated instantly, without waiting for scheduled tasks to execute. The latest result can be obtained at any time, and the output result is completely based on the original real records, without mixing in any artificially constructed virtual data, ensuring the traceability and reliability of the statistics. Attached Figure Description

[0015] The accompanying drawings, which are incorporated in and form part of this specification, illustrate embodiments consistent with the invention and, together with the description, serve to explain the principles of the invention.

[0016] 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, for those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0017] One or more embodiments are illustrated by way of example with reference numerals in the accompanying drawings. These illustrations do not constitute a limitation on the embodiments. Elements with the same reference numerals in the drawings are denoted as similar elements. Unless otherwise stated, the figures in the drawings are not to be limited by scale.

[0018] Figure 1 A flowchart illustrating a method for processing intermittent data, as provided in this application embodiment; Figure 2 A flowchart illustrating a method for summarizing, statistically analyzing, and performing analysis on intermittent data, provided in this application embodiment; Figure 3 A schematic diagram of an apparatus for processing intermittent data is provided in an embodiment of this application; Figure 4 This is a schematic diagram of the structure of an electronic device provided in an embodiment of this application. Detailed Implementation

[0019] To make the objectives, technical solutions, and advantages of the embodiments of this application clearer, the technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, not all embodiments. Based on the embodiments of this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.

[0020] The following disclosure provides numerous different embodiments or examples for implementing various structures of the invention. To simplify the disclosure, specific examples of components and arrangements are described below. These are merely examples and are not intended to limit the scope of the invention. Furthermore, reference numerals and / or letters may be repeated in different examples. Such repetition is for simplification and clarity and does not in itself indicate a relationship between the various embodiments and / or arrangements discussed.

[0021] To address the problem in existing technologies where scheduled tasks force the latest records of each category to be initialized with the current day's virtual records to fill data gaps, resulting in the writing of a large number of redundant virtual records and severe consumption of storage resources, this application provides a method for processing intermittent data, such as... Figure 1 As shown, the steps of this method include: Step 101: Extract the latest valid record for each time unit within a preset time range for each category from the original data table to obtain a set of valid records. The original data table is used to record and store intermittent data generated by the business system and arranged in chronological order. Each category refers to the smallest combination of dimensions that determines the state attribution in the intermittent data. In this embodiment, intermittent data refers to data records generated by the business system, stored chronologically, but not continuously on the timeline, and appearing non-periodically. For example, records exist within certain time units (e.g., days), while there are no records in other time units, resulting in uneven record intervals. The original data table refers to the database table used for persistently storing the intermittent data, with each record corresponding to one real business event.

[0022] The classification in this application embodiment is used to determine the minimum combination of dimensions for state attribution. For example, in the automotive industry scenario, the classification is a combination of project and model, with each combination uniquely identifying an independent state tracking object. The granularity of the classification is determined by business requirements, and all attributes of the combination must be present; otherwise, the state attribution cannot be uniquely determined.

[0023] Furthermore, the preset time range refers to the continuous time interval for analysis and statistics, defined by the start and end times, encompassing every time unit between the start and end dates. The basic time granularity for summarizing and statistically analyzing time units is, for example, days, hours, or weeks, specifically set according to the business scenario. Further, a valid record refers to a single actual data record generated within a specific time unit for each category within the preset time range. If multiple records are generated for the same category within the same time unit, the record with the latest timestamp is taken as the valid record for that time unit. Therefore, the set of valid records is the collection of the latest valid records for each time unit across all categories within the preset time range.

[0024] As can be seen, in this embodiment of the application, the original multi-record data is compressed into at most one record per category per time unit, which reduces the amount of data for subsequent iteration processing, while retaining the latest state within each time unit, thus ensuring the accuracy of the statistical results.

[0025] Step 102: Obtain the latest historical state record for each category before the start time within the preset time range from the original data table to obtain the boundary state record set; In this embodiment, the boundary state record is the latest historical state record of each category before the start time of a preset time range. Therefore, the boundary state record set refers to the set consisting of the latest historical state records of all categories before the start time.

[0026] To address this, each category may already have historical states prior to the start date of the preset time range. To ensure that the initial state of a category is available on the first day of the preset time range, it is necessary to backtrack and query the latest record for each category before the start date. For example, if the preset time range starts on May 1, 2025, then the last record for each category up to April 30, 2025, needs to be queried. Categories that have never had a record can be assigned a default value (such as 0). All boundary state records constitute a boundary state record set, used for initializing the state. This method fills the state gaps at the start of the statistical window, ensuring that the state container has a complete historical baseline for each category from day one, avoiding the problem of missing initial states due to no records on the start date.

[0027] Step 103: Iterate through each unit of time within the preset time range in chronological order to update the state values ​​of the corresponding categories in the boundary state record set based on the effective record set, and perform aggregation calculation on the updated category state values ​​to obtain the aggregation result of the current time. In this embodiment, the status value refers to the current business value of each category, such as the number of warnings, the latest version number, etc., which may be updated over time. Furthermore, aggregation calculation refers to a mathematical operation performed on the status values ​​of all current categories, such as summation, averaging, finding the maximum value, finding the minimum value, or counting, to obtain a comprehensive statistical result for that time unit. The specific aggregation method can be selected based on the specific application scenario.

[0028] As can be seen, continuous processing of intermittent data is achieved through the rolling merging of in-memory state containers. Even if there is no real data for a certain time unit, aggregated results can still be output based on historical states, solving the gap problem caused by missing data in traditional methods. At the same time, the entire iterative process only needs to maintain the updated data, avoiding data bloat.

[0029] Step 104: Output the aggregation results in the target format.

[0030] In the embodiments of this application, the target format refers to a standardized data exchange format used to output the aggregation results, such as JSON format or key-value pair sequence, which facilitates subsequent processing or visualization.

[0031] Through steps 101 to 104 above, the latest valid record for each category within a preset time range is first extracted from the original data table to obtain a valid record set. Then, the latest historical state record for each category before the start time of the preset time range is obtained from the original data table to obtain a boundary state record set. Subsequently, each unit time within the preset time range is iterated in chronological order to update the state values ​​of the corresponding categories in the boundary state record set based on the valid record set. The updated state values ​​of the categories are then aggregated to obtain the aggregation result for the current time. Finally, the aggregation result is output in the target format. It is evident that, through the method described in this application, the storage overhead only comes from the updated data in the original data table, greatly reducing the data bloat rate. In addition, the aggregation result is calculated instantly, without waiting for scheduled tasks to execute; the latest result can be obtained at any time. Furthermore, the output result is entirely based on the original real records, without any artificially constructed virtual data, ensuring the traceability and reliability of the statistics.

[0032] In an optional embodiment of this application, the method of extracting the latest valid record for each category within a preset time range from the original data table to obtain the corresponding valid record set in step 101 above may further include: Step 11: Group the intermittent data within the preset time range in the original data table according to the combination of classification dimensions and time order; Step 12: Extract the latest valid record for each category's dimension combination from the grouping, and construct a valid record set based on the latest valid record for all categories.

[0033] For steps 11 and 12 above, in a specific example, taking the automotive industry scenario as an example, the original data table contains the fields: Project, Model, Inspection Time, and Warning Count, and the preset time range is from 2025-05-01 to 2025-05-03. The corresponding original data table is shown in Table 1: Table 1

[0034] The contents of Table 1 above are grouped according to the combination of classification dimensions and time order, resulting in the following groupings: Group 1: (Project A, Model X, 2025-05-01) → Contains two records at 08:00 and 14:00; Group 2: (Project A, Model Y, 2025-05-01) → Contains one record at 09:00; Group 3: (Project A, Model X, 2025-05-03) → Contains one record at 10:00; Other combinations (such as any model from 2025-05-02) have no records and are not grouped.

[0035] Extracting the latest record from the above grouping results yields the following: Group 1: Sort by time in descending order, record the latest record at 14:00 → Number of warnings extracted = 5; Group 2: Unique record → Number of warnings extracted = 2; Group 3: Unique record → Number of warnings extracted = 6.

[0036] Based on this, the following valid record set can be obtained: Date: 2025-05-01, Category: (Project A, Model X), Value: 5; Date: 2025-05-01, Category: (Project A, Model Y), Value: 2; Date: 2025-05-03, Category: (Project A, Model X), Value: 6; It should be noted that there are no records for 2025-05-02, and this date is not included in the set of valid records.

[0037] As can be seen from steps 11 and 12 above, compressing multiple possible records for each category within each time unit into a single latest record greatly reduces the amount of data processed in subsequent iterations. Moreover, the extracted latest record represents the true state of the category at the end of that time unit, ensuring the timeliness and accuracy of the statistical results.

[0038] In an optional embodiment of this application, the method of obtaining the latest historical state record of each category before the start time of a preset time range from the original data table in step 102 above, to obtain the corresponding boundary state record set, may further include: Step 21: Group the intermittent data within a preset duration before the start time of the preset time range in the original data table according to the combination of classification dimensions and time order; Step 22: If there is a historical record in the group that corresponds to the dimension combination of the target category, extract the latest historical record from the historical record; where the target category is any category of all categories in the valid record set. Step 23: If there is no historical record in the group that corresponds to the dimension combination of the target category, the default value of the target category is determined to be the latest historical record of the target category. As can be seen, if a target category has no historical records within a preset time period (i.e., the category has never appeared before the start time, or its last appearance was before the preset time period), the true historical state cannot be obtained from the original data table. In this case, the system assigns a preset default value (e.g., 0) to the category as its boundary state record. In other words, by limiting the historical query range through the preset time period, it ensures that reasonable historical states can be obtained (most categories have records within the preset time period) while avoiding the performance loss caused by querying excessively old historical data. The preset time period can be flexibly configured according to business characteristics (e.g., 30 days, 90 days).

[0039] Step 24: Construct a boundary state record set based on the latest historical record for all target classifications.

[0040] For steps 21 to 24 above, in this specific example, we still take the automotive company scenario, with a preset time range of 2025-05-01 to 2025-05-07, starting at 2025-05-01 00:00:00. The preset duration is 30 days (i.e., querying data from 2025-04-01 to 2025-04-30). The specific valid record set is as follows: Category C1: (Project A, Model X); Category C2: (Project A, Model Y); Category C3: (Item B, Model Z); Based on this, query the records in the original data table from April 1, 2025 to April 30, 2025, and sort them by project and model in descending order of time. The query results should look like this: Group (A, X): Contains records (2025-04-28, warning count = 4) and (2025-04-20, warning count = 3); Group (A, Y): Contains the record (2025-04-15, Warning Count = 1); Group (B, Z): No records (never appeared); For target category C1(A, X), there exists a grouping → extract the latest record (2025-04-28, warning count = 4).

[0041] For target category C2(A, Y), there exists a group → extract the latest record (2025-04-15, warning count = 1).

[0042] For the target category C3(B,Z), if no group exists, assign a default value, such as 0.

[0043] Thus, the following boundary state record set can be constructed: (A, X): 4, (A, Y): 1, (B, Z): 0.

[0044] Through steps 21 to 24 above, the historical boundary states of categories appearing only in the valid record set are queried, avoiding meaningless historical queries for all categories (which may be far more than the actual active categories). This is especially suitable for scenarios with a huge number of categories but few active categories. Moreover, for categories with missing history (such as newly added categories), default values ​​are assigned to ensure that each target category has a definite state value after the state container is initialized, avoiding aggregation errors caused by null values.

[0045] In an optional embodiment of this application, the method of iterating each unit time within a preset time range in chronological order in step 103 above to update the state values ​​of the corresponding categories in the boundary state record set based on the valid record set may further include: Step 31: Construct a state container in memory and load the records from the boundary state record set into the state container as the initial state; wherein, the state container is a single key-value pair mapping structure, and the number of keys is equal to the number of all categories within the preset time range; In this embodiment, the state container refers to a data structure maintained in memory for storing the latest state value of each category. Furthermore, the single key-value pair mapping structure refers to a dictionary (or hash map) where the key is the category identifier and the value is the latest state value of that category. Additionally, in this embodiment, the number of keys stored in the state container is equal to the total number of categories involved within the preset time range. Here, "total categories" refers to categories that have at least one actual record in all time units within the preset time range, plus the union of categories contained in the boundary state record set.

[0046] Step 32: Iterate through each unit of time within the preset time range in chronological order. If there is an update record for any category in the valid record set at the current time, use the update record to update the state value of the corresponding category in the state container; otherwise, keep the state value of the corresponding category unchanged.

[0047] As shown in step 32 above, starting from the beginning of the preset time range, each time unit (e.g., each day) is processed sequentially in ascending order of time. For the currently processed time unit t, it checks whether a record entry corresponding to that time unit exists in the valid record set. If it exists, it iterates through all category update records under that time unit (each record contains a category identifier and a new state value). For each such category, it finds the key corresponding to that category in the state container and updates its value to the new state value. If it does not exist (i.e., there are no update records for any category in that time unit), then all values ​​in the state container remain unchanged. It should be noted that "otherwise, keep the state value of the corresponding category unchanged" means that even if a category has no real data on that day, its state value will be automatically carried over to that day, thus ensuring the continuity of the aggregation results on the time axis.

[0048] In a specific example, taking a car manufacturer scenario as an example, steps 31 and 32 above are set within a preset time range of 2025-05-01 to 2025-05-07 (7 days in total). The boundary state record set is: (Project A, Model X) = 4, (Project A, Model Y) = 1, (Project B, Model Z) = 0. The valid record set is as follows: 2025-05-01: (A, X) = 5; 2025-05-02: (A, Y) = 2; 2025-05-03: (B, Z) = 3.

[0049] Therefore, after constructing the state container, the state container is initialized based on the boundary state record set, resulting in S = {(A, X): 4; (A, Y): 1; (B, Z): 0}.

[0050] Iterate daily: t=2025-05-01: The set of valid records has (A, X)=5 → Update S[(A, X)]=5 → S becomes {(A, X):5, (A, Y):1, (B, Z):0}; t=2025-05-02: The set of valid records has (A, Y)=2 → Update S[(A, Y)]=2 → S becomes {(A, X):5, (A, Y):2, (B, Z):0}; t=2025-05-03: The set of valid records has (B, Z)=3 → Update S[(B, Z)]=3 → S becomes {(A, X):5, (A, Y):2, (B, Z):3}; t=2025-05-04: No records in the valid record set → Keep S unchanged → {(A,X):5,(A,Y):2,(B,Z):3}; t=2025-05-05 ~ 2025-05-07: No records found → Keep the state unchanged.

[0051] As can be seen, in this embodiment, the size of the state container is only proportional to the number of categories and is independent of the statistical time. Compared with the storage expansion caused by writing virtual records for each category every day in the prior art, the method of this embodiment greatly saves storage space.

[0052] In an optional embodiment of this application, the method of aggregating the updated classification state values ​​to obtain the aggregation result at the current time in step 103 may further include: Step 41: During the time-sequential iteration process, perform aggregation calculations for each unit of time until the aggregation calculation for the last unit of time in the preset time range is completed, so as to obtain the aggregation result.

[0053] As can be seen, in this embodiment, each time unit (e.g., each day) is processed sequentially from the start time to the end time of a preset time range. Each time unit undergoes an independent aggregation calculation, not just those time units with data updates. During each aggregation, the aggregation calculation is performed, the current state value of each category is read, and then the aggregation operation (e.g., summation) is performed according to business requirements.

[0054] Furthermore, the method of performing aggregate calculations for each unit of time in step 41 above may further include: Step 51: Maintain a global aggregation variable, where the initial value of the global aggregation variable is equal to the aggregation result of all classification state values ​​in the boundary state record set; As can be seen, in this embodiment, an initial aggregate value is calculated before starting the time iteration. Specifically, all categories in the boundary state record set (i.e., the initial content of the state container) are traversed, and an initial aggregate result is calculated according to the required aggregation operation (such as summation), and stored in a separate variable, namely the global aggregate variable. For example, in a summation scenario, if the boundary state record set contains values ​​for three categories: 4, 1, and 0, then the global aggregate variable is initialized to 4 + 1 + 0 = 5.

[0055] Step 52: Within the current unit of time, for each category in the valid record set that has an updated record, calculate the change in its state value relative to the original state value in the state container, and accumulate the change to the global aggregate variable. Step 53: Determine the global aggregation variable after accumulation as the aggregation result for the current unit of time, and update the state value of the corresponding category in the state container.

[0056] For steps 51 to 53 above, in a specific example, the boundary state record set is: category A = 4, category B = 1, category C = 0 → initial global aggregation variable = 5.

[0057] Valid record set: Day 1: Category A is updated to 5; Day 2: Category B is updated to 2; Day 3: Category C is updated to 3; Day 4 and Day 5: No updates.

[0058] The iterative process is shown in Table 2.

[0059] Table 2

[0060] As can be seen, in this embodiment of the application, by introducing a global aggregation variable and a change accumulation mechanism, the computational complexity is reduced from being related to the total number of categories M to being related to the actual number of updated categories K, thereby significantly improving the query performance in large-scale sparse update scenarios and realizing real-time aggregation analysis of massive classification data.

[0061] In an optional embodiment of this application, the method of outputting the aggregation result in the target format involved in step 104 above may further include: Step 61: Convert the aggregation result into a structured data format, wherein the structured data format is a sequence of key-value pairs or JSON format; Step 62: Send the structured data format to the front end to drive the visualization chart component to display it in the form of a time series chart.

[0062] As can be seen, this embodiment uses a universal data format (JSON), eliminating the need to customize output interfaces for different front-end platforms and reducing the complexity of system integration. Furthermore, structured data can be directly parsed into objects or dictionaries in programming languages, facilitating efficient processing and plotting by the front-end.

[0063] The present application will be explained and described below with reference to specific embodiments, which provide a method for summarizing, statistically analyzing, and processing intermittent data, such as... Figure 2 As shown, the steps of this method include:

[0064] Step 201, Data Extraction; The extraction process employs SQL grouping queries combined with time boundary conditions to retrieve valid records from the original data. Specifically, for the original data table, it is grouped by category dimension (e.g., project + model in the example scenario) and date. ROW_NUMBER() OVER (PARTITION BY category, DATE(time) ORDER BY timeDESC) is used to extract the latest record for each category per day. If each category generates at most one record per day in the business scenario, this can be simplified to a simple GROUP BY.

[0065] Step 202, boundary completion; The boundary completion process uses SQL window functions and time boundary conditions to retrieve the historical state before the start of the statistical window. Specifically, it queries the latest record for each category before the start time of the preset time range. The purpose of this step is to fill in the missing state at the beginning boundary of the time window, ensuring the accuracy of the initial state for subsequent rolling merges. If the historical data volume is large, backtracking limits can be set (e.g., only tracing back the most recent N days), or default initial values ​​can be used for older historical data to control query overhead.

[0066] Step 203, State aggregation; Specifically, a state container is maintained in memory, and the data for each day is merged into the container in chronological order. The contents of the container are then aggregated and output on the same day.

[0067] The specific process is as follows: Based on the following inputs: D: preset time range [start_date, end_date]; R_daily: daily actual records (grouped by date); R_boundary: historical boundary records (the latest state of each category before start_date). The following output can be obtained: the summary value for each date after merging the latest states of all categories. If each date is further grouped by item, an additional dimension is needed; this example only illustrates grouping by date.

[0068] Further, execute the following process: Initialize the state container S = {}, a dictionary structure, where the keys are category identifiers and the values ​​are the latest record values. Load all records in R_boundary into S as the initial state. Iterate through each day t in D in date order. If t has a record in R_daily for a certain category, update S[category] = R_daily[t][category]; otherwise, keep S[category] = S[category] (using the historical state); aggregate the values ​​of all categories in S (e.g., sum, average, etc.) to obtain the final result for that date; output the aggregated result.

[0069] Step 204, output the result.

[0070] Using steps 201 to 204 above, queries are performed based on raw data without additional storage. Furthermore, queries can be performed in real-time (calculation occurs on the spot), without waiting for scheduled tasks to execute; results are displayed correctly at any time. Moreover, it is entirely based on raw data, ensuring no contamination. However, existing technologies often use business tables that mix real and virtual records, requiring careful identification and differentiation.

[0071] Corresponding to the above Figure 1 This application also provides an apparatus for processing intermittent data, such as... Figure 3 As shown, the device includes: The first processing module 302 is used to extract the latest valid record of each category in each time unit within a preset time range from the original data table to obtain a set of valid records. The original data table is used to record and store intermittent data generated by the business system and arranged in chronological order. Each category refers to the smallest combination of dimensions that determines the state attribution in the intermittent data. The second processing module 304 is used to obtain the latest historical state record of each category before the start time of the preset time range from the original data table, and obtain the boundary state record set. The third processing module 306 is used to iterate each unit time within a preset time range in chronological order, update the state values ​​of the corresponding categories in the boundary state record set based on the effective record set, and perform aggregation calculation on the updated category state values ​​to obtain the aggregation result of the current time. The fourth processing module 308 is used to output the aggregation results in the target format.

[0072] In an optional embodiment of this application, the first processing module 302 in this application embodiment may further include: a first processing unit, used to group intermittent data within a preset time range in the original data table according to the combination of dimensional categories and time order; and a second processing unit, used to extract the latest valid record of each category's dimensional combination from the grouping, and construct a valid record set based on the latest valid record of all categories.

[0073] In an optional embodiment of this application, the second processing unit 304 includes: a third processing unit, configured to group intermittent data within a preset duration before the start time of a preset time range in the original data table according to the combination of classification dimensions and time order; a fourth processing unit, configured to extract the latest historical record from the historical records if there are historical records in the group that correspond to the combination of dimensions of the target classification; wherein the target classification is any classification of all classifications in the valid record set; a fifth processing unit, configured to determine the default value of the target classification as the latest historical record of the target classification if there are no historical records in the group that correspond to the combination of dimensions of the target classification; and a sixth processing unit, configured to construct a boundary state record set based on the latest historical records of all target classifications.

[0074] In an optional embodiment of this application, the third processing module in this application includes: a seventh processing unit, used to construct a state container in memory and load the records in the boundary state record set into the state container as the initial state; wherein, the state container is a single key-value pair mapping structure, and the number of keys is equal to the number of all categories within a preset time range; an eighth processing unit, used to iterate each unit of time within the time range in chronological order, and if there is an updated record for any category in the valid record set at the current time, then the updated record is used to update the state value of the corresponding category in the state container, otherwise the state value of the corresponding category remains unchanged.

[0075] In an optional embodiment of this application, the third processing module in this application includes: a ninth processing unit, used to perform aggregation calculation for each unit of time during the time-sequential iteration process, until the aggregation calculation for the last unit of time in the preset time range is completed, so as to obtain the aggregation result.

[0076] In an optional embodiment of this application, the ninth processing unit further performs the following steps: maintaining a global aggregation variable, wherein the initial value of the global aggregation variable is equal to the aggregation result of all category state values ​​in the boundary state record set; within the current unit time, for each category with updated records in the valid record set, calculating the change in its state value relative to the original state value in the state container, and accumulating the change to the global aggregation variable; determining the global aggregation variable after accumulation as the aggregation result for the current unit time, and updating the state value of the corresponding category in the state container.

[0077] In an optional embodiment of this application, the fourth processing module in this application includes: a conversion unit, used to convert the aggregation result into a structured data format, wherein the structured data format is a key-value pair sequence or JSON format; and a tenth processing unit, used to send the structured data format to the front end, used to drive the visualization chart component to display it in the form of a time series chart.

[0078] like Figure 4 As shown in the figure, this application provides an electronic device, including a processor 411, a communication interface 412, a memory 413, and a communication bus 414, wherein the processor 411, the communication interface 412, and the memory 413 communicate with each other through the communication bus 414. Memory 413 is used to store computer programs; In one embodiment of this application, when the processor 411 executes the program stored in the memory 413, it implements the method for processing intermittent data provided in any of the foregoing method embodiments, and its function is similar, so it will not be described again here.

[0079] This application also provides a computer-readable storage medium having a computer program stored thereon, which, when executed by a processor, implements the steps of the method for processing intermittent data as provided in any of the foregoing method embodiments.

[0080] The device embodiments described above are merely illustrative. The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the modules can be selected to achieve the purpose of this embodiment according to actual needs.

[0081] Through the above description of the embodiments, those skilled in the art can clearly understand that each embodiment can be implemented using software plus a general-purpose hardware platform, or of course, using hardware. Based on this understanding, the above technical solutions, in essence or the parts that contribute to the related technology, can be embodied in the form of a software product. This computer software product can be stored in a computer-readable storage medium, such as ROM / RAM, magnetic disk, optical disk, etc., and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute the methods described in the various embodiments or some parts of the embodiments.

[0082] It should be understood that the terminology used herein is for the purpose of describing particular exemplary embodiments only and is not intended to be limiting. Unless the context clearly indicates otherwise, the singular forms “a,” “an,” and “described” as used herein may also include the plural forms. The terms “comprising,” “including,” “containing,” and “having” are inclusive and therefore indicate the presence of the stated features, steps, operations, elements, and / or components, but do not exclude the presence or addition of one or more other features, steps, operations, elements, components, and / or combinations thereof. The method steps, processes, and operations described herein are not construed as requiring them to be performed in a particular order described or illustrated unless the order of performance is explicitly indicated. It should also be understood that additional or alternative steps may be used.

[0083] The above description is merely a specific embodiment of the present invention, enabling those skilled in the art to understand or implement the invention. Various modifications to these embodiments will be readily apparent to those skilled in the art, and the general principles defined herein may be implemented in other embodiments without departing from the spirit or scope of the invention. Therefore, the present invention is not to be limited to the embodiments shown herein, but is to be accorded the widest scope consistent with the principles and novel features claimed herein.

Claims

1. A method for processing intermittent data, characterized in that, include: Extract the latest valid record for each category within each time unit of a preset time range from the original data table to obtain a valid record set. The original data table is used to record and store intermittent data generated by the business system and arranged in chronological order. Each category refers to the smallest dimensional combination in the intermittent data that determines the state affiliation. Obtain the latest historical state record for each category before the start time of the preset time range from the original data table to obtain the boundary state record set; Iterate through each unit of time within the preset time range in chronological order to update the state values ​​of the corresponding categories in the boundary state record set based on the effective record set, and perform aggregation calculation on the updated category state values ​​to obtain the aggregation result of the current time. Output the aggregation results in the target format.

2. The method according to claim 1, characterized in that, Extract the latest valid record for each category within each time unit of the preset time range from the original data table to obtain the corresponding valid record set, including: The intermittent data within a preset time range in the original data table are grouped according to the combination of classification dimensions and time order; Extract the latest valid record for each category's dimension combination from the grouping, and construct the valid record set based on the latest valid record for all categories.

3. The method according to claim 1, characterized in that, The latest historical state record for each category before the start time of the preset time range is obtained from the original data table, resulting in the corresponding boundary state record set, including: Intermittent data within a preset duration before the start time of a preset time range in the original data table are grouped according to the combination of classification dimensions and time order; If a historical record corresponding to the dimension combination of the target category exists in the group, the latest historical record is extracted from the historical record; wherein, the target category is any category of all categories in the valid record set; If there is no historical record in the group corresponding to the dimension combination of the target category, the default value of the target category shall be determined as the latest historical record of the target category; The boundary state record set is constructed based on the latest historical record of all the target classifications.

4. The method according to claim 1, characterized in that, Iterate through each unit of time within the preset time range in chronological order to update the state values ​​of the corresponding categories in the boundary state record set based on the valid record set, including: A state container is constructed in memory, and the records in the boundary state record set are loaded into the state container as the initial state; wherein, the state container is a single key-value pair mapping structure, and the number of keys is equal to the number of all categories within the preset time range; Iterate through each unit of time within the preset time range in chronological order. If there is an update record for any category in the valid record set at the current time, then use the update record to update the state value of the corresponding category in the state container; otherwise, keep the state value of the corresponding category unchanged.

5. The method according to claim 4, characterized in that, The updated classification state values ​​are aggregated to obtain the aggregated results for the current time, including: During the time-sequential iteration process, an aggregation calculation is performed for each unit of time until the aggregation calculation for the last unit of time in the preset time range is completed, so as to obtain the aggregation result.

6. The method according to claim 5, characterized in that, Performing aggregate calculations for each unit of time includes: Maintain a global aggregation variable, wherein the initial value of the global aggregation variable is equal to the aggregation result of all classification state values ​​in the boundary state record set; Within the current unit of time, for each category in the valid record set that has an updated record, calculate the change in its state value relative to the original state value in the state container, and accumulate the change to the global aggregate variable; The global aggregation variable after accumulation is determined as the aggregation result for the current unit of time, and the state value of the corresponding category in the state container is updated.

7. The method according to claim 1, characterized in that, Output the aggregation results in the target format, including: The aggregation result is converted into a structured data format, wherein the structured data format is a sequence of key-value pairs or JSON format; The structured data format is sent to the front end to drive the visualization chart component to display the data in the form of a time series chart.

8. An apparatus for processing intermittent data, characterized in that, include: The first processing module is used to extract the latest valid record of each category in each time unit within a preset time range from the original data table to obtain a set of valid records. The original data table is used to record and store intermittent data generated by the business system and arranged in chronological order. Each category refers to the smallest dimensional combination in the intermittent data that determines the state attribution. The second processing module is used to obtain the latest historical state record of each category before the start time of the preset time range from the original data table, and obtain the boundary state record set. The third processing module is used to iterate each unit time within the preset time range in chronological order, update the state values ​​of the corresponding categories in the boundary state record set based on the effective record set, and perform aggregation calculation on the updated category state values ​​to obtain the aggregation result of the current time. The fourth processing module is used to output the aggregation result in the target format.

9. An electronic device, characterized in that, include: The processor, communication interface, memory, and communication bus are connected, with the processor, communication interface, and memory communicating with each other via the communication bus. The memory is used to store a computer program; the processor is used to execute the computer program to implement the method for processing intermittent data as described in any one of claims 1-7.

10. A storage medium having a computer program stored thereon, characterized in that, When executed by a processor, the computer program implements the method for processing intermittent data as described in any one of claims 1-7.

Images