使用分组子句时如何避免重叠日期范围? [英] How to avoid overlapping date ranges when using a grouping clause?

问题描述

我有一种情况，我需要找出值变化之间的时间跨度.我尝试了一个简单的 group by 子句，但它消除了重叠的更改.考虑以下示例:

I have a situation where I need to find time spans between value changes. I tried a simple group by clause but it eliminates overlapping changes. Consider the following example:

create table #items (
      code varchar(4)
    , class varchar(4)
    , txdate datetime
)

insert into #items (code, class, txdate) values ('A', 'C', '2010-01-01');
insert into #items (code, class, txdate) values ('A', 'C', '2010-01-02');
insert into #items (code, class, txdate) values ('A', 'C', '2010-01-03');
insert into #items (code, class, txdate) values ('A', 'D', '2010-01-04');
insert into #items (code, class, txdate) values ('A', 'D', '2010-01-05');
insert into #items (code, class, txdate) values ('A', 'C', '2010-01-06');
insert into #items (code, class, txdate) values ('A', 'C', '2010-01-07');
insert into #items (code, class, txdate) values ('A', 'D', '2010-01-08');
insert into #items (code, class, txdate) values ('A', 'D', '2010-01-09');

select code
, class
, min(txdate) mindate
, max(txdate) maxdate
from #items
group by code, class

这将返回以下结果(注意重叠的日期范围):

This returns the following results (notice the overlapping date ranges):

|code|class|mindate   |maxdate   |
----------------------------------
|A   |C    |2010-01-01|2010-01-07|
|A   |D    |2010-01-04|2010-01-09|

我希望查询返回以下内容:

I would like to have the query return the following:

|code|class|mindate   |maxdate   |
----------------------------------
|A   |C    |2010-01-01|2010-01-03|
|A   |D    |2010-01-04|2010-01-05|
|A   |C    |2010-01-06|2010-01-07|
|A   |D    |2010-01-08|2010-01-09|

有什么想法和建议吗?

推荐答案

按照@KM 的建议研究 SQL SERVER ISLANDS 后，我想出了以下查询代码被添加到数据集中.

After researching SQL SERVER ISLANDS as suggested by @KM, I came up with the following query which seems to work well when additional class codes are added to the dataset.

select a.code, a.class, a.txdate as mindate, b.txdate as maxdate
from (
    --Find minimum island
    select code
        , class
        , txdate
        , row_number() over (order by code, class, txdate) as n
    from #items tb1
    where not exists (
        select *
        from #items tb2
        where datediff(d, tb1.txdate, tb2.txdate) = -1      
          and tb1.class = tb2.class
          and tb1.code = tb2.code
    ) 
) as a
inner join (
    --Find maximum island 
    select code
        , class
        , txdate
        , row_number() over (order by code, class, txdate) as n
    from #items tb1
    where not exists (
        select *
        from #items tb2
        where datediff(d, tb1.txdate, tb2.txdate) = 1   
          and tb1.class = tb2.class
          and tb1.code = tb2.code
    ) 
) as b on a.n = b.n

这种方法的唯一警告是最小集合中的条目数需要与最大集合中的条目数相匹配.到目前为止，我还没有做任何会使这不正确的事情.但是，我没有测试空值或性能.

The only caveat to this approach is that the number of entries in the minimum set needs to match the number of entries in the maximum set. So far I have not been able to do anything that would make this not true. However, I did not test null values or performance.

这篇关于使用分组子句时如何避免重叠日期范围?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！