分组和订购，但有额外的例外 [英] Group and order with additional exception
本文介绍了分组和订购，但有额外的例外的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有下表:
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-401002A 11-PSV-401002B','4-4-16 15:45','4-4-16 15:55','Big'),
('11PIC41010.PV 11-PSV-401002W 11-PSV-401002D','4/4/16 16:04','4/4/16 16:45','Big')
并使用以下查询来获得我需要的结果，按事件分组并按开始顺序排序，并在从小到大时从大到小:
And use following query to get result I need, group by event and order by start, and change big to small whenever small goes after 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-401002A 11-PSV-401002B 4-4-16 15:45 4-4-16 15:55 Big Big
11PIC41010.PV 11-PSV-401002W 11-PSV-401002D 4-4-16 16:04 4-4-16 16:45 Big Big
但是每当下面的序列出现在一组的 Tag 列中时，我都需要再应用一个例外，Tag_new 列应该是Small2"，从 Big 开始，直到 Tag 列中的下一个 Big:
but I need to apply one more exception whenever sequence below appears in Tag column in a group, Tag_new column should be "Small2" starting from Big till next Big in Tag column:
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:51 4-4-16 14:58 Big Small2
11PIC41010.PV 11-PSV-401002A 11-PSV-401002B 4-4-16 15:04 4-4-16 15:29 Small Small2
11PIC41010.PV 11-PSV-401002A 11-PSV-401002B 4-4-16 15:45 4-4-16 15:55 Big Big
11PIC41010.PV 11-PSV-401002W 11-PSV-401002D 4-4-16 16:04 4-4-16 16:45 Big Big
对 iamdave 的更新:
Updates to iamdave:
当我在大之后还有几个小时，它会在大之后变成第二个小，而小2应该是小2，所以它应该是小2直到下一个大:
When I have few more small after big then it makes second small after Big just Small while should Small2 so it should be Small2 till next Big:
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-401002A 11-PSV-401002B','4/4/16 15:30','4/4/16 15:32','Small'),
('11PIC41010.PV 11-PSV-401002A 11-PSV-401002B','4-4-16 15:45','4-4-16 15:55','Big'),
('11PIC41010.PV 11-PSV-401002W 11-PSV-401002D','4/4/16 16:04','4/4/16 16:45','Big')
这里是输出，以防在 Big 之后还有更多小:
And here is output in case few more small after Big:
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 Small2
11PIC41010.PV 11-PSV-401002A 11-PSV-401002B 4-4-16 15:04 4-4-16 15:29 Small Small2
11PIC41010.PV 11-PSV-401002A 11-PSV-401002B 4-4-16 15:30 4-4-16 15:32 Small Small
11PIC41010.PV 11-PSV-401002A 11-PSV-401002B 4-4-16 15:45 4-4-16 15:55 Big Big
11PIC41010.PV 11-PSV-401002W 11-PSV-401002D 4-4-16 16:04 4-4-16 16:45 Big Big
它应该如下所示:
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 Small2
11PIC41010.PV 11-PSV-401002A 11-PSV-401002B 4-4-16 15:04 4-4-16 15:29 Small Small2
11PIC41010.PV 11-PSV-401002A 11-PSV-401002B 4-4-16 15:30 4-4-16 15:32 Small Small2
11PIC41010.PV 11-PSV-401002A 11-PSV-401002B 4-4-16 15:45 4-4-16 15:55 Big Big
11PIC41010.PV 11-PSV-401002W 11-PSV-401002D 4-4-16 16:04 4-4-16 16:45 Big Big
更新@iamdave:再次为这种混乱感到抱歉，似乎更改为 small2 是错误的方向，因为如果我在下面的序列很少，它会在我寻找将它们分开时给所有的 small2:
Updates @iamdave: sorry again for this confusion, it seems changing to small2 was wrong direction since if I have few sequence below it gives small2 for all while I was looking for separate them:
Small
Big
Small
Small
Big
Small
这是我得到的:
Event Start End Tag Tag_new
10PIC700422.PV 10-PSV-700073A 10-PSV-700073B 2016-09-09 10:44:05.000 2016-09-09 10:48:08.000 Big Big
10PIC700422.PV 10-PSV-700073A 10-PSV-700073B 2016-09-09 10:44:10.000 2016-09-09 10:49:40.000 Big Small
10PIC700422.PV 10-PSV-700073A 10-PSV-700073B 2016-09-09 10:50:03.000 2016-09-09 10:51:04.000 Small Small
11PIC41010.PV 11-PSV-401002A 11-PSV-401002B 2016-04-04 12:51:07.000 2016-04-04 13:58:09.000 Big Small
11PIC41010.PV 11-PSV-401002A 11-PSV-401002B 2016-04-04 14:04:04.000 2016-04-04 14:29:00.000 Small Small
11PIC41010.PV 11-PSV-401002A 11-PSV-401002B 2016-04-04 14:51:02.000 2016-04-04 14:58:00.000 Big Small2
11PIC41010.PV 11-PSV-401002A 11-PSV-401002B 2016-04-04 15:04:06.000 2016-04-04 15:29:08.000 Small Small2
11PIC41010.PV 11-PSV-401002A 11-PSV-401002B 2016-04-04 15:45:08.000 2016-04-04 15:55:09.000 Big Small2
11PIC41010.PV 11-PSV-401002A 11-PSV-401002B 2016-04-04 16:22:08.000 2016-04-04 16:40:09.000 Small Small2
11PIC41010.PV 11-PSV-401002A 11-PSV-401002B 2016-04-04 16:50:04.000 2016-04-04 16:55:00.000 Small Small2
11PIC41010.PV 11-PSV-401002A 11-PSV-401002B 2016-04-04 17:05:02.000 2016-04-04 17:20:00.000 Big Small2
11PIC41010.PV 11-PSV-401002A 11-PSV-401002B 2016-04-04 17:22:06.000 2016-04-04 17:29:08.000 Small Small2
11PIC41010.PV 11-PSV-401002W 11-PSV-401002D 2016-04-04 16:04:01.000 2016-04-04 16:45:00.000 Big Big
所以最好的解决方案是添加额外的行，正如我在最近的问题中所描述的:
So the best solution would be add additional row as I described in my recent question:
预先感谢您的帮助，
推荐答案
这很丑陋，但我认为它可以满足您的需求?
This is pretty ugly, but I think it does what you need it to?
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:48','Big'),
('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-401002A 11-PSV-401002B','4-4-16 15:45','4-4-16 15:55','Big'),
('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 Lead(Tag,1,'') over (Partition By Event
Order By Start
)
= 'Small'
then 'Small'
else tag
end
,[Tag_new2] = case when (Tag = 'Big'
and lag(Tag,1,'') over (Partition By Event
Order By Start
)
= 'Small'
and Lead(Tag,1,'') over (Partition By Event
Order By Start
)
= 'Small'
)
or (Tag = 'Small'
and lag(Tag,2,'') over (Partition By Event
Order By Start
)
= 'Small'
and lag(Tag,1,'') over (Partition By Event
Order By Start
)
= 'Big'
)
then 'Small2'
else case when Tag = 'Big'
and Lead(Tag,1,'') over (Partition By Event
Order By Start
)
= 'Small'
then 'Small'
else tag
end
end
From @YourTable;
<小时>
为了响应您的更新，并假设您可以向暂存表添加一些列:
In response to your update, and assuming you can add some columns to your Staging table:
Declare @YourTable table (EventRN int, StartRN int, MaxStartRN int, [Event] varchar(100),[Start] DateTime,[End] DateTime, [Tag] varchar(25), [TagNext] varchar(25))
Insert Into @YourTable values
(null,null,null,'10PIC700422.PV 10-PSV-700073A 10-PSV-700073B','9/9/16 10:44','9/9/16 10:49','Big',null),
(null,null,null,'10PIC700422.PV 10-PSV-700073A 10-PSV-700073B','9/9/16 10:50','9/9/16 10:51','Small',null),
(null,null,null,'11PIC41010.PV 11-PSV-401002A 11-PSV-401002B','4/4/16 12:51','4/4/16 13:58','Big',null),
(null,null,null,'11PIC41010.PV 11-PSV-401002A 11-PSV-401002B','4/4/16 14:04','4/4/16 14:29','Small',null),
(null,null,null,'11PIC41010.PV 11-PSV-401002A 11-PSV-401002B','4/4/16 14:51','4/4/16 14:58','Big',null),
(null,null,null,'11PIC41010.PV 11-PSV-401002A 11-PSV-401002B','4/4/16 15:04','4/4/16 15:29','Small',null),
(null,null,null,'11PIC41010.PV 11-PSV-401002A 11-PSV-401002B','4/4/16 15:30','4/4/16 15:32','Small',null),
(null,null,null,'11PIC41010.PV 11-PSV-401002A 11-PSV-401002B','4-4-16 15:45','4-4-16 15:55','Big',null),
(null,null,null,'11PIC41010.PV 11-PSV-401002W 11-PSV-401002D','4/4/16 16:04','4/4/16 16:45','Big',null);
update t
set EventRN = tt.EventRN
,StartRN = tt.StartRN
,MaxStartRN = tt.MaxStartRN
,TagNext = tt.TagNext
from @YourTable t
inner join (
select dense_rank() over (order by Event
) as EventRN
,row_number() over (partition by Event
order by Start
) as StartRN
,count(1) over (partition by Event) as MaxStartRN
,[Event]
,[Start]
,[End]
,[Tag]
,lead(Tag,1,null) over (partition by Event
order by Start
)
as TagNext
from @YourTable
) tt
on(t.[Event] = tt.[Event]
and t.[Start] = tt.[Start]
and t.[End] = tt.[End]
and t.Tag = tt.Tag
);
with cte as
(
select EventRN
,StartRN
,MaxStartRN
,[Event]
,[Start]
,[End]
,Tag
,TagNext
,cast(null as varchar(25)) as TagPrev
,case when TagNext = 'Small'
then TagNext
else Tag
end as TagNew
from @YourTable
where EventRN = 1
and StartRN = 1
union all
select t.EventRN
,t.StartRN
,t.MaxStartRN
,t.[Event]
,t.[Start]
,t.[End]
,t.Tag
,t.TagNext
,case when t.EventRN = c.EventRN then c.Tag else null end as TagPrev
,case when t.EventRN = c.EventRN and c.Tag = 'Small' and t.Tag = 'Big' and t.TagNext = 'Small'
or t.EventRN = c.EventRN and c.TagNew = 'Small2' and t.Tag = 'Small'
then 'Small2'
else case when t.TagNext = 'Small'
then t.TagNext
else t.Tag
end
end
from cte c
inner join @YourTable t
on((c.StartRN < c.MaxStartRN
and t.EventRN = c.EventRN
and t.StartRN = c.StartRN+1
)
or (c.StartRN = c.MaxStartRN
and t.EventRN = c.EventRN+1
and t.StartRN = 1
)
)
)
select [Event]
,[Start]
,[End]
,Tag
,TagNew
from cte
order by Event
,Start;
这篇关于分组和订购，但有额外的例外的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
