组合/合并日期范围的算法

Question

我正在尝试找到如何将日期范围合并到一个数据库记录（数组元素）中的最佳方法。

这是我拥有的数据：

  Array
(
    [0] => Array
        (
            [id] => 18298
            [start_date] => 2011-07-09
            [end_date] => 2011-10-01
        )

    [1] => Array
        (
            [id] => 18297
            [start_date] => 2011-06-01
            [end_date] => 2011-06-30
        )

    [2] => Array
        (
            [id] => 17113
            [start_date] => 2011-03-31
            [end_date] => 2011-05-31
        )

    [3] => Array
        (
            [id] => 20555
            [start_date] => 2011-01-03
            [end_date] => 2011-03-31
        )
)

在我们将它们组合起来之后，数组（或数据库）应该如下所示：

Array
(
    [0] => Array
        (
            [merged_ids] => 18298
            [start_date] => 2011-07-09
            [end_date] => 2011-10-01
        )

    [1] => Array
        (
            [merged_ids] => 18297, 17113, 20555
            [start_date] => 2011-01-03
            [end_date] => 2011-06-30
        )
)

是否有任何算法可以遍历所有元素/范围并将它们组合起来？哪种方法更好/更容易实现 - 通过数据库（MYSQL）还是编码（PHP）？

任何建议都将受到高度赞赏。

谢谢！

更新：抱歉，我没有提供足够的信息：我们应该合并任何连续和重叠的日期范围。

Answer 1

按开始日期排序。

然后迭代并检查下一个项目的开始日期是在当前项目的结束日期之前还是之后。如果是，则将下一个合并到当前一个中。然后继续。

Answer 2

我编写了组合/合并范围列表的函数。它是用 Python 编写的，但用 PHP 重写应该很容易。这是完整的代码： https://gist.github.com/barszczmm/8447665 这是简化的算法（仍然是Python）：

list_of_ranges.sort() # sort input list
new_list_of_ranges = [] # output list

new_range_item_start = None
new_range_item_end = None

length = len(list_of_ranges)
for i, range_item in enumerate(list_of_ranges):
    if new_range_item_start is None:
        new_range_item_start = range_item[0]
        new_range_item_end = range_item[1]
    elif new_range_item_end >= range_item[0]:
        new_range_item_end = max(range_item[1], new_range_item_end)
    else:
        new_list_of_ranges.append((new_range_item_start, new_range_item_end))
        new_range_item_start = range_item[0]
        new_range_item_end = range_item[1]
    # save if this is last item
    if i + 1 == length:
        new_list_of_ranges.append((new_range_item_start, new_range_item_end))

Answer 3

实现就像：

function mergeDateTimeRanges($ranges)
{
    $retVal = [];
    //sort date ranges by begin time
    usort($ranges, function ($a, $b) {
        return strcmp($a['begin_at'], $b['begin_at']);
    });

    $currentRange = [];
    foreach ($ranges as $range) {
        // bypass invalid value
        if ($range['begin_at'] >= $range['end_at']) {
            continue;
        }
        //fill in the first element
        if (empty($currentRange)) {
            $currentRange = $range;
            continue;
        }

        if ($currentRange['end_at'] < $range['begin_at']) {
            $retVal[] = $currentRange;
            $currentRange = $range;
        } elseif ($currentRange['end_at'] < $range['end_at']) {
            $currentRange ['end_at'] = $range['end_at'];
        }
    }

    if ($currentRange) {
        $retVal[] = $currentRange;
    }

    return $retVal;
}

Answer 4

我的方法将生成一个合并数组，其中通过重叠或连续的日期分组在一起，并且组的 id 存储为逗号空格分隔值的字符串。

我使用现代的“太空飞船运算符”(<=>) 进行 usort() 的比较。如果您的代码运行在 php7 以下的版本上，您可以使用：

usort($array,function($a,$b){ return strcmp($a['start_date'],$b['start_date']); });

请参阅内联注释以获取分步说明。

代码：（演示）

function mergeRanges($array){
    usort($array,function($a,$b){ return $a['start_date']<=>$b['start_date']; });  // order by start_date ASC

    foreach($array as $i=>$row){
         if($i && $row['start_date']<=date('Y-m-d',strtotime("{$result[$x]['end_date']} +1 day"))){  // not the first iteration and dates are within current group's range
            if($row['end_date']>$result[$x]['end_date']){  // only if current end_date is greater than existing end_date
                $result[$x]['end_date']=$row['end_date'];  // overwrite end_date with new end_date in group
            }
            $result[$x]['merged_ids'][]=$row['id'];  // append id to merged_ids subarray
        }else{  // first iteration or out of range; start new group
            if($i){  // if not first iteration
                $result[$x]['merged_ids']=implode(', ',$result[$x]['merged_ids']);  // convert previous group's id elements to csv string
            }else{  // first iteration
                $x=-1;  // declare $x as -1 so that it becomes 0 when incremented with ++$x
            }
            $result[++$x]=['merged_ids'=>[$row['id']],'start_date'=>$row['start_date'],'end_date'=>$row['end_date']]; // declare new group
        }
    }
    $result[$x]['merged_ids']=implode(', ',$result[$x]['merged_ids']);  // convert final merged_ids subarray to csv string
    return $result;
}

$array=[
    ['id'=>18298,'start_date'=>'2011-07-09','end_date'=>'2011-10-01'],
    ['id'=>18297,'start_date'=>'2011-06-01','end_date'=>'2011-06-30'],
    ['id'=>17113,'start_date'=>'2011-03-31','end_date'=>'2011-05-31'],  // tests that 17113 and 18297 belong in same group
    ['id'=>20556,'start_date'=>'2011-02-03','end_date'=>'2011-02-13'],  // tests that "fully overlapped" date range is included
    ['id'=>20555,'start_date'=>'2011-01-03','end_date'=>'2011-03-31']
];

print_r(mergeRanges($array));

输出：

Array
(
    [0] => Array
        (
            [merged_ids] => 20555, 20556, 17113, 18297
            [start_date] => 2011-01-03
            [end_date] => 2011-06-30
        )

    [1] => Array
        (
            [merged_ids] => 18298
            [start_date] => 2011-07-09
            [end_date] => 2011-10-01
        )

)