MYSQL QUERY заменяет значение NULL в строке на средние значения

Question

Я использую базу данных MySQL для хранения огромного количества спутниковых данных, и эти наборы данных имеют много пробелов в данных. Я хотел бы заменить значения NULL средним значением за 1 час (или меньше) вокруг этой точки. Итак, я нашел, как заменить значение NULL предыдущим известным значением:

UPDATE mytable
SET number = (@n := COALESCE(number, @n))
ORDER BY date;

из этого поста: SQL QUERY заменяет значение NULL в строке значением из предыдущего известного значения

Мой стол выглядит

+---------------------+--------+
| date                | P_f    |
+---------------------+--------+
| 2001-01-01 20:20:00 |   1.88 | 
| 2001-01-01 20:25:00 |   NULL | 
| 2001-01-01 20:30:00 |   NULL | 
| 2001-01-01 20:35:00 |   1.71 | 
| 2001-01-01 20:40:00 |   NULL | 
| 2001-01-01 20:45:00 |   NULL | 
| 2001-01-01 20:50:00 |   NULL | 
| 2001-01-01 20:55:00 |  1.835 | 
| 2001-01-01 21:00:00 |  1.918 | 
| 2001-01-01 21:05:00 |  1.968 | 
| 2001-01-01 21:10:00 |  2.004 | 
| 2001-01-01 21:15:00 |  1.924 | 
| 2001-01-01 21:20:00 | 1.8625 | 
| 2001-01-01 21:25:00 |   1.94 | 
| 2001-01-01 21:30:00 | 2.0375 | 
| 2001-01-01 21:35:00 |  1.912 | 

Я хотел бы заменить значения NULL на средние значения в это время. Например, я хотел бы заменить,

| 2001-01-01 20:50:00 |   NULL |

со средним

select AVG(P_f) from table where date between '2001-01-01 20:30' and '2001-01-01 21:10';

Павел

Answer 1

Не самый элегантный, я признаю, но он должен получить то, что вы хотите.

Я не уверен, как вы хотите обрабатывать те значения NULL, которые имеют среднечасовое значение NULL. В приведенном ниже примере они будут обновлены до -1.

create table myTable
(myDate datetime not null,
P_f decimal(10,5) default null
);

insert into myTable(myDate,P_f) values ('2001-01-01 20:20:00',1.88);
insert into myTable(myDate,P_f) values ('2001-01-01 20:25:00',NULL);
insert into myTable(myDate,P_f) values ('2001-01-01 20:30:00',NULL);
insert into myTable(myDate,P_f) values ('2001-01-01 20:35:00',1.71);
insert into myTable(myDate,P_f) values ('2001-01-01 20:40:00',NULL);
insert into myTable(myDate,P_f) values ('2001-01-01 20:45:00',NULL);
insert into myTable(myDate,P_f) values ('2001-01-01 20:50:00',NULL);
insert into myTable(myDate,P_f) values ('2001-01-01 20:55:00',1.835);
insert into myTable(myDate,P_f) values ('2001-01-01 21:00:00',1.918);
insert into myTable(myDate,P_f) values ('2001-01-01 21:05:00',1.968);
insert into myTable(myDate,P_f) values ('2001-01-01 21:10:00',2.004);
insert into myTable(myDate,P_f) values ('2001-01-01 21:15:00',1.924);
insert into myTable(myDate,P_f) values ('2001-01-01 21:20:00',1.8625);
insert into myTable(myDate,P_f) values ('2001-01-01 21:25:00',1.94);
insert into myTable(myDate,P_f) values ('2001-01-01 21:30:00',2.0375);
insert into myTable(myDate,P_f) values ('2001-01-01 21:35:00',1.912);
insert into myTable(myDate,P_f) values ('2001-01-02 20:40:00',NULL);

-- Insert copy of null value P_f rows into myTable with 1 hour average about myDate 
insert into myTable
(myDate,P_f)
select t.myDate,ifnull((select avg(P_f) from myTable t1 where t1.myDate between t.myDate - interval 1 hour and t.myDate +interval 1 hour),-1) as hourAvg
from myTable t
where t.P_f is null;

-- delete rows where P_f is null
delete from myTable
where P_f is null;