为不可校准传感器添加修正因子的SQL实现方案咨询

方法1：直接解析现有修正因子字段 #

通过字符串函数提取运算符和数值，结合CASE语句实现动态计算，适配你当前的表结构：

SELECT
  sd.id AS sensorid,
  sd.unittype,
  sd.`时间`,
  sd.`数值`,
  CASE
    -- 无修正（修正因子为0/空）时返回原始值
    WHEN sm.`修正因子` IN ('0', '') OR sm.`修正因子` IS NULL THEN sd.`数值`
    -- 加法修正：提取+后的数值并计算
    WHEN LEFT(sm.`修正因子`, 1) = '+' THEN sd.`数值` + CAST(SUBSTRING(sm.`修正因子`, 2) AS DECIMAL(10,2))
    -- 乘法修正：提取*后的数值并计算
    WHEN LEFT(sm.`修正因子`, 1) = '*' THEN sd.`数值` * CAST(SUBSTRING(sm.`修正因子`, 2) AS DECIMAL(10,2))
    -- 格式不匹配时返回原始值
    ELSE sd.`数值`
  END AS realvalue
FROM
  telldus.sensorData sd
LEFT JOIN sensormeta sm ON sd.id = sm.id

关键逻辑说明：

• LEFT(sm.修正因子, 1)：提取修正因子的第一个字符作为运算符
• SUBSTRING(sm.修正因子, 2)：截取运算符后的数值部分，通过CAST转为小数类型
• CASE分支覆盖无修正、加法、乘法三种场景，兼容异常格式

方法2：优化表结构（推荐） #

将运算符和数值拆分存储，更利于数据校验和查询性能，修改sensormeta表结构：

• 添加operator字段：枚举类型（可选值：'NONE'、'+'、'*'）
• 添加modifier_value字段：DECIMAL(10,2)类型存储修正数值

优化后的查询SQL更简洁：

SELECT
  sd.id AS sensorid,
  sd.unittype,
  sd.`时间`,
  sd.`数值`,
  CASE
    WHEN sm.operator = 'NONE' OR sm.modifier_value = 0 THEN sd.`数值`
    WHEN sm.operator = '+' THEN sd.`数值` + sm.modifier_value
    WHEN sm.operator = '*' THEN sd.`数值` * sm.modifier_value
    ELSE sd.`数值`
  END AS realvalue
FROM
  telldus.sensorData sd
LEFT JOIN sensormeta sm ON sd.id = sm.id

注意事项 #

1. 若需支持减法（-N）或除法（/N），只需在CASE语句中添加对应分支即可
2. CAST的精度（如DECIMAL(10,2)）可根据你的传感器数值精度调整
3. 原SQL中的GROUP BY语句可能无需保留，因为sensorData的每条记录对应唯一时间点数据，分组会导致数据丢失；若存在重复记录需聚合，需调整分组字段包含所有查询字段

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