Postgresql – Get the average of the last X records for each foreign key

postgresql

My table is a list of yacht handicaps for yacht racing. After each race a new handicap is calculated based on how the yacht performed which is added to the table. The actual handicap used to modify the finish time of a yacht is the average of the last 3 races. I am having trouble getting my head around the sql to retrieve the race modifier of all yachts.

My table data (I have removed extraneous columns):

id | hcpval | yacht_id 
----------------------
 1 | 0.9    |  1
 2 | 0.75   |  2
 3 | 0.84   |  1
 4 | 0.71   |  2
 5 | 0.88   |  1
 6 | 0.73   |  2
 7 | 0.81   |  1
 8 | 0.7    |  2

Obviously, the most recent results have the higher id. After the query has finished I'm hoping to get the following results:

yacht_id | hcpval
-----------------
    1    | 0.843 - avg of ids 7,5 & 3
    2    | 0.713 - avg of ids 8,6 & 4

Best Answer

You need to find the three latest rows. That is typically done using a window function. The result of that can then be aggregated:

select yacht_id, 
       avg(hcpval) as hcpval
from (
  select yacht_id, 
         hcpval, 
         row_number() over (partition by yacht_id order by id desc) as rn
  from handicaps
)  t
where rn <= 3
group by yacht_id
order by yacht_id;

Online example: https://rextester.com/DDTU17483

Related Solutions

Postgresql – Nearest value for each foreign key

You can use window functions to achieve your goal. lag() and lead() are ones which can help you in a query like

SELECT lag(di_timestamp) OVER ordering, lead(di_timestamp) OVER ordering
  FROM data_item
 WHERE fk_fc_id IN (35246,35247)
WINDOW ordering AS (ORDER BY di_timestamp 
                    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);

This will return the previous and the next timestamps, if there is any.

postgresql – How to Implement a Sequence for Each Foreign Key Value

How about using a column in the table account called max_customer_id and updating that every time there's a new customer? That should be the simplest solution available that I can think of.

With this solution you have to take care of the concurrency. Take a look at the document PostgreSQL Concurrency issues:

If two concurrent transactions try to insert rows having the same key value, then the second one will block until the first one finishes. If the first transaction commits, the second one must abort because of the uniqueness constraint; but if the first one aborts the second one can proceed.

If you want to assign a serial number to each row in a table, you might try
INSERT INTO mytable (id, ...) VALUES( (SELECT MAX(id) + 1 FROM mytable), ...);
This will not work safely unless you take an explicit lock on the whole table, which will prevent concurrent insertions. (It’ll also be quite slow, because MAX scans the whole table in PostgreSQL.)

Alternatively you could place the value in a separate table. This table would contain the account_id and maximum account_specific_id in use for that account. That way you could handle the concurrency easier with UPDATEs. Same source:

A variant is to use a single-row table to hold the next ID number to assign:
SELECT next FROM mytable counter FOR UPDATE; UPDATE mytable counter SET next = $next + 1; INSERT INTO mytable (id, ...) VALUES($next, ...);
This works (as long as you use FOR UPDATE), but you still have the problem that only one insertion transaction can proceed at a time. The implicit write lock on the counter row is the bottleneck.
[The author goes on recommending sequences.]

The number 2 option with sequences is not however recommended in this case as new sequences are not meant to be routinely inserted in a production environment.

Best Answer

Related Solutions

Postgresql – Nearest value for each foreign key

postgresql – How to Implement a Sequence for Each Foreign Key Value

Related Question