sql - TSQL - 仅对连续行进行 GROUP BY

Question

我正在使用 SQL Server 2017 (v14.0)。

我有一张这样的桌子：

Key | State    | from       | until      |
----+----------+------------+------------+
100 | open     | 01.01.2021 | 01.01.2021 |
100 | open     | 02.01.2021 | 02.01.2021 |
100 | closed   | 03.01.2021 | 13.01.2021 |
100 | open     | 14.01.2021 | 20.01.2021 |
100 | open     | 20.01.2021 | 30.01.2021 |

我想按Keyand对它进行分组State，但仅适用于连续行。

所以我的预期结果是这样的：

Key | State    | from       | until      |
----+----------+------------+------------+
100 | open     | 01.01.2021 | 02.01.2021 |
100 | closed   | 03.01.2021 | 13.01.2021 |
100 | open     | 14.01.2021 | 30.01.2021 |

关于如何做到这一点的任何想法？我有一种强烈的感觉，这应该可以在ROW_NUMBER某种方式的帮助下实现，但我还没有弄清楚......

（在这个例子中，一些奇怪group by calendarweek或类似的数据可能是可能的，但这不是我的意图）

标签： sqlsql-servertsqlgroup-bysql-server-2017

Answer 1

一种可能性如下添加：

Create table myTable_o1
 (
 [key] int
 ,[state] varchar(100)
 ,[From] date
 ,[Until] date
 )

insert into myTable_o1 values (100, 'open', '2021-01-01','2021-01-01')
insert into myTable_o1 values (100, 'open', '2021-01-02','2021-01-02')
insert into myTable_o1 values (100, 'closed', '2021-01-03','2021-01-13')
insert into myTable_o1 values (100, 'open', '2021-01-4','2021-01-20')
insert into myTable_o1 values (100, 'open', '2021-01-20','2021-01-30')

SELECT 
   [key] 
   ,[state] 
   ,[From] 
   ,[until] 
FROM
(
Select 
  [key] 
 , [state] 
 , [From] 
 , row_number() over (partition by tiles order by [key]) row_num
 , ISNULL(Lead(Until) over (partition by tiles order by [key]) , Until) [until]
 FROM
 (
    SELECT * ,
    Ntile(2) over ( order by [Key]) as [tiles]
    from myTable_o1
   ) AS A
 ) AS B WHERE B.row_num in (1,3)