Tearadata substitution for Lag and Lead function in Oracle.

Database
Enthusiast

Tearadata substitution for Lag and Lead function in Oracle.

Hi Experts,

Could you please help me in acheiving the below desired output? It is similiar to the Lag and Lead function in Oracle and i came across this question in many of the interviews.

 

Input:

Product id    Product_Price

1                    1000

2                    2000

3                    3000

4                     4000

5                5000

6                6000

 

Output:

Product_Id     Product_Price    Prev_Price     Next_Price

1                        1000                 NULL              2000

2                        2000                 1000               3000

3                        3000                 2000               4000

4                        4000                 3000               5000

5                        5000                 4000               6000

6                        6000                 5000               NULL

 

Please can you help in acheiving the above desired result.

 

Thanks

John

4 REPLIES 4
Highlighted
Ambassador

Re: Tearadata substitution for Lag and Lead function in Oracle.

Teradata 16.10 added support for LEAD/LAG.

 

Before you can rewrite the basic syntax (without the optional default & IGNORE NULLS) using:

 

LAG(colA, n)
OVER (PARTITION BY colB
      ORDER BY colC)
= 
MIN(colA)
OVER (PARTITION BY colB
      ORDER BY colC
      ROWS BETWEEN n PRECEDING AND n PRECEDING)

-- Change to FOLLOWING for LEAD


 

 

Teradata Employee

Re: Tearadata substitution for Lag and Lead function in Oracle.

-- edit : outrunned by dieter :)

 

Hi John,

 

Since 16.10, LEAD and LAG functions are native in Teradata :

https://docs.teradata.com/reader/1Ms8rWHdBhcwr0PzwAWcDw/p0kMR35NRVlfHAFD4yhYwg

https://docs.teradata.com/reader/WX0vkeB8F3JQXZ0HTR~d0Q/r4fu0cjqJ7HLPYxsuqbftA

 

Before, you have to use as window rows between clause using any eligible function, like min/max :

create multiset volatile table mvt_lead_lag, no log
( mvt_pi        byteint
, product_id    byteint
, product_price integer
)
primary index (mvt_pi)
on commit preserve rows
;

insert into mvt_lead_lag values (0, 1, 1000);
insert into mvt_lead_lag values (0, 2, 2000);
insert into mvt_lead_lag values (0, 3, 3000);
insert into mvt_lead_lag values (0, 4, 4000);
insert into mvt_lead_lag values (0, 5, 5000);
insert into mvt_lead_lag values (0, 6, 6000);

select product_id
     , product_price
     , min(product_price) over(order by product_id asc rows between 1 preceding and 1 preceding) as lag_like
     , min(product_price) over(order by product_id asc rows between 1 following and 1 following) as lead_like
  from mvt_lead_lag
 where mvt_pi = 0;

 product_id product_price lag_like lead_like 
 ---------- ------------- -------- --------- 
          1          1000               2000
          2          2000     1000      3000
          3          3000     2000      4000
          4          4000     3000      5000
          5          5000     4000      6000
          6          6000     5000          

 

Enthusiast

Re: Tearadata substitution for Lag and Lead function in Oracle.

Thank you so much Dieter:). It worked.

Enthusiast

Re: Tearadata substitution for Lag and Lead function in Oracle.

Fantastic. It worked. Thank you so much Waldar:)

So the syntax remains the same, even if i substitute the Product_Price field value with some variables say like x, y and z. Correct? Like below answer set.

 

product_id product_price lag_like lead_like 
 ---------- ------------- -------- --------- 
          1          A            B
          2          B     A      C
          3          C     B      D
          4          D     C      E
          5          E     D      F
          6          F     E