按类型组合连续日期时间间隔

假设我们有这样一个表：

declare @periods table (
    s date, 
    e date,
    t tinyint
);

日期间隔无间隙，按开始日期排序

insert into @periods values
('2013-01-01' , '2013-01-02', 3),
('2013-01-02' , '2013-01-04', 1),
('2013-01-04' , '2013-01-05', 1),
('2013-01-05' , '2013-01-06', 2),
('2013-01-06' , '2013-01-07', 2),
('2013-01-07' , '2013-01-08', 2),
('2013-01-08' , '2013-01-09', 1);

所有日期间隔都有不同的类型 (t)。

需要组合相同类型的日期间隔，并且它们不会被其他类型的间隔打破（所有间隔均按开始日期排序）。

所以结果表应该是这样的：

      s     |      e     |  t
------------|------------|-----
 2013-01-01 | 2013-01-02 |  3
 2013-01-02 | 2013-01-05 |  1
 2013-01-05 | 2013-01-08 |  2
 2013-01-08 | 2013-01-09 |  1

有什么想法如何在没有光标的情况下做到这一点？

我有一个可行的解决方案：

declare @periods table (
    s datetime primary key clustered, 
    e datetime,
    t tinyint,
    period_number int   
);

insert into @periods (s, e, t) values
('2013-01-01' , '2013-01-02', 3),
('2013-01-02' , '2013-01-04', 1),
('2013-01-04' , '2013-01-05', 1),
('2013-01-05' , '2013-01-06', 2),
('2013-01-06' , '2013-01-07', 2),
('2013-01-07' , '2013-01-08', 2),
('2013-01-08' , '2013-01-09', 1);

declare @t tinyint = null;  
declare @PeriodNumber int = 0;
declare @anchor date;

update @periods
    set  period_number = @PeriodNumber, 
    @PeriodNumber = case
                        when @t <> t
                            then  @PeriodNumber + 1
                        else
                            @PeriodNumber
                    end,
    @t = t,
    @anchor = s
option (maxdop 1);

select 
    s = min(s),
    e = max(e),
    t = min(t)
from 
    @periods    
group by 
    period_number
order by 
    s;

但我怀疑我是否可以依赖 UPDATE 语句的这种行为？

我使用 SQL Server 2008 R2。

Edit:

感谢丹尼尔和这篇文章：http://www.sqlservercentral.com/articles/T-SQL/68467/ http://www.sqlservercentral.com/articles/T-SQL/68467/

我发现上述解决方案中遗漏了三个重要的事情：

1. 表上必须有聚集索引
2. 必须有锚变量和聚集列的调用
3. 更新语句应该由一个处理器执行，即没有并行性

我已根据这些规则更改了上述解决方案。

由于你的范围是连续的，所以问题本质上变成了间隙和岛屿 /questions/tagged/gaps-and-islands一。如果你有一个标准来帮助你区分具有相同序列的不同序列就好了t值，您可以使用该标准对所有行进行分组，然后只需取MIN(s), MAX(e)对于每个组。

获得这种标准的一种方法是使用两个ROW_NUMBER来电。考虑以下查询：

SELECT
  *,
  rnk1 = ROW_NUMBER() OVER (               ORDER BY s),
  rnk2 = ROW_NUMBER() OVER (PARTITION BY t ORDER BY s)
FROM @periods
;

对于您的示例，它将返回以下集合：

s           e           t   rnk1  rnk2
----------  ----------  --  ----  ----
2013-01-01  2013-01-02  3   1     1
2013-01-02  2013-01-04  1   2     1
2013-01-04  2013-01-05  1   3     2
2013-01-05  2013-01-06  2   4     1
2013-01-06  2013-01-07  2   5     2
2013-01-07  2013-01-08  2   6     3
2013-01-08  2013-01-09  1   7     3

有趣的是rnk1 and rnk2排名是，如果你从另一个中减去一个，你将得到的值，连同t, 唯一标识every具有相同的不同行序列t:

s           e           t   rnk1  rnk2  rnk1 - rnk2
----------  ----------  --  ----  ----  -----------
2013-01-01  2013-01-02  3   1     1     0
2013-01-02  2013-01-04  1   2     1     1
2013-01-04  2013-01-05  1   3     2     1
2013-01-05  2013-01-06  2   4     1     3
2013-01-06  2013-01-07  2   5     2     3
2013-01-07  2013-01-08  2   6     3     3
2013-01-08  2013-01-09  1   7     3     4

知道了这一点，您就可以轻松应用分组和聚合。最终查询可能如下所示：

WITH partitioned AS (
  SELECT
    *,
    g = ROW_NUMBER() OVER (               ORDER BY s)
      - ROW_NUMBER() OVER (PARTITION BY t ORDER BY s)
  FROM @periods
)
SELECT
  s = MIN(s),
  e = MAX(e),
  t
FROM partitioned
GROUP BY
  t,
  g
;

如果您愿意，可以使用此解决方案在 SQL 小提琴 http://sqlfiddle.com/#!3/01a4a/1.