log(2, no_of_jobs + 1) will give 0 for 0 jobs, 1 for 1 job, 1.58 for 2 etc. etc.
On 11 Feb 2011, at 14:04, Johan De Meersman wrote: > How about the square root of the number of jobs, or some other root if you > want another coefficient? That doesn't have the limiting behaviour a > logarithmic function offers, though. > > On Fri, Feb 11, 2011 at 2:08 PM, Richard Reina <gatorre...@gmail.com> wrote: > >> Hi Travis, >> >> This is very helpful thank you. However, is there a way to make it not be >> less than a 1. As it's written below someone with one job gets a zero and >> someone with no jobs gets a NULL. It would be great if someone with 1 job >> got a 1 and someone with zero jobs got a 0. >> >> Thanks again, >> >> Richard >> >> 2011/2/10 Travis Ard <travis_...@hotmail.com> >> >>> Maybe some sort of logarithmic expression? >>> >>> select no_of_jobs, 10 * log(10, no_of_jobs) as job_weight >>> from data; >>> >>> Of course, you'd have to tweak your coefficients to match the weighting >>> system you want to use. >>> >>> -Travis >>> >>> -----Original Message----- >>> From: Richard Reina [mailto:gatorre...@gmail.com] >>> Sent: Thursday, February 10, 2011 3:07 PM >>> To: mysql@lists.mysql.com >>> Subject: function to limit value of integer >>> >>> Is there a function that can limit the value of an integer in a MySQL >>> query? I am trying to write a query that scores someones experience. >>> However, number of jobs can become overweighted in the the query below. >> If >>> someone has done 10 jobs vs. 1 that's a big difference in experience. But >>> someone who's done 100 vs. someone who's done 50 the difference in >>> experience is not so great as they are both near the top of the learning >>> curve. In essence number of jobs becomes less and less of a contributor >> as >>> it increases. Is there a way to limit it's value as it increases? >>> >>> SELECT years_srvd + no_of_jobs AS EXPERIENCE >>> >>> Thanks, >>> >>> Richard >>> >>> >> > > > > -- > Bier met grenadyn > Is als mosterd by den wyn > Sy die't drinkt, is eene kwezel > Hy die't drinkt, is ras een ezel -- TTFN. Philip Riebold, p.rieb...@ucl.ac.uk /"\ Media Services \ / University College London X ASCII Ribbon Campaign Windeyer Building, 46 Cleveland Street / \ Against HTML Mail London, W1T 4JF +44 (0)20 7679 9259 (direct), 09259 (internal) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
