Mysql Query for logarithmic aging

Question

I want to implement a 'logarithmic' score-decay based on aging, and I'm trying to figure out the SUM/LOG combination. Here you have a simplified version of the current query:

SELECT SUM(1) as total_score FROM posts
JOIN votes ON votes.post_id = posts.post_id
WHERE 1
GROUP BY post_id
ORDER BY total_score DESC

I'm currently doing SELECT 'SUM(1) as total_score' but I want to modify the query to take the date/age of the vote into consideration; where a vote from today weights 1, a vote from 15 days ago weights close to .8 and a vote from 30 days ago close to 0. I'm storing the date field on the votes table (vote_date) as a unix_timestamp.

I'm not really concerned about the WHERE clausule; that's pretty straightforward. What I'm trying to figure out is the logarithmic aging part.

Answer 1

I think there are two parts to your answer. First, the weighting function and then the SQL implementation.

Wegighting function:

According to your graph, you don't want a log weight buit rather parabolic.

From this you have to solve

Xc = y

where

X = [1 1 1 ;
    15^2 15 1;
    30^2 30 1];

and

y = [1;.8;0];

you get c = X^(-1)y or in matlab

c = X\y

Now you have the appropriate wieights of the quadratic function you depicted; namely y = ax^2+bx+c with (a,b,c) =(-.0013,.0073,.9941).

SQL part:

you select statement should look like (assuming the column of interest is named "age")

SELECT (-.0013*age*age + .0073*age + .9941) as age_weighted

Hope it helps

Cheers

---

Here 's the complete Matlab code (also to doublecheck solution)

X = [1 1 1 ;
    15^2 15 1;
    30^2 30 1];

y = [1;.8;0];

c = X\y;

x= (1:30)';
y = [x.^2 x ones(30,1)]*c;

figure(1)
clf;hold on
plot(x,y)
plot([1 15 30],[1 .8 0],'o')