PostgreSQL中如何计算每个ID的前20条交易记录的amt平均值

DevOps 小满

2026-8-10

解决方案

要实现每个main_acc.id对应的前20条交易记录的amt平均值，可以借助窗口函数对每个分组内的记录排序筛选，具体SQL如下：

SELECT 
    CONCAT('ID: ', main_acc.id, ' -> avg ', ROUND(avg(trans.amt), 2)) AS "结果"
FROM (
    SELECT 
        main_acc.id,
        trans.amt,
        -- 按main_acc.id分组，对每组交易按指定规则排序并编号
        ROW_NUMBER() OVER (
            PARTITION BY main_acc.id 
            ORDER BY main_acc.track_id, trans.transaction_time DESC
        ) AS row_num
    FROM main_acc
    JOIN transactions trans ON main_acc.id = trans.main_acc_id
) AS ranked
WHERE row_num <= 20  -- 保留每组前20条记录
GROUP BY main_acc.id
ORDER BY main_acc.id;

代码说明：

子查询ranked：用ROW_NUMBER()窗口函数，以main_acc.id为分组依据，按照你指定的track_id和transaction_time降序排序，给每个分组内的交易记录分配唯一行号。
筛选前20条：通过WHERE row_num <= 20过滤掉每个分组中排名超过20的记录。
格式化输出：外层查询按main_acc.id分组计算平均值，用CONCAT拼接成你期望的输出格式，ROUND用于控制平均值的小数位数（可根据需求调整）。

可选调整：

如果需要保留无交易记录的main_acc.id，可以将JOIN改为LEFT JOIN，并处理NULL值（比如将平均值显示为N/A）：

SELECT 
    CONCAT('ID: ', main_acc.id, ' -> avg ', COALESCE(ROUND(avg(trans.amt), 2), 'N/A')) AS "结果"
FROM main_acc
LEFT JOIN (
    SELECT 
        trans.main_acc_id,
        trans.amt,
        ROW_NUMBER() OVER (
            PARTITION BY trans.main_acc_id 
            ORDER BY main_acc.track_id, trans.transaction_time DESC
        ) AS row_num
    FROM transactions trans
    JOIN main_acc ON main_acc.id = trans.main_acc_id
) AS ranked ON main_acc.id = ranked.main_acc_id AND ranked.row_num <=20
GROUP BY main_acc.id
ORDER BY main_acc.id;