按顺序组织/插入日期的算法 [英] Algorithm to organize/insert dates sequentially

问题描述

我需要构建一个算法，该算法可以查看其中包含重叠日期的X个记录，并按时间顺序组织和重新插入这些日期值。

例如：

表中有这样的记录：



开始日期结束日期值

1/1/2019 12/31/2099 10



当插入这样的新记录时，

5/1 / 2019年5月31日25



算法应该组织这样的记录并将其插入新表：

1/1 / 2019 4/30/2019 10

5/1/2019 5/31/2019 25

6/1/2019 12/31/2099 10



不应修改原始表格，即不对原始表格中的记录进行重新插入/重新排序。



任何提示在最有效的方式来实现这一目标？不寻找特定于数据库的解决方案..仅限编程。

谢谢！



我尝试过：



尝试过白板，但是在接近可能的解决方案之前，我似乎最终通过了太多的数据传递。我不确定我是否错过了一个更明显的算法。

解决方案

不确定我是否正确理解了这个问题，如果这是关于RDBMS，但是...... 。



在大多数情况下，数据不会在表格中排序，而是在提取时排序。 DBMS重用可用空间，并且一些DBMS可能在需要时重新定位行，因此根据解决方案排序可能无法在物理层上进行。此外，无法使用物理订单获取数据。



根据您的示例，为什么不简单地使用 ORDER BY 子句。例如

  SELECT  ... 
  FROM  ... 
  ORDER   BY  StartDate，EndDate 

Hi,
I need to build an algorithm that can look at X number of records which have overlapping dates in them and organize and re-insert those date values in a chronological sequence.
E.g.:
The table has a record like this:

Start Date End Date Value
1/1/2019 12/31/2099 10

When a new record like this is inserted,
5/1/2019 5/31/2019 25

The algorithm should organize the records like this and insert it into a new table:
1/1/2019 4/30/2019 10
5/1/2019 5/31/2019 25
6/1/2019 12/31/2099 10

The original table should not be modified, i.e. no reinserting/reordering of records in that original table.

Any tips on the most efficient way to accomplish this? Not looking for database specific solutions.. programming only.
Thanks!

What I have tried:

Tried whiteboarding it, but it seemed like i'm ending up with way too many passes thru the data before coming close to a possible solution. Wasn't sure if I was missing a more obvious algorithm out there.

解决方案

Not sure if I understand the question correctly and if this is about RDBMS, but...

In majority of cases the data is not sorted in the table, but sorted upon fetching. The DBMS reuses free space and some DBMS may relocate rows when needed so depending on the solution sorting may be impossible to do on a physical level. Also there is no way to fetch the data using the physical order.

Based on your example, why not simply use ORDER BY clause using two fields. For example

SELECT ...
FROM ...
ORDER BY StartDate, EndDate

这篇关于按顺序组织/插入日期的算法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！