我们有一个聊天系统，它有时每秒为聊天过程中的每个事件生成多个事件日志。问题在于这些存储会消耗大量的数据存储（在该平台上非常昂贵），我们希望简化实际存储的内容并删除实际上不必要的内容。
为此，有一个事件类型可以确定聊天在队列中的位置。我们不在乎每个位置，只要它们不干预该聊天的事件即可。因此，我们只希望在每个不同的组中仅保留第一个和最后一个，在该组中没有其他事件类型可以获取该时间段的“总排队时间”。
更为复杂的是，客户在按部门转移时可以进出队列，因此SAME CHAT可以在这些队列位置记录中包含多个块。我试过使用FIRST_VALUE和LAST_VALUE，它可以帮助我解决大部分问题，但是当我们遇到这些事件的两个截然不同的情况时会失败。
这是生成测试数据的脚本：
<!-- language: lang-sql -->
CREATE TABLE #testdata (
id varchar(18),
name varchar(8),
[type] varchar(20),
livechattranscriptid varchar(18),
groupid varchar(40))
INSERT INTO #testdata (id,name,[type],livechattranscriptid,groupid) VALUES
('0DZ14000003I2pOGAS','34128314','ChatRequest','57014000000ltfIAAQ','57014000000ltfIAAQChatRequest'),
('0DZ14000003IGmQGAW','34181980','Enqueue','57014000000ltfIAAQ','57014000000ltfIAAQEnqueue'),
('0DZ14000003IHbqGAG','34185171','Enqueue','57014000000ltfIAAQ','57014000000ltfIAAQEnqueue'),
('0DZ14000003ILuHGAW','34201743','Enqueue','57014000000ltfIAAQ','57014000000ltfIAAQEnqueue'),
('0DZ14000003IQ6cGAG','34217778','Enqueue','57014000000ltfIAAQ','57014000000ltfIAAQEnqueue'),
('0DZ14000003IR7JGAW','34221794','PushAssignment','57014000000ltfIAAQ','57014000000ltfIAAQPushAssignment'),
('0DZ14000003IiDnGAK','34287448','Enqueue','57014000000ltfIAAQ','57014000000ltfIAAQEnqueue'),
('0DZ14000003IiDoGAK','34287545','PushAssignment','57014000000ltfIAAQ','57014000000ltfIAAQPushAssignment'),
('0DZ14000003Iut5GAC','34336044','Enqueue','57014000000ltfIAAQ','57014000000ltfIAAQEnqueue'),
('0DZ14000003Iv7HGAS','34336906','Accept','57014000000ltfIAAQ','57014000000ltfIAAQAccept')
这是尝试识别任何东西的信息，该信息是名称字段所排序的组的第一个和最后一个ID，并按transcriptid进行分组：
select *,FIRST_VALUE(id) OVER(Partition BY groupid order by livechattranscriptid,name asc) as firstinstancegroup,
LAST_VALUE(id) OVER(Partition BY groupid order by livechattranscriptid,name asc RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as lastinstancegroup from #testdata order by livechattranscriptid,name
问题是，整个组为我提供了所有这些人的相同的第一个和最后一个ID，而不是将每组Enqueue记录视为一个单独的组。如何将Enqueue的每个不同的分组实例视为一个唯一的组？
这是一个类似的解决方案，对连续表数据进行分组
不是很漂亮，但是您会从OP中找到逻辑基础。同一列上的连续数据
declare @mytable table (
id varchar(18),
name varchar(8),
[type] varchar(20),
livechattranscriptid varchar(18),
groupid varchar(100))
INSERT INTO @mytable (id,name,[type],livechattranscriptid,groupid) VALUES
('0DZ14000003I2pOGAS','34128314','ChatRequest','57014000000ltfIAAQ','57014000000ltfIAAQChatRequest'),
('0DZ14000003IGmQGAW','34181980','Enqueue','57014000000ltfIAAQ','57014000000ltfIAAQEnqueue'),
('0DZ14000003IHbqGAG','34185171','Enqueue','57014000000ltfIAAQ','57014000000ltfIAAQEnqueue'),
('0DZ14000003ILuHGAW','34201743','Enqueue','57014000000ltfIAAQ','57014000000ltfIAAQEnqueue'),
('0DZ14000003IQ6cGAG','34217778','Enqueue','57014000000ltfIAAQ','57014000000ltfIAAQEnqueue'),
('0DZ14000003IR7JGAW','34221794','PushAssignment','57014000000ltfIAAQ','57014000000ltfIAAQPushAssignment'),
('0DZ14000003IiDnGAK','34287448','Enqueue','57014000000ltfIAAQ','57014000000ltfIAAQEnqueue'),
('0DZ14000003IiDoGAK','34287545','PushAssignment','57014000000ltfIAAQ','57014000000ltfIAAQPushAssignment'),
('0DZ14000003Iut5GAC','34336044','Enqueue','57014000000ltfIAAQ','57014000000ltfIAAQEnqueue'),
('0DZ14000003Iv7HGAS','34336906','Accept','57014000000ltfIAAQ','57014000000ltfIAAQAccept')
;with myend as ( --- get all ends
select
*
from
(select
iif(groupid <> lead(groupid,1,groupid) over (order by name),
id,
'x') [newid],name
from @mytable
)x
where newid <> 'x'
)
, mystart as -- get all starts
(
select
*
from
(select
iif(groupid <> lag(groupid,1,groupid) over (order by name),
id,
'x') [newid], name,type,livechattranscriptid
from @mytable
)x
where newid <> 'x'
) ,
finalstart as ( --- get all starts including the first row
select id,
name,type,livechattranscriptid,
row_number() over (order by name) rn
from (
select id,name,type,livechattranscriptid
from (
select top 1 id, name,type,livechattranscriptid
from @mytable
order by name) x
union all
select newid,name,type,livechattranscriptid from mystart
) y
),
finalend as -- get all ends and add the last row
(
select id,
row_number() over (order by name) rn
from (
select id,name from (
select top 1 id,name
from @mytable
order by name desc) x
union all
select newid,name from myend
) y
)
select
s.id [startid]
,s.name
,s.type
,s.livechattranscriptid
,e.id [lastid]
from
finalend e
inner join finalstart s
on e.rn = s.rn --- bind the two results over the positions or row number
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句