Funciones de ventana - Amazon Redshift
Resumen de la sintaxis de la función de ventanaOrdenación única de datos para funciones de ventanaFunciones compatiblesTabla de muestra para ejemplos de funciones de ventana

Funciones de ventana

Con las funciones de ventana, puede crear consultas empresariales analíticas de manera más eficiente. Las funciones de ventana operan en una partición o "ventana" de un conjunto de resultados y devuelven un valor para cada fila de esa ventana. Por el contrario, las funciones que no son de ventana realizan sus cálculos respecto de cada fila en el conjunto de resultados. A diferencia de las funciones de grupo que agregan las filas de resultados, las funciones de ventana retienen todas las filas de la expresión de tabla.

Los valores devueltos se calculan con los valores de los conjuntos de filas en esa ventana. Para cada fila en la tabla, la ventana define un conjunto de filas que se usan para computar atributos adicionales. Una ventana se define utilizando una especificación de ventana (la cláusula OVER) y se basa en tres conceptos principales:

  • Particionamiento de ventana, que forma grupos de filas (cláusula PARTITION).

  • Ordenación de ventana, que define un orden o una secuencia de filas dentro de cada partición (cláusula ORDER BY).

  • Marcos de ventana, que se definen en función de cada fila para restringir aún más el conjunto de filas (especificación ROWS).

Las funciones de ventana son el último conjunto de operaciones realizadas en una consulta, excepto por la cláusula final ORDER BY. Todas las combinaciones y todas las cláusulas WHERE, GROUP BY y HAVING se completan antes de que se procesen las funciones de ventana. Por lo tanto, las funciones de ventana pueden figurar solamente en la lista SELECT o en la cláusula ORDER BY. Se pueden usar distintas funciones de ventana dentro de una única consulta con diferentes cláusulas de marco. También se pueden usar las funciones de ventana en otras expresiones escalares, como CASE.

Resumen de la sintaxis de la función de ventana

Las funciones de ventana siguen una sintaxis estándar, que es la que se indica a continuación.

function (expression) OVER (
[ PARTITION BY expr_list ]
[ ORDER BY order_list [ frame_clause ] ] )

Aquí, function es una de las funciones que se describen en esta sección

La apariencia de expr_list es la siguiente.

expression | column_name [, expr_list ]

El order_list tiene la siguiente apariencia. 

expression | column_name [ ASC | DESC ] 
[ NULLS FIRST | NULLS LAST ]
[, order_list ]

La frame_clause tiene la siguiente apariencia. 

ROWS
{ UNBOUNDED PRECEDING | unsigned_value PRECEDING | CURRENT ROW } |

{ BETWEEN
{ UNBOUNDED PRECEDING | unsigned_value { PRECEDING | FOLLOWING } | CURRENT ROW}
AND
{ UNBOUNDED FOLLOWING | unsigned_value { PRECEDING | FOLLOWING } | CURRENT ROW }}

Argumentos

función

La función de ventana. Para obtener más información, consulte las descripciones de las funciones individuales.

OVER

La cláusula que define la especificación de ventana. La cláusula OVER es obligatoria para las funciones de ventana y distingue funciones de ventana de otras funciones SQL.

PARTITION BY expr_list

(Opcional) La cláusula PARTITION BY subdivide el conjunto de resultado en particiones, muy similar a la cláusula GROUP BY. Si hay una cláusula de partición, la función se calcula para las filas en cada partición. Si no se especifica una cláusula de partición, una única partición tiene toda la tabla y la función se computa para la tabla completa.

Las funciones de clasificación, DENSE_RANK, NTILE, RANK y ROW_NUMBER, requieren una comparación global de todas las filas en el conjunto de resultados. Cuando se utiliza una cláusula PARTITION BY, el optimizador de consultas puede ejecutar cada agregación en paralelo mediante la distribución de la carga de trabajo entre distintos sectores, según las particiones. Si no hay cláusula PARTITION BY, el paso de agregación se debe ejecutar en serie en un único sector, lo que puede tener consecuencias negativas importantes en el rendimiento, especialmente en el caso de clústeres grandes.

Amazon Redshift no admite literales de cadena en cláusulas PARTITION BY.

ORDER BY order_list

(Opcional) La función de ventana se aplica a las filas dentro de cada partición ordenada, según la especificación de orden en ORDER BY. Esta cláusula ORDER BY es distinta y no guarda relación alguna con las cláusulas ORDER BY de frame_clause. La cláusula ORDER BY se puede usar sin la cláusula PARTITION BY.

Para las funciones de clasificación, la cláusula ORDER BY identifica las medidas para los valores de clasificación. Para las funciones de agregación, las filas particionadas se deben ordenar antes de que la función de agregación se compute para cada marco. Para obtener más información acerca de los tipos de funciones de ventana, consulte Funciones de ventana.

Se requieren identificadores de columnas o expresiones que toman el valor de los identificadores de columnas en la lista de ordenación. No se pueden usar constantes ni expresiones constantes como sustitutos para los nombres de columnas.

Los valores NULLS se tratan como su propio grupo; se ordenan y se clasificación según la opción NULLS FIRST o NULLS LAST. De manera predeterminada, los valores NULL se ordenan y clasificación al final en orden ASC, y se ordenan y se clasifican primero en orden DESC.

Amazon Redshift no admite literales de cadena en cláusulas ORDER BY.

Si se omite la cláusula ORDER BY, el orden de las filas no es determinista.

nota

En cualquier sistema paralelo, como Amazon Redshift, cuando una cláusula ORDER BY no produce una ordenación total y única de los datos, el orden de las filas no es determinístico. Es decir, si la expresión ORDER BY produce valores duplicados (una ordenación parcial), el orden de retorno de esas filas puede variar de una ejecución de Amazon Redshift a la siguiente. A su vez, las funciones de ventana pueden devolver resultados inesperados o inconsistente. Para obtener más información, consulte Ordenación única de datos para funciones de ventana.

column_name

Nombre de una columna que se particionará u ordenará.

ASC | DESC

Opción que define el orden de ordenación para la expresión, de la siguiente manera:

  • ASC: ascendente (por ejemplo, de menor a mayor para valores numéricos y de la A a la Z para cadenas con caracteres). Si no se especifica ninguna opción, los datos se ordenan, de manera predeterminada, en orden ascendente.

  • DESC: descendente (de mayor a menor para valores numéricos y de la Z a la A para cadenas).

NULLS FIRST | NULLS LAST

Opción que especifica si los valores NULL se deben ordenar en primer lugar, antes de los valores no nulos, o al final, después de los valores no nulos. De manera predeterminada, los valores NULL se ordenan y clasificación al final en orden ASC, y se ordenan y se clasifican primero en orden DESC.

frame_clause

Para funciones de agregación, la cláusula de marco limita el conjunto de filas en una ventana de función al usar ORDER BY. Le permite incluir o excluir conjuntos de filas dentro del resultado ordenado. La cláusula de marco consta de la palabra clave ROWS y de los especificadores correspondientes.

La cláusula de marco no se aplica a las funciones de clasificación. Además, no es necesaria cuando no se utiliza una cláusula ORDER BY en la cláusula OVER para una función de agrupación. Si se utiliza una cláusula ORDER BY para una función de agregación, se necesita una cláusula de marco explícita.

Cuando no se especifica una cláusula ORDER BY, el marco implícito es ilimitado, lo que es equivalente a ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

ROWS

Esta cláusula define el marco de ventana especificando un desplazamiento físico desde la fila actual .

Esta cláusula especifica las filas en la ventana o partición actual que se combinará con el valor de la fila actual. Utiliza argumentos que especifican la posición de la fila, que puede ser antes o después de la fila actual. El punto de referencia para todos los marcos de ventana es la fila actual. Cada fila se convierte en la fila actual cuando el marco de ventana se desplaza hacia delante en la partición.

El marco puede ser un conjunto simple de filas hasta la fila actual, que se incluye.

{UNBOUNDED PRECEDING | offset PRECEDING | CURRENT ROW}

O bien, puede ser un conjunto de filas entre dos límites.

BETWEEN
{ UNBOUNDED PRECEDING | offset { PRECEDING | FOLLOWING } | CURRENT ROW }
AND
{ UNBOUNDED FOLLOWING | offset { PRECEDING | FOLLOWING } | CURRENT ROW }

UNBOUNDED PRECEDING indica que la ventana comienza en la primera fila de la partición; offset PRECEDING indica que la ventana comienza en un número de filas equivalente al valor de desplazamiento antes de la fila actual. UNBOUNDED PRECEDING es el valor predeterminado.

CURRENT ROW indica que la ventana comienza o finaliza en la fila actual.

UNBOUNDED FOLLOWING indica que la ventana finaliza en la última fila de la partición; offset FOLLOWING indica que la ventana finaliza en un número de filas equivalente al valor de desplazamiento después de la fila actual.

offset identifica un número físico de filas antes o después de la fila actual. En este caso, offset debe ser una constante que se evalúe como un valor numérico positivo. Por ejemplo, 5 FOLLOWING finaliza el marco de cinco filas después de la fila actual.

Cuando no se especifica BETWEEN, el marco se limita implícitamente a la fila actual. Por ejemplo, ROWS 5 PRECEDING equivale a ROWS BETWEEN 5 PRECEDING AND CURRENT ROW. Además, ROWS UNBOUNDED FOLLOWING equivale a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING.

nota

No puede especificar un marco en el que el límite de inicio sea mayor que el límite final. Por ejemplo, no puede especificar ninguno de estos marcos. 

between 5 following and 5 preceding
between current row and 2 preceding
between 3 following and current row

Ordenación única de datos para funciones de ventana

Si una cláusula ORDER BY para una función de ventana no produce una ordenación total y única de los datos, el orden de las filas no es determinístico. Si la expresión ORDER BY produce valores duplicados (una ordenación parcial), el orden de retorno de esas filas puede variar en distintas ejecuciones. En este caso, las funciones de ventana también pueden devolver resultados inesperados o inconsistentes.

Por ejemplo, la siguiente consulta devuelve resultados diferentes con las múltiples ejecuciones. Estos resultados diferentes se producen porque order by dateid no genera una ordenación única de los datos para la función de ventana SUM. 

select dateid, pricepaid,
sum(pricepaid) over(order by dateid rows unbounded preceding) as sumpaid
from sales
group by dateid, pricepaid;

dateid | pricepaid |   sumpaid
--------+-----------+-------------
1827 |   1730.00 |     1730.00
1827 |    708.00 |     2438.00
1827 |    234.00 |     2672.00
...

select dateid, pricepaid,
sum(pricepaid) over(order by dateid rows unbounded preceding) as sumpaid
from sales
group by dateid, pricepaid;

dateid | pricepaid |   sumpaid
--------+-----------+-------------
1827 |    234.00 |      234.00
1827 |    472.00 |      706.00
1827 |    347.00 |     1053.00
...

En este caso, agregar una segunda columna ORDER BY a la función de ventana puede solucionar el problema. 

select dateid, pricepaid,
sum(pricepaid) over(order by dateid, pricepaid rows unbounded preceding) as sumpaid
from sales
group by dateid, pricepaid;

dateid | pricepaid | sumpaid
--------+-----------+---------
1827 |    234.00 |  234.00
1827 |    337.00 |  571.00
1827 |    347.00 |  918.00
...

Funciones compatibles

Amazon Redshift admite dos tipos de funciones de ventana: agrupación y clasificación.

A continuación, se indican las funciones de agregado admitidas:

A continuación, se indican las funciones de clasificación admitidas:

Tabla de muestra para ejemplos de funciones de ventana

Puede encontrar ejemplos específicos de funciones de ventana con la descripción de cada función. Algunos de los ejemplos utilizan una tabla denominada WINSALES, que tiene 11 filas, como se muestra a continuación.

SALESID DATEID SELLERID BUYERID QTY QTY_SHIPPED
30001 8/2/2003 3 B 10 10
10001 12/24/2003 1 C 10 10
10005 12/24/2003 1 A 30
40001 1/9/2004 4 A 40
10006 1/18/2004 1 C 10
20001 2/12/2004 2 B 20 20
40005 2/12/2004 4 A 10 10
20002 2/16/2004 2 C 20 20
30003 4/18/2004 3 B 15
30004 4/18/2004 3 B 20
30007 9/7/2004 3 C 30

El siguiente script crea y completa la tabla de muestra WINSALES.

CREATE TABLE winsales(
  salesid int,
  dateid date,
  sellerid int,
  buyerid char(10),
  qty int,
  qty_shipped int);

INSERT INTO winsales VALUES
  (30001, '8/2/2003', 3, 'b', 10, 10),
  (10001, '12/24/2003', 1, 'c', 10, 10),
  (10005, '12/24/2003', 1, 'a', 30, null),	
  (40001, '1/9/2004', 4, 'a', 40, null),	
  (10006, '1/18/2004', 1, 'c', 10, null),	
  (20001, '2/12/2004', 2, 'b', 20, 20),
  (40005, '2/12/2004', 4, 'a', 10, 10),
  (20002, '2/16/2004', 2, 'c', 20, 20),
  (30003, '4/18/2004', 3, 'b', 15, null),
  (30004, '4/18/2004', 3, 'b', 20, null),	
  (30007, '9/7/2004', 3, 'c', 30, null);