PostgreSQL：能否将两个不同分组的COUNT查询合并为单查询？FILTER是否适用？

你的思路方向是对的，FILTER子句确实可以用来实现这种多维度统计，但需要调整分组逻辑来适配两个不同的分组列（user_id和created_by_user_id）。直接按单一列分组会漏掉只创建物品但未拥有物品的用户，或者反过来，下面是两种可行的实现方式：

方法1：先整合所有目标用户，再关联统计 #

这种方式先通过UNION获取所有需要统计的用户ID（包括拥有物品和创建物品的），再分别关联原表统计两个数值，逻辑清晰且避免重复计算：

WITH all_target_users AS (
    -- 获取所有需要统计的用户ID（去重）
    SELECT user_id AS user_id FROM items WHERE user_id IN (?)
    UNION
    SELECT created_by_user_id AS user_id FROM items WHERE created_by_user_id IN (?)
)
SELECT
    atu.user_id,
    -- 统计该用户拥有的物品数量
    COUNT(i_owned.id) AS "Owned",
    -- 统计该用户创建的物品数量
    COUNT(i_created.id) AS "Created"
FROM all_target_users atu
-- 关联物品表统计拥有数
LEFT JOIN items i_owned 
    ON atu.user_id = i_owned.user_id 
    AND i_owned.user_id IN (?)
-- 关联物品表统计创建数
LEFT JOIN items i_created 
    ON atu.user_id = i_created.created_by_user_id 
    AND i_created.created_by_user_id IN (?)
GROUP BY atu.user_id;

方法2：用FILTER结合聚合函数直接统计 #

如果不想用CTE（公共表表达式），也可以直接在原表上聚合，通过COALESCE统一分组依据，同时用FILTER筛选对应列的统计条件：

SELECT
    -- 统一用户ID分组：优先取user_id，没有则取created_by_user_id
    COALESCE(user_id, created_by_user_id) AS user_id,
    -- 统计该用户拥有的物品数：仅统计user_id等于当前分组ID的行
    COUNT(id) FILTER (WHERE user_id = COALESCE(user_id, created_by_user_id)) AS "Owned",
    -- 统计该用户创建的物品数：仅统计created_by_user_id等于当前分组ID的行
    COUNT(id) FILTER (WHERE created_by_user_id = COALESCE(user_id, created_by_user_id)) AS "Created"
FROM items
-- 筛选目标用户：只要在user_id或created_by_user_id的IN列表中
WHERE user_id IN (?) OR created_by_user_id IN (?)
GROUP BY COALESCE(user_id, created_by_user_id);

注意要点 #

• 如果你需要统计的用户列表是同一个集合，可以把IN(?)的占位符统一，避免重复参数；
• 方法2中，如果某行的user_id和created_by_user_id是同一个用户，这行数据会同时被计入Owned和Created，这符合“用户自己创建并拥有该物品”的业务逻辑；
• 如果要避免统计重复的用户ID，UNION会自动去重，而UNION ALL不会，可根据业务需求选择。

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