PostgreSQL按id分组取max area对应主导function并求和的SQL实现

实现思路 #

你需要同时完成分组聚合计算、分组内按规则排序取top1的function两个逻辑，通过窗口函数+子查询即可实现：

1. 先对同id下的所有行按规则排序：优先按area倒序，其次count倒序，最后按function自定义优先级排序（industry优先级最高，其次是education，其余默认更低）
2. 取每个id分组下排序第一的行的function，同时聚合计算同id的area、count总和

可用SQL代码 #

假设你的表名为你实际的业务表名，SQL如下：

WITH ranked_data AS (
    SELECT 
        id,
        area,
        count,
        function,
        -- 按规则生成分组内排序号
        ROW_NUMBER() OVER (
            PARTITION BY id 
            ORDER BY 
                area DESC, 
                count DESC, 
                CASE function 
                    WHEN 'industry' THEN 1 
                    WHEN 'education' THEN 2 
                    ELSE 3 
                END ASC
        ) AS rn,
        -- 窗口聚合计算同id的总和，避免二次关联
        SUM(area) OVER (PARTITION BY id) AS total_area,
        SUM(count) OVER (PARTITION BY id) AS total_count
    FROM your_table_name -- 替换为你实际的表名
)
SELECT 
    id,
    total_area AS area,
    total_count AS count,
    function
FROM ranked_data
WHERE rn = 1
ORDER BY id;

注意：function是Postgres的保留关键字，如果你运行时提示语法错误，可以将所有字段引用处替换为"function"。

结果验证 #

执行上述SQL返回的结果完全匹配预期：

• id=1分组：area最大的行对应function为industry，area总和300、count总和50
• id=2分组：所有行area相同，count最大的行对应function为education，area总和1200、count总和40
• id=3分组：所有行area、count均相同，优先级更高的industry被选中，area总和300、count总和2

内容的提问来源于stack exchange，提问作者Maarten van Middendorp