Everything you need to know about ROWS BETWEEN in Teradata

ROWS BETWEEN is used in Teradata to determine group for calculating aggregation. Whenever you use window functions or OLAP functions you specify the rows which shall be considered for calculating measures. If you don’t specify anything then by default all the rows in the partition participate in calculating measures. Some of the common window functions used are MIN, MAX, SUM, AVG, COUNT. As we specify range in usual BETWEEN clause similarly we specify range in ROWS BETWEEN in OLAP functions. RANGE must always be from start to end i.e. Start must be before End.
Some common specifications are:
UNBOUNDED PRECEDING: All rows before current row are considered.
UNBOUNDED FOLLOWING: All rows after the current row are considered.
CURRENT ROW: Range starts or ends at CURRENT ROW.

You can also specify NUMERICAL values in place of “UNBOUNDED” keyword in above mentioned specifications like 1 PRECEDING and 2 FOLLOWING. Default value is UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING i.e. all rows are considered.

Let’s quickly look at some examples to see how you can specify different window specification via “ROWS BETWEEN”.

Create a Table with dummy data.

create multiset volatile table vt_rows_between
(
sid int
)
on commit preserve rows;
insert into vt_rows_between select 1;
insert into vt_rows_between select 2;
insert into vt_rows_between select 3;
insert into vt_rows_between select 4;
insert into vt_rows_between select 5;
insert into vt_rows_between select 6;
insert into vt_rows_between select 7;
insert into vt_rows_between select 8;
insert into vt_rows_between select 9;
insert into vt_rows_between select 10;

So we have numbers from 1-10 in the table. Let’s use some OLAP Functions to understand ROWS BETWEEN usage.

Case1: To consider only previous row value we can specify 1 preceding and 1 preceding. This is how you can access previous row value in the current row.

select sid,sum(sid) over(order by sid rows between 1 preceding and 1 preceding) as sid2 from vt_rows_between;  
sidsid2
1NULL
21
32
43
54
65
76
87
98
109

Case2: To consider all the previous rows we can specify UNBOUNDED preceding and 1 preceding.

select sid,sum(sid) over(order by sid rows between UNBOUNDED preceding and 1 preceding) as sid2 from vt_rows_between;
sidsid2
1NULL
21
33
46
510
615
721
828
936
1045

Case3: To consider previous 2 rows only we can specify 2 preceding and 1 preceding

select sid,sum(sid) over(order by sid rows between 2 preceding and 1 preceding) as sid2 from vt_rows_between;
sidsid2
1NULL
21
33
45
57
69
711
813
915
1017

Case4: To consider the next row only we can specify 1 following and 1 following

select sid,sum(sid) over(order by sid rows between 1 following and 1 following) as sid2 from vt_rows_between;
sidsid2
12
23
34
45
56
67
78
89
910
10NULL

Case5: To consider all the following rows we can specify 1 following and unbounded preceding

select sid,sum(sid) over(order by sid rows between 1 following and unbounded following) as sid2 from vt_rows_between;
sidsid2
154
252
349
445
540
634
727
819
910
10NULL

Case6: To consider next 2 rows only we can specify 2 preceding and 1 preceding

select sid,sum(sid) over(order by sid rows between 1 following and 2 following) as sid2 from vt_rows_between;
sidsid2
15
27
39
411
513
615
717
819
910
10NULL

Case7: If you want to include current row as well in the calculation then you can use current row and say 2 following

select sid,sum(sid) over(order by sid rows between current row and 2 following) as sid2 from vt_rows_between;
sidsid2
16
29
312
415
518
621
724
827
919
1010

Case8: If you want to calculate sum of all rows till current row then you can use UNBOUNDED preceding and current row

select sid,sum(sid) over(order by sid rows between UNBOUNDED preceding and current row) as sid2 from vt_rows_between;
sidsid2
11
23
36
410
515
621
728
836
945
1055

You can observe that it results in NULL value if window specified is more than actual data present in the table.
If you have any comments or confusion , feel free to leave a comment.

2 Comments

  1. Correction suggestion for caption.

    To

    The post is precise.

  2. Case6: To consider next 2 rows only we can specify 2 preceding and 1 preceding

Leave a Reply

Your email address will not be published. Required fields are marked *