sql - ¿Para qué se utiliza ROWS UNBOUNDED PRECEDING en Teradata?

window-functions

Question

Estoy empezando en Teradata y me he encontrado con una función analítica ordenada llamada "Filas sin precedente" en Teradata. Probé varios sitios para aprender sobre la función, pero todos usan un ejemplo complicado que explica lo mismo. ¿Podría por favor proporcionarme un ejemplo ingenuo para que pueda aclarar lo básico?

Answer 1

Es la cláusula "marco" o "rango" de las funciones de la ventana, que forman parte del estándar SQL e implementadas en muchas bases de datos, incluida Teradata.

Un ejemplo simple sería calcular la cantidad promedio en un marco de tres días. Estoy usando la sintaxis de PostgreSQL para el ejemplo, pero será la misma para 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

... cuyos rendimientos:

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

Como puede ver, cada promedio se calcula "sobre" un cuadro ordenado que consiste en el rango entre la fila anterior ( 1 preceding ) y la fila subsiguiente ( 1 following ).

Cuando escribe ROWS UNBOUNDED PRECEDING , el límite inferior del marco es simplemente infinito. Esto es útil cuando se calculan sumas (es decir, "totales acumulados" ), por ejemplo:

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

flexible...

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

Aquí hay otra muy buena explicación de las funciones de la ventana SQL .

Answer 2

ROWS UNBOUNDED PRECEDING no es una sintaxis específica de Teradata, es Standard SQL. Junto con ORDER BY , define la ventana en la que se calcula el resultado.

Lógicamente, una función de agregado de ventana se calcula nuevamente para cada fila dentro de la PARTICIÓN en función de todas las filas entre una fila inicial y una fila final.

Las filas de inicio y finalización pueden ser fijas o relativas a la fila actual según las siguientes palabras clave:

• PRECEDENTES SIN LÍMITES, todas las filas antes de la fila actual -> corregidas
• Sin límites, todas las filas después de la fila actual -> corregidas
• x PRECEDENTES, x filas antes de la fila actual -> relativa
• y SIGUIENDO, y filas después de la fila actual -> relativa

Los posibles tipos de cálculo incluyen:

• Tanto la fila de inicio como la de finalización son fijas, la ventana consta de todas las filas de una partición, por ejemplo, una suma de grupo, es decir, un agregado más filas de detalles
• Un extremo es fijo, el otro relativo a la fila actual, el número de filas aumenta o disminuye, por ejemplo, un total acumulado, suma restante
• Las filas de inicio y finalización son relativas a la fila actual, el número de filas dentro de una ventana es fijo, por ejemplo, un promedio móvil sobre n filas

Por lo tanto, SUM(x) OVER (ORDER BY col ROWS UNBOUNDED PRECEDING) da como resultado una suma acumulativa o un total acumulado

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