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ónRANK()
.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áusulaOVER
.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 listaSELECT
, su lista de argumentos y la cláusulaOVER
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áusulaORDER BY
de la consulta, su lista de argumentos puede hacer referencia a los alias de la listaSELECT
.
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:
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áusulaWINDOW
.
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.
- Se permiten varias expresiones de partición en la cláusula
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 requiereORDER 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 deORDER BY
ywindow_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 necesitasPARTITION 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:
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 claveORDER 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áusulaORDER 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ónUNIX_DATE()
. Si deseas usar un rango con una marca de tiempo, usa la funciónUNIX_SECONDS()
,UNIX_MILLIS()
oUNIX_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
ocurrent_row
.unbounded_preceding
: El marco de ventana comienza al inicio de la partición.numeric_preceding
onumeric_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
ounbounded_following
.numeric_preceding
onumeric_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.
- Define el comienzo del marco de ventana mediante
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
:
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:
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
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
: OpcionalORDER BY
:- No permitida para
PERCENTILE_CONT
niPERCENTILE_DISC
- Necesaria para
FIRST_VALUE
,LAST_VALUE
,NTH_VALUE
,LEAD
yLAG
- No permitida para
window_frame_clause
:- No permitida para
PERCENTILE_CONT
,PERCENTILE_DISC
,LEAD
niLAG
- Opcional para
FIRST_VALUE
,LAST_VALUE
yNTH_VALUE
- No permitida para
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 queRANK()
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 queDENSE_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 de agregación.
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)