mysql特征缩放计算

2022-01-15 00:00:00 sql window-functions mariadb mysql feature-scaling

我需要制定一个 mysql 查询来选择以这种方式标准化的值:标准化 = (value-min(values))/(max(values)-min(values))我的尝试如下所示:

I need to formulate a mysql query to select values normalized this way: normalized = (value-min(values))/(max(values)-min(values)) My attempt looks like this:

select 
    Measurement_Values.Time, 
    ((Measurement_Values.Value-min(Measurement_Values.Value))/(max(Measurement_Values.Value)-min(Measurement_Values.Value))) 
from Measurement_Values  
where Measurement_Values.Measure_ID = 49 and Measurement_Values.time >= '2020-05-30 00:00'

但显然是错误的，因为它只返回一个值.你能帮我找到正确的语法吗?

but is obviously wrong as it returns only one value. Can you help me finding the correct syntax?

推荐答案

你的问题解释有点短，但我认为你需要窗口函数(仅在 MySQL 8.0 中可用):

Your question is a little short on explanations, but I think that you want window functions (available in MySQL 8.0 only):

select 
    time, 
    value,
    (value - min(value) over() / (max(value) over() - min(value) over()) normalized_value
from measurement_values  
where measure_id = 49 and time >= '2020-05-30 00:00'

或者，在早期版本中，您可以通过使用聚合查询连接表来获得相同的结果:

Or, in earlier versions, you can get the same result by joining the table with an aggregate query:

select 
    mv.time, 
    mv.value,
    (mv.value - mx.min_value) / (mx.max_value - mx.min_value) normalized_value
from measurement_values  
cross join (
    select min(value) min_value, max(value) max_value
    from measurement_values
    where measure_id = 49 and time >= '2020-05-30 00:00'
) mx
where measure_id = 49 and time >= '2020-05-30 00:00'

相关文章