Fonctions de fenêtre MaxCompute - syntaxe, paramètres, exemples - MaxCompute

Remarques d'utilisation

Les fonctions de fenêtre ne peuvent apparaître que dans les instructions SELECT.
Une fonction de fenêtre ne peut pas être imbriquée avec d'autres fonctions de fenêtre ou des fonctions d'agrégation.
Les fonctions de fenêtre ne peuvent pas être utilisées conjointement avec des fonctions d'agrégation au même niveau.

Index

MaxCompute SQL prend en charge les fonctions de fenêtre suivantes.

Fonction

Fonctionnalités

AVG

Calcule la valeur moyenne des données dans une fenêtre.

CLUSTER_SAMPLE

Effectue un échantillonnage aléatoire. Renvoie true si la ligne est échantillonnée.

COUNT

Compte le nombre d'enregistrements dans une fenêtre.

CUME_DIST

Calcule la distribution cumulative.

DENSE_RANK

Calcule le rang. Les rangs sont consécutifs.

FIRST_VALUE

Renvoie la valeur de la première ligne du cadre de fenêtre de la ligne actuelle.

LAG

Renvoie la valeur de la Nième ligne précédant la ligne actuelle dans une partition.

LAST_VALUE

Renvoie la valeur de la dernière ligne du cadre de fenêtre de la ligne actuelle.

LEAD

Renvoie la valeur de la Nième ligne suivant la ligne actuelle dans une partition.

MAX

Calcule la valeur maximale dans une fenêtre.

MEDIAN

Calcule la valeur médiane dans une fenêtre.

MIN

Calcule la valeur minimale dans une fenêtre.

NTILE

Divise les données ordonnées en N groupes de taille égale et renvoie le numéro du groupe (de 1 à N) pour chaque ligne.

NTH_VALUE

Renvoie la valeur de la Nième ligne du cadre de fenêtre de la ligne actuelle.

PERCENT_RANK

Calcule le rang sous forme de pourcentage.

PERCENTILE_CONT

Calcule le centile exact.

PERCENTILE_DISC

Calcule une valeur de centile donnée en triant la colonne spécifiée par ordre croissant.

RANK

Calcule le rang. Les rangs peuvent ne pas être consécutifs.

ROW_NUMBER

Calcule le numéro de ligne, en commençant par 1.

STDDEV

Calcule l'écart type de la population. Il s'agit d'un alias pour STDDEV_POP.

STDDEV_SAMP

Calcule l'écart type de l'échantillon.

SUM

Calcule la somme des données dans une fenêtre.

Syntaxe des fonctions de fenêtre

Syntaxe des fonctions de fenêtre :

<function_name>([distinct][<expression> [, ...]]) over (<window_definition>)
<function_name>([distinct][<expression> [, ...]]) over <window_name>

function_name : une fonction de fenêtre intégrée, une fonction d'agrégation ou une fonction d'agrégation définie par l'utilisateur (UDAF).
expression : le format de la fonction, qui doit être conforme à la syntaxe de la fonction.
windowing_definition : la définition de la fenêtre. Pour plus d'informations sur la syntaxe, consultez la section windowing_definition.
window_name : le nom de la fenêtre. Vous pouvez utiliser le mot-clé window pour définir une fenêtre personnalisée et attribuer un nom à la windowing_definition. La syntaxe d'une définition de fenêtre nommée (named_window_def) est la suivante :
```
window <window_name> as (<window_definition>)
```
La section suivante décrit les positions des instructions personnalisées dans SQL :
```
select ... from ... [where ...] [group by ...] [having ...] named_window_def [order by ...] [limit ...]
```

windowing_definition

Syntaxe de windowing_definition :

--partition_clause:
[partition by <expression> [, ...]]
--orderby_clause:
[order by <expression> [asc|desc][nulls {first|last}] [, ...]]
[<frame_clause>]

Lorsque vous ajoutez une fonction de fenêtre à une instruction SELECT, les données sont partitionnées et triées en fonction des clauses partition by et order by de la définition de la fenêtre. Si vous ne spécifiez pas de clause partition by, toutes les données sont traitées comme une seule partition. Si vous ne spécifiez pas de clause order by, l'ordre des données au sein d'une partition n'est pas garanti. Pour chaque ligne, appelée ligne actuelle, un segment de données est extrait de la partition sur la base de la clause frame_clause afin de former la fenêtre pour cette ligne. La fonction de fenêtre calcule ensuite un résultat pour la ligne actuelle en fonction des données présentes dans sa fenêtre.

partition by <expression> [, ...] : facultatif. Spécifie la partition. Les lignes ayant les mêmes valeurs de colonne de clé de partition se trouvent dans la même partition. Pour plus d'informations sur le format, consultez la section Opérations sur les tables.
order by <expression> [asc|desc][nulls {first|last}] [, ...] : facultatif. Spécifie comment les données sont triées au sein d'une partition.

Remarque
Si les lignes ont les mêmes valeurs order by, l'ordre de tri n'est pas garanti. Pour assurer un ordre cohérent, veillez à ce que les valeurs order by soient aussi uniques que possible.
frame_clause : facultatif. Définit les limites de la fenêtre. Pour plus d'informations sur frame_clause, consultez la section frame_clause.

filter_clause

Syntaxe de filter_clause :

FILTER (WHERE filter_condition)

filter_condition est une expression booléenne, utilisée de la même manière que la clause WHERE dans une instruction select ... from ... where.

Si vous fournissez une clause FILTER, seules les lignes pour lesquelles filter_condition est évaluée à true sont incluses dans le cadre de la fenêtre. Pour les fonctions de fenêtre d'agrégation (telles que COUNT, SUM, AVG, MAX et MIN), une valeur est toujours renvoyée pour chaque ligne. Toutefois, les lignes pour lesquelles l'expression FILTER n'est pas évaluée à true (par exemple NULL ou false) ne sont pas incluses dans le cadre de la fenêtre pour le calcul de chaque ligne. NULL est traité comme false.

Exemple

Préparation des données

-- Create a table.
CREATE TABLE IF NOT EXISTS mf_window_fun(key BIGINT,value BIGINT) STORED AS ALIORC;

-- Insert data.
insert into mf_window_fun values (1,100),(2,200),(1,150),(2,250),(3,300),(4,400),(5,500),(6,600),(7,700);

-- Query data from the mf_window_fun table.
select * from mf_window_fun;

-- The following result is returned:
+------------+------------+
| key        | value      |
+------------+------------+
| 1          | 100        |
| 2          | 200        |
| 1          | 150        |
| 2          | 250        |
| 3          | 300        |
| 4          | 400        |
| 5          | 500        |
| 6          | 600        |
| 7          | 700        |
+------------+------------+

Interrogez la somme cumulative des lignes dont la valeur est supérieure à 100 dans la fenêtre.

select key,sum(value) filter(where value > 100) 
       over (partition by key order by key)  
       from mf_window_fun;

Le résultat suivant est renvoyé :

+------------+------------+
| key        | _c1        |
+------------+------------+
| 1          | NULL       | -- Skipped
| 1          | 150        |
| 2          | 200        |
| 2          | 450        |
| 3          | 300        |
| 4          | 400        |
| 5          | 500        |
| 6          | 600        |
| 7          | 700        |
+------------+------------+

Remarque

La clause FILTER ne supprime pas les lignes qui échouent à la condition filter_condition du résultat de la requête. Elle les exclut uniquement du calcul de la fonction de fenêtre. Pour supprimer ces lignes de la sortie finale, vous devez utiliser une clause select ... from ... where. La valeur de la fonction de fenêtre pour une ligne exclue n'est pas 0 ou NULL. Au lieu de cela, elle hérite de la valeur de la ligne précédente.
Vous pouvez utiliser la clause FILTER uniquement avec des fonctions de fenêtre d'agrégation, telles que COUNT, SUM, AVG, MAX, MIN et WM_CONCAT. Vous ne pouvez pas utiliser la clause FILTER avec des fonctions non agrégées telles que RANK, ROW_NUMBER ou NTILE. Dans le cas contraire, une erreur de syntaxe se produit.
Pour utiliser la syntaxe FILTER dans une fonction de fenêtre, vous devez activer l'indicateur de session suivant : set odps.sql.window.function.newimpl=true;.

frame_clause

Syntaxe de frame_clause :

-- Format 1
{ROWS|RANGE|GROUPS} <frame_start> [<frame_exclusion>]
-- Format 2
{ROWS|RANGE|GROUPS} between <frame_start> and <frame_end> [<frame_exclusion>]

frame_clause est un intervalle fermé qui définit les limites de la fenêtre. Il inclut les lignes aux positions frame_start et frame_end.

ROWS|RANGE|GROUPS : obligatoire. Le type de frame_clause. Les règles d'implémentation pour frame_start et frame_end varient selon le type.
- ROWS : définit les limites de la fenêtre en fonction du nombre de lignes.
- RANGE : définit les limites de la fenêtre en comparant les valeurs de la colonne order by. Généralement, une clause order by est spécifiée dans la définition de la fenêtre. Si aucune clause order by n'est spécifiée, toutes les lignes d'une partition ont la même valeur de colonne order by. Les valeurs NULL sont considérées comme égales.
- GROUPS : toutes les lignes d'une partition ayant la même valeur de colonne order by forment un GROUP. Si aucune clause order by n'est spécifiée, toutes les lignes de la partition forment un seul GROUP. Les valeurs NULL sont considérées comme égales.

frame_start et frame_end : spécifient les limites de début et de fin de la fenêtre. frame_start est obligatoire. frame_end est facultatif. S'il est omis, la valeur par défaut est CURRENT ROW.

La position spécifiée par frame_start doit précéder la position spécifiée par frame_end, ou correspondre à la position de frame_end. En d'autres termes, frame_start est plus proche du début de la partition que frame_end. Le début de la partition correspond à la position de la première ligne après le tri des données par l'instruction order by dans la définition de la fenêtre. Le tableau suivant décrit les valeurs valides et la logique pour frame_start et frame_end lorsque le type de frame_clause est ROWS, RANGE ou GROUPS.

Type de frame_clause	Valeur frame_start/frame_end	Description
ROWS, RANGE, GROUPS	UNBOUNDED PRECEDING	La première ligne de la partition. Le comptage commence à 1.
ROWS, RANGE, GROUPS	UNBOUNDED FOLLOWING	La dernière ligne de la partition.
ROWS	CURRENT ROW	La position de la ligne actuelle. Chaque ligne de données correspond à un résultat de fonction de fenêtre. La ligne actuelle est la ligne pour laquelle le résultat de la fonction de fenêtre est en cours de calcul.
	offset PRECEDING	La position qui se trouve à `offset` lignes avant la ligne actuelle, vers le début de la partition. Par exemple, `0 PRECEDING` fait référence à la ligne actuelle, et `1 PRECEDING` fait référence à la ligne précédente. `offset` doit être un entier non négatif.
	offset FOLLOWING	La position qui se trouve à `offset` lignes après la ligne actuelle, vers la fin de la partition. Par exemple, `0 FOLLOWING` fait référence à la ligne actuelle, et `1 FOLLOWING` fait référence à la ligne suivante. `offset` doit être un entier non négatif.
RANGE	CURRENT ROW	En tant que frame_start, il fait référence à la position de la première ligne ayant la même valeur de colonne `order by` que la ligne actuelle. En tant que frame_end, il fait référence à la position de la dernière ligne ayant la même valeur de colonne `order by` que la ligne actuelle.
	offset PRECEDING	Les positions de frame_start et frame_end dépendent de la séquence `order by`. Supposons que la fenêtre soit triée par X. Xi représente la valeur X de la i-ème ligne, et Xc représente la valeur X de la ligne actuelle. Les positions sont décrites comme suit : Lorsque `order by` est croissant : frame_start : la position de la première ligne qui satisfait `Xc - Xi <= offset`. frame_end : la position de la dernière ligne qui satisfait `Xc - Xi >= offset`. Lorsque `order by` est décroissant : frame_start : la position de la première ligne qui satisfait `Xi - Xc <= offset`. frame_end : la position de la dernière ligne qui satisfait `Xi - Xc >= offset`. Les types de données pris en charge pour la colonne `order by` sont : TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, DECIMAL, DATETIME, DATE et TIMESTAMP. La syntaxe pour l'`offset` des types de date est la suivante : `N` : représente N jours ou N secondes. Il doit s'agir d'un entier non négatif. Pour DATETIME et TIMESTAMP, il représente N secondes. Pour DATE, il représente N jours. `interval 'N' {YEAR\MONTH\DAY\HOUR\MINUTE\SECOND}` : représente N années, mois, jours, heures, minutes ou secondes. Par exemple, `INTERVAL '3' YEAR` représente 3 ans. `INTERVAL 'N-M' YEAR TO MONTH` : représente N années et M mois. Par exemple, `INTERVAL '1-3' YEAR TO MONTH` représente 1 an et 3 mois. `INTERVAL 'D[ H[:M[:S[:N]]]]' DAY TO SECOND` : représente D jours, H heures, M minutes, S secondes et N nanosecondes. Par exemple, `INTERVAL '1 2:3:4:5' DAY TO SECOND` représente 1 jour, 2 heures, 3 minutes, 4 secondes et 5 nanosecondes.
	offset FOLLOWING	Les positions de frame_start et frame_end dépendent de la séquence `order by`. Supposons que la fenêtre soit triée par X. Xi représente la valeur X de la i-ème ligne, et Xc représente la valeur X de la ligne actuelle. Les positions sont décrites comme suit : Lorsque `order by` est croissant : frame_start : la position de la première ligne qui satisfait `Xi - Xc >= offset`. frame_end : la position de la dernière ligne qui satisfait `Xi - Xc <= offset`. Lorsque `order by` est décroissant : frame_start : la position de la première ligne qui satisfait `Xc - Xi >= offset`. frame_end : la position de la dernière ligne qui satisfait `Xc - Xi <= offset`.
GROUPS	CURRENT ROW	En tant que frame_start, il fait référence à la première ligne du GROUP auquel appartient la ligne actuelle. En tant que frame_end, il fait référence à la dernière ligne du GROUP auquel appartient la ligne actuelle.
	offset PRECEDING	En tant que frame_start, il fait référence à la position de la première ligne du GROUP qui se trouve à `offset` GROUPs avant le GROUP de la ligne actuelle, vers le début de la partition. En tant que frame_end, il fait référence à la position de la dernière ligne du GROUP qui se trouve à `offset` GROUPs avant le GROUP de la ligne actuelle, vers le début de la partition. Remarque Vous ne pouvez pas définir frame_start sur UNBOUNDED FOLLOWING ni frame_end sur UNBOUNDED PRECEDING.
	offset FOLLOWING	En tant que frame_start, il fait référence à la position de la première ligne du GROUP qui se trouve à `offset` GROUPs après le GROUP de la ligne actuelle, vers la fin de la partition. En tant que frame_end, il fait référence à la position de la dernière ligne du GROUP qui se trouve à `offset` GROUPs après le GROUP de la ligne actuelle, vers la fin de la partition. Remarque Vous ne pouvez pas définir frame_start sur UNBOUNDED FOLLOWING ni frame_end sur UNBOUNDED PRECEDING.

frame_exclusion : facultatif. Utilisé pour exclure une partie des données de la fenêtre. Les valeurs valides sont :
- EXCLUDE NO OTHERS : n'exclut aucune donnée.
- EXCLUDE CURRENT ROW : exclut la ligne actuelle.
- EXCLUDE GROUP : exclut l'intégralité du GROUP, c'est-à-dire toutes les données de la partition ayant la même valeur order by que la ligne actuelle.
- EXCLUDE TIES : exclut toutes les lignes partageant la même valeur order by que la ligne actuelle, à l'exception de la ligne actuelle elle-même.

frame_clause par défaut

Si vous ne spécifiez pas de frame_clause, MaxCompute utilise une clause frame_clause par défaut pour déterminer les limites des données incluses dans la fenêtre. La clause frame_clause par défaut est la suivante :

Lorsque le mode compatible Hive est activé (set odps.sql.hive.compatible=true;), la clause frame_clause par défaut est la suivante, ce qui correspond au comportement de la plupart des autres systèmes SQL.
```
RANGE between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE NO OTHERS
```
Lorsque le mode compatible Hive est désactivé (set odps.sql.hive.compatible=false;), si une clause order by est spécifiée et que la fonction de fenêtre est AVG, COUNT, MAX, MIN, STDDEV, STDDEV_POP, STDDEV_SAMP ou SUM, la clause frame_clause par défaut est de type ROWS.
```
ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE NO OTHERS
```

Exemples de limites de fenêtre

Supposons que la table tbl ait la structure pid: bigint, oid: bigint, rid: bigint et contienne les données suivantes :

+------------+------------+------------+
| pid        | oid        | rid        |
+------------+------------+------------+
| 1          | NULL       | 1          |
| 1          | NULL       | 2          |
| 1          | 1          | 3          |
| 1          | 1          | 4          |
| 1          | 2          | 5          |
| 1          | 4          | 6          |
| 1          | 7          | 7          |
| 1          | 11         | 8          |
| 2          | NULL       | 9          |
| 2          | NULL       | 10         |
+------------+------------+------------+

Fenêtre de type ROW

Définition de fenêtre 1

partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW
--SQL statement is as follows.
select pid, 
       oid, 
       rid, 
collect_list(rid) over(partition by pid order by 
oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW) as window from tbl;

Le résultat suivant est renvoyé :

+------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | [1]    |
| 1          | NULL       | 2          | [1, 2] |
| 1          | 1          | 3          | [1, 2, 3] |
| 1          | 1          | 4          | [1, 2, 3, 4] |
| 1          | 2          | 5          | [1, 2, 3, 4, 5] |
| 1          | 4          | 6          | [1, 2, 3, 4, 5, 6] |
| 1          | 7          | 7          | [1, 2, 3, 4, 5, 6, 7] |
| 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 2          | NULL       | 9          | [9]    |
| 2          | NULL       | 10         | [9, 10] |
+------------+------------+------------+--------+

Définition de fenêtre 2

partition by pid order by oid ROWS between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING
--SQL statement is as follows.
select pid, 
       oid, 
       rid, 
collect_list(rid) over(partition by pid order by 
oid ROWS between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING) as window from tbl;

Le résultat suivant est renvoyé :

+------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1          | NULL       | 2          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1          | 1          | 3          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1          | 1          | 4          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1          | 2          | 5          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1          | 4          | 6          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1          | 7          | 7          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 2          | NULL       | 9          | [9, 10] |
| 2          | NULL       | 10         | [9, 10] |
+------------+------------+------------+--------+

Définition de fenêtre 3

partition by pid order by oid ROWS between 1 FOLLOWING and 3 FOLLOWING
--SQL statement is as follows.
select pid, 
       oid, 
       rid, 
collect_list(rid) over(partition by pid order by 
oid ROWS between 1 FOLLOWING and 3 FOLLOWING) as window from tbl;

Le résultat suivant est renvoyé :

+------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | [2, 3, 4] |
| 1          | NULL       | 2          | [3, 4, 5] |
| 1          | 1          | 3          | [4, 5, 6] |
| 1          | 1          | 4          | [5, 6, 7] |
| 1          | 2          | 5          | [6, 7, 8] |
| 1          | 4          | 6          | [7, 8] |
| 1          | 7          | 7          | [8]    |
| 1          | 11         | 8          | NULL   |
| 2          | NULL       | 9          | [10]   |
| 2          | NULL       | 10         | NULL   |
+------------+------------+------------+--------+

Définition de fenêtre 4

partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE CURRENT ROW
--SQL statement is as follows.
select pid, 
oid, 
rid, 
collect_list(rid) over(partition by pid order by 
oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE CURRENT ROW) as window from tbl;

Le résultat suivant est renvoyé :

+------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | NULL   |
| 1          | NULL       | 2          | [1]    |
| 1          | 1          | 3          | [1, 2] |
| 1          | 1          | 4          | [1, 2, 3] |
| 1          | 2          | 5          | [1, 2, 3, 4] |
| 1          | 4          | 6          | [1, 2, 3, 4, 5] |
| 1          | 7          | 7          | [1, 2, 3, 4, 5, 6] |
| 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7] |
| 2          | NULL       | 9          | NULL   |
| 2          | NULL       | 10         | [9]    |
+------------+------------+------------+--------+

Définition de fenêtre 5

partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE GROUP
--SQL statement is as follows.
select pid, 
       oid, 
       rid, 
collect_list(rid) over(partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE GROUP) as window from tbl;

Le résultat suivant est renvoyé :

+------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | NULL   |
| 1          | NULL       | 2          | NULL   |
| 1          | 1          | 3          | [1, 2] |
| 1          | 1          | 4          | [1, 2] |
| 1          | 2          | 5          | [1, 2, 3, 4] |
| 1          | 4          | 6          | [1, 2, 3, 4, 5] |
| 1          | 7          | 7          | [1, 2, 3, 4, 5, 6] |
| 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7] |
| 2          | NULL       | 9          | NULL   |
| 2          | NULL       | 10         | NULL   |
+------------+------------+------------+--------+

Définition de fenêtre 6

partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE TIES
--SQL statement is as follows.
select pid, 
       oid, 
       rid, 
collect_list(rid) over(partition by pid order by 
oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE TIES) as window from tbl;

Le résultat suivant est renvoyé :

+------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | [1]    |
| 1          | NULL       | 2          | [2]    |
| 1          | 1          | 3          | [1, 2, 3] |
| 1          | 1          | 4          | [1, 2, 4] |
| 1          | 2          | 5          | [1, 2, 3, 4, 5] |
| 1          | 4          | 6          | [1, 2, 3, 4, 5, 6] |
| 1          | 7          | 7          | [1, 2, 3, 4, 5, 6, 7] |
| 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 2          | NULL       | 9          | [9]    |
| 2          | NULL       | 10         | [10]   |
+------------+------------+------------+--------+

En comparant les résultats de la colonne window pour les lignes où rid vaut 2, 4 et 10 dans cet exemple et le précédent, vous pouvez observer la différence entre EXCLUDE CURRENT ROW et EXCLUDE GROUP. Pour EXCLUDE GROUP, dans la même partition (où pid est égal), toutes les données ayant la même valeur oid que la ligne actuelle sont exclues.

Fenêtre de type RANGE

Définition de fenêtre 1

partition by pid order by oid RANGE between UNBOUNDED PRECEDING and CURRENT ROW
--SQL statement is as follows.
select pid, 
       oid, 
       rid, 
collect_list(rid) over(partition by pid order by 
oid RANGE between UNBOUNDED PRECEDING and CURRENT ROW) as window from tbl;

Le résultat suivant est renvoyé :

+------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | [1, 2] |
| 1          | NULL       | 2          | [1, 2] |
| 1          | 1          | 3          | [1, 2, 3, 4] |
| 1          | 1          | 4          | [1, 2, 3, 4] |
| 1          | 2          | 5          | [1, 2, 3, 4, 5] |
| 1          | 4          | 6          | [1, 2, 3, 4, 5, 6] |
| 1          | 7          | 7          | [1, 2, 3, 4, 5, 6, 7] |
| 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 2          | NULL       | 9          | [9, 10] |
| 2          | NULL       | 10         | [9, 10] |
+------------+------------+------------+--------+

Lorsque CURRENT ROW est utilisé comme frame_end, il inclut toutes les lignes jusqu'à la dernière ligne ayant la même valeur order by oid que la ligne actuelle. Par conséquent, le résultat window pour l'enregistrement où rid vaut 1 est [1, 2].

Définition de fenêtre 2

partition by pid order by oid RANGE between CURRENT ROW and UNBOUNDED FOLLOWING
--SQL statement is as follows.
select pid, 
       oid, 
       rid, 
collect_list(rid) over(partition by pid order by 
oid RANGE between CURRENT ROW and UNBOUNDED FOLLOWING) as window from tbl;

Le résultat suivant est renvoyé :

+------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1          | NULL       | 2          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1          | 1          | 3          | [3, 4, 5, 6, 7, 8] |
| 1          | 1          | 4          | [3, 4, 5, 6, 7, 8] |
| 1          | 2          | 5          | [5, 6, 7, 8] |
| 1          | 4          | 6          | [6, 7, 8] |
| 1          | 7          | 7          | [7, 8] |
| 1          | 11         | 8          | [8]    |
| 2          | NULL       | 9          | [9, 10] |
| 2          | NULL       | 10         | [9, 10] |
+------------+------------+------------+--------+

Définition de fenêtre 3

partition by pid order by oid RANGE between 3 PRECEDING and 1 PRECEDING
--SQL statement is as follows.

select pid, 
       oid, 
       rid, 
collect_list(rid) over(partition by pid order by 
oid RANGE between 3 PRECEDING and 1 PRECEDING) as window from tbl;

Le résultat suivant est renvoyé :

+------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | [1, 2] |
| 1          | NULL       | 2          | [1, 2] |
| 1          | 1          | 3          | NULL   |
| 1          | 1          | 4          | NULL   |
| 1          | 2          | 5          | [3, 4] |
| 1          | 4          | 6          | [3, 4, 5] |
| 1          | 7          | 7          | [6]    |
| 1          | 11         | 8          | NULL   |
| 2          | NULL       | 9          | [9, 10] |
| 2          | NULL       | 10         | [9, 10] |
+------------+------------+------------+--------+

Pour les lignes dont la valeur order by oid est NULL, si vous utilisez offset {PRECEDING|FOLLOWING} et que offset n'est pas UNBOUNDED, la limite est déterminée comme suit : lorsqu'il est utilisé comme frame_start, il pointe vers la première ligne ayant une valeur order by NULL dans la partition. Lorsqu'il est utilisé comme frame_end, il pointe vers la dernière ligne ayant une valeur order by NULL.

Fenêtre de type GROUPS

La définition de la fenêtre est la suivante :

partition by pid order by oid GROUPS between 2 PRECEDING and CURRENT ROW
--SQL statement is as follows.
select pid, 
       oid, 
       rid, 
collect_list(rid) over(partition by pid order by 
oid GROUPS between 2 PRECEDING and CURRENT ROW) as window from tbl;

Le résultat suivant est renvoyé :

+------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | [1, 2] |
| 1          | NULL       | 2          | [1, 2] |
| 1          | 1          | 3          | [1, 2, 3, 4] |
| 1          | 1          | 4          | [1, 2, 3, 4] |
| 1          | 2          | 5          | [1, 2, 3, 4, 5] |
| 1          | 4          | 6          | [3, 4, 5, 6] |
| 1          | 7          | 7          | [5, 6, 7] |
| 1          | 11         | 8          | [6, 7, 8] |
| 2          | NULL       | 9          | [9, 10] |
| 2          | NULL       | 10         | [9, 10] |
+------------+------------+------------+--------+

Données d'exemple

Les exemples suivants utilisent ces données d'exemple. Créez et remplissez la table emp :

create table if not exists emp
   (empno bigint,
    ename string,
    job string,
    mgr bigint,
    hiredate datetime,
    sal bigint,
    comm bigint,
    deptno bigint);
tunnel upload emp.txt emp;

Le fichier emp.txt contient les données suivantes :

7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20
7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30
7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20
7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30
7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10
7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20
7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10
7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30
7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20
7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30
7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20
7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10
7948,JACCKA,CLERK,7782,1981-04-12 00:00:00,5000,,10
7956,WELAN,CLERK,7649,1982-07-20 00:00:00,2450,,10
7956,TEBAGE,CLERK,7748,1982-12-30 00:00:00,1300,,10

AVG

Syntaxe

double avg([distinct] double <expr>) over ([partition_clause] [orderby_clause] [frame_clause])
decimal avg([distinct] decimal <expr>) over ([partition_clause] [orderby_clause] [frame_clause])

Description

Renvoie la valeur moyenne de expr dans une fenêtre.
Paramètres
- expr : obligatoire. Expression pour laquelle calculer le résultat. Elle doit être de type DOUBLE ou DECIMAL.
  - Si la valeur d'entrée est de type STRING ou BIGINT, elle est implicitement convertie en type DOUBLE pour le calcul. Une erreur est renvoyée pour les autres types de données.
  - Si la valeur d'entrée est NULL, la ligne n'est pas incluse dans le calcul.
  - Si vous spécifiez le mot-clé distinct, la fonction calcule la moyenne des valeurs uniques.
- partition_clause, orderby_clause et frame_clause : pour plus d'informations, consultez la section windowing_definition.
Valeur de retour

Si expr est de type DECIMAL, une valeur DECIMAL est renvoyée. Sinon, une valeur DOUBLE est renvoyée. Si toutes les valeurs de expr sont NULL, NULL est renvoyé.

Exemples

Exemple 1 : Partitionnez par département (deptno) et calculez le salaire moyen (sal) sans tri. La fonction calcule le salaire moyen pour l'ensemble de la partition (toutes les lignes ayant le même deptno). La commande est la suivante :

select deptno, sal, avg(sal) over (partition by deptno) from emp;

Le résultat suivant est renvoyé :

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 2916.6666666666665 |   -- This is the first row of the window. The value is the cumulative average from the first to the sixth row.
| 10         | 2450       | 2916.6666666666665 |   -- The value is the cumulative average from the first to the sixth row.
| 10         | 5000       | 2916.6666666666665 |   -- The value is the cumulative average from the first to the sixth row.
| 10         | 1300       | 2916.6666666666665 |
| 10         | 5000       | 2916.6666666666665 |
| 10         | 2450       | 2916.6666666666665 |
| 20         | 3000       | 2175.0     |
| 20         | 3000       | 2175.0     |
| 20         | 800        | 2175.0     |
| 20         | 1100       | 2175.0     |
| 20         | 2975       | 2175.0     |
| 30         | 1500       | 1566.6666666666667 |
| 30         | 950        | 1566.6666666666667 |
| 30         | 1600       | 1566.6666666666667 |
| 30         | 1250       | 1566.6666666666667 |
| 30         | 1250       | 1566.6666666666667 |
| 30         | 2850       | 1566.6666666666667 |
+------------+------------+------------+

Exemple 2 : En mode non compatible Hive, partitionnez par département (deptno), triez par salaire (sal) et calculez le salaire moyen. La fonction calcule une moyenne cumulative de la première ligne de la partition à la ligne actuelle. Les commandes sont les suivantes :

-- Disable Hive compatible mode.
set odps.sql.hive.compatible=false;
-- Execute the following SQL command.
select deptno, sal, avg(sal) over (partition by deptno order by sal) from emp;

Le résultat suivant est renvoyé :

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 1300.0     |           -- First row of the window.
| 10         | 1300       | 1300.0     |           -- Cumulative average from the first to the second row.
| 10         | 2450       | 1683.3333333333333 |   -- Cumulative average from the first to the third row.
| 10         | 2450       | 1875.0     |           -- Cumulative average from the first to the fourth row.
| 10         | 5000       | 2500.0     |           -- Cumulative average from the first to the fifth row.
| 10         | 5000       | 2916.6666666666665 |   -- Cumulative average from the first to the sixth row.
| 20         | 800        | 800.0      |
| 20         | 1100       | 950.0      |
| 20         | 2975       | 1625.0     |
| 20         | 3000       | 1968.75    |
| 20         | 3000       | 2175.0     |
| 30         | 950        | 950.0      |
| 30         | 1250       | 1100.0     |
| 30         | 1250       | 1150.0     |
| 30         | 1500       | 1237.5     |
| 30         | 1600       | 1310.0     |
| 30         | 2850       | 1566.6666666666667 |
+------------+------------+------------+

Exemple 3 : En mode compatible Hive, partitionnez par département (deptno), triez par salaire (sal) et calculez le salaire moyen. La fonction calcule une moyenne cumulative de la première ligne de la partition au dernier pair de la ligne actuelle (les lignes ayant le même sal ont la même moyenne). Les commandes sont les suivantes :

-- Enable Hive compatible mode.
set odps.sql.hive.compatible=true;
-- Execute the following SQL command.
select deptno, sal, avg(sal) over (partition by deptno order by sal) from emp;

Le résultat suivant est renvoyé :

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 1300.0     |          -- First row of the window. Since the sal of the first and second rows are the same, the average for the first row is the cumulative average of the first two rows.
| 10         | 1300       | 1300.0     |          -- Cumulative average from the first to the second row.
| 10         | 2450       | 1875.0     |          -- Since the sal of the third and fourth rows are the same, the average for the third row is the cumulative average of the first four rows.
| 10         | 2450       | 1875.0     |          -- Cumulative average from the first to the fourth row.
| 10         | 5000       | 2916.6666666666665 |
| 10         | 5000       | 2916.6666666666665 |
| 20         | 800        | 800.0      |
| 20         | 1100       | 950.0      |
| 20         | 2975       | 1625.0     |
| 20         | 3000       | 2175.0     |
| 20         | 3000       | 2175.0     |
| 30         | 950        | 950.0      |
| 30         | 1250       | 1150.0     |
| 30         | 1250       | 1150.0     |
| 30         | 1500       | 1237.5     |
| 30         | 1600       | 1310.0     |
| 30         | 2850       | 1566.6666666666667 |
+------------+------------+------------+

CLUSTER_SAMPLE

Syntaxe

boolean cluster_sample(bigint <N>) OVER ([partition_clause])
boolean cluster_sample(bigint <N>, bigint <M>) OVER ([partition_clause])

Description
- cluster_sample(bigint <N>): sélectionne aléatoirement N lignes dans la partition.
- cluster_sample(bigint <N>, bigint <M>): sélectionne aléatoirement une fraction de lignes (M/N) dans la partition. Le nombre de lignes sélectionnées est approximativement égal à partition_row_count × M / N, où partition_row_count représente le nombre de lignes dans la partition.
Paramètres
- N: Obligatoire. Une constante BIGINT. Si N a la valeur NULL, la valeur renvoyée est NULL.
- M: Obligatoire. Une constante BIGINT. Si M a la valeur NULL, la valeur renvoyée est NULL.
- partition_clause: Facultatif. Pour plus d'informations, consultez windowing_definition.
Valeur renvoyée

Renvoie une valeur BOOLEAN.

Exemple

Pour sélectionner environ 20 % des lignes de chaque groupe, exécutez la commande suivante :

select deptno, sal
    from (
        select deptno, sal, cluster_sample(5, 1) over (partition by deptno) as flag
        from emp
        ) sub
    where flag = true;

Le résultat suivant est renvoyé :

+------------+------------+
| deptno     | sal        |
+------------+------------+
| 10         | 1300       |
| 20         | 3000       |
| 30         | 950        |
+------------+------------+

COUNT

Syntaxe

-- Count the number of records.
BIGINT COUNT([DISTINCT|ALL] <colname>)

-- Count the number of records in the window.
BIGINT COUNT(*) OVER ([partition_clause] [orderby_clause] [frame_clause])
BIGINT COUNT([DISTINCT] <expr>[,...]) OVER ([partition_clause] [orderby_clause] [frame_clause])

Paramètres

Paramètre

Obligatoire

Description

`DISTINCT

ALL`

Non

Contrôle la gestion des doublons. ALL (par défaut) compte toutes les lignes non NULL. DISTINCT ne compte que les valeurs non NULL uniques.

colname

Oui

La colonne à compter. Accepte tout type de données. Utilisez * pour compter toutes les lignes, y compris celles dont la valeur de colonne est NULL.

expr

Oui

Une expression de n'importe quel type de données. Les lignes NULL sont exclues. Avec DISTINCT, seules les valeurs non NULL uniques sont comptées. COUNT([DISTINCT] <expr>[,...]) OVER compte les lignes où toutes les expressions spécifiées sont non NULL.

partition_clause, orderby_clause, frame_clause

Non

Clauses de définition de fenêtre. Consultez Aperçu des fonctions de fenêtre.

Valeur renvoyée

Renvoie une valeur BIGINT. Les lignes NULL sont exclues, sauf si vous utilisez COUNT(*).

Exemples

Préparation des données de test

Si vous disposez déjà de données, vous pouvez ignorer cette étape.

Téléchargez les données de test test_data.txt.

Créez une table de test.

CREATE TABLE IF NOT EXISTS emp(
  empno BIGINT,
  ename STRING,
  job STRING,
  mgr BIGINT,
  hiredate DATETIME,
  sal BIGINT,
  comm BIGINT,
  deptno BIGINT
);

Chargez les données.

Remplacez FILE_PATH par le chemin et le nom réels de votre fichier de données.
```
TUNNEL UPLOAD {{FILEPATH}} emp;
```

Exemple 1 : Partitionner une fenêtre sans tri

Partitionnez la fenêtre par sal. Sans ORDER BY, chaque ligne renvoie le nombre total de lignes dans sa partition.

SELECT sal, COUNT(sal) OVER (PARTITION BY sal) AS count
FROM emp;

Résultat :

+------------+------------+
| sal        | count      |
+------------+------------+
| 800        | 1          |
| 950        | 1          |
| 1100       | 1          |
| 1250       | 2          |  -- Two rows share sal=1250; both return 2.
| 1250       | 2          |
| 1300       | 2          |
| 1300       | 2          |
| 1500       | 1          |
| 1600       | 1          |
| 2450       | 2          |
| 2450       | 2          |
| 2850       | 1          |
| 2975       | 1          |
| 3000       | 2          |
| 3000       | 2          |
| 5000       | 2          |
| 5000       | 2          |
+------------+------------+

Exemple 2 : Partitionner une fenêtre avec tri (mode non compatible Hive)

En mode non compatible Hive, l'ajout de ORDER BY génère un compte cumulatif. Chaque ligne renvoie le nombre cumulé depuis la première ligne jusqu'à la ligne actuelle dans sa partition.

-- Disable Hive compatible mode.
SET odps.sql.hive.compatible=false;

SELECT sal, COUNT(sal) OVER (PARTITION BY sal ORDER BY sal) AS count
FROM emp;

Résultat :

+------------+------------+
| sal        | count      |
+------------+------------+
| 800        | 1          |
| 950        | 1          |
| 1100       | 1          |
| 1250       | 1          |  -- Running count starts at 1 for the first row in the partition.
| 1250       | 2          |  -- Increments to 2 for the second row.
| 1300       | 1          |
| 1300       | 2          |
| 1500       | 1          |
| 1600       | 1          |
| 2450       | 1          |
| 2450       | 2          |
| 2850       | 1          |
| 2975       | 1          |
| 3000       | 1          |
| 3000       | 2          |
| 5000       | 1          |
| 5000       | 2          |
+------------+------------+

Exemple 3 : Partitionner une fenêtre avec tri (mode compatible Hive)

En mode compatible Hive, ORDER BY ne génère pas de compte cumulatif. Chaque ligne de la partition renvoie le nombre total de la partition, comme si ORDER BY était omis.

-- Enable Hive compatible mode.
SET odps.sql.hive.compatible=true;

SELECT sal, COUNT(sal) OVER (PARTITION BY sal ORDER BY sal) AS count
FROM emp;

Résultat :

+------------+------------+
| sal        | count      |
+------------+------------+
| 800        | 1          |
| 950        | 1          |
| 1100       | 1          |
| 1250       | 2          |  -- Both rows in the partition return the full partition count.
| 1250       | 2          |
| 1300       | 2          |
| 1300       | 2          |
| 1500       | 1          |
| 1600       | 1          |
| 2450       | 2          |
| 2450       | 2          |
| 2850       | 1          |
| 2975       | 1          |
| 3000       | 2          |
| 3000       | 2          |
| 5000       | 2          |
| 5000       | 2          |
+------------+------------+

Exemple 4 : Compter toutes les lignes d'une table

SELECT COUNT(*) FROM emp;

Résultat :

+------------+
| _c0        |
+------------+
| 17         |
+------------+

Exemple 5 : Compter les lignes par groupe

Utilisez COUNT avec GROUP BY pour obtenir le nombre d'employés par département.

SELECT deptno, COUNT(*) FROM emp GROUP BY deptno;

Résultat :

+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 20         | 5          |
| 30         | 6          |
| 10         | 6          |
+------------+------------+

Exemple 6 : Compter les valeurs uniques

Utilisez DISTINCT pour compter le nombre de départements distincts.

SELECT COUNT(DISTINCT deptno) FROM emp;

Résultat :

+------------+
| _c0        |
+------------+
| 3          |
+------------+

CUME_DIST

Syntaxe

double cume_dist() over([partition_clause] [orderby_clause])

Description

Calcule la distribution cumulative d'une valeur au sein d'un groupe de valeurs. Le résultat correspond au nombre de lignes dont les valeurs sont inférieures ou égales à la valeur de la ligne actuelle, divisé par le nombre total de lignes dans la partition. La comparaison est déterminée par la clause orderby_clause.
Paramètres

partition_clause et orderby_clause : Pour plus d'informations, consultez windowing_definition.
Valeur renvoyée

Renvoie une valeur DOUBLE. La valeur renvoyée spécifique est égale à row_number_of_last_peer / partition_row_count, où row_number_of_last_peer est la valeur renvoyée par la fonction de fenêtre ROW_NUMBER pour la dernière ligne du groupe de la ligne actuelle, et partition_row_count est le nombre de lignes dans la partition à laquelle appartient la ligne.

Exemple

Partitionnez par département (deptno) et calculez la distribution cumulative du salaire (sal) au sein de chaque département. La commande est la suivante :

select deptno, ename, sal, concat(round(cume_dist() over (partition by deptno order by sal desc)*100,2),'%') as cume_dist from emp;

Le résultat suivant est renvoyé :

+------------+------------+------------+------------+
| deptno     | ename      | sal        | cume_dist  |
+------------+------------+------------+------------+
| 10         | JACCKA     | 5000       | 33.33%     |
| 10         | KING       | 5000       | 33.33%     |
| 10         | CLARK      | 2450       | 66.67%     |
| 10         | WELAN      | 2450       | 66.67%     |
| 10         | TEBAGE     | 1300       | 100.0%     |
| 10         | MILLER     | 1300       | 100.0%     |
| 20         | SCOTT      | 3000       | 40.0%      |
| 20         | FORD       | 3000       | 40.0%      |
| 20         | JONES      | 2975       | 60.0%      |
| 20         | ADAMS      | 1100       | 80.0%      |
| 20         | SMITH      | 800        | 100.0%     |
| 30         | BLAKE      | 2850       | 16.67%     |
| 30         | ALLEN      | 1600       | 33.33%     |
| 30         | TURNER     | 1500       | 50.0%      |
| 30         | MARTIN     | 1250       | 83.33%     |
| 30         | WARD       | 1250       | 83.33%     |
| 30         | JAMES      | 950        | 100.0%     |
+------------+------------+------------+------------+

DENSE_RANK

Syntaxe

bigint dense_rank() over ([partition_clause] [orderby_clause])

Description

Calcule le rang de la ligne actuelle au sein de sa partition en fonction de l'ordre de tri spécifié dans la clause orderby_clause. Le classement commence à 1. Les lignes ayant les mêmes valeurs order by dans une partition reçoivent le même rang. Le rang s'incrémente de 1 chaque fois que la valeur order by change.
Paramètres

partition_clause et orderby_clause : Pour plus d'informations, consultez windowing_definition.
Valeur renvoyée

Renvoie une valeur BIGINT. Si aucune clause orderby_clause n'est spécifiée, toutes les lignes reçoivent un rang de 1.

Exemple

Partitionnez par département (deptno) et classez les employés au sein de chaque département en fonction de leur salaire (sal) par ordre décroissant. La commande est la suivante :

select deptno, ename, sal, dense_rank() over (partition by deptno order by sal desc) as nums from emp;

Le résultat suivant est renvoyé :

+------------+------------+------------+------------+
| deptno     | ename      | sal        | nums       |
+------------+------------+------------+------------+
| 10         | JACCKA     | 5000       | 1          |
| 10         | KING       | 5000       | 1          |
| 10         | CLARK      | 2450       | 2          |
| 10         | WELAN      | 2450       | 2          |
| 10         | TEBAGE     | 1300       | 3          |
| 10         | MILLER     | 1300       | 3          |
| 20         | SCOTT      | 3000       | 1          |
| 20         | FORD       | 3000       | 1          |
| 20         | JONES      | 2975       | 2          |
| 20         | ADAMS      | 1100       | 3          |
| 20         | SMITH      | 800        | 4          |
| 30         | BLAKE      | 2850       | 1          |
| 30         | ALLEN      | 1600       | 2          |
| 30         | TURNER     | 1500       | 3          |
| 30         | MARTIN     | 1250       | 4          |
| 30         | WARD       | 1250       | 4          |
| 30         | JAMES      | 950        | 5          |
+------------+------------+------------+------------+

FIRST_VALUE

Syntaxe

first_value(<expr>[, <ignore_nulls>]) over ([partition_clause] [orderby_clause] [frame_clause])

Description

Renvoie la valeur de l'expression expr de la première ligne du cadre de fenêtre.
Paramètres
- expr : Obligatoire. L'expression pour laquelle calculer le résultat.
- ignore_nulls : Facultatif. Une valeur BOOLEAN qui indique s'il faut ignorer les valeurs NULL. La valeur par défaut est False. Si ce paramètre est défini sur True, la fonction renvoie la première valeur non NULL de expr dans le cadre de fenêtre.
- partition_clause, orderby_clause et frame_clause : Pour plus d'informations, consultez windowing_definition.
Valeur renvoyée

La valeur renvoyée possède le même type de données que expr.

Exemple

La commande suivante regroupe tous les employés par département et renvoie la première ligne de données de chaque groupe :

Sans spécifier order by :

select deptno, ename, sal, first_value(sal) over (partition by deptno) as first_value from emp;

Le résultat suivant est renvoyé :

+------------+------------+------------+-------------+
| deptno     | ename      | sal        | first_value |
+------------+------------+------------+-------------+
| 10         | TEBAGE     | 1300       | 1300        |   -- First row of the current window.
| 10         | CLARK      | 2450       | 1300        |
| 10         | KING       | 5000       | 1300        |
| 10         | MILLER     | 1300       | 1300        |
| 10         | JACCKA     | 5000       | 1300        |
| 10         | WELAN      | 2450       | 1300        |
| 20         | FORD       | 3000       | 3000        |   -- First row of the current window.
| 20         | SCOTT      | 3000       | 3000        |
| 20         | SMITH      | 800        | 3000        |
| 20         | ADAMS      | 1100       | 3000        |
| 20         | JONES      | 2975       | 3000        |
| 30         | TURNER     | 1500       | 1500        |   -- First row of the current window.
| 30         | JAMES      | 950        | 1500        |
| 30         | ALLEN      | 1600       | 1500        |
| 30         | WARD       | 1250       | 1500        |
| 30         | MARTIN     | 1250       | 1500        |
| 30         | BLAKE      | 2850       | 1500        |
+------------+------------+------------+-------------+

Spécification de order by :

select deptno, ename, sal, first_value(sal) over (partition by deptno order by sal desc) as first_value from emp;

Le résultat suivant est renvoyé :

+------------+------------+------------+-------------+
| deptno     | ename      | sal        | first_value |
+------------+------------+------------+-------------+
| 10         | JACCKA     | 5000       | 5000        |   -- First row of the current window.
| 10         | KING       | 5000       | 5000        |
| 10         | CLARK      | 2450       | 5000        |
| 10         | WELAN      | 2450       | 5000        |
| 10         | TEBAGE     | 1300       | 5000        |
| 10         | MILLER     | 1300       | 5000        |
| 20         | SCOTT      | 3000       | 3000        |   -- First row of the current window.
| 20         | FORD       | 3000       | 3000        |
| 20         | JONES      | 2975       | 3000        |
| 20         | ADAMS      | 1100       | 3000        |
| 20         | SMITH      | 800        | 3000        |
| 30         | BLAKE      | 2850       | 2850        |   -- First row of the current window.
| 30         | ALLEN      | 1600       | 2850        |
| 30         | TURNER     | 1500       | 2850        |
| 30         | MARTIN     | 1250       | 2850        |
| 30         | WARD       | 1250       | 2850        |
| 30         | JAMES      | 950        | 2850        |
+------------+------------+------------+-------------+

LAG

Syntaxe

lag(<expr>[,bigint <offset>[, <default>]]) over([partition_clause] orderby_clause)

Description

Renvoie la valeur de l'expression expr de la ligne située offset lignes avant la ligne actuelle (vers le début de la partition). L'expression expr peut être une colonne, une opération sur une colonne ou une opération de fonction.
Paramètres
- expr : Obligatoire. L'expression à calculer.
- offset : Facultatif. Le décalage, qui est une constante BIGINT supérieure ou égale à 1. Une valeur de 1 indique la ligne précédente. La valeur par défaut est 1. Si la valeur d'entrée est de type STRING ou DOUBLE, elle est implicitement convertie en type BIGINT pour le calcul.
- default : Facultatif. Spécifie une valeur par défaut à renvoyer lorsque le offset est hors limites. Si ce paramètre n'est pas spécifié, la valeur par défaut est NULL. La valeur doit être une constante du même type de données que expr. Si expr n'est pas une constante, cette valeur est évaluée en fonction de la ligne actuelle.
- partition_clause et orderby_clause : Pour plus d'informations, consultez windowing_definition.
Valeur renvoyée

La valeur renvoyée possède le même type de données que expr.

Exemple

Partitionnez par département (deptno) et récupérez le salaire (sal) de la ligne précédente pour chaque employé. La commande est la suivante :

select deptno, ename, sal, lag(sal, 1) over (partition by deptno order by sal) as sal_new from emp;

Le résultat suivant est renvoyé :

+------------+------------+------------+------------+
| deptno     | ename      | sal        | sal_new    |
+------------+------------+------------+------------+
| 10         | TEBAGE     | 1300       | NULL       |
| 10         | MILLER     | 1300       | 1300       |
| 10         | CLARK      | 2450       | 1300       |
| 10         | WELAN      | 2450       | 2450       |
| 10         | KING       | 5000       | 2450       |
| 10         | JACCKA     | 5000       | 5000       |
| 20         | SMITH      | 800        | NULL       |
| 20         | ADAMS      | 1100       | 800        |
| 20         | JONES      | 2975       | 1100       |
| 20         | SCOTT      | 3000       | 2975       |
| 20         | FORD       | 3000       | 3000       |
| 30         | JAMES      | 950        | NULL       |
| 30         | MARTIN     | 1250       | 950        |
| 30         | WARD       | 1250       | 1250       |
| 30         | TURNER     | 1500       | 1250       |
| 30         | ALLEN      | 1600       | 1500       |
| 30         | BLAKE      | 2850       | 1600       |
+------------+------------+------------+------------+

LAST_VALUE

Syntaxe

last_value(<expr>[, <ignore_nulls>]) over([partition_clause] [orderby_clause] [frame_clause])

Description

Renvoie la valeur de l'expression expr issue de la dernière ligne du cadre de fenêtre.

Paramètres

expr : obligatoire. Expression à évaluer.
ignore_nulls : facultatif. Valeur BOOLEAN indiquant s'il faut ignorer les valeurs NULL. La valeur par défaut est False. Si ce paramètre est défini sur True, la fonction renvoie la dernière valeur non NULL de expr dans le cadre de fenêtre.
partition_clause, orderby_clause et frame_clause : pour plus d'informations, consultez la section windowing_definition.
Valeur de retour

La valeur de retour possède le même type de données que expr.

Exemple

La commande suivante regroupe tous les employés par département et renvoie la dernière ligne de données de chaque groupe :

Sans clause order by, le cadre de fenêtre inclut toutes les lignes de la partition. La fonction renvoie la valeur de la dernière ligne de la partition.

select deptno, ename, sal, last_value(sal) over (partition by deptno) as last_value from emp;

Le résultat suivant est renvoyé :

+------------+------------+------------+-------------+
| deptno     | ename      | sal        | last_value |
+------------+------------+------------+-------------+
| 10         | TEBAGE     | 1300       | 2450        |
| 10         | CLARK      | 2450       | 2450        |
| 10         | KING       | 5000       | 2450        |
| 10         | MILLER     | 1300       | 2450        |
| 10         | JACCKA     | 5000       | 2450        |   
| 10         | WELAN      | 2450       | 2450        |   -- Last row of the current window.
| 20         | FORD       | 3000       | 2975        |
| 20         | SCOTT      | 3000       | 2975        |
| 20         | SMITH      | 800        | 2975        |
| 20         | ADAMS      | 1100       | 2975        |
| 20         | JONES      | 2975       | 2975        |   -- Last row of the current window.
| 30         | TURNER     | 1500       | 2850        |
| 30         | JAMES      | 950        | 2850        |
| 30         | ALLEN      | 1600       | 2850        |
| 30         | WARD       | 1250       | 2850        |
| 30         | MARTIN     | 1250       | 2850        |
| 30         | BLAKE      | 2850       | 2850        |   -- Last row of the current window.
+------------+------------+------------+-------------+

Avec une clause order by, le cadre de fenêtre par défaut est RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. La fonction renvoie la valeur de la ligne actuelle.

select deptno, ename, sal, last_value(sal) over (partition by deptno order by sal desc) as last_value from emp;

Le résultat suivant est renvoyé :

+------------+------------+------------+-------------+
| deptno     | ename      | sal        | last_value |
+------------+------------+------------+-------------+
| 10         | JACCKA     | 5000       | 5000        |   -- Current row of the current window.
| 10         | KING       | 5000       | 5000        |   -- Current row of the current window.
| 10         | CLARK      | 2450       | 2450        |   -- Current row of the current window.
| 10         | WELAN      | 2450       | 2450        |   -- Current row of the current window.
| 10         | TEBAGE     | 1300       | 1300        |   -- Current row of the current window.
| 10         | MILLER     | 1300       | 1300        |   -- Current row of the current window.
| 20         | SCOTT      | 3000       | 3000        |   -- Current row of the current window.
| 20         | FORD       | 3000       | 3000        |   -- Current row of the current window.
| 20         | JONES      | 2975       | 2975        |   -- Current row of the current window.
| 20         | ADAMS      | 1100       | 1100        |   -- Current row of the current window.
| 20         | SMITH      | 800        | 800         |   -- Current row of the current window.
| 30         | BLAKE      | 2850       | 2850        |   -- Current row of the current window.
| 30         | ALLEN      | 1600       | 1600        |   -- Current row of the current window.
| 30         | TURNER     | 1500       | 1500        |   -- Current row of the current window.
| 30         | MARTIN     | 1250       | 1250        |   -- Current row of the current window.
| 30         | WARD       | 1250       | 1250        |   -- Current row of the current window.
| 30         | JAMES      | 950        | 950         |   -- Current row of the current window.
+------------+------------+------------+-------------+

LEAD

Syntaxe

lead(<expr>[, bigint <offset>[, <default>]]) over([partition_clause] orderby_clause)

Description

Renvoie la valeur de l'expression expr issue de la ligne située offset lignes après la ligne actuelle (vers la fin de la partition). L'expression expr peut être une colonne, une opération sur une colonne ou une opération de fonction.
Paramètres
- expr : obligatoire. Expression à évaluer.
- offset : facultatif. Décalage, représenté par une constante BIGINT supérieure ou égale à 0. Une valeur de 0 indique la ligne actuelle et une valeur de 1 indique la ligne suivante. La valeur par défaut est 1. Si la valeur d'entrée est de type STRING ou DOUBLE, elle est implicitement convertie en type BIGINT pour le calcul.
- default : facultatif. Valeur à renvoyer si le offset est hors limites. Cette valeur doit être une constante du même type de données que expr. La valeur par défaut est NULL. Si expr n'est pas une constante, la valeur est évaluée en fonction de la ligne actuelle.
- partition_clause et orderby_clause : pour plus d'informations, consultez la section windowing_definition.
Valeur de retour

La valeur de retour possède le même type de données que expr.

Exemple

Partitionnez par département (deptno) et récupérez le salaire (sal) de la ligne suivante pour chaque employé. La commande est la suivante :

select deptno, ename, sal, lead(sal, 1) over (partition by deptno order by sal) as sal_new from emp;

Le résultat suivant est renvoyé :

+------------+------------+------------+------------+
| deptno     | ename      | sal        | sal_new    |
+------------+------------+------------+------------+
| 10         | TEBAGE     | 1300       | 1300       |
| 10         | MILLER     | 1300       | 2450       |
| 10         | CLARK      | 2450       | 2450       |
| 10         | WELAN      | 2450       | 5000       |
| 10         | KING       | 5000       | 5000       |
| 10         | JACCKA     | 5000       | NULL       |
| 20         | SMITH      | 800        | 1100       |
| 20         | ADAMS      | 1100       | 2975       |
| 20         | JONES      | 2975       | 3000       |
| 20         | SCOTT      | 3000       | 3000       |
| 20         | FORD       | 3000       | NULL       |
| 30         | JAMES      | 950        | 1250       |
| 30         | MARTIN     | 1250       | 1250       |
| 30         | WARD       | 1250       | 1500       |
| 30         | TURNER     | 1500       | 1600       |
| 30         | ALLEN      | 1600       | 2850       |
| 30         | BLAKE      | 2850       | NULL       |
+------------+------------+------------+------------+

MAX

Syntaxe

max(<expr>) over([partition_clause] [orderby_clause] [frame_clause])

Description

Renvoie la valeur maximale de expr dans une fenêtre.
Paramètres
- expr : obligatoire. Expression utilisée pour calculer la valeur maximale. Elle peut être de n'importe quel type de données, à l'exception de BOOLEAN. Si la valeur est NULL, la ligne n'est pas incluse dans le calcul.
- partition_clause, orderby_clause et frame_clause : pour plus d'informations, consultez la section windowing_definition.
Valeur de retour

La valeur de retour possède le même type de données que expr.

Exemples

Exemple 1 : Partitionnez par département (deptno), calculez le salaire maximal (sal) sans tri. La fonction renvoie la valeur maximale de la partition actuelle (lignes ayant le même deptno). La commande est la suivante :

select deptno, sal, max(sal) over (partition by deptno) from emp;

Le résultat suivant est renvoyé :

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 5000       |   -- First row of the window. The value is the maximum from the first to the sixth row.
| 10         | 2450       | 5000       |   -- The value is the maximum from the first to the sixth row.
| 10         | 5000       | 5000       |   -- The value is the maximum from the first to the sixth row.
| 10         | 1300       | 5000       |
| 10         | 5000       | 5000       |
| 10         | 2450       | 5000       |
| 20         | 3000       | 3000       |
| 20         | 3000       | 3000       |
| 20         | 800        | 3000       |
| 20         | 1100       | 3000       |
| 20         | 2975       | 3000       |
| 30         | 1500       | 2850       |
| 30         | 950        | 2850       |
| 30         | 1600       | 2850       |
| 30         | 1250       | 2850       |
| 30         | 1250       | 2850       |
| 30         | 2850       | 2850       |
+------------+------------+------------+

Exemple 2 : Partitionnez par département (deptno), calculez le salaire maximal (sal) et triez les résultats. La fonction renvoie la valeur maximale de la première ligne à la ligne actuelle de la partition actuelle (lignes ayant le même deptno). La commande est la suivante :

select deptno, sal, max(sal) over (partition by deptno order by sal) from emp;

Le résultat suivant est renvoyé :

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 1300       |   -- First row of the window.
| 10         | 1300       | 1300       |   -- Maximum value from the first to the second row.
| 10         | 2450       | 2450       |   -- Maximum value from the first to the third row.
| 10         | 2450       | 2450       |   -- Maximum value from the first to the fourth row.
| 10         | 5000       | 5000       |
| 10         | 5000       | 5000       |
| 20         | 800        | 800        |
| 20         | 1100       | 1100       |
| 20         | 2975       | 2975       |
| 20         | 3000       | 3000       |
| 20         | 3000       | 3000       |
| 30         | 950        | 950        |
| 30         | 1250       | 1250       |
| 30         | 1250       | 1250       |
| 30         | 1500       | 1500       |
| 30         | 1600       | 1600       |
| 30         | 2850       | 2850       |
+------------+------------+------------+

MEDIAN

Syntaxe

median(<expr>) over ([partition_clause] [orderby_clause] [frame_clause])

Description

Calcule la médiane de expr dans une fenêtre.
Paramètres
- expr : obligatoire. Expression pour laquelle calculer la médiane. Elle doit être de type DOUBLE ou DECIMAL.
  - Si la valeur d'entrée est de type STRING ou BIGINT, elle est implicitement convertie en type DOUBLE pour le calcul. Une erreur est renvoyée pour les autres types de données.
  - Si l'entrée est NULL, la valeur de retour est NULL.
- partition_clause, orderby_clause et frame_clause : pour plus d'informations, consultez la section windowing_definition.
Valeur de retour

Renvoie une valeur DOUBLE ou DECIMAL. Si toutes les valeurs de expr sont NULL, NULL est renvoyé.

Exemple

Partitionnez par département (deptno) et calculez le salaire médian (sal). La fonction renvoie la médiane pour l'ensemble de la partition (toutes les lignes ayant le même deptno). La commande est la suivante :

select deptno, sal, median(sal) over (partition by deptno) from emp;

Le résultat suivant est renvoyé :

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 2450.0     |   -- First row of the window. The value is the median from the first to the sixth row.
| 10         | 2450       | 2450.0     |
| 10         | 5000       | 2450.0     |
| 10         | 1300       | 2450.0     |
| 10         | 5000       | 2450.0     |
| 10         | 2450       | 2450.0     |
| 20         | 3000       | 2975.0     |
| 20         | 3000       | 2975.0     |
| 20         | 800        | 2975.0     |
| 20         | 1100       | 2975.0     |
| 20         | 2975       | 2975.0     |
| 30         | 1500       | 1375.0     |
| 30         | 950        | 1375.0     |
| 30         | 1600       | 1375.0     |
| 30         | 1250       | 1375.0     |
| 30         | 1250       | 1375.0     |
| 30         | 2850       | 1375.0     |
+------------+------------+------------+

MIN

Syntaxe

min(<expr>) over([partition_clause] [orderby_clause] [frame_clause])

Description

Renvoie la valeur minimale de expr dans une fenêtre.
Paramètres
- expr : obligatoire. Expression utilisée pour calculer la valeur minimale. Elle peut être de n'importe quel type de données, à l'exception de BOOLEAN. Si la valeur est NULL, la ligne n'est pas incluse dans le calcul.
- partition_clause, orderby_clause et frame_clause : pour plus d'informations, consultez la section windowing_definition.
Valeur de retour

La valeur de retour possède le même type de données que expr.

Exemples

Exemple 1 : Partitionnez par département (deptno), calculez le salaire minimal (sal) sans tri. La fonction renvoie la valeur minimale de la partition actuelle (lignes ayant le même deptno). La commande est la suivante :

select deptno, sal, min(sal) over (partition by deptno) from emp;

Le résultat suivant est renvoyé :

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 1300       |   -- First row of the window. The value is the minimum from the first to the sixth row.
| 10         | 2450       | 1300       |   -- The value is the minimum from the first to the sixth row.
| 10         | 5000       | 1300       |   -- The value is the minimum from the first to the sixth row.
| 10         | 1300       | 1300       |
| 10         | 5000       | 1300       |
| 10         | 2450       | 1300       |
| 20         | 3000       | 800        |
| 20         | 3000       | 800        |
| 20         | 800        | 800        |
| 20         | 1100       | 800        |
| 20         | 2975       | 800        |
| 30         | 1500       | 950        |
| 30         | 950        | 950        |
| 30         | 1600       | 950        |
| 30         | 1250       | 950        |
| 30         | 1250       | 950        |
| 30         | 2850       | 950        |
+------------+------------+------------+

Exemple 2 : Partitionnez par département (deptno), calculez le salaire minimal (sal) et triez les résultats. La fonction renvoie la valeur minimale de la première ligne à la ligne actuelle de la partition actuelle (lignes ayant le même deptno). La commande est la suivante :

select deptno, sal, min(sal) over (partition by deptno order by sal) from emp;

Le résultat suivant est renvoyé :

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 1300       |   -- First row of the window.
| 10         | 1300       | 1300       |   -- Minimum value from the first to the second row.
| 10         | 2450       | 1300       |   -- Minimum value from the first to the third row.
| 10         | 2450       | 1300       |
| 10         | 5000       | 1300       |
| 10         | 5000       | 1300       |
| 20         | 800        | 800        |
| 20         | 1100       | 800        |
| 20         | 2975       | 800        |
| 20         | 3000       | 800        |
| 20         | 3000       | 800        |
| 30         | 950        | 950        |
| 30         | 1250       | 950        |
| 30         | 1250       | 950        |
| 30         | 1500       | 950        |
| 30         | 1600       | 950        |
| 30         | 2850       | 950        |
+------------+------------+------------+

NTILE

Syntaxe

bigint ntile(bigint <N>) over ([partition_clause] [orderby_clause])

Description

Divise les lignes ordonnées d'une partition en N groupes de taille aussi égale que possible et renvoie le numéro de groupe pour chaque ligne. Si le nombre de lignes n'est pas divisible exactement par N, les premiers groupes (ceux avec des numéros de groupe plus petits) contiendront une ligne supplémentaire.
Paramètres
- N : obligatoire. Nombre de groupes. Valeur BIGINT.
- partition_clause et orderby_clause : pour plus d'informations, consultez la section windowing_definition.
Valeur de retour

Renvoie une valeur BIGINT.

Exemple

Divisez tous les employés en 3 groupes au sein de chaque département en fonction du salaire (sal) par ordre décroissant et renvoyez le numéro de groupe pour chaque employé. La commande est la suivante :

select deptno, ename, sal, ntile(3) over (partition by deptno order by sal desc) as nt3 from emp;

Le résultat suivant est renvoyé :

+------------+------------+------------+------------+
| deptno     | ename      | sal        | nt3        |
+------------+------------+------------+------------+
| 10         | JACCKA     | 5000       | 1          |
| 10         | KING       | 5000       | 1          |
| 10         | CLARK      | 2450       | 2          |
| 10         | WELAN      | 2450       | 2          |
| 10         | TEBAGE     | 1300       | 3          |
| 10         | MILLER     | 1300       | 3          |
| 20         | SCOTT      | 3000       | 1          |
| 20         | FORD       | 3000       | 1          |
| 20         | JONES      | 2975       | 2          |
| 20         | ADAMS      | 1100       | 2          |
| 20         | SMITH      | 800        | 3          |
| 30         | BLAKE      | 2850       | 1          |
| 30         | ALLEN      | 1600       | 1          |
| 30         | TURNER     | 1500       | 2          |
| 30         | MARTIN     | 1250       | 2          |
| 30         | WARD       | 1250       | 3          |
| 30         | JAMES      | 950        | 3          |
+------------+------------+------------+------------+

NTH_VALUE

Syntaxe

nth_value(<expr>, <number> [, <ignore_nulls>]) over ([partition_clause] [orderby_clause] [frame_clause])

Description

Renvoie la valeur de l'expression expr issue de la N-ième ligne du cadre de fenêtre.
Paramètres
- expr : obligatoire. Expression à calculer.
- number : obligatoire. Valeur BIGINT. Entier supérieur ou égal à 1. Si la valeur est 1, cette fonction équivaut à FIRST_VALUE.
- ignore_nulls : facultatif. Valeur BOOLEAN indiquant s'il faut ignorer les valeurs NULL. La valeur par défaut est False. Si ce paramètre est défini sur True, la fonction renvoie la N-ième valeur non NULL de expr dans le cadre de fenêtre.
- partition_clause, orderby_clause et frame_clause : pour plus d'informations, consultez windowing_definition.
Valeur de retour

La valeur de retour possède le même type de données que expr.

Exemple

La commande suivante regroupe tous les employés par département et renvoie la sixième ligne de données de chaque groupe :

Sans clause order by, le cadre de fenêtre inclut toutes les lignes de la partition. La fonction renvoie la valeur de la sixième ligne de la partition.

select deptno, ename, sal, nth_value(sal,6) over (partition by deptno) as nth_value from emp;

Le résultat suivant est renvoyé :

+------------+------------+------------+------------+
| deptno     | ename      | sal        | nth_value  |
+------------+------------+------------+------------+
| 10         | TEBAGE     | 1300       | 2450       |
| 10         | CLARK      | 2450       | 2450       |
| 10         | KING       | 5000       | 2450       |
| 10         | MILLER     | 1300       | 2450       |
| 10         | JACCKA     | 5000       | 2450       |
| 10         | WELAN      | 2450       | 2450       |   -- 6th row of the current window.
| 20         | FORD       | 3000       | NULL       |
| 20         | SCOTT      | 3000       | NULL       |
| 20         | SMITH      | 800        | NULL       |
| 20         | ADAMS      | 1100       | NULL       |
| 20         | JONES      | 2975       | NULL       |   -- The current window does not have a 6th row, so NULL is returned.
| 30         | TURNER     | 1500       | 2850       |
| 30         | JAMES      | 950        | 2850       |
| 30         | ALLEN      | 1600       | 2850       |
| 30         | WARD       | 1250       | 2850       |
| 30         | MARTIN     | 1250       | 2850       |
| 30         | BLAKE      | 2850       | 2850       |   -- 6th row of the current window.
+------------+------------+------------+------------+

Avec une clause order by, le cadre de fenêtre par défaut est RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. La fonction renvoie la valeur de la sixième ligne du cadre de fenêtre.

select deptno, ename, sal, nth_value(sal,6) over (partition by deptno order by sal) as nth_value from emp;

Le résultat suivant est renvoyé :

+------------+------------+------------+------------+
| deptno     | ename      | sal        | nth_value  |
+------------+------------+------------+------------+
| 10         | TEBAGE     | 1300       | NULL       |   
| 10         | MILLER     | 1300       | NULL       |   -- The current window has only 2 rows, so the 6th row exceeds the window length.
| 10         | CLARK      | 2450       | NULL       |
| 10         | WELAN      | 2450       | NULL       |
| 10         | KING       | 5000       | 5000       |  
| 10         | JACCKA     | 5000       | 5000       |
| 20         | SMITH      | 800        | NULL       |
| 20         | ADAMS      | 1100       | NULL       |
| 20         | JONES      | 2975       | NULL       |
| 20         | SCOTT      | 3000       | NULL       |
| 20         | FORD       | 3000       | NULL       |
| 30         | JAMES      | 950        | NULL       |
| 30         | MARTIN     | 1250       | NULL       |
| 30         | WARD       | 1250       | NULL       |
| 30         | TURNER     | 1500       | NULL       |
| 30         | ALLEN      | 1600       | NULL       |
| 30         | BLAKE      | 2850       | 2850       |
+------------+------------+------------+------------+

PERCENT_RANK

Syntaxe

double percent_rank() over([partition_clause] [orderby_clause])

Description

Calcule le rang percentile de la ligne actuelle au sein de sa partition, selon l'ordre de tri spécifié par orderby_clause.
Paramètres

partition_clause et orderby_clause : pour plus d'informations, consultez windowing_definition.
Valeur de retour

Renvoie une valeur DOUBLE comprise dans l'intervalle [0,0 ; 1,0]. La valeur de retour spécifique est égale à “(rank - 1) / (partition_row_count - 1)”, où rank correspond au résultat de la fonction de fenêtre RANK pour cette ligne, et partition_row_count représente le nombre de lignes dans la partition à laquelle appartient la ligne. Si la partition ne contient qu'une seule ligne, la sortie est 0,0.

Exemple

Calculez le rang percentile du salaire de chaque employé au sein de son département. La commande est la suivante :

select deptno, ename, sal, percent_rank() over (partition by deptno order by sal desc) as sal_new from emp;

Le résultat suivant est renvoyé :

+------------+------------+------------+------------+
| deptno     | ename      | sal        | sal_new    |
+------------+------------+------------+------------+
| 10         | JACCKA     | 5000       | 0.0        |
| 10         | KING       | 5000       | 0.0        |
| 10         | CLARK      | 2450       | 0.4        |
| 10         | WELAN      | 2450       | 0.4        |
| 10         | TEBAGE     | 1300       | 0.8        |
| 10         | MILLER     | 1300       | 0.8        |
| 20         | SCOTT      | 3000       | 0.0        |
| 20         | FORD       | 3000       | 0.0        |
| 20         | JONES      | 2975       | 0.5        |
| 20         | ADAMS      | 1100       | 0.75       |
| 20         | SMITH      | 800        | 1.0        |
| 30         | BLAKE      | 2850       | 0.0        |
| 30         | ALLEN      | 1600       | 0.2        |
| 30         | TURNER     | 1500       | 0.4        |
| 30         | MARTIN     | 1250       | 0.6        |
| 30         | WARD       | 1250       | 0.6        |
| 30         | JAMES      | 950        | 1.0        |
+------------+------------+------------+------------+

PERCENTILE_CONT

Syntaxe

-- Calculate the exact percentile
PERCENTILE_CONT(<col_name>, DOUBLE <percentile>[, BOOLEAN <isIgnoreNull>])

-- Calculate the exact percentile in a window
PERCENTILE_CONT(<col_name>, DOUBLE <percentile>[, BOOLEAN <isIgnoreNull>]) OVER ([partition_clause] [orderby_clause])

Description

Calcule le centile exact. Cette fonction utilise un algorithme d'interpolation linéaire, trie la colonne spécifiée par ordre croissant et renvoie la valeur exacte au percentile indiqué.
Paramètres
- col_name : obligatoire. Colonne de type DOUBLE ou DECIMAL.
- percentile : obligatoire. Centile à calculer. Constante DOUBLE comprise dans l'intervalle [0, 1].
- isIgnoreNull : facultatif. Indique s'il faut ignorer les valeurs NULL. Constante BOOLEAN. La valeur par défaut est TRUE. Si elle est définie sur FALSE, les valeurs NULL sont traitées comme la valeur minimale lors du tri.
- partition_clause et orderby_clause : pour plus d'informations, consultez windowing_definition..
Valeur de retour

Renvoie la valeur du centile calculée sous forme de DOUBLE.

Exemples

Exemple 1 : ignorez les valeurs NULL et calculez le centile exact dans une fenêtre.

SELECT
  PERCENTILE_CONT(x, 0) OVER() AS min,
  PERCENTILE_CONT(x, 0.01) OVER() AS percentile1,
  PERCENTILE_CONT(x, 0.5) OVER() AS median,
  PERCENTILE_CONT(x, 0.9) OVER() AS percentile90,
  PERCENTILE_CONT(x, 1) OVER() AS max
FROM VALUES(0D),(3D),(NULL),(1D),(2D) AS tbl(x) LIMIT 1;

-- Return result
+------------+-------------+------------+--------------+------------+
| min        | percentile1 | median     | percentile90 | max        | 
+------------+-------------+------------+--------------+------------+
| 0.0        | 0.03        | 1.5        | 2.7          | 3.0        | 
+------------+-------------+------------+--------------+------------+

Exemple 2 : n'ignorez pas les valeurs NULL. Les valeurs NULL sont traitées comme la valeur minimale lors du tri. Calculez le centile exact dans une fenêtre.

SELECT
  PERCENTILE_CONT(x, 0, false) OVER() AS min,
  PERCENTILE_CONT(x, 0.01, false) OVER() AS percentile1,
  PERCENTILE_CONT(x, 0.5, false) OVER() AS median,
  PERCENTILE_CONT(x, 0.9, false) OVER() AS percentile90,
  PERCENTILE_CONT(x, 1, false) OVER() AS max
FROM VALUES(0D),(3D),(NULL),(1D),(2D) AS tbl(x) LIMIT 1;

-- Return result
+------------+-------------+------------+--------------+------------+
| min        | percentile1 | median     | percentile90 | max        | 
+------------+-------------+------------+--------------+------------+
| NULL       | 0.0         | 1.0        | 2.6          | 3.0        | 
+------------+-------------+------------+--------------+------------+

PERCENTILE_DISC

Syntaxe

-- Calculate a given percentile value
PERCENTILE_DISC(<col_name>, DOUBLE <percentile>[, BOOLEAN <isIgnoreNull>])

-- Calculate the percentile value in a window
PERCENTILE_DISC(<col_name>, DOUBLE <percentile>[, BOOLEAN <isIgnoreNull>]) OVER ([partition_clause] [orderby_clause])

Description

Calcule une valeur de centile donnée. Cette fonction trie d'abord la colonne spécifiée par ordre croissant, puis renvoie la première valeur dont la distribution cumulative est supérieure ou égale au centile spécifié.
Paramètres
- col_name : obligatoire. Colonne dotée de tout type de données triable.
- percentile : obligatoire. Centile à calculer. Constante DOUBLE comprise dans l'intervalle [0, 1].
- isIgnoreNull : facultatif. Indique s'il faut ignorer les valeurs NULL. Constante BOOLEAN. La valeur par défaut est TRUE. Si elle est définie sur FALSE, les valeurs NULL sont traitées comme la valeur minimale lors du tri.
- partition_clause et orderby_clause : pour plus d'informations, consultez windowing_definition.
Valeur de retour

Renvoie la valeur du centile calculée. Le type de données est identique à celui de la colonne d'entrée col_name.

Exemples

Exemple 1 : ignorez les valeurs NULL et calculez la valeur du centile dans une fenêtre.

SELECT
  x,
  PERCENTILE_DISC(x, 0) OVER() AS min,
  PERCENTILE_DISC(x, 0.5) OVER() AS median,
  PERCENTILE_DISC(x, 1) OVER() AS max
FROM VALUES('c'),(NULL),('b'),('a') AS tbl(x);

-- Return result
+------------+------------+------------+------------+
| x          | min        | median     | max        | 
+------------+------------+------------+------------+
| c          | a          | b          | c          | 
| NULL       | a          | b          | c          | 
| b          | a          | b          | c          | 
| a          | a          | b          | c          | 
+------------+------------+------------+------------+

Exemple 2 : n'ignorez pas les valeurs NULL. Les valeurs NULL sont traitées comme la valeur minimale lors du tri. Calculez la valeur du centile dans une fenêtre.

SELECT
  x,
  PERCENTILE_DISC(x, 0, false) OVER() AS min,
  PERCENTILE_DISC(x, 0.5, false) OVER() AS median,
  PERCENTILE_DISC(x, 1, false) OVER() AS max
FROM VALUES('c'),(NULL),('b'),('a') AS tbl(x);

-- Return result
+------------+------------+------------+------------+
| x          | min        | median     | max        | 
+------------+------------+------------+------------+
| c          | NULL       | a          | c          | 
| NULL       | NULL       | a          | c          | 
| b          | NULL       | a          | c          | 
| a          | NULL       | a          | c          | 
+------------+------------+------------+------------+

RANK

Syntaxe

bigint rank() over ([partition_clause] [orderby_clause])

Description

Calcule le rang de la ligne actuelle au sein de sa partition, selon l'ordre de tri spécifié par orderby_clause. Le comptage commence à 1.
Paramètres

partition_clause et orderby_clause : pour plus d'informations, consultez windowing_definition.
Valeur de retour

Renvoie une valeur BIGINT. Les valeurs de retour peuvent être dupliquées et non consécutives. La valeur de retour spécifique correspond à la valeur ROW_NUMBER() de la première ligne du groupe auquel appartient la ligne de données. Si aucune clause orderby_clause n'est spécifiée, toutes les lignes reçoivent un rang de 1.

Exemple

Partitionnez par département (deptno) et classez les employés de chaque département selon leur salaire (sal) par ordre décroissant. La commande est la suivante :

select deptno, ename, sal, rank() over (partition by deptno order by sal desc) as nums from emp;

Le résultat suivant est renvoyé :

+------------+------------+------------+------------+
| deptno     | ename      | sal        | nums       |
+------------+------------+------------+------------+
| 10         | JACCKA     | 5000       | 1          |
| 10         | KING       | 5000       | 1          |
| 10         | CLARK      | 2450       | 3          |
| 10         | WELAN      | 2450       | 3          |
| 10         | TEBAGE     | 1300       | 5          |
| 10         | MILLER     | 1300       | 5          |
| 20         | SCOTT      | 3000       | 1          |
| 20         | FORD       | 3000       | 1          |
| 20         | JONES      | 2975       | 3          |
| 20         | ADAMS      | 1100       | 4          |
| 20         | SMITH      | 800        | 5          |
| 30         | BLAKE      | 2850       | 1          |
| 30         | ALLEN      | 1600       | 2          |
| 30         | TURNER     | 1500       | 3          |
| 30         | MARTIN     | 1250       | 4          |
| 30         | WARD       | 1250       | 4          |
| 30         | JAMES      | 950        | 6          |
+------------+------------+------------+------------+

ROW_NUMBER

Syntaxe

row_number() over([partition_clause] [orderby_clause])

Description

Calcule le numéro de ligne de la ligne actuelle au sein de sa partition, en commençant par 1.
Paramètres

Pour plus d'informations, consultez windowing_definition. La clause frame_clause n'est pas autorisée.
Valeur de retour

Renvoie une valeur BIGINT.

Exemple

Partitionnez par département (deptno) et attribuez un numéro séquentiel unique à chaque employé au sein de son département, selon le salaire (sal) par ordre décroissant. La commande est la suivante :

select deptno, ename, sal, row_number() over (partition by deptno order by sal desc) as nums from emp;

Le résultat suivant est renvoyé :

+------------+------------+------------+------------+
| deptno     | ename      | sal        | nums       |
+------------+------------+------------+------------+
| 10         | JACCKA     | 5000       | 1          |
| 10         | KING       | 5000       | 2          |
| 10         | CLARK      | 2450       | 3          |
| 10         | WELAN      | 2450       | 4          |
| 10         | TEBAGE     | 1300       | 5          |
| 10         | MILLER     | 1300       | 6          |
| 20         | SCOTT      | 3000       | 1          |
| 20         | FORD       | 3000       | 2          |
| 20         | JONES      | 2975       | 3          |
| 20         | ADAMS      | 1100       | 4          |
| 20         | SMITH      | 800        | 5          |
| 30         | BLAKE      | 2850       | 1          |
| 30         | ALLEN      | 1600       | 2          |
| 30         | TURNER     | 1500       | 3          |
| 30         | MARTIN     | 1250       | 4          |
| 30         | WARD       | 1250       | 5          |
| 30         | JAMES      | 950        | 6          |
+------------+------------+------------+------------+

STDDEV

Syntaxe

double stddev|stddev_pop([distinct] <expr>) over ([partition_clause] [orderby_clause] [frame_clause])
decimal stddev|stddev_pop([distinct] <expr>) over ([partition_clause] [orderby_clause] [frame_clause])

Description

Calcule l'écart type de la population. Il s'agit d'un alias de la fonction STDDEV_POP.
Paramètres
- expr : obligatoire. Expression pour laquelle calculer l'écart type de la population. Elle doit être de type DOUBLE ou DECIMAL.
  - Si la valeur d'entrée est de type STRING ou BIGINT, elle est implicitement convertie au type DOUBLE pour le calcul. Une erreur est renvoyée pour les autres types de données.
  - Si la valeur d'entrée est NULL, la ligne n'est pas incluse dans le calcul.
  - Si vous spécifiez le mot-clé distinct, la fonction calcule l'écart type de la population des valeurs uniques.
- partition_clause, orderby_clause et frame_clause : pour plus d'informations, consultez windowing_definition.
Valeur de retour

La valeur de retour possède le même type de données que expr. Si toutes les valeurs de expr sont NULL, NULL est renvoyé.

Exemples

Exemple 1 : partitionnez par département (deptno), calculez l'écart type de la population du salaire (sal) et n'effectuez aucun tri. La fonction renvoie l'écart type cumulé de la population de la partition actuelle (lignes ayant le même deptno). La commande est la suivante :

select deptno, sal, stddev(sal) over (partition by deptno) from emp;

Le résultat suivant est renvoyé :

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 1546.1421524412158 |   -- First row of the window. The value is the cumulative population standard deviation from the first to the sixth row.
| 10         | 2450       | 1546.1421524412158 |   -- The value is the cumulative population standard deviation from the first to the sixth row.
| 10         | 5000       | 1546.1421524412158 |
| 10         | 1300       | 1546.1421524412158 |
| 10         | 5000       | 1546.1421524412158 |
| 10         | 2450       | 1546.1421524412158 |
| 20         | 3000       | 1004.7387720198718 |
| 20         | 3000       | 1004.7387720198718 |
| 20         | 800        | 1004.7387720198718 |
| 20         | 1100       | 1004.7387720198718 |
| 20         | 2975       | 1004.7387720198718 |
| 30         | 1500       | 610.1001739241042 |
| 30         | 950        | 610.1001739241042 |
| 30         | 1600       | 610.1001739241042 |
| 30         | 1250       | 610.1001739241042 |
| 30         | 1250       | 610.1001739241042 |
| 30         | 2850       | 610.1001739241042 |
+------------+------------+------------+

Exemple 2 : en mode non compatible avec Hive, partitionnez par département (deptno), calculez l'écart type de la population du salaire (sal) et triez les résultats. La fonction renvoie l'écart type cumulé de la population de la première ligne à la ligne actuelle de la partition actuelle (lignes ayant le même deptno). Les commandes sont les suivantes :

-- Disable Hive compatible mode.
set odps.sql.hive.compatible=false;
-- Execute the following SQL command.
select deptno, sal, stddev(sal) over (partition by deptno order by sal) from emp;

Le résultat suivant est renvoyé :

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 0.0        |           -- First row of the window.
| 10         | 1300       | 0.0        |           -- Cumulative population standard deviation from the first to the second row.
| 10         | 2450       | 542.1151989096865 |    -- Cumulative population standard deviation from the first to the third row.
| 10         | 2450       | 575.0      |           -- Cumulative population standard deviation from the first to the fourth row.
| 10         | 5000       | 1351.6656391282572 |
| 10         | 5000       | 1546.1421524412158 |
| 20         | 800        | 0.0        |
| 20         | 1100       | 150.0      |
| 20         | 2975       | 962.4188277460079 |
| 20         | 3000       | 1024.2947268730811 |
| 20         | 3000       | 1004.7387720198718 |
| 30         | 950        | 0.0        |
| 30         | 1250       | 150.0      |
| 30         | 1250       | 141.4213562373095 |
| 30         | 1500       | 194.8557158514987 |
| 30         | 1600       | 226.71568097509268 |
| 30         | 2850       | 610.1001739241042 |
+------------+------------+------------+

Exemple 3 : en mode compatible avec Hive, partitionnez par département (deptno), calculez l'écart type de la population du salaire (sal) et triez les résultats. La fonction renvoie l'écart type cumulé de la population de la première ligne à la ligne ayant la même valeur que la ligne actuelle (les lignes ayant le même sal possèdent le même écart type de population). Les commandes sont les suivantes :

-- Enable Hive compatible mode.
set odps.sql.hive.compatible=true;
-- Execute the following SQL command.
select deptno, sal, stddev(sal) over (partition by deptno order by sal) from emp;

Le résultat suivant est renvoyé :

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 0.0        |           -- First row of the window. Since the sal of the first and second rows are the same, the population standard deviation for the first row is the cumulative population standard deviation of the first two rows.
| 10         | 1300       | 0.0        |           -- Cumulative population standard deviation from the first to the second row.
| 10         | 2450       | 575.0      |           -- Since the sal of the third and fourth rows are the same, the population standard deviation for the third row is the cumulative population standard deviation of the first four rows.
| 10         | 2450       | 575.0      |           -- Cumulative population standard deviation from the first to the fourth row.
| 10         | 5000       | 1546.1421524412158 |
| 10         | 5000       | 1546.1421524412158 |
| 20         | 800        | 0.0        |
| 20         | 1100       | 150.0      |
| 20         | 2975       | 962.4188277460079 |
| 20         | 3000       | 1004.7387720198718 |
| 20         | 3000       | 1004.7387720198718 |
| 30         | 950        | 0.0        |
| 30         | 1250       | 141.4213562373095 |
| 30         | 1250       | 141.4213562373095 |
| 30         | 1500       | 194.8557158514987 |
| 30         | 1600       | 226.71568097509268 |
| 30         | 2850       | 610.1001739241042 |
+------------+------------+------------+

STDDEV_SAMP

Syntaxe

double stddev_samp([distinct] <expr>) over([partition_clause] [orderby_clause] [frame_clause])
decimal stddev_samp([distinct] <expr>) over([partition_clause] [orderby_clause] [frame_clause])

Description

Calcule l'écart type d'un échantillon.
Paramètres
- expr : obligatoire. Expression pour laquelle calculer l'écart type de l'échantillon. Elle doit être de type DOUBLE ou DECIMAL.
  - Si la valeur d'entrée est de type STRING ou BIGINT, elle est implicitement convertie en type DOUBLE pour le calcul. Une erreur est renvoyée pour les autres types de données.
  - Si la valeur d'entrée est NULL, la ligne n'est pas incluse dans le calcul.
  - Si vous spécifiez le mot-clé distinct, la fonction calcule l'écart type de l'échantillon des valeurs uniques.
- partition_clause, orderby_clause et frame_clause : pour plus d'informations, consultez windowing_definition.
Valeur de retour

La valeur de retour a le même type de données que expr. Si toutes les valeurs de expr sont NULL, la fonction renvoie NULL. Si la fenêtre ne contient qu'une seule valeur non NULL pour expr, le résultat est 0.

Exemples

Exemple 1 : partitionnez par département (deptno), calculez l'écart type de l'échantillon du salaire (sal) sans tri. La fonction renvoie l'écart type cumulé de l'échantillon de la partition actuelle (lignes ayant le même deptno). La commande est la suivante :

select deptno, sal, stddev_samp(sal) over (partition by deptno) from emp;

Le résultat suivant est renvoyé :

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 1693.7138680032904 |   -- First row of the window. The value is the cumulative sample standard deviation from the first to the sixth row.
| 10         | 2450       | 1693.7138680032904 |   -- The value is the cumulative sample standard deviation from the first to the sixth row.
| 10         | 5000       | 1693.7138680032904 |   -- The value is the cumulative sample standard deviation from the first to the sixth row.
| 10         | 1300       | 1693.7138680032904 |     
| 10         | 5000       | 1693.7138680032904 |
| 10         | 2450       | 1693.7138680032904 |
| 20         | 3000       | 1123.3320969330487 |
| 20         | 3000       | 1123.3320969330487 |
| 20         | 800        | 1123.3320969330487 |
| 20         | 1100       | 1123.3320969330487 |
| 20         | 2975       | 1123.3320969330487 |
| 30         | 1500       | 668.331255192114 |
| 30         | 950        | 668.331255192114 |
| 30         | 1600       | 668.331255192114 |
| 30         | 1250       | 668.331255192114 |
| 30         | 1250       | 668.331255192114 |
| 30         | 2850       | 668.331255192114 |
+------------+------------+------------+

Exemple 2 : partitionnez par département (deptno), calculez l'écart type de l'échantillon du salaire (sal) et triez les résultats. La fonction renvoie l'écart type cumulé de l'échantillon de la première ligne à la ligne actuelle de la partition actuelle (lignes ayant le même deptno). La commande est la suivante :

select deptno, sal, stddev_samp(sal) over (partition by deptno order by sal) from emp;

Le résultat suivant est renvoyé :

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 0.0        |          -- First row of the window.
| 10         | 1300       | 0.0        |          -- Cumulative sample standard deviation from the first to the second row.
| 10         | 2450       | 663.9528095680697 |   -- Cumulative sample standard deviation from the first to the third row.
| 10         | 2450       | 663.9528095680696 |
| 10         | 5000       | 1511.2081259707413 |
| 10         | 5000       | 1693.7138680032904 |
| 20         | 800        | 0.0        |
| 20         | 1100       | 212.13203435596427 |
| 20         | 2975       | 1178.7175234126282 |
| 20         | 3000       | 1182.7536725793752 |
| 20         | 3000       | 1123.3320969330487 |
| 30         | 950        | 0.0        |
| 30         | 1250       | 212.13203435596427 |
| 30         | 1250       | 173.20508075688772 |
| 30         | 1500       | 225.0      |
| 30         | 1600       | 253.4758371127315 |
| 30         | 2850       | 668.331255192114 |
+------------+------------+------------+

SUM

Syntaxe

sum([distinct] <expr>) over ([partition_clause] [orderby_clause] [frame_clause])

Description

Renvoie la somme de expr dans une fenêtre.
Paramètres
- expr : obligatoire. Colonne pour laquelle calculer la somme. Elle doit être de type DOUBLE, DECIMAL ou BIGINT.
  - Si la valeur d'entrée est de type STRING, elle est implicitement convertie en type DOUBLE pour le calcul. Une erreur est renvoyée pour les autres types de données.
  - Si la valeur d'entrée est NULL, la ligne n'est pas incluse dans le calcul.
  - Si vous spécifiez le mot-clé distinct, la fonction calcule la somme des valeurs uniques.
- partition_clause, orderby_clause et frame_clause : pour plus d'informations, consultez windowing_definition.
Valeur de retour
- Si la valeur d'entrée est de type BIGINT, une valeur BIGINT est renvoyée.
- Si la valeur d'entrée est de type DECIMAL, une valeur DECIMAL est renvoyée.
- Si la valeur d'entrée est de type DOUBLE ou STRING, une valeur DOUBLE est renvoyée.
- Si toutes les valeurs d'entrée sont NULL, la fonction renvoie NULL.

Exemples

Exemple 1 : partitionnez par département (deptno), calculez la somme du salaire (sal) sans tri. La fonction renvoie la somme cumulée de la partition actuelle (lignes ayant le même deptno). La commande est la suivante :

select deptno, sal, sum(sal) over (partition by deptno) from emp;

Le résultat suivant est renvoyé :

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 17500      |   -- First row of the window. The value is the cumulative sum from the first to the sixth row.
| 10         | 2450       | 17500      |   -- The value is the cumulative sum from the first to the sixth row.
| 10         | 5000       | 17500      |   -- The value is the cumulative sum from the first to the sixth row.
| 10         | 1300       | 17500      |
| 10         | 5000       | 17500      |
| 10         | 2450       | 17500      |
| 20         | 3000       | 10875      |
| 20         | 3000       | 10875      |
| 20         | 800        | 10875      |
| 20         | 1100       | 10875      |
| 20         | 2975       | 10875      |
| 30         | 1500       | 9400       |
| 30         | 950        | 9400       |
| 30         | 1600       | 9400       |
| 30         | 1250       | 9400       |
| 30         | 1250       | 9400       |
| 30         | 2850       | 9400       |
+------------+------------+------------+

Exemple 2 : en mode non compatible Hive, partitionnez par département (deptno), calculez la somme du salaire (sal) et triez les résultats. La fonction renvoie la somme cumulée de la première ligne à la ligne actuelle de la partition actuelle (lignes ayant le même deptno). Les commandes sont les suivantes :

-- Disable Hive compatible mode.
set odps.sql.hive.compatible=false;
-- Execute the following SQL command.
select deptno, sal, sum(sal) over (partition by deptno order by sal) from emp;

Le résultat suivant est renvoyé :

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 1300       |   -- First row of the window.
| 10         | 1300       | 2600       |   -- Cumulative sum from the first to the second row.
| 10         | 2450       | 5050       |   -- Cumulative sum from the first to the third row.
| 10         | 2450       | 7500       |
| 10         | 5000       | 12500      |
| 10         | 5000       | 17500      |
| 20         | 800        | 800        |
| 20         | 1100       | 1900       |
| 20         | 2975       | 4875       |
| 20         | 3000       | 7875       |
| 20         | 3000       | 10875      |
| 30         | 950        | 950        |
| 30         | 1250       | 2200       |
| 30         | 1250       | 3450       |
| 30         | 1500       | 4950       |
| 30         | 1600       | 6550       |
| 30         | 2850       | 9400       |
+------------+------------+------------+

Exemple 3 : en mode compatible Hive, partitionnez par département (deptno), calculez la somme du salaire (sal) et triez les résultats. La fonction renvoie la somme cumulée de la première ligne à la ligne ayant la même valeur que la ligne actuelle (les lignes ayant le même sal ont la même somme). Les commandes sont les suivantes :

-- Enable Hive compatible mode.
set odps.sql.hive.compatible=true;
-- Execute the following SQL command.
select deptno, sal, sum(sal) over (partition by deptno order by sal) from emp;

Le résultat suivant est renvoyé :

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 2600       |   -- First row of the window. Since the sal of the first and second rows are the same, the sum for the first row is the cumulative sum of the first two rows.
| 10         | 1300       | 2600       |   -- Cumulative sum from the first to the second row.
| 10         | 2450       | 7500       |   -- Since the sal of the third and fourth rows are the same, the sum for the third row is the cumulative sum of the firstfour rows.
| 10         | 5000       | 17500      |
| 10         | 5000       | 17500      |
| 20         | 800        | 800        |
| 20         | 1100       | 1900       |
| 20         | 2975       | 4875       |
| 20         | 3000       | 10875      |
| 20         | 3000       | 10875      |
| 30         | 950        | 950        |
| 30         | 1250       | 3450       |
| 30         | 1250       | 3450       |
| 30         | 1500       | 4950       |
| 30         | 1600       | 6550       |
| 30         | 2850       | 9400       |
+------------+------------+------------+

Références

Si les fonctions intégrées ne répondent pas à vos besoins, vous pouvez créer des fonctions définies par l'utilisateur. Vue d'ensemble des UDF MaxCompute.
Problèmes courants liés au langage SQL MaxCompute :
Codes d'erreur courants pour les fonctions intégrées :
- Codes d'erreur courants pour les fonctions intégrées
- FAQ sur les fonctions intégrées