如何查询各账户对应最新balance_event中的账户余额（PostgreSQL兼容）

CREATE TABLE account (
  account_id VARCHAR(36) NOT NULL PRIMARY KEY, -- uuid
  account_name VARCHAR(255) NOT NULL
);

CREATE TABLE "transaction" (
  transaction_id VARCHAR(36) NOT NULL PRIMARY KEY, -- uuid
  transaction_timestamp BIGINT NOT NULL,
  account_debit_id VARCHAR(36) NOT NULL,
  account_credit_id VARCHAR(36) NOT NULL,
  transaction_amount DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (account_debit_id) REFERENCES account(account_id),
  FOREIGN KEY (account_credit_id) REFERENCES account(account_id)
);

CREATE TABLE balance_event (
  balance_event_id VARCHAR(36) NOT NULL PRIMARY KEY, -- uuid
  transaction_id VARCHAR(36) NOT NULL,
  account_id VARCHAR(36) NOT NULL,
  account_balance DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (transaction_id) REFERENCES "transaction"(transaction_id),
  FOREIGN KEY (account_id) REFERENCES account(account_id)
);

balance_event表用于记录每个账户的余额变动事件，每当账户因交易发生余额变化时，该表会留存相关记录。这样无需遍历累加数千条交易即可获取账户余额，同时保留账户余额历史。

需求 #

编写兼容PostgreSQL的SQL查询，获取account表的所有行数据，并关联对应账户在balance_event中的最新account_balance。要求适配PostgreSQL，且保持ONLY_FULL_GROUP_BY配置开启。

使用PostgreSQL窗口函数ROW_NUMBER()标记每个账户的最新余额事件，再关联账户表，确保所有账户（包括无余额事件的账户）都能被查询到：

SELECT 
    a.account_id,
    a.account_name,
    be.account_balance AS latest_balance
FROM account a
LEFT JOIN (
    SELECT 
        account_id,
        account_balance,
        ROW_NUMBER() OVER (
            PARTITION BY account_id 
            ORDER BY (SELECT t.transaction_timestamp FROM "transaction" t WHERE t.transaction_id = be.transaction_id) DESC
        ) AS rn
    FROM balance_event be
) be ON a.account_id = be.account_id AND be.rn = 1;

说明 #

1. 子查询通过PARTITION BY account_id按账户分组余额事件，关联transaction表的transaction_timestamp倒序排序，确保最新交易对应的余额事件排在首位。
2. ROW_NUMBER()为每个分组内的行分配序号，序号为1的即为该账户的最新余额记录。
3. LEFT JOIN保证无余额事件的账户也会出现在结果中，此时latest_balance为NULL。
4. 该查询完全符合ONLY_FULL_GROUP_BY要求，所有非聚合列逻辑明确，不会出现分组字段不匹配问题。

如果balance_event表自身有可直接排序的字段（比如创建时间戳created_at），可简化排序逻辑：

SELECT 
    a.account_id,
    a.account_name,
    be.account_balance AS latest_balance
FROM account a
LEFT JOIN (
    SELECT 
        account_id,
        account_balance,
        ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY created_at DESC) AS rn
    FROM balance_event
) be ON a.account_id = be.account_id AND be.rn = 1;

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