Catégories :

Fonctions d’agrégation (General) , Fonctions de fenêtre

MODE

Renvoie la valeur la plus fréquente pour les valeurs comprises dans expr1. Les valeurs NULL sont ignorées. Si toutes les valeurs sont NULL ou s’il n’y a aucune ligne, la fonction renvoie NULL.

Syntaxe

Fonction d’agrégation

MODE( <expr1> )
Copy

Fonction de fenêtre

MODE( <expr1> ) OVER ( [ PARTITION BY <expr2> ] )
Copy

Arguments

expr1

Cette expression produit les valeurs recherchées pour trouver la valeur la plus fréquente. L’expression peut être de l’un des types de données suivants :

  • BINARY

  • BOOLEAN

  • DATE

  • FLOAT

  • INTEGER

  • NUMBER

  • TIMESTAMP (TIMESTAMP_LTZ, TIMESTAMP_NTZ, TIMESTAMP_TZ)

  • VARCHAR

  • VARIANT

Cette fonction ne prend pas en charge les types de données suivants :

  • ARRAY

  • GEOGRAPHY

  • OBJECT

expr2

L’expression facultative sur laquelle partitionner les données en groupes. La sortie contient la valeur la plus fréquente pour chaque groupe/partition.

Renvoie

Le type de données de la valeur renvoyée est identique à celui de l’expression d’entrée.

Notes sur l’utilisation

  • S’il existe un lien pour la valeur la plus fréquente (deux valeurs ou plus apparaissent aussi souvent et plus fréquemment que toute autre valeur), MODE renvoie l’une de ces valeurs.

  • DISTINCT n’est pas pris en charge pour cette fonction.

  • Même si NULL est la valeur la plus fréquente, la fonction ne renvoie pas NULL (sauf si toutes les valeurs sont NULL).

  • Lorsque cette fonction est appelée en tant que fonction de fenêtre, elle ne prend pas en charge :

    • Une clause ORDER BY dans la clause OVER.

    • Cadres de fenêtre explicites.

Exemples

Le code suivant illustre l’utilisation de la fonction MODE :

Créer une table et des données :

create or replace table aggr(k int, v decimal(10,2));
Copy

Obtenez la valeur MODE pour la colonne v. La fonction renvoie NULL, car il n’y a pas de lignes.

select mode(v) from aggr;
+---------+
| MODE(V) |
|---------|
|    NULL |
+---------+
Copy

Insérer quelques lignes :

INSERT INTO aggr (k, v) VALUES
    (1, 10), 
    (1, 10), 
    (1, 10), 
    (1, 10), 
    (1, 20), 
    (1, 21);
Copy

La fonction MODE renvoie la valeur la plus fréquente 10 :

select mode(v) from aggr;
+---------+
| MODE(V) |
|---------|
|   10.00 |
+---------+
Copy

Insérer quelques lignes supplémentaires :

INSERT INTO aggr (k, v) VALUES
    (2, 20), 
    (2, 20), 
    (2, 25), 
    (2, 30);
Copy

Il existe maintenant deux valeurs les plus fréquentes. La fonction MODE sélectionne la valeur 10 :

select mode(v) from aggr;
+---------+
| MODE(V) |
|---------|
|   10.00 |
+---------+
Copy

Insérez une ligne avec une valeur NULL :

INSERT INTO aggr (k, v) VALUES (3, null);
Copy

Obtenir la valeur MODE pour chaque groupe. Notez que parce que le groupe k = 3 contient uniquement des valeurs NULL, la valeur renvoyée pour ce groupe est NULL.

select k, mode(v) 
    from aggr 
    group by k
    order by k;
+---+---------+
| K | MODE(V) |
|---+---------|
| 1 |   10.00 |
| 2 |   20.00 |
| 3 |    NULL |
+---+---------+
Copy

La fonction MODE peut également être utilisée comme fonction de fenêtre de base avec une clause OVER :

select k, v, mode(v) over (partition by k) 
    from aggr 
    order by k, v;
+---+-------+-------------------------------+
| K |     V | MODE(V) OVER (PARTITION BY K) |
|---+-------+-------------------------------|
| 1 | 10.00 |                         10.00 |
| 1 | 10.00 |                         10.00 |
| 1 | 10.00 |                         10.00 |
| 1 | 10.00 |                         10.00 |
| 1 | 20.00 |                         10.00 |
| 1 | 21.00 |                         10.00 |
| 2 | 20.00 |                         20.00 |
| 2 | 20.00 |                         20.00 |
| 2 | 25.00 |                         20.00 |
| 2 | 30.00 |                         20.00 |
| 3 |  NULL |                          NULL |
+---+-------+-------------------------------+
Copy