How to implement lag function in teradata.

Question

Input :

Output :

I want the output as shown in the image below.

In the output image, 4 in 'behind' is evaluated as tot_cnt-tot and the subsequent numbers in 'behind', for eg: 2 is evaluated as lag(behind)-tot & as long as the 'rank' remains same, even 'behind' should remain same.

Can anyone please help me implement this in teradata?

@trincot i will try to check that later, but first i need to use that lag function in this .. — Swapnil Sudhir, Jul 26 '18 at 13:01
Maybe I will wait for you to clarify this first before spending time on it :) — trincot, Jul 26 '18 at 13:02
@trincot the tot_cnt is actually the total number of observations — Swapnil Sudhir, Jul 26 '18 at 13:05
LAG implements access to the previous row's data, but I don't see how that matches your expected result. — dnoeth, Jul 26 '18 at 13:18
https://stackoverflow.com/questions/8124756/teradata-equivalent-for-lead-and-lag-function-of-oracle#8130515 - Also Teradata 16.x implements a proper LEAD and LAG function for ANSI compliance. — Rob Paller, Jul 26 '18 at 16:27

score 1 · Answer 1 · answered Jul 26 '18 at 13:04

1

You appears to want :

select *, (select count(*) 
           from table t1 
           where t1.rank > t.rank
          ) as behind
from table t;

answered Jul 26 '18 at 13:04

Yogesh Sharma

49,870
5
26
52

score 0 · Answer 2 · answered Jul 26 '18 at 13:45

I would summarize the data and do:

select id, max(tot_cnt), max(tot),
       (max(tot_cnt) -
        sum(max(tot)) over (order by id rows between unbounded preceding and current row)
       ) as diff
from t
group by id;

This provides one row per id, which makes a lot more sense to me. If you want the original data rows (which are all duplicates anyway), you can join this back to your table.

How to implement lag function in teradata.

2 Answers2