Want to calculate Lag value in Teradata.

General
Enthusiast

Want to calculate Lag value in Teradata.

A B C D E F

Q1 2000 1.2 11.8 4.0 14296.2 101.6 

Q2 2000 7.8 6.1 3.9 13618.5 104.5 

Q3 2000 0.5 7.4 4.0 13613.3 106.9 

Q4 2000 2.1 3.6 3.9 12175.9 109.5 

I want to calculate the Lag value of the Variable(column) B,C,D... in Teradata

The lag value of Q1 for each variable(B,C,D..F) should be 0

The Lag value for Q2 :   lag1Q_B = 1.2 ,lag1Q_C = 11.8  and so on.

The lag value for Q3 :   lag1Q_B= 7.8 , lag2Q_B =1.2   ,  lag1Q_C=6.1 ,lag2Q_C=11.8

The equivalent SAS code for time series calculation is :

convert B    = lag1Q_B     / transformout = (lag 1);

convert C    = lag1Q_C     / transformout = (lag 1);

convert B    = lag2Q_B     / transformout = (lag 2);

convert C    = lag2Q_C     / transformout = (lag 2);

convert B    = lag3Q_B     / transformout = (lag 3);

convert C    = lag3Q_C     / transformout = (lag 3);

Please provide the equivalent SQL in Teradata.

2 REPLIES
Teradata Employee

Re: Want to calculate Lag value in Teradata.

Use a "window aggregate function" with ORDER BY and ROWS clauses (and optionally PARTITION BY). Unlike SAS, there is no intrinsic order of rows; you must explicitly say how the rows are to be logically ordered.

SELECT COALESCE(MIN(B) OVER (ORDER BY SUBSTRING(A FROM 3 FOR 4), SUBSTRING(A FROM 2 FOR 1)

   ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING),0.0) AS LAG1Q_B,

etc.

Note that MIN is only to make syntax acceptable; MAX or SUM would also work since we are limiting the "window" to a single row. When there is no row, the function returns NULL and COALESCE supplies a default.

Enthusiast

Re: Want to calculate Lag value in Teradata.

Hi Fred,

Thanks for quick reply , I got the lag values for the variable by this --

 

Sum(B) OVER (ORDER BY A ASC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS lag1Q_B

Sum(B) OVER (ORDER BY A ASC ROWS BETWEEN 2 PRECEDING AND 2 PRECEDING) AS lag2Q_B

 

but please let me know how it exactly works.