Fonctions Window (Analytique)

Selon la spécification SQL, les fonctions window (également connues sous le nom de fonctions analytiques) sont un type de fonctions d’agrégation qui ne réduisent pas la granularité. Les données agrégées sont sorties avec les données non agrégées.

Syntaxiquement, une fonction Window est appelée en spécifiant son nom, qui est toujours suivi du mot clé OVER() avec les arguments éventuels entre parenthèses. C’est la différence syntaxique avec une fonction normale ou une fonction agrégée. Les fonctions Windows ne peuvent être que dans une liste SELECT et une clause ORDER BY.

La clause OVER peut contenir le regroupement ("selection"), le tri et le fenêtrage.

Disponible en

DSQL

Syntaxe

<window_function> ::=
    <aggregate_function> OVER <window_name_or_spec>
  | <window_function_name> ([<expr> [, <expr> ...]]) OVER <window_name_or_spec>

<window_name_or_spec> ::=
  <window_specification> | window_name

<window_function_name> ::=
     <ranking_function>
   | <navigation_function>

<window_specification> ::=
   ([window_name] [<window_partition>] [<window_order>] [<window_frame>])


<window_partition> ::= PARTITION BY <expr> [, <expr> ...]

<window_order> ::=
  ORDER BY <expr> [<direction>] [<nulls_placement>]
        [, <expr> [<direction>] [<nulls_placement>] ...]

<direction> ::= ASC | DESC

<nulls_placement> ::= NULLS {FIRST | LAST}

<window_frame> ::=
  {ROWS | RANGE} <window_frame_extent>

<window_frame_extent> ::=
  <window_frame_start> | <window_frame_between>

<window_frame_start> ::=
  UNBOUNDED PRECEDING | <expr> PRECEDING | CURRENT ROW

<window_frame_between> ::=
  BETWEEN <window_frame_bound_1> AND <window_frame_bound_2>

<window_frame_bound_1> ::=
    UNBOUNDED PRECEDING | <expr> PRECEDING | CURRENT ROW
  | <expr> FOLLOWING

<window_frame_bound_2> ::=
    <expr> PRECEDING | CURRENT ROW | <expr> FOLLOWING
  | UNBOUNDED FOLLOWING

<aggregate_function> ::= Fonctions agrégées

<ranking_function> ::=
    DENSE_RANK
  | RANK
  | PERCENT_RANK
  | CUME_DIST
  | NTILE
  | ROW_NUMBER

<navigation_function> ::=
    LEAD
  | LAG
  | FIRST_VALUE
  | LAST_VALUE
  | NTH_VALUE

<query-spec> ::=
  SELECT
    [<first-clause>] [<skip-clause>]
    [<distinct-clause>]
    <select-list>
    <from-clause>
    [<where-clause>]
    [<group-clause>]
    [<having-clause>]
    [<named-windows-clause>]
    [<order-clause>]
    [<rows-clause>]
    [<offset-clause>] [<limit clause>]
    [<plan-clause>]

<named-windows-clause> ::=
  WINDOW <window-definition> [, <window-definition>] ...

<window-definition> ::=
  window_name AS <window_specification>

Table 1. Options pour les fonctions windows
Paramètre	Description
expr	Expression : peut contenir une colonne de table, une constante, une variable, une expression, un scalaire ou une fonction d’agrégation. Les fonctions de Window ne sont pas autorisées comme expression.
window_partition	Expression de partition.
window_order	Expression de tri.
window_frame	Une expression pour définir le cadre de la fonction window
window_name	Nom de la fonction.
direction	Direction du tri.
nulls_placement	La position du pseudo-digit `NULL` dans l’ensemble trié.
aggregate_function	Fonction agrégée.
ranking_function	Fonction de classement.
navigation_function	Fonction de navigation.

Fonctions agrégées

Toutes les fonctions agrégées peuvent être utilisées comme des fonctions de Window, en ajoutant une clause OVER.

Supposons que nous ayons une table EMPLOYEE avec les colonnes ID, NAME et SALARY. Nous avons besoin de montrer pour chaque employé, son salaire correspondant et le pourcentage de la masse salariale.

Une simple requête permet de résoudre ce problème comme suit :

select
    id,
    department,
    salary,
    salary / (select sum(salary) from employee) percentage
from employee
order by id;

Résultat

id department salary percentage
-- ---------- ------ ----------
1  R & D       10.00     0.2040
2  SALES       12.00     0.2448
3  SALES        8.00     0.1632
4  R & D        9.00     0.1836
5  R & D       10.00     0.2040

La requête est répétitive et peut prendre beaucoup de temps, surtout si EMPLOYEE est une représentation complexe.

Cette requête peut être réécrite sous une forme plus rapide et plus élégante en utilisant les fonctions de la Window :

select
  id,
  department,
  salary,
  salary / sum(salary) OVER () percentage
from employee
order by id;

Ici, sum(salaire) OVER () calcule la somme de tous les salaires de la requête (table des employés).

Fonctions Window (Analytique)

Fonctions Window (analytique)

Fonctions agrégées