MaxCompute:Vue d'ensemble des fonctions d'agrégation

Précautions

MaxCompute V2.0 fournit des fonctions supplémentaires. Si les fonctions que vous utilisez impliquent de nouveaux types de données pris en charge dans l'édition de types de données MaxCompute V2.0, vous devez exécuter l'instruction SET pour activer l'édition de types de données MaxCompute V2.0. Les nouveaux types de données incluent TINYINT, SMALLINT, INT, FLOAT, VARCHAR, TIMESTAMP et BINARY.

• Niveau session : Pour utiliser un nouveau type de données, ajoutez l'instruction set odps.sql.type.system.odps2=true; avant votre instruction SQL et soumettez-les ensemble pour exécution.

• Un Project Owner peut configurer les paramètres au niveau du projet selon les besoins. Les modifications prennent effet dans un délai de 10 à 15 minutes. La commande est la suivante.

  setproject odps.sql.type.system.odps2=true;

  Pour plus d'informations sur setproject, consultez Project commands. Pour plus d'informations sur les précautions à prendre lors de l'activation des types de données au niveau du projet, consultez Data type versions.

• Un worker peut contenir un maximum de 2 millions d'éléments.

Si vous utilisez une instruction SQL incluant plusieurs fonctions d'agrégation et que les ressources du projet sont insuffisantes, un dépassement de mémoire peut se produire. Nous vous recommandons d'optimiser l'instruction SQL ou d'acheter des ressources de calcul selon vos besoins.

Syntaxe

Syntaxe d'une fonction d'agrégation :

<aggregate_name>(<expression>[,...]) [WITHIN GROUP (ORDER BY <col1>[,<col2>…])] [FILTER (WHERE <where_condition>)]

• <aggregate_name>(<expression>[,...]): une fonction d'agrégation intégrée ou une fonction d'agrégation définie par l'utilisateur (UDAF). Le format spécifique dépend de la syntaxe de la fonction d'agrégation.

• WITHIN GROUP (ORDER BY <col1>[,<col2>…]): si une fonction d'agrégation contient cette expression, les données d'entrée de <col1>[,<col2>…] sont triées par ordre croissant par défaut. Pour trier les données par ordre décroissant, utilisez l'expression WITHIN GROUP (ORDER BY <col1>[,<col2>…] DESC).

  Tenez compte des points suivants lors de l'utilisation de cette expression :

  • Vous pouvez utiliser cette expression uniquement pour WM_CONCAT, COLLECT_LIST, COLLECT_SET et les UDAF.

  • Si plusieurs fonctions d'agrégation dans une instruction SELECT contiennent l'expression WITHIN GROUP (ORDER BY <col1>[,<col2>…]), la clause ORDER BY <col1>[,<col2>…] doit être identique pour toutes ces fonctions.

  • Si les paramètres d'une fonction d'agrégation incluent le mot-clé DISTINCT, seules les colonnes DISTINCT peuvent être utilisées dans la clause ORDER BY <col1>[,<col2>…]. L'ensemble des colonnes dans la clause ORDER BY doit être un sous-ensemble des colonnes DISTINCT. De plus, les types de données des champs dans <col1>[,<col2>…] doivent correspondre aux types de données des paramètres d'entrée de la fonction d'agrégation.

    Remarque

    Les fonctions d'agrégation qui prennent en charge l'expression WITHIN GROUP (ORDER BY <col1>[,<col2>…]) n'acceptent qu'un seul paramètre d'entrée. Par conséquent, si une fonction d'agrégation utilise le mot-clé DISTINCT, la clause ORDER BY ne peut inclure qu'une seule colonne, et son type de données doit correspondre à celui du paramètre d'entrée de la fonction d'agrégation.

    Par exemple, le paramètre d'entrée de la fonction WM_CONCAT doit être de type STRING, donc le champ suivant la clause ORDER BY doit également être de type STRING. Pour plus d'informations, consultez l'Exemple 4 ci-dessous. Pour obtenir des détails sur la création de la table emp utilisée dans l'exemple, consultez WM_CONCAT.

  Exemples :

  -- Example 1: Sort the input data in ascending order and then return the output.
  SELECT x, wm_concat(',', y) WITHIN GROUP (ORDER BY y) FROM 
    VALUES('k', 1),('k', 3),('k', 2) AS t(x, y) GROUP BY x;
  -- The following result is returned.
  +------------+------------+
  | x          | _c1        |
  +------------+------------+
  | k          | 1,2,3      |
  +------------+------------+
  
  -- Example 2: Sort the input data in descending order and then return the output.
  SELECT x, wm_concat(',', y) WITHIN GROUP (ORDER BY y DESC) FROM
    VALUES('k', 1),('k', 3),('k', 2) AS t(x, y) GROUP BY x;
  -- The following result is returned.
  +------------+------------+
  | x          | _c1        |
  +------------+------------+
  | k          | 3,2,1      |
  +------------+------------+
  
  -- Example 3
  SELECT id, wm_concat(DISTINCT ',', name) WITHIN GROUP (ORDER BY name DESC) FROM 
    VALUES('k', '1'),('k', '3'),('k', '2') AS t(id, name) GROUP BY id;
  
  -- The following result is returned.
  +------------+------------+
  | id         | _c1        |
  +------------+------------+
  | k          | 3,2,1      |
  +------------+------------+
  
  -- Example 4
  -- Because the parameters of the aggregate function contain the DISTINCT keyword, the sal input parameter of the BIGINT type in the wm_concat function is implicitly converted to the STRING type.
  -- To be consistent with the input parameter type of the wm_concat function, you must use cast to convert sal to the STRING type in `order by sal`. Otherwise, an error is reported.
  SELECT deptno, wm_concat(DISTINCT ',', sal) 
    WITHIN GROUP (ORDER BY cast(sal AS STRING ) DESC) 
    FROM emp GROUP BY deptno ORDER BY deptno;
  
  -- The following result is returned.
  +------------+------------+
  | deptno     | _c1        |
  +------------+------------+
  | 10         | 5000,2450,1300 |
  | 20         | 800,3000,2975,1100 |
  | 30         | 950,2850,1600,1500,1250 |
  +------------+------------+

• [FILTER (WHERE <where_condition>)]: si une fonction d'agrégation contient cette expression, elle traite uniquement les données qui satisfont la condition <where_condition>. Pour plus d'informations sur <where_condition>, consultez Clause WHERE (where_condition).

  Tenez compte des points suivants lors de l'utilisation de cette expression :

  • Seules les fonctions d'agrégation intégrées prennent en charge cette expression. Les UDAF ne la prennent pas en charge.

  • count(*) prend en charge l'expression [FILTER (WHERE <where_condition>)].

  • COUNT_IF ne prend pas en charge l'expression [FILTER (WHERE <where_condition>)].

  Exemples :

  -- Example 1: Filter and aggregate data.
  select
    sum(x),
    sum(x) filter (where y > 1),
    sum(x) filter (where y > 2)
    from values(null, 1),(1, 2),(2, 3),(3, null) as t(x, y);
  -- The following result is returned.
  +------------+------------+------------+
  | _c0        | _c1        | _c2        |
  +------------+------------+------------+
  | 6          | 3          | 2          |
  +------------+------------+------------+
  
  -- Example 2: Use multiple aggregate functions to filter and aggregate data.
  select
    count_if(x > 2),
    sum(x) filter (where y > 1),
    sum(x) filter (where y > 2)
    from values(null, 1),(1, 2),(2, 3),(3, null) as t(x, y);
  -- The following result is returned.
  +------------+------------+------------+
  | _c0        | _c1        | _c2        |
  +------------+------------+------------+
  | 1          | 3          | 2          |
  +------------+------------+------------+

Données d'exemple

create table if not exists emp
   (empno bigint,
    ename string,
    job string,
    mgr bigint,
    hiredate datetime,
    sal bigint,
    comm bigint,
    deptno bigint);
tunnel upload emp.txt emp;

7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20
7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30
7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20
7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30
7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10
7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20
7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10
7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30
7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20
7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30
7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20
7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10
7948,JACCKA,CLERK,7782,1981-04-12 00:00:00,5000,,10
7956,WELAN,CLERK,7649,1982-07-20 00:00:00,2450,,10
7956,TEBAGE,CLERK,7748,1982-12-30 00:00:00,1300,,10

Expressions de filtre

• Limites

  • Seules les fonctions d'agrégation intégrées de MaxCompute prennent en charge les expressions de filtre. Les UDAF ne les prennent pas en charge.

  • count(*) ne peut pas être utilisé avec des expressions de filtre. Utilisez plutôt la fonction COUNT_IF.

• Syntaxe

  <aggregate_name>(<expression>[,...]) [filter (where <where_condition>)]

• description

  Toutes les fonctions d'agrégation prennent en charge les expressions de filtre. Si vous spécifiez une condition de filtre, seules les lignes de données satisfaisant cette condition sont transmises à la fonction d'agrégation concernée pour le traitement des données.

• description.

  • aggregate_name: obligatoire. Nom de la fonction d'agrégation. Sélectionnez une fonction d'agrégation décrite dans cette rubrique selon vos besoins.

  • expression: obligatoire. Paramètres de la fonction d'agrégation sélectionnée. Spécifiez ce paramètre en fonction de la description de la fonction d'agrégation choisie.

  • where_condition: facultatif. Condition de filtre. Pour plus d'informations sur where_condition, consultez Clause WHERE (where_condition).

• description de la valeur renvoyée.

  Pour plus d'informations, consultez la description de la valeur renvoyée pour chaque fonction d'agrégation.

• Exemple d'utilisation

  select sum(sal) filter (where deptno=10), sum(sal) filter (where deptno=20), sum(sal) filter (where deptno=30) from emp;

  Le résultat suivant est renvoyé :

  +------------+------------+------------+
  | _c0        | _c1        | _c2        |
  +------------+------------+------------+
  | 17500      | 10875      | 9400       |
  +------------+------------+------------+

ANY

• Syntaxe

  BOOLEAN ANY(BOOLEAN <colname>)

• Description

  Cette fonction agrège les valeurs de la colonne spécifiée par colname dans un tableau et vérifie si au moins un élément est TRUE. Si au moins une valeur est TRUE, la fonction renvoie TRUE.

• Paramètres

  colname : obligatoire. La colonne doit être de type BOOLEAN.

• Valeur de retour

  Renvoie une valeur de type BOOLEAN. Si la valeur de colname est NULL, la ligne est exclue du calcul.

• Exemples

  -- Returns true.
  SELECT ANY(colname) FROM VALUES (true), (false), (false) AS tab(colname);
  -- Returns true.
  SELECT ANY(colname) FROM VALUES (NULL), (true), (false) AS tab(colname);
  -- Returns false.
  SELECT ANY(colname) FROM VALUES (false), (false), (NULL) AS tab(colname);
  -- Returns true.
  SELECT ANY(colname1) FILTER(WHERE colname2 = 2) FROM VALUES (true, 1), (false, 1), (true, 2) AS tab(colname1, colname2);

ANY_VALUE

• Syntaxe

  any_value(<colname>)

• Description

  Cette fonction d'extension MaxCompute V2.0 renvoie une valeur arbitraire issue d'une plage spécifiée.

• Paramètres

  colname : obligatoire. La colonne peut être de n'importe quel type de données.

• Valeur de retour

  Le type de données de la valeur de retour est identique à celui du paramètre colname. Si la valeur du paramètre colname est null, la ligne contenant cette valeur n'est pas utilisée pour le calcul.

• Exemples

  • Exemple 1 : sélectionnez l'un des employés. Exemple d'instruction :

    select any_value(ename) from emp;

    Le résultat suivant est renvoyé :

    +------------+
    | _c0        |
    +------------+
    | SMITH      |
    +------------+

  • Exemple 2 : utilisez cette fonction avec group by pour regrouper tous les employés par département (deptno) et sélectionnez un employé aléatoire dans chaque groupe. Exemple de commande :

    select deptno, any_value(ename) from emp group by deptno;

    Le résultat suivant est renvoyé :

    +------------+------------+
    | deptno     | _c1        |
    +------------+------------+
    | 10         | CLARK      |
    | 20         | SMITH      |
    | 30         | ALLEN      |
    +------------+------------+

APPROX_DISTINCT

• Syntaxe

  approx_distinct(<colname>)

• Description de la commande

  Renvoie le nombre approximatif de valeurs distinctes dans une colonne spécifiée. Cette fonction est une fonction supplémentaire de MaxCompute V2.0.

• Description

  colname : obligatoire. Nom de la colonne dont les doublons doivent être supprimés.

• Valeur de retour

  Une valeur de type BIGINT est renvoyée. Cette fonction produit une erreur standard de 5 %. Si une valeur de la colonne spécifiée par le paramètre colname est null, la ligne contenant cette valeur n'est pas utilisée pour le calcul.

• Exemples

  • Exemple 1 : calculez un nombre approximatif de valeurs distinctes dans la colonne sal. Exemple d'instruction :

    select approx_distinct(sal) from emp;

    Le résultat suivant est renvoyé :

    +-------------------+
    | numdistinctvalues |
    +-------------------+
    | 12                |
    +-------------------+

  • Exemple 2 : utilisez cette fonction avec group by pour regrouper tous les employés par département (deptno) et calculez le nombre approximatif de valeurs de salaire (sal) distinctes dans chaque groupe. Exemple de commande :

    select deptno, approx_distinct(sal) from emp group by deptno;

    Le résultat suivant est renvoyé :

    +------------+-------------------+
    | deptno     | numdistinctvalues |
    +------------+-------------------+
    | 10         | 3                 |
    | 20         | 4                 |
    | 30         | 5                 |
    +------------+-------------------+

ARG_MAX

• Syntaxe

  arg_max(<valueToMaximize>, <valueToReturn>)

• Description de la commande

  Recherche la ligne dans laquelle la valeur de valueToMaximize est incluse et renvoie la valeur de valueToReturn dans cette ligne. Cette fonction est une fonction supplémentaire de MaxCompute V2.0.

• Description

  • valueToMaximize : obligatoire. Ce paramètre accepte n'importe quel type de données.

  • valueToReturn : obligatoire. Ce paramètre accepte une valeur de n'importe quel type de données.

• Valeur de retour

  Le type de données de la valeur de retour est identique à celui du paramètre valueToReturn. Si plusieurs lignes contiennent la plus grande valeur de valueToMaximize, la valeur de valueToReturn dans l'une des lignes est renvoyée de manière aléatoire. Si la valeur de valueToMaximize est null, la ligne contenant cette valeur n'est pas utilisée pour le calcul.

• Exemples

  • Exemple 1 : renvoyez le nom de l'employé qui a le salaire le plus élevé. Exemple d'instruction :

    select arg_max(sal, ename) from emp;

    Le résultat suivant est renvoyé :

    +------------+
    | _c0        |
    +------------+
    | KING       |
    +------------+

  • Exemple 2 : utilisez cette fonction avec group by pour regrouper tous les employés par département (deptno) et renvoyez le nom de l'employé ayant le salaire le plus élevé dans chaque groupe. Exemple de commande :

    select deptno, arg_max(sal, ename) from emp group by deptno;

    Le résultat suivant est renvoyé :

    +------------+------------+
    | deptno     | _c1        |
    +------------+------------+
    | 10         | KING       |
    | 20         | SCOTT      |
    | 30         | BLAKE      |
    +------------+------------+

ARG_MIN

• Syntaxe

  arg_min(<valueToMinimize>, <valueToReturn>)

• Description

  Recherche la ligne dans laquelle la valeur minimale de valueToMinimize est incluse et renvoie la valeur de valueToReturn dans cette ligne. Cette fonction est une fonction supplémentaire de MaxCompute V2.0.

• Description

  • valueToMinimize : obligatoire. Valeur de n'importe quel type de données.

  • valueToReturn : obligatoire. Valeur de n'importe quel type de données.

• Les valeurs de retour sont décrites ci-dessous.

  Le type de données de la valeur de retour est identique à celui du paramètre valueToReturn. Si plusieurs lignes contiennent la plus petite valeur de valueToMinimize, la valeur de valueToReturn dans l'une des lignes est renvoyée de manière aléatoire. Si la valeur de valueToMinimize est null, la ligne contenant cette valeur n'est pas utilisée pour le calcul.

• Exemples

  • Exemple 1 : renvoyez le nom de l'employé qui a le salaire le plus bas. Exemple d'instruction :

    select arg_min(sal, ename) from emp;

    Le résultat suivant est renvoyé :

    +------------+
    | _c0        |
    +------------+
    | SMITH      |
    +------------+

  • Exemple 2 : utilisez cette fonction avec group by pour regrouper tous les employés par département (deptno) et renvoyez le nom de l'employé ayant le salaire le plus bas dans chaque groupe. Exemple de commande :

    select deptno, arg_min(sal, ename) from emp group by deptno;

    Le résultat suivant est renvoyé :

    +------------+------------+
    | deptno     | _c1        |
    +------------+------------+
    | 10         | MILLER     |
    | 20         | SMITH      |
    | 30         | JAMES      |
    +------------+------------+

AVG

• Syntaxe

  DECIMAL|DOUBLE  avg(<colname>)

• description

  Cette fonction permet de calculer la valeur moyenne.

• description

  colname : obligatoire. Les valeurs de la colonne prennent en charge tous les types de données et sont converties au type DOUBLE avant le calcul.

• Valeur renvoyée

  Si la valeur de colname est NULL, la ligne contenant cette valeur n'est pas utilisée pour le calcul. Le tableau suivant décrit les correspondances entre les types de données d'entrée et les valeurs de retour.

  Type d'entrée	Type de valeur de retour
  TINYINT	DOUBLE
  SMALLINT	DOUBLE
  INT	DOUBLE
  BIGINT	DOUBLE
  FLOAT	DOUBLE
  DOUBLE	DOUBLE
  DECIMAL	DECIMAL

• Exemples

  • Exemple 1 : calculez la moyenne des salaires (sal) de tous les employés. Exemple d'instruction :

    select avg(sal) from emp;

    Le résultat suivant est renvoyé :

    +------------+
    | _c0        |
    +------------+
    | 2222.0588235294117 |
    +------------+

  • Exemple 2 : utilisez cette fonction avec group by pour regrouper tous les employés par département (deptno) et calculer le salaire moyen (sal) pour chaque département. Exemple de commande :

    select deptno, avg(sal) from emp group by deptno;

    Le résultat suivant est renvoyé :

    +------------+------------+
    | deptno     | _c1        |
    +------------+------------+
    | 10         | 2916.6666666666665 |
    | 20         | 2175.0     |
    | 30         | 1566.6666666666667 |
    +------------+------------+

BITWISE_AND_AGG

• Syntaxe

  BIGINT bitwise_and_agg(BIGINT value)

• description

  Agrège les valeurs d'entrée en appliquant l'opération ET bit à bit.

• description

  value : obligatoire. Valeur de type BIGINT. Les valeurs NULL sont exclues du calcul.

• Valeur renvoyée

  Une valeur de type BIGINT est renvoyée.

• Exemples

  SELECT id, bitwise_and_agg(v) FROM
      VALUES (1L, 2L), (1L, 1L), (2L, null), (1L, null) t(id, v) GROUP BY id;

  Le résultat suivant est renvoyé :

  +------------+------------+
  | id         | _c1        |
  +------------+------------+
  | 1          | 0          |
  | 2          | NULL       |
  +------------+------------+

BITWISE_OR_AGG

• Déclaration de fonction

  bigint bitwise_or_agg(bigint value)

• description

  Agrège les valeurs d'entrée en appliquant l'opération OU bit à bit.

• description des paramètres

  value : obligatoire. Valeur de type BIGINT. La valeur NULL n'est pas utilisée pour le calcul.

• Valeur renvoyée

  Une valeur de type BIGINT est renvoyée.

• Exemples

  select id, bitwise_or_agg(v) from
      values (1L, 2L), (1L, 1L), (2L, null), (1L, null) t(id, v) group by id;

  Le résultat suivant est renvoyé :

  +------------+------------+
  | id         | _c1        |
  +------------+------------+
  | 1          | 3          |
  | 2          | NULL       |
  +------------+------------+

BITWISE_XOR_AGG

• Déclaration de fonction

  BIGINT BITWISE_XOR_AGG(BIGINT|INT|SMALLINT|TINYINT value)

• description

  Agrège les valeurs d'entrée en appliquant l'opération OU exclusif (XOR) bit à bit.

• description des paramètres

  value : obligatoire. Valeur de type BIGINT, INT, SMALLINT ou TINYINT. Les valeurs NULL sont exclues du calcul.

• Valeur renvoyée

  Renvoie une valeur de type BIGINT. Les règles suivantes s'appliquent :

  • Si value n'est pas de type BIGINT, INT, SMALLINT ou TINYINT, une erreur est renvoyée.

  • Renvoie NULL si value est NULL.

• Exemple

  SELECT id, bitwise_xor_agg(v) FROM 
    VALUES (1L, 2L), (1L, 1L), (2L, NULL), (1L, NULL) t(id, v) GROUP BY id;

  Le résultat suivant est renvoyé.

  +------------+------------+
  | id         | _c1        | 
  +------------+------------+
  | 1          | 3          | 
  | 2          | NULL       | 
  +------------+------------+

BOOL_AND

• Syntaxe

  BOOLEAN BOOL_AND(<colname>)

• description de la commande

  Agrège les valeurs de la colonne spécifiée par colname dans un tableau et applique une opération ET logique sur les valeurs booléennes.

• Paramètres

  colname : obligatoire. Nom d'une colonne de table. La colonne doit être de type BOOLEAN.

• Valeur renvoyée

  Renvoie une valeur de type BOOLEAN. Les règles suivantes s'appliquent :

  • Si toutes les valeurs d'entrée sont true, la fonction renvoie true. Sinon, elle renvoie false.

  • La fonction BOOL_AND() ignore les valeurs NULL du groupe.

• Exemples

  -- Example 1: Perform a simple logical AND operation.
  SELECT bool_and(colname) FROM VALUES (true), (false), (true) AS tab(colname);
  -- The following result is returned.
  +------+
  | _c0  | 
  +------+
  | false | 
  +------+
  
  -- Example 2: The BOOL_AND() function ignores NULL values in the group.
  SELECT bool_and(colname) FROM VALUES (NULL), (true), (true) AS tab(colname);
  -- The following result is returned.
  +------+
  | _c0  | 
  +------+
  | true | 
  +------+
  
  -- Example 3: Aggregate only a specific column.
  SELECT bool_and(colname1) FROM VALUES (true, 1), (false, 2), (true, 1) AS tab(colname1, colname2);
  -- The following result is returned.
  +------+
  | _c0  | 
  +------+
  | false | 
  +------+
  
  -- Example 4: Perform a logical AND operation after filtering.
  SELECT bool_and(colname1) FILTER(WHERE colname2 = 1) FROM VALUES (true, 1), (false, 2), (true, 1) AS tab(colname1, colname2);
  -- The following result is returned.
  +------+
  | _c0  | 
  +------+
  | true | 
  +------+

BOOL_OR

• Syntaxe

  BOOLEAN BOOL_OR(BOOLEAN <colname>)

• description de la commande

  Agrège les valeurs de la colonne spécifiée par colname dans un tableau et applique une opération OU logique sur les valeurs booléennes.

• détails des paramètres

  colname : obligatoire. Nom d'une colonne de table. La colonne doit être de type BOOLEAN.

• Valeur renvoyée

  Renvoie une valeur de type BOOLEAN. Les règles suivantes s'appliquent :

  • Si au moins une valeur d'entrée du groupe est true, la fonction renvoie true. Si toutes les valeurs sont false, la fonction renvoie false.

  • La fonction BOOL_OR() ignore les valeurs NULL du groupe.

• Exemples

  -- Example 1: Perform a simple logical OR operation.
  SELECT bool_or(colname) FROM VALUES (true), (false), (false) AS tab(colname);
  -- The following result is returned.
  +------+
  | _c0  | 
  +------+
  | true | 
  +------+
  
  -- Example 2: The BOOL_OR() function ignores NULL values in the group.
  SELECT bool_or(colname) FROM VALUES (NULL), (true), (false) AS tab(colname);
  -- The following result is returned.
  +------+
  | _c0  | 
  +------+
  | true | 
  +------+
  
  -- Example 3
  SELECT bool_or(colname1) FROM VALUES (false), (false), (NULL) AS tab(colname1);
  -- The following result is returned.
  +------+
  | _c0  | 
  +------+
  | false | 
  +------+
  
  -- Example 4: Perform a logical OR operation after filtering.
  SELECT bool_or(colname1) FILTER(WHERE colname2 = 1) FROM VALUES (true, 1), (false, 1), (true, 2) AS tab(colname1, colname2);
  -- The following result is returned.
  +------+
  | _c0  | 
  +------+
  | true | 
  +------+

COLLECT_LIST

• Syntaxe

  array collect_list(<colname>)

• Description

  Agrège les valeurs de la colonne spécifiée par colname dans un tableau. Cette fonction est une extension fournie par MaxCompute V2.0.

• Paramètres

  colname : obligatoire. Nom d'une colonne de table. La colonne peut être de n'importe quel type de données.

• Valeur renvoyée

  Renvoie une valeur de type ARRAY. Si une valeur de la colonne spécifiée par colname est null, la ligne contenant cette valeur n'est pas utilisée pour le calcul.

• Exemples

  • Exemple 1 : agrégez les valeurs de salaire (sal) de tous les employés dans un tableau. Exemple d'instruction :

    select collect_list(sal) from emp;

    Le résultat suivant est renvoyé :

    +------------+
    | _c0        |
    +------------+
    | [800,1600,1250,2975,1250,2850,2450,3000,5000,1500,1100,950,3000,1300,5000,2450,1300] |
    +------------+

  • Exemple 2 : utilisez cette fonction avec group by pour regrouper tous les employés par département (deptno) et agréger les salaires (sal) des employés d'un même groupe dans un tableau. Exemple de commande :

    select deptno, collect_list(sal) from emp group by deptno;

    Le résultat suivant est renvoyé :

    +------------+------------+
    | deptno     | _c1        |
    +------------+------------+
    | 10         | [2450,5000,1300,5000,2450,1300] |
    | 20         | [800,2975,3000,1100,3000] |
    | 30         | [1600,1250,1250,2850,1500,950] |
    +------------+------------+

  • Exemple 3 : utilisez cette fonction avec group by pour regrouper tous les employés par département (deptno) et agréger les valeurs de salaire (sal) distinctes des employés d'un même groupe dans un tableau. Exemple de commande :

    select deptno, collect_list(distinct sal) from emp group by deptno;

    Le résultat suivant est renvoyé :

    +------------+------------+
    | deptno     | _c1        |
    +------------+------------+
    | 10         | [1300,2450,5000] |
    | 20         | [800,1100,2975,3000] |
    | 30         | [950,1250,1500,1600,2850] |
    +------------+------------+

COLLECT_SET

• Syntaxe

  array collect_set(<colname>)

• Description

  Agrège les valeurs spécifiées par colname dans un tableau ne contenant que des valeurs distinctes. Cette fonction est une fonction supplémentaire de MaxCompute V2.0.

• Description

  colname : obligatoire. Nom d'une colonne, qui peut être de n'importe quel type de données.

• Valeur renvoyée

  Renvoie une valeur de type ARRAY. Si une valeur de la colonne spécifiée par colname est null, la ligne contenant cette valeur n'est pas utilisée pour le calcul.

• Exemples

  • Exemple 1 : agrégez les valeurs de salaire (sal) de tous les employés dans un tableau ne contenant que des valeurs distinctes. Exemple d'instruction :

    select collect_set(sal) from emp;

    Le résultat suivant est renvoyé :

    +------------+
    | _c0        |
    +------------+
    | [800,950,1100,1250,1300,1500,1600,2450,2850,2975,3000,5000] |
    +------------+

  • Exemple 2 : utilisez cette fonction avec group by pour regrouper tous les employés par département (deptno) et agréger les salaires (sal) des employés d'un même groupe dans un tableau de valeurs distinctes. Exemple de commande :

    select deptno, collect_set(sal) from emp group by deptno;

    Le résultat suivant est renvoyé :

    +------------+------------+
    | deptno     | _c1        |
    +------------+------------+
    | 10         | [1300,2450,5000] |
    | 20         | [800,1100,2975,3000] |
    | 30         | [950,1250,1500,1600,2850] |
    +------------+------------+

CORR

• Syntaxe

  double corr(<col1>, <col2>)

• Description

  Calcule le coefficient de corrélation de Pearson de deux colonnes de données. Il s'agit d'une fonction d'extension dans MaxCompute V2.0.

• Paramètres

  col1 et col2 : obligatoires. Noms des deux colonnes de la table pour lesquelles vous souhaitez calculer le coefficient de corrélation de Pearson. Les colonnes doivent être de type DOUBLE, BIGINT, INT, SMALLINT, TINYINT, FLOAT ou DECIMAL. Les types de données de col1 et col2 peuvent différer.

• Valeur renvoyée

  Renvoie une valeur de type DOUBLE. Si une ligne d'une colonne d'entrée contient une valeur NULL, cette ligne n'est pas utilisée dans le calcul.

• Exemple

  Sur la base des données d'exemple, la commande suivante calcule le coefficient de corrélation de Pearson des colonnes double_data et float_data.

  select corr(double_data,float_data) from mf_math_fun_t;

  La valeur renvoyée est 1,0.

COUNT

-- Count the number of records.
BIGINT COUNT([DISTINCT|ALL] <colname>)

-- Count the number of records in the window.
BIGINT COUNT(*) OVER ([partition_clause] [orderby_clause] [frame_clause])
BIGINT COUNT([DISTINCT] <expr>[,...]) OVER ([partition_clause] [orderby_clause] [frame_clause])

SELECT sal, COUNT(sal) OVER (PARTITION BY sal) AS count
FROM emp;

+------------+------------+
| sal        | count      |
+------------+------------+
| 800        | 1          |
| 950        | 1          |
| 1100       | 1          |
| 1250       | 2          |  -- Two rows share sal=1250; both return 2.
| 1250       | 2          |
| 1300       | 2          |
| 1300       | 2          |
| 1500       | 1          |
| 1600       | 1          |
| 2450       | 2          |
| 2450       | 2          |
| 2850       | 1          |
| 2975       | 1          |
| 3000       | 2          |
| 3000       | 2          |
| 5000       | 2          |
| 5000       | 2          |
+------------+------------+

-- Disable Hive compatible mode.
SET odps.sql.hive.compatible=false;

SELECT sal, COUNT(sal) OVER (PARTITION BY sal ORDER BY sal) AS count
FROM emp;

+------------+------------+
| sal        | count      |
+------------+------------+
| 800        | 1          |
| 950        | 1          |
| 1100       | 1          |
| 1250       | 1          |  -- Running count starts at 1 for the first row in the partition.
| 1250       | 2          |  -- Increments to 2 for the second row.
| 1300       | 1          |
| 1300       | 2          |
| 1500       | 1          |
| 1600       | 1          |
| 2450       | 1          |
| 2450       | 2          |
| 2850       | 1          |
| 2975       | 1          |
| 3000       | 1          |
| 3000       | 2          |
| 5000       | 1          |
| 5000       | 2          |
+------------+------------+

-- Enable Hive compatible mode.
SET odps.sql.hive.compatible=true;

SELECT sal, COUNT(sal) OVER (PARTITION BY sal ORDER BY sal) AS count
FROM emp;

+------------+------------+
| sal        | count      |
+------------+------------+
| 800        | 1          |
| 950        | 1          |
| 1100       | 1          |
| 1250       | 2          |  -- Both rows in the partition return the full partition count.
| 1250       | 2          |
| 1300       | 2          |
| 1300       | 2          |
| 1500       | 1          |
| 1600       | 1          |
| 2450       | 2          |
| 2450       | 2          |
| 2850       | 1          |
| 2975       | 1          |
| 3000       | 2          |
| 3000       | 2          |
| 5000       | 2          |
| 5000       | 2          |
+------------+------------+

SELECT COUNT(*) FROM emp;

+------------+
| _c0        |
+------------+
| 17         |
+------------+

SELECT deptno, COUNT(*) FROM emp GROUP BY deptno;

+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 20         | 5          |
| 30         | 6          |
| 10         | 6          |
+------------+------------+

SELECT COUNT(DISTINCT deptno) FROM emp;

+------------+
| _c0        |
+------------+
| 3          |
+------------+

COUNT_IF

• Syntaxe

  bigint count_if(boolean <expr>)

• Description

  Renvoie le nombre d'enregistrements dont la valeur de expr est True.

• Paramètres

  expr : obligatoire. Expression BOOLEAN.

• Valeur de retour

  Une valeur de type BIGINT est renvoyée. Si la valeur du paramètre expr est False ou si la valeur d'une colonne spécifique dans expr est null, la ligne contenant cette valeur n'est pas utilisée pour le calcul.

• Exemples

  select count_if(sal > 1000), count_if(sal <=1000) from emp;

  Le résultat suivant est renvoyé :

  +------------+------------+
  | _c0        | _c1        |
  +------------+------------+
  | 15         | 2          |
  +------------+------------+

COVAR_POP

• Syntaxe

  double covar_pop(<colname1>, <colname2>)

• description

  Calcule la covariance de population de deux colonnes numériques spécifiées. Cette fonction est une fonction supplémentaire de MaxCompute V2.0.

• description des paramètres

  colname1 et colname2 : obligatoire. Colonnes de type de données numérique. Si la colonne spécifiée n'est pas une colonne numérique, une valeur null est renvoyée.

• Exemples

  exécutez les commandes suivantes pour ajouter des données à la table emp :

  -- sal_new is the new salary column.
  alter table emp add columns (sal_new bigint);
  insert overwrite table emp select empno, ename, job, mgr, hiredate, sal, comm, deptno, sal+1000 from emp;

  • Exemple 1 : Calculez la covariance de population des colonnes sal et sal_new. Exemple d'instruction :

    select covar_pop(sal, sal_new) from emp;

    Le résultat suivant est renvoyé :

    +------------+
    | _c0        |
    +------------+
    | 1594550.1730103805 |
    +------------+

  • Exemple 2 : Utilisez cette fonction avec group by pour regrouper tous les employés par département (deptno) et calculer la covariance de population des colonnes sal et sal_new pour chaque groupe. Exemple de commande :

    select deptno, covar_pop(sal, sal_new) from emp group by deptno;

    Le résultat suivant est renvoyé :

    +------------+------------+
    | deptno     | _c1        |
    +------------+------------+
    | 10         | 2390555.5555555555 |
    | 20         | 1009500.0  |
    | 30         | 372222.2222222222 |
    +------------+------------+

COVAR_SAMP

• Syntaxe

  double covar_samp(<colname1>, <colname2>)

• description

  Calcule la covariance d'échantillon de deux colonnes numériques spécifiées. Cette fonction est une fonction supplémentaire de MaxCompute V2.0.

• description

  colname1 et colname2 : obligatoire. Colonnes de type de données numérique. Si la colonne spécifiée n'est pas une colonne numérique, une valeur null est renvoyée.

• Exemples

  exécutez les commandes suivantes pour ajouter des données à la table emp :

  -- sal_new is the new salary column.
  alter table emp add columns (sal_new bigint);
  insert overwrite table emp select empno, ename, job, mgr, hiredate, sal, comm, deptno, sal+1000 from emp;

  • Exemple 1 : Calculez la covariance d'échantillon des colonnes sal et sal_new. Exemple d'instruction :

    select covar_samp(sal, sal_new) from emp;

    Le résultat suivant est renvoyé :

    +------------+
    | _c0        |
    +------------+
    | 1694209.5588235292 |
    +------------+

  • Exemple 2 : Utilisez cette fonction avec group by pour regrouper tous les employés par département (deptno) et calculer la covariance d'échantillon des colonnes sal et sal_new pour chaque groupe. Exemple de commande :

    select deptno, covar_samp(sal, sal_new) from emp group by deptno;

    Le résultat suivant est renvoyé :

    +------------+------------+
    | deptno     | _c1        |
    +------------+------------+
    | 10         | 2868666.6666666665 |
    | 20         | 1261875.0  |
    | 30         | 446666.6666666666 |
    +------------+------------+

HISTOGRAM

• Déclare une fonction.

  map<K, bigint> histogram(K input);

• décrit la commande.

  Renvoie un map contenant le nombre d'occurrences de chaque valeur d'entrée. Les clés du map sont les valeurs d'entrée. Chaque valeur du map correspond au nombre d'occurrences d'une valeur d'entrée. La valeur null est ignorée.

• description

  input : valeurs d'entrée, utilisées comme clés dans le map.

• Valeur de retour

  Un map contenant le nombre d'occurrences de chaque valeur d'entrée est renvoyé.

• Exemples

  select histogram(a) from values
      ('hi'), (null), ('apple'), ('pie'), ('apple') t(a);

  Le résultat suivant est renvoyé :

  +----------------------------+
  | _c0                        |
  +----------------------------+
  | {"pie":1,"hi":1,"apple":2} |
  +----------------------------+

MAP_AGG

• Déclarations de fonctions

  map<K, V> map_agg(K a, V b);

• Cette rubrique décrit la commande.

  Renvoie un map créé à partir de a et b. a est la clé dans le map. b est la valeur de la clé dans le map. Si la clé dans le map est null, la clé est ignorée. Si le champ de clé contient des valeurs en double, l'une des valeurs est conservée de manière aléatoire.

• description.

  • a : champ d'entrée utilisé comme clé dans le map.

  • b : champ d'entrée utilisé comme valeur dans le map.

• description de la valeur de retour.

  Un nouveau map est renvoyé.

• Exemples

  select map_agg(a, b) from
          values (1L, 'apple'), (2L, 'hi'), (null, 'good'), (1L, 'pie') t(a, b);

  Le résultat suivant est renvoyé :

  +------------------------+
  | _c0                    |
  +------------------------+
  | {"2":"hi","1":"apple"} |
  +------------------------+

MAP_UNION

• Déclaration de fonction

  map<K, V> map_union(map<K, V> input);

• Cette section décrit la commande.

  Renvoie un nouveau map qui est l'union de tous les maps d'entrée. Si une clé existe dans plusieurs maps d'entrée, l'une des valeurs correspondant à la clé est conservée de manière aléatoire.

• Paramètres

  input : les maps d'entrée.

• description de la valeur de retour.

  Un nouveau map est renvoyé.

• Exemples

  select map_union(a) from values
      (map(1L, 'hi', 2L, 'apple', 3L, 'pie')), (map(1L, 'good', 4L, 'this')), (null) t(a);

  Le résultat suivant est renvoyé :

  +-----------------------------------------------+
  | _c0                                           |
  +-----------------------------------------------+
  | {"4":"this","1":"good","2":"apple","3":"pie"} |
  +-----------------------------------------------+

MAP_UNION_SUM

• Déclaration de fonction

  map<K, V> map_union_sum(map<K, V> input);

• description

  Renvoie un nouveau map qui est l'union de tous les maps d'entrée. Le map de sortie additionne les valeurs des clés correspondantes dans tous les maps d'entrée. Si la valeur correspondant à une clé est NULL, la valeur est convertie en 0.

  Remarque

  Les valeurs dans les maps d'entrée doivent être de type de données BIGINT, INT, SMALLINT, TINYINT, FLOAT, DOUBLE ou DECIMAL.

• description des paramètres.

  input : les maps d'entrée.

• description de la valeur de retour.

  Un nouveau map est renvoyé.

  Remarque

  Les valeurs dans le nouveau map sont de type BIGINT, DOUBLE ou DECIMAL.

• Exemples

  select map_union_sum(a) from values
      (map('hi', 2L, 'apple', 3L, 'pie', 1L)), (map('apple', null, 'hi', 4L)), (null) t(a);

  Le résultat suivant est renvoyé :

  +----------------------------+
  | _c0                        |
  +----------------------------+
  | {"apple":3,"hi":6,"pie":1} |
  +----------------------------+

MAX

• Syntaxe

  max(<colname>)

• description

  Renvoie la valeur maximale d'une colonne.

• description des paramètres.

  colname : obligatoire. Nom d'une colonne, qui peut être de n'importe quel type de données autre que BOOLEAN.

• Valeur de retour

  Le type de la valeur de retour est identique à celui du paramètre colname. La valeur de retour varie selon les règles suivantes :

  • Si la valeur de colname est null, la ligne contenant cette valeur n'est pas utilisée pour le calcul.

  • Si la valeur de colname est de type BOOLEAN, elle n'est pas utilisée pour le calcul.

• Exemples

  • Exemple 1 : calculez le salaire (sal) le plus élevé de tous les employés. Exemple d'instruction :

    select max(sal) from emp;

    Le résultat suivant est renvoyé :

    +------------+
    | _c0        |
    +------------+
    | 5000       |
    +------------+

  • Exemple 2 : utilisez cette fonction avec group by pour regrouper tous les employés par département (deptno) et calculer le salaire (sal) le plus élevé dans chaque département. Exemple de commande :

    select deptno, max(sal) from emp group by deptno;

    Le résultat suivant est renvoyé :

    +------------+------------+
    | deptno     | _c1        |
    +------------+------------+
    | 10         | 5000       |
    | 20         | 3000       |
    | 30         | 2850       |
    +------------+------------+

MAX_BY

• Syntaxe

  max_by(<valueToReturn>,<valueToMaximize>)

• description

  Remarque

  La fonction MAX_BY offre la même fonctionnalité que la fonction ARG_MAX. La différence réside dans l'ordre des paramètres. La fonction MAX_BY a été introduite dans MaxCompute pour maintenir la compatibilité avec la syntaxe open source.

  Recherche la ligne dans laquelle la valeur de valueToMaximize est incluse et renvoie la valeur de valueToReturn dans cette ligne. Cette fonction est une fonction supplémentaire de MaxCompute V2.0.

• description des paramètres

  • valueToMaximize : obligatoire. Valeur de n'importe quel type de données.

  • valueToReturn : obligatoire. Valeur de n'importe quel type de données.

• Valeur de retour

  Le type de données de la valeur de retour est identique à celui du paramètre valueToReturn. Si plusieurs lignes possèdent la valeur la plus élevée de valueToMaximize, la valeur de valueToReturn dans l'une des lignes est renvoyée de manière aléatoire. Si la valeur de valueToMaximize est null, la ligne contenant cette valeur n'est pas utilisée pour le calcul.

• Exemples

  • Exemple 1 : renvoyez le nom de l'employé ayant le salaire le plus élevé. Exemple d'instruction :

    select max_by(ename,sal) from emp;

    Le résultat suivant est renvoyé :

    +------------+
    | _c0        |
    +------------+
    | KING       |
    +------------+

  • Exemple 2 : utilisez cette fonction avec group by pour regrouper tous les employés par département (deptno) et renvoyer le nom de l'employé ayant le salaire le plus élevé dans chaque groupe. Exemple de commande :

    select deptno, max_by(ename,sal) from emp group by deptno;

    Le résultat suivant est renvoyé :

    +------------+------------+
    | deptno     | _c1        |
    +------------+------------+
    | 10         | KING       |
    | 20         | SCOTT      |
    | 30         | BLAKE      |
    +------------+------------+

MEDIAN

• Syntaxe

  double median(double <colname>)
  decimal median(decimal <colname>)

• description

  Renvoie la valeur médiane d'une colonne.

• description

  colname : obligatoire. Nom d'une colonne, qui peut être de type DOUBLE ou DECIMAL. Si la valeur d'entrée est de type STRING ou BIGINT, elle est implicitement convertie en type DOUBLE avant le calcul.

• Valeur de retour

  Si la valeur de colname est null, la ligne contenant cette valeur n'est pas utilisée pour le calcul. Le tableau suivant décrit les correspondances entre les types de données des données d'entrée et les valeurs de retour.

  Type d'entrée	Type de valeur de retour
  TINYINT	DOUBLE
  SMALLINT	DOUBLE
  INT	DOUBLE
  BIGINT	DOUBLE
  FLOAT	DOUBLE
  DOUBLE	DOUBLE
  DECIMAL	DECIMAL

• Exemples

  • Exemple 1 : calculez les valeurs médianes du salaire (sal) de tous les employés. Exemple d'instruction :

    select median(sal) from emp;

    Le résultat suivant est renvoyé :

    +------------+
    | _c0        |
    +------------+
    | 1600.0     |
    +------------+

  • Exemple 2 : utilisez cette fonction avec group by pour regrouper tous les employés par département (deptno) et calculer le salaire médian (sal) pour chaque département. Exemple de commande :

    select deptno, median(sal) from emp group by deptno;

    Le résultat suivant est renvoyé :

    +------------+------------+
    | deptno     | _c1        |
    +------------+------------+
    | 10         | 2450.0     |
    | 20         | 2975.0     |
    | 30         | 1375.0     |
    +------------+------------+

MIN

• Syntaxe

  min(<colname>)

• description de la commande.

  Vous pouvez calculer la valeur minimale.

• Paramètres

  colname : obligatoire. Nom d'une colonne, qui peut être de n'importe quel type de données autre que BOOLEAN.

• Valeur de retour

  La valeur de retour est du même type que colname. Les règles suivantes s'appliquent :

  • Si la valeur de colname est NULL, la ligne est exclue du calcul.

  • Si colname est de type BOOLEAN, il ne peut pas être utilisé dans les calculs.

• Exemples

  • Exemple 1 : calculez le salaire (sal) le plus bas de tous les employés. Exemple d'instruction :

    select min(sal) from emp;

    Le résultat suivant est renvoyé :

    +------------+
    | _c0        |
    +------------+
    | 800        |
    +------------+

  • Exemple 2 : utilisez cette fonction avec group by pour regrouper tous les employés par département (deptno) et calculer le salaire (sal) le plus bas dans chaque département. Exemple de commande :

    select deptno, min(sal) from emp group by deptno;

    Le résultat suivant est renvoyé :

    +------------+------------+
    | deptno     | _c1        |
    +------------+------------+
    | 10         | 1300       |
    | 20         | 800        |
    | 30         | 950        |
    +------------+------------+

MIN_BY

• Syntaxe

  min_by(<valueToReturn>,<valueToMinimize>)

• description

  Remarque

  La fonction MIN_BY offre la même fonctionnalité que la fonction ARG_MIN. Toutefois, l'ordre des paramètres diffère entre les deux fonctions. La fonction MIN_BY a été introduite dans MaxCompute afin de maintenir la compatibilité avec la syntaxe open source.

  Cette fonction identifie la ligne contenant la valeur minimale de valueToMinimize et renvoie la valeur de valueToReturn correspondante dans cette ligne. Il s'agit d'une fonction supplémentaire de MaxCompute V2.0.

• description des paramètres

  • valueToMinimize : obligatoire. Valeur de n'importe quel type de données.

  • valueToReturn : obligatoire. Valeur de n'importe quel type de données.

• Valeur de retour.

  Le type de données de la valeur renvoyée est identique à celui du paramètre valueToReturn. Si plusieurs lignes contiennent la plus petite valeur de valueToMinimize, la valeur de valueToReturn de l'une de ces lignes est renvoyée de manière aléatoire. Si la valeur de valueToMinimize est null, la ligne contenant cette valeur n'est pas prise en compte dans le calcul.

• Exemples

  • Exemple 1 : Renvoyez le nom de l'employé ayant le salaire le plus bas. Exemple d'instruction :

     select min_by(ename,sal) from emp;

    Le résultat suivant est renvoyé :

    +------------+
    | _c0        |
    +------------+
    | SMITH      |
    +------------+

  • Exemple 2 : Utilisez cette fonction avec group by pour regrouper tous les employés par département (deptno) et renvoyer le nom de l'employé ayant le salaire le plus bas dans chaque groupe. Exemple de commande :

    select deptno, min_by(ename,sal) from emp group by deptno;

    Le résultat suivant est renvoyé :

    +------------+------------+
    | deptno     | _c1        |
    +------------+------------+
    | 10         | MILLER     |
    | 20         | SMITH      |
    | 30         | JAMES      |
    +------------+------------+

MULTIMAP_AGG

• Déclaration de la fonction

  map<K, array<V>> multimap_agg(K a, V b);

• description

  Renvoie une map créée à partir de a et b. Le paramètre a constitue la clé de la map. Le paramètre b sert à créer un tableau, qui est utilisé comme valeur associée à la clé dans la map. Si la clé de la map est null, elle est ignorée.

• Paramètres

  • a : champ d'entrée utilisé comme clé dans la map.

  • b : champ d'entrée utilisé comme valeur dans la map. Les champs correspondant à une même clé sont regroupés dans un même tableau et servent de valeurs dans la map.

• Valeur de retour.

  Une nouvelle map est renvoyée.

• Exemples

  select multimap_agg(a, b) from
          values (1L, 'apple'), (2L, 'hi'), (null, 'good'), (1L, 'pie') t(a, b);

  Le résultat suivant est renvoyé :

  +----------------------------------+
  | _c0                              |
  +----------------------------------+
  | {"2":["hi"],"1":["apple","pie"]} |
  +----------------------------------+

NUMERIC_HISTOGRAM

• Syntaxe

  map<double key, double value> numeric_histogram(bigint <buckets>,
                                                  double <colname>
                                                  [, double <weight>])
                      

• description

  Renvoie un histogramme approximatif basé sur une colonne spécifiée. Il s'agit d'une fonction supplémentaire de MaxCompute V2.0.

• description des paramètres

  • buckets : obligatoire. Valeur de type BIGINT. Ce paramètre spécifie le nombre maximal de compartiments (buckets) dans la colonne dont l'histogramme approximatif est renvoyé.

  • colname : obligatoire. Valeur de type DOUBLE. Ce paramètre spécifie les colonnes dont les histogrammes approximatifs doivent être calculés.

  • weight : facultatif. Valeur de pondération des données de chaque ligne. La valeur est de type DOUBLE.

• Valeur de retour.

  Renvoie une valeur de type map<double key, double value>. Dans la valeur renvoyée, la clé représente la coordonnée sur l'axe x de l'histogramme approximatif, et la valeur représente la hauteur approximative sur l'axe y. Les règles suivantes s'appliquent :

  • Si la valeur de buckets est null, la valeur null est renvoyée.

  • Si la valeur de colname est null, la ligne contenant cette valeur n'est pas prise en compte dans le calcul.

• Exemples

  • Renvoyez un histogramme approximatif de la colonne sal. Exemple d'instruction :

    select numeric_histogram(5, sal) from emp;

    Le résultat suivant est renvoyé :

    +------------+
    | _c0        |
    +------------+
    | {"1328.5714285714287":7.0,"2450.0":2.0,"5000.0":2.0,"875.0":2.0,"2956.25":4.0} |
    +------------+

  • Calculez un histogramme approximatif pour la colonne de salaire (sal), où deptno dans chaque ligne représente la pondération du département. Exemple de commande :

    select numeric_histogram(5, sal, deptno) from emp;

    Le résultat suivant est renvoyé :

    +------------+
    | _c0        |
    +------------+
    | {"2944.4444444444443":90.0,"2450.0":20.0,"5000.0":20.0,"890.0":50.0,"1350.0":160.0} |
    +------------+

PERCENTILE

• Syntaxe

  double percentile(bigint <colname>, <p>)
  -- Return multiple exact percentiles as an array.
  array percentile(bigint <colname>, array(<p1> [, <p2>...]))

• description

  Calcule un centile exact. Cette fonction convient aux petits volumes de données. Elle trie d'abord la colonne spécifiée par ordre croissant, puis extrait le p-ième centile exact. La valeur de p doit être comprise entre 0 et 1. Le calcul de percentile commence à l'index 0. Par exemple, si une colonne contient les valeurs 100, 200 et 300, leurs index sont respectivement 0, 1 et 2. Pour calculer le centile 0,3, le résultat de percentile est 2 × 0,3 = 0,6. Cela signifie que la valeur se situe entre l'index 0 et 1. Le résultat est 100 + (200 - 100) × 0.6 = 160. Il s'agit d'une fonction d'extension de MaxCompute V2.0.

• description

  • colname : obligatoire. Colonne de type BIGINT.

  • p : Obligatoire. Centile exact, qui doit se situer dans la plage [0.0, 1.0].

• Valeur de retour.

  Une valeur de type DOUBLE ou ARRAY est renvoyée.

• Exemples

  • Exemple 1 : La commande suivante calcule le centile 0,3 du salaire (sal) :

    select percentile(sal, 0.3) from emp;

    Le résultat suivant est renvoyé :

    +------------+
    | _c0        |
    +------------+
    | 1290.0     |
    +------------+

  • Exemple 2 : Utilisez cette fonction avec group by pour regrouper tous les employés par département (deptno) et calculer le centile 0,3 du salaire (sal) pour chaque groupe. Exemple de commande :

    select deptno, percentile(sal, 0.3) from emp group by deptno;

    Le résultat suivant est renvoyé :

    +------------+------------+
    | deptno     | _c1        |
    +------------+------------+
    | 10         | 1875.0     |
    | 20         | 1475.0     |
    | 30         | 1250.0     |
    +------------+------------+

  • Exemple 3 : Utilisez cette fonction avec group by pour regrouper tous les employés par département (deptno) et calculer les centiles 0,3, 0,5 et 0,8 du salaire (sal) pour chaque groupe. Exemple de commande :

    set odps.sql.type.system.odps2=true;
    select deptno, percentile(sal, array(0.3, 0.5, 0.8)) from emp group by deptno;

    Le résultat suivant est renvoyé :

    +------------+------------+
    | deptno     | _c1        |
    +------------+------------+
    | 10         | [1875.0,2450.0,5000.0] |
    | 20         | [1475.0,2975.0,3000.0] |
    | 30         | [1250.0,1375.0,1600.0] |
    +------------+------------+

PERCENTILE_APPROX

• Syntaxe

  double percentile_approx (double <colname>[, double <weight>], <p> [, <B>]))
  -- Return multiple approximate percentiles as an array.
  array<double> percentile_approx (double <colname>
                                   [, double <weight>],
                                   array(<p1> [, <p2>...])
                                   [, <B>])

• description

  Il s'agit d'une fonction d'extension pour MaxCompute V2.0. Le calcul de la fonction percentile_approx utilise une indexation à partir de 1. Pour calculer le p-ième centile d'une colonne contenant n entrées de données, la fonction percentile_approx trie d'abord la colonne par ordre croissant. Les données triées de la colonne sont traitées comme un tableau nommé arr, et le résultat de la fonction percentile_approx est noté res. L'index du centile est calculé selon la formule suivante : index = n * p.

  • Si index <= 1, alors res = arr[0].

  • Si index >= n - 1, alors res = arr[n-1].

  • Si 1 < index < n - 1, calculez diff = index + 0.5 - ceil(index) :

    Si la condition abs(diff) < 0,5 est remplie, res est calculé selon la formule suivante : res = arr[ceil(index) - 1].

    Si la condition abs(diff) = 0,5 est remplie, res est calculé selon la formule suivante : res = arr[index - 1] + (arr[index] - arr[index - 1]) × 0,5.

    La valeur de abs(diff) ne peut pas être supérieure à 0,5.

  Par exemple, si la colonne col contient les valeurs 100, 200, 300 et 400, leurs index sont respectivement 1, 2, 3 et 4. Alors :

  • percentile_approx(col, 0.25) = 100 (index = 1).

  • percentile_approx(col, 0.5) = 200 + (300 - 200) * 0.5 = 250 (index = 2).

  • percentile_approx(col, 0.75) = 400 (index = 3).

  Remarque

  Les fonctions percentile_approx et percentile diffèrent sur les points suivants :

  • Précision

    percentile_approx renvoie un résultat approximatif ; percentile renvoie un résultat exact.

  • Mémoire

    Pour des volumes de données importants, percentile peut échouer en raison des limites de mémoire ; ce problème ne se pose pas avec percentile_approx.

  • Algorithme

    percentile_approx est implémentée de manière cohérente avec la fonction Hive du même nom, mais utilise un algorithme différent de celui de percentile. Par conséquent, pour de très petits volumes de données, les deux fonctions peuvent renvoyer des résultats différents.

• description

  • colname : obligatoire. Nom de la colonne, qui doit être de type DOUBLE.

  • weight : facultatif. Valeur de pondération des données de chaque ligne. La valeur est de type DOUBLE.

  • p : obligatoire. Centile approximatif, qui doit se situer dans la plage [0.0, 1.0].

  • B : précision de la valeur renvoyée. Une précision plus élevée indique une valeur plus exacte. Si vous ne spécifiez pas ce paramètre, la valeur 10000 est utilisée par défaut.

• description de la valeur renvoyée.

  Une valeur de type DOUBLE ou ARRAY est renvoyée. La valeur renvoyée varie selon les règles suivantes :

  • Si la valeur de colname est null, la ligne contenant cette valeur n'est pas utilisée pour le calcul.

  • Si la valeur de p ou de B est null, une erreur est renvoyée.

• Exemples

  • Exemple 1 : la commande suivante calcule le 0,3e centile du salaire (sal) :

    select percentile_approx(sal, 0.3) from emp;

    Le résultat suivant est renvoyé :

    +------------+
    | _c0        |
    +------------+
    | 1252.5     |
    +------------+

  • Exemple 2 : utilisez cette fonction avec la clause group by pour regrouper tous les employés par département (deptno) et calculer le 0,3e centile du salaire (sal) pour chaque groupe. Exemple de commande :

    select deptno, percentile_approx(sal, 0.3) from emp group by deptno;

    Le résultat suivant est renvoyé :

    +------------+------------+
    | deptno     | _c1        |
    +------------+------------+
    | 10         | 1300.0     |
    | 20         | 950.0      |
    | 30         | 1070.0     |
    +------------+------------+

  • Exemple 3 : utilisez cette fonction avec la clause group by pour regrouper tous les employés par département (deptno) et calculer les 0,3e, 0,5e et 0,8e centiles du salaire (sal) pour chaque groupe. Exemple de commande :

    set odps.sql.type.system.odps2=true;
    select deptno, percentile_approx(sal, array(0.3, 0.5, 0.8), 1000) from emp group by deptno;

    Le résultat suivant est renvoyé :

    +------------+------------+
    | deptno     | _c1        |
    +------------+------------+
    | 10         | [1300.0,1875.0,3470.000000000001] |
    | 20         | [950.0,2037.5,2987.5] |
    | 30         | [1070.0,1250.0,1580.0] |
    +------------+------------+

  • Exemple 4 (avec pondération) : utilisez cette fonction avec la clause group by pour regrouper tous les employés par département (deptno) et calculer les 0,3e, 0,5e et 0,8e centiles du salaire (sal) pour chaque groupe. La colonne cnt de la table emp représente le nombre de personnes percevant ce salaire. Exemple de commande :

    select deptno, percentile_approx(sal, deptno, array(0.3, 0.5, 0.8), 1000)
      from emp group by deptno;

    Le résultat suivant est renvoyé :

    +------------+------------+
    | deptno     | _c1        |
    +------------+------------+
    | 10         | [1300.0,1875.0,3470.0] |
    | 20         | [950.0,2037.5,2987.5] |
    | 30         | [1070.0,1250.0,1580.0] |
    +------------+------------+

PERCENTILE_CONT

• Syntaxe

  -- Calculate the exact percentile
  PERCENTILE_CONT(<col_name>, DOUBLE <percentile>[, BOOLEAN <isIgnoreNull>])
  
  -- Calculate the exact percentile in a window
  PERCENTILE_CONT(<col_name>, DOUBLE <percentile>[, BOOLEAN <isIgnoreNull>]) OVER ([partition_clause] [orderby_clause])

• description

  Calcule le centile exact. Elle utilise un algorithme d'interpolation linéaire, trie la colonne spécifiée par ordre croissant et renvoie la valeur exacte au niveau du centile spécifié.

• Paramètres

  • col_name : obligatoire. Colonne de type DOUBLE ou DECIMAL.

  • centile : obligatoire. Centile à calculer. Constante DOUBLE comprise dans la plage [0, 1].

  • isIgnoreNull : facultatif. Indique s'il faut ignorer les valeurs NULL. Constante BOOLEAN. La valeur par défaut est TRUE. Si la valeur est définie sur FALSE, les valeurs NULL sont traitées comme la valeur minimale lors du tri.

  • partition_clause et orderby_clause : pour plus d'informations, consultez la section windowing_definition..

• Valeur renvoyée

  Renvoie la valeur du centile calculée sous forme de DOUBLE.

• Exemples

  • Exemple 1 : ignorez les valeurs NULL et calculez le centile exact dans une fenêtre.

    SELECT
      PERCENTILE_CONT(x, 0) OVER() AS min,
      PERCENTILE_CONT(x, 0.01) OVER() AS percentile1,
      PERCENTILE_CONT(x, 0.5) OVER() AS median,
      PERCENTILE_CONT(x, 0.9) OVER() AS percentile90,
      PERCENTILE_CONT(x, 1) OVER() AS max
    FROM VALUES(0D),(3D),(NULL),(1D),(2D) AS tbl(x) LIMIT 1;
    
    -- Return result
    +------------+-------------+------------+--------------+------------+
    | min        | percentile1 | median     | percentile90 | max        | 
    +------------+-------------+------------+--------------+------------+
    | 0.0        | 0.03        | 1.5        | 2.7          | 3.0        | 
    +------------+-------------+------------+--------------+------------+

  • Exemple 2 : ne pas ignorer les valeurs NULL. Les valeurs NULL sont traitées comme la valeur minimale lors du tri. Calculez le centile exact dans une fenêtre.

    SELECT
      PERCENTILE_CONT(x, 0, false) OVER() AS min,
      PERCENTILE_CONT(x, 0.01, false) OVER() AS percentile1,
      PERCENTILE_CONT(x, 0.5, false) OVER() AS median,
      PERCENTILE_CONT(x, 0.9, false) OVER() AS percentile90,
      PERCENTILE_CONT(x, 1, false) OVER() AS max
    FROM VALUES(0D),(3D),(NULL),(1D),(2D) AS tbl(x) LIMIT 1;
    
    -- Return result
    +------------+-------------+------------+--------------+------------+
    | min        | percentile1 | median     | percentile90 | max        | 
    +------------+-------------+------------+--------------+------------+
    | NULL       | 0.0         | 1.0        | 2.6          | 3.0        | 
    +------------+-------------+------------+--------------+------------+

PERCENTILE_DISC

• Syntaxe

  -- Calculate a given percentile value
  PERCENTILE_DISC(<col_name>, DOUBLE <percentile>[, BOOLEAN <isIgnoreNull>])
  
  -- Calculate the percentile value in a window
  PERCENTILE_DISC(<col_name>, DOUBLE <percentile>[, BOOLEAN <isIgnoreNull>]) OVER ([partition_clause] [orderby_clause])

• Description

  Calcule une valeur de centile donnée. La fonction trie d'abord la colonne spécifiée par ordre croissant, puis renvoie la première valeur dont la distribution cumulative est supérieure ou égale au centile spécifié.

• Paramètres

  • col_name : obligatoire. Colonne de n'importe quel type de données triable.

  • percentile : obligatoire. Centile à calculer. Constante DOUBLE comprise dans l'intervalle [0, 1].

  • isIgnoreNull : facultatif. Indique s'il faut ignorer les valeurs NULL. Constante BOOLEAN. La valeur par défaut est TRUE. Si vous la définissez sur FALSE, les valeurs NULL sont traitées comme la valeur minimale lors du tri.

  • partition_clause et orderby_clause : pour plus d'informations, consultez windowing_definition.

• Valeur de retour

  Renvoie la valeur de centile calculée. Le type de données est identique à celui de la colonne col_name en entrée.

• Exemples

  • Exemple 1 : ignorez les valeurs NULL et calculez la valeur de centile dans une fenêtre.

    SELECT
      x,
      PERCENTILE_DISC(x, 0) OVER() AS min,
      PERCENTILE_DISC(x, 0.5) OVER() AS median,
      PERCENTILE_DISC(x, 1) OVER() AS max
    FROM VALUES('c'),(NULL),('b'),('a') AS tbl(x);
    
    -- Return result
    +------------+------------+------------+------------+
    | x          | min        | median     | max        | 
    +------------+------------+------------+------------+
    | c          | a          | b          | c          | 
    | NULL       | a          | b          | c          | 
    | b          | a          | b          | c          | 
    | a          | a          | b          | c          | 
    +------------+------------+------------+------------+

  • Exemple 2 : ne pas ignorer les valeurs NULL. Les valeurs NULL sont traitées comme la valeur minimale lors du tri. Calculez la valeur de centile dans une fenêtre.

    SELECT
      x,
      PERCENTILE_DISC(x, 0, false) OVER() AS min,
      PERCENTILE_DISC(x, 0.5, false) OVER() AS median,
      PERCENTILE_DISC(x, 1, false) OVER() AS max
    FROM VALUES('c'),(NULL),('b'),('a') AS tbl(x);
    
    -- Return result
    +------------+------------+------------+------------+
    | x          | min        | median     | max        | 
    +------------+------------+------------+------------+
    | c          | NULL       | a          | c          | 
    | NULL       | NULL       | a          | c          | 
    | b          | NULL       | a          | c          | 
    | a          | NULL       | a          | c          | 
    +------------+------------+------------+------------+

STDDEV

• Syntaxe

  double stddev(double <colname>)
  decimal stddev(decimal <colname>)

• Description

  Renvoie l'écart type de population de toutes les valeurs en entrée.

• Paramètres

  colname : obligatoire. Nom d'une colonne, qui peut être de type DOUBLE ou DECIMAL. Si la valeur en entrée est de type STRING ou BIGINT, elle est implicitement convertie en type DOUBLE avant le calcul.

• Valeur de retour

  Si la valeur de colname est null, la ligne contenant cette valeur n'est pas utilisée pour le calcul. Le tableau suivant décrit les correspondances entre les types de données des données en entrée et les valeurs de retour.

  Type d'entrée	Type de valeur de retour
  TINYINT	DOUBLE
  SMALLINT	DOUBLE
  INT	DOUBLE
  BIGINT	DOUBLE
  FLOAT	DOUBLE
  DOUBLE	DOUBLE
  DECIMAL	DECIMAL

• Exemples

  • Exemple 1 : calculez l'écart type de population des valeurs de salaire (sal) de tous les employés. Exemple d'instruction :

    select stddev(sal) from emp;

    Le résultat suivant est renvoyé :

    +------------+
    | _c0        |
    +------------+
    | 1262.7549932628976 |
    +------------+

  • Exemple 2 : utilisez cette fonction avec group by pour regrouper tous les employés par département (deptno) et calculer l'écart type de population du salaire (sal) pour chaque département. Exemple de commande :

    select deptno, stddev(sal) from emp group by deptno;

    Le résultat suivant est renvoyé :

    +------------+------------+
    | deptno     | _c1        |
    +------------+------------+
    | 10         | 1546.1421524412158 |
    | 20         | 1004.7387720198718 |
    | 30         | 610.1001739241043 |
    +------------+------------+

STDDEV_SAMP

• Syntaxe

  double stddev_samp(double <colname>)
  decimal stddev_samp(decimal <colname>)

• Description

  Renvoie l'écart type d'échantillon de toutes les valeurs en entrée.

• Paramètres

  colname : obligatoire. Nom d'une colonne, qui peut être de type DOUBLE ou DECIMAL. Si la valeur en entrée est de type STRING ou BIGINT, elle est implicitement convertie en type DOUBLE avant le calcul.

• Valeur de retour

  Si la valeur de colname est null, la ligne contenant cette valeur n'est pas utilisée pour le calcul. Le tableau suivant décrit les correspondances entre les types de données des données en entrée et les valeurs de retour.

  Type d'entrée	Type de valeur de retour
  TINYINT	DOUBLE
  SMALLINT	DOUBLE
  INT	DOUBLE
  BIGINT	DOUBLE
  FLOAT	DOUBLE
  DOUBLE	DOUBLE
  DECIMAL	DECIMAL

• Exemples

  • Exemple 1 : calculez l'écart type d'échantillon des valeurs de salaire (sal) de tous les employés. Exemple d'instruction :

    select stddev_samp(sal) from emp;

    Le résultat suivant est renvoyé :

    +------------+
    | _c0        |
    +------------+
    | 1301.6180541247609 |
    +------------+

  • Exemple 2 : utilisez cette fonction avec group by pour regrouper tous les employés par département (deptno) et calculer l'écart type d'échantillon du salaire (sal) pour chaque département. Exemple de commande :

    select deptno, stddev_samp(sal) from emp group by deptno;

    Le résultat suivant est renvoyé :

    +------------+------------+
    | deptno     | _c1        |
    +------------+------------+
    | 10         | 1693.7138680032901 |
    | 20         | 1123.3320969330487 |
    | 30         | 668.3312551921141 |
    +------------+------------+

SUM

• Syntaxe

  DECIMAL|DOUBLE|BIGINT  sum(<colname>)

• Description

  Calcule la somme.

• Paramètres

  colname : obligatoire. Les valeurs de colonne prennent en charge tous les types de données et peuvent être converties au type DOUBLE avant le calcul. Il s'agit du nom d'une colonne, qui peut être de type DOUBLE, DECIMAL ou BIGINT. Si la valeur d'entrée est de type STRING, elle est implicitement convertie au type DOUBLE avant le calcul.

• Cette section décrit la valeur de retour.

  Si la valeur de colname est nulle, la ligne contenant cette valeur n'est pas utilisée pour le calcul. Le tableau suivant décrit les correspondances entre les types de données des données d'entrée et les valeurs de retour.

  Type d'entrée	Type de valeur de retour
  TINYINT	BIGINT
  SMALLINT	BIGINT
  INT	BIGINT
  BIGINT	BIGINT
  FLOAT	DOUBLE
  DOUBLE	DOUBLE
  DECIMAL	DECIMAL

• Exemples

  • Exemple 1 : calculez la somme des salaires (sal) de tous les employés. Exemple d'instruction :

    select sum(sal) from emp;

    Le résultat suivant est renvoyé :

    +------------+
    | _c0        |
    +------------+
    | 37775      |
    +------------+

  • Exemple 2 : utilisez cette fonction avec group by pour regrouper tous les employés par département (deptno) et calculer la somme des salaires (sal) pour chaque département. Exemple de commande :

    select deptno, sum(sal) from emp group by deptno;

    Le résultat suivant est renvoyé :

    +------------+------------+
    | deptno     | _c1        |
    +------------+------------+
    | 10         | 17500      |
    | 20         | 10875      |
    | 30         | 9400       |
    +------------+------------+

VAR_SAMP

• Syntaxe

  double var_samp(<colname>)

• Description

  Calcule la variance d'échantillon d'une colonne numérique spécifiée. Cette fonction est une fonction supplémentaire de MaxCompute V2.0.

• Paramètres

  colname : obligatoire. Une colonne de type de données numérique. Si la colonne spécifiée n'est pas une colonne numérique, une valeur nulle est renvoyée.

• Valeur de retour

  Une valeur de type DOUBLE est renvoyée.

• Exemples

  • Exemple 1 : calculez la variance d'échantillon des salaires (sal) de tous les employés. Exemple d'instruction :

    select var_samp(sal) from emp;

    Le résultat suivant est renvoyé :

    +------------+
    | _c0        |
    +------------+
    | 1694209.5588235292 |
    +------------+

  • Exemple 2 : utilisez cette fonction avec group by pour regrouper tous les employés par département (deptno) et calculer la variance d'échantillon des salaires (sal) pour chaque groupe. Exemple de commande :

    select deptno, var_samp(sal) from emp group by deptno;

    Le résultat suivant est renvoyé :

    +------------+------------+
    | deptno     | _c1        |
    +------------+------------+
    | 10         | 2868666.666666667 |
    | 20         | 1261875.0  |
    | 30         | 446666.6666666667 |
    +------------+------------+

VARIANCE/VAR_POP

• Syntaxe

  double variance(<colname>)
  double var_pop(<colname>)

• Cette rubrique décrit la commande.

  Calcule la variance d'une colonne numérique spécifiée.

• Description de la métrique

  colname : obligatoire. Une colonne de type de données numérique. Si la colonne spécifiée n'est pas une colonne numérique, une valeur nulle est renvoyée. Cette fonction est une fonction supplémentaire de MaxCompute V2.0.

• Valeur de retour

  Une valeur de type DOUBLE est renvoyée.

• Exemples

  • Exemple 1 : calculez la variance des salaires (sal) de tous les employés. Exemple d'instruction :

    select variance(sal) from emp;
    -- This is equivalent to the following statement.
    select var_pop(sal) from emp;

    Le résultat suivant est renvoyé :

    +------------+
    | _c0        |
    +------------+
    | 1594550.1730103805 |
    +------------+

  • Exemple 2 : utilisez cette fonction avec group by pour regrouper tous les employés par département (deptno) et calculer la variance des salaires (sal) pour chaque groupe. Exemple de commande :

    select deptno, variance(sal) from emp group by deptno;
    -- This is equivalent to the following statement.
    select deptno, var_pop(sal) from emp group by deptno;

    Le résultat suivant est renvoyé :

    +------------+------------+
    | deptno     | _c1        |
    +------------+------------+
    | 10         | 2390555.5555555555 |
    | 20         | 1009500.0  |
    | 30         | 372222.22222222225 |
    +------------+------------+

WM_CONCAT

• Syntaxe

  string wm_concat(string <separator>, string <colname>)

• Description de la commande.

  Concatène les valeurs dans colname à l'aide d'un délimiteur spécifié par separator.

• Description.

  • separator : obligatoire. Le délimiteur, qui est une constante de type STRING.

  • colname : obligatoire. Une valeur de type STRING. Si la valeur d'entrée est de type BIGINT, DOUBLE ou DATETIME, elle est implicitement convertie au type STRING avant le calcul.

• Valeur de retour (lors de l'utilisation de group by pour le regroupement, les valeurs de retour au sein d'un groupe ne sont pas triées)

  Une valeur de type STRING est renvoyée. La valeur de retour varie selon les règles suivantes :

  • Si la valeur de separator n'est pas une constante de type STRING, une erreur est renvoyée.

  • Si la valeur de colname n'est pas de type STRING, BIGINT, DOUBLE ou DATETIME, une erreur est renvoyée.

  • Si la valeur de colname est nulle, la ligne contenant cette valeur n'est pas utilisée pour le calcul.

  Remarque

  Dans l'instruction select wm_concat(',', name) from table_name;, si table_name est un ensemble vide, l'instruction renvoie NULL.

• Exemples

  • Exemple 1 : concaténez les noms (ename) de tous les employés. Exemple d'instruction :

    select wm_concat(',', ename) from emp;

    Le résultat suivant est renvoyé :

    +------------+
    | _c0        |
    +------------+
    | SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER,JACCKA,WELAN,TEBAGE |
    +------------+

  • Exemple 2 : utilisez cette fonction avec group by pour regrouper tous les employés par département (deptno) et concaténer les noms (ename) des employés du même groupe. Exemple de commande :

    select deptno, wm_concat(',', ename) from emp group by deptno order by deptno;

    Le résultat suivant est renvoyé :

    +------------+------------+
    | deptno     | _c1        |
    +------------+------------+
    | 10         | CLARK,KING,MILLER,JACCKA,WELAN,TEBAGE |
    | 20         | SMITH,JONES,SCOTT,ADAMS,FORD |
    | 30         | ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES |
    +------------+------------+

  • Exemple 3 : utilisez cette fonction avec group by pour regrouper tous les employés par département (deptno) et concaténer les valeurs de salaire (sal) distinctes des employés du même groupe. Exemple de commande :

    select deptno, wm_concat(distinct ',', sal) from emp group by deptno order by deptno;

    Le résultat suivant est renvoyé :

    +------------+------------+
    | deptno     | _c1        |
    +------------+------------+
    | 10         | 1300,2450,5000 |
    | 20         | 1100,2975,3000,800 |
    | 30         | 1250,1500,1600,2850,950 |
    +------------+------------+

  • Exemple 4 : utilisez cette fonction avec group by et order by pour regrouper tous les employés par département (deptno), trier leurs salaires (sal) et les concaténer. Exemple de commande :

    select deptno, wm_concat(',',sal) within group(order by sal) from emp group by deptno order by deptno;

    Le résultat suivant est renvoyé :

    +------------+------------+
    |deptno|_c1|
    +------------+------------+
    |10|1300,1300,2450,2450,5000,5000|
    |20|800,1100,2975,3000,3000|
    |30|950,1250,1250,1500,1600,2850|
    +------------+------------+