FIRST_VALUE¶

Syntaxe¶

FIRST_VALUE( <expr> ) [ { IGNORE | RESPECT } NULLS ]
  OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2>  [ { ASC | DESC } ] [ NULLS { FIRST | LAST } ] [ <window_frame> ] )

Pour plus de détails sur la syntaxe window_frame, voir Syntaxe et utilisation des fonctions de fenêtre.

Arguments¶

expr

L’expression qui détermine la valeur de retour.

expr1

L’expression par laquelle il faut partitionner les lignes. Vous pouvez spécifier une seule expression ou une liste d’expressions séparées par des virgules. Par exemple :

PARTITION BY column_1, column_2

Copy

expr2

L’expression par laquelle il faut ordonner les lignes. Vous pouvez spécifier une seule expression ou une liste d’expressions séparées par des virgules. Par exemple :

ORDER BY column_3, column_4

Copy

{ IGNORE | RESPECT } NULLS

Ignorer ou respecter les valeurs de NULL lorsqu’une valeur expr contient des valeurs NULL :

• IGNORE NULLS renvoie la première valeur non NULL.

• RESPECT NULLS renvoie une valeur NULL s’il s’agit de la première valeur de l’expression.

Par défaut : RESPECT NULLS

Notes sur l’utilisation¶

• Cette fonction est une fonction de rang, elle doit donc spécifier une fenêtre. Une clause de fenêtre se compose des sous-clauses suivantes :

  • Sous-clause PARTITION BY expr1 (facultatif).

  • Sous-clause ORDER BY expr2 (obligatoire). Pour plus d’informations sur les options de classement supplémentaires prises en charge (ordre de tri, ordre de valeurs NULL, etc.), consultez la documentation sur la clause ORDER BY qui suit les mêmes règles.

  • Sous-clause window_frame (facultatif).

• L’ordre des lignes dans une fenêtre (et donc le résultat de la requête) n’est entièrement déterministe que si les clés de la clause ORDER BY rendent chaque ligne unique. Prenons l’exemple suivant :

  ... OVER (PARTITION BY p ORDER BY o COLLATE 'lower') ...
  

  Copy

  Le résultat de la requête peut varier si une partition contient des valeurs de la colonne o qui sont identiques, ou qui seraient identiques dans une comparaison insensible à la casse.

• La clause ORDER BY à l’intérieur de la clause OVER contrôle l’ordre des lignes uniquement dans la fenêtre, et non l’ordre des lignes dans la sortie de la requête entière. Pour contrôler l’ordre de sortie, utilisez une clause ORDER BY distincte au niveau le plus externe de la requête.

• Le paramètre facultatif window_frame spécifie le sous-ensemble de lignes de la fenêtre pour lesquelles la fonction est calculée. Si window_frame n’est pas spécifié, la fenêtre par défaut est la fenêtre entière :

  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

  Notez que cela diffère de la norme ANSI, qui spécifie la valeur par défaut suivante pour les cadres de fenêtre :

  RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Pour plus de détails sur la syntaxe window_frame, voir Syntaxe et utilisation des fonctions de fenêtre.

Exemples¶

Cet exemple montre une requête qui utilise la fonction FIRST_VALUE pour trouver l’élément de menu le moins cher dans chaque catégorie. La requête contient deux clauses ORDER BY : une pour contrôler l’ordre des lignes dans chaque partition, et une pour trier la sortie de la requête complète. Pour créer et charger la table utilisée dans cet exemple, voir Créer et charger la table menu_items.

SELECT menu_category, menu_item_name, menu_price_usd,
       FIRST_VALUE(menu_item_name) OVER (PARTITION BY menu_category ORDER BY menu_price_usd) AS cheapest_item
  FROM menu_items
  WHERE menu_category IN ('Beverage', 'Dessert', 'Snack')
  ORDER BY menu_category, menu_price_usd
  LIMIT 12;

+---------------+--------------------+----------------+---------------+
| MENU_CATEGORY | MENU_ITEM_NAME     | MENU_PRICE_USD | CHEAPEST_ITEM |
|---------------+--------------------+----------------+---------------|
| Beverage      | Bottled Water      |           2.00 | Bottled Water |
| Beverage      | Iced Tea           |           3.00 | Bottled Water |
| Beverage      | Bottled Soda       |           3.00 | Bottled Water |
| Beverage      | Lemonade           |           3.50 | Bottled Water |
| Dessert       | Popsicle           |           3.00 | Popsicle      |
| Dessert       | Ice Cream Sandwich |           4.00 | Popsicle      |
| Dessert       | Mango Sticky Rice  |           5.00 | Popsicle      |
| Dessert       | Sugar Cone         |           6.00 | Popsicle      |
| Dessert       | Waffle Cone        |           6.00 | Popsicle      |
| Dessert       | Two Scoop Bowl     |           7.00 | Popsicle      |
| Snack         | Spring Mix Salad   |           6.00 | Fried Pickles |
| Snack         | Fried Pickles      |           6.00 | Fried Pickles |
+---------------+--------------------+----------------+---------------+

L’exemple suivant utilise également la table menu_items pour comparer trois fonctions associées : FIRST_VALUE, NTH_VALUE et LAST_VALUE :

• La requête crée un cadre de fenêtre glissant de trois lignes de large, qui contient :

  • La ligne qui précède la ligne actuelle.

  • La ligne actuelle.

  • La ligne qui suit la ligne actuelle.

• Le 2 de l’appel NTH_VALUE(menu_price_usd, 2) indique la deuxième ligne du cadre de la fenêtre (qui, dans ce cas, est également la ligne actuelle).

• Lorsque la ligne actuelle est la toute première ligne dans le cadre de la fenêtre, il n’y a pas de ligne précédente à référencer, donc FIRST_VALUE renvoie NULL pour cette ligne.

• Les limites du cadre s’étendent parfois au-delà des lignes d’une partition, mais les lignes inexistantes ne sont pas prises en compte dans les calculs de la fonction de fenêtre. Par exemple, lorsque la ligne actuelle est la toute première ligne de la partition et que le cadre de fenêtre est ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING, il n’y a pas de ligne précédente à laquelle se référer, de sorte que la fonction FIRST_VALUE renvoie la valeur de la première ligne de la partition.

• Les résultats ne sont jamais identiques pour les trois fonctions, compte tenu des données figurant dans la table. Ces fonctions sélectionnent la première, la dernière ou la nième valeur pour chaque ligne du cadre, et la sélection des valeurs s’applique séparément à chaque partition.

SELECT menu_category, menu_item_name, menu_price_usd,
       FIRST_VALUE(menu_price_usd) OVER (PARTITION BY menu_category ORDER BY menu_price_usd
         ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS first_val,
       NTH_VALUE(menu_price_usd, 2) OVER (PARTITION BY menu_category ORDER BY menu_price_usd
         ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS nth_val,
       LAST_VALUE(menu_price_usd) OVER (PARTITION BY menu_category ORDER BY menu_price_usd
         ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS last_val
  FROM menu_items
  WHERE menu_category = 'Dessert'
  ORDER BY menu_price_usd;

+---------------+--------------------+----------------+-----------+---------+----------+
| MENU_CATEGORY | MENU_ITEM_NAME     | MENU_PRICE_USD | FIRST_VAL | NTH_VAL | LAST_VAL |
|---------------+--------------------+----------------+-----------+---------+----------|
| Dessert       | Popsicle           |           3.00 |      3.00 |    4.00 |     4.00 |
| Dessert       | Ice Cream Sandwich |           4.00 |      3.00 |    4.00 |     5.00 |
| Dessert       | Mango Sticky Rice  |           5.00 |      4.00 |    5.00 |     6.00 |
| Dessert       | Sugar Cone         |           6.00 |      6.00 |    6.00 |     7.00 |
| Dessert       | Waffle Cone        |           6.00 |      5.00 |    6.00 |     6.00 |
| Dessert       | Two Scoop Bowl     |           7.00 |      6.00 |    7.00 |     7.00 |
+---------------+--------------------+----------------+-----------+---------+----------+

Cet exemple montre la différence entre IGNORE NULLS et RESPECT NULLS. Les données d’exemple comprennent des lignes où la valeur du coût est NULL. Avec le comportement RESPECT NULLS par défaut, si la première ligne de la partition ordonnée comporte une valeur NULL, FIRST_VALUE renvoie NULL. Avec IGNORE NULLS, FIRST_VALUE ignore les valeurs NULL et renvoie la première valeur non NULL.

SELECT item_name, item_cost, item_price,
       FIRST_VALUE(item_cost) RESPECT NULLS
         OVER (ORDER BY item_price) AS first_cost_respect,
       FIRST_VALUE(item_cost) IGNORE NULLS
         OVER (ORDER BY item_price) AS first_cost_ignore
  FROM VALUES
    ('Pretzel', NULL, 3.00),
    ('Corn Dog', NULL, 4.00),
    ('Hot Dog', 1.50, 5.00),
    ('Sandwich', 2.50, 6.00)
  AS menu(item_name, item_cost, item_price)
  ORDER BY item_price;

+-----------+-----------+------------+--------------------+-------------------+
| ITEM_NAME | ITEM_COST | ITEM_PRICE | FIRST_COST_RESPECT | FIRST_COST_IGNORE |
|-----------+-----------+------------+--------------------+-------------------|
| Pretzel   |      NULL |       3.00 |               NULL |              1.50 |
| Corn Dog  |      NULL |       4.00 |               NULL |              1.50 |
| Hot Dog   |      1.50 |       5.00 |               NULL |              1.50 |
| Sandwich  |      2.50 |       6.00 |               NULL |              1.50 |
+-----------+-----------+------------+--------------------+-------------------+