根据“最接近"的匹配“较早"的行替换当前行中的NULL值. (基于日期的表) [英] Replacing NULL value in current row based on 'closest' matching 'earlier' row. (date based table)

问题描述

我有以下mysql表

+---------------------+------+
| time                | val  |
+---------------------+------+
| 2005-02-03 00:00:00 | 2.11 |
| 2005-02-04 00:00:00 | 2.11 |
| 2005-02-05 00:00:00 | NULL |
| 2005-02-06 00:00:00 | NULL |
| 2005-02-07 00:00:00 | 3.43 |
| 2005-02-08 00:00:00 | NULL |
| 2005-02-09 00:00:00 | NULL |
| 2005-02-10 00:00:00 | 5.66 |
| 2005-02-11 00:00:00 | 5.66 |
| 2005-02-12 00:00:00 | NULL |
+---------------------+------+

我想创建一个算法(在PHP中)，该算法基于最后一个非null值填充NULL值.因此该表将变为以下

I want to create an algorithm (in PHP) that fill the NULL values based on the last non-null value. So the table will become the following

+---------------------+------+
| time                | val  |
+---------------------+------+
| 2005-02-03 00:00:00 | 2.11 |
| 2005-02-04 00:00:00 | 2.11 |
| 2005-02-05 00:00:00 |>2.11 |
| 2005-02-06 00:00:00 |>2.11 |
| 2005-02-07 00:00:00 | 3.43 |
| 2005-02-08 00:00:00 |>3.43 |
| 2005-02-09 00:00:00 |>3.43 |
| 2005-02-10 00:00:00 | 5.66 |
| 2005-02-11 00:00:00 | 5.66 |
| 2005-02-12 00:00:00 |>5.66 |
+---------------------+------+

我正在寻找有关如何处理这种情况的线索.我正在使用PHP-Laravel.

I'm looking for clues on how to approach this situation. I'm using PHP-Laravel.

这里有一个 SQLFiddle ，用于标准" SQL.

There is an SQLFiddle here for 'standard' SQL.

推荐答案

如注释所示，填充表格时应解决此问题.也就是说，可以在PHP或MySQL中完成.这是一个选择:

As comments indicate, you should be fixing this when you populate the table. That said, it can be done in PHP or MySQL. Here is one option:

SET @x:=0;
SELECT `time`, IF(val IS NOT NULL, @x:=val, @x) AS val
FROM yourtable
ORDER BY `time`;

请记住，您的结果将根据订购和WHERE等而改变.对于第一行具有NULL值的情况，使用SET @x:=0;定义默认值.

Bear in mind that your result will change depending on ordering and WHERE and so on. Use SET @x:=0; to define your default value for cases when first row has NULL value.

如果您需要永久性地修复数据，而不是针对单个查询，则可以使用正确"的值更新表:

If you need to fix the data permanently, rather than for single query, you can update the table with 'correct' values:

SET @x:=0;
UPDATE yourtable SET val=IF(val IS NOT NULL, @x:=val, @x) 
 ORDER BY `time`;

这篇关于根据“最接近"的匹配“较早"的行替换当前行中的NULL值. (基于日期的表)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！