我正在寻找一种方法来选择排名靠前的重叠段。表看起来像这样:

CODE   START                STOP                 RANK
shift  2016-07-20 05:00 AM  2016-07-20 08:00 AM  5
late   2016-07-20 05:00 AM  2016-07-20 05:08 AM  1
break  2016-07-20 06:00 AM  2016-07-20 06:30 AM  2

这就是我希望我的输出是:
CODE   START                STOP
late   2016-07-20 05:00 AM  2016-07-20 05:08 AM
shift  2016-07-20 05:08 AM  2016-07-20 06:00 AM
break  2016-07-20 06:00 AM  2016-07-20 06:30 AM
shift  2016-07-20 06:30 AM  2016-07-20 08:00 AM

因此,我几乎只想看看排名最高的部分对这个人的状态有何看法,但如果他们除了标准的“轮类”部分之外没有任何其他状态,那么只需表明他们正在轮类。

有道理吗?请排除任何问题或建议的解决方案。此刻我似乎什么也想不起来。我可以选择排名最高的分割市场,但不能在它们重叠时选择。

编辑: 正如您在我的 所需的 输出中看到的那样,从 05:00 AM 到 05:08 AM,类次段被具有更高排名的后期段覆盖(较低的数字意味着更高的排名,与通常的排名一样) ,但是从 05:08 AM 开始,因为没有覆盖它的段,我们回到默认的段从 05:08 AM 到 06:00 AM。

然后有一个从 06:00 AM 和 06:30 AM 开始的预定休息段,它再次覆盖了类次段。完成此操作后,当类次结束时,我们将返回到默认的类次类次,从 06:30 AM 到 08:00 AM。

我希望这是有道理的。

最佳答案

是的,一个 SQL 难题,我无法抗拒! :D

这是一种可能的解决方案。我手头没有 SQLServer(使用 my favorite database :)),但 SQL 应该主要是可移植的:

create or replace table ranges(
        code varchar,
        beg timestamp_ntz,
        end timestamp_ntz,
        rank integer);
insert into ranges values
        ('shift', '2016-07-20 05:00:00', '2016-07-20 08:00:00', 5),
        ('late',  '2016-07-20 05:00:00', '2016-07-20 05:00:08', 1),
        ('break', '2016-07-20 06:00:00', '2016-07-20 06:30:00', 2);

WITH PERIODS AS (
  select beg, lead(beg, 1) over (order by beg) AS end
  from (select beg from ranges union select end from ranges)
),
MATCHING_RANGES AS (
  select periods.beg, periods.end, ranges.code, ranges.rank
  from periods
  join ranges on (periods.beg >= ranges.beg and periods.end <= ranges.end)
  where periods.end is not null
),
RANKED_RANGES AS (
  select beg, end, code, row_number() over (partition by beg order by rank) in_period_rank
  from matching_ranges
)
select code, beg, end from ranked_ranges
where in_period_rank = 1
order by beg;

-------+---------------------+---------------------+
 CODE  |         BEG         |         END         |
-------+---------------------+---------------------+
 late  | 2016-07-20 05:00:00 | 2016-07-20 05:00:08 |
 shift | 2016-07-20 05:00:08 | 2016-07-20 06:00:00 |
 break | 2016-07-20 06:00:00 | 2016-07-20 06:30:00 |
 shift | 2016-07-20 06:30:00 | 2016-07-20 08:00:00 |
-------+---------------------+---------------------+

解释(我对原始表使用“范围”,对这些切片使用“句点”,就像您在输出中想要的那样):
  • 在 PERIODS 中,我们及时创建所有不同的时刻,并使用 LAG 找到下一个。输出是:
    ---------------------+---------------------+
             BEG         |         END         |
    ---------------------+---------------------+
     2016-07-20 05:00:00 | 2016-07-20 05:00:08 |
     2016-07-20 05:00:08 | 2016-07-20 06:00:00 |
     2016-07-20 06:00:00 | 2016-07-20 06:30:00 |
     2016-07-20 06:30:00 | 2016-07-20 08:00:00 |
     2016-07-20 08:00:00 | [NULL]              |
    ---------------------+---------------------+
    
  • 然后在 MATCHING_RANGES 中,对于每个“周期”,我们从定义的表中找到所有可能的范围(也删除最后一行,NULL),输出:
    ---------------------+---------------------+-------+------+
             BEG         |         END         | CODE  | RANK |
    ---------------------+---------------------+-------+------+
     2016-07-20 05:00:00 | 2016-07-20 05:00:08 | shift | 5    |
     2016-07-20 05:00:00 | 2016-07-20 05:00:08 | late  | 1    |
     2016-07-20 05:00:08 | 2016-07-20 06:00:00 | shift | 5    |
     2016-07-20 06:00:00 | 2016-07-20 06:30:00 | shift | 5    |
     2016-07-20 06:00:00 | 2016-07-20 06:30:00 | break | 2    |
     2016-07-20 06:30:00 | 2016-07-20 08:00:00 | shift | 5    |
    ---------------------+---------------------+-------+------+
    

    请注意如何创建与
  • 匹配的范围和周期的所有组合
  • 然后在每一行的 RANKED_RANGES 中,我们计算其在其周期内的优先级:
    ---------------------+---------------------+-------+----------------+
             BEG         |         END         | CODE  | IN_PERIOD_RANK |
    ---------------------+---------------------+-------+----------------+
     2016-07-20 05:00:00 | 2016-07-20 05:00:08 | late  | 1              |
     2016-07-20 05:00:00 | 2016-07-20 05:00:08 | shift | 2              |
     2016-07-20 05:00:08 | 2016-07-20 06:00:00 | shift | 1              |
     2016-07-20 06:00:00 | 2016-07-20 06:30:00 | break | 1              |
     2016-07-20 06:00:00 | 2016-07-20 06:30:00 | shift | 2              |
     2016-07-20 06:30:00 | 2016-07-20 08:00:00 | shift | 1              |
    ---------------------+---------------------+-------+----------------+
    
  • 然后我们只需选择排名为 1 的行 :)
  • 关于sql - 选择排名靠前的重叠段 SQL,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/38483713/

    10-13 08:51