ROWS BETWEEN in Window 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.

If you prefer watching video, check our video below:

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:

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:

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:

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:

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:

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:

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:

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:

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.