组接近数字

Question

我有一个包含 2 列整数的表。第一列表示开始索引，第二列表示结束索引。

START END
1     8
9     13
14    20
20    25
30    42
42    49
60    67

到目前为止很简单。我想要做的是将后面的所有记录分组在一起：

START END
1     25
30    49
60    67

一条记录​​可以从与前一个结束索引相同的索引开始或边距为 1：

START END
1     10
10    20

并且

START END
1     10
11    20

两者都会导致

START END
1     20

我使用 SQL Server 2008 R2 。

任何帮助都会很棒

Answer 1

这适用于您的示例，如果它不适用于其他数据，请告诉我

create table #Range 
(
  [Start] INT,
  [End] INT
)

insert into #Range ([Start], [End]) Values (1, 8)
insert into #Range ([Start], [End]) Values (9, 13)
insert into #Range ([Start], [End]) Values (14, 20)
insert into #Range ([Start], [End]) Values (20, 25)
insert into #Range ([Start], [End]) Values (30, 42)
insert into #Range ([Start], [End]) Values (42, 49)
insert into #Range ([Start], [End]) Values (60, 67)



;with RangeTable as
(select
    t1.[Start],
    t1.[End],
    row_number() over (order by t1.[Start]) as [Index]
from
    #Range t1
where t1.Start not in (select 
                      [End] 
               from
                  #Range
                  Union
               select 
                  [End] + 1
               from
                  #Range
               )
)
select 
    t1.[Start],
    case 
   when t2.[Start] is null then
        (select max([End])
                     from #Range)
       else
        (select max([End])
                     from #Range
                     where t2.[Start] > [End])
end as [End]    
from 
    RangeTable t1
left join 
    RangeTable t2
on
    t1.[Index] = t2.[Index]-1 

drop table #Range;

Answer 2

编辑以包含另一个版本，我认为该版本更可靠，并且也适用于重叠范围

CREATE TABLE #data (start_range INT, end_range INT)
INSERT INTO #data VALUES (1,8) 
INSERT INTO #data VALUES (2,15) 
INSERT INTO #data VALUES (9,13)
INSERT INTO #data VALUES (14,20) 
INSERT INTO #data VALUES (13,26) 
INSERT INTO #data VALUES (12,21) 
INSERT INTO #data VALUES (9,25) 
INSERT INTO #data VALUES (20,25) 
INSERT INTO #data VALUES (30,42) 
INSERT INTO #data VALUES (42,49) 
INSERT INTO #data VALUES (60,67)   

;with ranges as
(
SELECT start_range as level
,end_range as end_range
,row_number() OVER (PARTITION BY (SELECT NULL) ORDER BY start_range) as row
FROM #data
UNION ALL
SELECT
level + 1 as level
,end_range as end_range
,row
From ranges 
WHERE level < end_range
)
,ranges2 AS
(
SELECT DISTINCT 
level
FROM ranges
)
,ranges3 AS
(
SELECT 
level
,row_number() OVER (ORDER BY level) - level as grouping_group
from ranges2
)
SELECT 
MIN(level) as start_number
,MAX(level) as end_number
FROM ranges3
GROUP BY grouping_group
ORDER BY start_number ASC

---

我认为这应该有效 - 但在较大的集合上可能不是特别有效......

CREATE TABLE #data (start_range INT, end_range INT)
INSERT INTO #data VALUES (1,8)
INSERT INTO #data VALUES (2,15)
INSERT INTO #data VALUES (9,13)
INSERT INTO #data VALUES (14,20)
INSERT INTO #data VALUES (21,25)
INSERT INTO #data VALUES (30,42)
INSERT INTO #data VALUES (42,49)
INSERT INTO #data VALUES (60,67)


;with overlaps as
(
select * 
,end_range - start_range as range
,row_number() OVER (PARTITION BY (SELECT NULL) ORDER BY start_range ASC) as line_number
from #data
)
,overlaps2 AS
(
SELECT
O1.start_range
,O1.end_range
,O1.line_number
,O1.range
,O2.start_range as next_range
,CASE WHEN O2.start_range - O1.end_range < 2 THEN 1 ELSE 0 END as overlap
,O1.line_number - DENSE_RANK() OVER (PARTITION BY (CASE WHEN O2.start_range - O1.end_range < 2 THEN 1 ELSE 0 END) ORDER BY O1.line_number ASC) as overlap_group
FROM overlaps O1
LEFT OUTER JOIN overlaps O2 on O2.line_number = O1.line_number + 1
)
SELECT 
MIN(start_range) as range_start
,MAX(end_range) as range_end
,MAX(end_range) - MIN(start_range) as range_span
FROM overlaps2
GROUP BY overlap_group

Answer 3

您可以使用 数字表来解决这个问题。基本上，您首先扩展范围，然后将后续项目分组。

这是一种实现：

WITH data (START, [END]) AS (
  SELECT  1,  8 UNION ALL
  SELECT  9, 13 UNION ALL
  SELECT 14, 20 UNION ALL
  SELECT 20, 25 UNION ALL
  SELECT 30, 42 UNION ALL
  SELECT 42, 49 UNION ALL
  SELECT 60, 67
),
expanded AS (
  SELECT DISTINCT
    N = d.START + v.number
  FROM data d
    INNER JOIN master..spt_values v ON v.number BETWEEN 0 AND d.[END] - d.START
  WHERE v.type = 'P'
),
marked AS (
  SELECT
    N,
    SeqID = N - ROW_NUMBER() OVER (ORDER BY N)
  FROM expanded
)
SELECT
  START = MIN(N),
  [END] = MAX(N)
FROM marked
GROUP BY SeqID

该解决方案使用 master..spt_values 作为数字表，用于扩展初始范围。但是，如果（全部或部分）这些范围可能跨越超过 2048 个（后续）值，那么您应该定义并使用 您自己的数字表。