[jira] Created: (ESPER-259) limiting resultsets

limiting resultsets
-------------------

                 Key: ESPER-259
                 URL: http://jira.codehaus.org/browse/ESPER-259
             Project: Esper
          Issue Type: Improvement
          Components: Core
    Affects Versions: 2.1
            Reporter: Torsten Curdt


The output clause does not have a limit to reduce the number of  events.

Right one would have to use two statements to get the sorted top n from a query.

 insert into UriCount select event.uri as uri, count(*) as count  from event.win:time(1 hour) group by uri
 select * from UriCount.ext:sort(count, true, 10) output every 5  seconds

I personally would prefer something along the lines of

 select event.uri as uri, count(*) as count  from event.win:time(1 hour) group by uri sorted by count limited to 10 output every 5 seconds

http://archive.esper.codehaus.org/user/B8A7E8AB-F0E7-4733-831B-2F96925DB4A5%40vafer.org


--
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-259?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Thomas Bernhardt updated ESPER-259:
-----------------------------------

    Summary: Enhance order-by and/or output rate limiting for ordered and batched result sets to output top or bottom N rows  (was: limiting resultsets)

> Enhance order-by and/or output rate limiting for ordered and batched result sets to output top or bottom N rows
> ---------------------------------------------------------------------------------------------------------------
>
>                 Key: ESPER-259
>                 URL: http://jira.codehaus.org/browse/ESPER-259
>             Project: Esper
>          Issue Type: Improvement
>          Components: Core
>    Affects Versions: 2.1
>            Reporter: Torsten Curdt
>
> The output clause does not have a limit to reduce the number of  events.
> Right one would have to use two statements to get the sorted top n from a query.
>  insert into UriCount select event.uri as uri, count(*) as count  from event.win:time(1 hour) group by uri
>  select * from UriCount.ext:sort(count, true, 10) output every 5  seconds
> I personally would prefer something along the lines of
>  select event.uri as uri, count(*) as count  from event.win:time(1 hour) group by uri sorted by count limited to 10 output every 5 seconds
> http://archive.esper.codehaus.org/user/B8A7E8AB-F0E7-4733-831B-2F96925DB4A5%40vafer.org

--
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-259?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=141473#action_141473 ]

Thomas Bernhardt commented on ESPER-259:
----------------------------------------

The syntax for output rate limiting is currently:
  output [all | first | last | snapshot] every output_rate [minutes | seconds | events]

When using order-by and output rate limiting, it can be preferable to output only the top N or bottom N rows according to sort order.

How about adding a "top" and "bottom" keyword:
  output [all | first | last | snapshot] every output_rate [minutes | seconds | events]  [ top|bottom X rows]

The the query would look as follows:
  select event.uri as uri, count as count from event.win:time(1 hour) group by uri order by count output every 5 seconds top 10 rows

> Enhance order-by and/or output rate limiting for ordered and batched result sets to output top or bottom N rows
> ---------------------------------------------------------------------------------------------------------------
>
>                 Key: ESPER-259
>                 URL: http://jira.codehaus.org/browse/ESPER-259
>             Project: Esper
>          Issue Type: Improvement
>          Components: Core
>    Affects Versions: 2.1
>            Reporter: Torsten Curdt
>
> The output clause does not have a limit to reduce the number of  events.
> Right one would have to use two statements to get the sorted top n from a query.
>  insert into UriCount select event.uri as uri, count(*) as count  from event.win:time(1 hour) group by uri
>  select * from UriCount.ext:sort(count, true, 10) output every 5  seconds
> I personally would prefer something along the lines of
>  select event.uri as uri, count(*) as count  from event.win:time(1 hour) group by uri sorted by count limited to 10 output every 5 seconds
> http://archive.esper.codehaus.org/user/B8A7E8AB-F0E7-4733-831B-2F96925DB4A5%40vafer.org

--
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-259?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=141475#action_141475 ]

Torsten Curdt commented on ESPER-259:
-------------------------------------

Sound fine to me. Was just thinking of the mysql 'limit' clause.

> Enhance order-by and/or output rate limiting for ordered and batched result sets to output top or bottom N rows
> ---------------------------------------------------------------------------------------------------------------
>
>                 Key: ESPER-259
>                 URL: http://jira.codehaus.org/browse/ESPER-259
>             Project: Esper
>          Issue Type: Improvement
>          Components: Core
>    Affects Versions: 2.1
>            Reporter: Torsten Curdt
>
> The output clause does not have a limit to reduce the number of  events.
> Right one would have to use two statements to get the sorted top n from a query.
>  insert into UriCount select event.uri as uri, count(*) as count  from event.win:time(1 hour) group by uri
>  select * from UriCount.ext:sort(count, true, 10) output every 5  seconds
> I personally would prefer something along the lines of
>  select event.uri as uri, count(*) as count  from event.win:time(1 hour) group by uri sorted by count limited to 10 output every 5 seconds
> http://archive.esper.codehaus.org/user/B8A7E8AB-F0E7-4733-831B-2F96925DB4A5%40vafer.org

--
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-259?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Thomas Bernhardt updated ESPER-259:
-----------------------------------

    Fix Version/s: 2.2

> Enhance order-by and/or output rate limiting for ordered and batched result sets to output top or bottom N rows
> ---------------------------------------------------------------------------------------------------------------
>
>                 Key: ESPER-259
>                 URL: http://jira.codehaus.org/browse/ESPER-259
>             Project: Esper
>          Issue Type: Improvement
>          Components: Core
>    Affects Versions: 2.1
>            Reporter: Torsten Curdt
>             Fix For: 2.2
>
>
> The output clause does not have a limit to reduce the number of  events.
> Right one would have to use two statements to get the sorted top n from a query.
>  insert into UriCount select event.uri as uri, count(*) as count  from event.win:time(1 hour) group by uri
>  select * from UriCount.ext:sort(count, true, 10) output every 5  seconds
> I personally would prefer something along the lines of
>  select event.uri as uri, count(*) as count  from event.win:time(1 hour) group by uri sorted by count limited to 10 output every 5 seconds
> http://archive.esper.codehaus.org/user/B8A7E8AB-F0E7-4733-831B-2F96925DB4A5%40vafer.org

--
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-259?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=141844#action_141844 ]

Thomas Bernhardt commented on ESPER-259:
----------------------------------------

By adding an optional "limit" clause after the "order by" clause, the iterator (poll API) would also benefit from this syntax. Therefore we may want to leave the output clause as is and add a limit clause just as MySQL/Oracle/Postgres support.

Example, see original example by Thorsten:
select event.uri as uri, count as count from event.win:time(1 hour) group by uri order by count limit 10 output every 5 seconds

The syntax of limit is:
  LIMIT <number of rows>
  LIMIT <range low endpoint>,   LIMIT <range high endpoint>

> Enhance order-by and/or output rate limiting for ordered and batched result sets to output top or bottom N rows
> ---------------------------------------------------------------------------------------------------------------
>
>                 Key: ESPER-259
>                 URL: http://jira.codehaus.org/browse/ESPER-259
>             Project: Esper
>          Issue Type: Improvement
>          Components: Core
>    Affects Versions: 2.1
>            Reporter: Torsten Curdt
>             Fix For: 2.2
>
>
> The output clause does not have a limit to reduce the number of  events.
> Right one would have to use two statements to get the sorted top n from a query.
>  insert into UriCount select event.uri as uri, count(*) as count  from event.win:time(1 hour) group by uri
>  select * from UriCount.ext:sort(count, true, 10) output every 5  seconds
> I personally would prefer something along the lines of
>  select event.uri as uri, count(*) as count  from event.win:time(1 hour) group by uri sorted by count limited to 10 output every 5 seconds
> http://archive.esper.codehaus.org/user/B8A7E8AB-F0E7-4733-831B-2F96925DB4A5%40vafer.org

--
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-259?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Thomas Bernhardt resolved ESPER-259.
------------------------------------

    Resolution: Fixed

> Enhance order-by and/or output rate limiting for ordered and batched result sets to output top or bottom N rows
> ---------------------------------------------------------------------------------------------------------------
>
>                 Key: ESPER-259
>                 URL: http://jira.codehaus.org/browse/ESPER-259
>             Project: Esper
>          Issue Type: Improvement
>          Components: Core
>    Affects Versions: 2.1
>            Reporter: Torsten Curdt
>             Fix For: 2.2
>
>
> The output clause does not have a limit to reduce the number of  events.
> Right one would have to use two statements to get the sorted top n from a query.
>  insert into UriCount select event.uri as uri, count(*) as count  from event.win:time(1 hour) group by uri
>  select * from UriCount.ext:sort(count, true, 10) output every 5  seconds
> I personally would prefer something along the lines of
>  select event.uri as uri, count(*) as count  from event.win:time(1 hour) group by uri sorted by count limited to 10 output every 5 seconds
> http://archive.esper.codehaus.org/user/B8A7E8AB-F0E7-4733-831B-2F96925DB4A5%40vafer.org

--
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-259?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Thomas Bernhardt closed ESPER-259.
----------------------------------


in release 2.2

> Enhance order-by and/or output rate limiting for ordered and batched result sets to output top or bottom N rows
> ---------------------------------------------------------------------------------------------------------------
>
>                 Key: ESPER-259
>                 URL: http://jira.codehaus.org/browse/ESPER-259
>             Project: Esper
>          Issue Type: Improvement
>          Components: Core
>    Affects Versions: 2.1
>            Reporter: Torsten Curdt
>             Fix For: 2.2
>
>
> The output clause does not have a limit to reduce the number of  events.
> Right one would have to use two statements to get the sorted top n from a query.
>  insert into UriCount select event.uri as uri, count(*) as count  from event.win:time(1 hour) group by uri
>  select * from UriCount.ext:sort(count, true, 10) output every 5  seconds
> I personally would prefer something along the lines of
>  select event.uri as uri, count(*) as count  from event.win:time(1 hour) group by uri sorted by count limited to 10 output every 5 seconds
> http://archive.esper.codehaus.org/user/B8A7E8AB-F0E7-4733-831B-2F96925DB4A5%40vafer.org

--
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