MySQL: Matching inexact values using "ON"

Question

I'm way out of my league here...

I have a mapping table (table1) to assign particular values (value) to a whole number (map_nu). My second table (table2), is a collection of averages (avg) for each user (user_id).

(I couldn't figure out how to properly make a markdown table, please feel free to edit!)

table1:          table2:
(value)(Map_nu)  (user_id)(avg)
----             -----
1      1         1   1.111
1.045   2        2   1.2
1.09    3        3   1.33333
1.135   4        4   1
1.18    5        5   1.389
1.225   6        6   1.42
1.27    7        7   1.07
1.315   8
1.36    9
1.405   10

The value Map_nu is a special number that each user gets assigned according to their average. I need to find a way to match the averages from table2 to the closest value in table1. I only need to match to the 2 digit past the decimal, so I've added the Truncated function

SELECT table2.user_id, map_nu
FROM `table1`
JOIN table2 ON TRUNCATE(table1.value,2)=TRUNCATE(table2.avg,2)

I still miss the values that don't match the averages exactly. Is there a way to pick the nearest truncated value or even to round to the second decimal? Rounding up/down wont matter as long as its applied to all values the same.

I am trying to have the following result (if rounded up):

(user_id)(Map_nu)  
----
1   4
2   6
3   6
4   1
5   10
6   11
7   3

Thanks!

Answer 1

i think you might have to do this in 2 separate queries. there is no 'nearest' operator in sql, so you can either calculate it in your software, or you could use

select map_nu from table1 ORDER BY abs(value - $avg) LIMIT 1

inside a loop. however, that cannot be used as a join function as it requires the ORDER and LIMIT which are not valid as joins.

another way of looking at it is it seems that your map_nu and value are deterministic in relation to each other - value = 1 + ((map_nu - 1) * 0.045) - so maybe you could make use of that fact and calculate an integer based on that equation? assuming that relationship holds true for all values of map_nu.

Answer 2

This is an awkward database design. What is the data representing and what are you trying to solve? There might be a better way.

Answer 3

Maybe do something like...

SELECT a.user_id, b.map_nu, abs(a.avg - b.value)
FROM
table2 a
join table1 b
left join table1 c on abs(a.avg - b.value) > abs(a.avg - c.value)
where c.value is null
order by a.user_id

Doesn't actually produce the same output as the one you were expecting for (doesn't do any rounding). Though you should be able to tweak it from there. Above query will produce the output below (w/ data you've provided):

user_id  map_nu  abs(a.avg - b.value)
-------  ------  --------------------
      1       3    0.0209999999999999
      2       5                  0.02
      3       8               0.01833
      4       1                     0
      5      10                 0.016
      6      10    0.0149999999999999
      7       3                  0.02

Beware though if you're dealing with large tables. Evaluate the explain of the above query if it'll be practical to run it within MySQL or if better to be done outside it.

Note 2: Will produce duplicate rows if there are avg values that are equi-distant to value values within table1 (Ex. if value for map_nu's 11 and 12 are 2 and 3 and someone get's an avg of 2.5). Your question doesn't really specify what to do for that so you might want to take that into account.

Answer 4

Its taking a little extra work, but I figure the easiest way to get my results will be to map all values to the second decimal place in table1:

1      1
1.01    1
1.02    1
1.03    1
1.04    1
1.05    2
1.06    2
1.07    2
1.08    2
1.09    3
1.1     3
1.11    3
1.12    3
1.13    3
1.14    4
...

Thanks for the suggestions! Sorry I couldn't present the question more clear.