PostgreSQL 14如何高效查询同时属A、B、C团队的用户计数及列表？

问题分析 #

你需要找出同时属于A、B、C三个团队的唯一用户，统计其数量并生成用户ID列表。之前用多CTE的方式效率低下甚至超时，核心原因是多次CTE会重复扫描数据表，数据量大时性能损耗极高。

最优SQL实现 #

SELECT 
    COUNT(*) AS count,
    ARRAY_AGG(DISTINCT "user" ORDER BY "user") AS list_user
FROM (
    SELECT "user"
    FROM your_table_name  -- 替换为你的实际表名
    WHERE team IN ('A', 'B', 'C')
    GROUP BY "user"
    HAVING COUNT(DISTINCT team) = 3
) AS qualified_users;

代码解释 #

1. 内层子查询：
   • 先筛选出团队为A、B、C的记录，减少后续处理的数据量
   • 按user分组，用COUNT(DISTINCT team)统计每个用户所属的不同团队数量
   • 通过HAVING子句筛选出恰好属于3个不同团队的用户（即同时覆盖A、B、C）
2. 外层聚合：
   • 用COUNT(*)统计符合条件的用户总数
   • 用ARRAY_AGG(DISTINCT "user" ORDER BY "user")生成有序的用户ID数组，DISTINCT确保用户唯一，ORDER BY让列表更规整

性能优化提示 #

如果表数据量较大，建议在(user, team)上创建复合索引，避免全表扫描：

CREATE INDEX idx_user_team ON your_table_name ("user", team);

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