Getting Previous Row Data in Teradata using OLAP Teradata Function

Getting Previous Row Data in Teradata using OLAP Teradata Function

"ROWS UNBOUNDED PRECEDING" is used in an ordered analytical function to tell it to include all of the preceding rows in the partition in the calculation being performed.

 “ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING” is used in an ordered analytical function to tell it to include only preceding row in the partition in the calculation being performed.

“ROWS BETWEEN 1 FOLLOWINGAND 1 FOLLOWING” is used in an ordered analytical function to tell it to include only following row in the partition in the calculation being performed.

Then Following example illustrates OLAP Functions Usage:

CREATE MULTISET TABLE TEMP1 (COL1 INT, COL2 INT);

INSERT INTO TEMP1 VALUES(100 , 2000);

INSERT INTO TEMP1 VALUES(100 , 3000);

INSERT INTO TEMP1 VALUES(100 , 4000);

INSERT INTO TEMP1 VALUES(300 , 5000);

INSERT INTO TEMP1 VALUES(300 , 6000);

INSERT INTO TEMP1 VALUES(300 , 7000);

SELECT  COL1

,COL2

,COALESCE(MIN(col2) OVER (PARTITION BY col1  ORDER BY col2 ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING), 0) A1

,COALESCE(MAX(col2) OVER (PARTITION BY col1  ORDER BY col2 ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING), 0) A2

,COALESCE(MIN(col2) OVER (PARTITION BY col1  ORDER BY col2 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 0) A3

,COALESCE(MAX(col2) OVER (PARTITION BY col1  ORDER BY col2 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 0) A4

,COALESCE(SUM(col2) OVER (PARTITION BY col1  ORDER BY col2 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 0) A5

,COALESCE(AVG(col2) OVER (PARTITION BY col1  ORDER BY col2 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 0) A6

,COALESCE(COUNT(col2) OVER (PARTITION BY col1  ORDER BY col2 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 0) A7

FROM TEMP1;