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

Question

假设我们有这样一个表:

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。

编辑:

感谢 Daniel 和这篇文章: http://www.sqlservercentral.com/articles/T-SQL/68467/

我发现上面的解决方案中遗漏了三件重要的事情:

表

上必须有聚集索引

必须有 anchor 变量和聚集列的调用

更新语句应该由一个处理器执行，即没有并行性

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

Answer 1

由于您的范围是连续的，因此问题基本上变成了 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 的有趣事情和 rnk2排名是，如果你从另一个中减去一个，你会得到值，连同 t , 唯一标识具有相同 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
;

如果您愿意，可以使用此解决方案 at SQL Fiddle .