sql-server - 总结区间数据的最佳方法是什么？

Question

表中的给定数据具有任意间隔(不是日期/时间!!)，定义如下:

START float
END float
VALUE varchar(40)

例如

 START    END    VALUE
 -----    ---    ------
 0        1      Banana
 1        3      Banana
 3        4      Orange
 4        7      Orange
 7        8      Apple
 8        9      Apple
 9       10      Apple
10       15      Apple
20       22      Apple
22       23      Apple
23       28      Banana
28       30      Banana
etc..

如何汇总数据，以便对于连续的时间间隔，仅列出一个值。 IE。查询结果应如下所示:

START     END    VALUE
-----     ---    ------
 0        3      Banana
 3        7      Orange
 7       15      Apple
20       23      Apple
23       30      Banana

请注意上面 15 和 20 之间的差距。我正在处理相当多的数据(~500k 行)，但不经常运行查询。所以效率是最好的。可以在不使用光标的情况下完成此操作吗？

(注意:使用 SQL2008R2，因此无法利用较新的功能(如果存在)

谢谢!

Answer 1

这应该适合你:

DECLARE @T TABLE (Start INT, [End] INT, Value VARCHAR(100));
INSERT @T (Start, [End], Value)
VALUES
    (0, 1, 'Banana'), (1, 3, 'Banana'), (3, 4, 'Orange'), (4, 7, 'Orange'),
    (7, 8, 'Apple'), (8, 9, 'Apple'), (9, 10, 'Apple'), (10, 15, 'Apple'), 
    (20, 22, 'Apple'), (22, 23, 'Apple'), (23, 28, 'Banana'), (28, 30, 'Banana');

WITH CTE AS
(   SELECT  t.[Start], 
            t.[End], 
            t.[value], 
            IsStart = ISNULL(c.IsStart, 1)
    FROM    @T AS T
            OUTER APPLY
            (   SELECT  TOP 1 IsStart = 0
                FROM    @T AS T2
                WHERE   T2.Value = T.Value
                AND     T2.[End] = T.Start
            ) AS c
)
SELECT  Value, Start = MIN(Start), [End] = MAX([End])
FROM    CTE AS T
        OUTER APPLY
        (   SELECT  SUM(IsStart)
            FROM    CTE AS T2
            WHERE   T2.Value = T.Value
            AND     T2.Start <= T.Start
        ) g (GroupingSet)
GROUP BY Value, GroupingSet
ORDER BY Start;

第一步是识别作为新范围开始的每条记录。这部分:

SELECT  t.[Start], 
        t.[End], 
        t.[value], 
        IsStart = ISNULL(c.IsStart, 1)
FROM    @T AS T
        OUTER APPLY
        (   SELECT  TOP 1 IsStart = 0
            FROM    @T AS T2
            WHERE   T2.Value = T.Value
            AND     T2.[End] = T.Start
        ) AS c

将给出:

Start   End value   IsStart
0       1   Banana  1
1       3   Banana  0
3       4   Orange  1
4       7   Orange  0
7       8   Apple   1
8       9   Apple   0
9       10  Apple   0
10      15  Apple   0
20      22  Apple   1

然后，您可以通过识别在当前记录之前开始的范围数来创建唯一组，本质上是添加按值分区的 IsStart 列的运行总计。这就是这里正在做的事情:

SELECT  *
FROM    CTE AS T
        OUTER APPLY
        (   SELECT  SUM(IsStart)
            FROM    CTE AS T2
            WHERE   T2.Value = T.Value
            AND     T2.Start <= T.Start
        ) g (GroupingSet);

给予:

Start   End value   IsStart GroupingSet
0       1   Banana  1       1
1       3   Banana  0       1
3       4   Orange  1       1
4       7   Orange  0       1
7       8   Apple   1       1
8       9   Apple   0       1
9       10  Apple   0       1
10      15  Apple   0       1
20      22  Apple   1       2   -- SECOND NON CONTINUOUS RANGE FOR APPLES
22      23  Apple   0       2
23      28  Banana  1       2   -- SECOND NON CONTINUOUS RANGE FOR BANANAS
28      30  Banana  0       2

最后，您可以按值聚合分组，并使用此标识符列来标识唯一组。

您还可以通过交叉连接到数字表将每个范围扩展为行来实现此目的(为简洁起见，我使用了 master..spt_values):

WITH CTE AS
(   SELECT  t.[value], 
            Number = t.Start + v.Number,
            GroupingSet = t.Start + v.Number - ROW_NUMBER() OVER(PARTITION BY t.[value] ORDER BY t.Start + v.Number)
    FROM    @T AS T
            INNER JOIN Master..spt_values v
                ON v.[Type] = 'P'
                AND v.Number < (t.[End] - t.[Start])
)
SELECT  Value, [Start] = MIN(Number), [End] = MAX(Number)
FROM    CTE
GROUP BY GroupingSet, Value;

这样做的缺点是，如果您有很多行/大范围，它可能会占用大量内存。扩展范围后，这仅使用 Itzik Ben-Gan's Gaps and Islands Solutions 中描述的使用排名函数的方法。