就业史上的差距

Question

我有一个简单的雇佣历史数据库，我存储了条目id，用户id，他们的工作，开始和结束日期。可能会有一些重叠的条目。我需要确定在给定的时间段内，给定的时间范围内没有中断。

因此user_id 1的工作历史如下所示

Marketing - 01/01/2013 to 19/05/2013
Sales - 01/01/2011 to 01/05/2012
Admin - 01/01/2010 to 31/12/2010

我实际上将日期存储为unix时间戳。在三年的历史中，user_id 1在他们的销售工作和营销工作之间有超过6个月的休息。我如何将此标记给他们，以便他们可以提供此期间的条目。

Answer 1

这个问题比看起来更难，因为你可能在工作历史中有重叠。事实上，你可以将一段工作时间完全“放在”另一段时间内，或者在两端重叠。

你要找的是一个离开始日期有差距的EndDate，而且不是在另一个就业阶段。因此，让我们使用相关子查询来获取结束日期之后的下一个开始日期，如下所示：

     select t.*,
             (select StartDate
              from t t2
              where t2.user_id = t.user_id and
                    t2.StartDate > t.EndDate
              order by StartDate desc
              limit 1
             ) nextStartDate
      from t

现在，问题是:从开始的时间段是否被另一个时间段覆盖。为此，我将使用一个复杂的where子句：

select t.*
from (select t.*,
             (select StartDate
              from t t2
              where t2.user_id = t.user_id and
                    t2.StartDate > t.EndDate
              order by StartDate desc
              limit 1
             ) nextStartDate
      from t
     ) t
where not exists (select 1
                  from t t2
                  where t2.StartDate <= t.EndDate and
                        t2.EndDate >= t.nextStartDate
                 ) and
      (t.EndDate is null or t.EndDate > now())

根据您处理重叠的方式，您可能希望允许日期之间有几天的延迟。

这应该会返回表中有间隙的所有记录。在示例数据中，这将返回record today (2013-05-19)。“销售”记录有一个间隙，直到“营销”位置。"Marketing“将在未来返回，因为它将在过去结束。

实际上，一个间隙可能会导致返回多条记录。当存在重叠时可能会发生这种情况，我将仅用数字来解释：(0，10)，(5，12)，(15，20)。从12/13开始的差距将影响这两个记录。如果这是一个问题，那么查询是可以修复的，尽管我的第一个想法是如何修复它会使查询变得更加复杂。

假设您还将过滤用户in，您可以将其放在最外面的where子句中。

Answer 2

您将查找类似于以下内容的内容：

<?php
    $time_allowed = '1 month';

    $person = [
        [
            1305819929, // 19-05-11
            1337442341  // 19-05-12
        ],

        [
            1337442341, // 19-05-12
            1345933614  // 26-08-12
        ],

            // almost 4 month gap here - this will result in flagged() getting triggered

        [
            1355935614, // 19-12-12
            1368978787  // 19-05-13
        ]
    ];

    $time_allowed = strtotime('now') - strtotime("-{$time_allowed}");

    foreach($person as $key => $job)
    {
        if( ! isset($person[$key + 1]))
        {
            if($job[1] !== 'current' && (strtotime('now') - $job[1]) > $time_allowed)
            {
                flagged();
            }
        }
        else
        {
            if(($person[$key + 1][0] - $job[1]) > $time_allowed)
            {
                flagged();
            }
        }
    }

    function flagged()
    {
        // user flagged
    }

显然，将从数据库中获取$person。脚本应该是不言自明的，