Conceptos de las funciones analíticas en SQL estándar

Una función analítica calcula valores en un grupo de filas y muestra un solo resultado para cada fila. Esto es diferente de una función de agregación, que muestra un solo resultado para un grupo de filas.

Una función analítica incluye una cláusula OVER, que define una ventana de filas alrededor de la fila que se evalúa. En cada fila, el resultado de la función analítica se calcula con la ventana de filas seleccionada como entrada, que puede realizarse mediante una agregación.

Con las funciones analíticas, puedes calcular promedios móviles, clasificar elementos, calcular sumas acumulativas y realizar otros análisis.

Las siguientes funciones se pueden usar como funciones analíticas: funciones de navegación, funciones de numeración y funciones analíticas de agregación.

Sintaxis de las funciones analíticas

analytic_function_name ( [ argument_list ] ) OVER over_clause

over_clause:
  { named_window | ( [ window_specification ] ) }

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

window_frame_clause:
  { rows_range } { frame_start | frame_between }

rows_range:
  { ROWS | RANGE }

Reglas de notación

Los corchetes “[ ]” indican cláusulas opcionales.
Los paréntesis “( )” indican paréntesis literales.
La barra vertical “|” indica un OR lógico.
Las llaves “{ }” encierran un conjunto de opciones.
Una coma seguida de puntos suspensivos entre corchetes “[, … ]” indica que el elemento anterior se puede repetir en una lista separada por comas.

Descripción

Una función analítica calcula los resultados en un grupo de filas. Puedes usar la siguiente sintaxis para compilar una función analítica:

analytic_function_name: La función que realiza una operación analítica. Por ejemplo, aquí podría usarse la función de numeración RANK().
argument_list: Los argumentos específicos de la función analítica. Algunas funciones los tienen, otras no.
OVER: Una palabra clave que se requiere en la sintaxis de la función analítica anterior a la cláusula OVER.
over_clause: Hace referencia a una ventana que define un grupo de filas en una tabla en la que puedes usar una función analítica.
window_specification: Define las especificaciones de la ventana.
window_frame_clause: Define el marco de la ventana.
rows_range: Define las filas físicas o un rango lógico para un marco de ventana.

Notas

Una función analítica puede aparecer como un operando de expresión escalar en dos partes de la consulta:

En la lista SELECT. Si la función analítica aparece en la lista SELECT, su lista de argumentos y la cláusula OVER no se pueden referir a los alias ingresados en la misma lista SELECT.
En la cláusula ORDER BY. Si la función analítica aparece en la cláusula ORDER BY de la consulta, su lista de argumentos puede hacer referencia a los alias de la lista SELECT.

Una función analítica no puede hacer referencia a otra en su lista de argumentos ni en su cláusula OVER, ni siquiera de forma indirecta a través de un alias.

Una función analítica se evalúa después de la agregación. Por ejemplo, la cláusula GROUP BY y las funciones de agregación que no son analíticas se evalúan primero. Debido a que las funciones agregadas se evalúan antes que las funciones analíticas, se pueden usar como operandos de entrada de funciones analíticas.

Qué muestra

Un solo resultado para cada fila en la entrada.

Define la cláusula `OVER`

analytic_function_name ( [ argument_list ] ) OVER over_clause

over_clause:
  { named_window | ( [ window_specification ] ) }

Descripción

La cláusula OVER hace referencia a una ventana que define un grupo de filas de una tabla en la que se usa una función analítica. Puedes proporcionar una named_window que se defina en tu consulta, o puedes definir las especificaciones de una ventana nueva.

Notas

Si no se proporciona una ventana con nombre ni una especificación de ventana, todas las filas de entrada se incluyen en la ventana para cada fila.

Ejemplos en los que se usa la cláusula OVER

En estas consultas se usan especificaciones de ventana:

Calcula un total general
Calcula un subtotal
Calcula una suma acumulativa
Calcula un promedio móvil
Calcula la cantidad de elementos dentro de un rango
Obtén el elemento más popular de cada categoría
Obtén el último valor de un rango
Calcula la clasificación

En estas consultas se usa una ventana con nombre:

Obtén el último valor de un rango
Usa una ventana con nombre en una cláusula de marco de ventana

Define la especificación de la ventana

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

Descripción

Define las especificaciones de la ventana.

named_window: El nombre de una ventana existente que se definió con una cláusula WINDOW.

PARTITION BY: Divide las filas de entrada en particiones separadas, en las que la función analítica se evalúa de forma independiente.
- Se permiten varias expresiones de partición en la cláusula PARTITION BY.
- Una expresión no puede contener tipos de punto flotante, tipos que no se puedan agrupar, constantes o funciones analíticas.
- Si no se usa esta cláusula opcional, todas las filas de la tabla de entrada conformarán una sola partición.
ORDER BY: Define la forma en que se ordenan las filas en una partición. Esta cláusula es opcional en la mayoría de los casos, pero es obligatoria para las funciones de navegación.
window_frame_clause: En las funciones analíticas agregadas, define el marco de ventana dentro de la partición actual. El marco de ventana determina qué incluir en la ventana. Si se usa esta cláusula, se requiere ORDER BY, excepto para ventanas completamente ilimitadas.

Notas

Si no están presentes la cláusula ORDER BY ni la del marco de ventana, el marco de ventana incluye todas las filas de esa partición.

Para las funciones analíticas agregadas, si la cláusula ORDER BY está presente, pero la cláusula de marco de ventana no, se usa la siguiente cláusula de marco de ventana de forma predeterminada:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Por ejemplo, las siguientes consultas son equivalentes:

SELECT book, LAST_VALUE(item)
  OVER (ORDER BY year)
FROM Library

SELECT book, LAST_VALUE(item)
  OVER (
    ORDER BY year
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM Library

Reglas para usar una ventana con nombre en la especificación de la ventana

Si usas una ventana con nombre en las especificaciones de la ventana, se aplican las siguientes reglas:

Las especificaciones de la ventana con nombre se pueden extender con especificaciones nuevas que definas en la cláusula de especificación de la ventana.
No puedes tener definiciones redundantes. Si tienes una cláusula ORDER BY en la ventana con nombre y en la cláusula de especificación de la ventana, se genera un error.

El orden de las cláusulas es importante. PARTITION BY debe ir primero, seguida de ORDER BY y window_frame_clause. Si agregas una ventana con nombre, sus especificaciones se procesan primero.

--this works:
SELECT item, purchases, LAST_VALUE(item)
  OVER (item_window ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS most_popular
FROM Produce
WINDOW item_window AS (ORDER BY purchases)

--this does not work:
SELECT item, purchases, LAST_VALUE(item)
  OVER (item_window ORDER BY purchases) AS most_popular
FROM Produce
WINDOW item_window AS (ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)

Una ventana con nombre y PARTITION BY no pueden aparecer juntas en la especificación de la ventana. Si necesitas PARTITION BY, agrégala a la ventana con nombre.
No puedes hacer referencia a una ventana con nombre en una cláusula ORDER BY, una consulta externa o cualquier subconsulta.

Ejemplos en los que se usa la especificación de la ventana

Mediante estas consultas, se definen particiones en una función analítica:

Calcula un subtotal
Calcula una suma acumulativa
Obtén el elemento más popular de cada categoría
Obtén el último valor de un rango
Calcula la clasificación
Usa una ventana con nombre en una cláusula de marco de ventana

Estas consultas incluyen una ventana con nombre en una especificación de la ventana:

Obtén el último valor de un rango
Usa una ventana con nombre en una cláusula de marco de ventana

Con estas consultas, se define cómo se ordenan las filas en una partición:

Calcula un subtotal
Calcula una suma acumulativa
Calcula un promedio móvil
Calcula la cantidad de elementos dentro de un rango
Obtén el elemento más popular de cada categoría
Obtén el último valor de un rango
Calcula la clasificación
Usa una ventana con nombre en una cláusula de marco de ventana

Define la cláusula de marco de ventana

window_frame_clause:
  { rows_range } { frame_start | frame_between }

rows_range:
  { ROWS | RANGE }

frame_between:
  {
    BETWEEN  unbounded_preceding AND frame_end_a
    | BETWEEN numeric_preceding AND frame_end_a
    | BETWEEN current_row AND frame_end_b
    | BETWEEN numeric_following AND frame_end_c
  }

frame_start:
  { unbounded_preceding | numeric_preceding | [ current_row ] }

frame_end_a:
  { numeric_preceding | current_row | numeric_following | unbounded_following }

frame_end_b:
  { current_row | numeric_following | unbounded_following }

frame_end_c:
  { numeric_following | unbounded_following }

unbounded_preceding:
  UNBOUNDED PRECEDING

numeric_preceding:
  numeric_expression PRECEDING

unbounded_following:
  UNBOUNDED FOLLOWING

numeric_following:
  numeric_expression FOLLOWING

current_row:
  CURRENT ROW

La cláusula de marco de ventana define el marco de ventana de la fila actual dentro de una partición, en la que se evalúa la función analítica. Solo las funciones analíticas agregadas pueden usar una cláusula de marco de ventana.

rows_range: Es una cláusula que define un marco de ventana con filas físicas o un rango lógico.
- ROWS: Calcula el marco de ventana en función de desplazamientos físicos desde la fila actual. Por ejemplo, puedes incluir dos filas antes y después de la fila actual.
- RANGE: Calcula el marco de ventana en función de un rango lógico de filas de la fila actual, según el valor de clave ORDER BY de la fila actual. El valor del rango proporcionado se suma al valor de clave de la fila actual o se resta de este a fin de definir un límite de rango inicial o final para el marco de ventana. En un marco de ventana basado en rangos, debe haber, con exactitud, una expresión en la cláusula ORDER BY, y la expresión debe tener un tipo numérico.
Sugerencia: Si deseas usar un rango con una fecha, usa ORDER BY con la función UNIX_DATE(). Si deseas usar un rango con una marca de tiempo, usa la función UNIX_SECONDS(), UNIX_MILLIS() o UNIX_MICROS().
frame_between: Crea un marco de ventana con un límite inferior y superior. El primer límite representa el límite inferior. El segundo límite representa el límite superior. Solo se pueden usar ciertas combinaciones de límites, como se muestra en la sintaxis anterior.
- Define el comienzo del marco de ventana mediante unbounded_preceding, numeric_preceding, numeric_following o current_row.
  - unbounded_preceding: El marco de ventana comienza al inicio de la partición.
  - numeric_preceding o numeric_following: El inicio del marco de ventana es relativo a la fila actual.
  - current_row: El marco de ventana comienza en la fila actual.
- Define el final del marco de ventana mediante numeric_preceding, numeric_following, current_row o unbounded_following.
  - numeric_preceding o numeric_following: El final del marco de ventana es relativo a la fila actual.
  - current_row: El marco de ventana finaliza en la fila actual.
  - unbounded_following: El marco de ventana termina al final de la partición.
frame_start: Crea un marco de ventana con un límite inferior. El marco de ventana finaliza en la fila actual.
- unbounded_preceding: El marco de ventana comienza al inicio de la partición.
- numeric_preceding: El inicio del marco de ventana es relativo a la fila actual.
- current_row: El marco de ventana comienza en la fila actual.
numeric_expression: Es una expresión que representa un tipo numérico. La expresión numérica debe ser un parámetro o número entero constante que no sea negativo.

Notas

Si un límite se extiende más allá del comienzo o del final de una partición, el marco de ventana solo incluirá filas provenientes de esa partición.

No puedes usar una cláusula de marco de ventana con funciones de navegación y funciones de numeración, como RANK().

Ejemplos en los que se usa la cláusula de marco de ventana

Con estas consultas se calculan valores con ROWS:

Calcula una suma acumulativa
Calcula un promedio móvil
Obtén el elemento más popular de cada categoría
Obtén el último valor de un rango
Usa una ventana con nombre en una cláusula de marco de ventana

Con estas consultas se calculan valores con RANGE:

Calcula la cantidad de elementos dentro de un rango

Con estas consultas se calculan valores con una ventana parcial o completamente ilimitada:

Calcula un total general
Calcula un subtotal
Calcula una suma acumulativa
Obtén el elemento más popular de cada categoría
Calcula la clasificación

Con estas consultas se calculan valores con límites numéricos:

Calcula una suma acumulativa
Calcula un promedio móvil
Calcula la cantidad de elementos dentro de un rango
Obtén el último valor de un rango
Usa una ventana con nombre en una cláusula de marco de ventana

Con estas consultas se calculan valores con la fila actual como límite:

Calcula un total general
Calcula un subtotal
Calcula una suma acumulativa

Haz referencia a una ventana con nombre

SELECT query_expr,
  analytic_function_name ( [ argument_list ] ) OVER over_clause
FROM from_item
WINDOW named_window_expression [, ...]

over_clause:
  { named_window | ( [ window_specification ] ) }

window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ ASC | DESC [, ...] ]
  [ window_frame_clause ]

named_window_expression:
  named_window AS { named_window | ( [ window_specification ] ) }

Una ventana con nombre representa un grupo de filas en una tabla en la que se usa una función analítica. Una ventana con nombre se define en la cláusula WINDOW, y se hace referencia a ella en la cláusula OVER de una función analítica. En una cláusula OVER, una ventana con nombre puede aparecer sola o incorporada en una especificación de la ventana.

Ejemplos

Obtén el último valor de un rango
Usa una ventana con nombre en una cláusula de marco de ventana

Conceptos de las funciones de navegación

Las funciones de navegación, por lo general, calculan alguna 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.

Requisitos para la cláusula OVER:

PARTITION BY: Opcional
ORDER BY:
1. No permitida para PERCENTILE_CONT ni PERCENTILE_DISC
2. Necesaria para FIRST_VALUE, LAST_VALUE, NTH_VALUE, LEAD y LAG
window_frame_clause:
1. No permitida 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.

Conceptos de las 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 dentro de la ventana especificada.

A continuación, se muestra un ejemplo de RANK(), DENSE_RANK() y ROW_NUMBER():

WITH Numbers AS
 (SELECT 1 as x
  UNION ALL SELECT 2
  UNION ALL SELECT 2
  UNION ALL SELECT 5
  UNION ALL SELECT 8
  UNION ALL SELECT 10
  UNION ALL SELECT 10
)
SELECT x,
  RANK() OVER (ORDER BY x ASC) AS rank,
  DENSE_RANK() OVER (ORDER BY x ASC) AS dense_rank,
  ROW_NUMBER() OVER (ORDER BY x) AS row_num
FROM Numbers

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

Conceptos de las funciones analíticas de agregación

Una función agregada es una función que realiza un cálculo en un conjunto de valores. La mayoría de las funciones agregadas se pueden usar en una función analítica. Estas funciones de agregación se denominan funciones analíticas de agregación.

Con estas funciones, la cláusula OVER se adjunta a la llamada a función de agregación. Por lo demás, la sintaxis de la llamada a función permanece sin cambios. Al igual que sus equivalentes de las funciones de agregación, estas funciones analíticas realizan agregaciones, en especial en el marco de ventana relevante de cada fila. Los tipos de datos del resultado de estas funciones analíticas son los mismos que sus equivalentes de las funciones agregadas.

Filtra resultados con la cláusula QUALIFY

La cláusula QUALIFY se puede usar para filtrar los resultados de una función analítica. Para obtener más información y ejemplos, consulta la cláusula QUALIFY.

Ejemplos de funciones analíticas

En estos ejemplos el elemento destacado es la fila actual. Los elementos en negrita son las filas que se incluyen en el análisis.

Tablas comunes que se usan en los ejemplos

Las siguientes tablas se usan en los ejemplos de consultas analíticas agregadas posteriores: Produce, Employees y Farm.

Tabla Produce

En algunos ejemplos se hace referencia a una tabla llamada Produce:

WITH Produce AS
 (SELECT 'kale' as item, 23 as purchases, 'vegetable' as category
  UNION ALL SELECT 'orange', 2, 'fruit'
  UNION ALL SELECT 'cabbage', 9, 'vegetable'
  UNION ALL SELECT 'apple', 8, 'fruit'
  UNION ALL SELECT 'leek', 2, 'vegetable'
  UNION ALL SELECT 'lettuce', 10, 'vegetable')
SELECT * FROM Produce

+-------------------------------------+
| item      | category   | purchases  |
+-------------------------------------+
| kale      | vegetable  | 23         |
| orange    | fruit      | 2          |
| cabbage   | vegetable  | 9          |
| apple     | fruit      | 8          |
| leek      | vegetable  | 2          |
| lettuce   | vegetable  | 10         |
+-------------------------------------+

Tabla Employees

En algunos ejemplos se hace referencia a una tabla llamada Employees:

WITH Employees AS
 (SELECT 'Isabella' as name, 2 as department, DATE(1997, 09, 28) as start_date
  UNION ALL SELECT 'Anthony', 1, DATE(1995, 11, 29)
  UNION ALL SELECT 'Daniel', 2, DATE(2004, 06, 24)
  UNION ALL SELECT 'Andrew', 1, DATE(1999, 01, 23)
  UNION ALL SELECT 'Jacob', 1, DATE(1990, 07, 11)
  UNION ALL SELECT 'Jose', 2, DATE(2013, 03, 17))
SELECT * FROM Employees

+-------------------------------------+
| name      | department | start_date |
+-------------------------------------+
| Isabella  | 2          | 1997-09-28 |
| Anthony   | 1          | 1995-11-29 |
| Daniel    | 2          | 2004-06-24 |
| Andrew    | 1          | 1999-01-23 |
| Jacob     | 1          | 1990-07-11 |
| Jose      | 2          | 2013-03-17 |
+-------------------------------------+

Tabla Farm

En algunos ejemplos se hace referencia a una tabla llamada Farm:

WITH Farm AS
 (SELECT 'cat' as animal, 23 as population, 'mammal' as category
  UNION ALL SELECT 'duck', 3, 'bird'
  UNION ALL SELECT 'dog', 2, 'mammal'
  UNION ALL SELECT 'goose', 1, 'bird'
  UNION ALL SELECT 'ox', 2, 'mammal'
  UNION ALL SELECT 'goat', 2, 'mammal')
SELECT * FROM Farm

+-------------------------------------+
| animal    | category   | population |
+-------------------------------------+
| cat       | mammal     | 23         |
| duck      | bird       | 3          |
| dog       | mammal     | 2          |
| goose     | bird       | 1          |
| ox        | mammal     | 2          |
| goat      | mammal     | 2          |
+-------------------------------------+

Calcula un total general

Esto calcula un total general para todos los elementos de la tabla Produce.

(orange [naranja], apple [manzana], leek [puerro], cabbage [repollo], lettuce [lechuga], kale [col rizada]) = 54 compras en total
(orange [naranja], apple [manzana], leek [puerro], cabbage [repollo], lettuce [lechuga], kale [col rizada]) = 54 compras en total
(orange [naranja], apple [manzana], leek [puerro], cabbage [repollo], lettuce [lechuga], kale [col rizada]) = 54 compras en total
(orange [naranja], apple [manzana], leek [puerro], cabbage [repollo], lettuce [lechuga], kale [col rizada]) = 54 compras en total
(orange [naranja], apple [manzana], leek [puerro], cabbage [repollo], lettuce [lechuga], kale [col rizada]) = 54 compras en total
(orange [naranja], apple [manzana], leek [puerro], cabbage [repollo], lettuce [lechuga], kale [col rizada]) = 54 compras en total

SELECT item, purchases, category, SUM(purchases)
  OVER () AS total_purchases
FROM Produce

+-------------------------------------------------------+
| item      | purchases  | category   | total_purchases |
+-------------------------------------------------------+
| orange    | 2          | fruit      | 54              |
| leek      | 2          | vegetable  | 54              |
| apple     | 8          | fruit      | 54              |
| cabbage   | 9          | vegetable  | 54              |
| lettuce   | 10         | vegetable  | 54              |
| kale      | 23         | vegetable  | 54              |
+-------------------------------------------------------+

Calcula un subtotal

Esto calcula un subtotal para cada categoría en la tabla Produce.

fruit (fruta)
- (orange [naranja], apple [manzana]) = 10 compras en total
- (orange [naranja], apple [manzana]) = 10 compras en total
vegetable (verdura)
- (leek [puerro], cabbage [repollo], lettuce [lechuga], kale [col rizada]) = 44 compras en total
- (leek [puerro], cabbage [repollo], lettuce [lechuga], kale [col rizada]) = 44 compras en total
- (leek [puerro], cabbage [repollo], lettuce [lechuga], kale [col rizada]) = 44 compras en total
- (leek [puerro], cabbage [repollo], lettuce [lechuga], kale [col rizada]) = 44 compras en total

SELECT item, purchases, category, SUM(purchases)
  OVER (
    PARTITION BY category
    ORDER BY purchases
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS total_purchases
FROM Produce

+-------------------------------------------------------+
| item      | purchases  | category   | total_purchases |
+-------------------------------------------------------+
| orange    | 2          | fruit      | 10              |
| apple     | 8          | fruit      | 10              |
| leek      | 2          | vegetable  | 44              |
| cabbage   | 9          | vegetable  | 44              |
| lettuce   | 10         | vegetable  | 44              |
| kale      | 23         | vegetable  | 44              |
+-------------------------------------------------------+

Calcula una suma acumulativa

Esto calcula una suma acumulativa para cada categoría en la tabla Produce. La suma se calcula según el orden que se definió con la cláusula ORDER BY.

fruit (fruta)
- (orange [naranja], apple [manzana]) = 2 compras en total
- (orange [naranja], apple [manzana]) = 10 compras en total
vegetable (verdura)
- (leek [puerro], cabbage [repollo], lettuce [lechuga], kale [col rizada]) = 2 compras en total
- (leek [puerro], cabbage [repollo], lettuce [lechuga], kale [col rizada]) = 11 compras en total
- (leek [puerro], cabbage [repollo], lettuce [lechuga], kale [col rizada]) = 21 compras en total
- (leek [puerro], cabbage [repollo], lettuce [lechuga], kale [col rizada]) = 44 compras en total

SELECT item, purchases, category, SUM(purchases)
  OVER (
    PARTITION BY category
    ORDER BY purchases
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS total_purchases
FROM Produce

+-------------------------------------------------------+
| item      | purchases  | category   | total_purchases |
+-------------------------------------------------------+
| orange    | 2          | fruit      | 2               |
| apple     | 8          | fruit      | 10              |
| leek      | 2          | vegetable  | 2               |
| cabbage   | 9          | vegetable  | 11              |
| lettuce   | 10         | vegetable  | 21              |
| kale      | 23         | vegetable  | 44              |
+-------------------------------------------------------+

Esto hace lo mismo que el ejemplo anterior. No es necesario que agregues CURRENT ROW como límite, a menos que desees facilitar la lectura.

SELECT item, purchases, category, SUM(purchases)
  OVER (
    PARTITION BY category
    ORDER BY purchases
    ROWS UNBOUNDED PRECEDING
  ) AS total_purchases
FROM Produce

En este ejemplo, todos los elementos de la tabla Produce se incluyen en la partición. Solo se analizan las filas anteriores. El análisis comienza dos filas antes de la fila actual en la partición.

(orange [naranja], leek [puerro], apple [manzana], cabbage [repollo], lettuce [lechuga], kale [col rizada]) = NULL (nulo)
(orange [naranja], leek [puerro], apple [manzana], cabbage [repollo], lettuce [lechuga], kale [col rizada]) = NULL (nulo)
(orange [naranja], leek [puerro], apple [manzana], cabbage [repollo], lettuce [lechuga], kale [col rizada]) = 2
(orange [naranja], leek [puerro], apple [manzana], cabbage [repollo], lettuce [lechuga], kale [col rizada]) = 4
(orange [naranja], leek [puerro], apple [manzana], cabbage [repollo], lettuce [lechuga], kale [col rizada]) = 12
(orange [naranja], leek [puerro], apple [manzana], cabbage [repollo], lettuce [lechuga], kale [col rizada]) = 21

SELECT item, purchases, category, SUM(purchases)
  OVER (
    ORDER BY purchases
    ROWS BETWEEN UNBOUNDED PRECEDING AND 2 PRECEDING
  ) AS total_purchases
FROM Produce;

+-------------------------------------------------------+
| item      | purchases  | category   | total_purchases |
+-------------------------------------------------------+
| orange    | 2          | fruit      | NULL            |
| leek      | 2          | vegetable  | NULL            |
| apple     | 8          | fruit      | 2               |
| cabbage   | 9          | vegetable  | 4               |
| lettuce   | 10         | vegetable  | 12              |
| kale      | 23         | vegetable  | 21              |
+-------------------------------------------------------+

Calcular una media móvil

Esto calcula un promedio móvil en la tabla Produce. El límite inferior es 1 fila antes de la fila actual. El límite superior es 1 fila después de la fila actual.

(orange [naranja], leek [puerro], apple [manzana], cabbage [repollo], lettuce [lechuga], kale [col rizada]) = 2 compras promedio
(orange [naranja], leek [puerro], apple [manzana], cabbage [repollo], lettuce [lechuga], kale [col rizada]) = 4 compras promedio
(orange [naranja], leek [puerro], apple [manzana], cabbage [repollo], lettuce [lechuga], kale [col rizada]) = 6.3333 compras promedio
(orange [naranja], leek [puerro], apple [manzana], cabbage [repollo], lettuce [lechuga], kale [col rizada]) = 9 compras promedio
(orange [naranja], leek [puerro], apple [manzana], cabbage [repollo], lettuce [lechuga], kale [col rizada]) = 14 compras promedio
(orange [naranja], leek [puerro], apple [manzana], cabbage [repollo], lettuce [lechuga], kale [col rizada]) = 16.5 compras promedio

SELECT item, purchases, category, AVG(purchases)
  OVER (
    ORDER BY purchases
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS avg_purchases
FROM Produce

+-------------------------------------------------------+
| item      | purchases  | category   | avg_purchases   |
+-------------------------------------------------------+
| orange    | 2          | fruit      | 2               |
| leek      | 2          | vegetable  | 4               |
| apple     | 8          | fruit      | 6.33333         |
| cabbage   | 9          | vegetable  | 9               |
| lettuce   | 10         | vegetable  | 14              |
| kale      | 23         | vegetable  | 16.5            |
+-------------------------------------------------------+

Calcula la cantidad de elementos dentro de un rango

En este ejemplo, se obtiene la cantidad de animales que tienen un recuento de población similar en la tabla Farm.

(goose [ganso], dog [perro], ox [buey], goat [cabra], duck [pato], cat [gato]) = 4 animales entre el rango de población de 0 a 2.
(goose [ganso], dog [perro], ox [buey], goat [cabra], duck [pato], cat [gato]) = 5 animales entre el rango de población de 1 a 3.
(goose [ganso], dog [perro], ox [buey], goat [cabra], duck [pato], cat [gato]) = 5 animales entre el rango de población de 1 a 3.
(goose [ganso], dog [perro], ox [buey], goat [cabra], duck [pato], cat [gato]) = 5 animales entre el rango de población de 1 a 3.
(goose [ganso], dog [perro], ox [buey], goat [cabra], duck [pato], cat [gato]) = 4 animales entre el rango de población de 2 a 4.
(goose [ganso], dog [perro], ox [buey], goat [cabra], duck [pato], cat [gato]) = 1 animal entre el rango de población de 22 a 24.

SELECT animal, population, category, COUNT(*)
  OVER (
    ORDER BY population
    RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS similar_population
FROM Farm;

+----------------------------------------------------------+
| animal    | population | category   | similar_population |
+----------------------------------------------------------+
| goose     | 1          | bird       | 4                  |
| dog       | 2          | mammal     | 5                  |
| ox        | 2          | mammal     | 5                  |
| goat      | 2          | mammal     | 5                  |
| duck      | 3          | bird       | 4                  |
| cat       | 23         | mammal     | 1                  |
+----------------------------------------------------------+

Obtén el elemento más popular de cada categoría

En este ejemplo, se obtiene el elemento más popular de cada categoría. Define cómo se particionan y ordenan las filas de una ventana en cada partición. Se hace referencia a la tabla Produce.

fruit (fruta)
- (orange [naranja], apple [manzana]) = apple (manzana) es el elemento más popular
- (orange [naranja], apple [manzana]) = apple (manzana) es el elemento más popular
vegetable (verdura)
- (leek [puerro], cabbage [repollo], lettuce [lechuga], kale [col rizada]) = kale (col rizada) es el elemento más popular
- (leek [puerro], cabbage [repollo], lettuce [lechuga], kale [col rizada]) = kale (col rizada) es el elemento más popular
- (leek [puerro], cabbage [repollo], lettuce [lechuga], kale [col rizada]) = kale (col rizada) es el elemento más popular
- (leek [puerro], cabbage [repollo], lettuce [lechuga], kale [col rizada]) = kale (col rizada) es el elemento más popular

SELECT item, purchases, category, LAST_VALUE(item)
  OVER (
    PARTITION BY category
    ORDER BY purchases
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS most_popular
FROM Produce

+----------------------------------------------------+
| item      | purchases  | category   | most_popular |
+----------------------------------------------------+
| orange    | 2          | fruit      | apple        |
| apple     | 8          | fruit      | apple        |
| leek      | 2          | vegetable  | kale         |
| cabbage   | 9          | vegetable  | kale         |
| lettuce   | 10         | vegetable  | kale         |
| kale      | 23         | vegetable  | kale         |
+----------------------------------------------------+

Obtén el último valor de un rango

En este ejemplo, se obtiene el elemento más popular de un marco de ventana específico mediante la tabla Produce. El marco de ventana analiza hasta tres filas a la vez. Revisa la columna most_popular para ver las verduras. En lugar de obtener el elemento más popular de una categoría específica, obtiene el elemento más popular en un rango específico de esa categoría.

fruit (fruta)
- (orange [naranja], apple [manzana]) = apple (manzana) es el elemento más popular
- (orange [naranja], apple [manzana]) = apple (manzana) es el elemento más popular
vegetable (verdura)
- (leek [puerro], cabbage [repollo], lettuce [lechuga], kale [col rizada]) = cabbage (repollo) es el elemento más popular
- (leek [puerro], cabbage [repollo], lettuce [lechuga], kale [col rizada]) = lettuce (lechuga) es el elemento más popular
- (leek [puerro], cabbage [repollo], lettuce [lechuga], kale [col rizada]) = kale (col rizada) es el elemento más popular
- (leek [puerro], cabbage [repollo], lettuce [lechuga], kale [col rizada]) = kale (col rizada) es el elemento más popular

SELECT item, purchases, category, LAST_VALUE(item)
  OVER (
    PARTITION BY category
    ORDER BY purchases
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS most_popular
FROM Produce

+----------------------------------------------------+
| item      | purchases  | category   | most_popular |
+----------------------------------------------------+
| orange    | 2          | fruit      | apple        |
| apple     | 8          | fruit      | apple        |
| leek      | 2          | vegetable  | cabbage      |
| cabbage   | 9          | vegetable  | lettuce      |
| lettuce   | 10         | vegetable  | kale         |
| kale      | 23         | vegetable  | kale         |
+----------------------------------------------------+

En este ejemplo, se muestran los mismos resultados que en el ejemplo anterior, pero se incluye una ventana con nombre llamada item_window. Algunas de las especificaciones de la ventana se definen directamente en la cláusula OVER y otras en la ventana con nombre.

SELECT item, purchases, category, LAST_VALUE(item)
  OVER (
    item_window
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS most_popular
FROM Produce
WINDOW item_window AS (
  PARTITION BY category
  ORDER BY purchases)

Calcula la clasificación

En este ejemplo, se calcula la clasificación de cada empleado en su departamento, según su fecha de inicio. La especificación de la ventana se define directamente en la cláusula OVER. Se hace referencia a la tabla Employees.

department 1 (departamento 1)
- (Jacob, Anthony, Andrew) = Asigna la clasificación 1 a Jacob
- (Jacob, Anthony, Andrew) = Asigna la clasificación 2 a Anthony
- (Jacob, Anthony, Andrew) = Asigna la clasificación 3 a Andrew
department 2 (departamento 2)
- (Isabella, Daniel, Jose) = Asigna la clasificación 1 a Isabella
- (Isabella, Daniel, Jose) = Asigna la clasificación 2 a Daniel
- (Isabella, Daniel, Jose) = Asigna la clasificación 3 a Jose

SELECT name, department, start_date,
  RANK() OVER (PARTITION BY department ORDER BY start_date) AS rank
FROM Employees;

+--------------------------------------------+
| name      | department | start_date | rank |
+--------------------------------------------+
| Jacob     | 1          | 1990-07-11 | 1    |
| Anthony   | 1          | 1995-11-29 | 2    |
| Andrew    | 1          | 1999-01-23 | 3    |
| Isabella  | 2          | 1997-09-28 | 1    |
| Daniel    | 2          | 2004-06-24 | 2    |
| Jose      | 2          | 2013-03-17 | 3    |
+--------------------------------------------+

Usa una ventana con nombre en una cláusula de marco de ventana

Puedes definir parte de tu lógica en una ventana con nombre y el resto en una cláusula de marco de ventana. Esta lógica se combina. A continuación, se muestra un ejemplo en el que se usa la tabla Produce.

SELECT item, purchases, category, LAST_VALUE(item)
  OVER (item_window) AS most_popular
FROM Produce
WINDOW item_window AS (
  PARTITION BY category
  ORDER BY purchases
  ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)

+-------------------------------------------------------+
| item      | purchases  | category   | most_popular    |
+-------------------------------------------------------+
| orange    | 2          | fruit      | apple           |
| apple     | 8          | fruit      | apple           |
| leek      | 2          | vegetable  | lettuce         |
| cabbage   | 9          | vegetable  | kale            |
| lettuce   | 10         | vegetable  | kale            |
| kale      | 23         | vegetable  | kale            |
+-------------------------------------------------------+

También puedes obtener los resultados anteriores con estos ejemplos:

SELECT item, purchases, category, LAST_VALUE(item)
  OVER (item_window) AS most_popular
FROM Produce
WINDOW
  a AS (PARTITION BY category),
  b AS (a ORDER BY purchases),
  c AS (b ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING),
  item_window AS (c)

SELECT item, purchases, category, LAST_VALUE(item)
  OVER (item_window ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS most_popular
FROM Produce
WINDOW
  a AS (PARTITION BY category),
  b AS (a ORDER BY purchases),
  item_window AS (b)

Con el siguiente ejemplo, se produce un error porque una cláusula de marco de ventana se definió dos veces:

SELECT item, purchases, category, LAST_VALUE(item)
  OVER (
    item_window
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS most_popular
FROM Produce
WINDOW item_window AS (
  ORDER BY purchases
  ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)

Conceptos de las funciones analíticas en SQL estándar

Sintaxis de las funciones analíticas

Define la cláusula OVER

Define la especificación de la ventana

Define la cláusula de marco de ventana

Haz referencia a una ventana con nombre

Conceptos de las funciones de navegación

Conceptos de las funciones de numeración

Conceptos de las funciones analíticas de agregación

Filtra resultados con la cláusula QUALIFY

Ejemplos de funciones analíticas

Tablas comunes que se usan en los ejemplos

Tabla Produce

Tabla Employees

Tabla Farm

Calcula un total general

Calcula un subtotal

Calcula una suma acumulativa

Calcular una media móvil

Calcula la cantidad de elementos dentro de un rango

Obtén el elemento más popular de cada categoría

Obtén el último valor de un rango

Calcula la clasificación

Usa una ventana con nombre en una cláusula de marco de ventana

Define la cláusula `OVER`