Ask HN: Fuzzy Logic SQL select framework

Hi, I've recently been reading up on fuzzy logic. The crux of it seems to be allowing members to have partial inclusion in a set, instead of our current set paradigm of "it's in or it's out. As an example, consider the set "Long Rivers". The Amazon would have a 1.0 membership in this set whereas the Mississippi might have - let's say - 0.8 membership, the Ohio River 0.5, and the stream in your backyard would be 0. This number quantifies how much the item belongs in the set.

An analogous example would be our everyday sql selects. Let's say a company is searching for young employees with great sales records, to consider for promotions. "SELECT * FROM Employee WHERE Age<30 and Sales>100000 ORDER BY Sales desc,Age asc". This statement will miss the 21 year-old with $90,000 in sales and the 31 year-old with $500,000, although those people may be bright young stars as well. Widening the search parameters waters down the results and the black & white nature of it will always miss those on the cusp.

What the company actually wants is to do a sql statement "SELECT young employees with great sales records".

One solution would be fuzzy logic. They want employees that fall into two sets - 1.) young and 2.) good sales. The fuzzy solution would say, ok, every employee selling over $100,000 per year is a member of the Good Sales set with a membership value of 1.0. Over $90,000 is 0.8. $75,000 is 0.6.

Also, anyone less than 30 years old is a member of the Young Employee set with membership 1.0. 31 years old is 0.8. 35 is 0.5.

Once you define those parameters, by definition the membership of an employee in the two sets is the lowest membership he has in either set. Our precocious 21 year-old would be 0.8 (he has 1.0 in Young, and 0.8 in Good Sales) and our older but productive 31 year-old would also be 0.8 (0.8 Young and 1.0 Good Sales).

Our new query is something like "SELECT * FROM Employees WHERE Membership_YoungAndGoodSales>0 ORDER BY Membership_YoungAndGoodSales DESC, Sales DESC, Age ASC". This first returns all employees with perfect matches (1.0 membership in both sets) but scales down to include partial matches as well that might warrant a further look, as long as they have a non-zero membership in both sets.

I'm also testing this now with NBA games - instead of selecting teams that have scored 110 points per game AND have held opponents to 90 points per game AND (etc.), I just want to "SELECT high-scoring teams with good defense AND (etc.)"

Anyway, I was just curious if there was any existing db framework or code to deal with this. The major challenge seems to be coming up with the partial membership weights (e.g. a 31 year-old is 0.8 Young...why not 0.7 or 0.9?)

I was thinking that it would be possible to write a db selection framework that works with existing sql filter statements without modification - it could pre-process it and return exact matches first, then partials (for fields where it has membership information), ordering by the membership weight DESC. Anyway, feedback?