Poemranker [Suggestion] Wed Mar 12 05:44:01 PDT 2025

nentwined @ 64.60.192.130 | 20-Jan-05/11:13 AM | Reply

That's... difficult, if you mean "by votes placed in the last month or three months". There's 10,000 poems, and it's a slow operation to actually calculate a poem's average; at least in "internet time". At least, I don't know of a better way than what I'm doing, and that takes about (runs "explain analyze") 7.5 seconds to get the "current average" of every poem. What I do to get around that is keep the running tally on the poem itself and update that when either the poem is edited or someone votes on it.

SELECT id, voteaverage(id) FROM poemranker_poems: 7483.31 msec
SELECT id, avg FROM poemranker_poems: 190.28 msec

nentwined @ 64.60.192.130 > nentwined | 20-Jan-05/11:14 AM | Reply

create or replace function voteaverage(int4) returns float as '
DECLARE
myavg float8;
BEGIN
myavg = 5+(float8(((avg(v.value)-5)))*(1/(1+exp(float8(3-count(v.value)/3))))) from votes v, poemranker_poems p WHERE v.poemid = $1 and v.poemid = p.id AND v.stamp > p.lastmod;
if myavg IS NULL THEN
return 5;
end if;
return myavg;
END;
' language 'plpgsql';