如何在SQL Server或ClickHouse中实现数值范围到数值的映射

嘿，这个问题我太熟了——用CASE WHEN堆一堆判断确实不现实，尤其是区间数量多到离谱的时候。给你几个通用的SQL解决方案，不管你用MySQL、PostgreSQL还是SQL Server基本都能直接用：

1. 通用取整公式（跨数据库兼容） #

核心思路是利用步长取整：你的区间步长是0.01，每个区间的中心x是0.01的整数倍。我们可以把数值偏移0.005后，按0.01步长取整，刚好对应到你的区间规则。

公式：

FLOOR((原始数值 + 0.005)/0.01)*0.01

举个实际查询的例子，假设你的表是sensor_data，存储原始浮点值的字段是reading：

SELECT
  reading,
  FLOOR((reading + 0.005)/0.01)*0.01 AS display_value
FROM sensor_data;

原理验证： #

• 区间[0.115, 0.125)内的数值：(0.115+0.005)/0.01=12，取整后乘0.01得到0.12；(0.124999+0.005)/0.01=12.9999，取整后是12，乘0.01仍为0.12，完全符合区间规则。
• 数值0.175：(0.175+0.005)/0.01=18，取整后乘0.01得到0.18，完美匹配你的区间划分。

2. 数据库专属截断函数（更简洁） #

不同数据库有专门的截断小数位函数，结合偏移量可以更直观实现需求：

MySQL #

用TRUNCATE函数直接截断到两位小数：

SELECT
  reading,
  TRUNCATE(reading + 0.005, 2) AS display_value
FROM sensor_data;

SQL Server #

用ROUND函数的第三个参数（设为1表示截断，而非四舍五入）：

SELECT
  reading,
  ROUND(reading + 0.005, 2, 1) AS display_value
FROM sensor_data;

PostgreSQL #

用TRUNC函数：

SELECT
  reading,
  TRUNC(reading + 0.005, 2) AS display_value
FROM sensor_data;

3. 浮点精度避坑提示 #

如果你的原始数值是高精度float类型，可能会出现微小的精度误差（比如0.125被存储为0.12499999999999999），这时候可以先转换为DECIMAL类型再计算，避免错误映射：

-- 以通用公式为例
SELECT
  reading,
  FLOOR((CAST(reading AS DECIMAL(10,5)) + 0.005)/0.01)*0.01 AS display_value
FROM sensor_data;

这些方法的优势是：不需要维护大量的区间判断逻辑，数据量越大性能优势越明显，而且能自动适配所有符合步长规则的区间。

内容的提问来源于stack exchange，提问作者Paniz Asghari