在PostgreSQL中查询用户浏览次数超2次的书籍ID

我有一张名为user_activity的表，表结构及数据如下：

其中event_type_id=1代表“用户已浏览书籍”。需要编写PostgreSQL查询语句，获取用户浏览次数超过两次的书籍ID，预期输出结果如下：

这里提供两种实现方式，均能满足需求：

方式一：嵌套分组 #

SELECT
    user_id AS "用户ID",
    STRING_AGG(DISTINCT book_id::TEXT, ',') AS "已浏览书籍ID"
FROM user_activity
WHERE event_type_id = 1
GROUP BY user_id, book_id
HAVING COUNT(*) > 2
GROUP BY user_id;

方式二：使用CTE（逻辑更清晰） #

WITH filtered_books AS (
    SELECT
        user_id,
        book_id
    FROM user_activity
    WHERE event_type_id = 1
    GROUP BY user_id, book_id
    HAVING COUNT(*) > 2
)
SELECT
    user_id AS "用户ID",
    STRING_AGG(book_id::TEXT, ',') AS "已浏览书籍ID"
FROM filtered_books
GROUP BY user_id;

关键说明 #

1. 先按user_id和book_id分组统计浏览次数，通过HAVING COUNT(*) > 2筛选出浏览次数超过两次的书籍；
2. 利用PostgreSQL内置的STRING_AGG函数，将同一用户下符合条件的书籍ID拼接成逗号分隔的字符串；
3. book_id::TEXT是把数值类型的book_id转换为字符串，因为STRING_AGG要求聚合参数为字符串类型，避免类型不匹配报错。

内容的提问来源于stack exchange，提问作者Ricky Vikram