PostgreSQL distinct去重查询同时求和并获取最新元数据

核心需求 #

基于给定的业务数据表，按Name（客户端）维度聚合输出三类结果：

• 该客户端所有记录的Quantity全量总和（不区分正负值）
• 该客户端所有Quantity>0的记录中，Execution time最大（版本最新，数值越大版本越新）对应的Metadata值
• 上述最新正记录对应的Execution time值

原始数据集结构 #

预期输出结果 #

原SQL错误原因 #

原写法存在3个核心问题，导致结果不符合预期：

• WHERE quantity > 0直接过滤了负数量记录，后续求和逻辑无法统计到-1这类冲销记录，总和计算错误
• 求和子查询没有关联当前行的Name字段，属于非关联子查询，计算的是全表总和而非单客户端总和
• 全量求和、取最新正记录元数据两个逻辑耦合在同一个过滤后的数据集中执行，逻辑边界混乱

修正后的SQL #

该写法适配PostgreSQL（原SQL使用了PG专属的DISTINCT ON语法），通过CTE拆分两个独立计算逻辑后关联，避免逻辑冲突：

WITH client_total AS (
    -- 第一步：计算每个客户端的全量Quantity总和，不过滤任何记录
    SELECT
        Name,
        CAST(SUM(Quantity) AS INT) AS summed_quantity
    FROM your_table
    GROUP BY Name
),
client_latest_positive AS (
    -- 第二步：筛选Quantity>0的记录，取每个客户端最新版本的元数据
    SELECT DISTINCT ON (Name)
        Name,
        Metadata,
        Execution_time
    FROM your_table
    WHERE Quantity > 0
    ORDER BY Name, Execution_time DESC
)
-- 关联两部分结果输出
SELECT
    t.Name,
    t.summed_quantity,
    p.Metadata,
    p.Execution_time
FROM client_total t
LEFT JOIN client_latest_positive p
    ON t.Name = p.Name
ORDER BY t.Name;

注意：执行时将your_table替换为实际业务表名即可。

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