SQL Server 2012中的编号岛 [英] Numbering islands in SQL Server 2012

问题描述

我需要在SQL Server 2012中为孤岛编号.孤岛定义为一组行，其中同一ItemId中的DateFrom和DateTo之间没有日期间隔.

I need to number islands in SQL Server 2012. Island is defined as a set of rows where there is no day gaps between DateFrom and DateTo within the same ItemId).

以下数据集:

CREATE TABLE #Ranges (ItemId INT, DateFrom DATETIME, DateTo DATETIME)

INSERT INTO #Ranges VALUES (1,'2015-01-31','2015-02-17')
INSERT INTO #Ranges VALUES (1,'2015-02-18','2015-03-31')
INSERT INTO #Ranges VALUES (1,'2015-04-14','2015-05-21')
INSERT INTO #Ranges VALUES (2,'2015-07-12','2015-07-19')
INSERT INTO #Ranges VALUES (2,'2015-07-20','2015-07-24')
INSERT INTO #Ranges VALUES (2,'2015-07-26','2015-08-02')
INSERT INTO #Ranges VALUES (2,'2015-08-03','2015-08-07')

应编号如下:

ItemId;  DateFrom;    DateTo;      Number
1;       2015-01-31;  2015-02-17;  1
1;       2015-02-18;  2015-03-31;  1
1;       2015-04-14;  2015-05-21;  2
2;       2015-07-12;  2015-07-19;  3
2;       2015-07-20;  2015-07-24;  3
2;       2015-07-26;  2015-08-02;  4
2;       2015-08-03;  2015-08-07;  4

非常感谢您的帮助.

关于， 普热梅克

推荐答案

如果您只想对它们编号，那么我建议使用累积总和lag():

If you want to just number them, then I would suggest lag() with a cumulative sum:

select t.*,
       sum(case when datefrom = dateadd(day, 1, prev_dateto
                then 0 else 1
           end) over (order by itemId, datefrom)
from (select t.*,
             lag(dateto) over (partition by itemid order by datefrom) as prev_dateto
      from table t
     ) t;

case确定新岛的起点.累计总和就是这个标志的总和.

The case determines where a new island begins. The cumulative sum just sums this flag.

这篇关于SQL Server 2012中的编号岛的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！