I made a MySQL function which almost does what I want, but not quite.
I make a user variable:
SELECT @start := price FROM prices_up_down WHERE id = 1;
Then UPDATE:
UPDATE prices_up_down SET signal_ = comparePrice(price), start_value = @start;
The variable @start should have 2 decimal places , but I just end up with .00
I don’t know why the decimal places are truncated. Anyone have an idea?
For example, row id = 4 signal_ should be 0, but because @start gets truncated, I get a -1
How can I stop @start being truncated??
This is the output
| id | price | signal_ | start_value |
|---|---|---|---|
| 1 | 100.86 | 0 | 100.86 |
| 2 | 99.19 | -1 | 99 |
| 3 | 100.6 | 1 | 101 |
| 4 | 100.43 | -1 | 100 |
| 5 | 100.6 | 1 | 101 |
| 6 | 100.57 | 0 | 101 |
| 7 | 101.83 | 1 | 102 |
|  |
Here is the function, my first MySQL function. The function should check the column price. If there is a change, up or down, of 1 or more, the column signal_ should be modified to 1 if price increased, -1 if price decreased, 0 if the change was less than 1.
I am getting a warning:
Warning: #1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: ‘SET variable=expression, …’, or ‘SELECT expression(s) INTO variables(s)’.
DELIMITER //
CREATE FUNCTION comparePrice(p DECIMAL)
RETURNS INT DETERMINISTIC
BEGIN
DECLARE signalnum INT;
# @start is the first value in row 1 when we start
# if p > @start by 1 or more price has gone up
IF p - @start >= 1 THEN SET signalnum := 1, @start := p;
# if @start > p by 1 or more price has gone down
ELSEIF @start - p >= 1 THEN SET signalnum := -1, @start := p;
# if the change is < 1, signalnum = 0
ELSE SET signalnum := 0;
END IF;
RETURN signalnum;
END; //
DELIMITER ;