sql - 识别 SQL Server 中的序列开始和结束

Question

我有一个表，其中的数据按如下方式排列:

ID |  BOUNDARY  | TIMESTAMP
1  |  NULL      | 2016-01-01 00:20:00
2  |  A         | 2016-01-01 00:20:10
3  |  A         | 2016-01-01 00:20:14
4  |  A         | 2016-01-01 00:20:22
5  | NULL       | 2016-01-01 00:20:38
6  |  A         | 2016-01-01 00:20:45
7  |  B         | 2016-01-01 00:21:02
8  |  B         | 2016-01-01 00:21:12
9  |  A         | 2016-01-01 00:21:16
10 |  A         | 2016-01-01 00:21:22
11 |  C         | 2016-01-01 00:21:30
12 |  A         | 2016-01-01 00:21:35
13 |  A         | 2016-01-01 00:21:40
14 |  A         | 2016-01-01 00:21:46
15 |  A         | 2016-01-01 00:21:50

我想要做的是找到一种有效的方法来标记 SQL Server 2014 中序列的开始和结束的 ID 和时间戳。当边界不为空并且至少连续两次重复时，就会形成段。次。例如，第一段将是 ID 2-4，第二段将是 ID 7-8，第三段将是 ID 9-10。

我首先尝试的方法是创建两列，一个“startflag”列和一个“endflag”列。我创建了一个正确标记开始和结束的更新查询，但我想创建一个 View ，在其中可以将其视为一条记录，如下所示:

BOUNDARY | START ID | END ID  
A        |   2      |  4
B        |   7      |  8
A        |   9      |  10
A        |   12     |  15

Answer 1

好吧，我确信有更好的方法可以做到这一点，但这可行:

WITH CTE AS
(
    SELECT  *,
            RN1 = ROW_NUMBER() OVER(ORDER BY [TIMESTAMP]),
            RN2 = ROW_NUMBER() OVER(PARTITION BY BOUNDARY ORDER BY [TIMESTAMP])
    FROM #YourTable
), CTE2 AS
(
    SELECT  *, 
            RN1-RN2 RN3,
            COUNT(*) OVER(PARTITION BY RN1-RN2) N
    FROM CTE
)
SELECT  BOUNDARY,
        MIN(ID) [START ID],
        MAX(ID) [END ID]
FROM CTE2
WHERE N > 1
AND BOUNDARY IS NOT NULL
GROUP BY BOUNDARY, RN3
ORDER BY [START ID];

如果我们使用这个示例表:

CREATE TABLE #YourTable
    ([ID] int, [BOUNDARY] varchar(4), [TIMESTAMP] datetime)
;

INSERT INTO #YourTable
    ([ID], [BOUNDARY], [TIMESTAMP])
VALUES
    (1, NULL, '2016-01-01 00:20:00'),
    (2, 'A', '2016-01-01 00:20:10'),
    (3, 'A', '2016-01-01 00:20:14'),
    (4, 'A', '2016-01-01 00:20:22'),
    (5, NULL, '2016-01-01 00:20:38'),
    (6, 'A', '2016-01-01 00:20:45'),
    (7, 'B', '2016-01-01 00:21:02'),
    (8, 'B', '2016-01-01 00:21:12'),
    (9, 'A', '2016-01-01 00:21:16'),
    (10, 'A', '2016-01-01 00:21:22'),
    (11, 'C', '2016-01-01 00:21:30'),
    (12, 'A', '2016-01-01 00:21:35'),
    (13, 'A', '2016-01-01 00:21:40'),
    (14, 'A', '2016-01-01 00:21:46'),
    (15, 'A', '2016-01-01 00:21:50')
;

结果是:

╔══════════╦══════════╦════════╗
║ BOUNDARY ║ START ID ║ END ID ║
╠══════════╬══════════╬════════╣
║ A        ║        2 ║      4 ║
║ B        ║        7 ║      8 ║
║ A        ║        9 ║     10 ║
║ A        ║       12 ║     15 ║
╚══════════╩══════════╩════════╝