You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@pinot.apache.org by GitBox <gi...@apache.org> on 2021/03/22 21:05:12 UTC
[GitHub] [incubator-pinot] siddharthteotia opened a new issue #6707: Support EXPR_MIN, EXPR_MAX aggregation functions
siddharthteotia opened a new issue #6707:
URL: https://github.com/apache/incubator-pinot/issues/6707
Naming can be discussed but here is what the functions should do
An aggregation only query will return exactly one row and one column as the output of the aggregation function. So for example, we can do sum/min/max on a column and get the sum/min/max computed over the column values.
When aggregations are used with GROUP BY, the output rows are going to contain GROUP BY columns along with 0 or more aggregation columns.
However, there is currently no way to do the aggregation over one column by doing the computation min/max over the column values and return another column value corresponding to the minimum/maximum value in the aggregation column.
Examples
- For each unique member, return all columns where timestamp is maximum
-- SELECT id, EXPR_MAX(timestamp, *) FROM Foo GROUP BY id. MAX will be done over timestamp column but all columns will be returned
- For each unique product, find the cheapest supplier
-- SELECT product, EXPR_MIN(price, supplier) FROM Foo GROUP BY product. MIN will be done over price column but supplier column will be returned along with product group by column
Function will take two arguments
- Name of column to minimize/maximize just like we do today with MIN() and MAX()
- Names of column(s) (could be *) to return corresponding to minimized/maximized value
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
[GitHub] [incubator-pinot] fx19880617 commented on issue #6707: Support EXPR_MIN, EXPR_MAX aggregation functions
Posted by GitBox <gi...@apache.org>.
fx19880617 commented on issue #6707:
URL: https://github.com/apache/incubator-pinot/issues/6707#issuecomment-804431703
Hmm, what do you plan to handle in the case that multiple rows with the same max value? Do we want to return 1 row, or can we try to support multiple rows as well? Also, do we want to support order by on the selected columns?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
[GitHub] [incubator-pinot] siddharthteotia commented on issue #6707: Support EXPR_MIN, EXPR_MAX aggregation functions
Posted by GitBox <gi...@apache.org>.
siddharthteotia commented on issue #6707:
URL: https://github.com/apache/incubator-pinot/issues/6707#issuecomment-804428351
Yes, so just like current aggregation functions, it will return one row. If use along with GROUP BY, then there will be as many rows as the number of groups.
The main semantic difference between this and existing aggregation functions is that user may not necessarily want to return the column on which aggregation is computed on. So, that's where this will come handy
I was initially thinking its usage for MIN and MAX only. Generic syntax is good too
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
[GitHub] [incubator-pinot] amrishlal commented on issue #6707: Support EXPR_MIN, EXPR_MAX aggregation functions
Posted by GitBox <gi...@apache.org>.
amrishlal commented on issue #6707:
URL: https://github.com/apache/incubator-pinot/issues/6707#issuecomment-812247390
Looks useful, but we would probably need to hash out the edge cases and put it in a generic form. For example:
`SELECT id AS myid, EXPR_MAX(timestamp, *) AS mydata FROM Foo GROUP BY id`
in case of scalar or aggregate function "AS" will rename the projected column. We would need to hash out how AS will work in this case (and how it may feed into a higher-level query from a subquery using this function).
This will work well for MAX and MIN, but if we consider SUM or AVG etc, then we can have something like:
`SELECT id, EXPR_SUM(account_balance, name, email, *) FROM Foo GROUP BY id`
In this case, the first value of the output is the sum of the group, but how would the value of "name", and "email" be set?
It looks somewhat similar to a "Table Function", which are mainly used in the FROM clause:
SELECT name, max(b) FROM mytransform(mytable) as X GROUP BY X.name;
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
[GitHub] [incubator-pinot] kishoreg commented on issue #6707: Support EXPR_MIN, EXPR_MAX aggregation functions
Posted by GitBox <gi...@apache.org>.
kishoreg commented on issue #6707:
URL: https://github.com/apache/incubator-pinot/issues/6707#issuecomment-805063961
lets restrict it to return one row for now, makes it easy to reason about. To support order by etc, we can model that using the same syntax right for e.g
```reduce_function(sub_query("select .. from ... order by"), input columns, output columns)```
we can add support for more things like a groovy script etc.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org
[GitHub] [incubator-pinot] kishoreg commented on issue #6707: Support EXPR_MIN, EXPR_MAX aggregation functions
Posted by GitBox <gi...@apache.org>.
kishoreg commented on issue #6707:
URL: https://github.com/apache/incubator-pinot/issues/6707#issuecomment-804398562
I like this!
Can we find a better way to model this in a generic manner? This is more like a function that takes a bunch of rows and returns one row in the end right?
more like a
```reduce_function(functionName, input columns, output columns)```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@pinot.apache.org
For additional commands, e-mail: commits-help@pinot.apache.org