Skip to content
Reference > Prepare Methods

offset(value,offset)

Definition

Object offset(Object value, Integer offset)

Description

Will grab the value offset from the current row. If offset==0 use current row, negative number is nth prior row, positive number is nth future row. Evaluates to null if offset is out of bounds for current table. Ex: offset(price,-1) for first row returns null, for second row returns first Row's price and for nth row returns (n-1)ths row's price

Parameter Definition

Name Type Description
value Object
offset Integer

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 *,offset(price,-1) AS newCol 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 |newCol|
// |String|Integer|Double|Double|
// +------+-------+------+------+
// |CAT   |1      |50.0  !null  |
// |CAT   |2      |54.0  |50.0  |
// |CAT   |3      |52.0  |54.0  |
// |CAT   |4      |52.0  |52.0  |
// |CAT   |5      !null  |52.0  |
// |CAT   |6      |57.0  !null  |
// |IBM   |1      |20.0  !null  |
// |IBM   |2      |24.0  |20.0  |
// |IBM   |3      |22.0  |24.0  |
// |IBM   |4      |27.0  |22.0  |
// |IBM   |5      !null  |27.0  |
// +------+-------+------+------+
//