PostgreSQL average for every field-CodePudding

I'm trying to calculate the average in this sample; This example is working (but only when I select a specific ID, rather than the avg for every ID limited to 20 entries) but I'm having a hard time remembering how to calculate this for every id within the database, rather than the developer specifying the ID explicitly (in this case as 2958). I.E. It would be optimal to have the following rows (assuming this is grouped by each primary key with a limit of 20 values per avg):

ID: 1 -> avg 5
ID: 2 -> avg 2
ID: 3 -> avg 7

etc....

select avg(acc.amt) 
from (
    select acc.amt amt
    from main_acc main_acc
      join transactions trans on main_acc.id = trans.main_acc_id
    where main_acc.id = 2958
    order by main_acc.track_id, transactions.transaction_time desc 
    limit 20
) acc;

Any help at all would be greatly appreciated. The only relevant columns are the ones shown above, I can add a schema definition if requested. Thank you!

CodePudding user response：

select main_acc.id, avg(acc.amt) from (select acc.amt amt
    from main_acc main_acc
             join transactions trans on main_acc.id = trans.main_acc_id
    
    order by main_acc.track_id, transactions.transaction_time desc) acc
group by main_acc.id;

In fact you do not need the subquery.

select acc.id, avg(acc.amt)
    from main_acc acc
             join transactions trans on acc.id = trans.main_acc_id
group by acc.id

CodePudding user response：

How do you define most recent. Is there a timestamp which is not shown, do you use the greatest id, something else? Basically, you order by that criteria and then use limit. So expanding the answer from @Tarik and assuming highest ids as most recent would yield something like:

select acc.id, avg(acc.amt) avg_for_id
    from main_acc acc
             join transactions trans on acc.id = trans.main_acc_id
group by acc.id
order by acc.id desc
limit 20;