Conceptos de funciones analíticas en SQL estándar

Conceptos de funciones analíticas

En este tema, se explica cómo operan las funciones analíticas en BigQuery. Para obtener una descripción de las diferentes funciones analíticas compatibles con BigQuery, consulta los temas de referencia para las funciones de navegación, las funciones de numeración y las funciones analíticas de agregación.

En las bases de datos, una función analítica es una función que calcula valores de agregación en un grupo de filas. A diferencia de las funciones de agregación, que muestran un solo valor agregado en un grupo de filas, las funciones analíticas muestran un solo valor por fila mediante el cálculo de la función en un grupo de filas de entrada.

Las funciones analíticas son un mecanismo potente para representar de forma concisa operaciones analíticas complejas y habilitan evaluaciones eficaces que, de lo contrario, implicarían costosas autocombinaciones, self-JOIN, o cálculos fuera de la consulta de SQL.

En SQL estándar y en algunas bases de datos comerciales se las denomina "funciones analíticas (window)". Esto se debe a que una función analítica se evalúa en un grupo de filas, que se conoce como window o window frame. En otras bases de datos, pueden conocerse como funciones de procesamiento analítico en línea (OLAP).

Sintaxis simplificada:

analytic_function_name ( [ argument_list ] )
  OVER (
    [ PARTITION BY partition_expression_list ]
    [ ORDER BY expression [{ ASC | DESC }] [, ...] ]
    [ window_frame_clause ]
  )

Una función analítica requiere una cláusula OVER, que define el window frame que evalúa la función analítica. La cláusula OVER contiene las siguientes tres cláusulas opcionales. BigQuery evalúa las subcláusulas de una cláusula OVER en el orden en que están escritas.

• Una cláusula PARTITION BY divide las filas de entrada en particiones, similar a GROUP BY, pero sin combinar las filas con la misma clave.
• Una cláusula ORDER BY especifica el orden de cada partición.
• Una window_frame_clause define el window frame en la partición actual.

La cláusula OVER también puede estar vacía (OVER()) en cuyo caso el window frame incluye todas las filas de entrada.

Las funciones analíticas se evalúan después de la agregación (GROUP BY y funciones de agregación no analíticas).

Ejemplo: supongamos que una empresa que quiere crear una tabla de clasificación en cada departamento que muestre una “clasificación por antigüedad” de cada empleado, es decir, que muestre qué empleados han estado durante más tiempo. La tabla Employees contiene las columnas Name, StartDate y Department.

La siguiente consulta calcula la clasificación de cada empleado dentro de su departamento:

SELECT firstname, department, startdate,
  RANK() OVER ( PARTITION BY department ORDER BY startdate ) AS rank
FROM Employees;

El proceso de computación conceptual se ilustra en la Figura 1.

Figura 1: Ilustración de la función analítica

BigQuery evalúa las subcláusulas de una cláusula OVER en el orden en que aparecen:

1. PARTITION BY: la tabla se divide primero en dos particiones por department.
2. ORDER BY: las filas de empleados en cada partición se ordenan por startdate.
3. Enmarcado: ninguno. La cláusula de marco de ventana no está permitida para RANK(), como lo está con las funciones de numeración.
4. RANK(): la clasificación por antigüedad se calcula para cada fila en el window frame.

Sintaxis de la función analítica

analytic_function_name ( [ argument_list ] )
  OVER { window_name | ( [ window_specification ] ) }

window_specification:
  [ window_name ]
  [ PARTITION BY partition_expression_list ]
  [ ORDER BY expression [{ ASC | DESC }] [, ...] ]
  [ window_frame_clause ]

window_frame_clause:
{ ROWS | RANGE }
{
  { UNBOUNDED PRECEDING | numeric_expression PRECEDING | CURRENT ROW }
  |
  { BETWEEN window_frame_boundary_start AND window_frame_boundary_end }
}

window_frame_boundary_start:
{ UNBOUNDED PRECEDING | numeric_expression { PRECEDING | FOLLOWING } | CURRENT ROW }

window_frame_boundary_end:
{ UNBOUNDED FOLLOWING | numeric_expression { PRECEDING | FOLLOWING } | CURRENT ROW }

Las funciones analíticas pueden aparecer como una expresión escalar o un operando de expresión escalar en solo dos lugares en la consulta:

• Lista SELECT. Si la función analítica aparece en la lista SELECT, la argument_list no puede referirse a los alias que se incorporan en la misma lista SELECT.
• Cláusula ORDER BY. Si la función analítica aparece en la cláusula ORDER BY de la consulta, la argument_list puede hacer referencia a los alias de lista SELECT.

Además, una función analítica no puede referirse a otra función analítica en la argument_list o la cláusula OVER, aunque sea de forma indirecta a través de un alias.

No válido:

SELECT ROW_NUMBER() OVER () AS alias1
FROM Singers
ORDER BY ROW_NUMBER() OVER(PARTITION BY alias1)

En la consulta anterior, la función analítica alias1 se resuelve en una función analítica: ROW_NUMBER() OVER().

Cláusula OVER

Sintaxis:

OVER { window_name | ( [ window_specification ] ) }

window_specification:
  [ window_name ]
  [ PARTITION BY partition_expression_list ]
  [ ORDER BY sort_specification_list ]
  [ window_frame_clause ]

La cláusula OVER tiene tres componentes posibles:

• Cláusula PARTITION BY
• Cláusula ORDER BY
• Una window_frame_clause o un window_name, que hace referencia a una window_specification definida en una cláusula WINDOW.

Si la cláusula OVER está vacía, OVER(), la función analítica se calcula en una sola partición que contiene todas las filas de entrada, lo que significa que producirá el mismo resultado para cada fila de salida.

Cláusula PARTITION BY

Sintaxis:

PARTITION BY expression [, ... ]

La cláusula PARTITION BY divide las filas de entrada en particiones separadas, en las cuales la función analítica se evalúa de forma independiente. Se permiten múltiples expressions en la cláusula PARTITION BY.

El tipo de datos de expression debe ser agrupable y admitir particiones. Esto significa que la expression no puede ser ninguno de los siguientes tipos de datos:

• Punto flotante
• Struct
• Array

Esta lista es casi idéntica a la lista de los tipos de datos que GROUP BY no admite, con la exclusión adicional de los tipos de punto flotante (consulta “Agrupable” en la tabla Propiedades de tipo de datos en la parte superior de Tipos de datos de BigQuery).

Si no existe una cláusula PARTITION BY, BigQuery trata la entrada completa como una partición única.

Cláusula ORDER BY

Sintaxis:

ORDER BY expression [ ASC | DESC ] [, ... ]

La cláusula ORDER BY define un orden en cada partición. Si no existe una cláusula ORDER BY, el orden de las filas dentro de una partición es no determinista. Algunas funciones analíticas requieren ORDER BY; esto se observa en la sección para cada familia de funciones analíticas. Incluso si hay una cláusula ORDER BY presente, algunas funciones no son sensibles al orden en un window frame (p. ej., COUNT).

La cláusula ORDER BY en una cláusula OVER es coherente con la cláusula ORDER BY normal en cuanto a los siguientes factores:

• Pueden existir múltiples expressions.
• expression debe tener un tipo que admita el orden.
• Se permite una especificación ASC/DESC opcional para cada expression.
• Los valores NULL se ordenan como el valor mínimo posible (el primero ASC, el último DESC)

La asistencia de tipo de datos es idéntica a la cláusula ORDER BY normal en cuanto a que los siguientes tipos no admiten pedidos:

• Array
• Struct

Si la cláusula OVER contiene una cláusula ORDER BY, pero no window_frame_clause, entonces ORDER BY define de forma implícita a window_frame_clause de la siguiente manera:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Si no existe la cláusula window_frame_clause ni ORDER BY, window frame es la configuración predeterminada dentro toda la partición.

Cláusula de marco de ventana

Sintaxis:

{ ROWS | RANGE }
{
  { UNBOUNDED PRECEDING | numeric_expression PRECEDING | CURRENT ROW }
  |
  { BETWEEN window_frame_boundary_start AND window_frame_boundary_end }
}

window_frame_boundary_start:
{ UNBOUNDED PRECEDING | numeric_expression { PRECEDING | FOLLOWING } | CURRENT ROW }

window_frame_boundary_end:
{ UNBOUNDED FOLLOWING | numeric_expression { PRECEDING | FOLLOWING } | CURRENT ROW }

window_frame_clause define window frame, en la fila actual dentro de una partición, a través del cual se evalúa la función analítica. window_frame_clause permite los marcos de ventanas físicos (definidos por ROWS) y los marcos de ventanas lógicos (definidos por RANGE). Si la cláusula OVER contiene una cláusula ORDER BY, pero no window_frame_clause, entonces ORDER BY define de forma implícita a window_frame_clause de la siguiente manera:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Si no existe la cláusula window_frame_clause ni ORDER BY, window frame es la configuración predeterminada dentro toda la partición.

La numeric_expression solo puede ser una constante o un parámetro de consulta, ambos deben tener un valor no negativo. De lo contrario, BigQuery producirá un error.

Ejemplos de cláusulas de marco de ventana:

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

• Incluye toda la partición.
• Ejemplo de uso: calcula un total general en la partición.

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

• Incluye todas las filas de la partición anterior o incluye la fila actual.
• Ejemplo de uso: calcula una suma acumulativa.

ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING

• Incluye todas las filas entre dos antes y dos después de la fila actual.
• Ejemplo de uso: calcula una media móvil.

Si window_frame_spec usa la cláusula BETWEEN:

• window_frame_boundary_start debe especificar un límite que comience no más tarde que window_frame_boundary_end. Esto tiene las siguientes consecuencias:
  1. Si window_frame_boundary_start contiene CURRENT ROW, window_frame_boundary_end no puede contener PRECEDING.
  2. Si window_frame_boundary_start contiene FOLLOWING, window_frame_boundary_end no puede contener CURRENT ROW o PRECEDING.
• window_frame_boundary_start no tiene un valor predeterminado.

De lo contrario, el límite de window_frame_spec especificado representa el inicio de marco de ventana y el final del límite del marco de ventana predeterminado es 'CURRENT ROW'. Por lo que,

ROWS 10 PRECEDING

es equivalente a

ROWS BETWEEN 10 PRECEDING AND CURRENT ROW

FILAS

Los marcos de ventana basados en ROWS calculan el window frame basados en desplazamientos físicos desde la fila actual. Por ejemplo, el marco de ventana, a continuación, define un marco de ventana de tamaño cinco (como máximo) en la fila actual.

ROWS BETWEEN 2 PRECEDING and 2 FOLLOWING

La numeric_expression en window_frame_clause se interpreta como una cantidad de filas de la fila actual y debe ser un número entero constante, no negativo. También puede ser un parámetro de consulta.

Si el window frame de una fila dada se extiende más allá del comienzo o del final de la partición, entonces el window frame solo incluirá filas desde esa partición.

Ejemplo: considera la siguiente tabla con las columnas x, y y z.

z	x	y
1	5	AA
2	2	AA
3	11	AB
4	2	AA
5	8	AC
6	10	AB
7	1	AB

Considera la siguiente función analítica:

SUM(x) OVER (PARTITION BY y ORDER BY z ROWS BETWEEN 1 PRECEDING AND 1
FOLLOWING)

La cláusula PARTITION BY divide la tabla en 3 particiones en función del valor y, y ORDER BY ordena las filas en cada partición por el valor z.

Partición 1 de 3:

z	x	y
1	5	AA
2	2	AA
4	2	AA

Partición 2 de 3:

z	x	y
3	11	AB
6	10	AB
7	1	AB

Partición 3 de 3:

z	x	y
5	8	AC

En las siguientes tablas, la negrita indica la fila que se está evaluando en el momento y las celdas de color indican todas las filas en el window frame de esa fila.

• Para la primera fila de la partición y = AA, el window frame incluye solo 2 filas, ya que no hay una fila anterior, aunque window_frame_spec indica un tamaño de ventana de 3. El resultado de la función analítica es 7 para la primera fila.

z	x	y
1	5	AA
2	2	AA
4	2	AA

• Para la segunda fila de la partición, el window frame incluye las 3 filas. El resultado de la función analítica es 9 para la segunda fila.

z	x	y
1	5	AA
2	2	AA
4	2	AA

• Para la última fila de la partición, el window frame incluye solo 2 filas, ya que no existe una fila siguiente. El resultado de la función analítica para la tercera fila es 4.

z	x	y
1	5	AA
2	2	AA
4	2	AA

RANGE

Los marcos de ventana basados en RANGE calculan el window frame en función de un rango lógico de filas en la fila actual basados en el valor clave ORDER BY de la fila actual. El valor del rango proporcionado se suma o se resta al valor de clave de la fila actual a fin de definir un límite de rango inicial o final para el window frame.

La cláusula ORDER BY se debe especificar, a menos que la ventana sea como se muestra a continuación:

RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

La numeric_expression en la window_frame_clause se interpreta como un desplazamiento del valor de la fila actual de la clave ORDER BY. La numeric_expression debe tener un tipo numérico. En la actualidad, DATE y TIMESTAMP no son compatibles. Además, la numeric_expression debe ser un número entero constante, no negativo o un parámetro.

En un marco de ventana basado en RANGE, puede haber como máximo una expression en la cláusula ORDER BY, y la expression debe tener un tipo numérico.

Ejemplo de un marco de ventana basado en RANGE en el que existe una sola partición:

SELECT x, COUNT(*) OVER ( ORDER BY x
  RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING ) AS count_x
FROM T;

En las siguientes tablas, la negrita indica la fila que se está evaluando en el momento y las celdas de color indican todas las filas en el window frame de esa fila.

• Para la fila 1, x = 5 y, por lo tanto, COUNT(*) solo incluirá las filas en las que 3 <= x <= 7

x	count_x
5	1
2
11
2
8
10
1

• Para la fila 2, x = 2 y, por lo tanto, COUNT(*) solo incluirá las filas en las que 0 <= x <= 4

x	count_x
5	1
2	3
11
2
8
10
1

• Para la fila 3, x = 11 y, por lo tanto, COUNT(*) solo incluirá las filas en las que 9 <= x <= 13

x	count_x
5	1
2	3
11	2
2
8
10
1

Cláusula WINDOW

Sintaxis:

WINDOW window_definition [, ...]
window_definition: window_name AS ( window_specification )

Una cláusula WINDOW define una lista de ventanas con nombre cuyo window_name puede hacer referencia en funciones analíticas en la lista SELECT. Esto es útil cuando deseas usar la misma window_frame_clause para múltiples funciones analíticas.

La cláusula WINDOW solo puede aparecer al final de una cláusula SELECT, como se muestra en la sintaxis de consultas.

Ventanas denominadas

Una vez que defines una cláusula WINDOW, puedes usar las ventanas denominadas en funciones analíticas, pero solo en la lista SELECT; no pueden usar ventanas denominadas en la cláusula ORDER BY. Las ventanas denominadas pueden aparecer por sí mismas o estar incorporadas en una cláusula OVER. Las ventanas denominadas pueden hacer referencia a los alias de la lista SELECT.

Ejemplos:

SELECT SUM(x) OVER window_name FROM ...

SELECT SUM(x) OVER (
  window_name
  PARTITION BY...
  ORDER BY...
  window_frame_clause)
FROM ...

Cuando se incorpora en una cláusula OVER, la window_specification asociada a window_name debe ser compatible con PARTITION BY, ORDER BY y window_frame_clause que están en la misma cláusula OVER.

Las siguientes reglas se aplican a las ventanas denominadas:

• Puedes hacer referencia a ventanas denominadas solo en la lista SELECT; no puedes hacer referencia a ellas en una cláusula ORDER BY, una consulta externa o cualquier subconsulta.
• Una ventana W1 (con o sin nombre) puede hacer referencia a una ventana denominada NW2, con las siguientes reglas:
  1. Si W1 es una ventana denominada, entonces la ventana de referencia con nombre NW2 debe preceder a W1 en la misma cláusula WINDOW.
  2. W1 no puede contener una cláusula PARTITION BY.
  3. No puede ser cierto que W1 y NW2 contengan una cláusula ORDER BY.
  4. NW2 no puede contener una window_frame_clause.
• Si una ventana W1 (denominada o no) hace referencia a una ventana denominada NW2, la especificación de la ventana resultante se define mediante lo siguiente:
  1. La PARTITION BY de NW2, si existe una.
  2. El ORDER BY de W1 o NW2, si se especifica alguno; no es posible que ambos tengan una cláusula ORDER BY.
  3. La window_frame_clause de W1, si existe una.

Funciones de navegación

En este tema, se explica cómo operan las funciones de navegación analítica. Para obtener una descripción de las funciones de navegación analítica compatibles con BigQuery, consulta la referencia de las funciones para las funciones de navegación.

Las funciones de navegación, por lo general, calculan algunas value_expression en una fila diferente en el marco de ventana de la fila actual. La sintaxis de la cláusula OVER varía según las funciones de navegación.

A continuación, se detallan los requisitos de la cláusula OVER:

• PARTITION BY: opcional.
• ORDER BY:
  1. No permitido para PERCENTILE_CONT y PERCENTILE_DISC.
  2. Necesario para FIRST_VALUE, LAST_VALUE, NTH_VALUE, LEAD y LAG.
• window_frame_clause:
  1. No permitido para PERCENTILE_CONT, PERCENTILE_DISC, LEAD y LAG.
  2. Opcional para FIRST_VALUE, LAST_VALUE y NTH_VALUE.

Para todas las funciones de navegación, el tipo de datos del resultado es el mismo tipo que value_expression.

Funciones de numeración

En este tema, se explica cómo operan las funciones de numeración analítica. Para obtener una explicación de las funciones de numeración analítica compatibles con BigQuery, consulta la referencia de funciones de numeración.

Las funciones de numeración asignan valores de números enteros a cada fila en función de su posición en la ventana especificada.

Ejemplo de RANK(), DENSE_RANK() y ROW_NUMBER():

SELECT x,
  RANK() OVER (ORDER BY x ASC) AS rank,
  DENSE_RANK() OVER (ORDER BY x ASC) AS dense_rank,
  ROW_NUMBER() OVER (PARTITION BY x ORDER BY y) AS row_num
FROM ...

x	rank	dense_rank	row_num
1	1	1	1
2	2	2	2
2	2	2	3
5	4	3	4
8	5	4	5
10	6	5	6
10	6	5	7

• RANK(): para x = 5, el rank muestra 4, ya que RANK() se incrementa por la cantidad de filas en el grupo de orden de la ventana anterior.
• DENSE_RANK(): para x = 5, dense_rank muestra 3, ya que DENSE_RANK() siempre se incrementa en 1, sin omitir nunca un valor.
• ROW_NUMBER(): para x = 5, row_num muestra 4.

Funciones analíticas de agregación

BigQuery es compatible con determinadas funciones de agregación como las funciones analíticas.

Con estas funciones, la cláusula OVER solo se agrega a la llamada de función de agregación; la sintaxis de la llamada de la función permanece sin cambios. Al igual que los equivalentes de las funciones agregadas, estas funciones analíticas realizan agregaciones, pero específicamente en el marco de ventana relevante para cada fila. Los tipos de datos de resultados de estas funciones analíticas son los mismos que sus equivalentes de las funciones agregadas.

Para obtener una descripción de las funciones analíticas de agregación compatibles con BigQuery, consulta la referencia de funciones para las funciones analíticas de agregación.