合并区间数据组 - SQL Server

Question

合并区间数据组 - SQL Server

sqlsql-serversql-server-2008

3

我有两组区间数据，即

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')
;

- Harry

似乎可以在这里使用Itzik Ben-Gan的Packing intervals。 - Vladimir Baranov

这个链接很好，但所有的SQL都不兼容SQL 2008R2（我们不能使用FLOOR等函数）。 - Harry

3个回答

0

一种逐步的方法是：

-- Finding all break points
;WITH breaks AS (
    SELECT Start
    FROM yourTable
    UNION 
    SELECT [End]
    FROM yourTable
) -- Finding Possible Ends
, ends AS (
    SELECT Start
        , (SELECT Min([End]) FROM yourTable WHERE yourTable.Start = breaks.Start) End1
        , (SELECT Max([End]) FROM yourTable WHERE yourTable.Start < breaks.Start) End2
    FROM breaks
) -- Finding periods
, periods AS (
    SELECT Start, 
        CASE 
            WHEN End1 > End2 And End2 > Start THEN End2
            WHEN End1 IS NULL THEN End2
            ELSE End1
        END [End]
    FROM Ends
    WHERE NOT(End1 IS NULL AND Start = End2)
) -- Generating results
SELECT p.Start, p.[End], Max(Type1) Type1, Max(Type2) Type2
FROM periods p, yourTable t
WHERE p.start >= t.Start AND p.[End] <= t.[End]
GROUP BY p.Start, p.[End];

在上述查询中，某些情况可能不适合分析所有情况，您可以根据需要进行改进；)。

- shA.t

我遇到过你提到的一些“情况”，正在努力改进查询以适应它们。我会尝试提供一个具体的示例来查看。 - Harry

0

首先通过 Union 获取所有起始和结束数字。
然后在“L”和“S”记录上连接这些数字。

使用表变量进行测试。

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');

select 
n.Num as Start,
(case when s.[End] is null or l.[End] <= s.[End] then l.[End] else s.[End] end) as [End],
l.Type1, 
s.Type2
from
(select Start as Num from @Table1 union select [End] from @Table1) n
left join @Table1 l on (n.Num >= l.Start and n.Num < l.[End] and l.Type1 = 'L')
left join @Table1 s on (n.Num >= s.Start and n.Num < s.[End] and s.Type2 = 'S')
where (l.Start is not null or s.Start is not null)
order by Start, [End];

输出：

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

- LukStorms

在我所展示的示例中，类型仅显示为“L”和“S”，但在实际数据表中，这些是可变的。是否有任何方法使此语句更具动态性？ - Harry

在左连接中，您可以将类型的条件更改为“IS NOT NULL”，然后在where子句中添加特定类型1和/或类型2的条件。 - LukStorms

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Vladimir Baranov · Accepted Answer

我假设“Start”是包含在内的，“End”是排除在外的，并且给定的间隔不重叠。

“CTE_Number”是一个数字表。这里它是即时生成的。我已经将其作为永久表储存在我的数据库中。

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

2
3
4

这个过程会进行两次：针对Type1和Type2。 Type1和Type2的结果将在Value上进行FULL JOIN。

最后，一个间隙和区间分组通过将间隔/折叠回来。

逐步运行查询，按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');

Query

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];

Result

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