PostgreSQL中如何获取关联查询后列的众数出现次数？

不需要创建临时表，用CTE（公共表达式）或者带窗口函数的查询就能实现，给你两种可行的方案：

方案一：先算众数再关联统计次数 #

先通过CTE算出每个t1.id对应的众数，再关联原表统计该众数的出现次数：

WITH modal_values AS (
    SELECT t1.id, mode() WITHIN GROUP (ORDER BY t2.col) AS modal_col
    FROM t1
    JOIN t2 ON t1.id = t2.user_id  -- 替换成你的实际关联条件
    GROUP BY t1.id
)
SELECT 
    mv.id,
    mv.modal_col,
    COUNT(t2.col) AS modal_count
FROM modal_values mv
JOIN t2 ON mv.id = t2.user_id AND mv.modal_col = t2.col
GROUP BY mv.id, mv.modal_col;

这个思路很直观：先拿到每个分组的众数，再回到原数据里统计这个值的出现次数，最终结果仅按t1.id（以及众数，同一个id的众数唯一）分组，完全符合你的要求。

方案二：用窗口函数一步到位 #

先统计每个t1.id下各t2.col的出现次数，再通过窗口函数筛选出次数最多的那个值（众数）及其次数：

SELECT DISTINCT
    t1.id,
    FIRST_VALUE(t2.col) OVER (
        PARTITION BY t1.id 
        ORDER BY COUNT(*) DESC
    ) AS modal_col,
    FIRST_VALUE(COUNT(*)) OVER (
        PARTITION BY t1.id 
        ORDER BY COUNT(*) DESC
    ) AS modal_count
FROM t1
JOIN t2 ON t1.id = t2.user_id  -- 替换成你的实际关联条件
GROUP BY t1.id, t2.col;

这里先按t1.id和t2.col分组统计次数，再用窗口函数在每个id分组内，按次数降序取第一个值（即众数）和对应的次数，最后用DISTINCT去重，得到每个id唯一的结果。

为什么你之前的方法不行？ #

• 聚合函数不能嵌套：PostgreSQL不允许count(mode(...))这种写法，因为聚合函数的参数不能是另一个聚合函数；
• row_number()/RANK()只是给行排序，没有统计具体的出现次数，必须先统计次数再筛选最大值。

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