Die string_agg-Funktion in SQL ist eine Aggregatfunktion, die Zeichenfolgen aus mehreren Zeilen zu einer einzigen Zeichenfolge mit verschiedenen Optionen verknüpft (zusammenfügt).
Sie ist besonders nützlich, wenn Sie Werte aus einer Spalte für jede Datengruppe in Ihren Berichten kombinieren möchten, und kann Ihr Abfrageergebnis leichter lesbar machen, insbesondere bei vielen-zu-vielen-Beziehungen. Wenn Sie beispielsweise einen Bericht erstellen möchten, der alle für ein Thema verwendeten Tags anzeigt, könnten Sie string_agg verwenden, um alle Tags für jedes Thema zu einer einzigen Zeichenfolge zu verketten.
Syntax
Hier ist die grundlegende Syntax von string_agg:
STRING_AGG(expression, delimiter)
Die Funktion string_agg nimmt zwei Parameter entgegen:
expression: Der zu verknüpfende Wert.delimiter: Das Trennzeichen, das zwischen den Zeichenfolgen eingefügt wird. Das Trennzeichen kann eine Zeichenfolge oder ein Zeichen sein.
Beispielverwendung in DE-Abfragen
Werfen wir einen Blick auf einige Beispielabfragen, die string_agg verwenden.
Alle Kategorien auf einer Website auflisten
Diese Abfrage verknüpft die Felder name und id aus der Tabelle categories zu einer einzigen Zeichenfolge, wobei jedes Paar aus name und id durch ’ : ’ und jedes Paar durch ', ’ getrennt wird. Die Paare werden dann nach id sortiert.
SELECT
-- Die Funktion STRING_AGG verknüpft die Felder 'name' und 'id' zu einer einzigen Zeichenfolge.
-- Jedes Paar aus 'name' und 'id' wird durch ' : ' getrennt, und jedes Paar wird durch ', ' getrennt.
-- Die Paare werden nach 'id' sortiert.
STRING_AGG(name || ' : ' || id, ', ' ORDER BY id) AS category_list
FROM
categories -- Die Daten werden aus der Tabelle 'categories' ausgewählt.
Beispielergebnisse:
| category_list |
|---|
| Uncategorized : 1, Site Feedback : 2, Staff : 3, Lounge : 4, Email : 5, Event : 6, Parent Category : 7, Sub Category |
Alle Themen und zugehörige Tags auflisten
Diese Abfrage wählt die id aus der Tabelle topics aus und verknüpft die zugehörigen tags für jedes Thema zu einer einzigen Zeichenfolge. Jeder Tag wird durch ein Komma und ein Leerzeichen getrennt, und die Tags werden alphabetisch sortiert. Das Ergebnis wird nach der id des Themas gruppiert und nach den Tags sortiert. Wenn ein Thema keine Tags hat, ist die Spalte tags NULL.
SELECT t.id topic_id,
string_agg(tags.name, ', ' ORDER BY tags.name) AS "tags"
FROM topics t
LEFT JOIN topic_tags tt ON tt.topic_id = t.id
LEFT JOIN tags ON tags.id = tt.tag_id
GROUP BY t.id
ORDER BY tags
Das Ergebnis ist eine Liste formatierter Themen-IDs, jede mit einer Zeichenfolge zugehöriger, alphabetisch sortierter Tags. Zum Beispiel:
Beispielergebnisse:
| topic | tags |
|---|---|
| Welcome to the Lounge (3) | tag1, tag2 |
| A Very Safe for work post (3) | tag3, tag4, tag,5 |
| This is a blog tagged post (4) | tag3 |
| About the Lounge category (3) | tag5 |
| Welcome to your 14 day standard hosting trial! | NULL |
Detaillierte Erklärung mit Inline-Kommentaren
-- Diese SQL-Anweisung wählt Daten aus den Tabellen 'topics' und zugehörigen 'tags' aus.
SELECT
t.id topic_id, -- Wählt die 'id' aus der Tabelle 'topics' aus.
-- Die Funktion STRING_AGG verknüpft die zugehörigen 'tags' für jedes Thema zu einer einzigen Zeichenfolge.
-- Jeder Tag wird durch ein Komma und ein Leerzeichen getrennt, und die Tags werden alphabetisch sortiert.
string_agg(tags.name, ', ' ORDER BY tags.name) AS "tags"
FROM
topics t -- Die Daten werden aus der Tabelle 'topics' ausgewählt.
-- Die Tabelle 'topic_tags' wird über 'topic_id' aus der Tabelle 'topics' verknüpft.
LEFT JOIN topic_tags tt ON tt.topic_id = t.id
-- Die Tabelle 'tags' wird über 'tag_id' aus der Tabelle 'topic_tags' verknüpft.
LEFT JOIN tags ON tags.id = tt.tag_id
GROUP BY
t.id -- Das Ergebnis wird nach der 'id' des Themas gruppiert.
ORDER BY
"tags" -- Das Ergebnis wird nach den 'tags' sortiert.
Benutzer, die in Themen gepostet haben
Diese Abfrage gibt eine Liste von topic-IDs und -Titeln, die usernames der Benutzer, die in jedem Thema gepostet haben, und die Anzahl der eindeutigen Benutzer, die in jedem Thema gepostet haben, zurück. Die Ergebnisse werden dann nach der Anzahl der Benutzer in absteigender Reihenfolge sortiert.
SELECT
topics.id AS "topic_id",
topics.title AS "topic_title",
STRING_AGG(DISTINCT users.username, ', ' ORDER BY users.username) AS "users_posted",
COUNT(DISTINCT users.id) AS "user_count"
FROM
topics
JOIN
posts ON posts.topic_id = topics.id
JOIN
users ON users.id = posts.user_id
GROUP BY
topics.id, topics.title
ORDER BY
"user_count" DESC
Beispielergebnisse
| topic | topic_title | users_posted | user_count |
|---|---|---|---|
| Lets make a topic (10) | Lets make a topic | anonymous, user1, user2, user3 | 4 |
| Another Topic (3) | Another Topic | user3, user4, user5 | 3 |
| Discobot test (2) | Discobot test | user6 | 1 |
Detaillierte Erklärung mit Inline-Kommentaren
-- Diese SQL-Anweisung wählt Daten aus den Tabellen 'topics', 'posts' und 'users' aus.
SELECT
topics.id AS "topic_id", -- Wählt die 'id' aus der Tabelle 'topics' aus.
topics.title AS "topic_title", -- Wählt den 'title' aus der Tabelle 'topics' aus.
-- Die Funktion STRING_AGG verknüpft die Benutzernamen der Benutzer, die in dem Thema gepostet haben, zu einer einzigen Zeichenfolge.
-- Jeder Benutzername wird durch ein Komma und ein Leerzeichen getrennt, und die Benutzernamen werden alphabetisch sortiert.
STRING_AGG(DISTINCT users.username, ', ' ORDER BY users.username) AS "users_posted",
-- Zählt die Anzahl der eindeutigen Benutzer, die in dem Thema gepostet haben.
COUNT(DISTINCT users.id) AS "user_count"
FROM
topics -- Die Daten werden aus der Tabelle 'topics' ausgewählt.
-- Die Tabelle 'posts' wird über 'topic_id' aus der Tabelle 'topics' verknüpft.
JOIN posts ON posts.topic_id = topics.id
-- Die Tabelle 'users' wird über 'id' aus der Tabelle 'posts' verknüpft.
JOIN users ON users.id = posts.user_id
GROUP BY
topics.id, topics.title -- Das Ergebnis wird nach der 'id' und dem 'title' des Themas gruppiert.
ORDER BY
"user_count" DESC -- Das Ergebnis wird nach der Anzahl der Benutzer in absteigender Reihenfolge sortiert.
Das war’s für dieses Tutorial!
Wenn Sie Fragen oder Beispiele haben, wie Sie string_agg verwendet haben, teilen Sie diese gerne unten mit. 