SQL Server分开的重叠日期 [英] SQL Server separate overlapping dates

问题描述

我有一个包含以下数据的表：

I have a table with the following data:

如果类型的日期重叠，我想在重叠期间返回单独的一行，因此得到以下结果：

If the dates overlap for a Type, I want to return a separate row for the overlapping period so end up with the following results:

推荐答案

请考虑将范围视为几何线，然后使用SQL Server的几何工具。

Consider treating the ranges as geometric lines and then using SQL Server's geometry tools.

首先，我需要将您的数据放入一个我可以使用的表中：

First, I need to get your data into a table I can work with:

declare @spans table (
    type char(1),
    start datetime,
    stop datetime
);

insert @spans values 
    ('S', '2010-01-01', '2010-01-14'),
    ('S', '2010-01-10', '2010-01-31'),
    ('A', '2010-01-05', '2010-01-30'),
    ('A', '2010-01-24', '2010-02-06'),
    ('T', '2010-01-20', '2010-01-27'),
    ('T', '2010-01-28', '2010-01-30');

不幸的是，SQL Server的空间工具在一方面受到限制。当您需要查询几何值中的单个形状或点时，必须传递索引号才能获取它。在Microsoft推出输出所有形状的TVF之前，我们一直使用数字表来完成工作。

Unfortunately, SQL Server's spatial tools are limited on one respect. When you need to query individual shapes or points inside a 'geometry' value, you have to pass the index number to get it. Until microsoft comes out with a TVF that outputs all the shapes, we resort to a numbers table to get the job done.

用您喜欢的方法来创建数字表：

Swap this out with your preferred method to create a numbers table:

declare     @numbers table (i int);
insert      @numbers    
select      i = row_number() over (order by (select null))
from        @spans a, @spans b;

现在您已准备好进行主查询。

Now you're ready for the main query.

在 geoAggregates中：

In 'geoAggregates':

• 我将日期转换为浮点值。我在停止值
  中添加一个以使几何在实数空间中工作，而不是在单元之间没有值的
  整数空间中。


• 我将浮点值转换为几何点


• 我从这些点中划出线，并且还将点
  合并到一个称为分隔线的几何空间中


• 我汇总行和汇总拆分器。对于
  行，如果它们重叠，则将它们合并到一个范围中。对于拆分器，
  会将它们进一步压缩为一个集合。

• I convert the dates to float values. I add one to the stop value to make the geometry work in the real-number space, as opposed to 'integer space' where there are no values between units.
• I convert to float values to geometric points
• I make lines out of these points, and I also hold the points together into a single geometric space called 'splitters'
• I aggregate the lines and I aggregate the splitters. For the lines this merges them into one range if they overlap. For the splitters, it just further condenses them into one collection.

在外部查询中：

• 我用分割线分割线。拆分器需要一个缓冲区
  来为其提供非零长度，以便实际执行拆分。


• 我将分割行的集合提取到单独的行中


• 我将行封装起来以确保它们仅由
  端点表示。


• 我解析端点以获取浮点值，撤消添加值
  的止损，然后四舍五入以撤消缓冲效果（以及浮点
  的存储效果）。


• 我将这些浮点值转换回日期表示形式。

• I split the lines with the splitters. The splitters need a buffer to give them non-zero length in order to actually perform the split.
• I extract the collections of splitted lines into individual lines
• I envelope the lines to ensure they are only represented by their endpoints.
• I parse the endpoints to get the float values, undo the added value for stop, and round these to undo the buffer effects (and the float storage effects).
• I convert these float values back into date representations.

此处是代码。它将按您的期望输出，除了在 A的第二行中键入o之外。

Here' the code. It outputs as you expect, except for your type-o in the second row of 'A'.

with

    geoAggregates as (

        select      type,
                    lines = geometry::UnionAggregate(line),
                    splitters = geometry::UnionAggregate(splitters)
        from        @spans
        cross apply (select 
                        startF = convert(float, start),
                        stopF = convert(float, stop) + 1
                    ) prepare
        cross apply (select 
                        startP = geometry::Point(startF, 0, 0),
                        stopP = geometry::Point(stopF, 0, 0)
                    ) pointify
        cross apply (select 
                        line = startP.STUnion(stopP).STEnvelope(),
                        splitters = startP.STUnion(stopP)
                    ) lineify
        group by    type 

    )

    select      type, 
                start,
                stop 
    from        geoAggregates ga
    cross apply (select 
                    splitted = ga.lines.STDifference(splitters.STBuffer(0.001))
                ) sp
    join        @numbers n on n.i between 1 and sp.splitted.STNumGeometries()
    cross apply (select 
                    line = sp.splitted.STGeometryN(i).STEnvelope()
                ) l
    cross apply (select 
                    start = convert(datetime, round(l.line.STPointN(1).STX,0)),
                    stop = convert(datetime, round(l.line.STPointN(3).STX - 1,0)) 
                ) dateify
    order by    type, start;

这篇关于SQL Server分开的重叠日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！