0

I have a database with column A,B,C and row data, for example:

A         B         C 
test1    2.0123    3.0123
test2    2.1234    3.1234

In my program I would like to search for the bestfit match in the databases, for example I will key in value b=2.133, c=3.1342, then it will return me test2, how can I do that?

Please give me some idea or keyword to google as what I was thinking is searching algorithm but seem like searching algorithm are more on completely match and is not find the bestfit match. Or is this binpacking algorithm? How can I solve the problem with that.

I got about 5 column B,C,D,E,F and find the most match value.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
xvi_16
  • 115
  • 1
  • 1
  • 10

2 Answers2

2

Seems like you are looking for a k-d tree that maps 2-dimensional space (attributes B,C that are the key) to a value (attribute A).

K-D tree allows efficient look up for nearest neighbor of a given query, which seems to be exactly what you are after.

Note that the same DS will efficiently handle more attributes if needed, by increasing the dimensionality of the key.

amit
  • 175,853
  • 27
  • 231
  • 333
0

Take a look at this(Nearest neighbor search):

http://en.wikipedia.org/wiki/Nearest_neighbor_search

In this the simplest algorithm(Linear search) would look something like this in SQL(for b=2.133, c=3.1342): 

SELECT A, MIN(SQRT(POW(B-2.133,2)+POW(C-3.1342,2))) FROM tablename;

i.e. take the row with the minimum vector distance from the points (sqrt((b1-b2)^2+(c1-c2)^2))

TK Omble
  • 369
  • 3
  • 8
  • Vector distance can be calculated in any number of dimensions. http://en.wikipedia.org/wiki/Euclidean_distance – TK Omble Mar 19 '15 at 08:36