ROWS BETWEEN in Analytical Function in Teradata

ROWS BETWEEN is used in Teradata to determine group for calculating aggregation in Analytical Functions. 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:

  1. UNBOUNDED PRECEDING: All rows before current row are considered.
  2. UNBOUNDED FOLLOWING: All rows after the current row are considered.
  3. 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;

Load some dummy data into the table.

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 read previous row value

In many cases, we may want to read value from previous row only then we can specify 1 preceding and 1 preceding. This is how we 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;

Output:

sidsid2
1NULL
21
32
43
54
65
76
87
98
109

Case2: To read all the previous rows

If we want to consider all the previous row in analytical function then we can specify UNBOUNDED preceding and 1 preceding. In this example we will do sum of all the previous rows only excluding current row.

select
	sid,
	sum(sid) over(
	order by sid rows between UNBOUNDED preceding and 1 preceding) as sid2
from
	vt_rows_between;

Output:

sidsid2
1NULL
21
33
46
510
615
721
828
936
1045

Case3: To read previous 2 rows only

If we want to read only specific precious rows then we can mention the number to read limited rows in place of all the previous rows. In this example we can specify 2 preceding and 1 preceding to read only previous 2 rows.

select
	sid,
	sum(sid) over(
	order by sid rows between 2 preceding and 1 preceding) as sid2
from
	vt_rows_between;

Output:

sidsid2
1NULL
21
33
45
57
69
711
813
915
1017

Case4: To read the next row only

In some cases, we may want to do some computation depending on the next row value in such case 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;

Output:

sidsid2
12
23
34
45
56
67
78
89
910
10NULL

Case5: To read all the following rows

If we want to read all the following rows or next rows then 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;

Output:

sidsid2
154
252
349
445
540
634
727
819
910
10NULL

Case6: To read next 2 rows only

In some case, we may want to read the next few rows only and not all during computation. In such cases , we can specify numbers to read only limit next rows and not all. In this example we will use 1 following and 2 following

select
	sid,
	sum(sid) over(
	order by sid rows between 1 following and 2 following) as sid2
from
	vt_rows_between;

Output:

sidsid2
15
27
39
411
513
615
717
819
910
10NULL

Case7: If you want to include current row

In some cases, we may want to include current row as well in the calculation then we can specify current row and then following/preceding as per requirement. In this example we will use current row and 2 following

select
	sid,
	sum(sid) over(
	order by sid rows between current row and 2 following) as sid2
from
	vt_rows_between;

Output:

sidsid2
16
29
312
415
518
621
724
827
919
1010

Case8: Calculate sum of all rows till current row

If we want to consider all the previous till current row then we 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;

Output:

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.

3 thoughts on “ROWS BETWEEN in Analytical Function in Teradata

Leave a Reply

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