Skip to content
Reference > Prepare Methods

movingAvg(value,sampleSize,options)

Definition

Number movingAvg(Number value, Integer sampleSize, String options)

Description

moving average of nearest sampleSize records

Parameter Definition

Name Type Description
value Number data to calculate avg from
sampleSize Integer number of records to consider
options String Either MID,BEFORE,AFTER (null defaults to MID)

Example 1

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
CREATE TABLE input(symbol String, id int, price double);
INSERT INTO input VALUES ("CAT", 1, 50),("CAT", 2, 54),("CAT", 3, 52),("CAT", 4, 52),("CAT", 5, null),("CAT", 6, 57);
INSERT INTO input VALUES ("IBM", 1, 20),("IBM", 2, 24),("IBM", 3, 22),("IBM", 4, 27),("IBM", 5, null);
CREATE TABLE result AS PREPARE *,movingAvg(price,3,"MID") AS movAvg3Mid, movingAvg(price,2,"BEFORE") AS movAvg2Before FROM input PARTITION BY symbol;
Table input = SELECT * FROM input;
TABLE result = SELECT * FROM result;

// input = 
// +---------------------+
// |        input        |
// +------+-------+------+
// |symbol|id     |price |
// |String|Integer|Double|
// +------+-------+------+
// |CAT   |1      |50.0  |
// |CAT   |2      |54.0  |
// |CAT   |3      |52.0  |
// |CAT   |4      |52.0  |
// |CAT   |5      !null  |
// |CAT   |6      |57.0  |
// |IBM   |1      |20.0  |
// |IBM   |2      |24.0  |
// |IBM   |3      |22.0  |
// |IBM   |4      |27.0  |
// |IBM   |5      !null  |
// +------+-------+------+
// 
// result = 
// +------------------------------------------------------+
// |                        result                        |
// +------+-------+------+------------------+-------------+
// |symbol|id     |price |movAvg3Mid        |movAvg2Before|
// |String|Integer|Double|Double            |Double       |
// +------+-------+------+------------------+-------------+
// |CAT   |1      |50.0  |52.0              |50.0         |
// |CAT   |2      |54.0  |52.0              |52.0         |
// |CAT   |3      |52.0  |52.666666666666664|53.0         |
// |CAT   |4      |52.0  |52.0              |52.0         |
// |CAT   |5      !null  |54.5              |52.0         |
// |CAT   |6      |57.0  |57.0              |57.0         |
// |IBM   |1      |20.0  |22.0              |20.0         |
// |IBM   |2      |24.0  |22.0              |22.0         |
// |IBM   |3      |22.0  |24.333333333333332|23.0         |
// |IBM   |4      |27.0  |24.5              |24.5         |
// |IBM   |5      !null  |27.0              |27.0         |
// +------+-------+------+------------------+-------------+
//