Joins

The examples in this topic show how to correlate values from different streams by using a join operation. A join operation compares each event from one input stream with each event from one or more other input streams. If their valid time intervals overlap and the join condition holds, the operation produces one output event.

As usual in LINQ, the join condition can be specified as an equi-join predicate between two input streams using the on … equals clause or by a where clause that can refer to two or more input streams. The equi-join predicate can compare single fields, like where x.a equals y.a or composite keys, for example where {x.a, x.b} equals {y.a, y.b} or where x equals y.

Examples

Inner Join

An inner join operation returns all events from two or more input streams when the join predicate between them evaluates to true. A join predicate is an expression that compares the payload fields of the event streams that are being joined. An inner join eliminates all events that do not have a matching event in the other specified event streams.

Equi Join

The following example compares events in stream stream1 with events in stream stream2. Events in the stream that meet the equality criteria defined in the on clause (in addition to overlap in the time intervals of the two events) are joined and output into a new event that contains the payload fields i and j from event e1 and field j from event e2.

// Assuming the following input event type for both stream1 and stream2.  
public class MyPayload  
{  
    public int i;  
    public float j;  
}  
  
var equiJoin = from e1 in stream1  
               join e2 in stream2  
               on e1.i equals e2.i  
               select new { e1.i, e1.j, e2.j };  

The equality predicate allows for the comparison of both primitive and composite types. For example, it is possible to join on {e1i, e1j} equals {e2i, e2j}.

Cross Join

A cross join (Cartesian product) operation returns an event stream that is the combination of each event from the first stream with each event from the second stream, for the duration of overlap of the intervals of the event from each stream. A filter expression can be used in the where clause to limit the events from each input stream. It is important to note that for two input streams a cross join with a where clause that checks for equality is equivalent to an equi-join with the corresponding on … equals clause. For non-equality predicates or more than two input streams, a cross join has to be used.

In the following example, events in stream1 that have a value in payload field i that is greater than 3 are joined with events from stream2 with a value in payload field j that is less than 10.

var crossJoin = from e1 in stream1  
                from e2 in stream2  
                where e1.i > 3 && e2.j < 10  
                select new { e1.i, e2.j };  

Left Anti-Semi-Join

The left anti-semi-join produces a join result for each event on the left side only if the result of the ordinary join is empty, for each point in time. This operation is useful to detect gaps with zero events.

The following example performs a left anti-semi join.

left.LeftAntiJoin
(right, (x, y) => left.A > right.B)  

The following illustration shows the above join result with two example input streams, assuming that the join condition evaluates to true. The intermediate ordinary join result is displayed as well.

// CepStream example:  
var leftAntiSemiJoin = from left in stream1   
                       where (from right in stream2   
                              where left.v == right.v  
                              select right).IsEmpty()  
                       select left;  

Joining Multiple Streams

You can join multiple streams in a single query, as shown in the following example.

var slopetest = from f in fastSignal  
                          from s in slowSignal  
                          from r in refSignal  
                          select new { alarm = f.avg / s.avg < r.Threshold };