Use management method for timebucketgapfill function of database

Abstract

The invention discloses a use management method for a timebucketgapfill function of a database, and relates to the technical field of databases. Comprising the following steps of: 1, setting three state values of a timebucketgapfill function; 2, carrying out line filling operation on time sequence data of a database by utilizing the timebucketgapfill function; according to the method, the database can perform row filling operation on the missing window interval through the use management of the timebucketgapfill function, so that the data output is more complete.

Description

Technical Field

[0001] The invention discloses a use management method of a database time_bucket_gapfill function and relates to the technical field of databases. Background Art

[0002] The original time series data usually contains a large amount of incomplete and deviated data, which will affect the subsequent use of the time series data and the execution results of the analysis. The existing database does not support data filling according to the specified pattern when the data rows in the specified window interval are missing, making the data incomplete. Summary of the invention

[0003] In view of the problems of the prior art, the present invention provides a method for using and managing a time_bucket_gapfill function of a database. By using and managing the time_bucket_gapfill function, the database can perform a row-filling operation on a missing window interval, thereby making the data output more complete.

[0004] The specific scheme proposed by the present invention is:

[0005] The present invention provides a method for managing the use of a database time_bucket_gapfill function, comprising:

[0006] Step 1: Set three status values ​​of the time_bucket_gapfill function, using gapFillInit, gapFillStart and gapFillEnd to represent them respectively. Use gapFillInit to indicate that there is no interval that meets the filling condition yet. Use gapFillStart to indicate that there is an interval that meets the filling condition and the filling operation begins. Use gapFillEnd to indicate that the filling is finished. Use gapFillState to represent that the initial value of the current state is gapFillInit.

[0007] Step 2: Use the time_bucket_gapfill function to fill in the time series data in the database:

[0008] Step 21: Record the time of the first row of data as preTime, and return the first row of data to the upper operator.

[0009] Step 22: Continue to read data and determine the current state of gapFillState. If the current state of gapFillState is gapFillInit, go to step 3. If the current state of gapFillState is gapFillStart, go to step 4. If the current state of gapFillState is gapFillEnd, go to step 5.

[0010] Step 23: Operate the gapFillInit state: If the current data time is curTime-preTime>=interval interval, change gapFillState from gapFillInit to gapFillStart, and record the current row data as gapFillLastRow, and go to step 4; otherwise, update preTime, return the data to the upper operator, and return to step 2.

[0011] Step 24: Operate the gapFillStart state: If the data time column of the newly added row is preTime+N*interval, then each time a row is added, the data is immediately returned to the upper layer operator. When preTime+N*interval is greater than or equal to the time of gapFillLastRow in step 3, the row is added and the preTime value is updated. The state gapFillState is changed from gapFillStart to gapFillEnd, and the process goes to step 5.

[0012] Step 25: Operate on the gapFillEnd state: process the current row data gapFillLastRow recorded in step 3, return the processed data to the upper operator, change the state gapFillState from gapFillEnd to gapFillInit, and then return to step 2 until the data reading is completed.

[0013] Furthermore, step 1 of the method for managing the use of a time_bucket_gapfill function in a database also includes: performing function preparation work, wherein SQL is converted into a structure that can be run by the database, and the time_bucket_gapfill function is parsed into FuncExpr.

[0014] Furthermore, step 1 of the method for managing the use of a database time_bucket_gapfill function also includes: creating a GroupBy expression, loading the data of the time_bucket_gapfill function into GroupByExpr, and sorting the time column in ascending order before performing the groupBy grouping operation.

[0015] Furthermore, in step 1 of the method for managing the use of a database time_bucket_gapfill function, for the data of the time_bucket_gapfill function, before performing a group by operation, the time in each row of data is rounded according to the interval interval to obtain an interval timestamp tb, and then tb is sorted in ascending order.

[0016] Furthermore, when performing a row-filling operation on the time series data of the database in step 2 of the method for using and managing a time_bucket_gapfill function of a database, the interpolate filling function and the time_bucket_gapfill function are used in combination to generate corresponding column values ​​of the new row according to a filling method, and the filling methods include using a constant value constant, using a previous value prev, using a next value next, using linear filling, and filling a null value null.

[0017] The present invention also provides a use management device for a database time_bucket_gapfill function, including a function management module and an execution module.

[0018] The function management module sets three status values ​​of the time_bucket_gapfill function, which are represented by gapFillInit, gapFillStart and gapFillEnd. gapFillInit indicates that there is no interval that meets the filling condition yet. gapFillStart indicates that there is an interval that meets the filling condition and the filling operation begins. gapFillEnd indicates that the filling is finished. gapFillState indicates that the initial value of the current state is gapFillInit.

[0019] The execution module uses the time_bucket_gapfill function to fill in rows of the database's time series data:

[0020] Step 21: Record the time of the first row of data as preTime, and return the first row of data to the upper operator.

[0021] Step 22: Continue to read data and determine the current state of gapFillState. If the current state of gapFillState is gapFillInit, go to step 3. If the current state of gapFillState is gapFillStart, go to step 4. If the current state of gapFillState is gapFillEnd, go to step 5.

[0022] Step 23: Operate the gapFillInit state: If the current data time is curTime-preTime>=interval interval, change gapFillState from gapFillInit to gapFillStart, and record the current row data as gapFillLastRow, and go to step 4; otherwise, update preTime, return the data to the upper operator, and return to step 2.

[0023] Step 24: Operate the gapFillStart state: If the data time column of the newly added row is preTime+N*interval, then each time a row is added, the data is immediately returned to the upper layer operator. When preTime+N*interval is greater than or equal to the time of gapFillLastRow in step 3, the row is added and the preTime value is updated. The state gapFillState is changed from gapFillStart to gapFillEnd, and the process goes to step 5.

[0024] Step 25: Operate on the gapFillEnd state: process the current row data gapFillLastRow recorded in step 3, return the processed data to the upper operator, change the state gapFillState from gapFillEnd to gapFillInit, and then return to step 2 until the data reading is completed.

[0025] Furthermore, the function management module of the device for managing the use of the time_bucket_gapfill function in the database also performs function preparation work, in which SQL is converted into a structure that can be run by the database, and the time_bucket_gapfill function is parsed into FuncExpr.

[0026] Furthermore, the function management module of the device for managing the use of the time_bucket_gapfill function in a database also creates a GroupBy expression, loads the data of the time_bucket_gapfill function into GroupByExpr, and sorts the time column in ascending order before performing the groupBy grouping operation.

[0027] Furthermore, the function management module of the device for managing the use of the time_bucket_gapfill function in a database rounds the time in each row of data according to the interval interval before performing a group by operation on the data of the time_bucket_gapfill function to obtain an interval timestamp tb, and then sorts tb in ascending order.

[0028] Furthermore, when the execution module of the management device for using the time_bucket_gapfill function of the database performs a row filling operation on the time series data of the database, the interpolate filling function and the time_bucket_gapfill function are used to generate the corresponding column values ​​of the new row according to the filling method. The filling method includes using a constant value constant, using a previous value prev, using a next value next, using linear filling, and filling a null value null.

[0029] The benefits of the present invention are:

[0030] The method of the present invention can supplement the missing grouping data to make the data more complete.

[0031] The method of the present invention uses the process of aggregate calculation and the characteristic that the input is required to be ordered, and embeds the row-filling operation into the orderedAggregator, thereby reducing the implementation complexity. BRIEF DESCRIPTION OF THE DRAWINGS

[0032] Figure 1 It is a flowchart of the aggregation calculation of data according to the grouping method.

[0033] Figure 2 This is a flowchart of the aggregate calculation after adding the row padding operation.

[0034] Figure 3 It is a schematic diagram of the specific process of the line filling operation. DETAILED DESCRIPTION

[0035] group by: A SQL statement that groups the result set according to one or more columns.

[0036] orderedAggregator: A process that aggregates data in groups, requiring the input data to be ordered.

[0037] The present invention is further described below in conjunction with the accompanying drawings and specific embodiments so that those skilled in the art can better understand the present invention and implement it, but the embodiments are not intended to limit the present invention.

[0038] Example 1

[0039] The present invention provides a method for managing the use of a database time_bucket_gapfill function, comprising:

[0040] Step 1: Set three status values ​​of the time_bucket_gapfill function, represented by gapFillInit, gapFillStart and gapFillEnd. Use gapFillInit to indicate that there is no interval that meets the row filling condition yet. Use gapFillStart to indicate that there is an interval that meets the row filling condition and the row filling operation begins. Use gapFillEnd to indicate the end of row filling. Use gapFillState to indicate that the initial value of the current state is gapFillInit.

[0041] In step 1, function preparation is also performed to convert SQL into a structure that can be run by the database and parse the time_bucket_gapfill function into FuncExpr.

[0042] When building the GroupBy expression, the time_bucket_gapfill information is loaded into GroupByExpr. For example, when calculating time_bucket_gapfill(time,'1day'), the time column is sorted in ascending order before the groupBy operation.

[0043] Take time_bucket_gapfill(time,interval) as an example. In the execution phase, before the group by operation, the time in each row of data needs to be rounded to the integer interval to obtain an interval timestamp tb, and then the data is sorted in ascending order according to tb.

[0044] The orderedAggregator is a process for performing aggregate calculations on data in groups, and can process ordered data. The orderedAggregator process after adding the time_bucket_gapfill operation is as follows: Figure 2 As shown in the figure, add a row-adding operation when the orderedAggregator process returns data. The specific process is as follows:

[0045] Use the time_bucket_gapfill function to fill in rows of the database's time series data:

[0046] Step 21: Record the time of the first row of data as preTime, and return the first row of data to the upper operator.

[0047] Step 22: Continue to read data and determine the current state of gapFillState. If the current state of gapFillState is gapFillInit, go to step 3. If the current state of gapFillState is gapFillStart, go to step 4. If the current state of gapFillState is gapFillEnd, go to step 5.

[0048] Step 23: Operate the gapFillInit state: If the current data time is curTime-preTime>=interval interval, change gapFillState from gapFillInit to gapFillStart, and record the current row data as gapFillLastRow, and go to step 4; otherwise, update preTime, return the data to the upper operator, and return to step 2.

[0049] Step 24: Operate the gapFillStart state: If the data time column of the newly added row is preTime+N*interval, then each time a row is added, the data is immediately returned to the upper layer operator. When preTime+N*interval is greater than or equal to the time of gapFillLastRow in step 3, the row is added and the preTime value is updated. The state gapFillState is changed from gapFillStart to gapFillEnd, and the process goes to step 5.

[0050] Step 25: Operate on the gapFillEnd state: process the current row data gapFillLastRow recorded in step 3, return the processed data to the upper operator, change the state gapFillState from gapFillEnd to gapFillInit, and then return to step 2 until the data reading is completed.

[0051] When performing row padding operations on the time series data in the database, the interpolate padding function and the time_bucket_gapfill function are used to generate the corresponding column values ​​of the new row according to the padding method. The padding methods include using a constant value constant, using a previous value prev, using a next value next, using linear padding, and filling in null values.

[0052] The method of the present invention groups the time series data according to time and fills the blank interval data to ensure the continuity of the time series, which is helpful for subsequent analysis and visualization.

[0053] Example 2

[0054] The present invention also provides a use management device for a database time_bucket_gapfill function, including a function management module and an execution module.

[0055] The function management module sets three status values ​​of the time_bucket_gapfill function, which are represented by gapFillInit, gapFillStart and gapFillEnd. gapFillInit indicates that there is no interval that meets the filling condition yet. gapFillStart indicates that there is an interval that meets the filling condition and the filling operation begins. gapFillEnd indicates that the filling is finished. gapFillState indicates that the initial value of the current state is gapFillInit.

[0056] The execution module uses the time_bucket_gapfill function to fill in rows of the database's time series data:

[0057] Step 21: Record the time of the first row of data as preTime, and return the first row of data to the upper operator.

[0058] Step 22: Continue to read data and determine the current state of gapFillState. If the current state of gapFillState is gapFillInit, go to step 3. If the current state of gapFillState is gapFillStart, go to step 4. If the current state of gapFillState is gapFillEnd, go to step 5.

[0059] Step 23: Operate the gapFillInit state: If the current data time is curTime-preTime>=interval interval, change gapFillState from gapFillInit to gapFillStart, and record the current row data as gapFillLastRow, and go to step 4; otherwise, update preTime, return the data to the upper operator, and return to step 2.

[0060] Step 24: Operate the gapFillStart state: If the data time column of the newly added row is preTime+N*interval, then each time a row is added, the data is immediately returned to the upper layer operator. When preTime+N*interval is greater than or equal to the time of gapFillLastRow in step 3, the row is added and the preTime value is updated. The state gapFillState is changed from gapFillStart to gapFillEnd, and the process goes to step 5.

[0061] Step 25: Operate on the gapFillEnd state: process the current row data gapFillLastRow recorded in step 3, return the processed data to the upper operator, change the state gapFillState from gapFillEnd to gapFillInit, and then return to step 2 until the data reading is completed.

[0062] As the information interaction and execution process between the modules of the above-mentioned device are based on the same concept as the embodiment of the method of the present invention, the specific contents can be found in the description of the embodiment of the method of the present invention and will not be repeated here.

[0063] Similarly, the device of the present invention can supplement the missing group data to make the data more complete. Using the aggregate calculation process, taking advantage of the characteristic that the input requires order, the row supplement operation is embedded in the orderedAggregator, reducing the implementation complexity.

[0064] It should be noted that not all steps and modules in the above-mentioned processes and device structures are necessary, and some steps or modules can be ignored according to actual needs. The execution order of each step is not fixed and can be adjusted as needed. The system structure described in the above-mentioned embodiments can be a physical structure or a logical structure, that is, some modules may be implemented by the same physical entity, or some modules may be implemented by multiple physical entities, or some components in multiple independent devices may be implemented together.

[0065] The above-described embodiments are only preferred embodiments for fully illustrating the present invention, and the protection scope of the present invention is not limited thereto. Equivalent substitutions or changes made by those skilled in the art based on the present invention are within the protection scope of the present invention. The protection scope of the present invention shall be subject to the claims.

Claims

1. A method for managing the use of a database time_bucket_gapfill function, characterized in that include: Step 1: Set three status values ​​of the time_bucket_gapfill function, using gapFillInit, gapFillStart and gapFillEnd to represent them respectively. Use gapFillInit to indicate that there is no interval that meets the filling condition yet. Use gapFillStart to indicate that there is an interval that meets the filling condition and the filling operation begins. Use gapFillEnd to indicate that the filling is finished. Use gapFillState to represent that the initial value of the current state is gapFillInit. Step 2: Use the time_bucket_gapfill function to fill in the time series data in the database: Step 21: Record the time of the first row of data as preTime, and return the first row of data to the upper operator. Step 22: Continue to read data and determine the current state of gapFillState. If the current state of gapFillState is gapFillInit, go to step 3. If the current state of gapFillState is gapFillStart, go to step 4. If the current state of gapFillState is gapFillEnd, go to step 5. Step 23: Operate the gapFillInit state: If the current data time is curTime-preTime>=interval interval, change gapFillState from gapFillInit to gapFillStart, and record the current row data as gapFillLastRow, and go to step 4; otherwise, update preTime, return the data to the upper operator, and return to step 2. Step 24: Operate the gapFillStart state: If the data time column of the newly added row is preTime+N*interval, then each time a row is added, the data is immediately returned to the upper layer operator. When preTime+N*interval is greater than or equal to the time of gapFillLastRow in step 3, the row is added and the preTime value is updated. The state gapFillState is changed from gapFillStart to gapFillEnd, and the process goes to step 5. Step 25: Operate on the gapFillEnd state: process the current row data gapFillLastRow recorded in step 3, return the processed data to the upper operator, change the state gapFillState from gapFillEnd to gapFillInit, and then return to step 2 until the data reading is completed.

2. The method for using and managing a database time_bucket_gapfill function according to claim 1, characterized in that Step 1 also includes: performing function preparation, in which SQL is converted into a structure that can be run by the database, and the time_bucket_gapfill function is parsed into FuncExpr.

3. The method for using and managing a database time_bucket_gapfill function according to claim 1, characterized in that Step 1 also includes: creating a GroupBy expression, loading the data of the time_bucket_gapfill function into GroupByExpr, and sorting the time column in ascending order before performing the groupBy operation.

4. The method for using and managing a database time_bucket_gapfill function according to claim 3, characterized in that For the data of the time_bucket_gapfill function in step 1, before performing the group by operation, round the time in each row of data to the interval interval to obtain an interval timestamp tb, and then sort tb in ascending order.

5. The method for using and managing a database time_bucket_gapfill function according to claim 1, characterized in that When performing row padding operation on the time series data of the database in step 2, the interpolate padding function and the time_bucket_gapfill function are used to generate corresponding column values ​​of the new row according to the padding method. The padding methods include using a constant value constant, using a previous value prev, using a next value next, using linear padding, and filling a null value null.

6. A use management device for a database time_bucket_gapfill function, characterized in that Including function management module and execution module, The function management module sets three status values ​​of the time_bucket_gapfill function, which are represented by gapFillInit, gapFillStart and gapFillEnd. gapFillInit indicates that there is no interval that meets the filling condition yet. gapFillStart indicates that there is an interval that meets the filling condition and the filling operation begins. gapFillEnd indicates that the filling is finished. gapFillState indicates that the initial value of the current state is gapFillInit. The execution module uses the time_bucket_gapfill function to fill in rows of the database's time series data: Step 21: Record the time of the first row of data as preTime, and return the first row of data to the upper operator. Step 22: Continue to read data and determine the current state of gapFillState. If the current state of gapFillState is gapFillInit, go to step 3. If the current state of gapFillState is gapFillStart, go to step 4. If the current state of gapFillState is gapFillEnd, go to step 5. Step 23: Operate the gapFillInit state: If the current data time is curTime-preTime>=interval interval, change gapFillState from gapFillInit to gapFillStart, and record the current row data as gapFillLastRow, and go to step 4; otherwise, update preTime, return the data to the upper operator, and return to step 2. Step 24: Operate the gapFillStart state: If the data time column of the newly added row is preTime+N*interval, then each time a row is added, the data is immediately returned to the upper layer operator. When preTime+N*interval is greater than or equal to the time of gapFillLastRow in step 3, the row is added and the preTime value is updated. The state gapFillState is changed from gapFillStart to gapFillEnd, and the process goes to step 5. Step 25: Operate on the gapFillEnd state: process the current row data gapFillLastRow recorded in step 3, return the processed data to the upper operator, change the state gapFillState from gapFillEnd to gapFillInit, and then return to step 2 until the data reading is completed.

7. The use management device of a database time_bucket_gapfill function according to claim 6, characterized in that the function The management module also performs function preparation, which converts SQL into a structure that the database can run and parses the time_bucket_gapfill function into FuncExpr.

8. The use management device of a database time_bucket_gapfill function according to claim 6, characterized in that the function The management module also creates a GroupBy expression, loads the data of the time_bucket_gapfill function into GroupByExpr, and sorts the time column in ascending order before performing the groupBy operation.

9. The use management device of a database time_bucket_gapfill function according to claim 8, characterized in that The function management module rounds the time in each row of data to an interval interval before performing the group by operation for the data of the time_bucket_gapfill function, and obtains an interval timestamp tb, and then sorts tb in ascending order.

10. The use management device of a database time_bucket_gapfill function according to claim 6, characterized in that When the execution module performs row padding operations on the time series data of the database, the interpolate padding function and the time_bucket_gapfill function are used to generate the corresponding column values ​​of the new row according to the padding method. The padding methods include using a constant value constant, using a previous value prev, using a next value next, using linear padding, and filling a null value null.

Images