Catégories :

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

AVG

Renvoie la moyenne des enregistrements non NULL. Si tous les enregistrements d’un groupe sont NULL, la fonction renvoie NULL.

Syntaxe

Fonction d’agrégation

AVG( [ DISTINCT ] <expr1> )

Fonction de fenêtre

AVG( [ DISTINCT ] <expr1> ) OVER (
                                 [ PARTITION BY <expr2> ]
                                 [ ORDER BY <expr3> [ 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

L’expression est une expression qui correspond à un type de données numérique (INTEGER, FLOAT, DECIMAL, etc.).

expr2

C’est l’expression facultative à partir de laquelle effectuer la partition.

expr3

C’est l’expression facultative à partir de laquelle effectuer le classement dans chaque partition.

Notes sur l’utilisation

  • Lorsqu’elle est transmise à une expression VARCHAR, cette fonction convertit implicitement l’entrée en valeurs à virgule flottante. Si la conversion ne peut pas être effectuée, une erreur est renvoyée.

  • Lorsque cette fonction est appelée en tant que fonction de fenêtre (c’est-à-dire avec une clause OVER) :

    • Si la clause OVER contient une sous-clause ORDER BY, alors :

      • Un cadre de fenêtre est nécessaire. Si aucun cadre de fenêtre n’est spécifié de façon explicite, ORDER BY implique un cadre de fenêtre cumulatif :

        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

        Pour des informations sur les cadres de fenêtre, y compris la syntaxe et des exemples, voir Syntaxe et utilisation du cadre de fenêtre.

        Pour des informations sur les cadres de fenêtre implicites, voir Notes sur l’utilisation du cadre de fenêtre.

      • L’utilisation du mot-clé DISTINCT à l’intérieur de la fonction de fenêtre est interdite et entraîne une erreur de compilation.

Exemples

Configuration :

CREATE OR REPLACE TABLE avg_example(int_col int, d decimal(10,5), s1 varchar(10), s2 varchar(10));
INSERT INTO avg_example VALUES
    (1, 1.1, '1.1','one'), 
    (1, 10, '10','ten'),
    (2, 2.4, '2.4','two'), 
    (2, NULL, NULL, 'NULL'),
    (3, NULL, NULL, 'NULL'),
    (NULL, 9.9, '9.9','nine');

Afficher les données :

SELECT * 
    FROM avg_example 
    ORDER BY int_col, d;
+---------+----------+------+------+
| INT_COL |        D | S1   | S2   |
|---------+----------+------+------|
|       1 |  1.10000 | 1.1  | one  |
|       1 | 10.00000 | 10   | ten  |
|       2 |  2.40000 | 2.4  | two  |
|       2 |     NULL | NULL | NULL |
|       3 |     NULL | NULL | NULL |
|    NULL |  9.90000 | 9.9  | nine |
+---------+----------+------+------+

Calculez la moyenne des colonnes numériques ou pouvant être converties en nombres :

SELECT AVG(int_col), AVG(d)
    FROM avg_example;
+--------------+---------------+
| AVG(INT_COL) |        AVG(D) |
|--------------+---------------|
|     1.800000 | 5.85000000000 |
+--------------+---------------+

Combinez AVG avec GROUP BY pour calculer les moyennes de différents groupes :

SELECT int_col, AVG(d), AVG(s1) 
    FROM avg_example 
    GROUP BY int_col
    ORDER BY int_col;
+---------+---------------+---------+
| INT_COL |        AVG(D) | AVG(S1) |
|---------+---------------+---------|
|       1 | 5.55000000000 |    5.55 |
|       2 | 2.40000000000 |    2.4  |
|       3 |          NULL |    NULL |
|    NULL | 9.90000000000 |    9.9  |
+---------+---------------+---------+

Utilisez comme une simple fonction de fenêtre :

SELECT 
       int_col,
       AVG(int_col) OVER(PARTITION BY int_col) 
    FROM avg_example
    ORDER BY int_col;
+---------+-----------------------------------------+
| INT_COL | AVG(INT_COL) OVER(PARTITION BY INT_COL) |
|---------+-----------------------------------------|
|       1 |                                   1.000 |
|       1 |                                   1.000 |
|       2 |                                   2.000 |
|       2 |                                   2.000 |
|       3 |                                   3.000 |
|    NULL |                                    NULL |
+---------+-----------------------------------------+