PostgreSQL查询jsonb字段分组sum结果最大值的实现方法

直接使用嵌套子查询完成需求，代码如下：

SELECT *
FROM (
    -- 内层子查询：完成分组聚合，同时计算支出、收入两类金额总和
    SELECT
        row_data->>'companyName' AS "NOME EMPRESA",
        count(1) AS "Count",
        -- 计算类型为S的支出总额
        SUM(CASE
                WHEN row_data->>'bankMovementOperationType' = 'S'
                THEN (row_data->>'bankMovementAmount')::float
                ELSE 0
            END) AS "VALOR S",
        -- 计算类型为E的收入总额
        SUM(CASE
                WHEN row_data->>'bankMovementOperationType' = 'E'
                THEN (row_data->>'bankMovementAmount')::float
                ELSE 0
            END) AS "VALOR E"
    FROM public.teste
    WHERE abbreviation = 'BMO'
    GROUP BY row_data->>'companyName'
) AS agg_result
-- 按目标sum值倒序取第一条，即为sum结果最大的记录
ORDER BY "VALOR S" DESC
LIMIT 1;

实现说明 #

• 内层子查询没有提前过滤收支类型，通过CASE WHEN条件聚合一次扫描就完成两类金额的求和计算，执行效率更高。
• 完全采用select from (select ...)的嵌套结构，没有使用CTE、窗口函数等其他语法，符合写法要求。
• 保留了原查询里的公司名、记录数字段，金额类型转换逻辑和原SQL保持一致，不会出现JSON文本类型导致sum计算失败的问题。
• 排序字段可按需调整：需要筛选支出总和最大的记录就按"VALOR S"倒序，需要筛选收入总和最大的记录就按"VALOR E"倒序，需要筛选收支总金额最大的记录就按"VALOR S" + "VALOR E"倒序。

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