A method and system for grouping and aggregating at fixed time intervals

Abstract

The present invention discloses a method and system for grouping and aggregating at fixed time intervals, belonging to the field of database technology, including: formalizing the description of the disorder degree of time series data by comparing the time stamp of the time series data with the latest time stamp of the inserted data; real-time monitoring and real-time updating of the information describing the disorder degree; query optimization using the information describing the disorder degree; dynamic allocation of resources according to the disorder degree, grouping and aggregating time series data at fixed time intervals during scanning; and performance prediction according to the disorder degree and performance indicators to achieve database autonomy. The present invention can greatly improve the query performance of grouping and aggregating at fixed time intervals, while reducing the resource consumption of the system, especially the memory consumption.

Description

Technical Field

[0001] The present invention relates to the field of database technology, in particular to a method and system for grouping and aggregating at fixed time intervals. Background Art

[0002] With the development of IoT technology, the number of IoT devices and applications has grown rapidly, and the demand for efficient storage, management, and analysis of the massive data generated by IoT devices has also increased. Compared with the data processed by traditional databases, the data generated by IoT devices and applications has obvious time series data processing characteristics, such as:

[0003] Data always contains a timestamp attribute and is usually generated frequently and stably with time as the dimension;

[0004] The amount of data is huge. For example, a sensor generates about 1.4M data in a day. A factory with tens of millions of sensors can generate 500T of data in a year. In emerging industries such as the Internet of Vehicles and digital energy, managing hundreds of millions or even billions of devices is becoming a trend.

[0005] Data is updated and deleted less frequently;

[0006] Queries usually use time as a filter condition, and aggregate analysis is usually performed on the device and time dimensions.

[0007] Since traditional data processing technology is difficult to efficiently store and analyze such large-scale data, time series data processing was born and developed rapidly as a specialized technology for data processing. In addition to the Internet of Things industry, time series data processing technology is also widely used in industries such as system monitoring and stock trading. In time series data processing, aggregate query on the time dimension is one of the most common data analysis scenarios. In particular, grouped aggregate query at fixed time intervals is widely used in applications such as reporting, monitoring, and visualization. For example, query the hourly power consumption of each device in the past week (the power consumption of the device is collected once a minute).

[0008] Many mainstream database products, including traditional relational databases and time series databases, support aggregate queries at fixed time intervals. For the above electricity consumption statistics scenario, the SQL query statements that can be used by several databases are as follows:

[0009] PostgreSQL:

[0010] SELECT DATE_BIN('1hour',V_TIMESTAMP,'2024-02-05')T,SUM(USAGE)

[0011] FROM METRICS

[0012] WHERE V_TIMESTAMP BETWEEN'2024-02-05 00:00:00'AND'2024-02-11 23:59:59'

[0013] GROUP BY T

[0014] ORDER BY T;

[0015] TimescaleDB:

[0016] SELECT TIME_BUCKET('1hour',V_TIMESTAMP)T,SUM(USAGE)

[0017] FROM METRICS

[0018] WHERE V_TIMESTAMP BETWEEN'2024-02-05 00:00:00'AND'2024-02-11 23:59:59'

[0019] GROUP BY T

[0020] ORDER BY T;

[0021] TDEngine:

[0022] SELECT SUM(USAGE)FROM METRICS INTERVAL(1h);

[0023] Traditional grouping aggregation methods include sorting aggregation and hashing aggregation.

[0024] Sorting aggregation first sorts by the grouping field, then aggregates the data with the same grouping field on the ordered data and returns the result. If the data is already ordered, the sorting step can be omitted. Aggregating ordered data is sometimes called "stream aggregation".

[0025] Hash aggregation distributes the grouping fields to different buckets after hashing. After all the data is read, the data in all buckets is aggregated and the results are returned.

[0026] Since "sort aggregation" (sometimes called "stream aggregation") on ordered data is more efficient than hash aggregation, time series data is usually generated in chronological order, and time series data is more suitable for sort aggregation (or "stream aggregation"). However, the generation of time series data cannot always be guaranteed to be ordered (due to various reasons such as network fluctuations and equipment failures), and the database has to perform sort aggregation or hash aggregation that requires sorting, which makes fixed time interval grouping aggregation on massive time series data very inefficient. The query described in the above example can take several seconds. If the query time range is expanded to one year and the number of devices increases from one to tens of thousands, the query can take minutes or even hours. This brings difficulties to data analysis and artificial intelligence learning tasks based on time series data. Summary of the invention

[0027] The technical task of the present invention is to address the above shortcomings and provide a method and system for grouping and aggregating data at fixed time intervals, which can perform efficient grouping and aggregating operations even when the data generation time is not in order, without the need for sorting operations or hash operations, and without the need for indexing and other data objects that require additional overhead. When the data is generally generated in chronological order and some are out of order, the method and system can improve the efficiency of fixed time interval grouping and aggregating queries by 3-10 times.

[0028] The technical solution adopted by the present invention to solve its technical problem is:

[0029] A method for grouping and aggregating at fixed time intervals, comprising:

[0030] The degree of disorder of time series data is formally described by comparing the timestamp of the time series data with the latest timestamp of the inserted data;

[0031] Real-time monitoring and real-time updating of information describing the degree of disorder;

[0032] Use information describing the degree of disorder to optimize queries;

[0033] Dynamically allocate resources according to the disorder degree, and perform grouping and aggregation of time series data at fixed time intervals during the scanning process;

[0034] Make performance predictions based on the degree of disorder and performance indicators to achieve database autonomy.

[0035] Furthermore, the maximum disorder distance is used to define the disorder degree of time series data, and then the data statistics of the maximum disorder distance are collected in real time to assist the query optimizer and query executor to group and aggregate the time series data at fixed time intervals during the scanning process by establishing dynamic time buckets, avoiding sorting or hash aggregation, thereby greatly improving the execution performance of fixed time interval grouping aggregation queries and reducing resource consumption;

[0036] At the same time, by learning from the workload, we predict the correlation between the performance improvement brought by this method and the degree of disorder, and further improve the overall performance of fixed time interval grouping and aggregation query workloads.

[0037] Furthermore, the specific implementation of this method includes:

[0038] 1) Time series data is partitioned and stored according to device and time dimensions;

[0039] 2) Collect out-of-order distance information, record the maximum out-of-order distance of time series data on a partition;

[0040] 3) Check queries containing fixed time interval grouping aggregation through the query optimizer and determine whether the dynamic time bucket construction method can be used, and evaluate the cost of the dynamic time bucket construction method and compare it with the cost of other grouping aggregation methods;

[0041] 4) If the method of dynamically constructing time buckets is adopted by the query optimizer, the query executor will execute the method, and the query executor scans the devices and time partitions that meet the query conditions one by one;

[0042] 5) Provide feedback on query performance based on workload conditions by adding learning-based query autonomy.

[0043] Furthermore, the time series data is partitioned and stored according to the device and time dimensions. The time series data of a specified device and a specified time period are written into a continuous physical space (such as the same file), and the time series data of different devices or different time periods of the same device are written into different physical spaces (such as different files).

[0044] Furthermore, data time distance is defined as the time difference of data record timestamps, and out-of-order distance is defined by comparing the maximum difference between the latest record (that is, the data record with the largest timestamp) and the timestamps of all data inserted thereafter from the data semantics perspective; the maximum out-of-order distance represents the degree of out-of-orderness of a time partition.

[0045] Furthermore, the query optimizer obtains the summary value of the out-of-order distance statistics for cost evaluation.

[0046] Furthermore, the query executor executes the following process for each partition that meets the conditions:

[0047] 4.1) Read the maximum out-of-order distance of the partition;

[0048] 4.2) Construct time buckets based on the maximum out-of-order distance and time interval of the partition. The number of buckets is determined by the number of time intervals contained in the maximum out-of-order distance of the partition. For example, if the maximum out-of-order distance is 5 minutes and 30 seconds and the time interval is 1 minute, the maximum time interval span of the maximum out-of-order distance is 7. The query executor can construct 7 time buckets. The time bucket adopts a circular linked list structure, that is, all "buckets" have a previous and next "bucket".

[0049] 4.3) Scan the partition data and find the first record that meets the conditions, and assign a time identifier to the time bucket according to the timestamp of the record; for example, if the first record is '2024-02-05 09:03:25' and the time interval is 1 minute, the seven time buckets are identified as '2024-02-05 09:03:00', '2024-02-05 09:04:00', '2024-02-05 09:05:00', '2024-02-05 09:06:00', '2024-02-05 09:07:00', '2024-02-05 09:08:00', '2024-02-05 09:09:00';

[0050] 4.4) When the time bucket identification is completed, the first record that meets the conditions is classified into the first time bucket, marked as the "current time bucket", and the corresponding aggregation operation is performed;

[0051] 4.5) Continue to scan the partition sequentially, classify each record into its corresponding time bucket, and perform corresponding aggregation operations; here, corresponding means that the record timestamp is greater than the time bucket identification timestamp and less than (time bucket identification timestamp + time interval).

[0052] 4.6) If the scanned record cannot find the corresponding time bucket, that is, the record timestamp is greater than the maximum time bucket identifier timestamp plus the time interval; since the disordered time distance in the partition must be less than the number of time buckets × time interval, it proves that the time series data corresponding to the "current time bucket" has been completely scanned, and the aggregation result of the time interval corresponding to the "current time bucket" can be returned;

[0053] 4.7) When the time interval corresponding to the "current time bucket" is aggregated, the time bucket identifier is modified to: original identifier timestamp + number of time buckets × time interval, and the next time bucket of the "current time bucket" is identified as the "current time bucket"; as in the above example, when the record '2024-02-05 09:10:05' is scanned, the aggregation result of the time bucket '2024-02-05 09:03:00' is returned, and the time bucket identifier timestamp is modified to '2024-02-05 09:10:00', the new record is classified into the modified time bucket, and the corresponding aggregation operation is continued;

[0054] 4.8) Repeat steps 4.5), 4.6), and 4.7) for the subsequent scanned records until all records are scanned.

[0055] The present invention also claims a system for fixed time interval group aggregation, comprising:

[0056] Supports data storage engine partitioned by device and time;

[0057] The out-of-order distance information collection module is used to record the maximum out-of-order distance of time series data on a partition;

[0058] A query optimizer that supports dynamically building time buckets;

[0059] A query executor that supports dynamically building time buckets;

[0060] Learning-based query autonomy module;

[0061] The system implements the above-mentioned method of grouping and aggregating at fixed time intervals.

[0062] The present invention also claims a device for fixed time interval group aggregation, comprising: at least one memory and at least one processor;

[0063] The at least one memory is used to store a machine-readable program;

[0064] The at least one processor is used to call the machine-readable program to implement the above method.

[0065] The present invention also claims protection for a computer-readable medium having computer instructions stored thereon, which can implement the above method when executed by a processor.

[0066] Compared with the prior art, the method and system for grouping and aggregating at fixed time intervals of the present invention have the following beneficial effects:

[0067] The present invention can greatly improve the query performance of fixed time interval grouping aggregation, while reducing system resource consumption, especially memory consumption. This is mainly due to the fact that the present invention can complete fixed time interval grouping aggregation in one scanning process without sorting or hashing operations.

[0068] The test results on the time series data test benchmark TSBS scenario 2 show that this method can improve the performance of TSBS related queries by 3-10 times. BRIEF DESCRIPTION OF THE DRAWINGS

[0069] Figure 1 This is a system architecture diagram of fixed time interval group aggregation provided by an embodiment of the present invention;

[0070] Figure 2 It is a flowchart of collecting out-of-order distance information in a method for grouping and aggregating at fixed time intervals provided by an embodiment of the present invention;

[0071] Figure 3 It is a flowchart of query execution in a method for grouping and aggregating at fixed time intervals provided by an embodiment of the present invention. DETAILED DESCRIPTION

[0072] The present invention will be further described below in conjunction with specific embodiments.

[0073] An embodiment of the present invention provides a method for grouping and aggregating at fixed time intervals, including:

[0074] The degree of disorder of time series data is formally described by comparing the timestamp of the time series data with the latest timestamp of the inserted data;

[0075] Real-time monitoring and real-time updating of information describing the degree of disorder;

[0076] Use information describing the degree of disorder to optimize queries;

[0077] Dynamically allocate resources according to the disorder degree, and perform grouping and aggregation of time series data at fixed time intervals without sorting and hashing operations during the scanning process;

[0078] Make performance predictions based on the degree of disorder and performance indicators to achieve database autonomy.

[0079] This method uses the characteristic that time series data is generated in time order in most cases, and adopts the "maximum disorder distance" to define the disorder degree of time series data. Then, by collecting the data statistical information of "maximum disorder distance" in real time, it assists the query optimizer and query executor to group and aggregate time series data at fixed time intervals during the scanning process by establishing a dynamic "time bucket" method, avoiding sorting or hashing aggregation, thereby greatly improving the execution performance of fixed time interval grouping aggregation queries and reducing resource consumption; at the same time, by learning about the workload, it predicts the correlation between the performance improvement brought by this method and the disorder degree, and further improves the overall performance of fixed time interval grouping aggregation query workloads.

[0080] This method proposes an aggregation method to dynamically construct "time buckets" based on the time distance of data to improve the efficiency of group aggregation queries at fixed time intervals. Its specific implementation includes:

[0081] 1. To effectively construct a "time bucket" of appropriate size, time series data should be partitioned and stored according to the device and time dimensions. That is, the time series data of a specified device and a specified time period are written into a continuous physical space (such as the same file), and the time series data of different devices or different time periods of the same device are written into different physical spaces (such as different files).

[0082] For example:

[0083] Partition 1 stores the time series data with timestamps between '2024-02-05' and '2024-02-11' on device 1;

[0084] Partition 2 stores the time series data with timestamps between '2024-02-12' and '2024-02-18' on device 1;

[0085] …

[0086] Partition N stores the time series data with timestamps between '2024-02-05' and '2024-02-11' on device M;

[0087] Partition N+1 stores time series data with timestamps between '2024-02-12' and '2024-02-18' on device M;

[0088] …

[0089] 2. Collect time distance information and record the maximum out-of-order distance of time series data on a partition.

[0090] The "data time distance" here is defined as the time difference of the data record timestamps. The out-of-order distance is defined here by comparing the maximum difference between the timestamps of the latest record (that is, the data record with the largest timestamp) and all the data inserted thereafter from the data semantics perspective. The maximum out-of-order distance represents the degree of out-of-orderness of a time partition.

[0091] Combination Figure 2 As shown, for example, after the time series data is written continuously and sequentially, the timestamp of the latest record is '2024-02-10 09:00:00.052';

[0092] Afterwards, the data record '2024-02-10 08:52:00.073' is written, and the maximum out-of-order distance of this segment of data is: '2024-02-10 09:00:00.052'-'2024-02-10 08:52:00.073'=479979ms;

[0093] Afterwards, the data record '2024-02-10 08:59:00.073' is written. Since the distance between this record and the latest record is less than 479979ms, the maximum out-of-order distance remains unchanged;

[0094] Afterwards, the data record '2024-02-10 08:50:00.049' is written. Since the distance between this record and the latest record is 600003ms, which is greater than 479979ms, the maximum out-of-order distance is updated to 479979ms.

[0095] 3. The query optimizer that supports the dynamic construction of time buckets method is used to check queries containing fixed time interval grouping aggregations and determine whether the dynamic construction of time buckets method can be used. The query optimizer also evaluates the cost of the dynamic construction of time buckets method and compares it with the cost of other grouping aggregation methods. The query optimizer needs to obtain the summary value of the out-of-order distance statistics for cost evaluation.

[0096] 4. If the method of dynamically constructing time buckets is adopted by the query optimizer, the query executor will execute the method.

[0097] The query executor scans the devices and time partitions that meet the query conditions one by one; combined Figure 3 As shown, the following process is performed for each partition that meets the conditions:

[0098] 4.1. Read the maximum out-of-order distance of the partition;

[0099] 4.2. Construct time buckets based on the maximum out-of-order distance and time interval of the partition. The number of buckets is determined by the number of time partitions contained in the maximum out-of-order distance of the partition. For example, if the maximum out-of-order distance is 5 minutes and 30 seconds and the time interval is 1 minute, the maximum time interval span of the maximum out-of-order distance is 7. The query executor can construct 7 time buckets. The time bucket adopts a circular linked list structure, that is, all "buckets" have a previous and next "bucket".

[0100] 4.3. Scan the partition data and find the first record that meets the conditions. Assign a time identifier to the time bucket according to the timestamp of the record. For example, if the first record is '2024-02-05 09:03:25' and the time interval is 1 minute, the 7 time buckets are identified as '2024-02-05 09:03:00', '2024-02-05 09:04:00', '2024-02-05 09:05:00', '2024-02-05 09:06:00', '2024-02-05 09:07:00', '2024-02-05 09:08:00', '2024-02-05 09:09:00'.

[0101] 4.4. When the time bucket identification is completed, the first record that meets the conditions is classified into the first time bucket, marked as the "current time bucket", and the corresponding aggregation operation is performed.

[0102] 4.5. Continue to scan the partition sequentially, classify each record into its corresponding time bucket, and perform corresponding aggregation operations. Correspondence here means that the record timestamp is greater than the time bucket identification timestamp and less than (time bucket identification timestamp + time interval).

[0103] 4.6. If the scanned record cannot find the corresponding time bucket, that is, the record timestamp is greater than the maximum time bucket identifier timestamp plus the time interval; since the disordered time distance in the partition must be less than the number of time buckets × time interval, it proves that the time series data corresponding to the "current time bucket" has been completely scanned, and the aggregation result of the time interval corresponding to the "current time bucket" can be returned.

[0104] 4.7. When the time interval corresponding to the "current time bucket" is aggregated, the time bucket identifier is modified to: original identifier timestamp + number of time buckets × time interval, and the next time bucket of the "current time bucket" is identified as the "current time bucket". For example, when the record '2024-02-05 09:10:05' is scanned, the aggregation result of the time bucket '2024-02-05 09:03:00' is returned, and the time bucket identifier timestamp is modified to '2024-02-05 09:10:00', the new record is classified into the modified time bucket, and the corresponding aggregation operation is continued;

[0105] 4.8. Repeat steps 4.5), 4.6), and 4.7) for the subsequent scanned records until all records are scanned.

[0106] 5. The above method is not effective for all workloads. For example, in a specific environment, the generation of time series data may be very disordered. In this case, the above method will not bring the expected performance improvement and resource usage reduction due to too many constructed "time buckets". By adding learning-based query autonomy, feedback on query effects can be provided based on the workload.

[0107] For example, by learning the correlation between the maximum out-of-order distance and performance improvement, the applicable maximum out-of-order distance range for a specific workload can be found and given feedback to the optimizer.

[0108] For example, by learning the time interval grouping patterns commonly used by workloads, feedback can be given to the out-of-order information collection module, so that the out-of-order information collection module collects information with appropriate time accuracy, reducing computing and storage resource consumption.

[0109] The following are the test results on the time series data test benchmark TSBS scenario 2:

[0110]

[0111] As shown in the above experiments, this method can improve the performance of TSBS related queries by 3-10 times.

[0112] The embodiment of the present invention further provides a system for group aggregation at fixed time intervals, which implements the above-mentioned method for group aggregation at fixed time intervals. It includes:

[0113] 1. Data storage engine that supports device and time partitioning

[0114] In order to effectively construct a "time bucket" of appropriate size, time series data should be partitioned according to the device and time dimensions. That is, the time series data of a specified device and a specified time period are written into a continuous physical space (such as the same file), and the time series data of different devices or different time periods of the same device are written into different physical spaces (such as different files).

[0115] For example:

[0116] Partition 1 stores the time series data with timestamps between '2024-02-05' and '2024-02-11' on device 1;

[0117] Partition 2 stores the time series data with timestamps between '2024-02-12' and '2024-02-18' on device 1;

[0118] …

[0119] Partition N stores the time series data with timestamps between '2024-02-05' and '2024-02-11' on device M;

[0120] Partition N+1 stores time series data with timestamps between '2024-02-12' and '2024-02-18' on device M;

[0121] …

[0122] 2. Out-of-order distance information collection module

[0123] The time distance information collection module is a key module in this system. This module is used to record the maximum out-of-order distance of time series data on a partition.

[0124] The "data time distance" here is defined as the time difference of the data record timestamps. The out-of-order distance is defined here by comparing the maximum difference between the timestamps of the latest record (that is, the data record with the largest timestamp) and all the data inserted thereafter from the data semantics perspective. The maximum out-of-order distance represents the degree of out-of-orderness of a time partition.

[0125] For example, after writing time series data in a continuous order, the timestamp of the latest record is '2024-02-1009:00:00.052';

[0126] Afterwards, the data record '2024-02-10 08:52:00.073' is written, and the maximum out-of-order distance of this segment of data is: '2024-02-10 09:00:00.052'-'2024-02-10 08:52:00.073'=479979ms;

[0127] Afterwards, the data record '2024-02-10 08:59:00.073' is written. Since the distance between this record and the latest record is less than 479979ms, the maximum out-of-order distance remains unchanged;

[0128] Afterwards, the data record '2024-02-10 08:50:00.049' is written. Since the distance between this record and the latest record is 600003ms, which is greater than 479979ms, the maximum out-of-order distance is updated to 479979ms.

[0129] 3. Query optimizer that supports dynamic construction of time buckets

[0130] The query optimizer is used to check queries that contain fixed time interval grouping aggregations and determine whether the dynamic time bucket construction method can be used and to evaluate the cost of the dynamic time bucket construction method and compare it with the cost of other grouping aggregation methods. The optimizer needs to obtain the summary value of the out-of-order distance statistics for cost evaluation.

[0131] 4. Query executor that supports dynamic construction of time bucket method

[0132] The executor of the dynamic time bucket construction method is also a key module of this system. If the method of dynamically constructing time buckets is adopted by the optimizer, the query executor will execute this method.

[0133] The query executor scans the devices and time partitions that meet the query conditions one by one. For each partition that meets the conditions, the following process is executed:

[0134] 4.1) Read the maximum out-of-order distance of the partition;

[0135] 4.2) Construct time buckets based on the maximum out-of-order distance and time interval of the partition. The number of buckets is determined by the number of time partitions contained in the maximum out-of-order distance of the partition. For example, if the maximum out-of-order distance is 5 minutes and 30 seconds and the time interval is 1 minute, the maximum time interval span of the maximum out-of-order distance is 7. The query executor can construct 7 time buckets. The time bucket adopts a circular linked list structure, that is, all "buckets" have a previous and next "bucket".

[0136] 4.3) Scan the partition data and find the first record that meets the conditions. Assign a time identifier to the time bucket according to the timestamp of the record. For example, if the first record is '2024-02-05 09:03:25' and the time interval is 1 minute, the 7 time buckets are identified as '2024-02-05 09:03:00', '2024-02-05 09:04:00', '2024-02-05 09:05:00', '2024-02-05 09:06:00', '2024-02-05 09:07:00', '2024-02-05 09:08:00', '2024-02-05 09:09:00'.

[0137] 4.4) When the time bucket identification is completed, the first record that meets the conditions is classified into the first time bucket, marked as the "current time bucket", and the corresponding aggregation operation is performed.

[0138] 4.5) Continue to scan the partition sequentially, classify each record into its corresponding time bucket, and perform corresponding aggregation operations. Correspondence here means that the record timestamp is greater than the time bucket identification timestamp and less than (time bucket identification timestamp + time interval).

[0139] 4.6) If the scanned record cannot find the corresponding time bucket, that is, the record timestamp is greater than the maximum time bucket identifier timestamp plus the time interval. Since the disordered time distance in the partition must be less than the number of time buckets * time interval, it proves that the time series data corresponding to the "current time bucket" has been completely scanned, and the aggregation result of the time interval corresponding to the "current time bucket" can be returned.

[0140] 4.7) When the time interval corresponding to the "current time bucket" is aggregated, the time bucket identifier is modified to (original identifier timestamp + number of time buckets * time interval), and the next time bucket of the "current time bucket" is identified as the "current time bucket". For example, when the record '2024-02-05 09:10:05' is scanned, the aggregation result of the time bucket '2024-02-05 09:03:00' is returned, and the time bucket identifier timestamp is modified to '2024-02-0509:10:00', the new record is classified into the modified time bucket, and the corresponding aggregation operation is continued.

[0141] 4.8) Repeat steps 4.5, 4.6, and 4.7 for the subsequent scanned records until all records are scanned.

[0142] 5. Learning-based query autonomy module

[0143] The above method is not effective for all workloads. For example, in a specific environment, the generation of time series data may be very disordered. In this case, the above method will not bring the expected performance improvement and resource usage reduction due to too many constructed "time buckets". By adding learning-based query autonomy, feedback on query effects can be provided based on the workload.

[0144] For example, by learning the correlation between the maximum out-of-order distance and performance improvement, the applicable maximum out-of-order distance range for a specific workload can be found and given feedback to the optimizer.

[0145] For example, by learning the time interval grouping patterns commonly used by workloads, feedback can be given to the out-of-order information collection module, so that the out-of-order information collection module collects information with appropriate time accuracy, reducing computing and storage resource consumption.

[0146] An embodiment of the present invention further provides a device for group aggregation at fixed time intervals, comprising: at least one memory and at least one processor;

[0147] The at least one memory is used to store a machine-readable program;

[0148] The at least one processor is used to call the machine-readable program to implement the method for grouping at fixed time intervals described in the above embodiment.

[0149] The embodiment of the present invention further provides a computer-readable medium, on which computer instructions are stored, and when the computer instructions are executed by a processor, the method for group aggregation at fixed time intervals described in the above embodiment is implemented. Specifically, a system or device equipped with a storage medium can be provided, on which a software program code implementing the functions of any of the above embodiments is stored, and a computer (or CPU or MPU) of the system or device reads and executes the program code stored in the storage medium.

[0150] In this case, the program code itself read from the storage medium can realize the function of any one of the above-mentioned embodiments, and thus the program code and the storage medium storing the program code constitute a part of the present invention.

[0151] The storage medium embodiments for providing the program code include a floppy disk, a hard disk, a magneto-optical disk, an optical disk (such as CD-ROM, CD-R, CD-RW, DVD-ROM, DVD-RAM, DVD-RW, DVD+RW), a magnetic tape, a non-volatile memory card, and a ROM. Alternatively, the program code can be downloaded from a server computer by a communication network.

[0152] In addition, it should be clear that the functions of any of the above embodiments can be implemented not only by executing the program code read by the computer, but also by enabling an operating system operating on the computer to complete part or all of the actual operations based on instructions from the program code.

[0153] In addition, it can be understood that the program code read from the storage medium is written to a memory provided in an expansion board inserted into the computer or written to a memory provided in an expansion unit connected to the computer, and then based on the instructions of the program code, a CPU installed on the expansion board or the expansion unit is enabled to perform part or all of the actual operations, thereby realizing the functions of any of the above-mentioned embodiments.

[0154] The present invention is shown and described in detail above through the accompanying drawings and preferred embodiments. However, the present invention is not limited to these disclosed embodiments. Based on the above multiple embodiments, those skilled in the art can know that the code review methods in the above different embodiments can be combined to obtain more embodiments of the present invention, and these embodiments are also within the protection scope of the present invention.

Claims

1. A method for grouping and aggregating at fixed time intervals, characterized in that: include: The degree of disorder of time series data is formally described by comparing the timestamp of the time series data with the latest timestamp of the inserted data; Real-time monitoring and real-time updating of information describing the degree of disorder; Use information describing the degree of disorder to optimize queries; Dynamically allocate resources according to the degree of disorder, and perform grouping and aggregation of time series data at fixed time intervals during the scanning process; Perform performance prediction based on disorder level and performance indicators to achieve database autonomy; The specific implementation of this method includes: 1) Time series data is partitioned and stored according to the device and time dimensions; 2) Collect out-of-order distance information, recording the maximum out-of-order distance of time series data on a partition; 3) Check queries containing fixed time interval grouping aggregations through the query optimizer and determine whether the dynamic time bucket construction method can be used, and evaluate the cost of the dynamic time bucket construction method and compare it with the cost of other grouping aggregation methods; 4) If the method of dynamically constructing time buckets is adopted by the query optimizer, the query executor will execute the method of dynamically constructing time buckets, and the query executor scans the devices and time partitions that meet the query conditions one by one; 5) Provide feedback on query performance based on workload conditions by adding learning-based query autonomy; The query executor performs the following process for each partition that meets the conditions: 4.1) Read the maximum out-of-order distance of the partition; 4.2) Construct time buckets based on the maximum out-of-order distance and time interval of the partition. The number of buckets is determined by the number of time intervals contained in the maximum out-of-order distance of the partition. The time buckets adopt a circular linked list structure. 4.3) Scan the partition data and find the first record that meets the conditions, and assign a time identifier to the time bucket based on the timestamp of the record; 4.4) When the time bucket identification is completed, the first record that meets the conditions is placed in the first time bucket, marked as "current time bucket", and the corresponding aggregation operation is performed; 4.5) Continue to scan the partition sequentially, classify each record into its corresponding time bucket, and perform corresponding aggregation operations; here, corresponding means that the record timestamp is greater than the time bucket identification timestamp and less than (time bucket identification timestamp + time interval); 4.6) If the scanned record cannot find the corresponding time bucket, that is, the record timestamp is greater than the maximum time bucket identifier timestamp plus the time interval; since the disordered time distance in the partition must be less than the number of time buckets × time interval, it proves that the time series data corresponding to the "current time bucket" has been completely scanned, and the aggregation result of the time interval corresponding to the "current time bucket" can be returned; 4.7) When the time interval corresponding to the "current time bucket" is aggregated, the time bucket identifier is modified to: original identifier timestamp + number of time buckets × time interval, and the next time bucket of the "current time bucket" is marked as the "current time bucket"; 4.8) Repeat steps 4.5), 4.6), and 4.7) for the subsequent scanned records until all records are scanned.

2. The method for grouping and aggregating at fixed time intervals according to claim 1, characterized in that: The maximum out-of-order distance is used to define the disorder degree of time series data. Then, by collecting the data statistics of the maximum out-of-order distance in real time, the query optimizer and query executor are assisted to group and aggregate the time series data at fixed time intervals during the scanning process by establishing dynamic time buckets. At the same time, by learning about the workload, we predict the correlation between the performance improvement brought by this method and the degree of disorder.

3. The method for grouping and aggregating at fixed time intervals according to claim 1, characterized in that: The time series data is partitioned and stored according to the device and time dimensions. The time series data of a specified device and a specified time period are written into a continuous physical space, and the time series data of different devices or different time periods of the same device are written into different physical spaces.

4. The method for grouping and aggregating at fixed time intervals according to claim 1, characterized in that: Data time distance is defined as the time difference between data record timestamps, and out-of-order distance is defined by comparing the maximum difference between the timestamps of the latest record from the data semantics perspective and all data inserted thereafter; the maximum out-of-order distance represents the degree of out-of-orderness of a time partition.

5. The method for grouping and aggregating at fixed time intervals according to claim 1, characterized in that: The query optimizer obtains the summary value of the out-of-order distance statistics for cost evaluation.

6. A system for grouping and aggregating at fixed time intervals, characterized in that: include: Supports data storage engine partitioned by device and time; The out-of-order distance information collection module is used to record the maximum out-of-order distance of time series data on a partition; A query optimizer that supports dynamically building time buckets; A query executor that supports dynamically building time buckets; Learning-based query autonomy module; The system implements the method for grouping and aggregating at fixed time intervals as described in any one of claims 1 to 5.

7. A device for grouping and aggregating at fixed time intervals, characterized in that: include: at least one memory and at least one processor; The at least one memory is used to store a machine-readable program; The at least one processor is used to call the machine-readable program to implement the method described in any one of claims 1 to 5.

8. A computer-readable medium, characterized in that The computer readable medium stores computer instructions, which, when executed by a processor, can implement the method according to any one of claims 1 to 5.

Images