398 votes

Comment concaténer les chaînes d'un champ de type chaîne dans une requête 'group by' de PostgreSQL ?

Je cherche un moyen de concaténer les chaînes d'un champ dans une requête de type group by. Par exemple, j'ai une table :

ID   COMPANY_ID   EMPLOYEE
1    1            Anna
2    1            Bill
3    2            Carol
4    2            Dave

et je voulais regrouper par numéro de société pour obtenir quelque chose comme.. :

COMPANY_ID   EMPLOYEE
1            Anna, Bill
2            Carol, Dave

Il y a une fonction intégrée dans mySQL pour faire ceci groupe_concat

1 votes

La réponse de Markus Döring est techniquement meilleure.

0 votes

@pstanton, la réponse de Döring n'est meilleure que pour les versions 8.4 et inférieures.

0 votes

Cette question semble mieux convenir à dba.stackexchange.com .

604voto

Neall Points 12075

PostgreSQL 9.0 ou plus récent :

Les Postgres modernes (depuis 2010) disposent de la fonction string_agg(expression, delimiter) qui fera exactement ce que l'auteur de la demande recherchait :

SELECT company_id, string_agg(employee, ', ')
FROM mytable
GROUP BY company_id;

Postgres 9 a également ajouté la possibilité de spécifier un fichier de type ORDER BY clause dans toute expression agrégée sinon vous devez ordonner tous vos résultats ou faire face à un ordre indéfini. Vous pouvez donc maintenant écrire :

SELECT company_id, string_agg(employee, ', ' ORDER BY employee)
FROM mytable
GROUP BY company_id;

PostgreSQL 8.4.x :

Introduction de PostgreSQL 8.4 (en 2009) la fonction agrégée array_agg(expression) qui rassemble les valeurs dans un tableau. Ensuite, array_to_string() peut être utilisé pour obtenir le résultat souhaité :

SELECT company_id, array_to_string(array_agg(employee), ', ')
FROM mytable
GROUP BY company_id;

PostgreSQL 8.3.x et plus ancien :

Lorsque cette question a été posée à l'origine, il n'existait pas de fonction d'agrégation intégrée permettant de concaténer des chaînes de caractères. L'implémentation personnalisée la plus simple ( suggéré par Vajda Gabo dans ce message de liste de diffusion parmi beaucoup d'autres) est d'utiliser la fonction intégrée textcat (qui se trouve derrière la fonction || opérateur) :

CREATE AGGREGATE textcat_all(
  basetype    = text,
  sfunc       = textcat,
  stype       = text,
  initcond    = ''
);

Voici le CREATE AGGREGATE documentation.

Cela colle simplement toutes les cordes ensemble, sans séparateur. Afin d'obtenir un ", " inséré entre elles sans l'avoir à la fin, vous pouvez créer votre propre fonction de concaténation et la substituer à la fonction "textcat" ci-dessus. En voici une que j'ai créée et testée sous 8.3.12 :

CREATE FUNCTION commacat(acc text, instr text) RETURNS text AS $$
  BEGIN
    IF acc IS NULL OR acc = '' THEN
      RETURN instr;
    ELSE
      RETURN acc || ', ' || instr;
    END IF;
  END;
$$ LANGUAGE plpgsql;

Cette version affichera une virgule même si la valeur de la ligne est nulle ou vide, de sorte que vous obtenez un résultat comme celui-ci :

a, b, c, , e, , g

Si vous préférez supprimer les virgules supplémentaires pour l'affichage :

a, b, c, e, g

Ajoutez ensuite un ELSIF à la fonction comme ceci :

CREATE FUNCTION commacat_ignore_nulls(acc text, instr text) RETURNS text AS $$
  BEGIN
    IF acc IS NULL OR acc = '' THEN
      RETURN instr;
    ELSIF instr IS NULL OR instr = '' THEN
      RETURN acc;
    ELSE
      RETURN acc || ', ' || instr;
    END IF;
  END;
$$ LANGUAGE plpgsql;

1 votes

J'ai dû transformer varchar en texte (dernière version stable de pgsql) mais c'est génial !

1 votes

Vous pouvez écrire la fonction en SQL uniquement, ce qui est plus facile pour l'installation (plpgsql doit être installé par le superutilisateur). Voir mon post pour un exemple.

11 votes

"Il n'existe pas de fonction d'agrégation intégrée pour concaténer des chaînes de caractères. array_to_string(array_agg(employee), ',') ?

106voto

Markus Döring Points 551

Pourquoi ne pas utiliser les fonctions de tableau intégrées de Postgres ? Au moins sur la version 8.4, cela fonctionne dès le départ :

SELECT company_id, array_to_string(array_agg(employee), ',')
FROM mytable
GROUP BY company_id;

0 votes

Malheureusement, cela ne fonctionne pas pour nous sur Greenplum (v8.2). +1 tout de même

0 votes

Cela fonctionne bien pour moi sur Greenplum 4.3.4.1 (construit sur PostgreSQL 8.2.15).

20voto

dirbacke Points 440

À partir de PostgreSQL 9.0, vous pouvez utiliser la fonction d'agrégation appelée string_agg . Votre nouveau SQL devrait ressembler à quelque chose comme ceci :

SELECT company_id, string_agg(employee, ', ')
FROM mytable
GROUP BY company_id;

14voto

Guy C Points 930

Je ne revendique aucun mérite pour la réponse, car je l'ai trouvée après quelques recherches :

Ce que je ne savais pas, c'est que PostgreSQL vous permet de définir vos propres fonctions d'agrégation avec les fonctions suivantes CRÉER UN AGRÉGAT

Ce poste sur la liste PostgreSQL montre combien il est trivial de créer une fonction pour faire ce qui est nécessaire :

CREATE AGGREGATE textcat_all(
  basetype    = text,
  sfunc       = textcat,
  stype       = text,
  initcond    = ''
);

SELECT company_id, textcat_all(employee || ', ')
FROM mytable
GROUP BY company_id;

7voto

bortzmeyer Points 12246

Comme nous l'avons déjà mentionné, créer votre propre fonction d'agrégation est la bonne chose à faire. Voici ma fonction d'agrégation de concaténation (vous pouvez retrouver détails en français ) :

CREATE OR REPLACE FUNCTION concat2(text, text) RETURNS text AS '
    SELECT CASE WHEN $1 IS NULL OR $1 = \'\' THEN $2
            WHEN $2 IS NULL OR $2 = \'\' THEN $1
            ELSE $1 || \' / \' || $2
            END; 
'
 LANGUAGE SQL;

CREATE AGGREGATE concatenate (
  sfunc = concat2,
  basetype = text,
  stype = text,
  initcond = ''

) ;

Et ensuite l'utiliser comme :

SELECT company_id, concatenate(employee) AS employees FROM ...

Prograide.com

Prograide est une communauté de développeurs qui cherche à élargir la connaissance de la programmation au-delà de l'anglais.
Pour cela nous avons les plus grands doutes résolus en français et vous pouvez aussi poser vos propres questions ou résoudre celles des autres.

Powered by:

X