Simple Log Service:Fonctions de fenêtrage

Introduction

Contrairement aux fonctions d'agrégation qui regroupent les lignes en un seul résultat, les fonctions de fenêtrage calculent un résultat pour chaque ligne à partir d'un ensemble de lignes connexes. Les fonctions de fenêtrage reposent sur trois éléments fondamentaux : la partition, le tri et la fenêtre (Concepts et syntaxe des fonctions de fenêtrage).

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

• Partition : La clause partition by divise les lignes en partitions. Si cette clause est omise, l'ensemble des résultats est traité comme une seule partition.

• Tri : La clause order by trie les lignes au sein de chaque partition.

  Remarque

  L'utilisation de la clause order by sur des colonnes contenant des valeurs dupliquées entraîne un ordre des lignes non déterministe. Pour garantir un ordre de tri cohérent, spécifiez plusieurs colonnes. Par exemple, order by request_time, request_method.

• Fenêtre : Restreint les lignes au sein d'une partition. Ne peut pas être utilisée avec les fonctions de classement. Syntaxe : { rows | range} { frame_start | frame_between }. Exemple : range between unbounded preceding and unbounded following. Spécification de la fenêtre des fonctions de fenêtrage.

Functions

Fonctions d'agrégation

aggregate functions

Toutes les fonctions d'agrégation peuvent servir de fonctions de fenêtrage. L'exemple suivant utilise sum().

Syntax

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

Parameters

Return value type

double

Examples

Calcule le salaire de chaque employé en proportion du total de son département.

• Instruction de requête

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

• Résultats de la requête : les résultats affichent les données de quatre employés du département dev et de trois employés du département Marketing. La colonne salary_percentage indique le salaire de chaque employé sous forme de fraction du total de son département.

Fonction cume_dist

Calcule la distribution cumulative d'une valeur au sein d'une partition : il s'agit du rapport entre le nombre de lignes dont les valeurs sont inférieures ou égales à celle de la ligne actuelle et le nombre total de lignes. La fonction renvoie une valeur comprise dans l'intervalle (0, 1].

Syntax

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

Parameters

Return value type

double

Examples

Calcule la distribution cumulative des tailles d'objets dans le compartiment OSS bucket00788.

• Instruction de requête

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

Fonction dense_rank

Renvoie le rang d'une valeur au sein d'une partition. Les ex æquo reçoivent le même rang et les rangs se suivent sans lacune. Par exemple, si deux lignes partagent le rang 1, le rang suivant est 2.

Syntax

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

Parameters

Return value type

bigint

Examples

Calcule le classement des salaires par département.

• Instruction de requête et d'analyse

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

• Résultats de la requête et de l'analyse : dans le service Marketing, Blan Stark et Smith partagent le rang 1 (salaire 9000) ; Achilles occupe le rang 2 (8000). Dans le service dev, Rob est au rang 1 (9000), Blan au rang 2 (8500) et Sansa au rang 3 (8000).

Fonction ntile

Répartit les lignes ordonnées d'une partition en un nombre spécifié de groupes.

Syntax

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

Parameters

Return value type

bigint

Examples

Divise les données d'un objet spécifié en trois groupes.

• Instruction de requête

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

• Résultats de la requête et de l'analyse : la requête renvoie neuf lignes. Les valeurs de object_size, par ordre croissant, sont 3396, 3701, 3750, 3757, 3914, 3918, 7440, 7490 et 7521. Les valeurs correspondantes dans la colonne ntile sont 1, 1, 1, 2, 2, 2, 3, 3 et 3. Ainsi, ntile(3) répartit les neuf lignes en trois groupes égaux de trois éléments.

Fonction percent_rank

Calcule le rang en pourcentage de chaque ligne dans une partition. Formule : (rank - 1) / (total_rows - 1), où rank représente le rang de la ligne actuelle et total_rows la taille de la partition.

Syntax

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

Parameters

Return value type

double

Examples

Calcule le rang en pourcentage d'un objet OSS selon sa taille.

• Instruction de requête

  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

• Résultats de la requête et de l'analyse : six lignes triées par object_size. La colonne ntile affiche des valeurs de rang en pourcentage réparties uniformément de 0,0 à 1,0.

Fonction rank

Renvoie le rang de chaque ligne au sein d'une partition. Les ex æquo reçoivent le même rang, ce qui crée des lacunes dans la séquence. Par exemple, si deux lignes partagent le rang 1, le rang suivant est 3.

Syntax

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

Parameters

Return value type

bigint

Examples

Classe les employés par salaire au sein de chaque département.

• Instruction de requête et d'analyse

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

• Résultats de la requête et de l'analyse : dans le service Marketing, deux employés partagent le rang 1 (salaire 9000). Le rang suivant est 3, ce qui illustre comment les ex æquo créent des lacunes.

Fonction row_number

Attribue un entier séquentiel unique à chaque ligne au sein d'une partition, en commençant par 1.

Syntax

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

Parameters

Return value type

bigint.

Examples

Classe les employés par salaire au sein de chaque département.

• Requête

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

• Six lignes : dans Marketing, Blan Stark (9000) occupe la ligne 1, Smith (9000) la ligne 2 et Achilles (8000) la ligne 3. Dans dev, Rob (9000) est en ligne 1, Blan (8500) en ligne 2 et Sansa (8000) en ligne 3.

Fonction first_value

Renvoie la valeur de la première ligne de chaque partition.

Syntax

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

Parameters

Return value type

Renvoie le même type de données que x.

Examples

Renvoie la taille minimale de chaque objet dans un compartiment OSS.

• Instruction de requête et d'analyse

  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

• Résultats de la requête et de l'analyse : sept lignes réparties en trois groupes par object. Au sein de chaque groupe, first_value équivaut à la valeur minimale de object_size (la première ligne dans l'ordre croissant).

Fonction last_value

Renvoie la valeur de la dernière ligne d'une partition.

Syntax

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

Parameters

Return value type

Même type que x.

Examples

Recherche la taille maximale des objets dans le compartiment OSS spécifié.

• Instruction de requête et d'analyse

  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 requête renvoie trois colonnes : object, object_size et last_value. Pour 245-da918c.model, sept lignes affichent une valeur object_size allant de 2383 à 6936, avec une valeur last_value de 6936 pour toutes les lignes. Pour dashboard%2F2020%2F05%2F20%2F16%2F47.csv, deux lignes présentent des valeurs object_size de 2435 et 2603, avec une valeur last_value de 2603.

Fonction lag

Renvoie la valeur d'une ligne située à un offset spécifié avant la ligne actuelle dans une partition.

Syntax

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

Parameters

Return value type

Même type que x.

Examples

Calcule les UV quotidiens et le taux de croissance jour sur jour.

• Instruction de requête

  * |
  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
    )

• Résultats de la requête et de l'analyse : huit enregistrements du 2 au 9 août 2021. La valeur diff_percentage de la première ligne est Infinity car lag renvoie la valeur par défaut 0, ce qui entraîne une division par zéro. Les lignes suivantes affichent le rapport entre les UV du jour actuel et ceux du jour précédent, comme 2,098 et 0,976.

Fonction lead

Renvoie la valeur d'une ligne située à un offset spécifié après la ligne actuelle dans une partition.

Syntax

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

Parameters

Return value type

Renvoie une valeur du même type de données que x.

Examples

Calcule le ratio horaire des UV le 26 août 2021.

• Instruction de requête

  * |
  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
    )

• Résultats de la requête et de l'analyse : la requête renvoie huit lignes, affichant les visiteurs uniques horaires (UV) et le ratio entre les UV de l'heure actuelle et ceux de l'heure suivante (diff_percentage) de 00:00 à 07:00 le 26 août 2021.

Fonction nth_value

Renvoie la valeur de la ligne située à l'offset-ième position dans une partition.

Syntax

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

Parameters

Return value type

Identique au type de données de x.

Examples

Renvoie l'employé ayant le deuxième salaire le plus élevé par département.

• Instruction de requête

  * |
  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

• Résultats de la requête : la colonne second_highest_salary affiche Blan (8500) pour dev et San (7000) pour Marketing sur toutes les lignes de chaque partition.