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 type de données MaxCompute V2.0, vous devez exécuter l'instruction SET pour activer cette édition. 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 Opérations sur les projets. Pour plus d'informations sur les précautions à prendre lors de l'activation des types de données au niveau du projet, consultez Versions des types de données.

• 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 lorsque vous utilisez cette expression :

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

  • 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 plus de 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 lorsque vous utilisez cette expression :

  • Seules les fonctions d'agrégation intégrées prennent en charge cette expression. Les UDAFs 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 filtrage

• Limites

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

  • count(*) ne peut pas être utilisée avec des expressions de filtrage. 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 filtrage. Si vous spécifiez une condition de filtrage, seules les lignes qui satisfont à cette condition sont transmises à la fonction d'agrégation concernée pour le traitement des données.

• Paramètres.

  • 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 conformément à la description de la fonction choisie.

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

• Valeur de retour.

  Pour plus d'informations, reportez-vous à la description de la valeur de retour de 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

  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électionner 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 il faut supprimer les doublons.

• 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 calculer 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 contenant la valeur maximale de valueToMaximize et renvoie la valeur de valueToReturn présente 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 valeur maximale de valueToMaximize, la valeur de valueToReturn de l'une de ces 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 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 renvoyer 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 contenant la valeur minimale de valueToMinimize et renvoie la valeur de valueToReturn présente dans cette ligne. Cette fonction est une fonction supplémentaire de MaxCompute V2.0.

• description

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

  • valueToReturn : obligatoire. Une 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 valeur minimale 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 utilisée pour le calcul.

• Exemples

  • Exemple 1 : renvoyez le nom de l'employé ayant 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 renvoyer 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

  Vous pouvez calculer la valeur moyenne.

• Paramètres.

  colname : obligatoire. Les valeurs de colonne supportent tous les types de données et peuvent être converties au 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 la valeur 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 effectuant une opération ET binaire (bitwise AND).

• Paramètres.

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

• Cette section décrit la valeur de retour.

  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

• Il s'agit d'une déclaration de fonction.

  bigint bitwise_or_agg(bigint value)

• description

  Agrège les valeurs d'entrée en effectuant une opération OU binaire (bitwise OR).

• Descriptions des métriques

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

• Description de la valeur de retour.

  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 effectuant une opération XOR binaire (bitwise XOR).

• Descriptions des métriques

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

• Valeur de retour

  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 effectue 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 de retour

  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

  Agrège les valeurs de la colonne spécifiée par colname dans un tableau et effectue 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 vraie, la fonction renvoie true. Si toutes les valeurs sont fausses, 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

  Une valeur de type ARRAY est renvoyée. 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 salaires (sal) de tous les employés dans un tableau. Exemple de commande :

    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.

• Paramètres

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

• Valeur renvoyée

  Une valeur de type ARRAY est renvoyée. 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 salaires (sal) de tous les employés dans un tableau ne contenant que des valeurs distinctes. Exemple de commande :

    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

  Une valeur de type DOUBLE est renvoyée. 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 expr est True.

• Paramètres

  expr : obligatoire. Expression BOOLEAN.

• Valeur renvoyée

  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.

• Descriptions des paramètres

  colname1 et colname2 : obligatoires. 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 de commande :

    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 correspondent aux valeurs d'entrée. Chaque valeur du map indique le 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 fonction

  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 représente la clé dans le map. b représente la valeur associée à la clé dans le map. Si la clé du map est null, elle 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 correspond à 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 correspond à 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, elle est convertie en 0.

  Remarque

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

• descriptions des paramètres.

  input : les maps d'entrée.

• description de la valeur de retour.

  Un nouveau map est renvoyé.

  Remarque

  Les valeurs du 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.

• descriptions 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 au type 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, la valeur n'est pas utilisée pour le calcul.

• Exemples

  • Exemple 1 : calculez le salaire le plus élevé (sal) 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 le plus élevé (sal) 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 est introduite dans MaxCompute pour maintenir la compatibilité avec la syntaxe open source.

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

• descriptions 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 au type de données 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 le plus bas (sal) 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 le plus bas (sal) 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, les fonctions diffèrent par l'ordre des paramètres. La fonction MIN_BY est introduite dans MaxCompute pour maintenir la compatibilité avec la syntaxe open source.

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

• descriptions 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.

• description de la valeur de retour.

  Le type de données de la valeur de retour est identique au type de données 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é 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 carte (map) créée à partir des paramètres a et b. Le paramètre a sert de clé dans la carte. Le paramètre b est utilisé pour créer un tableau, qui constitue la valeur associée à la clé dans la carte. Si une clé de la carte est nulle, elle est ignorée.

• Paramètres

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

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

• Valeur de retour.

  Une nouvelle carte 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. Cette fonction est 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 de retour, 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 nulle, la valeur renvoyée est nulle.

  • Si la valeur de colname est nulle, la ligne contenant cette valeur n'est pas utilisée pour 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 centile exact correspondant au paramètre p. 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. Cette fonction est une fonction d'extension de MaxCompute V2.0.

• description

  • colname : obligatoire. Colonne de type BIGINT.

  • p : Obligatoire. Centile exact, qui doit être compris 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 percentile_approx est basé sur un index commençant à 1. Pour calculer le centile p d'une colonne comportant 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 percentile_approx est res. L'index du centile est calculé comme suit : 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

  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 les grands volumes de données, percentile peut échouer en raison des limites de mémoire ; percentile_approx ne présente pas ce problème.

  • Algorithme

    percentile_approx est implémenté 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 d'une colonne, qui peut ê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 être compris dans la plage [0.0, 1.0].

  • B : précision de la valeur de retour. 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.

• Valeur de retour.

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

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

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

• Exemples

  • Exemple 1 : La commande suivante calcule le centile 0,3 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 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_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 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_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 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. La colonne cnt de la table emp représente le nombre de personnes ayant 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 percentile spécifié.

• Paramètres

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

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

  • isIgnoreNull : Facultatif. Indique s'il faut ignorer les valeurs NULL. Constante BOOLÉENNE. 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 windowing_definition..

• Valeur de retour

  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 : N'ignorez pas 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. Elle 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 avec n'importe quel type de données triable.

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

  • isIgnoreNull : Facultatif. Indique s'il faut ignorer les valeurs NULL. Constante BOOLÉENNE. 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 windowing_definition.

• Valeur de retour

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

• Exemples

  • Exemple 1 : Ignorez les valeurs NULL et calculez la valeur du 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 : N'ignorez pas les valeurs NULL. Les valeurs NULL sont traitées comme la valeur minimale lors du tri. Calculez la valeur du 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 la population pour toutes les valeurs d'entrée.

• Paramètres

  colname : obligatoire. Nom de la 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 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 types de valeurs renvoyées.

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

• Exemples

  • Exemple 1 : calculez l'écart type de la population des salaires (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 la population des salaires (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 de l'échantillon pour toutes les valeurs d'entrée.

• Paramètres

  colname : obligatoire. Nom de la 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 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 types de valeurs renvoyées.

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

• Exemples

  • Exemple 1 : calculez l'écart type de l'échantillon des salaires (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 de l'échantillon des salaires (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 en type DOUBLE avant le calcul. Nom de la colonne, qui peut être de type DOUBLE, DECIMAL ou BIGINT. Si la valeur d'entrée est de type STRING, elle est implicitement convertie en type DOUBLE avant le calcul.

• Cette section décrit la 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 types de valeurs renvoyées.

  Type d'entrée	Type de valeur renvoyée
  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 de l'échantillon d'une colonne numérique spécifiée. Cette fonction est une fonction supplémentaire de MaxCompute V2.0.

• Paramètres

  colname : obligatoire. Colonne 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.

• Valeur renvoyée

  Une valeur de type DOUBLE est renvoyée.

• Exemples

  • Exemple 1 : calculez la variance de l'é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 de l'é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. Colonne 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. Cette fonction est une fonction supplémentaire de MaxCompute V2.0.

• Valeur renvoyée

  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 de colname à l'aide d'un délimiteur spécifié par separator.

• Description.

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

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

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

  Une valeur de type STRING est renvoyée. La valeur renvoyée 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 null, 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|
    +------------+------------+