使用 STRING_AGG 实现简洁的查询结果

数据与报告
1

SQL 中的 string_agg 函数是一个聚合函数，它可以使用各种选项将多行中的字符串（连接）合并为单个字符串。

当您想在报表中为每个数据分组组合列中的值时，它特别有用，它可以使您的查询结果更易于阅读，尤其是在处理多对多关系时。例如，如果您想创建一个显示每个主题使用的所有标签的报表，您可以使用 string_agg 将每个主题的所有标签连接成一个字符串。

语法

这是 string_agg 的基本语法：

STRING_AGG(expression, delimiter)

string_agg 函数接受两个参数：

  • expression：要连接的值。
  • delimiter：要在字符串之间插入的分隔符。分隔符可以是字符串或字符。

在 DE 查询中的示例用法

让我们看几个使用 string_agg 的示例查询。

列出网站上的所有类别

此查询将 categories 表中的 name 和 id 字段连接成一个字符串，其中每个 name 和 id 对由“ : ”分隔，每个对由“ , ”分隔。然后按 id 对这些对进行排序。

SELECT  
    -- STRING_AGG 函数将 'name' 和 'id' 字段连接成一个字符串。
    -- 每个 'name' 和 'id' 对由 ' : ' 分隔，每个对由 ', ' 分隔。
    -- 这些对按 'id' 排序。
    STRING_AGG(name || ' : ' || id, ', ' ORDER BY id) AS category_list
FROM 
    categories -- 数据从 'categories' 表中选择。

示例结果：

category_list
Uncategorized : 1, Site Feedback : 2, Staff : 3, Lounge : 4, Email : 5, Event : 6, Parent Category : 7, Sub Category

列出所有主题和关联标签

此查询选择 topics 表中的 id，并将每个主题的关联 tags 连接成一个字符串。每个标签由逗号和空格分隔，标签按字母顺序排序。结果按主题 id 分组，并按标签排序。如果主题没有任何标签，则 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

结果将是一个格式化的主题 ID 列表，每个主题 ID 都有一个按字母顺序排序的关联标签字符串。例如：

示例结果：

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
详细解释及内联注释 
-- 此 SQL 语句正在从 'topics' 表和关联的 'tags' 中选择数据。
SELECT 
    t.id topic_id, -- 从 'topics' 表中选择 'id'。
    -- STRING_AGG 函数将每个主题的关联 'tags' 连接成一个字符串。
    -- 每个标签由逗号和空格分隔，标签按字母顺序排序。
    string_agg(tags.name, ', ' ORDER BY tags.name) AS "tags"
FROM 
    topics t -- 数据从 'topics' 表中选择。
    -- 'topic_tags' 表通过 'topics' 表中的 'topic_id' 进行左连接。
    LEFT JOIN topic_tags tt ON tt.topic_id = t.id
    -- 'tags' 表通过 'topic_tags' 表中的 'tag_id' 进行左连接。
    LEFT JOIN tags ON tags.id = tt.tag_id
GROUP BY 
    t.id -- 结果按主题 'id' 分组。
ORDER BY
    "tags" -- 结果按 'tags' 排序。

在主题中发帖的用户

此查询将返回一个 topic ids 和 titles 列表，每个主题中发帖的用户的 usernames，以及每个主题中发帖的 distinct users 的 count。然后按用户数量降序对结果进行排序。

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

示例结果

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
详细解释及内联注释 
-- 此 SQL 语句正在从 'topics', 'posts', 和 'users' 表中选择数据。
SELECT
    topics.id AS "topic_id", -- 从 'topics' 表中选择 'id'。
    topics.title AS "topic_title", -- 从 'topics' 表中选择 'title'。
    -- STRING_AGG 函数将主题中发帖的用户的用户名连接成一个字符串。
    -- 每个用户名由逗号和空格分隔，用户名按字母顺序排序。
    STRING_AGG(DISTINCT users.username, ', ' ORDER BY users.username) AS "users_posted",
    -- 计算在主题中发帖的独立用户的数量。
    COUNT(DISTINCT users.id) AS "user_count"
FROM
    topics -- 数据从 'topics' 表中选择。
    -- 'posts' 表通过 'topics' 表中的 'topic_id' 进行连接。
    JOIN posts ON posts.topic_id = topics.id
    -- 'users' 表通过 'posts' 表中的 'id' 进行连接。
    JOIN users ON users.id = posts.user_id
GROUP BY
    topics.id, topics.title -- 结果按主题 'id' 和 'title' 分组。
ORDER BY
    "user_count" DESC -- 结果按用户数量降序排序。

本教程到此结束！

如果您有任何关于如何使用 string_agg 的问题或示例，请随时在下方分享。:slightly_smiling_face:

6 个赞