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 à la hausse.

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 spécifie la colonne à partitionner.

  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 spécifie la colonne que vous souhaitez classer.

  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 égales donnent la même valeur de rang ; cependant, des lacunes dans la séquence seront causées en raison du nombre de valeurs égales (ex æquo).

  Par exemple, si trois lignes renvoient 1, RANK ignorera 2 et 3 et affectera 4 à la ligne suivante du groupe.

• Pour éviter les espaces, utilisez plutôt la fonction DENSE_RANK.

Exemples

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

SELECT state, bushels_produced, RANK()
  OVER (ORDER BY bushels_produced DESC)
  FROM corn_production;

+--------+------------------+------+
|  state | bushels_produced | RANK |
+--------+------------------+------+
| Kansas |              130 |     1|
| Kansas |              120 |     2|
| Iowa   |              110 |     3|
| Iowa   |              100 |     4|
+--------+------------------+------+

Dans chaque état ou province, affichez la production de maïs des agriculteurs par ordre décroissant, ainsi que le rang de la production de chaque agriculteur (valeur la plus élevée = 1) :

SELECT state, bushels_produced, RANK()
  OVER (PARTITION BY state ORDER BY bushels_produced)
  FROM corn_production;

+--------+------------------+------+
|  state | bushels_produced | RANK |
+--------+------------------+------+
| Iowa   |              110 |     1|
| Iowa   |              100 |     2|
| Kansas |              130 |     1|
| Kansas |              120 |     2|
+--------+------------------+------+

Dans la requête et la sortie ci-dessous, les valeurs du symbole boursier SPY indiquent comment les valeurs égales sont gérées (notez que les rangs sont 1, 2, 2, 4. Le rang 3 est ignoré. car il y avait une égalité pour le rang 2) :

SELECT
  symbol,
  exchange,
  price,
  RANK() OVER (PARTITION BY symbol ORDER BY price) AS rank
FROM trades;

+------+--------+------+----+
|symbol|exchange|price |RANK|
+------+--------+------+----+
|QQQ   |Q       | 83.99|   1|
|QQQ   |N       | 84.35|   2|
|QQQ   |Q       | 87.49|   3|
|QQQ   |N       | 88.50|   4|
|SPY   |P       |185.27|   1|
|SPY   |N       |186.29|   2|
|SPY   |N       |186.29|   2|
|SPY   |C       |188.47|   4|
|MSFT  |Q       | 38.77|   1|
|MSFT  |Q       | 39.55|   2|
|MSFT  |P       | 39.66|   3|
|MSFT  |P       | 40.00|   4|
|YHOO  |N       | 32.89|   1|
|YHOO  |Q       | 33.49|   2|
|YHOO  |Q       | 34.21|   3|
|YHOO  |P       | 34.48|   4|
|AAPL  |P       |568.33|   1|
|AAPL  |C       |569.42|   2|
|AAPL  |C       |570.37|   3|
|AAPL  |N       |571.94|   4|
+------+--------+------+----+