Catégories :: Fonctions de la fenêtre (en lien avec le rang, cadre de fenêtre)

RANK¶

Renvoie le rang d’une valeur au sein d’un groupe ordonné de valeurs.

La valeur de rang commence à 1 et continue séquentiellement.

Si deux valeurs sont identiques, elles auront le même rang.

Syntaxe¶

RANK() OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2> [ { ASC | DESC } ] [ <window_frame> ] )

Pour plus d’informations sur la syntaxe cadre_fenêtre , voir Syntaxe et utilisation du cadre de fenêtre .

Arguments¶

Aucun.

La fonction elle-même ne prend aucun argument, car elle renvoie le rang (position relative) de la ligne en cours dans la fenêtre, qui est classée par <expr2>. L’ordre de la fenêtre détermine le rang, il n’est donc pas nécessaire de transmettre un paramètre supplémentaire à la fonction RANK.

Notes sur l’utilisation¶

expr1 La colonne ou l’expression pour partitionner la fenêtre.

Par exemple, supposons que dans chaque état ou province, vous souhaitiez classer les agriculteurs par ordre de quantité de maïs produit. Dans ce cas, vous partitionnez par état.

Si vous souhaitez un seul groupe (par exemple, vous souhaitez classer tous les agriculteurs des États-Unis, quel que soit l’état dans lequel ils vivent), omettez la clause PARTITION BY.
expr2 La colonne ou l’expression à utiliser pour le rang (ordre).

Par exemple, si vous classez les agriculteurs pour voir qui produit le plus de maïs (dans leur état), utilisez la colonne bushels_produced . Pour plus de détails, voir Exemples (dans ce chapitre).
Les valeurs liées donnent la même valeur de rang ; cependant, des lacunes dans la séquence sont causées en raison du nombre de valeurs liées.

Par exemple, si les trois premières lignes renvoient 1, RANK ignore 2 et 3 et affecte 4 à la ligne suivante du groupe.
Pour éviter les espaces, utilisez plutôt la fonction DENSE_RANK .

Exemples¶

Créer une table et des données :

-- Create table and load data.
create or replace table corn_production (farmer_ID INTEGER, state varchar, bushels float);
insert into corn_production (farmer_ID, state, bushels) values
    (1, 'Iowa', 100),
    (2, 'Iowa', 110),
    (3, 'Kansas', 120),
    (4, 'Kansas', 130);

Affichez la production de maïs des agriculteurs dans l’ordre décroissant, ainsi que le rang de la production de chaque agriculteur (le plus élevé = 1) :

SELECT state, bushels,
        RANK() OVER (ORDER BY bushels DESC),
        DENSE_RANK() OVER (ORDER BY bushels DESC)
    FROM corn_production;
+--------+---------+-------------------------------------+-------------------------------------------+
| STATE  | BUSHELS | RANK() OVER (ORDER BY BUSHELS DESC) | DENSE_RANK() OVER (ORDER BY BUSHELS DESC) |
|--------+---------+-------------------------------------+-------------------------------------------|
| Kansas |     130 |                                   1 |                                         1 |
| Kansas |     120 |                                   2 |                                         2 |
| Iowa   |     110 |                                   3 |                                         3 |
| Iowa   |     100 |                                   4 |                                         4 |
+--------+---------+-------------------------------------+-------------------------------------------+

Au sein de chaque état, affichez la production de maïs des agriculteurs dans l’ordre décroissant, ainsi que le rang de la production de chaque agriculteur (le plus élevé = 1) :

SELECT state, bushels,
        RANK() OVER (PARTITION BY state ORDER BY bushels DESC),
        DENSE_RANK() OVER (PARTITION BY state ORDER BY bushels DESC)
    FROM corn_production;
+--------+---------+--------------------------------------------------------+--------------------------------------------------------------+
| STATE  | BUSHELS | RANK() OVER (PARTITION BY STATE ORDER BY BUSHELS DESC) | DENSE_RANK() OVER (PARTITION BY STATE ORDER BY BUSHELS DESC) |
|--------+---------+--------------------------------------------------------+--------------------------------------------------------------|
| Iowa   |     110 |                                                      1 |                                                            1 |
| Iowa   |     100 |                                                      2 |                                                            2 |
| Kansas |     130 |                                                      1 |                                                            1 |
| Kansas |     120 |                                                      2 |                                                            2 |
+--------+---------+--------------------------------------------------------+--------------------------------------------------------------+

La requête et la sortie ci-dessous montrent comment les valeurs de liens sont gérées avec les fonctions RANK() et DENSE_RANK(). Notez que pour DENSE_RANK, les rangs sont 1, 2, 3, 3, 4. Contrairement à la sortie de la fonction RANK(), le rang 4 n’est pas ignoré, car il y avait une égalité pour le rang 3.

SELECT state, bushels,
        RANK() OVER (ORDER BY bushels DESC),
        DENSE_RANK() OVER (ORDER BY bushels DESC)
    FROM corn_production;
+--------+---------+-------------------------------------+-------------------------------------------+
| STATE  | BUSHELS | RANK() OVER (ORDER BY BUSHELS DESC) | DENSE_RANK() OVER (ORDER BY BUSHELS DESC) |
|--------+---------+-------------------------------------+-------------------------------------------|
| Kansas |     130 |                                   1 |                                         1 |
| Kansas |     120 |                                   2 |                                         2 |
| Iowa   |     110 |                                   3 |                                         3 |
| Iowa   |     110 |                                   3 |                                         3 |
| Iowa   |     100 |                                   5 |                                         4 |
+--------+---------+-------------------------------------+-------------------------------------------+