如何在PostgreSQL聚合查询中用窗口函数获取价值总和最高的部门？

测试数据 #

现有基础查询 #

当前已实现按id分组，获取总价值与出现频率最高的部门：

SELECT 
  id,
  MODE() WITHIN GROUP(ORDER BY dept) AS dept_freq,
  SUM(value) AS value
FROM test
GROUP BY id;

需求 #

需要额外获取每个id下部门价值总和最高的dept_value，期望输出：

可行方案：CTE+窗口函数+聚合过滤 #

通过CTE先计算每个部门的价值总和并排序，再在主聚合查询中筛选出排序第一的部门，避免复杂的子查询关联：

WITH dept_value_ranked AS (
    SELECT 
        id,
        dept,
        value,
        -- 计算当前id+dept组合的总价值
        SUM(value) OVER (PARTITION BY id, dept) AS dept_total,
        -- 按部门总价值降序、部门名称升序排序，确保唯一结果
        ROW_NUMBER() OVER (
            PARTITION BY id 
            ORDER BY SUM(value) OVER (PARTITION BY id, dept) DESC, dept
        ) AS rank_num
    FROM test
)
SELECT 
    id,
    MODE() WITHIN GROUP(ORDER BY dept) AS dept_freq,
    -- 筛选出当前id下排名第一的部门
    MAX(dept) FILTER (WHERE rank_num = 1) AS dept_value,
    SUM(value) AS value
FROM dept_value_ranked
GROUP BY id;

原理说明 #

1. CTE部分：
   • 用SUM(value) OVER (PARTITION BY id, dept)计算每个部门在对应id下的总价值
   • 用ROW_NUMBER()给每个id下的部门按总价值降序排序，若总价值相同则按部门名称升序，确保唯一的排名第一结果
2. 主查询部分：
   • 保留原有的MODE()获取频率最高部门
   • 用MAX(dept) FILTER (WHERE rank_num = 1)筛选出当前id下排名第一的部门（即价值总和最高的部门）
   • 正常计算总价值SUM(value)

这个方案将窗口计算与聚合逻辑整合在一个查询链中，可读性和执行效率都优于嵌套子查询关联的方式。

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