MYSQL QUERY用平均值替换行中的NULL值 [英] MYSQL QUERY replace NULL value in a row with average values

问题描述

我正在使用mysql数据库存储大量的卫星数据，这些数据集具有许多数据空白. 我想在该点附近将NULL值替换为1小时(或更少)的平均值. 到目前为止，我已经找到了如何将NULL值替换为以前的已知值:

I'm using a mysql database to store huge amount of satellite data, and these datasets has many data-gaps. I would like to replace the NULL values with an 1 hour(or less) average around that point. So far I've found how to replace the NULL value with the previous known value:

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

来自此帖子:

from this post: SQL QUERY replace NULL value in a row with a value from the previous known value

我的桌子看起来像

+---------------------+--------+
| 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值. 例如，我想替换，

I'd like to replace the NULL values with average values around that datetime. For instance I'd like to replace ,

| 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';

保罗

推荐答案

我承认，这不是最优雅的方法，但是它应该可以为您带来想要的东西.

Not the most elegant I admit but it should get you what you want.

我不确定您要如何处理那些小时平均值为NULL的NULL值.在下面的示例中，这些值将更新为-1.

I'm not sure how you want to handle those NULL values that have an hour average of NULL. In the example below these will get updated to -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;

这篇关于MYSQL QUERY用平均值替换行中的NULL值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！