Re:Ask HN: Fuzzy Logic SQL select framework
by mdasen on Thursday January 29, 2009 @06:34PM
(#457361)
So, you can just think of this like a math problem. To get a score, you can take the reciprocal of the person's age (so, someone 30 becomes 1/30) and then (for readability's sake) multiply it by a constant (say, 30) to make someone 30 a 1.0, someone 31 becomes .96, someone 35 becomes .85, etc.
If you want it to vary more or less from that norm, you can use exponents and logs. In your example, you wanted .8 for 31 and .5 for 35. I think raising it to the 4th or 5th power will get you around that mark. So, you can make it really sensitive to change in value or really insensitive to change depending on the log or exp that you use.
Likewise, do the same thing with sales (only in this case, since higher is better, we don't need to do the reciprocal thing). So, 100,000 becomes your baseline and that's a 1.0 (just divide by 100,000). Here, linear looks good since a person drawing 200k should be twice as good as the person drawing 100k, but you could use exponents or logs just like the previous example.
Then, you multiply them together. So, someone 30 years old who gets 100,000 in sales has a 1.0 rating. Someone 25 years old with 100,000 in sales has a 2.0736 rating. Someone 35 with 500,000 in sales has a 2.69 rating. And so on.
The SQL would look something like this:
SELECT *, (POW((1/age)*30, 4)*(sales/100000)) as the_awesome_score FROM table_name ORDER BY the_awesome_score
Basically, the difficulty in fuzzy matching is creating a common measurement. Once we have both items on a common scale (here with age 30 and 100,000 in sales being a 1.0), you can just multiply them and then you have your score. You can easily adjust the numbers to make it more or less sensitive to change on either side of the equation to suit your needs.
If you want it to vary more or less from that norm, you can use exponents and logs. In your example, you wanted .8 for 31 and .5 for 35. I think raising it to the 4th or 5th power will get you around that mark. So, you can make it really sensitive to change in value or really insensitive to change depending on the log or exp that you use.
Likewise, do the same thing with sales (only in this case, since higher is better, we don't need to do the reciprocal thing). So, 100,000 becomes your baseline and that's a 1.0 (just divide by 100,000). Here, linear looks good since a person drawing 200k should be twice as good as the person drawing 100k, but you could use exponents or logs just like the previous example.
Then, you multiply them together. So, someone 30 years old who gets 100,000 in sales has a 1.0 rating. Someone 25 years old with 100,000 in sales has a 2.0736 rating. Someone 35 with 500,000 in sales has a 2.69 rating. And so on.
The SQL would look something like this:
Basically, the difficulty in fuzzy matching is creating a common measurement. Once we have both items on a common scale (here with age 30 and 100,000 in sales being a 1.0), you can just multiply them and then you have your score. You can easily adjust the numbers to make it more or less sensitive to change on either side of the equation to suit your needs.