sql-server - 使用超前/滞后有额外的异常(exception)

Question

我有下表:

 Declare @YourTable table ([Event] varchar(100),[Start] DateTime,[End] DateTime, [Tag] varchar(25))
 Insert Into @YourTable values
 ('10PIC700422.PV 10-PSV-700073A 10-PSV-700073B','9/9/16 10:44','9/9/16 10:49','Big'),
 ('10PIC700422.PV 10-PSV-700073A 10-PSV-700073B','9/9/16 10:50','9/9/16 10:51','Small'),
 ('11PIC41010.PV 11-PSV-401002A 11-PSV-401002B','4/4/16 12:51','4/4/16 13:58','Big'),
 ('11PIC41010.PV 11-PSV-401002A 11-PSV-401002B','4/4/16 14:04','4/4/16 14:29','Small'),
 ('11PIC41010.PV 11-PSV-401002A 11-PSV-401002B','4/4/16 14:51','4/4/16 14:58','Big'),
 ('11PIC41010.PV 11-PSV-401002A 11-PSV-401002B','4/4/16 15:04','4/4/16 15:29','Small'),
 ('11PIC41010.PV 11-PSV-401002W 11-PSV-401002D','4/4/16 16:04','4/4/16 16:45','Big')

并使用以下查询来获取我需要的结果，按事件分组并按开始排序，并在小变大时将大变小:

 Select [Event]
  ,[Start]
  ,[End] 
  ,[Tag]
  ,[Tag_new] = case when Tag='Big' and 'Small' = Lead(Tag,1,Tag) over (Partition By Event Order By Start) then 'Small' else tag end
  From  @YourTable

   Event                                          Start         End             Tag    Tag_new
   10PIC700422.PV 10-PSV-700073A 10-PSV-700073B   9-9-16 10:44  9-9-16 10:49    Big    Small
   10PIC700422.PV 10-PSV-700073A 10-PSV-700073B   9-9-16 10:50  9-9-16 10:51    Small  Small
   11PIC41010.PV 11-PSV-401002A 11-PSV-401002B    4-4-16 12:51  4-4-16 13:58    Big    Small
   11PIC41010.PV 11-PSV-401002A 11-PSV-401002B    4-4-16 14:04  4-4-16 14:29    Small  Small
   11PIC41010.PV 11-PSV-401002A 11-PSV-401002B    4-4-16 14:51  4-4-16 14:58    Big    Small
   11PIC41010.PV 11-PSV-401002A 11-PSV-401002B    4-4-16 15:04  4-4-16 15:29    Small  Small
   11PIC41010.PV 11-PSV-401002W 11-PSV-401002D    4-4-16 16:04  4-4-16 16:45    Big    Big

唯一的事情是，每当下面的序列出现在一组标签列中时，我需要添加一个异常(exception)，它应该在小和大之间添加相应的开始时间和结束时间，并且开始时间比之前的结束时间和结束时间多 1 分钟比开始时间多 1 分钟并且 Tag_new 是“坏的”:

  small
  big
  small

我想得到以下结果:

   Event                                          Start         End             Tag    Tag_new
   10PIC700422.PV 10-PSV-700073A 10-PSV-700073B   9-9-16 10:44  9-9-16 10:49    Big    Small
   10PIC700422.PV 10-PSV-700073A 10-PSV-700073B   9-9-16 10:50  9-9-16 10:51    Small  Small
   11PIC41010.PV 11-PSV-401002A 11-PSV-401002B    4-4-16 12:51  4-4-16 13:58    Big    Small
   11PIC41010.PV 11-PSV-401002A 11-PSV-401002B    4-4-16 14:04  4-4-16 14:29    Small  Small
   11PIC41010.PV 11-PSV-401002A 11-PSV-401002B    4-4-16 14:30  4-4-16 14:31    Bad    Bad
   11PIC41010.PV 11-PSV-401002A 11-PSV-401002B    4-4-16 14:51  4-4-16 14:58    Big    Small
   11PIC41010.PV 11-PSV-401002A 11-PSV-401002B    4-4-16 15:04  4-4-16 15:29    Small  Small
   11PIC41010.PV 11-PSV-401002W 11-PSV-401002D    4-4-16 16:04  4-4-16 16:45    Big    Big

Answer 1

您可以尝试使用 LEAD() 两次，以选择下一个 val 和最后一个 val，然后在所有条件下对其使用 CASE EXPRESSION:

SELECT [Event],[Start],[End],[Tag],
       CASE WHEN t.next_one = 'Small' and t.Last_one = 'Small' And t.Tag = 'Big' then 'Big'
            WHEN t.tag = 'Big' and t.next_one = 'Small' THEN 'Small'
            ELSE t.tag
       END as new_tag
FROM(
     Select [Event]
       ,[Start]
       ,[End] 
       ,[Tag]
       Lead(Tag,1,Tag) over (Partition By Event Order By Start) as next_one,
       Lead(Tag,1,Tag) over (Partition By Event Order By Start DESC) as last_one,
    From  @YourTable) t