0

My event stream generally contains an open event followed by a close event. Let's call them O and C, correspndingly. However, there are two particulars:

  1. O may be followed by one or more O before C arrives
  2. C may be missing completely (see below)

It is assumed that C should arrive not later than within time T after some O. Otherwise, C is considered missing. When a C eventually arrives, all open Os arrived earlier than T from this C are considered orphans and are of no interest.

I want esper to fire each pair of O followed by C, where earliest O not farther then T from C is selected. Any Os in between as well as before selected O are skipped.

For example,

O1 O2 O3 ... C

should select (O1,C) if datediff(O1, C) < T

should select (O2,C) if above is false and datediff(O2, C) < T

etc.

A lost my temper in approaching this problem. Looks like my mind is not compatible with esper. Your help is very appritiated.

Vitamin C
  • 871
  • 6
  • 2

1 Answers1

0

This could be something like below, the idea is that when Event arrives we want to look at the last 1 minute of events and find the first one that matches the time diff. Have a second statement filter out those without matches, if that is desired.

insert into Pair select , (select window().firstOf(v => v.time - e2.time < T) from Event.win:time(1 min) as e1) as matched from Event as e2

select * from Pair where matched is not null

user650839
  • 2,594
  • 1
  • 13
  • 9
  • First of all, thank you for the response, Having your idea in mind, I tried the following query: `select (select first(*) from O.win:time(1 hour)), c.* from C c;` It outputs earliest `O` within hour from `C`. Unfortunately, it continues to report earliest `O` when subsequent pairs of `O` followed by `C` arrives within same hour. That is, for the following series where all events arrived within 1 hour: `O1 O2 C1 O3 C2` , it returns pairs `(O1,C1)` and `(O1, C2)`. While what I want is `(O1,C1), (O3,C2)`. There must be some means to reset time window of arriving `O`s, when `C` arrives... – Vitamin C Jun 26 '13 at 08:51