Conceptos de funciones analíticas en SQL estándar

En este tema, se explica cómo operan las funciones analíticas en BigQuery. A fin de 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 agregadas.

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 agregadas, 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, denominadas 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, de manera similar a GROUP BY, pero sin combinar las filas que tienen la misma clave.
Una cláusula ORDER BY especifica el orden dentro 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: pensemos en 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 trabajaron allí durante más tiempo. La tabla Employees contiene las columnas Name, StartDate y Department.

Con la siguiente consulta, se 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.

Imagen de Markdown 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:

PARTITION BY: la tabla se divide primero en dos particiones por department.
ORDER BY: las filas de empleados en cada partición se ordenan por startdate.
Enmarcado: ninguno. La cláusula de marco de ventana no está permitida para RANK(), como lo está para las funciones de numeración.
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 de la consulta:

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

Además, una función analítica no puede hacer referencia a otra en la argument_list ni en 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
Un 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 varias 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
Arreglo

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 SQL estándar).

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 dentro de cada partición. Si no existe una cláusula ORDER BY, el orden de las filas dentro de una partición no es 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 normal en cuanto a los siguientes factores:

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

Se admiten los mismos tipos de datos que en la cláusula ORDER BY normal en cuanto a que los siguientes tipos no admiten ordenamiento:

Arreglo
Struct

Si la cláusula OVER contiene una cláusula ORDER BY pero no una window_frame_clause, 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 para 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 el window frame, en la fila actual dentro de una partición, a través de la cual se evalúa la función analítica. window_frame_clause permite los marcos de ventanas físicos (que se definen por ROWS) y los marcos de ventanas lógicos (que se definen por RANGE). Si la cláusula OVER contiene una cláusula ORDER BY, pero no una 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 para toda la partición.

La numeric_expression solo puede ser una constante o un parámetro de consulta; ambos deben tener un valor que no sea 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: calcular una media móvil.

Si window_frame_spec usa la cláusula BETWEEN, sucede lo siguiente:

window_frame_boundary_start debe especificar un límite que no comience 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 ni 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”. Entonces,

ROWS 10 PRECEDING

es equivalente a

ROWS BETWEEN 10 PRECEDING AND CURRENT ROW

ROWS

Los marcos de ventana basados en ROWS calculan el window frame en función de desplazamientos físicos desde la fila actual. Por ejemplo, el marco de ventana que se muestra 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 la window_frame_clause se interpreta como una cantidad de filas desde la fila actual y debe ser un número entero constante que no sea 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 solo incluirá filas provenientes de esa partición.

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

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 la cláusula 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.

En 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

En 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

En 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 basado en el valor clave ORDER BY de la fila actual. El valor del rango proporcionado se suma o se resta del 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 clave ORDER BY de la fila actual. 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 parámetro o un número entero constante que no sea negativo.

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.

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

x	count_x
5	1
2
11
2
8
10
1

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

x	count_x
5	1
2	3
11
2
8
10
1

En la fila 3, x = 11, por lo que COUNT(*) solo incluirá las filas en las que 9 <= <= 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 denominadas cuyo window_name se puede referenciar en funciones analíticas en la lista SELECT. Esto es útil cuando deseas usar la misma window_frame_clause para varias 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 puedes usar ventanas denominadas en la cláusula ORDER BY. Las ventanas denominadas pueden aparecer por sí solas o 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 las cláusulas PARTITION BY, ORDER BY y window_frame_clause que están en la misma cláusula .

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, en una consulta externa o en cualquier subconsulta.
Una ventana W1 (denominada o no) 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 cláusula PARTITION BY de NW2, si existe una.
2. La cláusula ORDER BY de W1 o NW2, si se especifica alguno. No es posible que ambos tengan una cláusula .
3. La window_frame_clause de W1, si existe una.

En este tema, se explica cómo operan las funciones de navegación analítica. A fin de 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: Es opcional.
ORDER BY:
1. No permitido para PERCENTILE_CONT ni 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 ni 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 del 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.

A continuación, se muestra un 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 el RANK() se incrementa por la cantidad de pares en el grupo de orden de la ventana anterior.
DENSE_RANK(): para x = 5, dense_rank muestra 3, ya que el siempre aumenta en 1 y nunca omite un valor.
ROW_NUMBER():: para x = 5, row_num muestra 4.

Funciones analíticas de agregación

BigQuery admite determinadas funciones de agregación como 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, en especial 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.

Conceptos de funciones analíticas en SQL estándar

Sintaxis de la función analítica

Cláusula OVER

Cláusula PARTITION BY

Cláusula ORDER BY

Cláusula de marco de ventana

ROWS

RANGE

Cláusula WINDOW

Ventanas denominadas

Funciones de navegación

Funciones de numeración

Funciones analíticas de agregación