Comment transposer les lignes de l'historique des e-mails des utilisateurs en colonnes dans PostgreSQL ?

Restructuration de l'historique des e-mails des utilisateurs : lignes en colonnes dans PostgreSQL

Imaginez un tableau stockant plusieurs adresses e-mail pour chaque utilisateur. Cet article montre comment transformer efficacement ces données basées sur des lignes en un format de colonnes, en alignant spécifiquement les adresses e-mail les plus récentes sous forme de colonnes distinctes. Nous y parviendrons en utilisant le module tablefunc de PostgreSQL et la puissante fonction crosstab().

La crosstab()Solution

La fonction crosstab() du module tablefunc est parfaitement adaptée pour transposer des lignes en colonnes par utilisateur. La requête suivante illustre son application :

SELECT *
FROM crosstab(
   $$SELECT user_id, user_name, rn, email_address
     FROM  (
        SELECT u.user_id, u.user_name, e.email_address
             , row_number() OVER (PARTITION BY u.user_id
                            ORDER BY e.creation_date DESC NULLS LAST) AS rn
        FROM   usr u
        LEFT   JOIN email_tbl e USING (user_id)
        ) sub
     WHERE  rn <= 3
   $$,
   $$VALUES (1),(2),(3)$$
) AS ct (user_id INT, user_name TEXT, email_1 TEXT, email_2 TEXT, email_3 TEXT);

Comprendre les composants de requête

• Le premier argument de crosstab() est une sous-requête. Cette sous-requête récupère les ID utilisateur, les noms d'utilisateur, un numéro de ligne (rn) et les adresses e-mail.
• row_number() OVER (PARTITION BY u.user_id ORDER BY e.creation_date DESC NULLS LAST) attribue un classement unique (rn) à chaque adresse e-mail pour chaque utilisateur, classé par date de création (la plus récente en premier).
• La clause WHERE (WHERE rn <= 3) limite les résultats aux trois adresses e-mail les plus récentes par utilisateur.
• Le deuxième argument de crosstab() ($$VALUES (1),(2),(3)$$) spécifie les valeurs potentielles pour les nouvelles colonnes (représentant les classements des adresses e-mail).

Relever les défis potentiels

L'absence de clé distincte dans la table d'origine pour chaque adresse e-mail a nécessité l'utilisation de row_number() pour générer une clé de substitution. De plus, le nombre variable d'e-mails par utilisateur nécessitait de limiter la sortie à trois colonnes dans l'instruction SELECT externe et de définir la plage de valeurs clés possibles dans le deuxième paramètre crosstab().

Cette approche transforme efficacement les dernières adresses e-mail par utilisateur en colonnes distinctes, présentant une vue plus claire et plus organisée de l'historique des e-mails des utilisateurs.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!