Postgresql中已实现rank函数的表如何使用GROUP BY子句

参考示例表结构 #

candidate_id |  name   |    status    | phone_num | process_fk
--------------+---------+--------------+-----------+------------
            1 | james   | regular      | 0985905   |          1
            2 | kylie   | probationary | 098889487 |          2
            3 | Anne    | regular      | 095590    |          1
            4 | Olivia  | probationary | 09556451  |          3
            5 | Charles | regular      | 09445099  |          2
            6 | Hannah  | regular      | 0977988   |          2

核心逻辑前提 #

PostgreSQL中RANK()属于窗口函数，执行优先级晚于WHERE、GROUP BY、HAVING子句，仅早于ORDER BY。因此需要根据使用场景选择对应写法：

场景1：先聚合再排名（同层级查询可实现） #

如果需求是先分组聚合得到统计值，再对统计值计算排名，可直接在同一查询中编写，RANK()窗口函数中直接引用聚合结果即可。
示例需求：按process_fk分组统计每个流程的正式/试用期员工数量，再按正式员工数量降序排名：

SELECT 
  process_fk,
  COUNT(*) FILTER (WHERE status = 'regular') AS regular_num,
  COUNT(*) FILTER (WHERE status = 'probationary') AS probationary_num,
  RANK() OVER (ORDER BY COUNT(*) FILTER (WHERE status = 'regular') DESC) AS regular_rank
FROM candidate
GROUP BY process_fk;

场景2：先排名再聚合（需嵌套子查询/CTE） #

如果需求是先对原始数据计算排名，再对带排名的结果做分组聚合，必须先将排名逻辑封装在子查询或公共表表达式（CTE）中，再对外层的结果执行GROUP BY。
示例需求：先给每个process_fk分组内的员工按candidate_id升序排名，再统计每个流程中排名为1的员工数量：

WITH ranked_candidate AS (
  SELECT 
    *,
    RANK() OVER (PARTITION BY process_fk ORDER BY candidate_id ASC) AS rk
  FROM candidate
)
SELECT 
  process_fk,
  COUNT(*) AS rank1_employee_count
FROM ranked_candidate
WHERE rk = 1
GROUP BY process_fk;

常见错误说明 #

不要在同一个查询层级同时使用RANK()和GROUP BY，除非RANK()中引用的所有字段都属于GROUP BY的分组字段，或者是聚合函数的计算结果，否则会触发「字段未包含在分组列表中」的报错。 #

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