I have a table contains a few columns say: column_1, column_2 and column_3.
I appended an new column to the table called score. What I want to do is calculate the score based on these three columns and tune the parameters easily.
Say my score formula looks like below:
score = a * column_1 + b * column_2 + c * column_3
is it possible to create a udf or process(never used before) to easily do that?
so I have a function like getScore(a,b,c)
and I could do something like:
select
column_1,
column_2,
column_3,
getScore(0.5, 0.1, 0.4) as score
from table
or
update table set score = getScore(0.5, 0.1, 0.4)
Thanks!
解决方案
Yes.
CREATE FUNCTION `getScore`(`a` DECIMAL(12,4), `b` DECIMAL(12,4), `c` DECIMAL(12,4)) RETURNS DECIMAL(12,4)
BEGIN
RETURN a + b + c;
END
SELECT getScore(0.3, 0.4, 0.5)
-> 1.2000
But if you need some values from the table, you need to include those as parameters too.
SELECT getScore(column1, column2, column3, 0.5, 0.1, 0.4) AS score FROM table