mysql功能缩放计算 [英] mysql feature-scaling calculation
本文介绍了mysql功能缩放计算的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我需要制定一个mysql查询来选择以这种方式标准化的值:
normalized = (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'
这篇关于mysql功能缩放计算的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文