Search code examples
sqlteradatalag

Teradata - Lag column and UNBOUNDED PRECEDING

suppose I have the following table in teradata sql. How do I get the 'final' column.

The first value is equal to the 'mount' column, the second is equal to final - price (10-1), and the third is equal (9-2).

hour    mount   price
0       10       1
1       10       2
2       10       3 

hour    mount   price   final
0        10      1       10
1        10      2       9
2        10      3       7
Solution

  • You seem to want a cumulative sum -- and then the difference from mount:

    select hour, mount price,
       (mount + price -
        sum(price) over (order by hour rows between unbounded preceding and current row)
       ) as final
from t;

    You really want the sum up to the preceding row. But if you use:

            sum(price) over (order by hour rows between unbounded preceding and 1 preceding)

    then you will need to deal with NULL values. Instead, I just add price in from the current row and then let the cumulative sum include that price.