Catégories :

Fonctions de la fenêtre (en lien avec le rang, cadre de fenêtre)

DENSE_RANK

Renvoie le rang d’une valeur au sein d’un groupe de valeurs, sans espaces dans les rangs.

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

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

Syntaxe

DENSE_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

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, 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).

Notes sur l’utilisation

  • Les valeurs liées donneront la même valeur de rang, mais contrairement à RANK, elles ne créeront pas de trous dans la séquence.

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_produced float);
insert into corn_production (farmer_ID, state, bushels_produced) 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_produced, DENSE_RANK() OVER (ORDER BY bushels_produced DESC)
    FROM corn_production;
+--------+------------------+----------------------------------------------------+
| STATE  | BUSHELS_PRODUCED | DENSE_RANK() OVER (ORDER BY BUSHELS_PRODUCED DESC) |
|--------+------------------+----------------------------------------------------|
| Kansas |              130 |                                                  1 |
| Kansas |              120 |                                                  2 |
| Iowa   |              110 |                                                  3 |
| Iowa   |              100 |                                                  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_produced, DENSE_RANK()
      OVER (PARTITION BY state ORDER BY bushels_produced DESC)
      FROM corn_production;
+--------+------------------+----------------------------------------------------------------+
| STATE  | BUSHELS_PRODUCED |                                                   DENSE_RANK() |
|        |                  |       OVER (PARTITION BY STATE ORDER BY BUSHELS_PRODUCED DESC) |
|--------+------------------+----------------------------------------------------------------|
| Iowa   |              110 |                                                              1 |
| Iowa   |              100 |                                                              2 |
| Kansas |              130 |                                                              1 |
| Kansas |              120 |                                                              2 |
+--------+------------------+----------------------------------------------------------------+

La requête et la sortie ci-dessous montrent comment les valeurs de liens sont gérées. Notez que 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_produced, DENSE_RANK()
      OVER (ORDER BY bushels_produced DESC)
      FROM corn_production;
+--------+------------------+---------------------------------------------+
| STATE  | BUSHELS_PRODUCED |                                DENSE_RANK() |
|        |                  |       OVER (ORDER BY BUSHELS_PRODUCED DESC) |
|--------+------------------+---------------------------------------------|
| Kansas |              130 |                                           1 |
| Kansas |              120 |                                           2 |
| Iowa   |              110 |                                           3 |
| Iowa   |              110 |                                           3 |
| Iowa   |              100 |                                           4 |
+--------+------------------+---------------------------------------------+