Simple Log Service:Funciones de ventana

Introducción

A diferencia de las funciones de agregación, que reducen varias filas a un solo resultado, las funciones de ventana calculan un valor para cada fila con base en un conjunto relacionado de ellas. Estas funciones constan de tres elementos principales: partición, ordenamiento y marco (Conceptos y sintaxis de funciones de ventana).

function over (
    [partition by partition_expression]
    [order by order_expression]
    [frame]
)

• Partición: La cláusula partition by divide las filas en particiones. Si se omite esta cláusula, todo el conjunto de resultados se trata como una sola partición.

• Ordenamiento: La cláusula order by ordena las filas dentro de cada partición.

  Nota

  El uso de la cláusula order by en columnas con valores duplicados genera un orden de filas no determinista. Para garantizar un orden consistente, especifique varias columnas. Por ejemplo, order by request_time, request_method.

• Marco: Restringe las filas dentro de una partición. No se puede utilizar con funciones de clasificación. Sintaxis: { rows | range} { frame_start | frame_between }. Ejemplo: range between unbounded preceding and unbounded following. Consulte la Especificación de marco de funciones de ventana.

Funciones

Funciones de agregación

Todas las aggregate functions pueden funcionar como funciones de ventana. El siguiente ejemplo utiliza sum().

Syntax

sum() over (
    [partition by partition_expression]
    [order by order_expression]
    [frame]
)

Parameters

Return value type

double

Examples

Calcula el salario de cada empleado como proporción del total de su departamento.

• Instrucción de consulta

  * |
  SELECT
    department,
    staff_name,
    salary,
    round ( salary * 1.0 / sum(salary) over(partition by department), 3) AS salary_percentage 

• Resultados de la consulta: Los resultados muestran datos de cuatro empleados en el departamento dev y tres en el departamento Marketing. La columna salary_percentage muestra el salario de cada empleado como fracción del total de su departamento.

Función Cume_dist

Calcula la distribución acumulada de un valor dentro de una partición: la proporción de filas con valores menores o iguales al valor de la fila actual respecto al total de filas. Devuelve un valor en el intervalo (0, 1].

Syntax

cume_dist() over (
    [partition by partition_expression]
    [order by order_expression]
)

Parameters

Return value type

double

Examples

Calcula la distribución acumulada de tamaños de objetos en el bucket de OSS bucket00788.

• Instrucción de consulta

  bucket=bucket00788 |
  select
    object,
    object_size,
    cume_dist() over (
      partition by object
      order by
        object_size
    ) as cume_dist
  from  oss-log-store

Función Dense_rank

Devuelve la posición de un valor dentro de una partición. Los empates reciben la misma posición y los rangos son consecutivos sin saltos. Por ejemplo, si dos filas comparten la posición 1, la siguiente será 2.

Syntax

dense_rank() over (
    [partition by partition_expression]
    [order by order_expression]
)

Parameters

Return value type

bigint

Examples

Calcula la clasificación salarial por departamento.

• Instrucción de consulta y análisis

  * |
  select
    department,
    staff_name,
    salary,
    dense_rank() over(
      partition by department
      order by
        salary desc
    ) as salary_rank
  order by
    department,
    salary_rank

• Resultados de la consulta y análisis: En Marketing, Blan Stark y Smith comparten la posición 1 (salario 9000); Achilles ocupa la posición 2 (8000). En dev, Rob tiene la posición 1 (9000), Blan la posición 2 (8500) y Sansa la posición 3 (8000).

Función Ntile

Divide las filas ordenadas de una partición en un número específico de grupos.

Syntax

ntile(n) over (
    [partition by partition_expression]
    [order by order_expression]
)

Parameters

Return value type

bigint

Examples

Divide los datos de un objeto específico en tres grupos.

• Instrucción de consulta

  object=245-da918c.model |
  select
    object,
    object_size,
    ntile(3) over (
      partition by object
      order by
        object_size
    ) as ntile
  from  oss-log-store

• Resultados de la consulta y análisis: La consulta devuelve nueve filas. Los valores de object_size, en orden ascendente, son 3396, 3701, 3750, 3757, 3914, 3918, 7440, 7490 y 7521. Los valores correspondientes en la columna ntile son 1, 1, 1, 2, 2, 2, 3, 3 y 3. En consecuencia, ntile(3) divide las nueve filas en tres grupos iguales de tres elementos cada uno.

Función percent_rank

Calcula el rango porcentual de cada fila en una partición. Fórmula: (rank - 1) / (total_rows - 1), donde rank es la posición de la fila actual y total_rows es el tamaño de la partición.

Syntax

percent_rank() over (
    [partition by partition_expression]
    [order by order_expression]
)

Parameters

Return value type

double

Examples

Calcula el rango porcentual de un objeto de OSS según su tamaño.

• Instrucción de consulta

  object=245-da918c3e2dd9dc9cb4d9283b%2F555e2441b6a4c7f094099a6dba8e7a5f.model|
  select
    object,
    object_size,
   percent_rank() over (
      partition by object
      order by
        object_size
    ) as ntile
  FROM  oss-log-store

• Resultados de la consulta y análisis: Seis filas ordenadas por object_size. La columna ntile muestra valores de rango porcentual distribuidos uniformemente entre 0.0 y 1.0.

Función Rank

Devuelve la posición de cada fila dentro de una partición. Los empates reciben la misma posición, lo que genera saltos en la secuencia. Por ejemplo, si dos filas comparten la posición 1, la siguiente será 3.

Syntax

rank() over (
    [partition by partition_expression]
    [order by order_expression]
)

Parameters

Return value type

bigint

Examples

Clasifica a los empleados por salario dentro de cada departamento.

• Instrucción de consulta y análisis

  * |
  select
    department,
    staff_name,
    salary,
    rank() over(
      partition by department
      order by
        salary desc
    ) as salary_rank
  order by
    department,
    salary_rank

• Resultados de la consulta y análisis: En Marketing, dos empleados comparten la posición 1 (salario 9000). La siguiente posición es 3, lo que demuestra cómo los empates generan saltos en la secuencia.

Función row_number

Asigna un entero secuencial único a cada fila dentro de una partición, comenzando desde 1.

Syntax

row_number() over (
    [partition by partition_expression]
    [order by order_expression]
)

Parameters

Return value type

bigint.

Examples

Clasifica a los empleados por salario dentro de cada departamento.

• Consulta

  * |
  select
    department,
    staff_name,
    salary,
    row_number() over(
      partition by department
      order by
        salary desc
    ) as salary_rank
  order by
    department,
    salary_rank

• Seis filas: En Marketing, Blan Stark (9000) es la fila 1, Smith (9000) la fila 2 y Achilles (8000) la fila 3. En dev, Rob (9000) es la fila 1, Blan (8500) la fila 2 y Sansa (8000) la fila 3.

Función First_value

Devuelve el valor de la primera fila en cada partición.

Syntax

first_value(x) over (
    [partition by partition_expression]
    [order by order_expression]
    [frame]
)

Parameters

Return value type

Devuelve el mismo tipo de dato que x.

Examples

Devuelve el tamaño mínimo de cada objeto en un bucket de OSS.

• Instrucción de consulta y análisis

  bucket :bucket90 |
  select
    object,
    object_size,
    first_value(object_size) over (
      partition by object
      order by
        object_size 
       range between unbounded preceding and unbounded following
    ) as first_value
  from  oss-log-store

• Resultados de la consulta y análisis: Siete filas divididas en tres grupos por object. Dentro de cada grupo, first_value equivale al valor mínimo de object_size (la primera fila en orden ascendente).

Función Last_value

Devuelve el valor de la última fila en una partición.

Syntax

last_value(x) over (
    [partition by partition_expression]
    [order by order_expression]
    [frame]
)

Parameters

Return value type

Mismo tipo que x.

Examples

Busca el tamaño máximo de objeto en el bucket de OSS especificado.

• Instrucción de consulta y análisis

  bucket :bucket90 |
  select
    object,
    object_size,
    last_value(object_size) over (
      partition by object
      order by
        object_size 
       range between unbounded preceding and unbounded following
    ) as last_value
  from  oss-log-store

• La consulta devuelve tres columnas: object, object_size y last_value. Para 245-da918c.model, siete filas muestran valores de object_size desde 2383 hasta 6936, con un last_value de 6936 para todas las filas. Para dashboard%2F2020%2F05%2F20%2F16%2F47.csv, dos filas tienen valores de object_size de 2435 y 2603, con un last_value de 2603.

Función Lag

Devuelve el valor de una fila situada en un offset específico antes de la fila actual dentro de una partición.

Syntax

lag(x, offset, default_value) over (
    [partition by partition_expression]
    [order by order_expression]
    [frame]
)

Parameters

Return value type

Mismo tipo que x.

Examples

Calcula los UV diarios y la tasa de crecimiento respecto al día anterior.

• Instrucción de consulta

  * |
  select
    day,
    UV,
    UV * 1.0 /(lag(UV, 1, 0) over()) as diff_percentage
  from  (
      select
        approx_distinct(client_ip) as UV,
        date_trunc('day', __time__) as day
      from  log
      group by
        day
      order by
        day asc
    )

• Resultados de la consulta y análisis: Ocho registros del 2 al 9 de agosto de 2021. El diff_percentage de la primera fila es Infinity porque lag devuelve el valor predeterminado 0, lo que causa una división por cero. Las filas siguientes muestran la proporción de UV del día actual respecto al día anterior, como 2.098 y 0.976.

Función Lead

Devuelve el valor de una fila situada en un offset específico después de la fila actual dentro de una partición.

Syntax

lead(x, offset, default_value) over (
    [partition by partition_expression]
    [order by order_expression]
    [frame]
)

Parameters

Return value type

Devuelve un valor del mismo tipo de dato que x.

Examples

Calcula la proporción horaria de UV el 26 de agosto de 2021.

• Instrucción de consulta

  * |
  select
    time,
    UV,
    UV * 1.0 /(lead(UV, 1, 0) over()) as diff_percentage
  from  (
      select
        approx_distinct(client_ip) as uv,
        date_trunc('hour', __time__) as time
      from    log
      group by
        time
      order by
        time asc
    )

• Resultados de la consulta y análisis: La consulta devuelve ocho filas que muestran los visitantes únicos por hora (UV) y la proporción de UV de la hora actual respecto a la siguiente (diff_percentage) desde las 00:00 hasta las 07:00 del 26 de agosto de 2021.

Función nth_value

Devuelve el valor de la fila en la posición offset dentro de una partición.

Syntax

nth_value(x, offset) over (
    [partition by partition_expression]
    [order by order_expression]
    [frame]
)

Parameters

Return value type

Mismo tipo de dato que x.

Examples

Devuelve el empleado con el segundo salario más alto por departamento.

• Instrucción de consulta

  * |
  select
    department,
    staff_name,
    salary,
    nth_value(staff_name, 2) over(
      partition by department
      order by
        salary desc
        range between unbounded preceding and unbounded following
    ) as second_highest_salary from log

• Resultados de la consulta: La columna second_highest_salary muestra a Blan (8500) para dev y a San (7000) para Marketing en todas las filas de cada partición.