Rank SqlServer data using C#

Question

4.33/5 (4 votes)

See more:

Hi all,

The question below was deleted for some reason I don't understand (no comment was being made). Perhaps the deleter wasn't familiar with the concept of fuzzy logic. This is okey - just think of the question as relating to ranking in SELECT statements for dealing with big data. So I try again, and have also tried to improve the question to the best of my knowledge. The question itself is very important to me, as it is fundamental to a research problem I am working on. /Petter

Hi all,

I'm working on a "fuzzy logic" way to retrieve and order (ranking/weighing) data from a database. For example, if a person looks for a hotel room in between 100 and 200 euros per night, a room that costs 150 euros might be ranked as number one, a room that costs 199 euros will be ranked rather low in the result list, and a room that costs 202 euros might still be included in the results, in case there are just a few (if any) results that fulfil the user's requirements. Another example: A customer wants to look for red shirts in the 30-50 euro price range, and the database displays darkred shirts as well, as they are a rather close match and cost 40 euros.

I'm now thinking of how to accomplish this behaviour using a SqlServer database. (NOTE: I'm not looking for the built-in freetext search features.)

One way to go ahead would probably mean using Sql functions, to which I send the values that the user enters (for example via a web site form --> stored procedure), so that I end up with a number of functions for various cases of fuzzy logic.

Another way would perhaps be to use Linq to Sql and then write the functions in C# (I might be incorrect here). I'm much better at writing C# code than advanced Sql code plus I can check the code as I test it in Visual Studio, so if this would be feasible, then that would probably be a good thing, but then again, this approach wouldn't be that independent, and I hope to implement a fuzzy logic project here at CodeProject for anyone to use.

A third option would be to do a simple rank in long, nasty-looking stored procedures, using syntax like in http://stackoverflow.com/questions/9329678/sql-search-query-how-to-assign-weight-to-each-input-parameters-and-how-to-order[^] However, this approach would not be easy to maintain or to use in other projects.

Perhaps there is a fourth option that I might have missed, but I have done extensive searching and reading on fuzzy logic, and these options are what I have come up with.

I also have yet to see any usable code of these options, so for the moment, I could really use any help that's out there. In other words, if someone is familiar with working with this concept, I would be happy for any pointers.

Thanks very much in advance,

Petter

Posted 31-Mar-13 23:02pm

petter2012

Updated 31-Mar-13 23:03pm

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jörgen Andersson · Accepted Answer · 2013-04-01T09:22:00

Storing, retrieving, filtering, ordering, ranking and aggregation of data is what databases are optimized for and better at. Math and presentation not so.

Your case of fuzzy logics in the filtering is not really affecting it that badly unless you're putting in a lot of math in the queries.
What you definitely should take a look at is Analytic Functions, for example CUME_DIST[^].

The question is, how much can you affect the design of the database?
How you store your data will be of the highest importance, it's really crucial for the query performance.

For example, you shouldn't just store "Red" in a colour column, but rather RGB values, in a column each, and then for example search for CUME_DIST () OVER (ORDER BY ABS(@RED - RED)) to get values close to the red colour you're searching for.

My opinion in short: The math in the fuzzy logics should be done in the application layer, getting/creating the values to calculate from should be done in the database.

CPallini · Accepted Answer · 2013-04-01T00:14:00

Solution 1

I would keep the fuzzification/defuzzification code on the C# side, because, in my opinion, a powerful programming language like C# is better suited for the task (more elegant and clean code).
My two cents.

Posted 1-Apr-13 0:14am

CPallini

Comments

petter2012 1-Apr-13 8:41am

I totally agree (SQL is such a hack when one tries to do more than the basic and very neat CRUD operations).

However, I can't figure out how to accomplish this. I mean, sending my code to the C# methods would work just fine, but how could I evaluate the Sql data this way? What I need to do is (I think) to

1. call my stored procedure, including calls to different functions/metods
2. In my where statements, call the functions/methods for each parameter in order to get a weighing number/rating,
3. create a new sql tabl based on the ratings derived from the functions/methods.

This would mean that Sql functions would be the only way to solve this problem, but if it would be possible using C#, then so much better.