2

I am new to SQL and I want to know what exactly the function ( rows between 1 preceding and 1 preceding )do in teradata ,I want a simple clarification please ,I am trying to use this function as a testcase to get the time gaps in history table between start and end date,can anyone help please or provide any useful links.

SELECT DISTINCT CUST_ID
FROM
(
SELECT 
CUST_ID,
STRT_dt - 
MIN(END_dt) OVER (PARTITION BY CUST_ID ORDER BY END_dt
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS diff
FROM table
QUALIFY diff > 1
) dt
Amr Hassan
  • 21
  • 1
  • 1
  • 7

2 Answers2

6

This returns the same result as Standard SQL's LAG(END_dt) OVER (PARTITION BY CUST_ID ORDER BY END_dt, i.e. the previous row's END_dt (or NULL for the 1st row per CUST_ID).

When you switch to FOLLOWING instead of PRECEDING it's the next row, LEAD in Standard SQL.

Both LAG and LEAD are finally implemented in TD16.10.

As you simply want to find gaps and you don't access the actual difference you can also simplify it to:

SELECT DISTINCT CUST_ID
FROM table
QUALIFY
   STRT_dt - 
   MIN(END_dt)
   OVER (PARTITION BY CUST_ID
         ORDER BY END_dt
         ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) > 1
dnoeth
  • 59,503
  • 4
  • 39
  • 56
1

Let's say we are dealing with the query ' rows between (A) preceding and (B) preceding '. In your case A=1 and B=1. So here (A) behaves as we expect, that is it specifies the number of rows that are to be included along with the current row to perform the aggregate function that we have mentioned. Now coming to (B), it specifies the number of rows (from below) that are to be excluded from included rows while performing aggregate function that we specify.

For better understanding, consider A=3 and B=1 and currently we are performing aggregate function on row number 7. Then as A=3 we need to consider 4,5,6,7 rows to perform aggregate function. Now B=1 says that 1 recent row should be excluded from selected rows i.e, 4,5,6,7 while performing mentioned aggregate function. In our case(4,5,6,7) row number 7 should be excluded. And hence we'll perform aggregate function on remaining rows i.e, 4,5,6 rows.

Hope you understood :)

sai charan teratipally
  • 13
  • 3