CODEWITHSUNDEEP
mysqlsql
hyspo
hyspo

Reputation: 29

SQL : I want to compare 10 consecutive value

I am trying to solve a problem with SQL.

I have a SQL database which has a table named "data", in this table you have a row named "points". There is like 10000 float values in this row.

I want to make a desktop application that can compare 10 consecutive values ( which i manually enter ) to his nearest 10 consecutive values in the database.

exemple :

i want to compare this list of 10 values ( that i enter ):

10.1 , 25.4, 2, 35, 45, 78.9, 41.1, 44, 1, 65

to the best list of 10 values in my database where the 10 values are the nearest to my 10 entered values ( IMPORTANT : VALUES HAVE TO BE CONSECUTIVE ).

You can see below what i want to do, i want to get the list of the 10 consecutive values that is the nearest to the 10 values i want to compare.

points ( 10000 values... )

points row : 10, 15.5, 14.3, 2, 1, 10.2, 55, 65.3, 41, 10, 25.2, 3, 34, 44, 78.8, 41.2, 41, 2, 66, 44, 25.1, 33.2, 45, 75, 98, 12, 11.2 etc etc

The 10 values in bold are the best nearest consecutive values:

10 is near to 10.1
25.2 is near to 25.4
3 is near to 2
34 is near to 35
44 is near to 45
78.8 is near to 78.9
41.2 is near to 41.1
41 is near to 44
2 is near to 1
66 is near to 65

Is there any way to do this with SQL Command ?

Thanks in advance.

Upvotes: 0

Views: 48

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

SQL tables represent unordered sets. You need a column to specify the ordering.

You can use lag() or lead() to bring 10 values together. Then you need a definition of closest. One possibility is to take the absolute value of the differences and add them up:

select t.*
from (select t.*,
             lead(val, 1) over (order by <ordercol>) as val_2,
             lead(val, 2) over (order by <ordercol>) as val_3,
             . . .
             lead(val, 9) over (order by <ordercol>) as val_10
      from t
     ) t
order by abs(val - $val_1) + abs(val_2 - $val_2) + . . .
limit 1;

The $val_1, $val_2, and so on represent the values that you are passing in.

The rest is just sorting and taking a limit.

Upvotes: 1

GMB
GMB

Reputation: 222472

One option uses a row-limiting correlated subquery:

select v.val,
    (select val from mytable t order by abs(t.val - v.val) limit 1) as match_val
from (
    select 10.1 as val
    union all select 25.4
    union all ...
) v

Basically the subquery executes for each row in the from clause (which is where you put the list of values): it scans the table, orders rows by the difference with the original value, and brings the value of top row only.

Upvotes: 2

Related Questions