Wie transponiere ich Zeilendaten von Benutzer-E-Mails in Spalten in PostgreSQL?

Zeilendaten in Spalten umwandeln: Zeigt die neueste E-Mail-Adresse des Benutzers an

Frage:

Sie haben mehrere E-Mail-Adressen pro Benutzer in Ihrer Tabelle und möchten sie in Spalten aufteilen, sodass die drei neuesten E-Mail-Adressen für jeden Benutzer basierend auf dem Erstellungsdatum angezeigt werden.

Erwartete Ausgabe:

user_name	user_id	email1	email2	email3
Mary	123	[email protected]	[email protected]	[email protected]
Joe	345	[email protected]	NULL	NULL

Lösung:

Dazu können Sie die Funktion crosstab() des Moduls tablefunc in PostgreSQL nutzen:

<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
    ORDER BY user_id, rn
    $$,
    'VALUES (1),(2),(3)'
) AS ct (user_id integer, user_name text, email1 text, email2 text, email3 text);</code>

Erklärung:

• Die Unterabfrage ruft die Benutzer-ID, den Benutzernamen und die E-Mail-Adresse jedes Benutzers sowie seine Ordnungszahlen ab. Die Funktion row_number() wird verwendet, um die Postfachreihenfolge für jeden Benutzer zu bestimmen, wobei NULLS LAST sicherstellt, dass NULL-Werte als die neuesten Werte betrachtet werden.
• Die Funktion crosstab() transponiert dann das Postfach in Spalten, wobei Zeilennummern als Schlüssel verwendet werden. Der zweite Parameter „VALUES (1),(2),(3)“ von crosstab() gibt die drei zu erstellenden Spalten an.
• Die abschließende SELECT-Anweisung gibt die transponierten Daten zurück, wobei die drei neuesten Postfächer jedes Benutzers in den Spalten email1, email2 und email3 erscheinen.

Hinweis:

• Wenn jeder Benutzer eine unterschiedliche Anzahl von Postfächern hat, können Sie „WERTE (1),(2),(3)“ durch eine Liste aller möglichen Schlüsselwerte ersetzen.
• Beachten Sie die Verwendung von NULLS LAST in der ORDER BY-Klausel, um sicherzustellen, dass NULL-Werte als die neuesten Werte behandelt werden.

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