CODEWITHSUNDEEP
phpmysql
MightyPork
MightyPork

Reputation: 18861

SQL query to make value monotonic?

I have a data table - a large one, with electricity consumption values.

Sometimes, due to a glitch, the value is smaller than the previous record, which then causes problems when processing.

monday    143 kWh
tuesday   140 kWh *glitch*
wednesday 150 kWh

I'd like to make the table monotonic. I'm interested in finding out if there is an sql query that will set each glitched value to the previous greatest value.

Is this possible to do without PHP?

The table is in the following format (when simplified a bit):

CREATE TABLE IF NOT EXISTS `history` (
  `day` int(11) NOT NULL,
  `value` float NOT NULL
)

I know how to do it in PHP, row by row, but if there's a cleaner SQL-only solution, that'd be superb!

Upvotes: 3

Views: 621

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You want the sequence to be "monotonic". "Monotonous" means boring.

If you have a lot of data, then the most efficient way is using variables:

select h.day,
       (@max := greatest(@max, h.value)
from history h cross join
     (select @max := -1) params
order by h.day;

If you actually want to update the values, then 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 defaults to a string variable. You cannot have both an order by and join in a an update query. So, either define the variable just before the update, or do a bit of string-to-number conversion.

Upvotes: 1

Related Questions