tsql 中的间隔重叠

发布于 2024-08-26 13:29:18 字数 1025 浏览 9 评论 0原文

我需要获取分割间隔和重叠间隔的数量，例如

基于数据：

interval A: startTime 08:00, endTime 12:00
interval B: startTime 09:00, endTime 12:00
interval C: startTime 12:00, endTime 16:00
interval D: startTime 13:00, endTime 14:00

现在我有一个从 10:00 到 15:00 的单独间隔，并且必须首先确定哪些间隔相交。结果应该是这样的：

1: 10:00 - 12:00 ( intersecting with interval A )
2: 10:00 - 12:00 ( intersecting with interval B )
3: 12:00 - 15:00 ( intersecting with interval C )
4: 13:00 - 14:00 ( intersecting with interval D )

这部分工作正常，以下原因导致了麻烦：

我需要对并行间隔进行某种加权。这也意味着，如果某个区间交集（部分）与另一个区间交集（部分）相交，则可能会出现该区间交集必须被分割 n 次的情况。

在上面的示例中，预期结果是：

1: 10:00 - 12:00 -> weightage: 50%
2: 10:00 - 12:00 -> weightage: 50%

3.1: 12:00 - 13:00 -> weightage: 1oo%
3.2: 13:00 - 14:00 -> weightage: 50%
3.3: 14:00 - 15:00 -> weightage: 50%

4: 13:00 - 14:00 -< weightage: 100%

间隔 3 的分裂是由于与间隔 4 在 13:00 和 14:00 之间相交而引起的。

sql-server 是 ms-sql 2008。

提前感谢您的帮助！

原文

i need to get splited intervals and the number of overlapping intervals, eg

basedata:

interval A: startTime 08:00, endTime 12:00
interval B: startTime 09:00, endTime 12:00
interval C: startTime 12:00, endTime 16:00
interval D: startTime 13:00, endTime 14:00

now i have a separate interval from 10:00 to 15:00 and have to determine what intervals are intersected at first. result should be something like:

1: 10:00 - 12:00 ( intersecting with interval A )
2: 10:00 - 12:00 ( intersecting with interval B )
3: 12:00 - 15:00 ( intersecting with interval C )
4: 13:00 - 14:00 ( intersecting with interval D )

this part works fine, the following causes the trouble:

i need some kind of weighting for parallel intervals. this also means, that it can occur that an interval-intersection must be splitted n times, if it's ( partly ) intersected by another one.

in the upper example the expecting result would be:

1: 10:00 - 12:00 -> weightage: 50%
2: 10:00 - 12:00 -> weightage: 50%

3.1: 12:00 - 13:00 -> weightage: 1oo%
3.2: 13:00 - 14:00 -> weightage: 50%
3.3: 14:00 - 15:00 -> weightage: 50%

4: 13:00 - 14:00 -< weightage: 100%

the splitting of interval 3 is caused by the intersecting with interval 4 between 13:00 and 14:00.

sql-server is ms-sql 2008.

thanks for help in advance!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

土豪我们做朋友吧 2024-09-02 13:29:18

如果我理解您想要正确执行的操作，那么您的预期结果不应该是

1: 10:00 - 12:00 -> weightage: 50%
2: 10:00 - 12:00 -> weightage: 50%

3.1: 12:00 - 13:00 -> weightage: 1oo%
3.2: 13:00 - 14:00 -> weightage: 50%
3.3: 14:00 - 15:00 -> weightage: 50%

4: 13:00 - 14:00 -< weightage: 50%

因为 13:00-14:00 使用了两次吗？

If I understand what you're trying to do correctly, shouldn't your expected result be

1: 10:00 - 12:00 -> weightage: 50%
2: 10:00 - 12:00 -> weightage: 50%

3.1: 12:00 - 13:00 -> weightage: 1oo%
3.2: 13:00 - 14:00 -> weightage: 50%
3.3: 14:00 - 15:00 -> weightage: 50%

4: 13:00 - 14:00 -< weightage: 50%

since 13:00-14:00 is used twice?

回复收藏 0 原文

~没有更多了~