离岛的SQL时间打包 [英] SQL Time Packing of Islands

问题描述

我有一个与此类似的sql表:

I have an sql table that has something similar to this:

EmpNo      StartTime               EndTime 
------------------------------------------
1          7:00                    7:30
1          7:15                    7:45
1          13:40                   15:00
2          8:00                    14:00
2          8:30                    9:00
3          10:30                   14:30

我看过很多例子，您可以找到所有内容之间的差距，很多例子中，您可以将所有内容打包在一起.但我希望能够按用户将其分开.

I've seen a lot of examples where you can find the gaps between everything, and a lot of examples where you can pack overlaps for everything. But I want to be able to separate these out by user.

可悲的是，我需要一个纯SQL解决方案.

Sadly, I need a pure SQL solution.

最终，我想返回:

EmpNo      StartTime               EndTime 
------------------------------------------
1          7:00                    7:45
1          13:40                   15:00
2          8:00                    14:00
3          10:30                   14:30

这似乎很简单，我只是花了最后一天试图弄清楚这一点，并提出了很少的建议.这里永远不会有任何列为NULL，并且您可以假设可能存在重复项或间隙​​为0.

It seems simple enough, I have just spent the last day trying to figure it out, and come up with very little. Never will any column here be NULL, and you can assume there could be duplicates, or gaps of 0.

我知道这是经典的孤岛问题，但是到目前为止，我看到的解决方案对保持单独的ID分组并不十分友好

I know this is the classic island problem, but the solutions I have seen so far aren't incredibly friendly with keeping separate ID's grouped

推荐答案

纯SQL"肯定会支持lag()，lead()和累积求和函数，因为它们是标准的一部分.这是使用标准SQL的解决方案:

"Pure SQL" would surely support the lag(), lead(), and cumulative sum functions because these are part of the standard. Here is a solution using standard SQL:

select EmpNo, min(StartTime) as StartTime, max(EndTime) as EndTime
from (select t.*, sum(StartGroup) over (partition by EmpNo order by StartTime) as grp
      from (select t.*,
                   (case when StartTime <= lag(EndTime) over (partition by EmpNo order by StartTime)
                         then 0
                         else 1
                    end) as StartGroup
            from table t 
           ) t
     ) t
group by EmpNo, grp;

如果您的数据库不支持这些功能，则可以使用相关的子查询来实现相同的逻辑.

If your database doesn't support these, you can implement the same logic using correlated subqueries.

这篇关于离岛的SQL时间打包的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！