sql - 合并区间数据组 - SQL Server

Question

我有两组区间数据 I.E.

Start End Type1 Type2
0     2   L     NULL
2     5   L     NULL
5     7   L     NULL
7     10  L     NULL
2     3   NULL  S
3     5   NULL  S
5     8   NULL  S
11    12  NULL  S

我想做的是将这些集合合并为一个。这似乎可以通过使用孤岛和间隙解决方案来实现，但由于间隔的非连续性，我不确定如何应用它……我期望的输出是:

Start End Type1 Type2
0     2   L     NULL
2     3   L     S
3     5   L     S
5     7   L     S
7     8   L     S
8     10  L     NULL
11    12  NULL  S

以前有人做过这样的事吗？？？谢谢!

在下面创建脚本:

CREATE TABLE Table1
    ([Start] int, [End] int, [Type1] varchar(4), [Type2] varchar(4))
;

INSERT INTO Table1
    ([Start], [End], [Type1], [Type2])
VALUES
    (0, 2, 'L', NULL),
    (2, 3, NULL, 'S'),
    (2, 5, 'L', NULL),
    (3, 5, NULL, 'S'),
    (5, 7, 'L', NULL),
    (5, 8, NULL, 'S'),
    (7, 10, 'L', NULL),
    (11, 12, NULL, 'S')
;

Answer 1

我假设 Start 是包含性的，End 是独占性的，并且给定的时间间隔不重叠。

CTE_Number 是一个数字表。在这里它是动态生成的。我将其作为数据库中的永久表。

CTE_T1 和 CTE_T2 使用数字表将每个区间扩展为相应的行数。例如，区间 [2,5) 生成具有 Values

的行

2
3
4

这会完成两次:Type1 和 Type2。

Type1 和 Type2 的结果在 Value 上FULL JOINed。

最后，间隙和孤岛传递组/折叠间隔。

逐步运行查询，逐个 CTE 并检查中间结果以了解其工作原理。

示例数据

我添加了几行来说明值之间存在差距的情况。

DECLARE @Table1 TABLE
    ([Start] int, [End] int, [Type1] varchar(4), [Type2] varchar(4))
;

INSERT INTO @Table1 ([Start], [End], [Type1], [Type2]) VALUES
( 0,  2, 'L', NULL),
( 2,  3, NULL, 'S'),
( 2,  5, 'L', NULL),
( 3,  5, NULL, 'S'),
( 5,  7, 'L', NULL),
( 5,  8, NULL, 'S'),
( 7, 10, 'L', NULL),
(11, 12, NULL, 'S'),

(15, 20, 'L', NULL),
(15, 20, NULL, 'S');

查询

WITH 
e1(n) AS
(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
) -- 10
,e2(n) AS (SELECT 1 FROM e1 CROSS JOIN e1 AS b) -- 10*10
,e3(n) AS (SELECT 1 FROM e1 CROSS JOIN e2) -- 10*100
,CTE_Numbers
AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY n) AS Number
    FROM e3
)
,CTE_T1
AS
(
    SELECT
        T1.[Start] + CA.Number - 1 AS Value
        ,T1.Type1
    FROM
        @Table1 AS T1
        CROSS APPLY
        (
            SELECT TOP(T1.[End] - T1.[Start]) CTE_Numbers.Number
            FROM CTE_Numbers
            ORDER BY CTE_Numbers.Number
        ) AS CA
    WHERE
        T1.Type1 IS NOT NULL
)
,CTE_T2
AS
(
    SELECT
        T2.[Start] + CA.Number - 1 AS Value
        ,T2.Type2
    FROM
        @Table1 AS T2
        CROSS APPLY
        (
            SELECT TOP(T2.[End] - T2.[Start]) CTE_Numbers.Number
            FROM CTE_Numbers
            ORDER BY CTE_Numbers.Number
        ) AS CA
    WHERE
        T2.Type2 IS NOT NULL
)
,CTE_Values
AS
(
    SELECT
        ISNULL(CTE_T1.Value, CTE_T2.Value) AS Value
        ,CTE_T1.Type1
        ,CTE_T2.Type2
        ,ROW_NUMBER() OVER (ORDER BY ISNULL(CTE_T1.Value, CTE_T2.Value)) AS rn
    FROM
        CTE_T1
        FULL JOIN CTE_T2 ON CTE_T2.Value = CTE_T1.Value
)
,CTE_Groups
AS
(
    SELECT
        Value
        ,Type1
        ,Type2
        ,rn
        ,ROW_NUMBER() OVER 
            (PARTITION BY rn - Value, Type1, Type2 ORDER BY Value) AS rn2
    FROM CTE_Values
)
SELECT
    MIN(Value) AS [Start]
    ,MAX(Value) + 1 AS [End]
    ,Type1
    ,Type2
FROM CTE_Groups
GROUP BY rn-rn2, Type1, Type2
ORDER BY [Start];

结果

+-------+-----+-------+-------+
| Start | End | Type1 | Type2 |
+-------+-----+-------+-------+
|     0 |   2 | L     | NULL  |
|     2 |   8 | L     | S     |
|     8 |  10 | L     | NULL  |
|    11 |  12 | NULL  | S     |
|    15 |  20 | L     | S     |
+-------+-----+-------+-------+