2
$\begingroup$

I have a table like this; all columns are numeric:

TableTest

Col1  Col2  Col3  Col4
  5    6     7     8
 12    6     5     6
  2    3.5   6     1

And I want to find the closest row with these values: 4, 5.75, 7.2, 6. A human can find it (row 1 is answer), but how can I write a query for a computer to find it?

I'm using SQL Server but I think it's relevant to machine learning.

$\endgroup$

2 Answers 2

1
$\begingroup$

This problem can be implemented as a query that minimizes "loss" from the inputs. If "mean square error" loss is used, loss calculation will look like :

select col1, col2, (col1 - inp1)^2 + (col2 - inp2)^2 as mse_loss, 
from table 

Now, you can find the row with least value of mse_loss

$\endgroup$
7
  • $\begingroup$ I get an error: The data type numeric and int are incompatible in the '^' operator. I'm using numeric. Doesn't this way work on numeric or float? $\endgroup$
    – Armin
    Commented Mar 12, 2019 at 12:17
  • $\begingroup$ This is not actual syntax. You have to use POWER function for calculation of square. docs.microsoft.com/en-us/sql/t-sql/functions/… $\endgroup$ Commented Mar 12, 2019 at 12:28
  • $\begingroup$ Is this right? select Col1 , Power((Col1 - 5), 2) as mse_loss from MYTABLE $\endgroup$
    – Armin
    Commented Mar 12, 2019 at 13:09
  • $\begingroup$ I have tested this way and I didn't get a strong answer from it. $\endgroup$
    – Armin
    Commented Mar 12, 2019 at 16:13
  • $\begingroup$ In the given example, row 1 has least loss (losses are 5.1 , 68.9 and 35.5 for rows 1 , 2 and 3). Can you post data where you did not get good results. $\endgroup$ Commented Mar 13, 2019 at 6:09
0
$\begingroup$

I'll use table-valued variables because they're easier to use in a demo. This will work exactly the same with temporary tables or proper tables.

declare @Data table(
    Col1 numeric(5, 2),
    Col2 numeric(5, 2),
    Col3 numeric(5, 2),
    Col4 numeric(5, 2)
);
insert @Data
values
    ( 5, 6  , 7,   8),
    (12, 6  , 5,   6),
    ( 2, 3.5, 6,   1),
    ( 1, 4  , 5, 500);

declare @Probe table(
    Col1 numeric(5, 2),
    Col2 numeric(5, 2),
    Col3 numeric(5, 2),
    Col4 numeric(5, 2)
);
insert @Probe
values
    (4, 5.75, 7.2, 6);

select
    d.Col1,
    d.Col2,
    d.Col3,
    d.Col4,
    Comparison =ABS(d.col1 - p.Col1) +
                ABS(d.Col2 - p.Col2) +
                ABS(d.Col3 - p.Col3) +
                ABS(d.Col4 - p.Col4)
from @Data as d
cross apply @Probe as p
order by Comparison;

I put the sought-for values in a table (@Probe) because that is more generally useful than hard-coding values in the query.

You don't specify what your distance function is so I've used a simple difference. Any other calculation can be substituted for the definition of column "Comparison".

CROSS APPLY says "for each row from the left side (i.e. @Data) use the row from the right side (i.e. @Probe)". It also works if the right-side refers to a T-SQL function which would allow for more complex calculation. Since the sought-for values are a single row a CROSS JOIN would work just as well here.

Finally the ORDER BY sorts the output by closeness with the closest at the head of the list. If two data rows produce the same Comparison value it is indeterminate which will be at the head of the list.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.