mysql 最接近数字_将数值转换成最接近的某数的整数倍

Every once in a while, I find myself needing to round a number up to

the nearest even power of 10, or round a time interval to the nearest

quarter of an hour, or something like that. This is actually quite

simple, but for some reason I always find myself confused about how to

do it. I have to reason it out all over again, instead of just

remembering how to do it. Perhaps writing this blog post will help me

remember next time.

The basic idea for rounding to whole multiples is to divide the

number, losing precision. Then round, floor, or ceiling the resulting

number, and multiply to get back to the original magnitude. For

rounding to fractions, reverse the process: multiply, round and divide

again.

This actually works for any programming language, not just SQL. But I find myself doing it in SQL most often.

Here’s an example of how to turn a year into a decade:

mysql> SELECT FLOOR(YEAR(NOW()) / 10) * 10 AS decade;

+--------+

| decade |

+--------+

| 2000 |

+--------+

There are other ways to do this, of course. In this case, since the

original year is expressed in decimal notation, and we are rounding

down to the nearest power of 10, we could simply take the leftmost

three digits and add a zero. But that wouldn’t work if we were trying

to “snap” to the nearest five-year interval. The technique I showed

above does:

mysql> SELECT FLOOR(YEAR(NOW()) / 5) * 5 AS half_decade;

+-------------+

| half_decade |

+-------------+

| 2005 |

+-------------+

Let’s suppose we want to take an arbitrary number, and round it to

the nearest 1/8th. In this case, we need to divide by 1/8 and then

multiply by 1/8 again to get to the nearest fraction, because dividing

by eight and multiplying by eight would actually get us to the nearest

even power of eight. I’ll just select random numbers between zero and

100 from one of the system tables to illustrate:

mysql> SELECT ROUND((RAND() * 100) / .125) * .125 AS nearest_eighth> FROM mysql.help_topic LIMIT 10;

+----------------+

| nearest_eighth |

+----------------+

| 42.875 |

| 27.875 |

| 10.875 |

| 70.375 |

| 19.625 |

| 86.875 |

| 75.750 |

| 17.750 |

| 61.500 |

| 54.500 |

+----------------+

Of course, 1/8 is an easy number to write out in decimal: .125. It

would not be so easy to write out 1/14. So naturally, we can do this by

using inverses.

mysql> SELECT ROUND((RAND() * 100) * 14) / 14 AS nearest_14th> FROM mysql.help_topic LIMIT 10;

+--------------+

| nearest_14th |

+--------------+

| 88.0714 |

| 76.7857 |

| 19.6429 |

| 67.8571 |

| 80.2857 |

| 98.0714 |

| 49.2857 |

| 52.2143 |

| 13.3571 |

| 10.0000 |

+--------------+

I hope this was useful to you. I’m betting I’ll be referring back to

it myself the next time I need to round a number to the nearest

fraction or whole multiple of some other number.