PostgreSQL中计算用户首笔交易金额总和的实现方法

表结构 #

users
____________
id | name

transactions
____________
id | user_id | amount 

用户与交易为一对多关系，需求是统计所有用户首笔交易的金额总和（示例：Alice首笔交易金额10，Alison首笔20，总和为30）。

你当前的查询已经能获取每个用户的首笔交易金额，只需在此基础上嵌套求和即可，以下是两种可行方案：

方案1：基于现有子查询求和 #

把你写的查询作为子查询，外层直接对first_amount求和，同时过滤掉无交易的用户：

SELECT SUM(first_amount) AS total_first_amount
FROM (
    SELECT (SELECT amount FROM transactions WHERE users.id = transactions.user_id ORDER BY id ASC LIMIT 1) AS first_amount
    FROM users
) AS user_first_transactions
WHERE first_amount IS NOT NULL;

方案2：使用窗口函数（更高效） #

通过ROW_NUMBER()窗口函数标记每个用户的交易顺序，筛选出首笔交易后求和，适合大数据量场景：

SELECT SUM(t.amount) AS total_first_amount
FROM (
    SELECT 
        user_id, 
        amount,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY id ASC) AS transaction_rank
    FROM transactions
) AS t
WHERE t.transaction_rank = 1;

如果需要包含无交易的用户（这类用户的首笔金额按0计算），可以用LEFT JOIN关联用户表：

SELECT COALESCE(SUM(t.amount), 0) AS total_first_amount
FROM users u
LEFT JOIN (
    SELECT 
        user_id, 
        amount,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY id ASC) AS transaction_rank
    FROM transactions
) AS t ON u.id = t.user_id AND t.transaction_rank = 1;

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