mysql - 将行插入 MySQL 数据库

Question

我有这样形成的数据:

+----------+-------+-------+
| DAY      | VALUE | Name  |
+----------+-------+-------+
| 01/01/14 |  1030 |  BOB
| 01/02/14 |  1020 |  BOB
| 01/03/14 |  1080 |  BOB
| 01/04/14 |  1090 |  BOB
| 01/05/14 |  1040 |  BOB
| 01/08/14 |  1030 |  BOB
| 01/11/14 |  4030 |  BOB
| 01/12/14 |  5000 |  BOB
| 01/13/14 |  6000 |  BOB
| 01/14/14 |  1096 |  BOB
| 01/14/14 |  1200 |  MIKE
| 01/15/14 |  1040 |  MIKE
| 01/16/14 |  1600 |  MIKE
| 01/17/14 |  1070 |  MIKE
| 01/18/14 |  1340 |  MIKE
| 01/19/14 |  1060 |  MIKE
| 01/01/14 |  6000 |  JANE
| 01/02/14 |  1700 |  JANE
| 01/03/14 |  1070 |  JANE
| 01/04/14 |  8000 |  JANE
+----------+-------+------+

对于每个名称，需要有一行表示 01/01/14 到 02/01/14(1 个月)之间的日期。如您所见，Bob、Mike 和 Jane(尽管在我的真实数据库中有数千个名字)都缺少该时间段之间的日期。我想通过某种插值以某种方式插入缺失的行。例如，Bob 缺少 01/06/14 和 01/07/14。我希望它通过添加这两个日期进行插值，然后将值作为两个字段之间的平均值，这样这两个缺失的字段都将具有值 ((1040+1030)/2) = 1035。如果没有MIKE 之前的数据(从 01/14/14 开始)我希望所有新行现在都具有 01/14/14 值。我尝试了各种不同的技术，例如使用 coalesce 命令、游标，但无法使其正常工作。此外，我不打算拥有这些精确值，如果有某种可以插值的数学库，我也会对此持开放态度。谢谢。

Answer 1

您有两个问题，生成行和插入值。您可以使用此 SQL 生成行:

select d.day, n.name, t.value
from (select distinct name from table t) n cross join
     (select distinct day from table t) d left outer join
     table t
     on t.name = n.name and t.day = d.day;

做插值很麻烦。您可以使用变量和多重排序来做到这一点。这是逻辑:

select day, name, value, prev_value,
       @value as next_value,
       @value := if(@name = name and value is not null, value, @value),
       @name := name
from (select d.day, n.name, t.value,
             @value as prev_value,
             @value := if(@name = name and value is not null, value, @value),
             @name := name
      from (select distinct name from table t) n cross join
           (select distinct day from table t) d left outer join
           table t
           on t.name = n.name and t.day = d.day cross join
           (select @name := '', @value := NULL) vars
      order by n.name, d.day
     ) t cross join
     (select @name := '', @value := NULL) vars
order by n.name, d.day desc;

这可能对您有用，但这取决于 MySQL 在每个 select 中按顺序评估表达式(用于变量的分配)。您可以使语法更复杂来解决此问题，但这会隐藏逻辑。您现在可以实现您想要的逻辑:

select day, name,
       (case when value is not null then value
             when prev_value is not null and next_value is not null
             then (prev_value + next_value) / 2
             when prev_value is null then next_value
             else prev_value
        end) as value
from (<previous query here>) t;