What Is Rows Unbounded Preceding Used for in Teradata

What is ROWS UNBOUNDED PRECEDING used for in Teradata?

It's the "frame" or "range" clause of window functions, which are part of the SQL standard and implemented in many databases, including Teradata.

A simple example would be to calculate the average amount in a frame of three days. I'm using PostgreSQL syntax for the example, but it will be the same for Teradata:

WITH data (t, a) AS (
  VALUES(1, 1),
        (2, 5),
        (3, 3),
        (4, 5),
        (5, 4),
        (6, 11)
)
SELECT t, a, avg(a) OVER (ORDER BY t ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
FROM data
ORDER BY t

... which yields:

t  a  avg
----------
1  1  3.00
2  5  3.00
3  3  4.33
4  5  4.00
5  4  6.67
6 11  7.50

As you can see, each average is calculated "over" an ordered frame consisting of the range between the previous row (1 preceding) and the subsequent row (1 following).

When you write ROWS UNBOUNDED PRECEDING, then the frame's lower bound is simply infinite. This is useful when calculating sums (i.e. "running totals"), for instance:

WITH data (t, a) AS (
  VALUES(1, 1),
        (2, 5),
        (3, 3),
        (4, 5),
        (5, 4),
        (6, 11)
)
SELECT t, a, sum(a) OVER (ORDER BY t ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM data
ORDER BY t

yielding...

t  a  sum
---------
1  1    1
2  5    6
3  3    9
4  5   14
5  4   18
6 11   29

Here's another very good explanations of SQL window functions.

Teradata - Lag column and UNBOUNDED PRECEDING

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.

Effect of QUALIFY SUM(1) OVER (PARTITION BY ROWS UNBOUNDED PRECEDING)=1?

The 1st query returns the first row of every partition.

It has a "running total" logic.

The sum for the 1st row will be 1, for the 2nd - 2, for the 3rd - 3 etc.

The 2nd query return rows from partitions that have only a single row.

---

This will give you the last record of each id_field

SELECT   *
FROM     table
QUALIFY  row_number() OVER (PARTITION BY id_field order by ... desc) = 1
;

DB2 : Implement LAG ROWS UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

You seem to want the most recent "Approved" value:

select t.*,
       max(case when status = 'Approved' then a_date end) over (partition by id order by col1) as l_a_date
from t;

Alternatively, you can use a cumulative max() (which is another interpretation of what you want):

select t.*,
       max(a_date) over (partition by id
                         order by col1 
                         rows between unbounded preceding and current row
                        ) as l_a_date
from t;

---

Related Topics