Catégories :

Fonctions d’agrégation (Général) , Fonctions de la fenêtre (Général)

ANY_VALUE

Renvoie une valeur de l’expression du groupe. Le résultat est non déterministe.

Syntaxe

Fonction d’agrégation

ANY_VALUE( [ DISTINCT ] <expr1> )

Fonction de fenêtre

ANY_VALUE( [ DISTINCT ] <expr1> ) OVER ( [ PARTITION BY <expr2> ] )

Notes sur l’utilisation

  • Le mot clé DISTINCT peut être spécifié pour cette fonction, mais il n’a aucun effet.

  • Lorsqu’il est utilisé comme une fonction de fenêtre :

    • Cette fonction ne prend pas en charge :

      • Sous-clause ORDER BY dans la clause OVER().

      • Cadres de fenêtre.

Utilisation avec les instructions GROUP BY

ANY_VALUE peut être utilisé pour simplifier et optimiser les performances des instructions GROUP BY. Un problème commun à de nombreuses requêtes est que le résultat d’une requête avec une clause GROUP BY ne peut contenir que des expressions utilisées dans la clause GROUP BY elle-même, ou des résultats de fonctions d’agrégat. Par exemple :

SELECT customer.id , customer.name , SUM(orders.value)
    FROM customer
    JOIN orders ON customer.id = orders.customer_id
    GROUP BY customer.id , customer.name;

Dans cette requête, l’attribut customer.name doit figurer dans le GROUP BY pour être inclus dans le résultat. Ceci est inutile (par exemple, lorsque customer.id est connu pour être unique) et rend le calcul éventuellement plus complexe et plus lent. Une autre option consiste à utiliser une fonction d’agrégat. Par exemple :

SELECT customer.id , MIN(customer.name) , SUM(orders.value)
    FROM customer
    JOIN orders ON customer.id = orders.customer_id
    GROUP BY customer.id;

Cela simplifie la clause GROUP BY, mais nécessite toujours le calcul de la fonction MIN, ce qui entraîne un coût supplémentaire.

Avec ANY_VALUE, vous pouvez exécuter la requête suivante :

SELECT customer.id , ANY_VALUE(customer.name) , SUM(orders.value)
    FROM customer
    JOIN orders ON customer.id = orders.customer_id
    GROUP BY customer.id;

Exemples

Configuration :

CREATE OR REPLACE TABLE example_any(k int, d decimal(10,5));
INSERT INTO example_any VALUES (1, 1), (1, 5), (1, 10), (2, 2), (2, NULL), (2, 20);

SELECT * FROM example_any;

+---+----------+
| K |        D |
|---+----------|
| 1 |  1.00000 |
| 1 |  5.00000 |
| 1 | 10.00000 |
| 2 |  2.00000 |
| 2 |     NULL |
| 2 | 20.00000 |
+---+----------+

Renvoyez toute valeur de example_any, groupée par k :

SELECT k, ANY_VALUE(d) FROM example_any GROUP BY k;

+---+--------------+
| K | ANY_VALUE(D) |
|---+--------------|
| 1 |      1.00000 |
| 2 |      2.00000 |
+---+--------------+

Cet exemple est identique à l’exemple précédent, démontrant le non déterminisme des résultats :

SELECT k, ANY_VALUE(d) FROM example_any GROUP BY k;

+---+--------------+
| K | ANY_VALUE(D) |
|---+--------------|
| 1 |      5.00000 |
| 2 |     20.00000 |
+---+--------------+