Wie transponiere ich Benutzer-E-Mail-Verlaufszeilen in Spalten in PostgreSQL?

Umstrukturierung des Benutzer-E-Mail-Verlaufs: Zeilen zu Spalten in PostgreSQL

Stellen Sie sich eine Tabelle vor, in der mehrere E-Mail-Adressen für jeden Benutzer gespeichert sind. In diesem Artikel wird gezeigt, wie Sie diese zeilenbasierten Daten effizient in ein Spaltenformat umwandeln und dabei insbesondere die neuesten E-Mail-Adressen als separate Spalten ausrichten. Dies erreichen wir mit dem tablefunc-Modul von PostgreSQL und der leistungsstarken crosstab()-Funktion.

Die crosstab()Lösung

Die crosstab()-Funktion innerhalb des tablefunc-Moduls eignet sich perfekt zum Transponieren von Zeilen in Spalten auf Benutzerbasis. Die folgende Abfrage veranschaulicht ihre Anwendung:

<code class="language-sql">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);</code>

Die Abfragekomponenten verstehen

• Das erste Argument für crosstab() ist eine Unterabfrage. Diese Unterabfrage ruft Benutzer-IDs, Benutzernamen, eine Zeilennummer (rn) und E-Mail-Adressen ab.
• row_number() OVER (PARTITION BY u.user_id ORDER BY e.creation_date DESC NULLS LAST) weist jeder E-Mail-Adresse für jeden Benutzer einen eindeutigen Rang (rn) zu, sortiert nach Erstellungsdatum (neueste zuerst).
• Die WHERE-Klausel (WHERE rn <= 3) beschränkt die Ergebnisse auf die drei aktuellsten E-Mail-Adressen pro Benutzer.
• Das zweite Argument für crosstab() ($$VALUES (1),(2),(3)$$) gibt die potenziellen Werte für die neuen Spalten an (die die Ränge der E-Mail-Adressen darstellen).

Bewältigung potenzieller Herausforderungen

Da in der Originaltabelle kein eindeutiger Schlüssel für jede E-Mail-Adresse vorhanden war, musste row_number() verwendet werden, um einen Ersatzschlüssel zu generieren. Darüber hinaus erforderte die variable Anzahl von E-Mails pro Benutzer die Begrenzung der Ausgabe auf drei Spalten in der äußeren SELECT-Anweisung und die Definition des Bereichs möglicher Schlüsselwerte im zweiten crosstab()-Parameter.

Dieser Ansatz wandelt die neuesten E-Mail-Adressen pro Benutzer effektiv in separate Spalten um und bietet so eine übersichtlichere und organisiertere Ansicht des E-Mail-Verlaufs des Benutzers.

Das obige ist der detaillierte Inhalt vonWie transponiere ich Benutzer-E-Mail-Verlaufszeilen in Spalten in PostgreSQL?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!