Catégories :

Fonctions d’agrégation (Général) , Fonctions de la fenêtre (Cadre 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).

  • En cas d’utilisation comme une fonction de fenêtre :

    • Cette fonction ne prend pas en charge :

      • Sous-clause ORDER BY dans la clause OVER(). L’ordre des valeurs n’a pas d’importance lors du choix de la valeur la plus fréquente.

      • Cadres de fenêtre.

Exemples

Le code suivant illustre l’utilisation de MODE :

Créer une table et des données :

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

Obtenir 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

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. MODE sélectionne la valeur 10 :

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

Insérer une ligne avec la valeur NULL :

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

Obtenir la valeur MODE pour chaque groupe. Notez que, comme les valeurs du groupe k = 3 ne contiennent que 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

MODE peut également être utilisé 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