SQL query to make value monotonic?
I have a datasheet - a large one with electricity consumption figures.
Sometimes, due to a failure, the value is less than the previous record, which causes processing problems.
monday 143 kWh
tuesday 140 kWh *glitch*
wednesday 150 kWh
I would like to make the table monotonous. I am curious to see if there is an SQL query that sets each collapsed value to the previous largest value.
Can I do without PHP?
The table is in the following format (bit simplified):
CREATE TABLE IF NOT EXISTS `history` (
`day` int(11) NOT NULL,
`value` float NOT NULL
)
I know how to do this in PHP, line by line, but if there is a cleaner SQL-only solution, that would be great!
source to share
You want the sequence to be "monotonous". "Monotonous" means boring.
If you have a lot of data, the most efficient way is to use variables:
select h.day,
(@max := greatest(@max, h.value)
from history h cross join
(select @max := -1) params
order by h.day;
If you really want to update the values, you can do basically the same thing:
update history h
set value = (@max := greatest(coalesce(@max + 0, 0), h.value)
order by h.day;
Note that in this case @max
, the default is a string variable. You cannot have both order by
, and join
in the request update
. So, either define a variable just before update
or convert the string to a number a bit.
source to share