PostgreSQL查询获取各用户最新负余额交易记录

原有查询缺陷 #

• 错误逻辑：直接全局筛选endingBalance < 0的记录后取最大交易id，未按user_id分组定位每个用户自身的最新交易，会误返回用户历史上的旧负余额记录，即使用户最新交易余额已经为正。
• 正确执行顺序：先定位每个用户id最大的最新交易，再从这批最新交易中筛选余额小于0的记录，最后关联用户表取所需字段。

修正后可直接运行的查询语句 #

SELECT
    et.id,
    et.user_id,
    et.amount,
    et.trans_type,
    COALESCE(et.endingBalance, 0) AS current_balance,
    et."processId",
    upu.email,
    et.created_at
FROM (
    SELECT *
    FROM (
        SELECT
            *,
            ROW_NUMBER() OVER (
                PARTITION BY user_id
                ORDER BY id DESC
            ) AS row_rank
        FROM employer_transactions
    ) ranked_trans
    WHERE row_rank = 1 -- 取每个用户分组下id最大的最新交易
    AND endingBalance < 0 -- 仅保留最新交易余额为负的记录
) et
LEFT JOIN "users-permissions_user" upu
    ON et.user_id = upu.id;

逻辑匹配验证 #

• 针对user_id=333的测试场景：若其最新交易id=1952对应余额为1297.31（正数），该记录会在最新交易筛选阶段被余额条件过滤，不会错误返回该用户id=1946的旧负余额记录，符合无返回的预期。
• 针对多用户测试场景：用户333最新交易余额-1297.31、用户222最新交易余额-1298.31会被正常返回；用户111最新交易余额900.31（正数）会被过滤，无相关记录返回，完全匹配需求。

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