Esper count with parameters

Question

I'd like to know if it's possible to use a "count" function or equivalent but with parameters specified, to only count specific results.

So I'd like to do something like:

select
    ...
    "nbAccel", count(type='c8y_HarshBehavior' and behaviorType = "ACCELERATION")
    "nbBraking", count(type='c8y_HarshBehavior' and behaviorType = "BRAKING")
    "nbUnknown", count(type='c8y_HarshBehavior' and behaviorType = "UNKNOWN")
from
    ...

Let me show you what I want. I'm using the EPL Online tool.

Here is my EPL Statements (with the part I pasted above not working, of couse):

create schema EventCreated(
  source String,
  type String,
  time Date,
  behaviorType String
);

create schema CreateMeasurement(
  source String,
  type String,
  time Date,
  fragments Object
);



@Name("no_message_20min")
insert into
    EventCreated

select
    e.source as source,
    "c8y_SilentTracker" as type,
    new Date() as time

from pattern [
    every e = EventCreated(
        type != "c8y_HeartbeatReport" and
        type != "c8y_ObdDisconnectionReport" and
        type != "c8y_PowerOffReport" and
        type != "c8y_SilentTracker")

-> (timer:interval(20 minute) and
    not EventCreated(
        source = e.source and
        type != "c8y_HeartbeatReport"))];



@Name("create_context")
create context Trip
    context bySource
        partition by source from EventCreated,

    context byEvents
        start EventCreated(
            type = "c8y_ObdConnectionReport" or
            type = "c8y_PowerOnReport" or
            type = "c8y_FixedReport" or
            type = "c8y_HarshBehaviorReport") as startEvent

        end EventCreated(
            type = "c8y_ObdDisconnectionReport" or
            type = "c8y_PowerOffReport" or
            type = "c8y_SilentTracker") as endEvent;



@Name("context_end")
context Trip
    insert into
        CreateMeasurement

    select
        context.bySource.key1 as source,
        "Trip" as type,
        e.time as time,
        {
            "startedBy", context.byEvents.startEvent.type,
            "startedAt", context.byEvents.startEvent.time,
            "endedBy", e.type,
            "endedAt", e.time,
            "nbAccel", count(type='c8y_HarshBehavior' and behaviorType = "ACCELERATION"),
            "nbBraking", count(type='c8y_HarshBehavior' and behaviorType = "BRAKING"),
            "nbUnknown", count(type='c8y_HarshBehavior' and behaviorType = "UNKNOWN")
        } as fragments

    from
        EventCreated e

    output
        last when terminated;

Here is my Time And Event Sequence:

EventCreated = {
    source = 'tracker1',
    type = 'c8y_ObdConnectionReport',
    time =  '2016-10-07T10:00:00.000'
}

t = t.plus(5 minutes)

EventCreated = {
    source = 'tracker1',
    type = 'c8y_HarshBehavior',
    time = '2016-10-07 10:05:00.000',
    behaviorType = "UNKNOWN"
}

t = t.plus(5 minutes)

EventCreated = {
    source = 'tracker1',
    type = 'c8y_HarshBehavior',
    time = '2016-10-07 10:10:00.000',
    behaviorType = "ACCELERATION"
}

t = t.plus(5 minutes)

EventCreated = {
    source = 'tracker1',
    type = 'c8y_HarshBehavior',
    time = '2016-10-07 10:15:00.000',
    behaviorType = "BRAKING"
}

t = t.plus(5 minutes)

EventCreated = {
    source = 'tracker1',
    type = 'c8y_Location',
    time='2016-10-07 10:20:00.000'
}

t = t.plus(25 minutes)

EventCreated = {
    source = 'tracker1',
    type = 'c8y_Location',
    time = '2016-10-07 10:45:00.000'
}

t = t.plus(5 minutes)

EventCreated = {
    source = 'tracker1',
    type = 'c8y_ObdDisconnectionReport',
    time = '2016-10-07 10:50:00.000'
}

With a Beginning Of Time at 2016-10-07 10:00:00.000

Answer 1

Okay I found how to do it with the sum function like this:

"nbAccel", coalesce(sum(1, type='c8y_HarshBehavior' and behaviorType = "ACCELERATION"), 0),
"nbBraking", coalesce(sum(1, type='c8y_HarshBehavior' and behaviorType = "BRAKING"), 0),
"nbUnknown", coalesce(sum(1, type='c8y_HarshBehavior' and behaviorType = "UNKNOWN"), 0)