如何合并两个PostgreSQL查询以批量获取关联用户统计数据？

你可以通过**CTE（公共表表达式）**或者子查询嵌套的方式，将两个查询合并，自动为第一个查询返回的每个用户生成对应的统计数据。以下是具体实现：

方法1：使用CTE（可读性更高） #

WITH interacting_users AS (
    SELECT DISTINCT user_id
    FROM transactions
    WHERE (type_id, operation_id) IN (
        SELECT type_id, operation_id
        FROM transactions
        WHERE user_id = 11111 -- 目标用户User1的ID
    )
    AND user_id != 11111
)
SELECT
    iu.user_id AS "用户ID",
    COUNT(DISTINCT o.operation_id) AS "# of operations"
FROM interacting_users iu
LEFT JOIN operations o
    ON iu.user_id = o.user_id
    AND o.category = 'category1' -- PostgreSQL中字符串常量需用单引号
GROUP BY iu.user_id
ORDER BY iu.user_id;

逻辑说明 #

1. interacting_users CTE先执行第一个查询，获取所有和User1有交互的用户ID列表；
2. 将该用户列表与operations表做左连接，确保即使某个用户没有符合条件的操作，也会出现在结果中（统计数为0）；
3. 按用户ID分组，统计每个用户在category1下的唯一操作数。

方法2：使用子查询嵌套 #

如果不想用CTE，也可以直接把第一个查询作为子查询嵌入到FROM子句中：

SELECT
    sub.user_id AS "用户ID",
    COUNT(DISTINCT o.operation_id) AS "# of operations"
FROM (
    SELECT DISTINCT user_id
    FROM transactions
    WHERE (type_id, operation_id) IN (
        SELECT type_id, operation_id
        FROM transactions
        WHERE user_id = 11111
    )
    AND user_id != 11111
) sub
LEFT JOIN operations o
    ON sub.user_id = o.user_id
    AND o.category = 'category1'
GROUP BY sub.user_id
ORDER BY sub.user_id;

注意事项 #

• 确保transactions和operations表的user_id字段类型一致，避免关联错误；
• 若数据量较大，建议为transactions.user_id、transactions(type_id, operation_id)、operations(user_id, category)创建索引，提升查询性能；
• 原第二个查询中用双引号包裹category1会被PostgreSQL识别为标识符（如列名），需改为单引号表示字符串常量。

内容的提问来源于stack exchange，提问作者Eu Román