Concepts de fonctions analytiques en SQL standard

Concepts de fonctions analytiques

Cet article explique le fonctionnement des fonctions analytiques dans BigQuery. Pour obtenir une description des différentes fonctions analytiques compatibles avec BigQuery, consultez la documentation de référence sur les fonctions de navigation, les fonctions de numérotation et les fonctions analytiques d'agrégation.

Dans une base de données, une fonction analytique calcule les valeurs agrégées sur un groupe de lignes. Contrairement aux fonctions d'agrégation, qui renvoient une valeur agrégée unique pour un groupe de lignes, les fonctions analytiques renvoient une valeur unique pour chaque ligne en calculant la fonction sur un groupe de lignes d'entrée.

Les fonctions analytiques constituent un mécanisme puissant, capable de représenter de manière succincte des opérations analytiques complexes. Elles permettent également d'effectuer des évaluations efficaces qui, autrement, impliqueraient des autojointures (self-JOIN) coûteuses ou des calculs en dehors de la requête SQL.

Les fonctions analytiques sont également appelées "fenêtrages (analytiques)" en langage standard SQL et dans certaines bases de données commerciales. En effet, une fonction analytique est évaluée sur un groupe de lignes, nommé window ou window frame (fenêtre ou fenêtrage). Dans d'autres bases de données, elles peuvent également être appelées fonctions de traitement analytique en ligne (OLAP).

Syntaxe simplifiée :

analytic_function_name ( [ argument_list ] )
  OVER (
    [ PARTITION BY partition_expression_list ]
    [ ORDER BY expression [{ ASC | DESC }] [, ...] ]
    [ window_frame_clause ]
  )

Une fonction analytique nécessite une clause OVER, qui définit le cadre de fenêtrage (window frame) sur lequel la fonction analytique est évaluée. La clause OVER comprend les trois clauses facultatives ci-dessous. BigQuery évalue les sous-clauses d'une clause OVER selon leur ordre d'écriture.

  • Une clause PARTITION BY divise les lignes d'entrée en partitions, de la même manière que GROUP BY, mais sans combiner les lignes avec la même clé.
  • Une clause ORDER BY spécifie l'ordre dans chaque partition.
  • Une clause window_frame_clause définit le cadre de fenêtrage (window frame) au sein de la partition actuelle.

La clause OVER peut également être vide (OVER()). Dans ce cas, window frame inclut toutes les lignes d'entrée.

Les fonctions analytiques sont évaluées après agrégation (clause GROUP BY et fonctions d'agrégation non analytiques).

Par exemple, imaginons qu'une entreprise souhaite créer pour chaque service un classement par "ordre d'ancienneté" des employés, soit un tableau indiquant les employés qui sont là depuis le plus longtemps. La table Employees contient les colonnes Name (nom), StartDate (date d'embauche) et Department (service).

La requête suivante calcule le classement de chaque employé au sein de son service :

SELECT firstname, department, startdate,
  RANK() OVER ( PARTITION BY department ORDER BY startdate ) AS rank
FROM Employees;

La figure 1 illustre le processus de calcul conceptuel.

Image Markdown Figure 1 : Illustration de la fonction analytique

BigQuery évalue les sous-clauses d'une clause OVER dans leur ordre d'apparition :

  1. PARTITION BY : la table est d'abord divisée en deux partitions par department (service).
  2. ORDER BY : les lignes des employés dans chaque partition sont triées par startdate (date d'embauche).
  3. Cadre de fenêtrage : aucun. La clause de cadre de fenêtrage n'est pas autorisée pour RANK(), comme c'est le cas pour toutes les fonctions de numérotation.
  4. RANK() : le classement par ordre d'ancienneté est calculé pour chaque ligne par rapport au cadre de fenêtrage window frame.

Syntaxe de la fonction analytique

analytic_function_name ( [ argument_list ] )
  OVER { window_name | ( [ window_specification ] ) }

window_specification:
  [ window_name ]
  [ PARTITION BY partition_expression_list ]
  [ ORDER BY expression [{ ASC | DESC }] [, ...] ]
  [ window_frame_clause ]

window_frame_clause:
{ ROWS | RANGE }
{
  { UNBOUNDED PRECEDING | numeric_expression PRECEDING | CURRENT ROW }
  |
  { BETWEEN window_frame_boundary_start AND window_frame_boundary_end }
}

window_frame_boundary_start:
{ UNBOUNDED PRECEDING | numeric_expression { PRECEDING | FOLLOWING } | CURRENT ROW }

window_frame_boundary_end:
{ UNBOUNDED FOLLOWING | numeric_expression { PRECEDING | FOLLOWING } | CURRENT ROW }

Les fonctions analytiques peuvent apparaître sous la forme d'une expression scalaire ou d'un opérande d'expression scalaire à deux endroits de la requête seulement :

  • Dans la liste SELECT. Si la fonction analytique apparaît dans la liste SELECT, sa liste d'arguments (argument_list) ne peut pas faire référence à des alias introduits dans cette même liste SELECT.
  • Dans la clause ORDER BY. Si la fonction analytique apparaît dans la clause ORDER BY de la requête, sa liste d'arguments (argument_list) peut faire référence à des alias compris dans la liste SELECT.

En outre, une fonction analytique ne peut pas faire référence à une autre fonction analytique dans sa liste d'arguments (argument_list), ni dans sa clause OVER, même de manière indirecte par le biais d'un alias.

Incorrect :

SELECT ROW_NUMBER() OVER () AS alias1
FROM Singers
ORDER BY ROW_NUMBER() OVER(PARTITION BY alias1)

Dans la requête ci-dessus, la fonction analytique alias1 se résout en une fonction analytique : ROW_NUMBER() OVER().

Clause OVER

Syntaxe :

OVER { window_name | ( [ window_specification ] ) }

window_specification:
  [ window_name ]
  [ PARTITION BY partition_expression_list ]
  [ ORDER BY sort_specification_list ]
  [ window_frame_clause ]

La clause OVER accepte les trois composants suivants :

  • La clause PARTITION BY
  • La clause ORDER BY
  • La clause window_frame_clause ou window_name, faisant référence à window_specification, dont la valeur est définie dans une clause WINDOW.

Si la clause OVER est vide (OVER()), la fonction analytique est calculée sur une seule partition contenant toutes les lignes d'entrée. Ainsi, elle produira le même résultat pour chaque ligne de sortie.

Clause PARTITION BY

Syntaxe :

PARTITION BY expression [, ... ]

La clause PARTITION BY divise les lignes d'entrée en partitions distinctes, sur lesquelles la fonction analytique est évaluée de manière indépendante. Plusieurs expressions sont autorisées dans la clause PARTITION BY.

Le type de données de l'expression doit être groupable et accepter le partitionnement. Autrement dit, l'expression ne peut pas correspondre à l'un des types de données suivants :

  • Virgule flottante
  • Structure
  • Tableau

Cette liste est presque identique à celle des types de données non compatibles avec la clause GROUP BY, avec l'exclusion supplémentaire des types à virgule flottante (consultez la section "Groupable" dans la table "Propriétés de type de données", située en haut des types de données BigQuery).

Si aucune clause PARTITION BY n'est présente, BigQuery traite l'intégralité de l'entrée en tant que partition unique.

Clause ORDER BY

Syntaxe :

ORDER BY expression [ ASC | DESC ] [, ... ]

La clause ORDER BY définit un ordre de tri dans chaque partition. Si aucune clause ORDER BY n'est présente, l'ordre des lignes d'une partition est non déterministe. Certaines fonctions analytiques nécessitent la clause ORDER BY. Cette information est spécifiée dans la section associée à chaque famille de fonctions analytiques. Même si une clause ORDER BY est présente, certaines fonctions ne sont pas sensibles à l'ordre dans un cadre de fenêtrage (window frame) (par exemple, COUNT).

La clause ORDER BY au sein d'une clause OVER est conforme à la clause ORDER BY standard pour les raisons suivantes :

  • Il peut exister plusieurs expressions.
  • L'expression doit avoir un type pouvant être classé.
  • Une spécification ASC/DESC facultative est acceptée pour chaque expression.
  • Les valeurs NULL sont classées comme étant les plus petites valeurs possible (la première pour ASC, la dernière pour DESC)

Les types de données compatibles sont les mêmes que pour la clause ORDER BY standard, étant donné que les types suivants ne peuvent pas être classés :

  • Tableau
  • Structure

Si la clause OVER contient une clause ORDER BY, mais pas de window_frame_clause, alors ORDER BY définit implicitement window_frame_clause de la manière suivante :

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Si ni window_frame_clause, ni la clause ORDER BY ne sont présentes, le cadre de fenêtrage (window frame) correspond à la partition entière par défaut.

Clause de cadre de fenêtrage

Syntaxe :

{ ROWS | RANGE }
{
  { UNBOUNDED PRECEDING | numeric_expression PRECEDING | CURRENT ROW }
  |
  { BETWEEN window_frame_boundary_start AND window_frame_boundary_end }
}

window_frame_boundary_start:
{ UNBOUNDED PRECEDING | numeric_expression { PRECEDING | FOLLOWING } | CURRENT ROW }

window_frame_boundary_end:
{ UNBOUNDED FOLLOWING | numeric_expression { PRECEDING | FOLLOWING } | CURRENT ROW }

La clause window_frame_clause définit le cadre de fenêtrage (window frame), autour de la ligne actuelle dans une partition, sur lequel la fonction analytique est évaluée. La clause window_frame_clause autorise les cadres de fenêtrage physiques (définis par ROWS) et logiques (définis par RANGE). Si la clause OVER contient une clause ORDER BY, mais pas de window_frame_clause, alors ORDER BY définit implicitement window_frame_clause de la manière suivante :

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Si ni window_frame_clause, ni la clause ORDER BY ne sont présentes, le cadre de fenêtrage (window frame) correspond à la partition entière par défaut.

L'expression numeric_expression ne peut être qu'une constante ou un paramètre de requête, et chacun doit posséder une valeur non négative. Sinon, BigQuery renvoie une erreur.

Exemples de clauses de cadre de fenêtrage :

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  • Comprend la partition entière.
  • Exemple d'utilisation : pour calculer un total général sur la partition.
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  • Comprend toutes les lignes de la partition avant la ligne actuelle ou incluant cette dernière.
  • Exemple d'utilisation : pour calculer une somme cumulée.
ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
  • Comprend toutes les lignes séparées par deux lignes avant ou après la ligne actuelle.
  • Exemple d'utilisation : pour calculer une moyenne mobile.

Si window_frame_spec utilise la clause BETWEEN :

  • window_frame_boundary_start doit spécifier une limite ne commençant pas plus tard que celle de window_frame_boundary_end. En conséquence :
    1. Si window_frame_boundary_start inclut CURRENT ROW, window_frame_boundary_end ne peut pas contenir PRECEDING.
    2. Si window_frame_boundary_start inclut FOLLOWING, window_frame_boundary_end ne peut pas contenir CURRENT ROW, ni PRECEDING.
  • window_frame_boundary_start ne possède pas de valeur par défaut.

Sinon, la limite de window_frame_spec spécifiée correspond au début du cadre de fenêtrage, et la fin de la limite de ce cadre est définie par défaut sur "CURRENT ROW". Ainsi :

ROWS 10 PRECEDING

est équivalent à :

ROWS BETWEEN 10 PRECEDING AND CURRENT ROW
ROWS

Les cadres de fenêtrage basés sur ROWS calculent le window frame en fonction des décalages physiques par rapport à la ligne actuelle. L'exemple ci-dessous définit un cadre de fenêtrage de taille cinq (au maximum) autour de la ligne actuelle.

ROWS BETWEEN 2 PRECEDING and 2 FOLLOWING

L'expression numeric_expression dans la clause window_frame_clause est interprétée comme étant un nombre de lignes à partir de la ligne actuelle, et doit correspondre à un nombre entier invariable non négatif. Il peut également s'agir d'un paramètre de requête.

Si le window frame pour une ligne donnée s'étend au-delà du début ou de la fin de la partition, le window frame n'inclura que les lignes situées au sein de cette partition.

Prenons l'exemple du tableau suivant avec les colonnes z, x et y :

z x y
1 5 AA
2 2 AA
3 11 AB
4 2 AA
5 8 AC
6 10 AB
7 1 AB

Prenons la fonction analytique suivante comme exemple :

SUM(x) OVER (PARTITION BY y ORDER BY z ROWS BETWEEN 1 PRECEDING AND 1
FOLLOWING)

La clause PARTITION BY divise la table en trois partitions en fonction de leur valeur y, et la clause ORDER BY classe les lignes de chaque partition en fonction de leur valeur z.

Partition 1 sur 3 :

z x y
1 5 AA
2 2 AA
4 2 AA

Partition 2 sur 3 :

z x y
3 11 AB
6 10 AB
7 1 AB

Partition 3 sur 3 :

z x y
5 8 AC

Dans les tableaux ci-dessous, les éléments en gras indiquent la ligne qui est évaluée, et les cellules colorées indiquent toutes les lignes du window frame pour cette ligne.

  • Pour la première ligne de la partition y = AA, le window frame ne comprend que deux lignes, car il n'existe pas de ligne précédente, même si l'élément window_frame_spec indique une taille de fenêtre de trois. Le résultat de la fonction analytique est de sept pour la première ligne.
z x y
1 5 AA
2 2 AA
4 2 AA
  • Pour la deuxième ligne de la partition, le window frame comprend les trois lignes. Le résultat de la fonction analytique est de neuf pour la deuxième ligne.
z x y
1 5 AA
2 2 AA
4 2 AA
  • Pour la dernière ligne de la partition, le window frame ne comprend que deux lignes, car il n'existe pas de ligne suivante. Le résultat de la fonction analytique est de quatre pour la troisième ligne.
z x y
1 5 AA
2 2 AA
4 2 AA
RANGE

Les cadres de fenêtrage basés sur RANGE calculent le window frame en fonction d'une plage de lignes logique qui s'articule autour de la ligne actuelle selon sa valeur de clé ORDER BY. La valeur de la plage fournie est ajoutée ou soustraite à la valeur de clé de la ligne actuelle afin de définir une limite de début ou de fin à la plage pour le window frame.

La clause ORDER BY doit être spécifiée, sauf si la fenêtre se présente sous la forme suivante :

RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

L'expression numeric_expression dans la clause window_frame_clause est interprétée comme étant un décalage par rapport à la valeur de la clé ORDER BY de la ligne actuelle. L'expression numeric_expression doit posséder un type numérique. Les types DATE et TIMESTAMP ne sont actuellement pas compatibles. De plus, l'expression numeric_expression doit correspondre à un nombre entier invariable non négatif ou à un paramètre de requête.

Dans un cadre de fenêtrage basé sur RANGE, il peut y avoir au plus une expression dans la clause ORDER BY et l'expression doit posséder un type numérique.

Exemple de cadre de fenêtrage basé sur RANGE comportant une seule partition :

SELECT x, COUNT(*) OVER ( ORDER BY x
  RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING ) AS count_x
FROM T;

Dans les tableaux ci-dessous, les éléments en gras indiquent la ligne qui est évaluée, et les cellules colorées indiquent toutes les lignes du window frame pour cette ligne.

  • Pour la ligne 1, x = 5. Ainsi, COUNT(*) n'inclura que les lignes où 3 <= x <= 7.
x count_x
5 1
2
11
2
8
10
1
  • Pour la ligne 2, x = 2. Ainsi, COUNT(*) n'inclura que les lignes où 0 <= x <= 4.
X count_x
5 1
2 3
11
2
8
10
1
  • Pour la ligne 3, x = 11. Ainsi, COUNT(*) n'inclura que les lignes où 9 <= x <= 13.
X count_x
5 1
2 3
11 2
2
8
10
1

Clause WINDOW

Syntaxe :

WINDOW window_definition [, ...]
window_definition: window_name AS ( window_specification )

Une clause WINDOW définit une liste de fenêtres nommées, dont l'élément window_name peut être référencé dans les fonctions analytiques de la liste SELECT. Cette méthode s'avère utile lorsque vous souhaitez utiliser la même clause window_frame_clause pour plusieurs fonctions analytiques.

La clause WINDOW ne peut apparaître qu'à la fin d'une clause SELECT, comme indiqué dans la section Syntaxe des requêtes.

Fenêtres nommées

Après avoir défini une clause WINDOW, vous pouvez utiliser les fenêtres nommées dans les fonctions analytiques, mais uniquement dans la liste SELECT. Vous ne pouvez pas utiliser de fenêtres nommées dans la clause ORDER BY. Les fenêtres nommées peuvent apparaître seules ou intégrées à une clause OVER. Les fenêtres nommées peuvent faire référence à des alias de liste SELECT.

Exemples :

SELECT SUM(x) OVER window_name FROM ...
SELECT SUM(x) OVER (
  window_name
  PARTITION BY...
  ORDER BY...
  window_frame_clause)
FROM ...

Lorsqu'elle est intégrée à une clause OVER, la valeur de window_specification associée à window_name doit être compatible avec les clauses PARTITION BY, ORDER BY et window_frame_clause qui figurent dans la même clause OVER.

Les règles suivantes s'appliquent aux fenêtres nommées :

  • Vous ne pouvez faire référence aux fenêtres nommées que dans la liste SELECT. Vous ne pouvez pas y faire référence dans une clause ORDER BY, une requête externe ou une sous-requête.
  • Une fenêtre W1 (nommée ou non) peut faire référence à une fenêtre nommée NW2, en respectant les règles suivantes :
    1. Si W1 est une fenêtre nommée, la fenêtre référencée NW2 doit précéder W1 dans la même clause WINDOW.
    2. W1 ne peut pas contenir de clause PARTITION BY.
    3. W1 et NW2 ne peuvent pas toutes les deux contenir une clause ORDER BY.
    4. NW2 ne peut pas contenir de clause window_frame_clause.
  • Si une fenêtre W1 (nommée ou non) fait référence à une fenêtre nommée NW2, la spécification de fenêtre obtenue est définie à l'aide des éléments suivants :
    1. La clause PARTITION BY à partir de NW2, s'il en existe une.
    2. La clause ORDER BY à partir de W1 ou NW2, si spécifiée pour l'une d'entre elles. Elles ne peuvent pas toutes les deux contenir une clause ORDER BY.
    3. La clause window_frame_clause à partir de W1, s'il en existe une.

Cet article explique le fonctionnement des fonctions de navigation analytiques. Pour obtenir une description des fonctions de navigation analytiques qui sont compatibles avec BigQuery, reportez-vous à la documentation de référence sur les fonctions de navigation.

Les fonctions de navigation calculent généralement des expressions value_expression sur une ligne différente de la ligne actuelle dans le cadre de fenêtrage. La syntaxe de la clause OVER varie selon les fonctions de navigation.

Exigences relatives à la clause OVER :

  • PARTITION BY : facultatif.
  • ORDER BY :
    1. Non autorisée pour PERCENTILE_CONT et PERCENTILE_DISC.
    2. Obligatoire pour FIRST_VALUE, LAST_VALUE, NTH_VALUE, LEAD et LAG.
  • window_frame_clause :
    1. Non autorisée pour PERCENTILE_CONT, PERCENTILE_DISC, LEAD et LAG.
    2. Facultative pour FIRST_VALUE, LAST_VALUE et NTH_VALUE.

Pour toutes les fonctions de navigation, le type de données de résultat est le même type que pour l'expression value_expression.

Fonctions de numérotation

Cet article explique le fonctionnement des fonctions de numérotation analytiques. Pour plus d'informations sur les fonctions de numérotation analytique compatibles avec BigQuery, reportez-vous à la documentation de référence sur les fonctions de numérotation.

Les fonctions de numérotation attribuent des valeurs entières à chaque ligne en fonction de sa position dans la fenêtre spécifiée.

Exemple de RANK(), DENSE_RANK() et ROW_NUMBER() :

SELECT x,
  RANK() OVER (ORDER BY x ASC) AS rank,
  DENSE_RANK() OVER (ORDER BY x ASC) AS dense_rank,
  ROW_NUMBER() OVER (PARTITION BY x ORDER BY y) AS row_num
FROM ...
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(): pour x = 5, rank renvoie 4, car RANK() incrémente la valeur du nombre d'appairages compris dans le précédent groupe de classement des fenêtres.
  • DENSE_RANK() : pour x = 5, dense_rank renvoie 3, car DENSE_RANK() incrémente toujours la valeur d'une unité, en ne sautant jamais une aucune valeur.
  • ROW_NUMBER(): pour x = 5, row_num renvoie 4.

Fonctions analytiques d'agrégation

BigQuery accepte certaines fonctions d'agrégation en tant que fonctions analytiques.

Avec ces fonctions, la clause OVER est simplement ajoutée à l'appel de la fonction d'agrégation. Par ailleurs, la syntaxe d'appel de la fonction reste inchangée. Tout comme les fonctions d'agrégation, ces fonctions analytiques effectuent des agrégations, mais en ciblant le cadre de fenêtrage approprié pour chaque ligne. Les types de données de résultat de ces fonctions analytiques sont les mêmes que ceux de leurs homologues, à savoir les fonctions d'agrégation.

Pour obtenir une description des fonctions analytiques d'agrégation qui sont compatibles avec BigQuery, reportez-vous à la documentation de référence sur les fonctions analytiques d'agrégation.

Cette page vous a-t-elle été utile ? Évaluez-la :

Envoyer des commentaires concernant…

Besoin d'aide ? Consultez notre page d'assistance.