是否有tSQL函数可以在每次更改Value1排序的Value2后创建一个计数器

Question

我必须开发一个SQL脚本，该脚本在按另一列排序的表的值每次更改后创建计数器。

请参见示例:如果可能，列"ExpectedCalculatedValue“应由tsQL计算

我已经尝试了像“Rank”或“Dense_Rank”这样的函数，但我不能解决这个问题。

DECLARE @testTab as TABLE (ID int, [From] datetime, Until datetime, TestID int, 
ExpectedCalculatedValue int)

--TEST DATA
INSERT INTO @testTab VALUES (100, '2019-4-11 11:00', '2019-4-11 11:01', 0, 1)
INSERT INTO @testTab VALUES (100, '2019-4-11 11:01', '2019-4-11 11:11', 200, 2)
INSERT INTO @testTab VALUES (100, '2019-4-11 11:11', '2019-4-11 11:15', 200, 2)
INSERT INTO @testTab VALUES (100, '2019-4-11 11:15', '2019-4-11 11:22', 100, 3)
INSERT INTO @testTab VALUES (100, '2019-4-11 11:22', '2019-4-11 11:45', 200, 4)

select * 
from @testTab a
order by a.[From]

Answer 1

您在数据中的时间列上有一个完全相同的副本。这真的把事情搞得一团糟。因此，首先要做的是引入一个唯一的数字。在本例中，它使用row_number()

select a.*,
       sum(case when prev_testid = testid then 0 else 1 end) over (partition by id order by [from], seqnum) as calculated_valued
from (select a.*,
             lag(testid) over (partition by id order by [from], seqnum) as prev_testid
      from (select a.*, row_number() over (partition by id order by [from], [until], testid) as seqnum
            from @testTab a
           ) a
     ) a
order by a.[From];

但是，我建议在表中包含一个identity列并使用它，就像在this db<>fiddle中一样。

Answer 2

select，sum(case when base.pVal = base.TestID then 0 else 1 end) over(order by base.From) as Counter from (select aa，lag(aa.TestID，1，null) over(order by aa.From) as pVal from @testTab aa) base