Data pre-processing functions

This function is available only in the China (Shanghai) region.

The moving average function supports a maximum data volume of 50 million rows.

This is a moving average function for offline scheduling.

Filters an input time series curve to identify its shape. Raw data collected from Internet of Things (IoT) devices often contains various types of noise. The moving average function uses information from adjacent data points to smooth the data curve, which simplifies data analytics.

num: Required. The data type is double. This parameter specifies the numeric column.

windowSize: Required. The data type is int. This parameter specifies the window size. The value ranges from 1 to 20.

Use the moving average function.

This example selects values from the `num`, `b`, `c`, and `d` columns in the `Values` table and calculates the moving average of the num column with a window size of 2. The Values table contains the rows (1,1,1,1), (2,2,2,2), and (3,3,3,3).

SELECT MOVING_AVERAGE(num,2),b,c,d FROM Values (1,1,1,1),(2,2,2,2),(3,3,3,3) t(num,b,c,d);

The data in the num column is 1, 2, and 3. The first sliding window contains only the value 1, which results in an output average value of 1. The window then slides forward to contain the values 1 and 2, so the output is (1 + 2) / 2 = 1.5. The window slides again to contain the values 2 and 3, so the output is (2 + 3) / 2 = 2.5.

Specify the calculation order for the moving average.

To specify the calculation order for the moving average, you can use the `ORDER BY` clause with an external column. In this example, the initial rows in the `Values` table are `(3,3)`, (1,1), and (2,2). The ORDER BY userTimestamp clause in the subquery sorts the rows of the `Values` table. After sorting, the rows are (1,1), (2,2), and (3,3). The MOVING_AVERAGE function is then used to calculate the average of the first column, num.

SELECT MOVING_AVERAGE(num,2),userTimestamp FROM 
(SELECT * FROM Values (3,3),(1,1),(2,2) t(num,userTimestamp) ORDER BY userTimestamp);

This function is available only in the China (Shanghai) region.

The weighted average function supports a maximum data volume of 50 million rows.

This is a weighted average function for offline scheduling.

Filters an input time series curve to identify its shape. Raw data collected from IoT devices often contains various types of noise. The weighted average function uses information from adjacent data points and assigns different weights to them to smooth the data curve, which simplifies data analytics.

num: Required. The data type is double. This parameter specifies the numeric column.

weight: Required. The data type is double. This parameter specifies the weight column.

windowSize: Required. The data type is int. This parameter specifies the window size. The value ranges from 1 to 20.

Use the weighted average function.

You can select values from the num, weight, c, and d columns in the Values table and calculate the weighted average of the num column over a window of 2. The Values table contains (1,1,1,1), (2,2,2,2), and (3,3,3,3).

SELECT WEIGHTED_AVERAGE(num,weight,2),c,d  FROM Values (1,1,1,1),(2,2,2,2),(3,3,3,3) t(num,weight,c,d);

The values in the num column are 1, 2, and 3, with corresponding weights of 1, 2, and 3 in the weight column. The first sliding window contains only the value 1, so the average value is (1 × 1) / 1 = 1. When the window slides forward, it contains the values 1 and 2, and the average value is (1 × 1 + 2 × 2) / (1 + 2) = 5/3. When the window slides forward again, it contains the values 2 and 3, and the average value is (2 × 2 + 3 × 3) / (2 + 3) = 2.6.

Specify the calculation order for the weighted average.

To specify the calculation order for the weighted average, you can use the `ORDER BY` clause with an external column. In this example, the initial rows in the `Values` table are `(3,3,3)`, (1,1,1), and (2,2,2). The ORDER BY userTimestamp clause in the subquery sorts the rows of the `Values` table. After sorting, the rows are (1,1,1), (2,2,2), and (3,3,3). The WEIGHTED_AVERAGE function is then used to calculate the weighted average of the first column, num.

SELECT WEIGHTED_AVERAGE(num,weight,2),userTimestamp FROM 
(SELECT * FROM Values (3,3,3),(1,1,1),(2,2,2) t(num,weight,userTimestamp) ORDER BY userTimestamp);