¿Para qué se utiliza la precedencia SIN LÍMITE de FILAS en Teradata?

Acabo de comenzar con Teradata y me he encontrado con una Función Analítica Ordenada llamada "Filas sin límites anteriores" en Teradata. Probé varios sitios para aprender sobre la función, pero todos ellos usan un ejemplo complicado que explica lo mismo. Podría por favor darme un ejemplo ingenuo para que pueda aclarar lo básico.

Author: Lukas Eder, 2015-06-16
Source

2 answers

Es la cláusula "frame" o "range" de las funciones de ventana, que son parte del estándar SQL e implementadas en muchas bases de datos, incluyendo Teradata.

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

... que produce: 

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 marco ordenado que consiste en el rango entre la fila anterior (1 preceding) y la fila siguiente (1 following).

Cuando escribes ROWS UNBOUNDED PRECEDING, entonces el límite inferior del fotograma es simplemente infinito. Esto es útil al calcular sumas (es decir, "totales corrientes" ), 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

Ceder...

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 SQL window .

 53
Author: Lukas Eder,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-06-16 08:17:42

ROWS UNBOUNDED PRECEDING no es una sintaxis específica de Teradata, es SQL estándar. 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 basada en todas las FILAS entre una fila inicial y una fila final.

Las filas iniciales y finales pueden ser fijas o relativas a la fila actual basada en las siguientes palabras clave:

  • PRECEDENTE SIN ACOTAR, todas las filas antes de la fila actual -> fijo
  • SIN LÍMITES SIGUIENTES, todas las filas después de la fila actual - > fijo
  • x PRECEDIENDO, x filas antes de la fila actual - > relativa
  • y SIGUIENTES, y filas después de la fila actual - > relativa

Los posibles tipos de cálculo incluyen:

  • Tanto la fila inicial como la final son fijas, la ventana consta de todas las filas de una partición, por ejemplo, una Suma de grupo, es decir, agregado más filas de detalle
  • Un extremo es fijo, el otro relativo a la fila actual, el número de filas aumenta o disminuye, por ejemplo, un Total Corriente, Suma Restante
  • La fila inicial y final son relativas a la fila actual, el número de filas dentro de una ventana es fijo, por ejemplo, una Media móvil sobre n filas

Así que SUM(x) OVER (ORDER BY col ROWS UNBOUNDED PRECEDING) resulta en una Suma Acumulativa o Total Corriente 

11 -> 11
 2 -> 11 +  2                = 13
 3 -> 13 +  3 (or 11+2+3)    = 16
44 -> 16 + 44 (or 11+2+3+44) = 60
 25
Author: dnoeth,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-06-16 08:38:00