I am trying to create a function that calculates the moving average for an item. What I have is a table of "item" and then each user can "vote" which produces one vote, with a score, user_id and item_id.
I want to calculate an AVG, which is stored in item_id after every insert and is calculated based on number of vote's (selected by vote.item_id) and averages the score using vote.score.
this is the idea of the query.. this works, but stores the moving average int he last column of vote and is not usable, but I am close!!
select vote.score as single_vote_score, vote.user_id, vote.item_id, items.score as item_score,
AVG(vote.score)
OVER(ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM vote
LEFT JOIN items on vote.item_id = items.id
WHERE vote.item_id = 10;
This is the function
CREATE FUNCTION calculate_item_score() RETURNS TRIGGER AS $calculates_items_score_by_average$
BEGIN UPDATE "item"
SET "score" = AVG("vote"."score")
FROM "vote"
WHERE "item"."id"="vote"."item_id";
RETURN NEW;
END;
$calculates_items_score_by_average$
LANGUAGE plpgsql;
This trigger should run every time a vote is inserted!
CREATE TRIGGER "update_item_score"
AFTER INSERT ON "vote"
FOR EACH ROW EXECUTE
PROCEDURE calculate_item_score();
-- this errors out
INSERT INTO vote (user_id, photo_id, score) VALUES ('129','10','50.0');
This is my error
ERROR: aggregate functions are not allowed in UPDATE
LINE 1: UPDATE "photo" SET "score" = AVG("vote"."score") FROM "vote"...
^
QUERY: UPDATE "item" SET "score" = AVG("vote"."score") FROM "vote" WHERE
"item"."id"="vote"."item_id"
CONTEXT: PL/pgSQL function calculate_item_score() line 1 at SQL statement