MySQL-查找具有相同值的连续行块的开始和结束

Question

我需要提取和迁移值从一个表到另一个。源表包含一个特定的生效日期的sumarated值。如果一个值被更改，如果组件值上的数据从此生效日期开始有效，则会写入新行。

我现在需要将这些数据迁移到这样的新表中。我们的目标是，选择给定月份的数据，结果就是给定该月份的有效数据。

每行表示在一个月内有效的组件的值。我现在通过将其设置为参数来运行每个有效月份的插入更新。我插入值变化作为新的行到表中，通过使用唯一的键（entity_id，effective_date，component_type）防止重复

    SET @effective_date = '2020-01-01';
    INSERT INTO component_final 
       select NULL,
              source_id, 
              entity_id,
              effective_date,
              NULL,
              1,
              component_1 
       FROM component_source 
       WHERE effective_date = @effective_date 
       AND component_1>0;

迁移第一行后，应该是这样的结果

    SET @effective_date = '2020-05-01';
    INSERT INTO component_final 
       select NULL,
              source_id, 
              entity_id,
              effective_date,
              NULL,
              1,
              component_1 
       FROM component_source 
       WHERE effective_date = @effective_date 
       AND component_1>0;

迁移第二行后，应该是这样的结果

因此，如果将来有值变化，必须设定结束日期。

如果组件将来发生更改，我无法执行第二步，即更新数据。也许在插入具有相同实体和组件的新行后，可以将其作为触发器，但我无法使其工作。

一些想法？我想只在MySQL里面处理这个。

Answer 1

我知道核心问题是如何找到组件发生变化的source_ids（0意味着删除，所以我们不希望这些条目出现在结果中），以及如何同时分配相应的结束日期。为了便于说明，我把你的例子简化了一点：

• 只有一个组件类型（我考虑到可能会有连续的条目，其值不变）
• 只有一个实体_id，所以我们可以忽略它

将这个更简单的版本扩展到现实世界的问题应该很容易。

下面是一个示例输入：

Answer 2

您不需要表中的列enddatecomponent_final，因为它的值取决于同一表中的其他值：

SELECT
   id,
   source_id,
   entity_id,
   startdate,
   (  SELECT DATE_ADD(MIN(cf2.startdate),INTERVAL -1 DAY) 
      FROM component_final cf2 
      WHERE cf2.startdate > cf1.startdate
        AND cf2.source_id = cf1.source_id
        AND cf2.entity_id = cf1.entity_id
   ) as enddate,
   component_type,
   value
FROM component_final cf1;