[jira] Created: (ESPER-399) Provide analog of prev function that behaves as aggregate

Provide analog of prev function that behaves as aggregate
---------------------------------------------------------

                 Key: ESPER-399
                 URL: http://jira.codehaus.org/browse/ESPER-399
             Project: Esper
          Issue Type: Improvement
          Components: Core
    Affects Versions: 3.1
            Reporter: Mitch
            Priority: Minor


If you use the prev function along with aggregates  in a group by, then it will be treated the same as if a non aggregated value had been selected from the event - so one row is returned for every event in the view. This does not seem intuitive to me- for example, prev(0, price) returns the same price for events in a window and so should behave the same as max(price).

AFAIK there is currently no convenient way of getting properties of the first or last events out of a window along with a set of aggregates. If the prev function can't be changed then it would be nice to have an analogue.

--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://jira.codehaus.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

       

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email

    [ http://jira.codehaus.org/browse/ESPER-399?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=192560#action_192560 ]

Thomas Bernhardt commented on ESPER-399:
----------------------------------------

Can you please provide an example statement, example input and output?
Properties of the last are prev(count(*) - 1, price) and prev(1, price).

> Provide analog of prev function that behaves as aggregate
> ---------------------------------------------------------
>
>                 Key: ESPER-399
>                 URL: http://jira.codehaus.org/browse/ESPER-399
>             Project: Esper
>          Issue Type: Improvement
>          Components: Core
>    Affects Versions: 3.1
>            Reporter: Mitch
>            Priority: Minor
>
> If you use the prev function along with aggregates  in a group by, then it will be treated the same as if a non aggregated value had been selected from the event - so one row is returned for every event in the view. This does not seem intuitive to me- for example, prev(0, price) returns the same price for events in a window and so should behave the same as max(price).
> AFAIK there is currently no convenient way of getting properties of the first or last events out of a window along with a set of aggregates. If the prev function can't be changed then it would be nice to have an analogue.

--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://jira.codehaus.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

       

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email

    [ http://jira.codehaus.org/browse/ESPER-399?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=192687#action_192687 ]

Mitch commented on ESPER-399:
-----------------------------

OK, I guess this only occurs in pretty specific circumstances. Here is an example:

MyEvent has row (a uid), quantity and colour and the listener outputs row count along with all rows it receives:

case 1 with aggregates only:
select colour, avg(quantity) from MyEvent.std:groupby(colour).win:length_batch(3) group by colour
1 100 blue
2 101 red
3 102 blue
4 103 red
5 104 blue
new=1 map={colour=blue, avg(quantity)=102.0}

Now suppose we wanted to know the row number at the point the aggregate was computed. My guess was to do the following:

case 2 with prev:
select prev(0, row), colour, avg(quantity) from MyEvent.std:groupby(colour).win:length_batch(3) group by colour
1 100 blue
2 101 red
3 102 blue
4 103 red
5 104 blue
new=3 map={colour=blue, avg(quantity)=102.0, prev(0,row)=1}
new=3 map={colour=blue, avg(quantity)=102.0, prev(0,row)=3}
new=3 map={colour=blue, avg(quantity)=102.0, prev(0,row)=5}

Multiple rows were returned when I was hoping for only the last one. Output clauses do not help. With sequential row ids I am using the workaround of max(id) but this is not pretty or general.

Use case: knowing the uid at the point aggregates are computed is handy for doing joins and for reporting.

> Provide analog of prev function that behaves as aggregate
> ---------------------------------------------------------
>
>                 Key: ESPER-399
>                 URL: http://jira.codehaus.org/browse/ESPER-399
>             Project: Esper
>          Issue Type: Improvement
>          Components: Core
>    Affects Versions: 3.1
>            Reporter: Mitch
>            Priority: Minor
>
> If you use the prev function along with aggregates  in a group by, then it will be treated the same as if a non aggregated value had been selected from the event - so one row is returned for every event in the view. This does not seem intuitive to me- for example, prev(0, price) returns the same price for events in a window and so should behave the same as max(price).
> AFAIK there is currently no convenient way of getting properties of the first or last events out of a window along with a set of aggregates. If the prev function can't be changed then it would be nice to have an analogue.

--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://jira.codehaus.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

       

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email

    [ http://jira.codehaus.org/browse/ESPER-399?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=192703#action_192703 ]

Mitch commented on ESPER-399:
-----------------------------

What I am trying to get across is that in a temporal context it makes perfect sense to have 'aggregates' like first() and last() which would be meaningless in ordinary SQL. Unless I am missing a trick I dont think esper provides this.

> Provide analog of prev function that behaves as aggregate
> ---------------------------------------------------------
>
>                 Key: ESPER-399
>                 URL: http://jira.codehaus.org/browse/ESPER-399
>             Project: Esper
>          Issue Type: Improvement
>          Components: Core
>    Affects Versions: 3.1
>            Reporter: Mitch
>            Priority: Minor
>
> If you use the prev function along with aggregates  in a group by, then it will be treated the same as if a non aggregated value had been selected from the event - so one row is returned for every event in the view. This does not seem intuitive to me- for example, prev(0, price) returns the same price for events in a window and so should behave the same as max(price).
> AFAIK there is currently no convenient way of getting properties of the first or last events out of a window along with a set of aggregates. If the prev function can't be changed then it would be nice to have an analogue.

--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://jira.codehaus.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

       

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email

     [ http://jira.codehaus.org/browse/ESPER-399?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Thomas Bernhardt updated ESPER-399:
-----------------------------------

    Fix Version/s: 4.0 - requires JDK6

> Provide analog of prev function that behaves as aggregate
> ---------------------------------------------------------
>
>                 Key: ESPER-399
>                 URL: http://jira.codehaus.org/browse/ESPER-399
>             Project: Esper
>          Issue Type: Improvement
>          Components: Core
>    Affects Versions: 3.1
>            Reporter: Mitch
>            Priority: Minor
>             Fix For: 4.0 - requires JDK6
>
>
> If you use the prev function along with aggregates  in a group by, then it will be treated the same as if a non aggregated value had been selected from the event - so one row is returned for every event in the view. This does not seem intuitive to me- for example, prev(0, price) returns the same price for events in a window and so should behave the same as max(price).
> AFAIK there is currently no convenient way of getting properties of the first or last events out of a window along with a set of aggregates. If the prev function can't be changed then it would be nice to have an analogue.

--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://jira.codehaus.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

       

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email

    [ http://jira.codehaus.org/browse/ESPER-399?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=194392#action_194392 ]

Thomas Bernhardt commented on ESPER-399:
----------------------------------------

Alternatively consider your own plug-in aggregation function.

> Provide analog of prev function that behaves as aggregate
> ---------------------------------------------------------
>
>                 Key: ESPER-399
>                 URL: http://jira.codehaus.org/browse/ESPER-399
>             Project: Esper
>          Issue Type: Improvement
>          Components: Core
>    Affects Versions: 3.1
>            Reporter: Mitch
>            Priority: Minor
>             Fix For: 4.0 - requires JDK6
>
>
> If you use the prev function along with aggregates  in a group by, then it will be treated the same as if a non aggregated value had been selected from the event - so one row is returned for every event in the view. This does not seem intuitive to me- for example, prev(0, price) returns the same price for events in a window and so should behave the same as max(price).
> AFAIK there is currently no convenient way of getting properties of the first or last events out of a window along with a set of aggregates. If the prev function can't be changed then it would be nice to have an analogue.

--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://jira.codehaus.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

       

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email

    [ http://jira.codehaus.org/browse/ESPER-399?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=194391#action_194391 ]

Thomas Bernhardt commented on ESPER-399:
----------------------------------------

You will find that Esper 3.2 allows the "last()" function for aggregation, it is not currently documented and supported, subject to change.
Scheduled for 4.0 release.

> Provide analog of prev function that behaves as aggregate
> ---------------------------------------------------------
>
>                 Key: ESPER-399
>                 URL: http://jira.codehaus.org/browse/ESPER-399
>             Project: Esper
>          Issue Type: Improvement
>          Components: Core
>    Affects Versions: 3.1
>            Reporter: Mitch
>            Priority: Minor
>             Fix For: 4.0 - requires JDK6
>
>
> If you use the prev function along with aggregates  in a group by, then it will be treated the same as if a non aggregated value had been selected from the event - so one row is returned for every event in the view. This does not seem intuitive to me- for example, prev(0, price) returns the same price for events in a window and so should behave the same as max(price).
> AFAIK there is currently no convenient way of getting properties of the first or last events out of a window along with a set of aggregates. If the prev function can't be changed then it would be nice to have an analogue.

--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://jira.codehaus.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

       

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email

    [ http://jira.codehaus.org/browse/ESPER-399?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=194412#action_194412 ]

Mitch commented on ESPER-399:
-----------------------------

Thanks. There are a bunch of general purpose temporal aggregates I can think of- Ill see how I get on implementing them.

> Provide analog of prev function that behaves as aggregate
> ---------------------------------------------------------
>
>                 Key: ESPER-399
>                 URL: http://jira.codehaus.org/browse/ESPER-399
>             Project: Esper
>          Issue Type: Improvement
>          Components: Core
>    Affects Versions: 3.1
>            Reporter: Mitch
>            Priority: Minor
>             Fix For: 4.0 - requires JDK6
>
>
> If you use the prev function along with aggregates  in a group by, then it will be treated the same as if a non aggregated value had been selected from the event - so one row is returned for every event in the view. This does not seem intuitive to me- for example, prev(0, price) returns the same price for events in a window and so should behave the same as max(price).
> AFAIK there is currently no convenient way of getting properties of the first or last events out of a window along with a set of aggregates. If the prev function can't be changed then it would be nice to have an analogue.

--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://jira.codehaus.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

       

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email

    [ http://jira.codehaus.org/browse/ESPER-399?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=194485#action_194485 ]

Thomas Bernhardt commented on ESPER-399:
----------------------------------------

If would be great if you can attach them here when done, is possible with test code.

> Provide analog of prev function that behaves as aggregate
> ---------------------------------------------------------
>
>                 Key: ESPER-399
>                 URL: http://jira.codehaus.org/browse/ESPER-399
>             Project: Esper
>          Issue Type: Improvement
>          Components: Core
>    Affects Versions: 3.1
>            Reporter: Mitch
>            Priority: Minor
>             Fix For: 4.0 - requires JDK6
>
>
> If you use the prev function along with aggregates  in a group by, then it will be treated the same as if a non aggregated value had been selected from the event - so one row is returned for every event in the view. This does not seem intuitive to me- for example, prev(0, price) returns the same price for events in a window and so should behave the same as max(price).
> AFAIK there is currently no convenient way of getting properties of the first or last events out of a window along with a set of aggregates. If the prev function can't be changed then it would be nice to have an analogue.

--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://jira.codehaus.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

       

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email

     [ http://jira.codehaus.org/browse/ESPER-399?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Mitch updated ESPER-399:
------------------------

    Attachment: aggregator_examples.tar.gz

Attached shows three new temporal aggregators in action:

leaving() returns true if events have started leaving view.
rate(timestamp) computes event rates
rate(timestamp,value) computes accumulated quantity rate (turnover)
nth(value,position) is aggregate analogue of prev()

There are five examples in all.

The src files are not part of a package- (compile with javac *.java) after uncommenting epl in TestEsper.java
Apologies for not using a test framework.

> Provide analog of prev function that behaves as aggregate
> ---------------------------------------------------------
>
>                 Key: ESPER-399
>                 URL: http://jira.codehaus.org/browse/ESPER-399
>             Project: Esper
>          Issue Type: Improvement
>          Components: Core
>    Affects Versions: 3.1
>            Reporter: Mitch
>            Priority: Minor
>             Fix For: 4.0 - requires JDK6
>
>         Attachments: aggregator_examples.tar.gz
>
>
> If you use the prev function along with aggregates  in a group by, then it will be treated the same as if a non aggregated value had been selected from the event - so one row is returned for every event in the view. This does not seem intuitive to me- for example, prev(0, price) returns the same price for events in a window and so should behave the same as max(price).
> AFAIK there is currently no convenient way of getting properties of the first or last events out of a window along with a set of aggregates. If the prev function can't be changed then it would be nice to have an analogue.

--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://jira.codehaus.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

       

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email

    [ http://jira.codehaus.org/browse/ESPER-399?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=195325#action_195325 ]

Mitch commented on ESPER-399:
-----------------------------

The built in last() aggregator has a serious bug in 3.2 so I don't recommend its use yet- especially not in a prepared statement. It seems its expression name is incorrectly mapped to "count".

So if you do "select last(eventID) from TestEvent" it actually gets reported to the listener as count(eventID).

> Provide analog of prev function that behaves as aggregate
> ---------------------------------------------------------
>
>                 Key: ESPER-399
>                 URL: http://jira.codehaus.org/browse/ESPER-399
>             Project: Esper
>          Issue Type: Improvement
>          Components: Core
>    Affects Versions: 3.1
>            Reporter: Mitch
>            Priority: Minor
>             Fix For: 4.0 - requires JDK6
>
>         Attachments: aggregator_examples.tar.gz
>
>
> If you use the prev function along with aggregates  in a group by, then it will be treated the same as if a non aggregated value had been selected from the event - so one row is returned for every event in the view. This does not seem intuitive to me- for example, prev(0, price) returns the same price for events in a window and so should behave the same as max(price).
> AFAIK there is currently no convenient way of getting properties of the first or last events out of a window along with a set of aggregates. If the prev function can't be changed then it would be nice to have an analogue.

--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://jira.codehaus.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

       

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email

    [ http://jira.codehaus.org/browse/ESPER-399?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=195329#action_195329 ]

Mitch commented on ESPER-399:
-----------------------------

filed as http://jira.codehaus.org/browse/ESPER-407

> Provide analog of prev function that behaves as aggregate
> ---------------------------------------------------------
>
>                 Key: ESPER-399
>                 URL: http://jira.codehaus.org/browse/ESPER-399
>             Project: Esper
>          Issue Type: Improvement
>          Components: Core
>    Affects Versions: 3.1
>            Reporter: Mitch
>            Priority: Minor
>             Fix For: 4.0 - requires JDK6
>
>         Attachments: aggregator_examples.tar.gz
>
>
> If you use the prev function along with aggregates  in a group by, then it will be treated the same as if a non aggregated value had been selected from the event - so one row is returned for every event in the view. This does not seem intuitive to me- for example, prev(0, price) returns the same price for events in a window and so should behave the same as max(price).
> AFAIK there is currently no convenient way of getting properties of the first or last events out of a window along with a set of aggregates. If the prev function can't be changed then it would be nice to have an analogue.

--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://jira.codehaus.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

       

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email