A database missing data interpolation method and device

Abstract

This invention relates to the field of database query statement design, specifically providing a method and apparatus for interpolating and supplementing missing data in a database. The method includes a user, an internet-connected device, an interactive platform, a database index processor, and a storage engine. The data index processor performs data supplementation on time-series data without storing the results. The database storage engine performs customized data supplementation queries based on the user's query requirements. Compared to existing technologies, this invention can effectively supplement missing data in massive amounts of data from the Internet of Things (IoT) according to data type and interpolation algorithms, providing personalized data supplementation services that adapt to the characteristics of various data streams. It can provide specific and efficient data supplementation services based on the characteristics of time-series data, targeting data with different lifecycles and values.

Description

Technical Field

[0001] This invention relates to the field of database query statement design, and specifically provides a method and apparatus for interpolating and supplementing missing data in a database. Background Technology

[0002] A typical IoT application usually needs to process massive streaming data generated by various devices and sensors, such as battery management systems, photovoltaic panels, automotive sensors, smart homes, industrial sensors, and meteorological data. These devices and sensors are characterized by diversity, large scale, large data volume, time sequence, distribution, and complex computing environments. Therefore, it is necessary to design a stable and secure database for time-series data.

[0003] First, the scale of device and sensor data in the Internet of Things (IoT) environment is enormous and widely distributed, spanning different corners of the globe and various fields. During data collection, data loss due to equipment malfunctions, power outages, or recording errors is not uncommon. If lost data is not addressed, it will introduce uncertainty into subsequent data analysis, leading to inaccurate results and incorrect judgments. Therefore, we need a data compensation method to reduce the impact of missing data on data analysis and processing.

[0004] Secondly, the diverse and massive data streams generated by various devices possess different data characteristics, structures, accuracy requirements, and user needs. To adapt to this data diversity, database applications need to provide diverse and customized interpolation and compensation functions for users to choose from, achieving the most accurate completion of missing data. For example, meteorological data is mostly continuously changing values, but industrial sensors on workpieces may exhibit discontinuously changing data characteristics. IoT computing models need to be able to adapt to various data stream characteristics and provide personalized computing services to meet the diverse computing needs of data streams.

[0005] Third, time-series data in the IoT environment is time-sensitive; data gaps can fluctuate, and the frequency, accuracy, and interpretation of the data required by users will all change. For example, automotive sensors continuously output data recordings in seconds when the car is started, but no data is output when the car is not moving. The time-sensitivity of data in the IoT environment requires IoT applications to emphasize the required time range while performing interpolation, in order to maximize performance and resources while meeting user needs.

[0006] In summary, the data generated in the complex environment of the Internet of Things (IoT) is massive, diverse, and unstable. In IoT applications, we need to supplement missing data to reduce and avoid its impact on data analysis. Because of the diversity and temporal nature of the data, we need to design customized interpolation algorithms to implement specific interpolation methods for specific time periods. Only by accurately supplementing missing data can users use relatively complete data information for accurate calculations, analysis, and processing, thus achieving comprehensive, automated, and intelligent services. Summary of the Invention

[0007] This invention addresses the shortcomings of the prior art by providing a practical method and apparatus for interpolating and supplementing missing data in a database.

[0008] A further technical objective of this invention is to provide a reasonably designed, safe, and applicable database missing data interpolation and compensation device.

[0009] The technical solution adopted by this invention to solve its technical problem is:

[0010] A method for interpolating and supplementing missing data in a database includes a user, an internet-connected device, an interactive platform, a database index processor, and a storage engine. The time-series data is supplemented within the data index processor without being stored. The database storage engine performs customized supplementation queries based on the user's query requirements.

[0011] Furthermore, for the complement value, interpolation complement functions are used, namely gapfill and interpolate. Among them, gapfill is used to detect the value of the key data column and process it.

[0012] Furthermore, the database defines and judges the missing row conditions based on the sequence number or other types of rows, and supplements null values ​​for other data used by the user. At the same time, it considers the time range selected by the user and only supplements values ​​for data within that range, but does not perform calculations on the values. It only judges the missing rows and supplements null values ​​during the data indexing process.

[0013] Furthermore, the `interpolate` method is used to supplement missing data based on the target data with specified complement values ​​and the corresponding row complement algorithm. The complement algorithm is implemented in the database expression.

[0014] Furthermore, the processing of timestamps and the function of filling in missing data points are separated. Users only need to use gapfill once to process the timestamps and fill in the missing data points.

[0015] The interpolate algorithm is used multiple times, each time for a different data value, and the complementation algorithm is selected based on the characteristics of the data value.

[0016] Furthermore, during the database indexing process, the data is first aggregated based on the key columns. Each data row after aggregation is read, and then the relevant variable values ​​of the key data columns and the complement data columns are updated.

[0017] Furthermore, if no new row is read, return and update the corresponding execution status, indicating completion or failure; after a new row is read, determine whether the data point is within the start and end time window;

[0018] If the function is in the window, it will check for missing rows based on the key rows in the gapfill data row. If a missing row is found, the function will enter the padding stage; otherwise, it will directly return the original data row.

[0019] Furthermore, after entering the padding stage, the padding value is calculated for each interpolate function and the padding value is returned. After completion, the first step of reading a new row of data from the data table is returned, thus completing a complete interpolation padding function operation cycle.

[0020] Compared with the prior art, the database missing data interpolation and compensation method and apparatus of the present invention have the following outstanding advantages:

[0021] This invention can effectively fill in missing data in massive amounts of data in the Internet of Things according to data type and interpolation algorithm, and can provide personalized data filling services that can adapt to the characteristics of various data streams.

[0022] Based on the characteristics of time series data, specific and efficient value compensation services can be provided for time series data with different lifecycles and values. Attached Figure Description

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

[0024] Appendix Figure 1 This is a flowchart illustrating a method for interpolating and supplementing missing data in a database.

[0025] Appendix Figure 2 This is a schematic diagram of the architecture of a database missing data interpolation and compensation method. Detailed Implementation

[0026] To enable those skilled in the art to better understand the present invention, the present invention will be further described in detail below with reference to specific embodiments. Obviously, the described embodiments are merely some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0027] The following is a preferred embodiment:

[0028] like Figure 1-2 As shown in this embodiment, a method for interpolating and supplementing missing data in a database is presented. This method includes a user, an internet-connected device, an interactive platform, a database index processor, and a storage engine. The time-series data is supplemented internally by the data index processor, but the supplemented results are not stored. The database storage engine performs customized supplementation queries based on the user's query requirements. This approach allows for data supplementation to be performed simultaneously with data table queries, preserving data integrity while optimizing performance.

[0029] To implement the function of filling in missing rows and values ​​based on timestamps, two function statements were designed: gapfill and interpolate. Among them, gapfill is used to detect the values ​​of key data columns and process them.

[0030] For example, timestamp column values ​​can be compared, merged, and adjusted, and a judgment can be made based on their relative values ​​and the conditions for adding rows. If rows need to be added, the added rows are returned; otherwise, the original data is returned. Similarly, the database can define and judge missing row conditions based on sequence numbers or other types of rows, and add null values ​​to other data used by the user. This method also considers the time range selected by the user, adding values ​​only to data within that range. However, this method does not perform operations on the values; it only judges for missing rows and adds null values ​​during the data indexing process.

[0031] `interpolate` is used to fill in missing data by specifying the target data for the filler value and the corresponding row filler algorithm. The filler algorithm is implemented in database expressions, such as using `SELECT` statements, `RegEX` expressions, and simple linear expressions.

[0032] Separating timestamp processing and interpolation functionality facilitates feature segmentation and performance improvements. Users only need to use gapfill once to process timestamps and add data points; they can use interpolate multiple times, each time targeting a different data value and selecting the interpolation algorithm based on the characteristics of that data value.

[0033] The following example can be used as a reference for implementing the design:

[0034] gapfill(gapfill_expression,interval,start,end);

[0035] gapfill_expression - Key data column, the basis for determining whether to fill in a row, such as timestamp or sequence number;

[0036] interval – determines the size of the gap between missing lines. If the interval is greater than this value, it is considered a missing line; otherwise, it is considered a non-missing line.

[0037] Start – Start time;

[0038] End – Termination time;

[0039] Interpolate(expression1,expression2);

[0040] Expression 1 – The target data column that needs to be padded;

[0041] Expression 2 – Methods for complementation, such as linear complementation, complementation using the previous data point, or user-specified function expressions, etc.

[0042] During database indexing, the data is first aggregated based on the key columns. We read each aggregated data row and then update the relevant variable values ​​of the key and complement columns, such as the value of the previous data point required in the linear function, or the size of the interval. If no new rows can be read, we return and update the corresponding execution status (complete or failed). After reading a new row, we determine whether the data point is within the start and end time window, as we only perform complementation on data points within the window. If it is within the window, we check for missing rows based on the key rows in the gapfill data rows. If a missing row is detected, the function enters the complementation phase; otherwise, it directly returns the original data row. In the complementation phase, we perform complementation calculations on each interpolate function and return the complemented row. After completion, we return to the first step of reading a new row from the data table. This completes one full cycle of the interpolation complementation function.

[0043] The specific embodiments described above are merely specific examples of the present invention. The patent protection scope of the present invention includes, but is not limited to, the specific embodiments described above. Any appropriate changes or substitutions made by a person skilled in the art that conform to the claims of the present invention regarding a database missing data interpolation and compensation method and apparatus should fall within the patent protection scope of the present invention.

[0044] Although embodiments of the invention have been shown and described, it will be understood by those skilled in the art that various changes, modifications, substitutions and alterations can be made to these embodiments without departing from the principles and spirit of the invention, the scope of which is defined by the appended claims and their equivalents.

Claims

1. A method for interpolating and supplementing missing data in a database, characterized in that, This includes users, devices on the Internet, interactive platforms, database index processors, and storage engines. The data index processor performs imputation on time-series data but does not store the imputation results. The database storage engine performs customized imputation queries based on the user's query requirements. For complementation, interpolation complementation functions are used, namely gapfill and interpolate. Among them, gapfill is used to detect and process the values ​​of key data columns. The database defines and judges the missing row conditions based on the sequence number or other types of rows. It fills in null values ​​for other data used by the user, and considers the time range selected by the user. It only fills in values ​​for data within the range, but does not perform calculations on the values. It only judges the missing rows and fills in null values ​​during the data indexing process. The processing of timestamps and the function of filling in missing values ​​are separated. Users only need to use gapfill once to process timestamps and fill in data points. The interpolate algorithm is used multiple times, each time for a different data value, and the complementation algorithm is selected based on the characteristics of the data value.

2. The database missing data interpolation and compensation method according to claim 1, characterized in that, The interpolate function is used to specify the target data for the interpolation value and the corresponding row interpolation algorithm. For the supplementation of lost data, the interpolation algorithm is implemented in the database expression.

3. The database missing data interpolation and compensation method according to claim 2, characterized in that, During the database indexing process, the data is first aggregated based on the key columns. Each aggregated data row is read, and then the relevant variable values ​​of the key data columns and the complement data columns are updated.

4. The database missing data interpolation and compensation method according to claim 3, characterized in that, If no new row is read, return and update the corresponding execution status, either completed or failed; after reading a new row, determine whether the data point is within the start and end time window; If the function is in the window, it will check for missing rows based on the key rows in the gapfill data row. If a missing row is found, the function will enter the padding stage; otherwise, it will directly return the original data row.

5. The database missing data interpolation and compensation method according to claim 4, characterized in that, After entering the padding stage, the interpolate function performs padding calculations on each interpolate function and returns the padding. After completion, it returns to the first step of reading a new row of data from the data table, thus completing a full cycle of the interpolation padding function.

Images