选择日期时间和值，当value != LAG(value)

Question

我在SQL Server 2014工作。

我有这样的数据:

datetime                      Value
2020-11-27 11:20:20.0000000   0
2020-11-27 11:21:20.0000000   0
2020-11-27 11:22:20.0000000   1
2020-11-27 11:23:20.0000000   1
2020-11-27 11:24:20.0000000   2
2020-11-27 11:25:20.0000000   2
2020-11-27 11:26:20.0000000   2
2020-11-27 11:27:20.0000000   1
2020-11-27 11:28:20.0000000   1
2020-11-27 11:29:20.0000000   1
2020-11-27 11:30:20.0000000   2
2020-11-27 11:31:20.0000000   null
2020-11-27 11:32:20.0000000   2

我需要:

datetime                      Value
2020-11-27 11:20:20.0000000   0
2020-11-27 11:22:20.0000000   1
2020-11-27 11:24:20.0000000   2
2020-11-27 11:27:20.0000000   1
2020-11-27 11:30:20.0000000   2
2020-11-27 11:31:20.0000000   null
2020-11-27 11:32:20.0000000   2

我可以有十万行，所以我还需要一些高效的东西。我试着用LAG(),rank(),first()工作，但我从来没有得到预期的结果。

Answer 1

直接使用LAG():

SELECT [datetime], [value]
FROM (
SELECT 
[datetime], [value],
LAG([value]) OVER (ORDER BY [datetime] ASC) AS [prevvalue]
FROM (VALUES
('2020-11-27T11:20:20.0000000', 0),
('2020-11-27T11:21:20.0000000', 0),
('2020-11-27T11:22:20.0000000', 1),
('2020-11-27T11:23:20.0000000', 1),
('2020-11-27T11:24:20.0000000', 2),
('2020-11-27T11:25:20.0000000', 2),
('2020-11-27T11:26:20.0000000', 2),
('2020-11-27T11:27:20.0000000', 1),
('2020-11-27T11:28:20.0000000', 1),
('2020-11-27T11:29:20.0000000', 1),
('2020-11-27T11:30:20.0000000', 2),
('2020-11-27T11:31:20.0000000', null),
('2020-11-27T11:32:20.0000000', 2)
) v ([datetime], [value])   
) t
WHERE COALESCE([value], -1) <> COALESCE([prevvalue], -1)

结果:

datetime                    value
2020-11-27T11:20:20.0000000 0
2020-11-27T11:22:20.0000000 1
2020-11-27T11:24:20.0000000 2
2020-11-27T11:27:20.0000000 1
2020-11-27T11:30:20.0000000 2
2020-11-27T11:31:20.0000000 null
2020-11-27T11:32:20.0000000 2