Una función analítica calcula valores sobre un grupo de filas y devuelve un resultado único para cada fila. Esto es diferente de una función de agregado, que devuelve un único 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 está evaluando. Para cada fila, el resultado de la función analítica se calcula utilizando la ventana de filas seleccionada como entrada, haciendo posible la agregación.

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

Las siguientes funciones se pueden utilizar como funciones analíticas:funciones de navegación,funciones de numeración y funciones analíticas agregadas

Sintaxis de funciones analíticas

analytic_function_name ( ) OVER over_clauseover_clause: { named_window | ( ) }window_specification: ] ] window_frame_clause: { rows_range } { frame_start | frame_between }rows_range: { ROWS | RANGE }

Reglas de notación

• Corchetes «» indican cláusulas opcionales.
• Paréntesis «() » indican paréntesis literales.
• La barra vertical » | » indica un OR lógico.
• Llaves «{} » encierran un conjunto de opciones.
• Una coma seguida de puntos suspensivos entre corchetes «» indica que el elemento precedente puede repetirse en una lista separada por comas.

Descripción

Una función analítica calcula los resultados sobre un grupo de filas. Puede utilizar la siguiente sintaxis para crear una función analítica:

• analytic_function_name : La función que realiza una operación analítica.Por ejemplo, la función de numeración RANK() podría utilizarse aquí.
• argument_list: Argumentos específicos de la función analítica.Algunas funciones las tienen, otras no.
• OVER: Palabra clave requerida en la sintaxis de la función analítica que precede a la cláusula OVER.
• over_clause: Hace referencia a una ventana que define un grupo de filas en una tabla en la que usar una función analítica.
• window_specification: Define las especificaciones de la ventana.
• window_frame_clause: Define el marco de ventana para la ventana.
• rows_range: Define las filas físicas o el rango alógico de un marco de ventana.

Notas

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

• La lista SELECT. Si la función analítica aparece en la lista SELECT, su lista de argumentos y la cláusula OVER no pueden hacer referencia a los alias introducidos en la misma lista de selección.
• La cláusulaORDER 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 función analítica en su lista de argumentos ni a su cláusula OVER, ni siquiera indirectamente a través de un alias.

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

Devuelve

Un único resultado para cada fila de la entrada.

Definir la cláusula OVER

analytic_function_name ( ) OVER over_clauseover_clause: { named_window | ( ) }

Descripción

La cláusula OVER hace referencia a una ventana que define un grupo de filas en una tabla para usar una función analítica. Puede proporcionar unnamed_window que esté definido en su consulta, o puede definir las especificaciones para una nueva ventana.

Notas

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

Ejemplos que utilizan la cláusula OVER

Estas consultas utilizan especificaciones de ventana:

• Calcular un total general
• Calcular un subtotal
• Calcular una suma acumulativa
• Calcular una media móvil
• Calcular el número de elementos dentro de un rango
• Obtener el elemento más popular de cada categoría
• Obtener el último valor de un rango
• Calcular rango

Estas consultas usan una ventana con nombre:

• Obtener el último valor de un rango
• Usar una ventana con nombre en una cláusula de marco de ventana

Definir la especificación de la ventana

window_specification: ] ] 

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, sobre las que la función analítica se evalúa de forma independiente.
  • Se permiten múltiples expresiones de partición en la cláusula PARTITION BY.
  • Una expresión no puede contener tipos de coma flotante, tipos no agrupables,constantes o funciones analíticas.
  • Si no se utiliza esta cláusula opcional, todas las filas de la tabla de entrada constituyen una sola partición.
• ORDER BY: Define cómo se ordenan las filas dentro de una partición.Esta cláusula es opcional en la mayoría de las situaciones, pero se requiere en algunos casos para las funciones de navegación.
• window_frame_clause: Para funciones analíticas agregadas, define el marco de ventana dentro de la partición actual.El marco de la ventana determina qué incluir en la ventana.Si se utiliza esta cláusula, se requiere ORDER BY, excepto para ventanas con límites completos.

Notas

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

Para funciones analíticas agregadas, si la cláusula ORDER BY está presente, pero la cláusula marco de ventana no lo está, la siguiente cláusula marco de ventana se utiliza 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 ventana

Si usa una ventana con nombre en sus especificaciones de ventana, se aplican estas reglas:

• Las especificaciones de la ventana con nombre se pueden ampliar con nuevas especificaciones que defina en la cláusula de especificación de ventana.
• No puede tener definiciones redundantes. Si tiene una cláusula ORDER BY en la ventana con nombre y la cláusula de especificación de ventana, se lanza un error.

• El orden de las cláusulas importa. PARTITION BY debe venir en primer lugar,seguido por ORDER BY y window_frame_clause. Si agrega una ventana con nombre, sus especificaciones de ventana se procesan primero.

  --this works:SELECT item, purchases, LAST_VALUE(item) OVER (item_window ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS most_popularFROM ProduceWINDOW item_window AS (ORDER BY purchases)--this does not work:SELECT item, purchases, LAST_VALUE(item) OVER (item_window ORDER BY purchases) AS most_popularFROM ProduceWINDOW 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 ventana. Si necesita PARTITION BY, agréguelo a la ventana con nombre.

• No se puede hacer referencia a una ventana con nombre en una cláusula ORDER BY, una consulta externa o cualquier subconsulta.

Ejemplos usando la especificación de ventana

Estas consultas definen particiones en una función analítica:

• Calcular un subtotal
• Calcular una suma acumulativa
• Obtener el elemento más popular de cada categoría
• Obtener el último valor en un rango
• Calcular rango
• Usar una ventana con nombre en una cláusula de marco de ventana

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

• Obtener el último valor de un rango
• Usar una cláusula de ventana con nombre en un marco de ventana

Estas consultas definen cómo se ordenan las filas en una partición:

• Calcular un subtotal
• Calcular una suma acumulativa
• Calcular una media móvil
• Calcular el número de elementos dentro de un rango
• Obtener el elemento más popular de cada categoría
• Obtener el último valor en un rango
• Calcular rango
• Utilizar una ventana con nombre en una cláusula de marco de ventana

Definir 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 | }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 PRECEDINGnumeric_preceding: numeric_expression PRECEDINGunbounded_following: UNBOUNDED FOLLOWINGnumeric_following: numeric_expression FOLLOWINGcurrent_row: CURRENT ROW

La cláusula marco de ventana define el marco de ventana alrededor de la fila actual dentro de una partición, sobre 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: Una cláusula que define un marco de ventana con filas físicas o un rango lógico.

  • ROWS: Calcula el marco de la ventana en función de las compensaciones físicas de la fila actual. Por ejemplo, puede 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 alrededor de la fila actual, en función del valor de clave ORDER BY de la fila actual.El valor de rango proporcionado se agrega o resta al valor de la fila actual para definir un límite de rango inicial o final para el marco de ventana. En un marco de ventana basado en rango, debe haber exactamente una expresión en la cláusula ORDER BY, y la expresión debe tener tipo anumerico.

  Consejo: Si desea utilizar un rango con una fecha, utilice ORDER BY con la funciónUNIX_DATE(). Si desea utilizar un rango con una marca de tiempo,utilice 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.

  • Definir el principio del marco de la ventana con la etiqueta unbounded_precedingnumeric_precedingnumeric_following o current_row.
    • unbounded_preceding : El marco de la ventana comienza al principio de la partición.
    • numeric_precedingor numeric_following: El inicio del marco de la ventana es relativo a la fila actual.
    • current_row: el marco de La ventana, comienza en la fila actual.
  • Definir el final del marco de la ventana con la etiqueta numeric_precedingnumeric_followingcurrent_row o unbounded_following.
    • numeric_preceding o numeric_following: el final de La windowframe es relativo a la fila actual.
    • current_row: El marco de la ventana termina en la fila actual.
    • unbounded_following : El marco de la ventana termina al final de la partición.

• frame_start: Crea un marco de la ventana con un límite inferior.El marco de la ventana termina en la fila actual.

  • unbounded_preceding : El marco de la ventana comienza al principio de la partición.
  • numeric_preceding : El inicio del marco de ventana es relativo a la fila actual.
  • current_row: el marco de La ventana, comienza en la fila actual.

• numeric_expression: Una expresión que representa un tipo numérico.La expresión numérica debe ser un parámetro de enteros constante y no negativo.

Notas

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

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

Ejemplos que utilizan la cláusula marco de ventana

Estas consultas calculan valores con ROWS:

• Calcular una suma acumulativa
• Calcular una media móvil
• Obtener el elemento más popular de cada categoría
• Obtener el último valor de un rango
• Utilizar una ventana con nombre en una cláusula de marco de ventana

Estas consultas calcular valores con RANGE:

• Calcular el número de elementos un rango

Estas consultas computan valores con una ventana parcial o totalmente desunida:

• Calcular un total general
• Calcular un subtotal
• Calcular una suma acumulativa
• Obtener el elemento más popular de cada categoría
• Calcular rango

Estas consultas calculan valores con límites numéricos:

• Calcular una suma acumulativa
• Calcular una media móvil
• Calcular el número de elementos dentro de un rango
• Obtenga el último valor de un rango
• Use una cláusula de ventana con nombre en un marco de ventana

Estas consultas calculan los valores con la fila actual como límite:

• Calcular un total general
• Calcular un subtotal
• Calcular una suma acumulativa

Hacer referencia a una ventana con nombre

SELECT query_expr, analytic_function_name ( ) OVER over_clauseFROM from_itemWINDOW named_window_expression over_clause: { named_window | ( ) }window_specification: ] ] named_window_expression: named_window AS { named_window | ( ) }

Una ventana con nombre representa un grupo de filas en una tabla sobre las que usar una función ananalítica. Una ventana con nombre se define en la cláusulaWINDOW, y se hace referencia a la función analítica OVER clause.In una cláusula OVER, una ventana con nombre puede aparecer por sí misma o incrustada dentro de una especificación de ventana.

Ejemplos

• Obtener el último valor de un rango
• Usar una cláusula de ventana con nombre en un marco de ventana

Conceptos de función de navegación

Las funciones de navegación generalmente calculan algo devalue_expression sobre 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 el OVER cláusula:

• PARTITION BY: Opcional.
• ORDER BY:
  1. Disallowed for PERCENTILE_CONT and PERCENTILE_DISC.
  2. Required for FIRST_VALUELAST_VALUENTH_VALUELEADand LAG.
• window_frame_clause:
  1. Disallowed for PERCENTILE_CONTPERCENTILE_DISCLEAD and LAG.
  2. Optional for FIRST_VALUELAST_VALUE, and NTH_VALUE.

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

Conceptos de función de numeración

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

Ejemplo 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_numFROM 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() incrementa el número de pares en el grupo de orden de la ventana anterior.
• DENSE_RANK(): Para x=5, dense_rank es 3, ya que DENSE_RANK() alwaysincrements por 1, nunca saltarse un valor.
• ROW_NUMBER(): Para x=5, row_num es 4.

Conceptos de funciones analíticas agregadas

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 funciones ananalíticas. Estas funciones agregadas se denominan funciones analíticas agregadas.

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

Ejemplos de funciones analíticas

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

Tablas comunes utilizadas en ejemplos

Las siguientes tablas se utilizan en los siguientes ejemplos de aggregate analyticquery: ProduceEmployees,y Farm.

Producir tabla

Algunos ejemplos hacen 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 |+-------------------------------------+

los Empleados de la tabla

Algunos ejemplos de referencia una tabla denominada 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 |+-------------------------------------+

mesa de Granja

Algunos ejemplos de referencia una tabla denominada 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 |+-------------------------------------+

Calcular un total

Este calcula un gran total de todos los elementos en elProduce tabla.

• (naranja, manzana, puerro, repollo, lechuga, col rizada) = 54 compras totales
• (naranja, manzana, puerro, repollo, lechuga, col rizada) = 54 compras totales
• (naranja, manzana, puerro, repollo, lechuga, col rizada) = 54 compras totales
• (naranja, manzana, puerro, repollo, lechuga, col rizada) = 54 compras totales
• (naranja, manzana, puerro, col, lechuga, col rizada) = 54 compras totales
• (naranja, manzana, puerro, col, lechuga, col rizada) = 54 compras totales

SELECT item, purchases, category, SUM(purchases) OVER () AS total_purchasesFROM 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 |+-------------------------------------------------------+

Calcular un subtotal

Esto calcula un subtotal para cada categoría en elProduce.

• fruta
  • (naranja, manzana) = 10 compras totales
  • (naranja, manzana) = 10 compras totales
• verdura
  • (puerro, col, lechuga, col rizada) = 44 compras totales
  • (puerro, col, lechuga, col rizada) = 44 compras totales
  • (puerro, col, lechuga, col rizada) = 44 compras totales
  • (puerro, col, lechuga, col rizada) = 44 compras totales

SELECT item, purchases, category, SUM(purchases) OVER ( PARTITION BY category ORDER BY purchases ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS total_purchasesFROM 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 |+-------------------------------------------------------+

Calcular una suma acumulativa

Esto calcula una suma acumulativa para cada categoría en la tablaProduce. La suma se calcula con respecto al orden definido utilizando la cláusula ORDER BY.

• fruta
  • (naranja, manzana) = 2 compras totales
  • (naranja, manzana) = 10 compras totales
• verdura
  • (puerro, col, lechuga, col rizada) = 2 compras totales
  • (puerro, col, lechuga, col rizada) = 11 compras totales
  • (puerro, col, lechuga, col rizada) = 21 compras totales
  • (puerro, col, lechuga, col rizada) = 44 compras totales

SELECT item, purchases, category, SUM(purchases) OVER ( PARTITION BY category ORDER BY purchases ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS total_purchasesFROM 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 agregarCURRENT ROW como límite a menos que lo desee para facilitar la lectura.

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

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

• (naranja, puerro, manzana, repollo, lechuga, col rizada) = NULO
• (naranja, puerro, manzana, repollo, lechuga, col rizada) = NULO
• (naranja, puerro, manzana, repollo, lechuga, col rizada) = 2
• (naranja, puerro, manzana, repollo, lechuga, col rizada) = 4
• (naranja, puerro, manzana, repollo, lechuga, col rizada) = 12
• (naranja, puerro, manzana, repollo, lechuga, col rizada) = 21

SELECT item, purchases, category, SUM(purchases) OVER ( ORDER BY purchases ROWS BETWEEN UNBOUNDED PRECEDING AND 2 PRECEDING ) AS total_purchasesFROM 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 una media 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.

• (naranja, puerro, manzana, repollo, lechuga, col rizada) = 2 compras medias
• (naranja, puerro, manzana, repollo, lechuga, col rizada) = 4 compras medias
• (naranja, puerro, manzana, repollo, lechuga, col rizada) = 6.3333 compras medias
• (naranja, puerro, manzana, repollo, lechuga, col rizada) = 9 compras medias
• (naranja, puerro, manzana, repollo, lechuga, col rizada) = 14 compras medias
• (naranja, puerro, manzana, repollo, lechuga, 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_purchasesFROM 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 |+-------------------------------------------------------+

Calcule el número de artículos dentro de un rango

Este ejemplo obtiene el número de animales que tienen una población similar en la tabla Farm.

• (ganso, perro,buey, cabra, pato, gato) = 4 animales entre el rango de población 0-2.
• (ganso, perro,buey, cabra, pato, gato) = 5 animales entre el rango de población 1-3.
• (ganso, perro,buey, cabra, pato, gato) = 5 animales entre el rango de población 1-3.
• (ganso, perro,buey, cabra, pato, gato) = 5 animales entre el rango de población 1-3.
• (ganso, perro,buey, cabra, pato, gato) = 4 animales entre el rango de población 2-4.
• (ganso, perro,buey, cabra, pato, gato) = 1 animal entre el rango de población 22-24.

SELECT animal, population, category, COUNT(*) OVER ( ORDER BY population RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS similar_populationFROM 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 |+----------------------------------------------------------+

Obtenga el elemento más popular de cada categoría

Este ejemplo 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 tablaProduce.

• fruta
  • (naranja, manzana) = la manzana es la más popular
  • (naranja, manzana) = la manzana es la más popular
• vegetal
  • (puerro, col, lechuga, col rizada) = la col rizada es la más popular
  • (puerro, col, lechuga, col rizada) = la col rizada es la más popular
  • (puerro, col, lechuga, col rizada) = la col rizada es la más popular
  • (puerro, col, lechuga, col rizada) = la col rizada es la 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_popularFROM 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 |+----------------------------------------------------+

Obtener el último valor en un rango

Este ejemplo obtiene el elemento más popular en un marco de ventana específico, utilizando el Produce tabla. El marco de la ventana analiza hasta tres pares a la vez. Eche un vistazo de cerca a la columna most_popular para verduras.En lugar de obtener el artículo más popular en una categoría específica, obtiene el artículo más popular en un rango específico en esa categoría.

• fruta
  • (naranja, manzana) = la manzana es la más popular
  • (naranja, manzana) = la manzana es la más popular
• vegetal
  • (puerro, col, lechuga, col rizada) = la col es la más popular
  • (puerro, col, lechuga, col rizada) = la lechuga es la más popular
  • (puerro, col, lechuga, col rizada) = es la más popular
  • (puerro, col, lechuga, col rizada) = la col rizada es la 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_popularFROM 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 |+----------------------------------------------------+

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

SELECT item, purchases, category, LAST_VALUE(item) OVER ( item_window ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS most_popularFROM ProduceWINDOW item_window AS ( PARTITION BY category ORDER BY purchases)

Calcular rango

Este ejemplo calcula el rango de cada empleado dentro de su departamento,en función de su fecha de inicio. La especificación de ventana se define directamente en la cláusula OVER. Se hace referencia a la tabla Employees.

• departamento 1
  • (Jacob, Anthony, Andrew) = Asignar el rango 1 a Jacob
  • (Jacob, Anthony, Andrew) = Asignar el rango 2 a Anthony
  • (Jacob, Anthony, Andrew) = Asignar el rango 3 a Andrew
• departamento 2
  • (Isabella, Daniel, Jose) = Asignar el rango 1 a Isabella
  • (Isabella, Daniel, Jose) = Asignar rango 2 a Daniel
  • (Isabella, Daniel, Jose) = Asignar rango 3 a Jose

SELECT name, department, start_date, RANK() OVER (PARTITION BY department ORDER BY start_date) AS rankFROM 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 |+--------------------------------------------+

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

Puede definir parte de su lógica en una ventana con nombre y parte de ella en cláusula marco de ventana abierta. Esta lógica se combina. Este es un ejemplo, usando la tablaProduce.

SELECT item, purchases, category, LAST_VALUE(item) OVER (item_window) AS most_popularFROM ProduceWINDOW 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 puede obtener los resultados anteriores con estos ejemplos:

SELECT item, purchases, category, LAST_VALUE(item) OVER (item_window) AS most_popularFROM ProduceWINDOW 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_popularFROM ProduceWINDOW a AS (PARTITION BY category), b AS (a ORDER BY purchases), item_window AS (b)

El siguiente ejemplo se produce un error porque el marco de una ventana cláusula beendefined dos veces:

SELECT item, purchases, category, LAST_VALUE(item) OVER ( item_window ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS most_popularFROM ProduceWINDOW item_window AS ( ORDER BY purchases ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)