Что такое ROWS UNBOUNDED PRECEDING, используемое в Teradata?

Ответ 1

Это предложение "frame" или "range" для оконных функций, которые являются частью стандарта SQL и реализованы во многих базах данных, включая Teradata.

Простым примером может быть вычисление средней суммы за три дня. Я использую синтаксис PostgreSQL для примера, но он будет таким же для 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

... что дает:

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

Как вы можете видеть, каждое среднее вычисляется "над" упорядоченным кадром, состоящим из диапазона между предыдущей строкой (1 preceding) и последующей строкой (1 following).

Когда вы пишете ROWS UNBOUNDED PRECEDING, нижняя граница кадра просто бесконечна. Это полезно при вычислении сумм (т.е. "running totals" ), например:

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

приносит...

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

Вот еще одно очень хорошее объяснение функций окна SQL.

Ответ 2

ROWS UNBOUNDED PRECEDING не является синтаксисом, специфичным для Teradata, это стандартный SQL. Вместе с ORDER BY он определяет окно, на котором рассчитывается результат.

Логически функция оконной агрегирования вычисляется для каждой строки в PARTITION на основе всех ROWS между начальной строкой и конечная строка.

Начальные и конечные строки могут быть фиксированными или относительными к текущей строки на основе следующих ключевых слов:

• UNBOUNDED PRECEDING, все строки перед текущей строкой → исправлены
• UNBOUNDED FOLLOWING, все строки после текущей строки → исправлены
• x PRECEDING, x строк перед текущей строкой → относительный
• y FOLLOWING, y строк после текущей строки → relative

Возможные виды вычислений включают:

• Исходная и конечная строка фиксированы, окно состоит из всех строк раздела, например. Групповая сумма, т.е. суммарные и подробные строки
• Один конец фиксирован, другой по отношению к текущей строке, количество строк увеличивается или уменьшается, например. Текущая общая сумма, оставшаяся сумма.
• Начальная и конечная строки относятся к текущей строке, количество строк в окне фиксировано, например. скользящее среднее по n строкам

Итак, SUM(x) OVER (ORDER BY col ROWS UNBOUNDED PRECEDING) приводит к суммарной сумме или общей сумме

11 -> 11
 2 -> 11 +  2                = 13
 3 -> 13 +  3 (or 11+2+3)    = 16
44 -> 16 + 44 (or 11+2+3+44) = 60