Simple Log Service:Funções de janela

Introdução

Diferentemente das funções de agregação, que consolidam linhas em um único resultado, as funções de janela calculam um resultado para cada linha com base em um conjunto relacionado de linhas. Essas funções possuem três elementos fundamentais: partição, ordenação e frame (Window Function Concepts and Syntax).

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

• Partição: a cláusula partition by divide as linhas em partições. Se omitida, o conjunto de resultados inteiro é tratado como uma única partição.

• Ordenação: a cláusula order by ordena as linhas dentro de cada partição.

  null

  Usar a cláusula order by em colunas com valores duplicados gera uma ordem de linhas não determinística. Para garantir uma ordenação consistente, especifique múltiplas colunas. Por exemplo: order by request_time, request_method.

• Frame: restringe as linhas dentro de uma partição. Não pode ser usado com funções de classificação. Sintaxe: { rows | range} { frame_start | frame_between }. Exemplo: range between unbounded preceding and unbounded following. Window Function Frame Specification.

Funções

Funções de agregação

Todas as funções de agregação podem atuar como funções de janela. O exemplo a seguir usa sum().

Syntax

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

Parameters

Return value type

double

Examples

Calcula o salário de cada funcionário como proporção do total do respectivo departamento.

• Instrução de consulta

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

• Resultados da consulta: os resultados exibem dados de quatro funcionários no departamento dev e três no departamento Marketing. A coluna salary_percentage mostra o salário de cada funcionário como fração do total do departamento.

Função cume_dist

Calcula a distribuição cumulativa de um valor dentro de uma partição: a razão entre o número de linhas com valores menores ou iguais ao da linha atual e o total de linhas. Retorna um valor no intervalo (0, 1].

Syntax

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

Parameters

Return value type

double

Examples

Calcula a distribuição cumulativa dos tamanhos de objetos no bucket OSS bucket00788.

• Instrução 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

Função dense_rank

Retorna o rank de um valor dentro de uma partição. Empates recebem o mesmo rank, e os ranks são consecutivos, sem lacunas. Por exemplo, se duas linhas compartilham o rank 1, o próximo rank é 2.

Syntax

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

Parameters

Return value type

bigint

Examples

Calcula o rank salarial por departamento.

• Instrução de consulta e análise

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

• Resultados da consulta e análise: em Marketing, Blan Stark e Smith compartilham o rank 1 (salário 9000); Achilles ocupa o rank 2 (8000). Em dev, Rob é rank 1 (9000), Blan rank 2 (8500) e Sansa rank 3 (8000).

Função ntile

Divide as linhas ordenadas de uma partição em um número especificado de grupos.

Syntax

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

Parameters

Return value type

bigint

Examples

Divide os dados de um objeto específico em três grupos.

• Instrução 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 da consulta e análise: a consulta retorna nove linhas. Os valores de object_size, em ordem crescente, são 3396, 3701, 3750, 3757, 3914, 3918, 7440, 7490 e 7521. Os valores correspondentes na coluna ntile são 1, 1, 1, 2, 2, 2, 3, 3 e 3. Dessa forma, ntile(3) divide as nove linhas em três grupos iguais de três.

Função percent_rank

Calcula o rank percentual de cada linha em uma partição. Fórmula: (rank - 1) / (total_rows - 1), onde rank é o rank da linha atual e total_rows é o tamanho da partição.

Syntax

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

Parameters

Return value type

double

Examples

Calcula o rank percentual de um objeto OSS por tamanho.

• Instrução 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 da consulta e análise: seis linhas ordenadas por object_size. A coluna ntile exibe valores de rank percentual distribuídos uniformemente de 0,0 a 1,0.

Função rank

Retorna o rank de cada linha dentro de uma partição. Empates recebem o mesmo rank, gerando lacunas na sequência. Por exemplo, se duas linhas compartilham o rank 1, o próximo rank é 3.

Syntax

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

Parameters

Return value type

bigint

Examples

Classifica os funcionários por salário dentro de cada departamento.

• Instrução de consulta e análise

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

• Resultados da consulta e análise: em Marketing, dois funcionários compartilham o rank 1 (salário 9000). O próximo rank é 3, demonstrando como empates criam lacunas na sequência.

Função row_number

Atribui um inteiro sequencial único a cada linha dentro de uma partição, começando em 1.

Syntax

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

Parameters

Return value type

bigint.

Examples

Classifica os funcionários por salário 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 linhas: em Marketing, Blan Stark (9000) é a linha 1, Smith (9000) a linha 2 e Achilles (8000) a linha 3. Em dev, Rob (9000) é a linha 1, Blan (8500) a linha 2 e Sansa (8000) a linha 3.

Função first_value

Retorna o valor da primeira linha de cada partição.

Syntax

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

Parameters

Return value type

Retorna o mesmo tipo de dado que x.

Examples

Retorna o menor tamanho de cada objeto em um bucket OSS.

• Instrução de consulta e análise

  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 da consulta e análise: sete linhas divididas em três grupos por object. Dentro de cada grupo, first_value corresponde ao menor object_size (a primeira linha em ordem crescente).

Função last_value

Retorna o valor da última linha de uma partição.

Syntax

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

Parameters

Return value type

Mesmo tipo que x.

Examples

Encontra o maior tamanho de objeto no bucket OSS especificado.

• Instrução de consulta e análise

  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

• A consulta retorna três colunas: object, object_size e last_value. Para 245-da918c.model, sete linhas exibem object_size de 2383 a 6936, com last_value igual a 6936 em todas as linhas. Para dashboard%2F2020%2F05%2F20%2F16%2F47.csv, duas linhas têm object_size 2435 e 2603, com last_value igual a 2603.

Função lag

Retorna o valor da linha que está a um offset especificado antes da linha atual em uma partição.

Syntax

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

Parameters

Return value type

Mesmo tipo que x.

Examples

Calcula os UVs diários e a taxa de crescimento dia a dia.

• Instrução 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 da consulta e análise: oito registros de 2 a 9 de agosto de 2021. O diff_percentage da primeira linha é Infinity porque lag retorna o valor padrão 0, causando uma divisão por zero. As linhas seguintes exibem a razão entre os UVs do dia atual e do dia anterior, como 2,098 e 0,976.

Função lead

Retorna o valor da linha que está a um offset especificado após a linha atual em uma partição.

Syntax

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

Parameters

Return value type

Retorna um valor do mesmo tipo de dado que x.

Examples

Calcula a proporção de UVs por hora em 26 de agosto de 2021.

• Instrução 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 da consulta e análise: a consulta retorna oito linhas com os visitantes únicos por hora (UV) e a razão entre os UVs da hora atual e da hora seguinte (diff_percentage), das 00:00 às 07:00 do dia 26 de agosto de 2021.

Função nth_value

Retorna o valor da offset-ésima linha de uma partição.

Syntax

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

Parameters

Return value type

Mesmo tipo de dado que x.

Examples

Retorna o funcionário com o segundo maior salário por departamento.

• Instrução 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 da consulta: a coluna second_highest_salary exibe Blan (8500) para dev e San (7000) para Marketing em todas as linhas de cada partição.