Esper中插入并查询同一窗口时无限循环的解决方案咨询

你当前的查询会因为新插入的HaltEvent进入keepall窗口后，再次触发关联逻辑生成新事件，形成无限循环。可以通过以下几种方式让Esper忽略该查询新增的事件：

1. 新增过滤条件，避免重复插入 #

在insert into语句中添加where条件，仅当生成的事件满足业务上的“新增/变化”要求时才插入。比如仅当无对应股票的HaltEvent，或新生成的类型与已有类型不同时执行插入：

insert into HaltEvent
select haltType(t, w) type, t.stockId as stockId
from StockEvent#lastevent as t
left outer join HaltEvent#keepall w on t.stockId = w.stockId
where w is null or haltType(t, w) != w.type

2. 使用上下文限定查询触发源 #

定义仅响应StockEvent的上下文，让查询仅在StockEvent到达时执行，忽略HaltEvent新增带来的触发：

create context StockEventTrigger
  initiated by StockEvent as se;

context StockEventTrigger
insert into HaltEvent
select haltType(t, w) type
from StockEvent#lastevent as t
left outer join HaltEvent#keepall w;

3. 改用On-Merge语句实现条件插入/更新 #

用on-merge替代普通insert，精准控制插入或更新的时机，避免重复生成事件：

on StockEvent#lastevent as t
left outer join HaltEvent#keepall w on t.stockId = w.stockId
merge into HaltEvent as target
where target.stockId = t.stockId
when not matched then
  insert select haltType(t, w) type, t.stockId as stockId
when matched and haltType(t, w) != target.type then
  update set type = haltType(t, w)

4. 调整窗口类型（结合业务需求） #

如果业务不需要保留所有HaltEvent，可将HaltEvent#keepall改为HaltEvent#unique(stockId)或HaltEvent#lastevent，仅保留单股票的最新事件，减少无意义的触发：

insert into HaltEvent
select haltType(t, w) type, t.stockId as stockId
from StockEvent#lastevent as t
left outer join HaltEvent#unique(stockId) w on t.stockId = w.stockId
where haltType(t, w) != coalesce(w.type, '')

内容的提问来源于stack exchange，提问作者Dakotah North